Slowly Changing Dimensions & Incremental Loading
A question that trips up a lot of interviews: how do you handle a dimension that changes over time, inside an incremental ETL/ELT pipeline? Two ideas almost always asked about together, taught here in one place.
Two ideas, one interview question:
- Incremental loading — process only what changed since the last run: watermark (
updated_at > last_load), hash-diff, or log-based CDC. - SCD Type 1 — overwrite in place, no history kept.
- SCD Type 2 — keep every version, each with its own surrogate key and a validity window (
effective_date/end_date/is_current). - SCD Type 3 — keep only the immediately-prior value in a
previous_Xcolumn. MERGE(ANSI SQL:2003) is the one-statement, modern way to express a Type 2 upsert on platforms that support it.
Full Load vs. Incremental Load
Full load means truncating the target table and reloading every single row from the source, on every run — simple to reason about and always correct, since it never has to figure out what changed, but it scales badly: reloading a 500-million-row table nightly to pick up 5,000 changed rows wastes both time and compute, and that cost only grows as the table does. Incremental load processes only the rows that actually changed since the last run, which requires some way to detect what changed: a watermark filters the source on a reliable "last updated" timestamp column; a hash-diff compares a computed hash of each row's columns against the previous run's hash to spot changes even without a trustworthy timestamp; log-based CDC (Change Data Capture) reads the source database's transaction log directly, catching every insert/update/delete as it happens (see the site's Orchestration & CDC page for the platform-architecture framing, and ETL vs. ELT for where this transform logic runs).
SQL · watermark-based delta detection
-- Only rows changed since the previous run's watermark come back
SELECT *
FROM source_customers
WHERE updated_at > '2024-03-01 00:00:00'; -- last_load_timestamp
-- 1 row processed, not the whole table — that's the entire point of incremental loadingIncremental Loading Is a Mechanism; SCD Is a Policy
These two ideas get taught separately and asked about together, which is exactly why they're confusing: detecting that a row changed (incremental loading, above) and deciding what to do about it (Slowly Changing Dimensions) are genuinely two separate decisions, made independently. A watermark can feed either policy: SCD Type 1 is the simplest — overwrite the changed value in place, no history kept, appropriate for correcting a typo or any attribute where the old value truly doesn't matter once it's wrong. SCD Type 2 and Type 3 (below) instead preserve some or all of that history. The mechanism that noticed the row changed doesn't dictate which of these three policies handles it — that choice depends entirely on whether anyone will ever need to ask "what was true before this changed."
SQL · SCD Type 1, overwrite in place
-- Simplest policy: the old value is gone once this runs
UPDATE dim_customer
SET city = (
SELECT city FROM source_customers
WHERE source_customers.customer_id = dim_customer.customer_id
)
WHERE customer_id = 7;
-- Fine for a typo fix; wrong if you'll ever ask "what city were they in on order #17?"SCD Type 2: the One Interviews Ask About
Keep every version of a row, each with its own surrogate key (Part 2) and validity window. This is the pattern that answers "what was true at the time" — which region a sale should be attributed to, what tier a customer was in when they churned. The upsert itself is a two-statement transaction: close out the current row (end_date, is_current = 0), then insert the new current version.
SQL · SCD Type 2 schema + the two-statement upsert
CREATE TABLE dim_customer_scd2 (
customer_key INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
city VARCHAR(50),
membership_level VARCHAR(10),
effective_date DATE NOT NULL,
end_date DATE, -- NULL means "still current"
is_current INTEGER NOT NULL DEFAULT 1
);
-- Step 1: close out the row that's about to become historical
UPDATE dim_customer_scd2
SET end_date = '2024-06-15', is_current = 0
WHERE customer_id = 7 AND is_current = 1;
-- Step 2: insert the new current version
INSERT INTO dim_customer_scd2
(customer_id, name, city, membership_level, effective_date, end_date, is_current)
VALUES (7, 'Grace Kim', 'Montreal', 'vip', '2024-06-15', NULL, 1);
-- The payoff: "what city was Grace in on 2024-03-01?" is now answerable, historically
SELECT city, effective_date, end_date
FROM dim_customer_scd2
WHERE customer_id = 7
AND effective_date <= '2024-03-01'
AND (end_date IS NULL OR end_date > '2024-03-01');Two rows, same customer_id, different surrogate customer_key — each valid for its own date range.
SCD Type 3: Just the Previous Value
Type 2 answers "what was true on any date in the past," but that full history has a real cost: extra rows, a surrogate key, and every query needing an effective_date/end_date filter to pick the right version. Type 3 is a deliberately cheaper middle ground for when you only ever need to know the immediately previous value, not the full timeline: instead of inserting a new row, add a previous_X column next to the current one, and on change, shift the old value sideways into it before overwriting the current column. No new rows, no surrogate-key versioning — but it only remembers one step back; a second change overwrites the first one's memory of "previous" with no way to recover it.
SQL · SCD Type 3, a previous-value column
CREATE TABLE dim_customer_scd3 (
customer_key INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
current_city VARCHAR(50),
previous_city VARCHAR(50)
);
INSERT INTO dim_customer_scd3 VALUES (1, 7, 'Grace Kim', 'Vancouver', NULL);
-- On change: shift the old value into previous_city, then overwrite current_city
UPDATE dim_customer_scd3
SET previous_city = current_city, current_city = 'Montreal'
WHERE customer_id = 7;Watermarks Are Blind to Deletes
A watermark-based load is the cheapest incremental strategy to build and the cheapest to run — just a timestamp comparison — but it has one structural blind spot: it only detects rows that were inserted or updated since the last watermark, because those are the only kinds of change that touch the "last updated" column it relies on. A row that gets hard-deleted from the source simply stops appearing in the next extract; there's no updated updated_at to trip the watermark, because the row isn't there to have one. If your pipeline needs to know about deletions — not just insertions and updates — a watermark alone can't tell you; that's exactly the gap log-based CDC closes, since it reads the transaction log directly and sees every operation, deletes included, as it happens. MERGE below is the ANSI-standard way most warehouses express the resulting upsert once a delta has been detected, by whichever method.
SQL · the same upsert as ANSI MERGE (reference — not SQLite)
-- ANSI SQL:2003 MERGE (PostgreSQL 15+, SQL Server, Oracle, Snowflake, BigQuery -- not SQLite):
MERGE INTO dim_customer_scd2 AS target
USING source_customers AS src
ON target.customer_id = src.customer_id AND target.is_current = 1
WHEN MATCHED AND target.city != src.city THEN
UPDATE SET end_date = CURRENT_DATE, is_current = 0
WHEN NOT MATCHED THEN
INSERT (customer_id, name, city, membership_level, effective_date, is_current)
VALUES (src.customer_id, src.name, src.city, src.membership_level, CURRENT_DATE, 1);
-- a second INSERT ... SELECT is still needed for the new current row on most platforms --
-- one MERGE statement takes only one action per matched rowIncremental loading finds the delta; SCD Type 2 decides what to keep.
The takeaway: Default to SCD Type 1 unless you have a concrete need to answer "what was true historically." Type 2's extra rows and query complexity aren't free — and Part 10 shows dbt's incremental materialization as the productionized version of everything on this page.
Runnable code for this part: sql-for-data-platforms on GitHub — Module 8 of 14.
← Back to Publications