Data Engineering Fundamentals

1. Your orders fact table has one row per order, and the business now wants revenue broken down by product. What is the underlying problem?

• The product dimension is too wide and must be snowflaked into category tables before any join
• Revenue is stored as a non-additive measure, so it can never be grouped by product attributes
• The grain is too coarse: products sit on order lines, so the fact should be one row per line
• The fact table lacks a surrogate key of its own, so it cannot be joined to the product table

2. After loading dim_customer as a Type 2 dimension, SELECT COUNT(*) FROM dim_customer returns more than the number of real customers. Why?

• Every tracked change added another row for the same customer, so you should count distinct natural keys
• Surrogate keys are reused when customers leave, so old and new customers end up sharing the rows
• The join from the fact table multiplied the dimension rows when the count was taken on the table
• Type 2 copies the previous value into an extra column, which doubles the row count on every load

3. A sale is loaded into a fact table whose customer dimension is Type 2. Which customer key should the fact row store?

• The natural key from the source system, so the join keeps working after changes
• The surrogate key of the newest customer version, updated whenever it changes
• The surrogate key of the very first version that was loaded for that customer
• The surrogate key of the customer version that was current when the sale happened