Gowtham Potureddi

Posted on Aug 3

Slowly Changing Dimensions Deep Dive: SCD Types 0–6 With Cross-Dialect SQL

#python #sql #interview #dataengineering

slowly changing dimensions are the part of a warehouse where correctness quietly lives or dies — because a fact table can be perfectly loaded and still tell a lie the moment someone asks "what did this customer's segment look like when the order was placed?" A customer moves cities, a product is re-categorised, a sales rep changes territory, an account is upgraded from Silver to Gold: each of these is a mutating attribute on a descriptive entity, and the way you store that mutation decides whether every historical report re-writes itself overnight or stays faithful to the state of the world at the time each event happened. Get the storage decision wrong once and you cannot recover the history later; the old values are simply gone.

This guide is the deep dive you wished you had the first time an interviewer slid a whiteboard marker across the table and said "write me the Type 2 MERGE." It walks the full type map — Type 0 (retain-original), Type 1 (overwrite), Type 2 (full history with effective dates and a current flag), Type 3 (previous-value columns), Type 4 (mini-dimension split), and Type 6 (the 1+2+3 hybrid) — grounded in real, runnable dimensional modeling SQL. You will see the canonical Type 2 MERGE statement written four ways for Postgres, Snowflake, BigQuery, and Databricks; the surrogate key and versioning mechanics that make history queryable as-of any date; and the production concerns — dbt snapshots, late-arriving dimensions, delete handling, and idempotency — that separate a demo from a warehouse you can trust. Each section pairs a teaching block with a Solution-Tail interview answer: code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, work the modeling muscles on the dimensional-modeling practice library →, and rehearse the history mechanics on the slowly-changing-data practice library →.

On this page

What SCDs are and why the type matters
SCD Type 0 & Type 1: retain-original vs overwrite
SCD Type 2: full history with effective dates & current flag
Type 3, Type 4 & Type 6 (hybrid)
Production SCD: dbt snapshots, performance & pitfalls
Cheat sheet — SCD recipes
Frequently asked questions
Practice on PipeCode

1. What SCDs are and why the type matters

Dimensions describe, facts measure — and the SCD type decides how a changed description is remembered

The one-sentence invariant: a slowly changing dimension is a descriptive table whose attributes mutate over time, and the SCD type is the storage policy that decides whether a mutation overwrites the old value, keeps it as history, or is refused outright — a decision that binds every downstream report because you can never recover history you chose not to store. Facts are the immutable measurements — an order for $120, a click, a sensor reading — and they are almost always append-only. Dimensions are the nouns those facts point at — the customer, the product, the store, the employee — and unlike facts, their attributes drift: a customer relocates, a product is reclassified, a store changes region. The question "how do we handle a changed attribute?" has six standard answers, and picking among them is the load-bearing modeling decision in any star schema.

Dimensions vs facts — the recap that frames everything.

Facts are the events/measurements: narrow, tall, append-only, keyed by foreign keys to dimensions. You rarely update a fact row; you insert new ones.
Dimensions are the descriptive context: wide, comparatively short, and mutable. Each fact row references a dimension via a key, and the whole point of SCDs is deciding which version of the dimension a fact should point at.
The join is where history matters. fact_sales.customer_key → dim_customer.customer_key — if dim_customer overwrites the old segment, every historical sale silently inherits the new segment. If it versions, each sale keeps the segment that was true when it happened.
"Slowly" is the operative word: these attributes change occasionally (a customer moves maybe once a year), not on every event. Rapidly-changing attributes get special treatment (Type 4 mini-dimensions), which is exactly why Type 4 exists.

The full SCD type map — memorise this table shape.

Type 0 — retain-original. The attribute is never changed after first load. Date-of-birth, original-signup-channel, the account's original credit limit. Changes are rejected or ignored.
Type 1 — overwrite. The attribute is updated in place; the old value is discarded. One row per business key, always current, zero history. Correcting a typo in a customer name is the canonical use.
Type 2 — add new row. Each change inserts a new version of the row with a fresh surrogate key, and the prior version is expired via effective dates and a current flag. This is full history and the star of every interview.
Type 3 — add new column. Keep a current_value and a previous_value column (sometimes original_value too). Limited history — one prior value — with no row explosion. Used for "before/after" reorganisation analysis.
Type 4 — history / mini-dimension split. Move the volatile, rapidly-changing attributes out into a separate mini-dimension so the base dimension stays stable and small. Facts reference both.
Type 6 — hybrid (1 + 2 + 3). Combine a Type 2 versioned row, a Type 1 overwritten "current" column, and optionally a Type 3 "prior" column — so one query can ask both "what was true then" and "what is true now."

Why the type matters — the irreversibility argument.

You can always downgrade, never upgrade. A Type 2 dimension can answer every Type 1 question ("give me the current value" = filter is_current). A Type 1 dimension can never answer a Type 2 question, because the history was overwritten and is gone.
The choice is per-attribute, not per-table. A single dim_customer routinely mixes types: date_of_birth is Type 0, email is Type 1 (correct typos in place), segment and region are Type 2 (track history), and a current_segment convenience column may be Type 6.
Downstream code hard-codes the shape. Every dashboard, every fact-join, every reconciliation query assumes whether the dimension has one row per key or many. Changing the SCD type later is a migration, not a config flip.

What interviewers actually probe.

Can you name all the types and give a one-line use case for each? — baseline signal.
Can you write the Type 2 MERGE from memory, including expiring the old version and inserting the new one in the correct order? — the single most common SCD interview task.
Do you distinguish surrogate key (versioned, unique per row) from business/natural key (stable per entity)? — required answer; conflating them is an instant red flag.
Do you know that Type 2 is queried "as-of" via WHERE event_date BETWEEN effective_from AND effective_to, not by is_current? — senior signal.
Do you assign types per attribute and defend the mix? — senior signal.

Worked example — the SCD-type comparison table

Detailed explanation. The single most useful artifact for an SCD interview is a memorised comparison table. Every dimensional-modeling discussion converges on it within minutes; having it in your head is what separates a fluent answer from a stumbling one. Build the table across the four axes that actually matter.

Behavior. What happens to the old value when the attribute changes?
Storage. One row per key, or many? Extra columns?
History. None, one prior value, or full?
Query pattern. How does a consumer read the "right" version?

Question. Build the six-row SCD comparison for a dim_customer table and state the query pattern each type forces on downstream consumers.

Input.

Type	Behavior on change	Storage shape
0	reject / ignore change	1 row per key
1	overwrite in place	1 row per key
2	insert new version, expire old	many rows per key
3	shift value into prior column	1 row per key, +columns
4	move volatile attrs to mini-dim	base + mini-dim rows
6	version + current column + prior	many rows per key, +columns

Code.

-- A Type-annotated dim_customer illustrating a realistic per-attribute mix
CREATE TABLE dim_customer (
    customer_key      BIGINT       PRIMARY KEY,   -- surrogate key (Type 2 versioned)
    customer_id       BIGINT       NOT NULL,      -- business/natural key (stable)
    date_of_birth     DATE,                       -- Type 0: retain-original
    email             TEXT,                       -- Type 1: overwrite (fix typos)
    segment           TEXT,                       -- Type 2: full history
    prior_segment     TEXT,                       -- Type 3: one prior value
    current_segment   TEXT,                       -- Type 6: fast "latest" column
    effective_from    DATE         NOT NULL,      -- Type 2 validity start
    effective_to      DATE         NOT NULL,      -- Type 2 validity end
    is_current        BOOLEAN      NOT NULL       -- Type 2 current flag
);

Step-by-step explanation.

customer_key is the surrogate key — a meaningless integer that is unique per version. Two rows for the same physical customer (before and after a segment change) get two different customer_key values. Fact tables join on this key, which is how a sale "sticks" to the version that was current when it happened.
customer_id is the business key — stable for the life of the customer. All versions of a customer share one customer_id. Confusing these two keys is the classic SCD mistake: joining facts on customer_id collapses the history you worked to build.
date_of_birth is Type 0 — set once at first load, never updated. email is Type 1 — overwritten in place because you almost never want to report on a customer's old email.
segment is Type 2 — every change spawns a new row; effective_from, effective_to, and is_current describe each version's validity window.
prior_segment (Type 3) and current_segment (Type 6) are convenience columns layered on top: prior_segment remembers the immediately previous value; current_segment is overwritten across all versions of the customer so a "what is their segment now" query needs no is_current filter.

Output.

Type	History kept	Query pattern for consumers
0	original only (frozen)	read the single row directly
1	none (current only)	read the single row directly
2	full	`WHERE date BETWEEN effective_from AND effective_to` (as-of) or `WHERE is_current` (latest)
3	one prior value	read `current` and `prior` columns side by side
4	full for volatile attrs	join base dim + mini-dim on the fact
6	full + fast current	as-of via versions; latest via `current_*` column

Rule of thumb. Never pick "the SCD type for the table." Pick a type per attribute against (history-needed × query-pattern × storage-cost), then write the comparison table on the whiteboard so the mix falls out of the requirements.

Worked example — choosing a type per attribute

Detailed explanation. The senior move is refusing to answer "what SCD type is this dimension?" and instead classifying each attribute. Walk through a dim_employee with eight attributes and assign a type to each with a one-line justification, because that is exactly how the interview conversation actually goes.

Immutable facts about the entity → Type 0.
Corrections you never want to report historically → Type 1.
Business-meaningful state whose history drives reports → Type 2.
"Before/after" comparisons with only one prior value needed → Type 3.
High-churn attributes that would explode a Type 2 table → Type 4.

Question. Classify the eight attributes of dim_employee and justify each choice.

Input.

Attribute	Changes?	History needed?
employee_id (business key)	no	n/a
hire_date	no	n/a
legal_name	rarely (corrections)	no
department	yes	yes (headcount by dept over time)
job_level	yes	yes (promotions over time)
manager_id	yes	one prior often enough
office_city	yes	yes
daily_login_count_bucket	very frequently	no (churns constantly)

Code.

dim_employee — per-attribute SCD assignment
===========================================

employee_id .............. business key (natural)      -> stable, not an SCD choice
hire_date ................ Type 0 retain-original       -> never changes after load
legal_name ............... Type 1 overwrite            -> corrections only; no historical value
department ............... Type 2 add-new-row          -> headcount-by-dept over time needs history
job_level ................ Type 2 add-new-row          -> promotion timeline is a core report
manager_id ............... Type 3 prior-value column   -> "who was your last manager" is enough
office_city .............. Type 2 add-new-row          -> relocation history matters for tax/reporting
daily_login_bucket ....... Type 4 mini-dimension       -> churns daily; would explode a Type 2 table

Step-by-step explanation.

hire_date is Type 0 because it is definitionally fixed — an employee has exactly one hire date, and any "change" is a data-entry correction handled out of band, not a business event to version.
legal_name is Type 1: you fix "Jhon" → "John" and you never want a report that still says "Jhon." Overwrite, discard, move on.
department, job_level, and office_city are Type 2 because the history of these values is itself the report. "How many people were in Engineering in Q1 2025" requires knowing the department each person had at that time, which only versioning preserves.
manager_id is Type 3: analysts occasionally ask "who was your previous manager," but rarely the full management chain over years. One prior_manager_id column answers the common case without a versioned row per reorg.
daily_login_bucket is Type 4: bucketing a metric that moves every day would create a new Type 2 version daily, exploding the row count. Splitting it into a mini-dimension keyed by the bucket value keeps the base dim_employee stable while still letting facts point at the right bucket.

Output.

Attribute	SCD type	One-line justification
hire_date	0	immutable by definition
legal_name	1	corrections; no historical value
department	2	headcount-over-time report
job_level	2	promotion timeline
manager_id	3	one prior value is enough
office_city	2	relocation history matters
daily_login_bucket	4	too volatile for Type 2

Rule of thumb. When asked "what SCD type," answer "which attribute?" — then classify each one. The correct dimension is almost always a mix of types, and demonstrating that judgment is the senior signal.

Worked example — anatomy of a dimension row

Detailed explanation. Before writing any MERGE, you must be fluent in the anatomy of a Type 2 row: the two kinds of key, the validity window, the current flag, and (optionally) a version number and a change hash. Walk through each column and what breaks if it is missing.

Surrogate key — unique per version; the fact-join target.
Business/natural key — stable per entity; groups versions together.
Validity window — effective_from / effective_to bound each version in time.
Current flag — a fast boolean for "give me the latest version."
Optional — version number for ordering, row_hash for change detection.

Question. Lay out the columns of a production Type 2 dim_product row and state the invariant each column enforces.

Input.

Column	Role	Invariant it enforces
product_key	surrogate PK	one per version; fact-join target
product_id	business key	groups all versions of a product
name, category, list_price	attributes	the tracked payload
effective_from	validity start	inclusive lower bound
effective_to	validity end	exclusive/`9999-12-31` for open
is_current	latest flag	exactly one TRUE per business key
row_hash	change detector	equal hash ⇒ no change ⇒ no new version

Code.

CREATE TABLE dim_product (
    product_key    BIGINT       PRIMARY KEY,          -- surrogate (per version)
    product_id     BIGINT       NOT NULL,             -- business key (per entity)
    name           TEXT         NOT NULL,
    category       TEXT         NOT NULL,
    list_price     NUMERIC(12,2) NOT NULL,
    effective_from DATE         NOT NULL,
    effective_to   DATE         NOT NULL DEFAULT DATE '9999-12-31',
    is_current     BOOLEAN      NOT NULL DEFAULT TRUE,
    row_hash       TEXT         NOT NULL,             -- md5/sha of tracked columns
    -- exactly one current row per business key
    CONSTRAINT uq_current UNIQUE (product_id, is_current)
        DEFERRABLE INITIALLY DEFERRED
);

Step-by-step explanation.

product_key (surrogate) is the primary key and the only thing a fact table references. Because it is unique per version, a fact row that pointed at version 1 keeps pointing at version 1 forever, even after version 2 appears.
product_id (business key) is what the ETL uses to find "the current version of this product" during a load. It is never the join target for facts — only the grouping key for versions.
effective_from is an inclusive lower bound; effective_to defaults to the sentinel 9999-12-31 for the open (current) version. Some shops use NULL for open — both work, but 9999-12-31 keeps BETWEEN predicates simple and index-friendly.
is_current is a denormalised convenience: the same information is derivable from effective_to = '9999-12-31', but a boolean flag is faster to filter and clearer to read. The unique constraint on (product_id, is_current) enforces "at most one current version per product" — a guardrail that catches the most common Type 2 bug.
row_hash is the change detector. On each load you hash the tracked columns; if the incoming hash equals the current version's hash, nothing changed and you skip the expire-and-insert. This is what makes the load idempotent and cheap.

Output.

Column	Missing it causes
surrogate key	facts can't pin to a version; history collapses
business key	can't group versions; can't find "current" to expire
effective_from/to	can't answer as-of queries
is_current	slow/awkward "latest" filters
row_hash	re-processing writes duplicate versions on every run

Rule of thumb. A Type 2 row is surrogate key + business key + attributes + validity window + current flag + change hash. Drop any one and a specific failure mode appears; keep all six and the MERGE and as-of query both become trivial.

Dimensional-modeling interview question on choosing SCD types

A senior interviewer often opens with: "You're designing dim_customer for a retail warehouse. Product wants to know a customer's current email instantly, analyze churn by the segment customers were in at purchase time, occasionally compare a customer's current vs immediately-previous loyalty tier, and never lose a customer's original acquisition channel. Walk me through the SCD type you'd assign to each attribute, the keys you'd use, and why the mix beats picking one type for the whole table."

Solution Using a per-attribute SCD mix with a surrogate key and a change hash

-- dim_customer with an explicit per-attribute SCD mix
CREATE TABLE dim_customer (
    customer_key        BIGINT        PRIMARY KEY,      -- surrogate (Type 2 version id)
    customer_id         BIGINT        NOT NULL,         -- business key (stable)

    acquisition_channel TEXT          NOT NULL,         -- Type 0: retain-original
    email               TEXT          NOT NULL,         -- Type 1: overwrite in place
    loyalty_segment     TEXT          NOT NULL,         -- Type 2: full history
    prior_loyalty_tier  TEXT,                           -- Type 3: one prior value
    current_email       TEXT          NOT NULL,         -- Type 6: fast "latest" mirror

    effective_from      DATE          NOT NULL,
    effective_to        DATE          NOT NULL DEFAULT DATE '9999-12-31',
    is_current          BOOLEAN       NOT NULL DEFAULT TRUE,
    row_hash            TEXT          NOT NULL
);

-- Current email in one hop (Type 1/Type 6 convenience), no is_current needed:
SELECT customer_id, current_email
FROM   dim_customer
WHERE  is_current;                 -- current_email is identical across versions anyway

-- Churn by the segment in effect AT purchase time (Type 2 as-of join):
SELECT d.loyalty_segment, COUNT(*) AS orders
FROM   fact_sales f
JOIN   dim_customer d
  ON   f.customer_key = d.customer_key         -- surrogate join pins the version
GROUP  BY d.loyalty_segment;

Step-by-step trace.

Requirement	Attribute	SCD type	Mechanism
Current email instantly	email / current_email	1 + 6	overwrite in place; mirror onto all versions
Churn by segment-at-purchase	loyalty_segment	2	versioned rows; facts join on surrogate key
Current vs previous tier	prior_loyalty_tier	3	shift old tier into prior column on change
Never lose acquisition channel	acquisition_channel	0	set at first load; never updated
Join facts to correct version	customer_key	surrogate	one key per version

The mix works because each requirement has a different temporal shape. Churn analysis needs the value that was true at purchase time — only Type 2 preserves it, and only a surrogate-key join preserves the pin. "Current email instantly" needs the latest value with no as-of logic — Type 1/6 delivers it in one hop. "Original channel" must survive forever untouched — Type 0. Forcing all of them into one type either loses history (Type 1 everywhere) or makes the simple "current" queries needlessly filter across versions (Type 2 everywhere).

Output:

Query	Reads	Correct because
current email	`current_email` (or `is_current` row)	Type 1/6 keeps latest current everywhere
churn by segment	`loyalty_segment` via surrogate join	Type 2 pins the at-purchase version
tier before/after	`loyalty_segment` + `prior_loyalty_tier`	Type 3 remembers one prior value
original channel	`acquisition_channel`	Type 0 never overwritten

Why this works — concept by concept:

Surrogate key vs business key — the surrogate customer_key is unique per version and is the fact-join target, so a sale stays bonded to the version current at its time; the business customer_id groups versions and is used only by the ETL to locate "the current row to expire." Separating them is what makes as-of history correct.
Per-attribute typing — treating SCD type as an attribute-level decision, not a table-level one, lets one dimension satisfy contradictory requirements (instant-current and full-history) without compromise.
Type 6 mirror column — current_email is overwritten across all versions on every change, so the common "what is it now" query needs neither an as-of predicate nor an is_current filter join — a Type 1 answer physically living inside a Type 2 table.
Change hash — row_hash over the tracked columns lets the loader skip no-op changes, keeping the load idempotent and the version count honest.
Cost — one wide dimension row per version; storage grows with change frequency, not row count, so a slowly changing table stays small. The as-of join is O(1) per fact via the surrogate PK. Compared to a single-type table, the mix costs a few extra columns and buys every query pattern the business asked for.

SQL
Topic — dimensional-modeling
Dimensional-modeling and star-schema problems

Practice →

SQL Topic — slowly-changing-data Slowly-changing-dimension design problems

Practice →

2. SCD Type 0 & Type 1: retain-original vs overwrite

The two "single-row" types — one refuses every change, the other overwrites and forgets

The mental model in one line: SCD type 1 and Type 0 both keep exactly one row per business key, but Type 0 refuses every change to preserve the original value forever while Type 1 overwrites the value in place and discards the history — neither can ever answer "what was it before," which is precisely why you must choose them deliberately, per attribute, and only when history genuinely does not matter. These are the simplest SCD types and, paradoxically, the ones most often chosen by accident: a naive UPDATE dim SET col = new is a Type 1 decision made without realising a decision was made at all.

Type 0 — retain-original, in depth.

What it is. The attribute is written once at first load and never updated. Any later change from the source is ignored (or logged and discarded). The value is immutable by policy.
When it's right. Attributes that are conceptually fixed: date of birth, original acquisition channel, the account's first contract type, a product's launch date. Also compliance fields you must preserve as-first-recorded.
How to enforce it. Simply exclude the column from every UPDATE/MERGE update-set. Belt-and-braces: revoke UPDATE on the column, or add a trigger that raises on change. The strongest enforcement is schema-level, not code-review-level.
The trap. Type 0 is not the same as "we forgot to update it." Document it explicitly, or a future engineer will "fix the bug" and start overwriting a field that was frozen on purpose.

Type 1 — overwrite, in depth.

What it is. On change, UPDATE (or MERGE ... WHEN MATCHED THEN UPDATE) the existing row in place. One row per business key, always reflecting the latest value, zero history.
When it's right. Corrections (typos, formatting), attributes with no analytical history value (a customer's current phone number for operational lookups), and any dimension where the business explicitly says "we only ever care about the current value."
The data-loss trade-off. Every Type 1 overwrite is irreversible. If someone later asks "what was this customer's segment last quarter," and segment was Type 1, the answer is gone. This is the single most expensive silent mistake in dimensional modeling because it is invisible until the day you need the history.
The fact-restatement effect. Because facts join to the (single) dimension row, a Type 1 change retroactively rewrites every historical report that reads that attribute. A sale from two years ago will report under the customer's new segment. Sometimes that is desired (correcting a misspelled country); usually it is a bug.

Choosing between 0, 1, and "actually you need 2."

Ask: "Will anyone ever need the previous value?" If yes → Type 2 (or 3/6), not Type 1.
Ask: "Should this value ever change at all?" If no → Type 0.
Ask: "Is a change here a correction or a real-world event?" Corrections lean Type 1; real-world events (a move, a promotion) lean Type 2.
Default to caution. When unsure, Type 2 is the safe choice because it can always answer Type 1 questions, but Type 1 can never be upgraded to answer Type 2 questions after the fact.

Worked example — the Type 1 MERGE (Postgres + Snowflake)

Detailed explanation. The canonical Type 1 load is an upsert: insert new business keys, overwrite changed attributes on existing keys. Write it as a MERGE statement so the insert and update paths live in one atomic statement, and show it in both Postgres (which historically used INSERT ... ON CONFLICT and now also supports MERGE) and Snowflake.

Match key. The business key (customer_id), not a surrogate — Type 1 has no versions.
Matched path. Overwrite the tracked columns with the incoming values.
Not-matched path. Insert the new customer.
Idempotency. Re-running with the same source is a no-op (the values are already equal).

Question. Write the Type 1 upsert for dim_customer_t1 from a stg_customer staging table, in Postgres and Snowflake.

Input.

customer_id	name	email	segment (stg)
1	Ada Lovelace	ada@x.io	Gold
2	Alan Turing	alan@x.io	Silver
3	Grace Hopper	grace@x.io	Gold

Code.

-- Postgres (14+) — MERGE for a Type 1 overwrite upsert
MERGE INTO dim_customer_t1 AS t
USING stg_customer AS s
   ON t.customer_id = s.customer_id
WHEN MATCHED AND (t.name, t.email, t.segment)
              IS DISTINCT FROM (s.name, s.email, s.segment) THEN
    UPDATE SET name    = s.name,
               email   = s.email,
               segment = s.segment,
               updated_at = now()
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, segment, updated_at)
    VALUES (s.customer_id, s.name, s.email, s.segment, now());

-- Snowflake — same Type 1 upsert; note IFF/EQUAL_NULL for null-safe compare
MERGE INTO dim_customer_t1 t
USING stg_customer s
   ON t.customer_id = s.customer_id
WHEN MATCHED AND NOT (
        EQUAL_NULL(t.name, s.name)
    AND EQUAL_NULL(t.email, s.email)
    AND EQUAL_NULL(t.segment, s.segment)
) THEN UPDATE SET
        t.name       = s.name,
        t.email      = s.email,
        t.segment    = s.segment,
        t.updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
    INSERT (customer_id, name, email, segment, updated_at)
    VALUES (s.customer_id, s.name, s.email, s.segment, CURRENT_TIMESTAMP());

Step-by-step explanation.

Both dialects match on the business key customer_id because Type 1 keeps one row per customer — there are no versions and therefore no surrogate key to worry about.
The WHEN MATCHED AND ... IS DISTINCT FROM guard (Postgres) / AND NOT (EQUAL_NULL...) guard (Snowflake) makes the update fire only when something actually changed. Without it, every run rewrites every row and bumps updated_at, which pollutes change-tracking and wastes MERGE work.
IS DISTINCT FROM (Postgres) and EQUAL_NULL (Snowflake) are the null-safe comparisons. Plain = returns NULL (not TRUE) when either side is NULL, so a change from NULL → 'Gold' would be missed by a naive t.segment <> s.segment. Null-safety is a common interview gotcha.
WHEN NOT MATCHED THEN INSERT handles brand-new customers. Because the match key is the business key, a new customer_id falls through to the insert branch.
The whole statement is atomic and idempotent: run it twice on the same staging data and the second run matches everything, finds nothing distinct, and updates zero rows.

Output.

customer_id	name	email	segment	note
1	Ada Lovelace	ada@x.io	Gold	updated if changed
2	Alan Turing	alan@x.io	Silver	updated if changed
3	Grace Hopper	grace@x.io	Gold	inserted if new

Rule of thumb. Every Type 1 MERGE needs a null-safe "did anything actually change" guard on the matched branch (IS DISTINCT FROM in Postgres, EQUAL_NULL in Snowflake). It makes the load idempotent and keeps updated_at honest.

Worked example — enforcing Type 0 so a frozen attribute stays frozen

Detailed explanation. Type 0 is a policy, and policies decay unless enforced by the schema. The robust pattern is threefold: exclude the column from the loader's update-set, revoke UPDATE on the column, and add a trigger that raises if the value is ever changed after first write. Walk through enforcing Type 0 on acquisition_channel.

Exclude from update-set. The MERGE simply never lists the column in UPDATE SET.
Revoke column UPDATE. Even ad-hoc SQL can't change it.
Guard trigger. A BEFORE UPDATE trigger raises if the frozen column differs from its stored value.

Question. Enforce Type 0 on dim_customer_t1.acquisition_channel so no code path can change it after insert.

Input.

Layer	Enforcement
Loader	column absent from UPDATE SET
Grants	REVOKE UPDATE(acquisition_channel)
Trigger	raise if OLD ≠ NEW on that column

Code.

-- 1. Guard trigger: reject any change to the frozen column
CREATE OR REPLACE FUNCTION freeze_acquisition_channel() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.acquisition_channel IS DISTINCT FROM OLD.acquisition_channel THEN
        RAISE EXCEPTION
          'acquisition_channel is Type 0 (retain-original): % -> % rejected',
          OLD.acquisition_channel, NEW.acquisition_channel;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_freeze_acq_channel
BEFORE UPDATE ON dim_customer_t1
FOR EACH ROW EXECUTE FUNCTION freeze_acquisition_channel();

-- 2. Belt-and-braces: revoke column-level UPDATE from the loader role
REVOKE UPDATE (acquisition_channel) ON dim_customer_t1 FROM etl_writer;

Step-by-step explanation.

The trigger fires BEFORE UPDATE on each row and compares NEW.acquisition_channel to OLD.acquisition_channel with the null-safe IS DISTINCT FROM. If they differ, it raises — so any code path attempting to change the frozen column fails loudly instead of silently corrupting the Type 0 guarantee.
Using IS DISTINCT FROM (not <>) means a change to/from NULL is also caught; a plain inequality would let NULL → 'web' slip through.
REVOKE UPDATE (acquisition_channel) is column-level privilege enforcement — even a human running ad-hoc SQL as etl_writer cannot update the column. This is stronger than relying on the loader to omit the column, because it survives loader bugs.
The two mechanisms are complementary: the grant stops most attempts at the door; the trigger catches anything that gets past (e.g. a superuser role) and documents why it was rejected in the error message.
The loader's MERGE simply never mentions acquisition_channel in its UPDATE SET clause, so under normal operation nothing even tries to change it — the trigger and grant are defense in depth, not the primary path.

Output.

Attempted change	Result
loader UPDATE (column omitted)	no attempt; value unchanged
ad-hoc `UPDATE ... SET acquisition_channel` as etl_writer	permission denied
UPDATE as owner changing the column	trigger raises exception
UPDATE not touching the column	allowed

Rule of thumb. Enforce Type 0 in the schema, not in code review. A guard trigger plus a column-level REVOKE UPDATE turns "we agreed not to change it" into "the database will not let you change it."

Worked example — the address-change consequence under Type 1

Detailed explanation. The most-quoted Type 1 cautionary tale: a customer's state attribute is Type 1, a customer moves from Oregon to Texas, the loader overwrites state, and suddenly every historical sales report by state is wrong because two years of Oregon sales now report as Texas. Walk through the failure and the fix.

The setup. dim_customer_t1.state is Type 1; fact_sales.customer_key joins to it.
The change. Customer 42 moves OR → TX; loader overwrites state.
The damage. Historical sales for customer 42 retroactively re-attribute to TX.
The fix. Promote state to Type 2 so each sale keeps the state that was true when it happened.

Question. Show the wrong result Type 1 produces after the move, and the corrected result once state becomes Type 2.

Input.

Event	Date	Customer 42 state at the time
Sale A ($100)	2024-06-01	Oregon
Sale B ($150)	2025-02-01	Oregon
Move OR → TX	2026-01-15	—
Sale C ($200)	2026-03-01	Texas

Code.

-- WRONG under Type 1: state was overwritten to 'TX', so ALL sales report TX
SELECT d.state, SUM(f.amount) AS revenue
FROM   fact_sales f
JOIN   dim_customer_t1 d ON f.customer_key = d.customer_key
WHERE  d.customer_id = 42
GROUP  BY d.state;
-- => TX | 450    (Oregon history lost: $250 mis-attributed)

-- RIGHT under Type 2: fact joins the version current at sale time
SELECT d.state, SUM(f.amount) AS revenue
FROM   fact_sales f
JOIN   dim_customer_t2 d ON f.customer_key = d.customer_key   -- surrogate pins version
WHERE  d.customer_id = 42
GROUP  BY d.state;
-- => OR | 250
--    TX | 200

Step-by-step explanation.

Under Type 1 there is exactly one row for customer 42, and after the move its state is TX. Because all three sales join to that single row, GROUP BY state buckets all $450 under Texas — the $250 of genuine Oregon revenue vanishes from history.
This is the retroactive restatement effect: a Type 1 overwrite silently rewrites the past for every report that reads the changed attribute. Nobody ran an UPDATE against the fact table, yet the historical numbers changed.
Under Type 2, the move creates a new version (a new surrogate customer_key) with state = 'TX', while the old version keeps state = 'OR'. Sales A and B were loaded pointing at the Oregon-version surrogate key; Sale C points at the Texas-version key.
The corrected query joins fact_sales.customer_key to the surrogate key, so each sale reads the state that was true when it happened: $250 Oregon, $200 Texas. History is faithful.
The lesson generalises: any attribute you might ever slice a historical fact by (region, segment, tier, category) is a Type 2 candidate. Reserve Type 1 for attributes you would never group a historical report by.

Output.

Model	Oregon revenue	Texas revenue	Faithful to history?
Type 1 (overwrite)	0	450	no — $250 mis-attributed
Type 2 (versioned)	250	200	yes

Rule of thumb. If you might ever GROUP BY a historical fact on an attribute, that attribute cannot be Type 1 — a Type 1 overwrite retroactively restates every past report. Slice-able attributes are Type 2 by default.

SCD Type 1 interview question on the overwrite trade-off

A senior interviewer might ask: "A team modeled customer_country as Type 1 to keep the dimension small. A large customer relocated from Germany to the US, and now the CFO's year-over-year revenue-by-country report shifted €2M from Germany to the US overnight, with no ETL change. Explain exactly why this happened, whether it's a bug or a feature, and how you'd remediate without losing the ability to also see 'current country' cheaply."

Solution Using a Type 2 promotion with a Type 6 current-country mirror

-- Promote customer_country from Type 1 to Type 2, keeping a fast "current" mirror (Type 6).
-- 1. New versioned dimension
CREATE TABLE dim_customer_v2 (
    customer_key      BIGINT       PRIMARY KEY,        -- surrogate per version
    customer_id       BIGINT       NOT NULL,           -- business key
    customer_country  TEXT         NOT NULL,           -- Type 2 (versioned)
    current_country   TEXT         NOT NULL,           -- Type 6 mirror (always latest)
    effective_from    DATE         NOT NULL,
    effective_to      DATE         NOT NULL DEFAULT DATE '9999-12-31',
    is_current        BOOLEAN      NOT NULL DEFAULT TRUE
);

-- 2. Historical revenue by the country in effect at sale time (correct going forward)
SELECT d.customer_country AS country_at_sale,
       DATE_TRUNC('year', f.sale_date) AS yr,
       SUM(f.amount) AS revenue
FROM   fact_sales f
JOIN   dim_customer_v2 d ON f.customer_key = d.customer_key
GROUP  BY 1, 2
ORDER  BY 2, 1;

-- 3. "Current country" cheaply via the Type 6 mirror, no as-of logic
SELECT customer_id, current_country
FROM   dim_customer_v2
WHERE  is_current;

Step-by-step trace.

Step	Before (Type 1)	After (Type 2 + Type 6 mirror)
Rows per customer	1	1 per country-era
Historical country	overwritten to latest	preserved per version
YoY report on relocation	€2M shifts DE → US	stays in DE for pre-move years
"Current country" query	read the one row	read `current_country` (or `is_current`)
Fact join key	business key (collapses history)	surrogate key (pins version)

The root cause is that Type 1 stores one row per customer, so the country column always holds the latest value; because facts join through to that single row, the entire relocated customer's history re-attributes to the US the instant the overwrite lands — no ETL change required, just the natural consequence of the storage model. It is neither a bug in the loader nor a feature — it is the defined behavior of Type 1, chosen (probably unknowingly) when customer_country was made Type 1. The remediation promotes the attribute to Type 2 (versioned rows with effective dates), which pins each historical sale to the country that was true at sale time, while a Type 6 current_country mirror keeps the "where are they now" query a single cheap hop.

Output:

Report	Type 1 result	Type 2 + mirror result
2024 revenue Germany	reduced by €2M	correct (includes the customer)
2026 revenue US	inflated by €2M	correct (post-move only)
current country	Germany→US (1 row)	US via `current_country`
history recoverable	no	yes

Why this works — concept by concept:

Type 1 retroactive restatement — with one row per key, overwriting an attribute rewrites the value for all facts joined to it, so a single relocation silently restates years of history. This is the defining hazard of SCD type 1 and the reason slice-able attributes must not use it.
Type 2 versioning — inserting a new row per change, bounded by effective_from/effective_to, preserves the value that was true in each era; the surrogate-key join binds each fact to its era.
Surrogate key pinning — because facts reference the version-scoped customer_key, promoting the attribute does not touch the fact table; old facts keep pointing at the old (Germany) version.
Type 6 mirror column — current_country is overwritten across every version so "current country" stays a one-hop read, recovering the cheap-current benefit that made someone reach for Type 1 in the first place — without the history loss.
Cost — a few extra rows per relocating customer (relocations are rare, so the table barely grows) plus one mirror column. The YoY report becomes correct and stable; the current-country query stays O(1). Compared to the Type 1 model, the only added cost is storage proportional to change frequency, which for a slowly changing attribute is negligible.

SQL
Topic — sql
SQL upsert and MERGE problems

Practice →

SQL Topic — slowly-changing-data Overwrite vs history SCD problems

Practice →

3. SCD Type 2: full history with effective dates & current flag

The star of every interview — a new versioned row per change, bounded by effective dates and a current flag

The mental model in one line: SCD type 2 records every historical state of a dimension by inserting a new row with a fresh surrogate key on each change, expiring the prior version by stamping its effective_to and clearing is_current, so any fact joins to exactly the version that was valid when it happened and any analyst can query the dimension "as-of" any date in the past. This is the type interviewers mean when they say "write the SCD SQL," and the load is always the same two moves: expire the old version, insert the new version — atomically, idempotently, in one MERGE where the dialect allows it.

The five columns that make Type 2 work.

Surrogate key (customer_key) — unique per version, generated by the warehouse (identity/sequence). Fact tables join on this. Never expose it to the business.
Business key (customer_id) — stable per entity; groups versions; the ETL uses it to find the row to expire.
effective_from / effective_to — the validity window. effective_from is the change's timestamp/date; effective_to is 9999-12-31 (or NULL) for the open version and gets stamped when the version is superseded.
is_current — a denormalised boolean, exactly one TRUE per business key. Redundant with effective_to = '9999-12-31' but far cheaper to filter.
Optional version / row_hash — a monotonic version number for ordering, and a hash of tracked columns for change detection.

The two-step load, always.

Step 1 — expire. For each business key whose tracked attributes changed, set the current version's effective_to = change_date - 1 (or the change instant) and is_current = FALSE.
Step 2 — insert. Add the new version with effective_from = change_date, effective_to = '9999-12-31', is_current = TRUE, and a new surrogate key.
Order matters. Expire before insert (or use a MERGE that does both), or you momentarily have two current rows and violate the one-current-per-key invariant.
Change detection. Only keys whose row_hash (or column-by-column compare) differs get a new version; unchanged keys are skipped so re-runs don't create phantom versions.

Two ways consumers read a Type 2 dimension.

Latest — WHERE is_current (or effective_to = '9999-12-31'). Use for "current state" dashboards.
As-of — WHERE :as_of_date >= effective_from AND :as_of_date < effective_to (half-open) or BETWEEN (closed). Use for "what was true at that time" analysis.
Fact joins pin automatically. Because the fact stored the surrogate key of the version current at event time, fact.customer_key = dim.customer_key needs no date predicate at all — the pin is baked in at load.

Cross-dialect notes (all four support MERGE, with quirks).

Snowflake / BigQuery / Databricks — first-class MERGE; a single statement can update-then-insert, but a single MERGE cannot both expire the old row and insert a new row for the same key in one pass, so the standard trick is a two-part load (or a MERGE whose source is unioned to drive both branches).
Postgres — has MERGE (14+) but the idiomatic Type 2 load is often a CTE: UPDATE ... RETURNING to expire, then INSERT the new versions.
Surrogate generation — IDENTITY/GENERATED ALWAYS AS IDENTITY (Postgres/Snowflake), GENERATE_UUID() or a sequence table (BigQuery has no native sequences), Delta IDENTITY columns (Databricks).

Worked example — the Type 2 MERGE deep dive (cross-dialect)

Detailed explanation. The canonical Type 2 load expires changed current rows and inserts their new versions. Because most engines can't expire-and-insert the same key in one MERGE pass, the robust pattern is: (1) a MERGE (or UPDATE) that expires current rows whose incoming hash differs, then (2) an INSERT of the new versions. Write it for Snowflake, then note the BigQuery and Databricks deltas.

Source. stg_customer with the latest attributes per business key, plus a computed row_hash.
Expire. Current rows whose stored hash ≠ incoming hash get effective_to/is_current stamped.
Insert. New business keys and changed keys get a fresh current version.
Skip. Unchanged keys touch nothing.

Question. Implement the two-step Type 2 load for dim_customer in Snowflake, with the BigQuery/Databricks differences called out.

Input.

customer_id	segment (incoming)	stored current segment	action
1	Gold	Gold	skip (unchanged)
2	Platinum	Gold	expire + insert (changed)
3	Silver	(none)	insert (new key)

Code.

-- SNOWFLAKE — Step 1: expire current versions whose tracked columns changed
MERGE INTO dim_customer t
USING (
    SELECT customer_id,
           MD5(COALESCE(segment,'') || '|' || COALESCE(region,'')) AS row_hash
    FROM   stg_customer
) s
   ON t.customer_id = s.customer_id
  AND t.is_current = TRUE
WHEN MATCHED AND t.row_hash <> s.row_hash THEN UPDATE SET
        t.effective_to = CURRENT_DATE() - 1,
        t.is_current   = FALSE;

-- SNOWFLAKE — Step 2: insert new versions for new keys AND changed keys
INSERT INTO dim_customer
    (customer_id, segment, region, effective_from, effective_to, is_current, row_hash)
SELECT s.customer_id, s.segment, s.region,
       CURRENT_DATE(), DATE '9999-12-31', TRUE,
       MD5(COALESCE(s.segment,'') || '|' || COALESCE(s.region,''))
FROM   stg_customer s
LEFT JOIN dim_customer t
       ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHERE  t.customer_id IS NULL                                   -- brand-new key
    OR t.row_hash <> MD5(COALESCE(s.segment,'') || '|' || COALESCE(s.region,''));  -- changed

-- BIGQUERY deltas: use TO_HEX(MD5(...)) for the hash, CURRENT_DATE(),
-- and DATE '9999-12-31'. No IDENTITY columns — generate the surrogate key with
-- GENERATE_UUID() or ROW_NUMBER() OVER(...) + an offset. Same two-step shape.

-- DATABRICKS (Delta) deltas: identical MERGE syntax; surrogate key via a Delta
-- IDENTITY column (GENERATED ALWAYS AS IDENTITY) or monotonically_increasing_id().
-- Cluster/ZORDER BY (customer_id, is_current) for fast expire lookups.

Step-by-step explanation.

Step 1's MERGE matches only current rows (t.is_current = TRUE) for each incoming business key, and updates only when the stored row_hash differs from the freshly computed incoming hash. That expires exactly the versions that need superseding — no more, no less.
The hash is computed over the tracked columns (segment, region) with COALESCE so NULLs are compared safely. Comparing a single hash is cheaper and less error-prone than a long null-safe column-by-column predicate.
Step 2 inserts a new current version for two populations: brand-new business keys (the LEFT JOIN ... WHERE t.customer_id IS NULL branch) and changed keys (the row_hash <> branch). Unchanged keys match a current row with an equal hash, so they satisfy neither condition and are skipped.
The two steps are ordered expire-then-insert so the one-current-per-key invariant is never violated mid-load. Wrapping both in a transaction makes the whole load atomic; a crash between steps leaves the DB unchanged on rollback.
Cross-dialect, the shape is identical — the only differences are the hash function spelling, the surrogate-key generator, and clustering hints. BigQuery lacks identity/sequences (use GENERATE_UUID()); Databricks and Snowflake have identity columns; all four honor the same MERGE semantics.

Output.

customer_id	versions after load	current segment	note
1	1	Gold	unchanged, skipped
2	2	Platinum	old Gold expired, new Platinum current
3	1	Silver	inserted (new key)

Rule of thumb. The Type 2 load is always expire-then-insert, driven by a change hash, wrapped in a transaction. The MERGE syntax is portable across Snowflake, BigQuery, and Databricks; only the hash function, surrogate generator, and clustering hint change.

Worked example — querying the dimension "as-of" any date

Detailed explanation. The payoff of Type 2 is the as-of query: reconstruct the exact state of a dimension at any point in the past. There are two idioms — a direct as-of filter on the dimension, and an implicit pin via the surrogate key stored on the fact. Show both, and the half-open interval that avoids double-counting at boundaries.

Direct as-of. Filter the dimension where the target date falls inside a version's validity window.
Fact pin. Join the fact's stored surrogate key — no date predicate needed.
Half-open window. from <= d < to avoids the row that ends and the row that starts on the same boundary date both matching.

Question. Return customer 2's segment as-of 2025-12-31 and as-of today, then compute revenue by the segment in effect at each sale.

Input.

customer_key	customer_id	segment	effective_from	effective_to
501	2	Gold	2024-01-01	2026-03-14
502	2	Platinum	2026-03-15	9999-12-31

Code.

-- As-of a specific date (half-open interval: from <= d < to)
SELECT customer_id, segment
FROM   dim_customer
WHERE  customer_id = 2
  AND  DATE '2025-12-31' >= effective_from
  AND  DATE '2025-12-31' <  effective_to;      -- => Gold

-- As-of "now": the current version
SELECT customer_id, segment
FROM   dim_customer
WHERE  customer_id = 2 AND is_current;          -- => Platinum

-- Revenue by the segment in effect AT sale time — two equivalent ways:

-- (a) implicit pin via the surrogate key stored on the fact (preferred)
SELECT d.segment, SUM(f.amount) AS revenue
FROM   fact_sales f
JOIN   dim_customer d ON f.customer_key = d.customer_key
GROUP  BY d.segment;

-- (b) explicit as-of join by business key + date (when the fact lacks the surrogate)
SELECT d.segment, SUM(f.amount) AS revenue
FROM   fact_sales f
JOIN   dim_customer d
  ON   d.customer_id = f.customer_id
 AND   f.sale_date >= d.effective_from
 AND   f.sale_date <  d.effective_to
GROUP  BY d.segment;

Step-by-step explanation.

The as-of filter uses a half-open interval: d >= effective_from AND d < effective_to. For 2025-12-31 that matches version 501 (Gold), whose window is 2024-01-01 .. 2026-03-14. Using 9999-12-31 as the open-ended effective_to means the current version always matches any recent date.
The "as-of now" query is just is_current — no date arithmetic needed, and it's the fastest path because is_current is a cheap boolean filter (and often part of a clustering key).
Query (a) is the preferred fact join: the fact stored customer_key = 501 for sales made while Gold was current and 502 for sales after the change, so the join pins each sale to its era with no date predicate. This is the whole reason surrogate keys exist.
Query (b) is the fallback when the fact only has the business key: you join on customer_id and the half-open date window. It's correct but slower (range join) and depends on sale_date being the right temporal grain.
The half-open interval is what prevents boundary double-counting: on the exact changeover date, only the new version's effective_from <= d and the old version's d < effective_to line up so a single version matches — never both.

Output.

Query	Result
as-of 2025-12-31	Gold
as-of now	Platinum
revenue via surrogate pin	Gold = pre-change sales, Platinum = post-change
revenue via as-of join	identical to surrogate pin

Rule of thumb. Prefer the surrogate-key pin for fact joins (no date predicate, fastest), and use a half-open from <= d < to interval for direct as-of queries so boundary dates never match two versions.

Worked example — the Postgres CTA-style two-step load

Detailed explanation. Postgres has MERGE, but the most readable idiomatic Type 2 load uses a CTE that expires changed current rows with UPDATE ... RETURNING, then inserts new versions. Walk through it because Postgres is the interview whiteboard default and the CTE form makes the two steps explicit.

Change set. A CTE identifies business keys whose incoming hash differs from the stored current hash (plus brand-new keys).
Expire. UPDATE the current rows in the change set.
Insert. Insert the new versions from the same change set.
Transaction. All in one transaction for atomicity.

Question. Write the Postgres Type 2 load for dim_customer using a staging table stg_customer.

Input.

customer_id	segment (stg)	current stored segment
2	Platinum	Gold (changed)
3	Silver	(new)
1	Gold	Gold (unchanged)

Code.

BEGIN;

WITH incoming AS (
    SELECT customer_id, segment, region,
           md5(coalesce(segment,'') || '|' || coalesce(region,'')) AS row_hash
    FROM   stg_customer
),
changed AS (                       -- keys that are new OR whose hash differs
    SELECT i.*
    FROM   incoming i
    LEFT JOIN dim_customer d
           ON d.customer_id = i.customer_id AND d.is_current
    WHERE  d.customer_id IS NULL OR d.row_hash <> i.row_hash
),
expired AS (                       -- Step 1: expire the outgoing current versions
    UPDATE dim_customer d
    SET    effective_to = CURRENT_DATE - 1,
           is_current   = FALSE
    FROM   changed c
    WHERE  d.customer_id = c.customer_id
      AND  d.is_current
    RETURNING d.customer_id
)
-- Step 2: insert the new current versions (new keys + changed keys)
INSERT INTO dim_customer
    (customer_id, segment, region, effective_from, effective_to, is_current, row_hash)
SELECT c.customer_id, c.segment, c.region,
       CURRENT_DATE, DATE '9999-12-31', TRUE, c.row_hash
FROM   changed c;

COMMIT;

Step-by-step explanation.

The incoming CTE computes the change hash for every staged row using null-safe coalesce, so the comparison is robust to NULL attributes.
The changed CTE is the heart of the load: it keeps only keys that are brand-new (d.customer_id IS NULL) or whose stored current hash differs (d.row_hash <> i.row_hash). Unchanged keys are excluded, so re-running the load creates no phantom versions — the idempotency guarantee.
The expired CTE runs the UPDATE that stamps effective_to and clears is_current on the outgoing current rows, but only for keys in changed — new keys have no current row to expire, and the UPDATE's FROM changed join naturally skips them.
The final INSERT adds the new current version for every key in changed — both new keys and changed keys — with a fresh effective_from, the open 9999-12-31 sentinel, is_current = TRUE, and the computed hash.
Wrapping everything in BEGIN ... COMMIT makes the expire and insert atomic. Postgres evaluates the CTEs against a single snapshot, so the changed set is stable across both the UPDATE and the INSERT — no race between the two steps.

Output.

customer_id	before	after
1	Gold (current)	Gold (current) — untouched
2	Gold (current)	Gold (expired) + Platinum (current)
3	—	Silver (current) — inserted

Rule of thumb. In Postgres, the CTE form (incoming → changed → expired UPDATE → INSERT) makes the expire-then-insert explicit and runs both against one snapshot inside a transaction, giving an atomic, idempotent Type 2 load without needing MERGE.

SCD Type 2 interview question on writing the MERGE from scratch

A senior interviewer might say: "On this whiteboard, write the SCD Type 2 load for dim_customer (business key customer_id, tracked columns segment and region). Handle new customers, changed customers, and unchanged customers; keep exactly one current row per customer; make it idempotent so a re-run is a no-op; and tell me how a fact table joins to get the segment that was true at order time. Then tell me what changes if we run this on Snowflake versus Postgres."

Solution Using a hash-driven, transactional expire-then-insert with surrogate-key fact pinning

-- Portable two-step Type 2 load (Snowflake shown; Postgres note below)
BEGIN;

-- Step 1 — EXPIRE: supersede current rows whose tracked columns changed
MERGE INTO dim_customer t
USING (
    SELECT customer_id, segment, region,
           MD5(COALESCE(segment,'') || '|' || COALESCE(region,'')) AS row_hash
    FROM   stg_customer
) s
   ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHEN MATCHED AND t.row_hash <> s.row_hash THEN UPDATE SET
        t.effective_to = CURRENT_DATE() - 1,
        t.is_current   = FALSE;

-- Step 2 — INSERT: new current version for new + changed keys (surrogate key auto)
INSERT INTO dim_customer
    (customer_id, segment, region, effective_from, effective_to, is_current, row_hash)
SELECT s.customer_id, s.segment, s.region,
       CURRENT_DATE(), DATE '9999-12-31', TRUE,
       MD5(COALESCE(s.segment,'') || '|' || COALESCE(s.region,''))
FROM   stg_customer s
LEFT JOIN dim_customer t
       ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHERE  t.customer_id IS NULL
    OR t.row_hash <> MD5(COALESCE(s.segment,'') || '|' || COALESCE(s.region,''));

COMMIT;

-- Fact join to get the segment true at order time (surrogate pin, no date predicate):
SELECT d.segment, SUM(f.amount)
FROM   fact_sales f
JOIN   dim_customer d ON f.customer_key = d.customer_key
GROUP  BY d.segment;

Step-by-step trace.

Key	Incoming	Stored current	Step 1 (expire)	Step 2 (insert)
1 (unchanged)	Gold/East	Gold/East	hash equal → no-op	condition false → skip
2 (changed)	Plat/East	Gold/East	hash differs → expire	changed branch → insert
3 (new)	Silver/West	none	no current row → no-op	new-key branch → insert
re-run all	same	now current	all hashes equal → no-op	all conditions false → skip

The load classifies every staged key into one of three populations by comparing a hash of the tracked columns against the stored current version's hash. Unchanged keys match a current row with an equal hash and are touched by neither step, which is exactly what makes a re-run a no-op — the fourth trace row shows the second run doing nothing. Changed keys have their current version expired in Step 1 and a new version inserted in Step 2; new keys skip Step 1 (no current row to expire) and are inserted in Step 2. The surrogate key is generated automatically on insert, so each new version gets a unique fact-join target, and the fact query needs no date predicate because the fact already stored the surrogate key of the version current at order time.

Output:

Scenario	Result
new customer	one current version inserted
changed customer	old version expired, new version current
unchanged customer	no rows written
re-run	zero rows written (idempotent)
current rows per customer	exactly one

Why this works — concept by concept:

Expire-then-insert ordering — stamping effective_to/is_current=FALSE before inserting the new current row guarantees the one-current-per-key invariant is never transiently violated; doing it in a transaction makes the pair atomic.
Hash-driven change detection — comparing MD5 of the coalesced tracked columns cleanly separates unchanged/changed/new keys and is what makes the load idempotent: equal hash ⇒ no work.
Surrogate key — the auto-generated per-version key is the fact-join target, so a fact query reads the era-correct attribute with no date logic; this is the mechanism behind faithful historical reporting.
Effective dates + current flag — effective_from/effective_to enable as-of queries; is_current gives an O(1) latest filter; together they let one table serve both "what was true then" and "what is true now."
Cross-dialect portability — Snowflake/BigQuery/Databricks share the MERGE semantics (BigQuery differs only in TO_HEX(MD5()) and UUID surrogate generation); Postgres swaps the expire MERGE for a CTE UPDATE ... RETURNING. The pattern is engine-agnostic.
Cost — one indexed lookup per staged key on (customer_id, is_current), plus inserts proportional to the number of changed keys, not total keys. Storage grows with change frequency; for a slowly changing dimension that is small. O(changed) work per load, O(1) per fact join.

SQL
Topic — slowly-changing-data
SCD Type 2 MERGE and effective-date problems

Practice →

SQL Topic — sql SQL window and as-of join problems

Practice →

4. Type 3, Type 4 & Type 6 (hybrid)

The specialist types — one prior value, a split for churn, and the hybrid that does it all

The mental model in one line: Type 3 keeps a fixed number of prior values in extra columns (usually just one), Type 4 splits rapidly-changing attributes into a separate mini-dimension so the base dimension stays stable, and Type 6 fuses Type 1 + Type 2 + Type 3 into one row so a single query can read both the as-of value and the always-current value — each solves a specific shortcoming of the "big three" (0/1/2) and knowing when to reach for them is a senior dimensional modeling signal. These are the types that separate people who memorised "Type 2" from people who actually model dimensions for a living.

Type 3 — previous-value columns.

What it is. Add current_X and prior_X (sometimes original_X) columns. On change, shift the current value into prior_X and write the new value into current_X. One row per key; no row explosion.
When it's right. "Before/after" analysis where only one prior value matters — a company reorganisation where you want to compare each employee's new vs old division, or a product re-categorisation where the prior category is enough.
The limitation. Fixed, shallow history. Type 3 remembers one prior value; a second change overwrites the first prior. It cannot answer "as-of an arbitrary date."
The change date. Often paired with an X_changed_date column so you know when the shift happened.

Type 4 — history / mini-dimension split.

What it is. Move volatile, rapidly-changing attributes out of the base dimension into a separate mini-dimension, keyed by the combination of those attribute values. The base dimension stays small and stable; facts reference both keys.
When it's right. Attributes that churn far faster than the rest of the dimension — a customer's age band, income band, credit-score bucket, or behavioral segment that updates monthly. Type-2-ing them would explode the base dimension with a new version every month.
The mini-dimension. Contains one row per distinct combination of the volatile attributes (e.g. every (age_band, income_band, score_band) tuple), not one per customer. Facts carry both customer_key and demographics_key.
The payoff. The base dimension's version count reflects only slow changes; the churn is absorbed by the small, bounded mini-dimension.

Type 6 — the 1+2+3 hybrid.

What it is. One dimension row that carries Type 2 versioning (effective dates, current flag, surrogate key), a Type 1 overwritten "current" column mirrored across all versions, and optionally a Type 3 "prior" column. The name is "6" because 1+2+3 = 6 (and it's a Type 6 in Kimball's numbering).
When it's right. When you need both the as-of value (segment at purchase time) and the current value (their segment now) frequently, and you don't want every "current" query to filter across versions.
How it loads. The Type 2 expire-and-insert runs as usual, but the load also overwrites the current_X column on every historical version of the key so it always reflects the latest value.
The payoff. SELECT current_segment needs no is_current filter or as-of predicate; SELECT segment WHERE as-of still works. History and fast-current in one table.

Worked example — Type 3 alter + shift-on-change update

Detailed explanation. Type 3 adds prior-value columns and, on each change, shifts the current value into the prior column before writing the new value. Walk through the ALTER that adds the columns and the UPDATE that performs the shift, using a product re-categorisation.

Schema. Add prior_category and category_changed_date.
Shift. On change: prior_category = category; category = new; category_changed_date = today.
Guard. Only shift when the category actually changed.

Question. Migrate dim_product to Type 3 on category and write the shift-on-change update.

Input.

product_id	category (current)	category (incoming)
10	Electronics	Electronics (unchanged)
11	Toys	Games (changed)

Code.

-- 1. Add the Type 3 prior-value columns
ALTER TABLE dim_product ADD COLUMN prior_category        TEXT;
ALTER TABLE dim_product ADD COLUMN category_changed_date DATE;

-- 2. Shift-on-change update from staging (only when category actually changed)
UPDATE dim_product d
SET    prior_category        = d.category,       -- shift current -> prior
       category              = s.category,       -- write the new value
       category_changed_date = CURRENT_DATE
FROM   stg_product s
WHERE  d.product_id = s.product_id
  AND  d.category IS DISTINCT FROM s.category;   -- null-safe change guard

-- 3. Read the before/after comparison
SELECT product_id, prior_category AS was, category AS now, category_changed_date
FROM   dim_product
WHERE  prior_category IS NOT NULL;

Step-by-step explanation.

The ALTER adds prior_category (holds the immediately previous value) and category_changed_date (records when the shift happened). Type 3 keeps one row per product, so no surrogate-key or effective-date machinery is needed.
The UPDATE's key move is the ordered assignment: prior_category = d.category captures the old value before category = s.category overwrites it. In SQL, all right-hand sides are evaluated against the old row, so this correctly shifts old→prior and new→current in a single statement.
The d.category IS DISTINCT FROM s.category guard ensures the shift fires only on a genuine change. Without it, an unchanged product would overwrite prior_category with the current value, destroying the one prior value Type 3 promises to keep.
IS DISTINCT FROM is null-safe, so a category going from NULL to 'Games' (or vice versa) is correctly detected as a change.
The read query surfaces the before/after pairs, which is exactly the "reorganisation impact" report Type 3 exists to serve — but note it can show only the single most recent prior value; a second re-categorisation overwrites the first.

Output.

product_id	was (prior)	now (current)	changed_date
10	(null)	Electronics	(null) — unchanged
11	Toys	Games	2026-08-07

Rule of thumb. Type 3's shift-on-change is a single ordered UPDATE (prior = current, current = new) guarded by IS DISTINCT FROM. It buys one prior value with zero row growth — but a second change overwrites the first prior, so use it only when one level of history is genuinely enough.

Worked example — Type 4 mini-dimension for churn-heavy attributes

Detailed explanation. Type 4 splits volatile attributes into a mini-dimension so the base dimension doesn't explode. Build a dim_customer base plus a dim_customer_demographics mini-dimension for (age_band, income_band, score_band), and show how a fact references both.

Mini-dimension. One row per distinct combination of the volatile bands — a small, bounded lookup.
Base dimension. Stays Type 2 on slow attributes only.
Fact. Carries both customer_key and demographics_key, captured at event time.

Question. Design the Type 4 split and show the fact insert that captures both keys at purchase time.

Input.

Volatile combo	age_band	income_band	score_band
combo A	25-34	50-75k	700-749
combo B	25-34	75-100k	750-799

Code.

-- 1. Mini-dimension: one row per distinct combination of volatile bands
CREATE TABLE dim_customer_demographics (
    demographics_key BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    age_band         TEXT NOT NULL,
    income_band      TEXT NOT NULL,
    score_band       TEXT NOT NULL,
    UNIQUE (age_band, income_band, score_band)   -- dedupe combinations
);

-- 2. Base dimension keeps only slow attributes (Type 2 as usual)
--    dim_customer(customer_key, customer_id, name, segment, effective_from, ...)

-- 3. At event time, resolve BOTH keys and store them on the fact
INSERT INTO fact_sales (sale_date, amount, customer_key, demographics_key)
SELECT :sale_date, :amount,
       (SELECT customer_key FROM dim_customer
         WHERE customer_id = :customer_id AND is_current),
       (SELECT demographics_key FROM dim_customer_demographics
         WHERE age_band = :age_band
           AND income_band = :income_band
           AND score_band = :score_band);

Step-by-step explanation.

The mini-dimension holds one row per distinct combination of the volatile bands, not one per customer. If ten thousand customers share the (25-34, 50-75k, 700-749) profile, they all point at the same demographics_key. This bounds the mini-dimension to the number of possible band combinations — small and stable.
The base dim_customer keeps only the slowly changing attributes (name, segment) and stays Type 2. Because the churny bands are no longer in it, a monthly band change does not create a new base-dimension version — the whole point of Type 4.
At event time the fact captures both keys: the current customer_key (the era-correct base version) and the demographics_key for the band combination the customer was in at that moment. This is how the fact remembers "which demographic profile at purchase time."
When a customer's band changes, no dimension row is expired or inserted — the next fact simply resolves a different demographics_key. The history lives in the sequence of demographics_key values across the customer's facts.
Analysts slice by demographics through the fact→mini-dimension join, and by slow attributes through the fact→base-dimension join, keeping each dimension's cardinality appropriate to its change rate.

Output.

Table	Row count driver	Change behavior
dim_customer (base)	slow attributes only	Type 2 versions on slow changes
dim_customer_demographics	distinct band combos	fixed small set; no per-customer growth
fact_sales	one per event	stores both keys at event time

Rule of thumb. When an attribute churns much faster than the rest of the dimension, split it into a Type 4 mini-dimension keyed by the combination of volatile values. The base dimension stays small; the fact captures the demographic profile at event time via a second key.

Worked example — the Type 6 combined schema and load

Detailed explanation. Type 6 fuses Type 2 versioning with a Type 1 "current" mirror column (and optionally a Type 3 prior column) in one row. Build the schema and the load that both versions the row and overwrites the current-mirror across all versions.

Schema. Type 2 columns (surrogate, effective dates, is_current) + current_segment (Type 1 mirror) + optionally prior_segment (Type 3).
Load part A. Standard Type 2 expire-and-insert on segment.
Load part B. Overwrite current_segment on every version of the changed key to the new value.

Question. Implement the Type 6 load so segment is versioned but current_segment is always the latest across all rows of a customer.

Input.

customer_key	customer_id	segment	current_segment	is_current
601	7	Gold	Gold	(before change) TRUE

Code.

-- Type 6 schema (versioned segment + current mirror + prior column)
-- dim_customer(customer_key PK, customer_id, segment, current_segment,
--              prior_segment, effective_from, effective_to, is_current, row_hash)

BEGIN;

-- Part A: standard Type 2 — expire old, insert new version for changed keys
UPDATE dim_customer d
SET    effective_to = CURRENT_DATE - 1, is_current = FALSE
FROM   stg_customer s
WHERE  d.customer_id = s.customer_id AND d.is_current
  AND  d.segment IS DISTINCT FROM s.segment;

INSERT INTO dim_customer
    (customer_id, segment, current_segment, prior_segment,
     effective_from, effective_to, is_current, row_hash)
SELECT s.customer_id, s.segment, s.segment,
       old.segment,                          -- Type 3 prior value
       CURRENT_DATE, DATE '9999-12-31', TRUE,
       md5(coalesce(s.segment,''))
FROM   stg_customer s
JOIN   dim_customer old
       ON old.customer_id = s.customer_id AND old.effective_to = CURRENT_DATE - 1;

-- Part B: Type 1 mirror — overwrite current_segment on ALL versions of changed keys
UPDATE dim_customer d
SET    current_segment = s.segment
FROM   stg_customer s
WHERE  d.customer_id = s.customer_id
  AND  d.current_segment IS DISTINCT FROM s.segment;

COMMIT;

Step-by-step explanation.

Part A is the ordinary Type 2 load: expire the current version whose segment changed, then insert a new version. The insert also sets prior_segment from the just-expired version (effective_to = CURRENT_DATE - 1), giving the Type 3 one-prior-value behavior for free.
current_segment on the new row is set to the new segment (s.segment) — it starts life already current.
Part B is what makes it Type 6: it overwrites current_segment on every row of the changed customer (old expired versions included) to the latest segment. After this, all historical versions of customer 7 carry current_segment = <latest>, while their segment column still holds the era-correct value.
The IS DISTINCT FROM guard on Part B keeps it idempotent — a re-run finds current_segment already equal to the latest and updates nothing.
The result: segment answers as-of queries (versioned), current_segment answers "what is it now" from any version with no is_current filter, and prior_segment answers "what was the immediately previous value" — three temporal questions from one row.

Output.

customer_key	segment (as-of)	current_segment (mirror)	prior_segment
601 (expired)	Gold	Platinum	(null)
602 (current)	Platinum	Platinum	Gold

Rule of thumb. Type 6 = Type 2 load + a Part B that overwrites the current_* mirror on all versions of the changed key. It costs one extra UPDATE per load and buys as-of history plus a filter-free "current" column in the same table.

SCD interview question on choosing Type 6 over Type 2

A senior interviewer might ask: "Your dim_customer is Type 2 on segment, and analysts constantly write ... WHERE is_current joins just to get each customer's current segment for operational dashboards, while data scientists need the segment at purchase time for churn models. The is_current join is showing up as a hot path in query profiles. Would you switch to Type 6? Explain the trade-off, the load change, and what you'd tell the analysts to write instead."

Solution Using a Type 6 current-mirror to remove the is_current hot path

-- Add a Type 1 mirror to the existing Type 2 dimension → Type 6
ALTER TABLE dim_customer ADD COLUMN current_segment TEXT;

-- Backfill the mirror: every version gets its business key's latest segment
UPDATE dim_customer d
SET    current_segment = latest.segment
FROM  (SELECT customer_id, segment
       FROM   dim_customer
       WHERE  is_current) latest
WHERE  d.customer_id = latest.customer_id;

-- Ongoing load: Part B overwrites current_segment on all versions of changed keys
-- (added to the existing Type 2 expire-and-insert load, inside the same transaction)

-- Analysts (current segment) — no is_current join, no as-of predicate:
SELECT customer_id, current_segment FROM dim_customer WHERE is_current;
-- ...or from a fact join, current_segment is identical on every version reached.

-- Data scientists (segment at purchase time) — unchanged Type 2 surrogate pin:
SELECT d.segment, COUNT(*) FROM fact_sales f
JOIN   dim_customer d ON f.customer_key = d.customer_key
GROUP  BY d.segment;

Step-by-step trace.

Consumer	Before (Type 2)	After (Type 6)
analysts (current)	join + `WHERE is_current` filter	read `current_segment` column directly
data scientists (as-of)	surrogate-key pin	surrogate-key pin (unchanged)
load	expire + insert	expire + insert + mirror overwrite
storage	segment only	segment + current_segment column
hot path	`is_current` filter scans	eliminated

Switching to Type 6 is the right call here because the two consumer groups want genuinely different temporal semantics from the same attribute, and Type 2 alone forces the "current" group to pay an is_current filter (or a self-join to the current row) on every query — which the profiler flagged as hot. Adding a Type 1 current_segment mirror lets the current-value queries read a single column with no filtering, while the as-of queries keep using the untouched surrogate-key pin. The cost is one extra column and one extra UPDATE in the load (Part B, overwriting the mirror on all versions of changed keys), which is cheap because it runs only for keys that actually changed.

Output:

Query class	Type 2 cost	Type 6 cost
current segment	filter/self-join per query	single-column read
as-of segment	surrogate pin	surrogate pin (same)
load per change	expire + insert	expire + insert + 1 mirror UPDATE
extra storage	—	1 text column

Why this works — concept by concept:

Type 6 = 1 + 2 + 3 — a single row carries a versioned attribute (Type 2), an always-current mirror (Type 1), and optionally a prior value (Type 3), so one table serves as-of and current and before/after without compromise.
Current-mirror overwrite — Part B of the load overwrites current_segment on all versions of a changed key, so the "current" value is readable from any version with no is_current predicate — removing the hot filter.
Surrogate-key pin preserved — the as-of consumers are untouched because their correctness comes from the fact storing the version's surrogate key, which Type 6 does not change.
Idempotent mirror update — guarding Part B with IS DISTINCT FROM means re-runs overwrite nothing, keeping the added load step re-runnable.
Cost — one extra column and one extra UPDATE per load, scoped to changed keys only. Query-side, the current-segment path drops from a filtered scan/self-join to a single-column read. Net: a small, bounded write cost buys a large read-path win on the hot query.

SQL
Topic — dimensional-modeling
Mini-dimension and hybrid SCD design problems

Practice →

Design Topic — design Data-warehouse dimension design problems

Practice →

5. Production SCD: dbt snapshots, performance & pitfalls

Where SCDs meet reality — let the tool run the MERGE, then survive late arrivals, deletes, and re-runs

The mental model in one line: in production you rarely hand-write the Type 2 MERGE — you declare a dbt snapshot (or an equivalent framework) that runs the expire-and-insert for you — but you still own the hard parts: late-arriving dimension rows, out-of-order changes, source deletes, idempotency under retries, and the MERGE/clustering cost of a data warehouse history table that only ever grows. This section is the "make it real" section: the mechanics that turn a correct SCD design into a pipeline you can run every hour without corrupting history.

dbt snapshots — the declarative Type 2.

What they are. A dbt snapshot is a config that materialises a Type 2 history of a source query. dbt manages the surrogate-equivalent, dbt_valid_from, dbt_valid_to, and dbt_scd_id for you and runs the MERGE on every dbt snapshot invocation.
timestamp strategy. You point dbt at an updated_at column; a row is considered changed when its updated_at advances. Cheapest and most reliable when a trustworthy updated_at exists.
check strategy. You list check_cols (or all); dbt hashes those columns and creates a new version when the hash changes. Use when there is no reliable updated_at.
invalidate_hard_deletes. Opt-in flag that closes out (expires) rows that disappeared from the source — dbt's built-in delete handling.

Late-arriving and out-of-order changes.

Late-arriving dimension. A fact arrives for a business key the dimension hasn't loaded yet. Standard fix: insert a placeholder/inferred dimension row (surrogate key assigned, attributes NULL/"Unknown") so the fact has something to point at, then let the real attributes backfill when they arrive.
Out-of-order changes. A change with an earlier effective date arrives after a later one. Correct handling re-computes the affected validity windows so the intervals stay contiguous and non-overlapping — harder than the happy path and a favorite senior probe.
Effective-date source. Prefer an event/business timestamp for effective_from over the load time, so history reflects when the change happened in the world, not when your pipeline saw it.

Delete handling in SCD Type 2.

Soft delete (best). Source uses deleted_at; the SCD treats it as an attribute change (a new version with a is_deleted/deleted_at flag). History preserved.
Hard delete detection. Source physically removes the row. Detect via anti-join (keys in the dimension's current set but absent from the source) and expire the current version (optionally inserting a tombstone version with is_deleted = TRUE). dbt's invalidate_hard_deletes automates this.
Never physically delete the historical versions — that destroys the very history the table exists to keep.

Performance and idempotency.

Idempotency. A re-run must produce zero new versions. Hash-based change detection + expire-then-insert-in-a-transaction is the recipe; the load must be a pure function of (current state, source snapshot).
MERGE cost. The expire step scans the current set; keep is_current (and the business key) in the clustering/partition key so the current-row lookup is cheap.
Clustering / partitioning. Snowflake cluster by (business_key); BigQuery partition by effective_from and cluster by business key; Databricks ZORDER by (business_key, is_current). As-of range predicates benefit from clustering on the validity columns.
Pruning history. Old versions can be moved to cold storage or partitioned by year; never mixed into the hot current set.

Worked example — a dbt snapshot config (check and timestamp)

Detailed explanation. A dbt snapshot declares the Type 2 history of a source. Show both strategies for a customers source, with invalidate_hard_deletes for delete handling, and explain what dbt generates.

timestamp strategy. Uses updated_at; simplest when the source maintains it.
check strategy. Uses check_cols; hashes columns to detect change.
invalidate_hard_deletes: true. Expires rows that vanish from the source.

Question. Write both a timestamp-strategy and a check-strategy dbt snapshot for customers, and state which columns dbt manages.

Input.

Strategy	Change signal	Use when
timestamp	`updated_at` advances	reliable updated_at exists
check	hash of `check_cols` changes	no reliable updated_at

Code.

-- snapshots/customers_snapshot.sql — TIMESTAMP strategy
{% snapshot customers_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='timestamp',
    updated_at='updated_at',
    invalidate_hard_deletes=True
  )
}}
select customer_id, name, segment, region, updated_at
from {{ source('crm', 'customers') }}
{% endsnapshot %}

-- snapshots/customers_snapshot.sql — CHECK strategy (no reliable updated_at)
{% snapshot customers_snapshot %}
{{
  config(
    target_schema='snapshots',
    unique_key='customer_id',
    strategy='check',
    check_cols=['segment', 'region'],
    invalidate_hard_deletes=True
  )
}}
select customer_id, name, segment, region
from {{ source('crm', 'customers') }}
{% endsnapshot %}

Step-by-step explanation.

unique_key='customer_id' is the business key — dbt uses it to group versions, exactly like a hand-rolled Type 2. dbt generates dbt_scd_id (a surrogate/hash id), dbt_valid_from, dbt_valid_to, and dbt_updated_at for you.
The timestamp strategy treats a row as changed when its updated_at is newer than the stored version's. It's the cheapest and most reliable strategy if the source maintains a trustworthy updated_at — one indexed comparison per key.
The check strategy hashes the listed check_cols and creates a new version when the hash changes. Use it when updated_at is missing or untrustworthy; check_cols='all' tracks every column. It's the framework equivalent of the row_hash you'd hand-write.
invalidate_hard_deletes=True tells dbt to expire (close out dbt_valid_to) rows that were present in a prior run but are absent from the current source — the built-in hard-delete handling. Without it, deleted source rows stay "current" forever.
Running dbt snapshot executes the expire-and-insert MERGE on each invocation; because it's hash/timestamp-driven, re-running with unchanged source is a no-op — dbt snapshots are idempotent by construction.

Output.

dbt-managed column	Meaning
dbt_scd_id	per-version surrogate/hash id
dbt_valid_from	version validity start
dbt_valid_to	version validity end (NULL = current)
dbt_updated_at	source change timestamp

Rule of thumb. Prefer dbt's timestamp strategy when a trustworthy updated_at exists, fall back to check (hash) when it doesn't, and set invalidate_hard_deletes=True so vanished source rows get expired. dbt manages the effective dates and surrogate id — you just declare the source and the change signal.

Worked example — handling late-arriving and out-of-order changes

Detailed explanation. Two related pitfalls: a fact arriving before its dimension (late-arriving dimension), and a change arriving with an effective date earlier than an already-loaded change (out-of-order). Show the inferred-member insert and the interval re-stitch.

Late-arriving dimension. Insert a placeholder row so the fact can join; backfill attributes later.
Out-of-order change. Insert the late version in the middle and re-stitch surrounding effective_to/effective_from so windows stay contiguous.

Question. Handle a fact for an unknown customer, then insert a change dated before the current version and fix the intervals.

Input.

Existing versions (customer 9)	segment	effective_from	effective_to
v1	Silver	2026-01-01	9999-12-31
late change (arrives now)	Gold	2025-06-01	—

Code.

-- A. Late-arriving dimension: insert an inferred placeholder so facts can join
INSERT INTO dim_customer (customer_id, segment, region,
                          effective_from, effective_to, is_current, row_hash)
SELECT f.customer_id, 'Unknown', 'Unknown',
       DATE '1900-01-01', DATE '9999-12-31', TRUE, md5('unknown')
FROM   fact_sales f
LEFT JOIN dim_customer d
       ON d.customer_id = f.customer_id AND d.is_current
WHERE  d.customer_id IS NULL;              -- fact references a key not yet in the dim

-- B. Out-of-order change dated 2025-06-01 (before the existing v1 start 2026-01-01):
--    1) shorten/adjust the neighbour, 2) insert the late version with a bounded window
BEGIN;

-- Insert the late 'Gold' version, bounded to end when the next-known version begins
INSERT INTO dim_customer (customer_id, segment, region,
                          effective_from, effective_to, is_current, row_hash)
VALUES (9, 'Gold', 'East', DATE '2025-06-01', DATE '2025-12-31', FALSE, md5('Gold|East'));

-- Re-stitch: the previously-open v1 keeps its window; ensure no overlap/gap
UPDATE dim_customer
SET    effective_from = DATE '2026-01-01'     -- unchanged here; shown for the general case
WHERE  customer_id = 9 AND segment = 'Silver';

COMMIT;

Step-by-step explanation.

Section A handles the late-arriving dimension: a fact references a customer_id the dimension hasn't seen. The anti-join (LEFT JOIN ... WHERE d.customer_id IS NULL) finds those keys and inserts an inferred placeholder row with "Unknown" attributes and a wide-open window, so the fact has a valid surrogate key to join. When the real attributes arrive, a normal Type 2 change updates the placeholder into a proper version.
The placeholder's effective_from = 1900-01-01 guarantees any historical fact date falls inside its window, so no fact is ever orphaned while waiting for the real dimension row.
Section B handles the out-of-order change: a 2025-06-01 "Gold" change arrives after v1 (Silver, starting 2026-01-01) was already loaded. Because the late change predates the existing version, it must be inserted as a closed interval (2025-06-01 .. 2025-12-31) that ends where the next known version begins — not as a new current row.
The re-stitch step ensures the intervals remain contiguous and non-overlapping: after insertion the timeline reads Gold (2025-06-01 .. 2025-12-31) then Silver (2026-01-01 .. open). Overlapping windows would make an as-of query match two versions; gaps would make it match none.
This ordering logic is why production SCD prefers frameworks or careful window re-computation: naive "expire current, insert new" assumes changes arrive in order, which real sources violate.

Output.

customer_id	segment	effective_from	effective_to	is_current
9	Gold	2025-06-01	2025-12-31	FALSE
9	Silver	2026-01-01	9999-12-31	TRUE

Rule of thumb. For late-arriving dimensions, insert an "Unknown" placeholder with a wide window so facts never orphan; for out-of-order changes, insert a bounded interval and re-stitch neighbours so validity windows stay contiguous and non-overlapping.

Worked example — making a Type 2 load idempotent under retries

Detailed explanation. A production loader will be retried — the orchestrator kills a task mid-run, or a downstream failure triggers a replay. The load must be safe to run twice. Show the guardrails: change-hash detection, expire-then-insert in one transaction, and a duplicate-version guard.

Hash detection. Only changed keys write anything.
Transaction. Expire + insert commit atomically or not at all.
Duplicate guard. A unique constraint on (business_key, is_current) catches accidental double-current.

Question. Make the Type 2 load re-runnable so a retry after a partial failure produces no duplicate versions.

Input.

Scenario	Desired outcome
full success then re-run	zero new versions
crash after expire, before insert	rollback; retry re-does both
crash after commit, then retry	hash equal; no-op

Code.

-- Guard: at most one current version per business key
ALTER TABLE dim_customer
  ADD CONSTRAINT uq_one_current UNIQUE (customer_id, is_current)
  DEFERRABLE INITIALLY DEFERRED;   -- deferred so expire+insert can swap within a txn

-- Idempotent load: hash-driven, single transaction
BEGIN;

WITH incoming AS (
    SELECT customer_id, segment, region,
           md5(coalesce(segment,'') || '|' || coalesce(region,'')) AS row_hash
    FROM   stg_customer
),
changed AS (
    SELECT i.* FROM incoming i
    LEFT JOIN dim_customer d ON d.customer_id = i.customer_id AND d.is_current
    WHERE  d.customer_id IS NULL OR d.row_hash <> i.row_hash
)
UPDATE dim_customer d
SET    effective_to = CURRENT_DATE - 1, is_current = FALSE
FROM   changed c
WHERE  d.customer_id = c.customer_id AND d.is_current;

INSERT INTO dim_customer (customer_id, segment, region,
                          effective_from, effective_to, is_current, row_hash)
SELECT c.customer_id, c.segment, c.region,
       CURRENT_DATE, DATE '9999-12-31', TRUE, c.row_hash
FROM  (SELECT i.* FROM (
          SELECT customer_id, segment, region,
                 md5(coalesce(segment,'') || '|' || coalesce(region,'')) AS row_hash
          FROM stg_customer) i
       LEFT JOIN dim_customer d ON d.customer_id = i.customer_id AND d.is_current
       WHERE d.customer_id IS NULL OR d.row_hash <> i.row_hash) c;

COMMIT;

Step-by-step explanation.

The DEFERRABLE INITIALLY DEFERRED unique constraint on (customer_id, is_current) enforces "at most one current row per customer," but defers the check to commit time so the expire (sets old to FALSE) and insert (adds a new TRUE) can both happen inside the transaction without transiently violating the constraint.
The changed CTE (hash-driven) is the idempotency core: on a clean re-run, every incoming hash equals the stored current hash, so changed is empty, the UPDATE touches nothing, and the INSERT selects nothing — a true no-op.
Wrapping expire + insert in BEGIN ... COMMIT handles the mid-run crash: if the task dies after the UPDATE but before the INSERT, the transaction never commits and Postgres rolls back the expire, leaving the dimension exactly as it was. The retry then re-does both steps cleanly.
A crash after commit is handled by the hash check on retry: the state is already correct, so the re-run finds nothing changed and writes nothing.
The duplicate-version guard is the safety net for logic bugs: if a coding error ever tried to insert a second current row for a customer, the deferred unique constraint would abort the transaction at commit rather than silently corrupting the one-current invariant.

Output.

Scenario	Rows written on retry	Invariant held
success then re-run	0	yes
crash before commit	rollback, then normal load	yes
crash after commit	0 (hash equal)	yes
buggy double-current	transaction aborts	yes (constraint)

Rule of thumb. Idempotent Type 2 = hash-driven change set + expire-and-insert in one transaction + a deferred UNIQUE(business_key, is_current) guard. Together they make the load a pure function of (current state, source), safe to retry any number of times.

SCD interview question on idempotent loads with delete handling

A senior interviewer might ask: "Design a Type 2 load for dim_customer that runs hourly and must be idempotent (a retry writes nothing new), must handle hard deletes in the source (a customer removed upstream should be expired, not left current forever), and must keep exactly one current row per customer. Walk me through change detection, the delete handling, the transaction boundaries, and how you'd keep the hourly MERGE cheap as the history table grows to hundreds of millions of rows."

Solution Using hash detection, an anti-join delete-expiry, a transaction, and clustering

BEGIN;

-- 1. Change set: new or hash-changed keys (idempotent core)
WITH incoming AS (
    SELECT customer_id, segment, region,
           md5(coalesce(segment,'')||'|'||coalesce(region,'')) AS row_hash
    FROM   stg_customer
),
changed AS (
    SELECT i.* FROM incoming i
    LEFT JOIN dim_customer d ON d.customer_id = i.customer_id AND d.is_current
    WHERE  d.customer_id IS NULL OR d.row_hash <> i.row_hash
)
-- 2a. Expire changed current versions
UPDATE dim_customer d
SET    effective_to = CURRENT_DATE - 1, is_current = FALSE
FROM   changed c
WHERE  d.customer_id = c.customer_id AND d.is_current;

-- 2b. Insert new versions for changed + new keys
INSERT INTO dim_customer (customer_id, segment, region,
                          effective_from, effective_to, is_current, is_deleted, row_hash)
SELECT c.customer_id, c.segment, c.region,
       CURRENT_DATE, DATE '9999-12-31', TRUE, FALSE, c.row_hash
FROM   changed c;

-- 3. Hard-delete handling: keys current in the dim but ABSENT from source → expire + tombstone
WITH deleted AS (
    SELECT d.customer_id
    FROM   dim_customer d
    LEFT JOIN stg_customer s ON s.customer_id = d.customer_id
    WHERE  d.is_current AND s.customer_id IS NULL
)
UPDATE dim_customer d
SET    effective_to = CURRENT_DATE - 1, is_current = FALSE
FROM   deleted x WHERE d.customer_id = x.customer_id AND d.is_current;

INSERT INTO dim_customer (customer_id, segment, region,
                          effective_from, effective_to, is_current, is_deleted, row_hash)
SELECT d.customer_id, d.segment, d.region,
       CURRENT_DATE, DATE '9999-12-31', TRUE, TRUE, d.row_hash
FROM   dim_customer d
JOIN  (SELECT customer_id FROM dim_customer
       WHERE effective_to = CURRENT_DATE - 1 AND NOT is_current) x
  ON   d.customer_id = x.customer_id
WHERE  d.effective_to = CURRENT_DATE - 1;

COMMIT;

-- Cluster/partition for cheap current-row lookups and as-of range scans:
-- Snowflake:  ALTER TABLE dim_customer CLUSTER BY (customer_id);
-- BigQuery:   PARTITION BY effective_from CLUSTER BY customer_id;
-- Databricks: OPTIMIZE dim_customer ZORDER BY (customer_id, is_current);

Step-by-step trace.

Concern	Mechanism	Effect
idempotency	`changed` = hash-diff CTE	re-run with same source ⇒ empty set ⇒ no writes
new/changed keys	expire (2a) + insert (2b)	one new current version each
hard deletes	anti-join `deleted` (step 3)	expire current + insert `is_deleted=TRUE` tombstone
atomicity	single `BEGIN..COMMIT`	partial failure rolls back cleanly
one-current invariant	deferred UNIQUE + logic	never two current rows per key
MERGE cost at scale	cluster/partition by business key	current-row lookup stays cheap

The load runs three logical phases inside one transaction. Phase 1–2 is the ordinary hash-driven Type 2 (new and changed keys), which is idempotent because an unchanged source produces an empty changed set. Phase 3 is delete handling: an anti-join finds keys that are still is_current in the dimension but no longer present in the source, expires them, and inserts a tombstone version flagged is_deleted = TRUE — so downstream can tell "this customer was removed on this date" instead of the row silently staying current forever. Everything commits atomically, and clustering on the business key keeps the per-hour current-row lookups cheap even as the history grows to hundreds of millions of rows because the engine prunes to the relevant micro-partitions/files.

Output:

Source event	Dimension outcome
unchanged customer	no write (idempotent)
attribute change	old expired, new current
new customer	new current version
customer deleted upstream	current expired + `is_deleted` tombstone
retry of any run	zero additional rows

Why this works — concept by concept:

Hash-based idempotency — the changed CTE compares a hash of tracked columns to the stored current hash, so a retry with identical source yields an empty change set and writes nothing; the load is a pure function of (state, source).
Anti-join delete detection — comparing the dimension's current keys against the source finds hard deletes; expiring them plus a is_deleted tombstone preserves history and signals the removal, which naive loads miss entirely.
Transactional expire-then-insert — one BEGIN..COMMIT makes every phase atomic, so a mid-run crash rolls back and the retry starts clean; there is never a half-applied version state.
Deferred one-current constraint — UNIQUE(customer_id, is_current) deferred to commit lets expire and insert swap the current row within the transaction while still guaranteeing exactly one current version per key at commit.
Clustering / partitioning — keeping the business key (and is_current) in the clustering/partition key means the hourly current-row lookups and as-of range scans prune to a small slice, keeping MERGE cost roughly flat as the data warehouse history table grows.
Cost — O(changed + deleted) writes per run, not O(total keys); one indexed/clustered current-row lookup per staged key; storage grows with change + delete frequency. At hundreds of millions of history rows the hot path stays cheap because clustering prunes the scan to current-era micro-partitions.

ETL
Topic — etl
ETL snapshot and incremental-load problems

Practice →

SQL
Topic — slowly-changing-data
Idempotent SCD and delete-handling problems

Practice →

Cheat sheet — SCD recipes

The type map in one line. Type 0 = retain-original (never change); Type 1 = overwrite (no history); Type 2 = add a new versioned row (full history via effective dates + current flag + surrogate key); Type 3 = add a prior-value column (one level of history); Type 4 = split volatile attributes into a mini-dimension; Type 6 = 1+2+3 hybrid (versioned + current mirror + prior). Assign a type per attribute, never per table.
Type 2 MERGE template. Two steps, always: (1) expire — set effective_to = change_date - 1, is_current = FALSE on the current version whose tracked-column hash changed; (2) insert — new version with effective_from = change_date, effective_to = '9999-12-31', is_current = TRUE, fresh surrogate key. Wrap both in one transaction; drive with a row_hash so re-runs are no-ops.
As-of query. Use a half-open interval: WHERE d >= effective_from AND d < effective_to. For "latest" use WHERE is_current. For fact joins, prefer the surrogate-key pin (fact.customer_key = dim.customer_key) — no date predicate needed because the version was pinned at load.
Effective-date rules. effective_from = the business/event timestamp of the change (not load time); effective_to = 9999-12-31 (or NULL) for the open version. Keep windows contiguous and non-overlapping so an as-of date matches exactly one version.
Surrogate vs business key. Surrogate key = unique per version, warehouse-generated (IDENTITY/sequence/UUID), the fact-join target; business/natural key = stable per entity, used by the ETL to find the row to expire. Facts must join on the surrogate key — joining on the business key collapses history.
is_current flag. Denormalised boolean, exactly one TRUE per business key; enforce with UNIQUE(business_key, is_current) DEFERRABLE. Redundant with effective_to = '9999-12-31' but far cheaper to filter and clearer to read.
Change detection (hash-diff). Hash the tracked columns with COALESCE for null-safety (MD5 / TO_HEX(MD5()) in BigQuery). Equal hash ⇒ no change ⇒ no new version. This is what makes the load idempotent and keeps the version count honest.
Cross-dialect MERGE. Snowflake/BigQuery/Databricks share MERGE semantics; BigQuery differs only in TO_HEX(MD5()) and UUID/ROW_NUMBER surrogate generation (no native sequences). Postgres 14+ has MERGE but the idiomatic Type 2 load is a CTE (incoming → changed → UPDATE...RETURNING → INSERT).
dbt snapshots. Declarative Type 2: strategy='timestamp' (uses updated_at, cheapest when trustworthy) or strategy='check' (hashes check_cols, use when no reliable updated_at); set invalidate_hard_deletes=True for delete handling. dbt manages dbt_scd_id, dbt_valid_from, dbt_valid_to.
Late-arriving / out-of-order. Late-arriving dimension: insert an "Unknown" placeholder with a wide window so facts never orphan; backfill later. Out-of-order change: insert a bounded interval and re-stitch neighbours so windows stay contiguous. Use the event timestamp for effective_from, not load time.
Delete handling. Soft delete (source deleted_at) → treat as a normal attribute change with an is_deleted flag. Hard delete → anti-join (current keys absent from source), expire the current version, optionally insert an is_deleted = TRUE tombstone. Never physically delete historical versions.
Performance / clustering. Snowflake CLUSTER BY (business_key); BigQuery PARTITION BY effective_from CLUSTER BY business_key; Databricks ZORDER BY (business_key, is_current). Keeps current-row lookups and as-of range scans pruned as the history table grows. Load cost is O(changed + deleted), not O(total).
Idempotency recipe. Hash-driven change set + expire-then-insert in one transaction + deferred UNIQUE(business_key, is_current). Makes the load a pure function of (current state, source snapshot) — safe to retry after any partial failure.

Frequently asked questions

What are slowly changing dimensions?

Slowly changing dimensions are descriptive warehouse tables — customers, products, stores, employees — whose attributes change occasionally over time, and the term also names the set of standard techniques for storing those changes. Facts are immutable measurements (an order, a click) that are append-only; dimensions are the mutable context those facts point at, and the "slowly" reflects that attributes like a customer's segment or a product's category change now and then, not on every event. The core problem an SCD solves is deciding whether and how to remember a changed attribute: overwrite it (lose history), version it (keep history), or freeze it (never change). Because facts join to dimensions, the storage choice determines whether historical reports stay faithful to the state of the world at the time each fact happened.

What is the difference between SCD Type 1 and SCD Type 2?

SCD Type 1 overwrites the changed attribute in place, keeping exactly one row per business key and discarding all history — it is right for corrections (fixing a typo) and attributes you would never report on historically. SCD Type 2 instead inserts a new versioned row on each change, expiring the prior version with effective dates and a current flag, so every historical state remains queryable. The critical consequence is that Type 1 retroactively restates the past: because every fact joins to the single dimension row, overwriting an attribute silently rewrites every historical report that reads it (a relocated customer's old sales re-attribute to the new region). Type 2 avoids this by pinning each fact to the version that was current at event time via a surrogate key. A useful rule: any attribute you might ever GROUP BY on a historical fact must be Type 2, not Type 1.

What is SCD Type 6?

SCD Type 6 is the hybrid that combines Type 1 + Type 2 + Type 3 (1 + 2 + 3 = 6) in a single dimension row. It keeps full Type 2 versioning (surrogate key, effective_from/effective_to, is_current) so you can query the attribute as-of any past date, and a Type 1 "current" mirror column (e.g. current_segment) that is overwritten across all versions of a business key so it always reflects the latest value, and optionally a Type 3 "prior" column for the immediately previous value. The payoff is that one table answers both "what was their segment at purchase time" (via the versioned column and surrogate-key join) and "what is their segment now" (via the mirror column, with no is_current filter). It costs one extra column and one extra UPDATE per load — the mirror overwrite on all versions of changed keys — which is worthwhile when both temporal questions are asked frequently.

How do you write a Type 2 MERGE?

A Type 2 load is always two steps: expire, then insert. First, expire the current version of every business key whose tracked columns changed — set effective_to to the change date minus one (or the change instant) and is_current = FALSE — detecting change via a hash of the tracked columns so unchanged keys are skipped. Second, insert a new version for both changed keys and brand-new keys, with effective_from = the change date, effective_to = 9999-12-31, is_current = TRUE, and a freshly generated surrogate key. Wrap both steps in one transaction so the one-current-per-key invariant is never transiently violated and a crash rolls back cleanly. In Snowflake, BigQuery, and Databricks this is a MERGE (to expire) followed by an INSERT; in Postgres the idiomatic form is a CTE (incoming → changed → UPDATE...RETURNING → INSERT). Facts then join on the surrogate key to read the era-correct attribute with no date predicate.

How do dbt snapshots implement SCD?

A dbt snapshot is a declarative Type 2 implementation: you write a select over a source and a config block, and running dbt snapshot executes the expire-and-insert MERGE for you, managing dbt_scd_id (a surrogate id), dbt_valid_from, and dbt_valid_to. There are two change-detection strategies: the timestamp strategy watches an updated_at column and versions a row when it advances (cheapest and most reliable when a trustworthy updated_at exists), and the check strategy hashes a list of check_cols and versions when the hash changes (use when there is no dependable updated_at). Setting invalidate_hard_deletes=True tells dbt to expire rows that vanished from the source, giving built-in hard-delete handling. Because both strategies are hash/timestamp-driven, snapshots are idempotent — re-running with unchanged source writes nothing.

How do you handle deletes in SCD Type 2?

There are two cases. For a soft delete, where the source sets a deleted_at (or is_deleted) flag, you treat it like any other attribute change — a new Type 2 version with the deletion flag set — so history is fully preserved. For a hard delete, where the row physically disappears from the source, you detect it with an anti-join (business keys that are still is_current in the dimension but absent from the current source snapshot), expire that current version, and optionally insert a tombstone version flagged is_deleted = TRUE with the deletion date so downstream can distinguish "removed on 2026-08-07" from "still active." dbt automates this via invalidate_hard_deletes=True. The one rule you must never break: never physically delete the historical versions from the dimension — doing so destroys the very history the Type 2 table exists to keep. Expire and tombstone; do not delete.

Practice on PipeCode

Drill the SQL practice library → for the MERGE, upsert, window-function, and as-of-join problems that Type 2 loads and history queries are built from.
Work the modeling muscles on the dimensional-modeling practice library → for star-schema, surrogate-key, and fact-to-dimension design problems.
Rehearse the history mechanics on the slowly-changing-data practice library → for effective-date, current-flag, and idempotent-load scenarios interviewers love.
Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the full SCD type map against real graded inputs.

Lock in SCD muscle memory

Docs explain the type map. PipeCode drills make you *write* it — the Type 2 MERGE from memory, the as-of query with the right half-open interval, the idempotent load that survives a retry, the delete handling that expires instead of deletes. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the dimensional-modeling trade-offs data engineers actually face on the whiteboard and in production.

Practice SCD problems →
Practice dimensional-modeling problems →