SCD Type 2 in Practice

• The dimension gains three columns. valid_from is the instant the version became effective, valid_to the instant it stopped, and is_current a convenience flag that is redundant with valid_to IS NULL and exists because filtering on a boolean is cheaper to write and to read.
• Intervals are half-open: [valid_from, valid_to). The version is in force from valid_from inclusive up to but not including valid_to. The new version's valid_from equals the old version's valid_to exactly, so the timeline is covered with no gap and no overlap. This is the single most important implementation detail in the lesson, and using BETWEEN in the join breaks it (Merge Intervals).
• The current version is marked by valid_to IS NULL or by a sentinel far-future timestamp such as 9999-12-31. The sentinel is uglier and considerably safer, because it makes every interval comparison a normal comparison rather than a three-valued-logic puzzle.
• Each version gets its own surrogate key. That is what lets a fact reference a specific version without carrying a timestamp predicate of its own (Surrogate Keys).
• The fact load resolves the key once, at load time, by finding the version whose interval contains the event's timestamp. After that, every analyst query is a plain equality join and is historically correct without anyone thinking about it.
• Which timestamp bounds the interval is a choice with consequences. Detection time — when the pipeline saw the change — is easy and produces intervals that are records of your own observation. Effective time — when the change happened in the world — is what consumers actually mean and requires the source to tell you, which most do not. Bitemporal models keep both, and cost roughly what that sounds like (Event Time).

BEFORE the change -- dim_customer, one open version

+--------------+-------------+---------+---------+---------------------+---------------------+------------+
| customer_key | customer_id | country | segment | valid_from          | valid_to            | is_current |
+--------------+-------------+---------+---------+---------------------+---------------------+------------+
|         4470 | C-9912      | PL      | SMB     | 2024-01-08 00:00:00 | 9999-12-31 00:00:00 | TRUE       |
+--------------+-------------+---------+---------+---------------------+---------------------+------------+

AFTER the source reports country = DE, effective 2026-03-14 09:12:00

+--------------+-------------+---------+---------+---------------------+---------------------+------------+
| customer_key | customer_id | country | segment | valid_from          | valid_to            | is_current |
+--------------+-------------+---------+---------+---------------------+---------------------+------------+
|         4470 | C-9912      | PL      | SMB     | 2024-01-08 00:00:00 | 2026-03-14 09:12:00 | FALSE      |
|         4471 | C-9912      | DE      | SMB     | 2026-03-14 09:12:00 | 9999-12-31 00:00:00 | TRUE       |
+--------------+-------------+---------+---------+---------------------+---------------------+------------+
                                                   ^^^^^^^^^^^^^^^^^^^^^^
                                                   same instant, no gap, no overlap
                                                   interval is HALF-OPEN: [from, to)

  customer_id C-9912 now appears TWICE. It is no longer a key.
  The key is (customer_id, valid_from). The grain is one row per VERSION.

THE FACTS ARE UNTOUCHED

  fct_orders
  +----------+--------------+---------------------+---------+
  | order_id | customer_key | ordered_at          | revenue |
  +----------+--------------+---------------------+---------+
  | O-7781   |         4470 | 2025-06-02 14:31:00 |  500.00 |   -> PL forever
  | O-8420   |         4470 | 2026-03-14 08:00:00 |  310.00 |   -> PL (before)
  | O-8421   |         4471 | 2026-03-14 09:12:00 |  240.00 |   -> DE (at the boundary)
  | O-9003   |         4471 | 2026-04-11 10:05:00 |  180.00 |   -> DE
  +----------+--------------+---------------------+---------+

  2025 revenue by country still reports 500.00 in Poland, next year,
  and the year after. Nothing restates.

WHAT THE THREE BUGS LOOK LIKE

  BETWEEN valid_from AND valid_to      -> O-8421 matches BOTH rows: 240.00 counted twice
  valid_to = NULL with  ts < valid_to  -> NULL comparison is never true: O-8421 and
                                          O-9003 match NOTHING and leave the model
  two rows with is_current = TRUE      -> every fact for C-9912 duplicated

• Use half-open intervals with a far-future sentinel instead of NULL, and write the predicate as event_time >= valid_from AND event_time < valid_to. Never BETWEEN — it is inclusive at both ends and matches two versions at the boundary instant — and never a bare NULL bound, because a null comparison is never true and silently drops every fact belonging to the current version. Put the predicate in a macro so nobody types it by hand (SQL Transformations, Nullability & Defaults).
• Make the merge idempotent: key it on (natural_key, valid_from) so a re-run updates rather than inserts, and re-running the same day's load produces a byte-identical table (Idempotent Data Pipelines).
• Generate surrogate keys deterministically from (natural_key, valid_from) so a full rebuild reproduces the same keys and does not silently repoint every fact (Surrogate Keys).
• Version only the attributes that need it. A Type 2 dimension with SCD1 columns alongside is normal and correct, and it keeps version count down (Slowly Changing Dimensions).
• Test interval integrity on every load — exactly one current row per entity, no overlaps, no gaps — and block publication on failure. These are the checks that separate a Type 2 dimension from a table with three extra columns (Contract Enforcement).
• Provide an is_current = TRUE view with the pre-versioning shape, so consumers who only want current state are not forced to learn interval logic and cannot get it wrong (Model Layering).

• Type 2 guarantees a reproducible historical answer for the versioned attributes — and only if three things hold: intervals do not overlap, the fact load resolved keys against event time, and surrogate keys are stable across rebuilds. Any one of the three failing silently removes the guarantee while leaving the schema intact.
• It guarantees the interval boundaries reflect when the pipeline learned of the change, not when the change occurred, unless the source supplies an effective date. Those differ by however long the source took to tell you (CDC vs Polling).
• It does not guarantee that a change was observed at all. Two changes between two batch runs collapse into one version, and the intermediate state is not recoverable from the dimension (Batch vs Streaming Ingestion).
• is_current guarantees nothing on its own. It is derived state, and a merge that fails between closing the old row and inserting the new one leaves an entity with zero current rows — which an inner join treats as a customer who does not exist (Atomic Publish).