A slowly changing dimension is a dimension table whose attributes change over time, such as a customer’s address or a claim’s status. Kimball’s scheme numbers the techniques for recording the change, and the numbers are the vocabulary to use: Type 1 overwrites the attribute and keeps no history, and Type 2 adds a new row per change with a row effective date, a row expiration date and a current-row indicator (Kimball Group, add new row).
In an FDE interview
The pattern appears whenever a customer asks what something looked like on a past date: which region an account belonged to when an invoice was raised, or how long a claim sat in each status. Building the table is half the answer, and querying it correctly is the other half.
Build it by comparing each incoming snapshot with the current rows on a hash of the tracked columns, then, in one transaction, closing the changed rows and inserting their new versions. Decide per attribute whether a change is a correction to overwrite or history to keep, and enforce one current row per key with a partial unique index where the engine has one, as PostgreSQL and SQLite do (CREATE UNIQUE INDEX one_current ON dim_customer (customer_id) WHERE is_current). Query with half-open ranges (valid_from <= d AND d < valid_to) so versions neither overlap nor leave gaps, and join each fact to the version valid at the fact’s own date. Store the open end as a far-future sentinel (valid_to = DATE '9999-12-31'), or write d < COALESCE(valid_to, DATE '9999-12-31'); with a NULL end, d < valid_to is never true and every current row drops out of the join.
Two planned SQL drills will practice it: claim status history builds the versions from daily snapshots, and inventory as of a date reconstructs a past state.