Don't Panic: Slowly Changing Dimensions

A slowly changing dimension is how a warehouse decides whether changing business labels are allowed to rewrite the past. A customer moves region, a product changes category, or an employee changes department. The data has changed. The awkward question is whether an old sale has changed with it. It has not. Its description might, which is why the warehouse needs a policy rather than a shrug.

With Type 1, the current dimension row is updated. Old facts show the replacement value. That is often right for a correction, such as display text that was wrong, because old reports gain nothing from preserving the typo. With Type 2, the old row closes and a new version begins. The fact keeps the key for the version that applied when it happened. History is not stored in a museum cabinet. It is stored so grouping an old event still means what it meant.

The two key names do different jobs. The business key recognizes the customer across loads. The surrogate key recognizes one warehouse version of that customer. This is the small plot twist: one person can be several dimension rows without becoming several customers. The rows are versions, not clones that escaped during a deployment.

The loader compares tracked attributes, expires the current version when a Type 2 value changes, and inserts a replacement with a new validity range. The ranges must meet without overlap. Only one version is current. Retrying the same input must not create another version. These rules sound fussy because data is very good at being fussy while looking entirely reasonable in a dashboard.

Read the Intro for the full policy, key, and late-arrival model. Use Slides for the row lifecycle. Keep the Cheatsheet nearby when choosing detection and checking version ranges. The Practice tab turns those decisions into a load and test sequence. The Exercise supplies one customer move and two sales, which is enough material for a historical reporting problem to make its intentions known.

SCDs do not decide what history matters. They make that decision executable. Start with the report question, then choose the row behavior that lets the answer keep its footing over time.