Q9: How do you handle missing or unknown Dimension keys when a Fact record arrives? (For example, a claim comes in for an agent ID that doesn't exist in the system yet).
Answer:
We handle this by using a Default or "Unknown" Member in our dimension tables (usually assigned an ID of -1 or 0 with text like "Unknown Agent" or "Not Specified"). If a fact record arrives with an unrecognized foreign key, the ETL pipeline does not fail; instead, it automatically assigns the -1 default key. This ensures the row is still loaded into the fact table without breaking reports, and the data team is alerted to fix the missing master data
