skip to content

How do you make an SCD Type 2 dimension load idempotent when the job reruns?

level: seniorimportance: must knowfreq 60%

answer

  1. run it twice, same result?
  2. who decides the effective_from value?
  3. current_timestamp is the usual culprit
  4. two source rows for one key in one batch
  5. a constraint that fails loudly beats a silent duplicate

basics

~20 s

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.

solid answer

~60 s

Idempotent means running the same input twice leaves the dimension in the same state as running it once. Four things get you there. First, the load must compare rather than append — insert a new version only where the incoming tracked-attribute hash differs from the hash on the key's current row, so the second run finds nothing to do. Second, never stamp effective dates with `current_timestamp` at execution time; derive them from a batch date or the source change timestamp, so a rerun computes identical values instead of a second version dated minutes later. Third, deduplicate staging to one row per business key per batch before loading, or a source with two updates for one key inserts two versions on one pass. Fourth, add a uniqueness constraint or test on business key plus effective-from and a test that each key has exactly one open row, so a violated assumption fails the run instead of silently corrupting history. Make the job recompute from source rather than resume from partial state.

code

sql · 12 lines
sql
-- version a key only when it is new or its tracked payload differs
insert into dim_customer
    (customer_id, customer_name, city, tier, hashdiff,
     effective_from, effective_to, is_current)
select s.customer_id, s.customer_name, s.city, s.tier, s.hashdiff,
       :batch_ts, timestamp '9999-12-31 00:00:00', 1
from stg_customer_hashed s
left join dim_customer d
       on  d.customer_id = s.customer_id
      and d.is_current   = 1
where d.customer_id is null
   or d.hashdiff <> s.hashdiff

go deeper

for a junior

Understand what a rerun means for a dimension: if the job runs twice on the same data, the table must not gain a second copy of the same version. Know that the load compares before it writes.

for a middle

Be able to name the concrete mechanisms — hash comparison against the current row, deterministic batch dates instead of the clock, deduplication of staging — and explain why each one is required rather than nice to have.

for a senior

Expect a scenario: a retried job corrupted the dimension. Show how you would detect it with assertion queries, repair it, and change the load so recomputation from source is always safe.

for a principal

Own the standard: reruns and backfills are normal operations, so every dimension load in the platform is written to be replayable and ships with its invariants as enforced tests, not as tribal knowledge in one team's runbook.

## What idempotence means here A load is idempotent when applying it twice to the same input produces the same result as applying it once. For a Type 2 dimension that is a concrete claim: after a rerun, every business key still has exactly one open version, no key has gained a duplicate version with identical attributes, and no effective date has moved. Reruns are not hypothetical — a task fails halfway and the orchestrator retries, an engineer reruns yesterday's batch to debug, a backfill replays a week. A load that is only correct on its first execution will corrupt the dimension the first time any of these happens, and it will do so quietly. ## Compare, do not append The root property is that the load's write set is derived from a comparison against the current state of the dimension, not from the arrival of a source row. Concretely: a key is inserted only if it has no current row, and it is versioned only if the incoming hashdiff differs from the hashdiff on its current row. On the second run of the same batch, the first run's output *is* the current state, the hashes now match, the changed set is empty, and both writes are no-ops. A load that instead says "insert every staging row as a new version" is not repairable by adding a retry policy; it is wrong by construction. ## Deterministic effective dates The most common idempotence bug is stamping the version boundary with the clock: ```sql set effective_to = current_timestamp -- not reproducible ``` Run the job at 02:00 and again at 02:40 and you get two different values, so even a comparison-driven load can produce a second version that differs from the first only in its dates. Pass the batch's logical date or the source change timestamp in as a parameter and use that everywhere in the run. This also makes a replayed backfill produce the same history it would have produced on the day, which is what makes reprocessing safe at all. ## Deduplicate the source first A snapshot extract usually has one row per key, but a change feed, a CDC stream or a union of files often has several. Loading them as-is inserts multiple versions in one pass, and — worse — the "current" row afterwards depends on insertion order, which is not deterministic. Reduce staging to one row per business key per batch before the compare, using a deterministic tiebreak (the highest source change timestamp, then the highest source sequence or offset). If you genuinely need every intermediate state as its own version, order the changes explicitly and apply them in sequence rather than letting the engine decide. ## Make violations loud Idempotence you cannot verify is a hope. Two cheap assertions catch nearly everything: ```sql -- no duplicate version boundaries select customer_id, effective_from from dim_customer group by customer_id, effective_from having count(*) > 1; -- exactly one open version per member select customer_id from dim_customer where is_current = 1 group by customer_id having count(*) > 1; ``` Run them as post-load tests, or better, enforce the first as a uniqueness constraint where the platform supports one so the offending write fails rather than lands. The failure mode you are protecting against is silent: nobody notices duplicated versions until a fact joined point-in-time starts returning two rows and a revenue number doubles. ## Recompute, do not resume Retry logic that tries to resume from wherever the previous attempt died has to know exactly what that attempt wrote, which is precisely the state a crash leaves ambiguous. Prefer a load whose steps are each safely re-executable from the top: rebuild staging from the source, recompute the changed set against the dimension as it now stands, apply close-plus-insert in a transaction. Then "retry" simply means "run it again", and a half-applied previous attempt is either rolled back by its transaction or absorbed by the comparison. ## The limit of idempotence One honest caveat: idempotence holds with respect to the *same input*. A rerun that re-reads a live source will see a newer snapshot, and a genuinely newer value legitimately produces a new version. That is correct behaviour, not a bug — but it means "the rerun added a version" is not by itself evidence the load is broken. Distinguishing the two cases is exactly why the load should key its dates on a batch parameter and read a pinned snapshot or an immutable staged extract wherever the platform allows.

  • Your source extract contains three updates for one customer in a single batch. What does the load do?
    Decide deliberately. Either collapse to the latest row per key using a deterministic tiebreak — highest source change timestamp, then sequence or offset — and record one version, or apply the three changes in explicit order to create three versions. What you must not do is load them unordered: the resulting current row depends on insertion order and differs between runs.
  • Which assertions would you run after every Type 2 load?
    Exactly one open version per business key; no duplicate business key plus effective-from pair; no version whose start is later than its end; and no gaps or overlaps in a key's version windows. These are cheap aggregate queries, and they turn silent history corruption into a failed run.
  • A retry ran after the close statement committed but before the insert. What state is the dimension in and how do you avoid it?
    That member has zero open versions, so every current-state query drops it and every point-in-time join after the close date finds nothing. Avoid it by wrapping close and insert in one transaction; if the platform cannot, make the load recompute from source on each attempt so the next run notices the member has no open row and inserts one.

saying these in an interview costs you the question

  • Stamping effective dates with current_timestamp at execution time
  • Inserting a new version for every staging row unconditionally
  • Assuming the orchestrator's retry makes the load safe
  • Loading a multi-row-per-key batch without deduplicating first
  • Testing only row counts instead of one-open-version-per-key

context

resolving links…