System Design · medium

How do you model a Type 2 slowly changing dimension, and how do you test it?

Asked in Analytics Engineer interviews, in the System Design round.

Short answer

One row per version of the entity with valid_from / valid_to and a current flag, and a surrogate key per version so facts join to the state as of the event. Test that exactly one version is current per natural key, that the validity windows never overlap, and that valid_from < valid_to — dbt unique/not_null plus a custom overlap test.

How to answer it

One row per version of the entity, each with a validity window, a current flag, and its own surrogate key so facts can join to the version that was true when the fact happened.

CREATE TABLE dim_customer (
  customer_key   BIGINT PRIMARY KEY,      -- surrogate, one per version
  customer_id    TEXT NOT NULL,           -- natural key, repeats across versions
  segment        TEXT,
  region         TEXT,
  valid_from     TIMESTAMP NOT NULL,
  valid_to       TIMESTAMP NOT NULL,      -- '9999-12-31' for the current version
  is_current     BOOLEAN NOT NULL
);

On a change: close the current version by setting valid_to to the change time and is_current to false, and insert a new version starting at that time. In dbt this is a snapshot with strategy='check' on the tracked columns, or timestamp if the source carries an updated_at.

Facts store the surrogate key resolved at load time with a range join on the event timestamp, so an order joins to the segment current when it was placed.

The tests are what make it trustworthy, because a subtle bug produces plausible numbers:

• Exactly one current version per natural key.
• Validity windows for a natural key never overlap and have no gaps.
• valid_from < valid_to on every row.
• Surrogate key unique and not null.
• Every fact row resolves to exactly one dimension version (a fan-out test on the join).

The one-current test and the overlap test are custom SQL in dbt; the rest are built-ins. Run them on every build; an SCD that silently grew two current versions doubles every metric joined through it.

• What they are checking: the version key, the half-open window, and that you name the overlap and one-current tests.
• Common mistake: joining facts on the natural key and filtering is_current, which reports today's attributes for all of history.

Related questions

• Design a data pipeline to ingest 1B events/day.
• Batch vs streaming: how do you decide for a given use case?
• A nightly pipeline fails halfway through. How do you design it so re-running is safe?
• Events can arrive up to 48 hours late. How do you handle that in a daily aggregation?
• When would you denormalize a warehouse table, and what do you give up?
• Design a data warehouse schema for an e-commerce company.

Practice this for real

• Check your resume against a Analytics Engineer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card