Slowly Changing Dimension Type 2: How SCD2 Works in a Data Warehouse

A customer moves from the South region to the North region. Every order they placed before the move still happened in the South. If the warehouse overwrites the region, last year's sales report changes shape overnight. Slowly Changing Dimension Type 2 (SCD2) is the modeling pattern that keeps the old row, adds a new one, and lets each fact join to the version that was true when it happened.

What is a slowly changing dimension?

A slowly changing dimension is a dimension table (customer, product, employee, location) whose descriptive attributes change over time, and the SCD type is the rule for what the warehouse does when that happens. The three basic answers are overwrite the value, add a new row, or add a new column.

In dimensional modeling, facts are the events you measure (an order, a shipment, a payment) and dimensions describe the who, what, and where around those events. Dimension attributes change slowly compared to facts: a customer changes address a few times in a lifetime, while orders arrive every minute. The SCD type is the warehouse owner's choice of how to respond to changed descriptions of entities such as Employee, Customer, Product, Supplier, and Location.

A common example is an employee's region or a customer's address. Sales credited to the old region should stay there. A report on "sales by current region" is a different question, and the SCD type decides whether the table can answer one, the other, or both.

What are SCD types 0 through 7?

Kimball defines eight types. Types 0, 1, 2, and 3 are the base techniques (keep the original, overwrite, add a row, add a column). Type 4 splits volatile attributes into a mini-dimension, and types 5, 6, and 7 are hybrids built on top of type 2.

Type 4 in Kimball's numbering is a mini-dimension. Some tools and blog posts use "type 4" to mean a separate history table next to a current table. That is a useful pattern, but it is a different thing from what Kimball numbered.

How does a Type 2 slowly changing dimension work?

When a tracked attribute changes, a Type 2 dimension keeps the old row untouched, inserts a new row with the new values and a new surrogate key, and marks the old row as expired. Kimball's minimum is three extra columns: a row effective date, a row expiration date, and a current-row indicator.

Two keys make this work. The natural key (customer_id from the source system) is the same on every version and is what Kimball calls the glue that holds the versions together. The surrogate key (customer_sk) is a warehouse-assigned integer that is different on every version. Fact rows store the surrogate key, so an order placed in February keeps pointing at the February version of the customer forever.

Two rules keep the validity windows clean. The expiration timestamp of one version must equal the effective timestamp of the next version, with no gaps and no overlaps. And the current version has either a NULL expiration or a far-future date such as 9999-12-31, depending on the tool. The current flag exists so that "give me today's customers" is a simple filter instead of a date comparison.

What columns does an SCD2 table need?

An SCD2 table needs the natural key, a per-version surrogate key, the tracked attributes, an effective-from timestamp, an effective-to timestamp, and a current flag. A hash of the tracked attributes is optional but makes change detection a single comparison.

Only hash the attributes you want to version. If the source table has a last_login column that changes daily and you include it in the hash, you get a new customer version every day. Microsoft's worked example uses the same shape with different names: SalesRepID as the surrogate key, RepSourceID as the natural key, plus StartDate, EndDate, IsCurrent, and Hash.

How do you implement SCD Type 2 in SQL?

The portable SCD2 load is two statements inside one transaction. First, close the current version of any customer whose hash differs from the staged row. Second, insert a new version for every staged row that now has no current match, which covers both brand-new customers and the ones just closed.

The staging table holds one row per customer per load with the values as they are now and the time they changed:

CREATE TABLE dim_customer (
customer_sk BIGINT,
customer_id INTEGER,
customer_name VARCHAR,
region VARCHAR,
attr_hash VARCHAR,
valid_from TIMESTAMP,
valid_to TIMESTAMP,
is_current BOOLEAN
);

CREATE TABLE stg_customer (
customer_id INTEGER,
customer_name VARCHAR,
region VARCHAR,
changed_at TIMESTAMP
);

Step one closes the changed versions. Unchanged customers match on hash and are skipped:

UPDATE dim_customer AS d
SET
valid_to = s.changed_at,
is_current = FALSE
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
AND d.is_current
AND d.attr_hash <> md5(concat_ws('|', s.customer_name, s.region));

Step two inserts a new version for anything without a current row. The surrogate key continues from the current maximum:

INSERT INTO dim_customer (
customer_sk, customer_id, customer_name, region,
attr_hash, valid_from, valid_to, is_current
)
WITH max_sk AS (
SELECT coalesce(max(customer_sk), 0) AS value
FROM dim_customer
),
rows_to_insert AS (
SELECT s.*
FROM stg_customer AS s
LEFT JOIN dim_customer AS d
ON d.customer_id = s.customer_id
AND d.is_current
WHERE d.customer_id IS NULL
)
SELECT
row_number() OVER (ORDER BY s.customer_id) + max_sk.value AS customer_sk,
s.customer_id,
s.customer_name,
s.region,
md5(concat_ws('|', s.customer_name, s.region)) AS attr_hash,
s.changed_at AS valid_from,
NULL AS valid_to,
TRUE AS is_current
FROM rows_to_insert AS s
CROSS JOIN max_sk;

To see it work, load three customers on 2024-01-01, then a second batch on 2024-03-10 where Ana (101) moved from South to North, Bruno (102) is unchanged, and Diego (104) is new. Carla (103) is absent from the second batch:

INSERT INTO stg_customer VALUES
(101, 'Ana', 'South', TIMESTAMP '2024-01-01 09:00:00'),
(102, 'Bruno', 'North', TIMESTAMP '2024-01-01 09:00:00'),
(103, 'Carla', 'South', TIMESTAMP '2024-01-01 09:00:00');
-- run the two steps

TRUNCATE stg_customer;
INSERT INTO stg_customer VALUES
(101, 'Ana', 'North', TIMESTAMP '2024-03-10 14:30:00'),
(102, 'Bruno', 'North', TIMESTAMP '2024-03-10 14:30:00'),
(104, 'Diego', 'West', TIMESTAMP '2024-03-10 14:30:00');
-- run the two steps again

The dimension after the second load, from DuckDB 1.5.5:

+-------------+-------------+---------------+--------+---------------------+---------------------+------------+
| customer_sk | customer_id | customer_name | region | valid_from | valid_to | is_current |
+-------------+-------------+---------------+--------+---------------------+---------------------+------------+
| 1 | 101 | Ana | South | 2024-01-01 09:00:00 | 2024-03-10 14:30:00 | false |
| 4 | 101 | Ana | North | 2024-03-10 14:30:00 | NULL | true |
| 2 | 102 | Bruno | North | 2024-01-01 09:00:00 | NULL | true |
| 3 | 103 | Carla | South | 2024-01-01 09:00:00 | NULL | true |
| 5 | 104 | Diego | West | 2024-03-10 14:30:00 | NULL | true |
+-------------+-------------+---------------+--------+---------------------+---------------------+------------+

Four source customers became five dimension rows. Ana has two versions, the South one closed at exactly the timestamp the North one opened. Bruno's hash matched, so nothing happened to him. Carla was missing from the second batch and is still current, because this pattern treats an absent row as "no news". Deletes are covered below.

Two things to watch in this pattern. It expects one row per customer_id in staging per run. If your staging table carries several versions of the same key in one load (a CDC feed does this), keep only the latest version per key before running it, or loop the two steps in changed_at order. And the UPDATE and INSERT must run in the same transaction, so a failure between them cannot leave a customer with zero current rows.

How do you query the current record or a point in time with SCD2?

The current state is a filter on the flag. A point-in-time lookup uses a half-open interval: the as-of timestamp is greater than or equal to valid_from and strictly less than valid_to, with NULL valid_to counting as open.

SELECT customer_id, customer_name, region
FROM dim_customer
WHERE is_current
ORDER BY customer_id;

+-------------+---------------+--------+
| customer_id | customer_name | region |
+-------------+---------------+--------+
| 101 | Ana | North |
| 102 | Bruno | North |
| 103 | Carla | South |
| 104 | Diego | West |
+-------------+---------------+--------+

The as-of query. Half-open matters at the boundary: asking for exactly 2024-03-10 14:30:00 returns the North row, because that instant belongs to the new version and not the old one:

SELECT customer_id, region, customer_sk, valid_from, valid_to
FROM dim_customer
WHERE customer_id = 101
AND TIMESTAMP '2024-02-15 00:00:00' >= valid_from
AND (TIMESTAMP '2024-02-15 00:00:00' < valid_to OR valid_to IS NULL);

+-------------+--------+-------------+---------------------+---------------------+
| customer_id | region | customer_sk | valid_from | valid_to |
+-------------+--------+-------------+---------------------+---------------------+
| 101 | South | 1 | 2024-01-01 09:00:00 | 2024-03-10 14:30:00 |
+-------------+--------+-------------+---------------------+---------------------+

The same interval logic is how facts get their surrogate key at load time. Two orders from Ana, one in February and one in April, land on different customer versions:

SELECT f.order_id, f.ordered_at, d.region, d.customer_sk
FROM fact_order AS f
JOIN dim_customer AS d
ON d.customer_id = f.customer_id
AND f.ordered_at >= d.valid_from
AND (f.ordered_at < d.valid_to OR d.valid_to IS NULL)
ORDER BY f.order_id;

+----------+---------------------+--------+-------------+
| order_id | ordered_at | region | customer_sk |
+----------+---------------------+--------+-------------+
| 1001 | 2024-02-01 10:00:00 | South | 1 |
| 1002 | 2024-04-01 16:45:00 | North | 4 |
+----------+---------------------+--------+-------------+

Once customer_sk is stored on the fact row, reports join on the surrogate key alone and never touch the date columns. February's order stays in the South forever, with no date math at query time.

Where do the changes come from: dbt snapshots, CDC, or a managed history mode?

An SCD2 transform can only version the changes that reached the warehouse. If the source is read once a day and a customer changes region twice in that day, the dimension gets one version, not two. So the capture method decides how faithful the history is, before any SQL runs.

There are three ways the changes arrive:

dbt snapshots are a batch-based approach to change data capture meant to run between hourly and daily. That is enough when a daily grain of history is enough. Fivetran History Mode shows the same split. When the source has logs, it captures all changes between syncs. When it does not, a value that changed from 10 to 15 to 20 between two syncs is recorded only as 20.

Change Data Capture (CDC) reads the log the database already writes for its own recovery. In PostgreSQL that is the write-ahead log (WAL), and a CDC process reads it through a replication slot and gets each operation, in order, with the row state. For updates and deletes to carry the full before and after row, the table needs REPLICA IDENTITY FULL, which the Erathos PostgreSQL connector docs list along with wal_level = logical and a max_slot_wal_keep_size limit so a stalled slot cannot fill the disk.

With Erathos, the Partial Versioned sync type appends every record version to the destination and marks which one is the most recent. That gives the two-step load above a staging table that already contains all the versions, with one caveat from the previous section: several versions of the same key can land in one run, so the load processes them in changed_at order. The MySQL setup for the same thing is in the MySQL CDC to BigQuery guide.

How do dbt, dlt, Fivetran, Databricks, and Airbyte name SCD2 columns?

The tools below use the same three ideas (valid-from, valid-to, current marker) under different column names and with different defaults for the open row and for deletes.

Two defaults matter more than the names. dbt recommends the timestamp strategy where possible because it needs one column and copes better when the source adds or removes columns. dlt's default hash covers every column in the resource, so an unstable column (an array whose order changes, for example) produces a false version unless you supply your own hash over the fields you care about.

How should an SCD2 pipeline handle deletes and late-arriving changes?

A delete needs a declared policy, because the two-step load above cannot see it. Late or out-of-order changes need a sequence column, because closing the current row at an earlier timestamp than its valid_from produces a negative-length version.

For deletes there are three policies, and the dbt hard_deletes options map to them one-to-one:

• Ignore. The row stays current forever. This is the dbt default, so a deleted customer still shows up in "today's customers".
• Invalidate. Set valid_to and is_current = FALSE on the current row, so the customer has no current version. Fivetran History Mode does this and closes the row at the delete time.
• New record. Insert a version with a deleted flag set, so a query for "who was a customer on this date" still has a row to find, and a restore later becomes one more version.

Detecting the delete in the first place needs either a full extract to compare against (dlt retires any row absent from a full extract) or a log-based source where the delete is an event. With a cursor sync there is nothing to detect.

For late-arriving changes, Databricks requires a SEQUENCE BY column and uses it to order events that arrive out of order. The equivalent in the two-step pattern is to process staging rows in changed_at order and to split an existing window when a change lands inside it, which is more work than the plain close-and-insert. If your source is CDC and the log is read in order, out-of-order events are rare and the ordering is already in the feed.

When is Type 1 enough, and what does Type 2 cost?

Type 1 is enough when no report needs the value as it was at the time of the event. Type 2 is needed when facts must stay attached to the version that was true when they happened, and it costs one extra row per tracked change plus a more involved load and join.

The cost is directional rather than a fixed ratio. Every tracked change adds a row, so a dimension with volatile attributes can grow fast, and a hash that includes the wrong columns turns a slowly changing dimension into a fast one. The same effect shows up on a bill. In Fivetran History Mode, every changed or inserted source record creates a destination row that counts toward paid MAR (monthly active rows, its pricing unit).

A useful middle path is Kimball's Type 6: keep Type 2 rows and add a current-value column that gets overwritten on all versions of the same customer. One table then answers "sales by region at order time" and "sales by the customer's region today" without a second dimension.

Erathos loads PostgreSQL and MySQL changes from the transaction log into BigQuery, Databricks, or Redshift, with the versioned sync type that keeps every record version for an SCD2 load like the one above. Try Erathos free for 14 days.