How Do You Track History?

Concepts covered: SCD in Pipelines

A MERGE statement overwrites the target row with the latest values. The interviewer will ask: "But what if you need to know what the values were last week?" Overwriting destroys history. This is the SCD question, and your answer needs to be specific enough to prove you have implemented one. Vague hand-waving about "keeping old versions" is a red flag.

SCD Type 2 in Pipelines

SCD Type 2 keeps every version of a row. When a customer changes their address, you don't update the existing row - you close it (set valid_to to the current timestamp) and insert a new row with the new address and valid_from set to now. The result is a complete audit trail. The interviewer wants to hear you describe this mechanically, not abstractly. Walk through the MERGE logic step by step.

-- SCD Type 2 MERGE: close old version, insert new
MERGE INTO dim_customer AS target
USING staging.customer_changes AS source
  ON target.customer_id = source.customer_id
 AND target.is_current = TRUE

-- Close the old version
WHEN MATCHED
 AND target.customer_hash != source.customer_hash
THEN UPDATE SET
  target.valid_to   = source.effective_date,
  target.is_current = FALSE

-- Insert unchanged (no-op via no match)
WHEN NOT MATCHED THEN INSERT (
  surrogate_key, customer_id,
  name, address, customer_hash,
  valid_from, valid_to, is_current
) VALUES (
  GENERATE_UUID(), source.customer_id,
  source.name, source.address,
  source.customer_hash,
  source.effective_date, '9999-12-31',
  TRUE
);

The customer_hash column is the detail that separates a textbook answer from a production answer. It is a hash of all business columns (name, address, etc.). By comparing hashes, the MERGE detects whether anything actually changed - avoiding unnecessary version creation when the same data arrives twice. Senior signal: mention hash comparison unprompted. The interviewer will ask "how do you avoid creating a new version when nothing changed?" if you do not.

Surrogate Keys for History

In SCD Type 2, the natural key (customer_id) is no longer unique - there are multiple rows per customer, one for each historical version. You need a surrogate key that uniquely identifies each version. Fact tables that reference this dimension must join on the surrogate key and filter by valid_from/valid_to to get the correct version for a given point in time. The follow-up: "Show me how you would join a fact table to this SCD." Have that answer ready.

surrogate_key	customer_id	address	valid_from	valid_to	is_current
sk_001	C-100	123 Oak St	2024-01-01	2025-06-15	FALSE
sk_002	C-100	456 Elm Ave	2025-06-15	2026-02-01	FALSE
sk_003	C-100	789 Pine Dr	2026-02-01	9999-12-31	TRUE

Customer C-100 has lived at three addresses. Any fact table row from January 2025 joins to sk_001. A fact row from December 2025 joins to sk_002. The current address is always WHERE is_current = TRUE. Walk through this example in your interview - concrete data beats abstract explanations every time.

Orchestrating SCD Runs

SCD processing must happen in a specific order within your DAG: first load the staging table with today's changes, then run the MERGE to close old versions and insert new ones, then update any downstream fact tables that need to re-point their dimension keys. The trap: if you run the fact table load before the SCD MERGE completes, fact rows join to stale dimension versions. Mentioning DAG ordering shows the interviewer you have operated this in production, not just designed it.

SCD Type 2 preserves every historical version of a dimension row - walk through the MERGE logic step by step
Hash comparison prevents unnecessary version creation - mention this before the interviewer asks
Surrogate keys are mandatory - natural keys are no longer unique in SCD Type 2
DAG ordering matters: SCD MERGE must complete before downstream fact table loads

About this lesson

This section is part of the Incremental Loading: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.