Slowly Changing Dimensions & History

A Type 1 change overwrites the attribute in place, so only the current value exists and history is lost. A Type 2 change retires the old row and inserts a new version, so old facts still report under the value that was true then.

A hashdiff hashes the concatenated tracked attributes of an incoming source row and compares that value to the hash stored on the key's current dimension version. Equal means skip the row; different means close the old version and insert a new one.

Never drop the row. Either route the fact to the dimension's reserved unknown member, or — the usual choice — insert an inferred placeholder dimension row carrying the natural key, and enrich it when the real record arrives.

An SCD Type 3 column keeps one prior value beside the current one on the same dimension row, so the same measures can be reported under both the old and the new attribute at once. It captures a realignment, not a timeline.

Make the load comparison-driven, not append-driven: version a member only when the incoming hash differs from its current version, derive effective dates from a deterministic batch or source timestamp rather than wall-clock now, deduplicate the source per key, and enforce uniqueness on key plus start date so a duplicate fails loudly.

It attributes the event to the wrong dimension version. A six-week-old order keyed to today's current customer row lands in the customer's new segment or territory, so historical reports shift and the Type 2 history you paid to maintain is bypassed entirely.

Resolving once at load freezes each fact against the dimension version that was true when the event happened, and turns every later query into a plain equality join. Storing the business key instead pushes a range predicate into every query, and forgetting it fans facts out across versions.

One changed source row implies two writes to the dimension: an update that end-dates the current version and an insert of a new open version. An upsert acts on a matched target row once, so the load runs both writes in one transaction or feeds the merge a doubled source.

Because the placeholder attributes were never true. A Type 2 version would record a fake history in which the customer really was named "Unknown". Overwrite in place, keeping the surrogate key so existing facts stay attached.

Nothing new in information — it is derivable from the effective window — but it makes the common "as things are now" query a single cheap boolean filter instead of a date comparison. The cost is redundant state that must be flipped in the same operation that end-dates the old row.

Use half-open windows: effective_to of a closed version equals the effective_from of the next one, and the join predicate is ts >= effective_from AND ts < effective_to. End the open row with a far-future sentinel, not NULL, so ranges have no gaps or overlaps.

Versioning a weekly-changing attribute would multiply the dimension into tens of versions per member. SCD Type 4 moves those volatile attributes into a small separate table of distinct value combinations that the fact row joins to directly, leaving the base dimension stable.

You can only recover history the source can still replay. With a change log or CDC stream, replay the missed changes in order and insert backdated versions. With only a current-state snapshot, you get one version per changed key — date it from a trustworthy source timestamp, otherwise from the first missed day, and document the gap.

An updated_at strategy trusts the source to stamp every write, so it misses bulk backfills, trigger-bypassing writes and rows with a NULL timestamp, and it creates spurious versions when the stamp moves without a tracked attribute changing. Comparing tracked column values depends on nothing the source maintains.

Splice it into the middle of the chain, not onto the end. End-date the existing version whose window covers the change date, insert the missed version starting at that date and ending where the next version begins, and leave the newest row's current flag untouched.

Hop through the durable business key: join the fact to its stored version to get the business key, then join that back to the version currently in force. The fact's own surrogate key gives the as-was value; the second hop gives the as-is one.

An SCD Type 6 dimension carries a current-value column alongside its Type 2 versions, so the same star answers both as-it-was and as-it-is: group by the versioned column for history, or by the current column to roll a member's whole history under today's value.

Set an explicit lateness window per subject area, driven by each source's observed arrival lag and by which periods the business has closed. Inside the window, restate; outside it, book corrections forward and never silently change a signed-off number.

Decide the SCD type per column, not per dimension: Type 2 only where a report must read a value as it was, Type 1 for corrections and cosmetic labels, Type 0 for origin facts, Type 4 for volatile ones. Then document and test the policy.

Use timestamps when an entity can change more than once a day or when facts carry intra-day event times. Date-grain windows collapse same-day changes into zero-length or overlapping periods, so the point-in-time lookup cannot tell those versions apart.

SCD Type 0 suits attributes that describe an origin and are true forever: the durable business key, the signup or first-order date, the original credit band, the branch an account was opened at. The value is written once and later source updates are ignored.

Only facts whose event date falls inside the newly inserted version's window and that still carry the surrogate key of the version that was shortened. Repoint exactly those; facts outside the window are untouched.

Recompute the stored change hash for existing current rows under the new column list in the same deployment. Otherwise every stored hash was built over the old list, every incoming hash differs, and the next run closes and re-versions every member for no business reason.