SCD Type 2 in practice: keeping customer and product history with SQL and dbt snapshots

In March, Ms Lan moved from Hà Nội to Đà Nẵng and was upgraded to the VIP tier. The CRM team edited exactly one row. The next morning, the revenue-by-city report had moved every order she placed in Hà Nội since last year to Đà Nẵng, and nobody could tell any more which tier she used to be in.

This kind of bug makes no noise. No exception is thrown. The numbers simply drift, and the person who notices is usually the client, not you. So when an FDE sits down with a client’s data team, the first question worth asking is whether their customer and product tables keep history at all.

The classic fix is the Type 2 slowly changing dimension. There are two ways to build one, and they are best learned in order: write it by hand in SQL to understand the mechanics, then hand it over to dbt snapshots to run in production.

What will you build, and what do you need?

You will end up with a dim_customer table that keeps full history, a dbt snapshot for the product table, and a join that ties each order to the version of the customer that existed at the time of purchase.

You need any SQL database (Postgres is enough), a dbt project on version 1.9 or later because the example uses the hard_deletes config, and an overwrite-style source table, customers or products, where every change edits the old row in place.

The syntax below has been trimmed for readability. Before using it in a real environment, check it against the dbt documentation for the version you run.

Step 1: why do you need a surrogate key?

Kimball’s definition is short: each Type 2 change adds a new row to the dimension with the updated attribute values. That new row is assigned a new surrogate key. From the moment of the change onwards, every fact table uses this key as its foreign key.

The customer ID KH001 from the CRM can therefore no longer be the primary key, because the same person now has several rows. A guide on SQLShack makes the same point: once you do Type 2, you cannot avoid surrogate keys. Kimball also recommends at least three tracking columns: an effective date, an expiration date and a current-row flag.

-- Illustrative example, simplified
CREATE TABLE dim_customer (
  customer_key    INT PRIMARY KEY,   -- surrogate key
  customer_id     VARCHAR(20),       -- natural key from the CRM
  segment         VARCHAR(20),
  city            VARCHAR(50),
  effective_date  DATE,
  expiration_date DATE,
  is_current      BOOLEAN
);

Check: customer_key is the primary key, and customer_id has no UNIQUE constraint. If you put UNIQUE on customer_id by mistake, the INSERT will violate that constraint at the very first change.

Step 2: handle one change by hand

When Ms Lan changes city and tier, you do not UPDATE the attributes. You do two things instead: close the old row, then open a new one.

-- Close the old version
UPDATE dim_customer
SET expiration_date = '2026-03-01', is_current = FALSE
WHERE customer_id = 'KH001' AND is_current = TRUE;

-- Open a new version with a new key
INSERT INTO dim_customer
VALUES (102, 'KH001', 'VIP', 'Da Nang', '2026-03-01', NULL, TRUE);

After these two statements, the table looks like this:

customer_key	customer_id	segment	city	effective_date	expiration_date	is_current
17	KH001	Regular	Hà Nội	2025-01-10	2026-03-01	FALSE
102	KH001	VIP	Đà Nẵng	2026-03-01	NULL	TRUE

The SQLShack example describes exactly this: when a customer’s job title changes, the system creates a new record with a new CustomerKey, so older facts still point to the old version. Ms Lan’s orders from last year carry key 17 and are still counted under Hà Nội. Orders from March onwards carry key 102 and belong to Đà Nẵng.

Check: each customer_id must have exactly one row with is_current = TRUE. If there are two, the step that closes the old row was skipped.

Step 3: hand the repetitive work to dbt snapshots

Writing it by hand builds understanding, but running it nightly across dozens of tables invites mistakes. The dbt documentation describes snapshots as the mechanism for implementing SCD Type 2 on mutable source tables, precisely the kind of table that holds customers or products.

# snapshots/products.yml — simplified, dbt 1.9+
snapshots:
  - name: products_snapshot
    relation: source('erp', 'products')
    config:
      unique_key: product_id
      strategy: timestamp
      updated_at: updated_at
      hard_deletes: invalidate

Run dbt snapshot. On the first run, each product has one row. Change the price of one product at the source, update updated_at, then run it again.

Check: the product you edited now has two rows. The current row has dbt_valid_to set to NULL, which is the dbt default. You can also use dbt_valid_to_current to set a different value, such as a date far in the future, if the client’s BI tool handles NULL poorly.

Timestamp or check: choose by how far you trust the source

dbt recommends the timestamp strategy because it handles added or removed columns more efficiently than check. But timestamp is only as reliable as the source’s updated_at column.

Picture an old ERP system where the accounting team edits prices directly in the database and nobody updates updated_at. With the timestamp strategy, those changes pass through without a trace.

This is where check belongs: the dbt documentation says this strategy suits tables without a reliable updated_at column. It compares the columns you list to detect changes.

    config:
      unique_key: product_id
      strategy: check
      check_cols: ['price', 'category']   # simplified

A quick test on site is to edit one row in the client’s test environment and see whether updated_at moves with it. Five minutes spent on that can save you weeks of missing history.

Three mistakes that corrupt history without anyone noticing

The first is a unique_key that is not truly unique. dbt uses this key to match source rows to snapshot rows, so the documentation requires it to be genuinely unique. A product table that has the same code in two warehouses will be matched incorrectly. Before the first run, execute SELECT product_id, COUNT(*) ... GROUP BY 1 HAVING COUNT(*) > 1.

The second is forgetting rows deleted at the source. By default, dbt snapshots ignore rows that have disappeared, so a discontinued product still looks as if it is on sale. From version 1.9, the hard_deletes: invalidate config closes those rows by setting dbt_valid_to, as in the configuration in Step 3.

The third sits on the consumer side: joining facts to the dimension while filtering to current rows only, which quietly turns Type 2 back into overwriting. Here the orders table carries only the original product_id from the source, so the join must use the natural key plus the validity window.

-- Simplified; check the column names in your snapshot table
SELECT o.order_id, p.price
FROM orders o
JOIN products_snapshot p
  ON o.product_id = p.product_id
 AND o.order_date >= p.dbt_valid_from
 AND (o.order_date < p.dbt_valid_to OR p.dbt_valid_to IS NULL);

This does not contradict Step 2. Joining on the surrogate key, as with Ms Lan’s orders carrying key 17, applies when the fact table was assigned the key at load time. Joining on the natural key plus the validity window applies when the facts carry only the original ID. Both give the same result provided the validity windows do not overlap.

Check: the row count after the join must equal the number of orders. If it is higher, validity windows are overlapping. If it is lower, some orders fall into a gap between two versions.

What should you ask the data team on day one?

An FDE is rarely handed a task called “do SCD”. The request usually arrives as a business question: revenue by customer tier at the time of purchase, or margin based on the old list price. If the dimension has already been overwritten, the correct answer no longer exists.

So before opening any dashboard, ask the client’s data team a single question: “When a customer changes tier or a product changes price, is the old version kept anywhere, and does the updated_at column change when someone edits the database directly?”

The first half of the answer tells you which tables need snapshots. The second half decides between timestamp and check.

If the answer is “we overwrite”, put snapshots on those tables in the first week. History is only recorded from the first run, and every day of delay is a day you will never get back.

On your CV, tell that sequence rather than writing “knows SCD”: what you asked, which tables you found were being overwritten, where you caught a duplicate unique_key, and how you fixed the join so period reports matched the accounting figures. FDE interviewers want to hear a story like that, not another keyword.