Data Engineering Interview: How Do You Choose an SCD Type and Handle Late Changes?

Prompt and when it applies

This is a dimensional-modeling and historical-semantics question. A customer address cannot be treated as only the latest value when a finance report must answer which region a customer belonged to on a past date. Assume daily full snapshots, no change log, and a target that supports current state, historical lookup, and replay. Dataquest lists SCD Types 1, 2, and 3 among data-engineering interview questions; AWS demonstrates Type 2 from full files without CDC.

What the interviewer is testing

• Defining whether reports ask “now” or “as of then” before choosing a type.
• Distinguishing overwrite, append-a-version, and one-previous-value semantics.
• Maintaining uniqueness with natural keys, surrogate keys, effective times, and a current flag.
• Handling late snapshots, duplicate files, deletes, replay, and concurrent writes instead of writing one UPDATE.

Clarifications to ask first

• Must reports reconstruct state at event time, or is the current profile enough?
• Do snapshots carry a version, extract time, or watermark, and can they arrive out of order?
• Does absence mean a business delete, temporary omission, or a source defect?
• May published history and fact joins be revised, and are audit, replay, and idempotent reruns required?

A 30-second answer

“I make the time semantics explicit: Type 1 keeps only the current value, Type 2 appends a version for every business change with validfrom and validto, and Type 3 keeps one or a small number of previous values. For point-in-time analysis I choose Type 2, look up the current row by natural key, join facts through a surrogate key, and use snapshot watermarks to identify late data and deletes. I land and deduplicate each batch, then close the old version and insert the new one atomically; replaying the same batch must produce the same result.”

Step-by-step solution

Step 1: Define the historical question

Type 1 fits corrections or profiles with no history and overwrites in place. Type 2 fits audit, revenue attribution, and point-in-time queries by keeping every version. Type 3 fits a fixed “current versus previous” window. If the requirement asks for a customer’s region last quarter, Types 1 and 3 lose information, so choose Type 2.

Step 2: Set Type 2 invariants

Store a surrogate key, natural key, attributes, validfrom, validto, iscurrent, and source-batch watermark. For each natural key, at most one row has iscurrent = true; effective intervals cannot overlap. AWS uses start/end dates, a current flag, and logical deletes for full history, while Microsoft's Type 2 configuration requires a natural key, surrogate key, two dates, and an active flag.

Step 3: Infer changes from a full snapshot

Write the file to an immutable landing table with file hash, extract time, and batch sequence. Compare an attribute hash for each natural key against the current dimension: insert new keys, close and append changed keys, and skip equal hashes. A missing key can imply a delete only when the complete-snapshot contract is true; an incremental file must not infer deletes from absence.

Step 4: Handle late and out-of-order data

Keep a monotonic watermark per source. Reject or quarantine a file below the applied watermark. If historical correction is allowed, split the affected effective interval and recompute impacted fact joins in a transaction; if published reports are immutable, put the batch in a correction queue and publish its impact. Using arrival time as validity time would turn an old snapshot into a new state.

Step 5: Deletes, replay, and concurrency

A missing key in a complete snapshot creates a logical-delete version or closes the current row, while retaining audit evidence. Make file hash or source batch a unique idempotency key; rerunning a batch must not append another version. Use a transaction or merge lock for the natural-key partition so closing and inserting are atomic. Apply batches by watermark order; an older batch must not reopen a closed interval.

A verifiable SQL skeleton

-- current_dim: one current row per customer_id
BEGIN;

UPDATE dim_customer AS old
SET valid_to = :as_of,
    is_current = FALSE,
    source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
  AND old.is_current = TRUE
  AND old.row_hash <> incoming.row_hash;

INSERT INTO dim_customer (
    customer_key, customer_id, city, valid_from, valid_to,
    is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
       incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
  ON old.customer_id = incoming.customer_id
 AND old.is_current = TRUE
WHERE old.customer_id IS NULL
   OR old.row_hash <> incoming.row_hash;

COMMIT;

Production still needs a unique constraint on batchid and pre-commit checks that each natural key has one current row, intervals do not overlap, and the input watermark is valid. The skeleton assumes stagecustomer is deduplicated per batch; it is not a complete out-of-order handler.

A high-quality sample answer

“I first ask which history the report must preserve. Use Type 1 for current-only values, Type 2 for a state at any past date, and Type 3 for exactly one previous value. For daily full snapshots, land the raw file and deduplicate by batch watermark and file hash. Compare natural keys and attribute hashes to classify inserts, changes, and no-ops. A Type 2 update must close the old row and insert a new version atomically, with one current row and non-overlapping intervals. Missing keys mean deletes only under a complete-snapshot contract; late batches are ordered or quarantined by watermark, and the report contract decides whether history is corrected.”

Common mistakes

• Defaulting to Type 2 for every dimension → higher storage and query cost → ask the history contract first.
• Using arrival time as effective time → out-of-order snapshots corrupt history → use business-effective time or a correction queue.
• Treating missing keys in an incremental file as deletes → valid increments are deleted → infer deletes only from complete snapshots.
• Updating is_current without a surrogate-key version → facts cannot join point-in-time state → close the old row and append a version.
• Omitting a batch idempotency key → replay appends duplicate history → enforce uniqueness on file hash or batch id.

Follow-ups and strong responses

Should a late event revise a published report?

Confirm whether the report is revisionable. If yes, split effective intervals by business time and recompute affected facts; if no, preserve the published result and issue a correction batch with its impact. Both contracts retain the raw batch so numbers do not change silently.

What if one customer appears twice in a snapshot?

Treat it as an input-quality error. Pick one row only when a business version or source sequence defines the winner; otherwise quarantine and alert. An arbitrary LIMIT 1 makes history irreproducible.

When is Type 3 better than Type 2?

Use Type 3 when the only required comparison is current versus previous and older versions will never be queried. It uses less storage and simpler queries. If arbitrary-date history appears later, migrate to Type 2 and state the backfill cost.

How do you verify that intervals do not overlap?

Sort by natural key and check that each interval ends no later than the next one starts, then assert at most one current row per key. Make this a batch release gate rather than a retrospective sample.