The Slowly Changing Dimension That Wasn't
A sales fact table joins to dim_customer on customer_sk. The dimension is loaded nightly with a type-2 pattern: a new row and new surrogate key whenever any attribute changes. customer_id is the durable business key. Last quarter, a customer's billing country changed from Germany to Ireland. The nightly load correctly closed the old row and inserted a new one. Two weeks later, the finance team reports that last year's revenue for that customer now appears under Ireland in the regional rollup. The ETL developer's fix is to stop tracking country as a tracked attribute, so the dimension will overwrite country in place. The fact table has 40 million rows and is partitioned by month.
1. Explain how the rollup came to show last year's revenue under Ireland, and why the proposed fix is wrong.
Read the worked answer
The fact rows were written when the customer was German, so they carry the German-era surrogate key. If the report joins fact to dimension on customer_sk and filters or groups by the dimension's current country, it should show Germany for those rows, because the old dimension row still exists and still says Germany.
The most likely cause is that the report joins on customer_id instead of customer_sk, or uses a "current record" flag (is_current = true) as a join filter. Either approach collapses history to the latest version, so every fact row for that customer, including last year's, resolves to Ireland. The type-2 design is fine; the query is bypassing it.
The proposed fix, overwriting country in place, destroys history permanently. After the overwrite, there is no record that the customer was ever German, so any restatement or audit of last year's regional revenue becomes impossible. It also silently changes the meaning of 40 million historical fact rows without touching them. That is worse than the bug.
2. State the correct fix and describe how you would validate it without a full reload of 40 million rows.
Read the worked answer
The correct fix is in the reporting layer: join fact to dimension on customer_sk only, and never filter the dimension by is_current when the question is historical. If analysts need a "current country" view, expose a separate current-dimension view keyed on customer_id and label it clearly as as-of-today, so the two questions are not conflated.
To validate without a full reload, I would pick a sample of months spanning the country change, say the two months before and two after, and compute regional revenue two ways: the corrected customer_sk join and a manual reconciliation against the source system for those customers. I would also run a query that counts fact rows whose customer_sk resolves to a dimension row with is_current = false; that count should be large and stable, confirming history is being used. If the totals match for the sample and the historical-row count is nonzero, the fix is sound. A full reload is unnecessary because no data changes.