Data Engineering Interview: Design an SCD Type 2 Dimension

Problem and Scope

A customer source sends a complete daily snapshot of 50 million rows. About 1% of the attributes selected for history tracking change each day. Orders arrive continuously, while source corrections can arrive up to three days late. Design a Slowly Changing Dimension Type 2 customer dimension that preserves the value in effect when each order occurred, handles deletions, supports safe reruns, and remains auditable after backfills.

The core task is dimensional modeling and temporal correctness. Change Data Capture may supply input events, but it does not define the target history. The answer must decide which attributes deserve Type 2 treatment, distinguish a durable business key from a version's surrogate key, define non-overlapping validity intervals, and explain how a fact resolves the correct version.

The scale makes careless choices visible. A 1% daily change rate means about 500,000 new versions per day and 182.5 million per year. If an added version averages 300 bytes, that is about 54.75 GB of raw row data per year before indexes, replicas, metadata, and compression. These numbers are interview assumptions and a sizing baseline, not a storage guarantee.

What Interviewers Evaluate

A basic answer says, “expire the old row and insert a new one.” A strong answer first defines history semantics. Type 1 overwrites a value and loses its earlier state. Type 2 creates a new version with a new surrogate key and preserves the old version. Not every source column should trigger a version: correcting capitalization may be Type 1, while a sales territory or pricing band used in historical reporting may be Type 2.

The next signal is temporal discipline. The candidate should state an interval convention such as [validfrom, validto), ensure versions for one business key never overlap, and allow only one current version. A fact at time t matches the version whose start is at or before t and whose end is after t. Using load time when the requirement is business effective time silently misattributes late data.

Interviewers also look for production behavior. A repeated batch must not create another version. An incomplete snapshot must not delete millions of customers. Closing a current row and inserting its replacement must be atomic. Late corrections may require splitting a historical interval rather than changing only the current row. If the business needs both “when the value was effective” and “when the warehouse learned it,” ordinary SCD Type 2 is insufficient; that is a bitemporal requirement.

Questions to Clarify Before Answering

• Which attributes affect historical analysis? Track only agreed Type 2 fields. Audit fields and ingestion timestamps should not create business versions.
• Is customerid stable and never reused? It is the business key. Every historical version receives a separate customersk surrogate key.
• Does the source provide a trusted effective time or sequence? A daily snapshot proves when a value was observed, not necessarily when it became true. Without reliable source time, the warehouse cannot invent a correct backdated boundary.
• Is each snapshot explicitly marked complete? Treat absence as deletion only after completeness, row-count, and control-total checks pass. A partial extract is not a delete feed.
• What should a delete mean? This design closes the active version and inserts a current tombstone version with is_deleted = true, preserving an explicit deletion boundary.
• Do facts store customer_sk at ingestion or join by time at query? Resolving the surrogate key during fact loading makes later queries simpler. A temporal join remains useful for backfills and validation.
• Are corrections allowed to rewrite prior business history? If yes, keep raw source versions and an audit trail because a backfill can legitimately change earlier analytical results.
• Must the system retain knowledge time as well as business time? If auditors need both, model valid time and system time separately instead of forcing both into one interval.

30-Second Answer

I would first define the business key, Type 2 attributes, trusted effective time, and delete policy.
Each version gets a surrogate key and a half-open [valid_from, valid_to) interval; one version
per customer is current. I stage and deduplicate a complete snapshot, hash only tracked attributes,
then classify rows as unchanged, new, changed, or deleted. A changed key closes the old version and
inserts the new one atomically. The batch ID and source version make reruns idempotent. Facts resolve
the version effective at order time. Late corrections split the historical interval they affect, and
validation rejects duplicate current rows, overlapping intervals, broken fact references, or an
implausible delete surge.

Step-by-Step Deep Dive

Step 1: Define the grain and columns before the load

The grain is one version of one customer over one validity interval. A practical table contains:

dim_customer {
  customer_sk       // surrogate primary key for this version
  customer_id       // durable business key from the source
  segment
  sales_region
  valid_from
  valid_to           // null means open-ended
  is_current
  is_deleted
  change_hash        // canonical hash of Type 2 attributes only
  source_version
  load_batch_id
}

Use [validfrom, validto) consistently. When a change is effective at 2026-07-10T09:00:00Z, the old row ends at that instant and the new row starts at the same instant. The half-open convention makes the boundary belong to exactly one version. Store timestamps in a defined time zone and precision; mixing dates, local time, and UTC creates artificial gaps or double matches.

The business key groups the versions. The surrogate key identifies one immutable historical state and is the foreign key stored by facts. iscurrent is a convenience, not an independent truth: it must agree with validto IS NULL. A hash is only a comparison optimization. Canonicalize nulls, types, Unicode, and column order, and still preserve the actual tracked columns for explanation and audit.

Step 2: Size the write and scan path

The daily snapshot scans 50 million source rows. At a 1% change rate:

50,000,000 × 1% = 500,000 new versions/day
500,000 × 365 = 182,500,000 new versions/year
182,500,000 × 300 bytes ≈ 54.75 GB/year of raw row data

This separates scan cost from change-write cost. Stage the snapshot once, project only required columns, and compare it with current dimension rows by business key. Partitioning or clustering should follow actual query and maintenance patterns—often business key for lookups and validity time for pruning—rather than creating thousands of tiny daily partitions. Measure indexes, columnar compression, retention, and backfill amplification with production-shaped data.

Step 3: Make the ordinary batch deterministic and atomic

Land the extract under an immutable snapshotid. Before touching the target, verify the completion marker, schema, expected key uniqueness, row count, and control totals. Deduplicate by customerid using a trusted source version; two equally ranked but different rows are an input error, not a reason to pick one arbitrarily.

Canonicalize the tracked attributes and compare each staged row with the current target version:

1. No current business key: insert its first version.
2. Same tracked values: do nothing; refreshing loaded_at must not manufacture history.
3. Different tracked values: close the current row at the trusted effective time and insert a new current version.
4. Current target key absent from a verified complete snapshot: close it and insert a current tombstone version.

For every changed key, the close and insert occur in one target transaction or one atomic table operation. A uniqueness rule on the current version protects against two concurrent loaders. Record snapshotid, loadbatchid, and sourceversion, and reject an already applied source version. A retry after a lost acknowledgement then converges without duplicating history.

Step 4: Resolve facts at the business event time

When loading an order, resolve the customer version using the order's business timestamp, then persist customer_sk in the fact. A vendor-neutral point-in-time lookup has this shape:

SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
  AND d.is_deleted = false
  AND d.valid_from <= :order_time
  AND (d.valid_to > :order_time OR d.valid_to IS NULL);

The inequalities encode the half-open interval. The lookup must return exactly one row. Zero matches need an explicit unknown-member or quarantine policy; multiple matches prove corrupted history. For a late-arriving fact, use its event time, not its ingestion time. Kimball's Type 2 pattern uses the version's surrogate key in facts precisely so previously loaded facts retain the contemporary historical profile.

Step 5: Treat late corrections as interval surgery

Suppose the warehouse currently has Seattle from July 1 and Denver from July 12. On July 14 it receives a trusted correction saying Denver became effective on July 10. Updating only the current row leaves July 10 and July 11 wrong. Find the version containing July 10, close Seattle at July 10, and move Denver's start to July 10. If the correction introduces a third value inside an existing interval, split that interval and preserve the next known boundary.

Apply corrections in source-version order and lock or serialize updates per business key. Keep the raw input and a correction audit containing the prior intervals, new intervals, source version, batch, and reason. Re-resolve affected facts when the business contract says their foreign keys must reflect corrected history.

There is an information limit: a snapshot first observed on July 14 cannot prove that a value became effective on July 10 unless the source carries trustworthy business time or another ordered record. Use July 14 as observation time or quarantine the correction; silently backdating it would create fabricated precision. If the warehouse must retain both the July 10 valid time and the July 14 knowledge time, add a second system-time interval and call the model bitemporal.

Step 6: Validate invariants and operational guardrails

Test the target after each batch and before publication:

• each surrogate key is unique;
• each business key has exactly one current version, including a tombstone for a deleted key;
• iscurrent agrees with an open validto;
• intervals for a business key never overlap and every row has validfrom before validto when the end exists;
• unchanged source values do not add a version;
• the same source version or batch replay produces no new rows;
• every non-unknown fact surrogate key references a dimension row effective at the fact's event time;
• inserted, changed, unchanged, and deleted counts reconcile to the staged snapshot.

Monitor snapshot completeness, duplicate keys, change rate, delete rate, version growth, rejected late corrections, zero- and multi-match fact lookups, load latency, and rerun deltas. A sudden disappearance of 40 million keys should fail closed before it becomes 40 million tombstones. Test a new key, repeated identical snapshot, normal change, delete and recreate, three-day-late correction, out-of-order source version, partial snapshot, and crash between close and insert.

Strong Sample Answer

“I would model one row per customer version, not one row per customer. customerid is the durable business key, while every version gets a new customersk. The Type 2 fields are agreed with analysts—say segment and sales region—so a load timestamp or a formatting correction does not create history. Each row uses the half-open interval [validfrom, validto), and exactly one row per customer is current.

The source scans 50 million rows daily but changes about 500,000. That means roughly 182.5 million new versions per year; at an illustrative 300 bytes per version, raw growth is about 54.75 GB before physical overhead. I would stage the snapshot once and compare it only with current dimension rows instead of repeatedly joining the full history.

Every snapshot has a stable ID and must pass completion, uniqueness, schema, row-count, and control-total checks. I canonicalize and hash only the tracked Type 2 columns. New keys are inserted, equal hashes do nothing, and changed keys close the old interval and insert a new version in one atomic operation. A verified missing key closes the old version and inserts a deletion tombstone. The source version plus batch ID makes the load idempotent, and a current-row uniqueness rule blocks concurrent duplicate versions.

Orders resolve customersk with validfrom <= ordertime and validto > order_time, treating a null end as open. The lookup must return one non-deleted version. Late facts use order time, not arrival time. Late dimension corrections are applied to the interval containing their trusted effective time: split or adjust that interval and preserve later known boundaries. If the source gives only arrival time, I will not invent a three-day-earlier effective time. If both business time and warehouse-knowledge time must be queryable, I will propose a bitemporal model.

Before publication, I check one current version per business key, no interval overlap, valid boundaries, fact referential integrity, source-to-target counts, and zero-row change on an identical rerun. I alert on abnormal delete or change rates and keep raw snapshots plus a correction audit so a backfill is explainable and reversible.”

Common Mistakes

• Hashing every source column → Audit timestamps create meaningless versions → Hash only contractually tracked Type 2 attributes after canonicalization.
• Using the natural key as the dimension primary key → Multiple historical versions collide → Group by the business key and identify each version with a surrogate key.
• Mixing inclusive interval ends → A fact on a change boundary matches two rows → Adopt [validfrom, validto) end to end.
• Using ingestion time as effective time → Late updates and facts join to the wrong historical state → Use trusted business time and state the fallback when it is unavailable.
• Treating every missing snapshot row as deleted → A partial extract can erase the dimension → Require a completeness marker and anomaly checks before applying absence semantics.
• Closing and inserting in separate commits → A crash leaves zero or two current rows → Apply the version transition atomically and enforce current-row uniqueness.
• Creating a version on every rerun → Retries inflate history and change past answers → Persist source versions and batch identity, then make equal input a no-op.
• Repairing only the current row for a late correction → Earlier facts remain attached to the wrong state → Split or adjust the affected historical interval and re-resolve the impacted fact window.
• Calling any two timestamps “bitemporal” → Their meanings remain ambiguous → Name valid time and system time, and define both intervals and correction rules.
• Checking row counts only → Overlaps and duplicate current rows can survive → Validate temporal, referential, idempotency, and reconciliation invariants.

Follow-up Questions and Responses

Follow-up 1: Why not use Type 1 for every attribute?

Type 1 is correct for values whose prior form has no analytical meaning, such as a typo correction. It destroys the old value, so it cannot answer “which region owned this order then?” Classify attributes by reporting semantics. A single dimension can use Type 1 for some columns and Type 2 for others, provided the update rules are explicit.

Follow-up 2: Should the current row use valid_to = NULL or a far-future sentinel?

Either can work. NULL makes “open-ended” explicit but requires null-aware predicates. A sentinel such as a maximum supported timestamp can simplify range filters but may leak into reports or exceed another engine's date range. Choose one representation, make is_current consistent with it, and test every query and connector on the same convention.

Follow-up 3: How do you handle a delete followed by recreation of the same business key?

Close the active business version at deletion time and add a deletion tombstone. At recreation time, close the tombstone and insert a new active version with a new surrogate key. First confirm that the source truly reuses the same entity identity; if the identifier was recycled for a different person, introduce a durable key that distinguishes the entities.

Follow-up 4: What if the source has no reliable updated_at?

Compare an explicit list of canonical tracked columns, as snapshot tools' check strategies do. The resulting interval begins when the warehouse observes the change, not when the business change necessarily occurred. Document that limitation. If exact business-effective history is required, obtain a source sequence, audit log, CDC feed, or domain event rather than manufacturing a timestamp.

Follow-up 5: How is this different from a CDC pipeline?

CDC answers which committed inserts, updates, and deletes occurred and in what source order. SCD Type 2 answers how selected dimension attributes become historical versions and which surrogate key a fact should reference. A CDC feed can drive the SCD loader, and a snapshot can also drive it. Reliable capture does not by itself prevent overlapping validity intervals or wrong point-in-time joins.

Follow-up 6: When should you avoid Type 2 row growth?

Avoid it for rapidly changing attributes that are not needed for historical grouping, large free-form payloads, and operational state better represented as events or facts. Use Type 1, a separate mini-dimension, an accumulating or periodic fact, or a dedicated history table according to the query. The decision follows analytical semantics and measured write/query cost.

Follow-up 7: How do you prove a late backfill did not corrupt history?

Run the backfill into staging or a shadow table, compare interval sets by business key, and report inserted, split, shortened, extended, and deleted versions. Assert no overlaps, one current row, stable unaffected surrogate keys, and the expected fact-key changes only inside the corrected time window. Retain the raw source version and batch manifest for replay and rollback.