A slowly changing dimension Type 2 keeps every version of a record as its own row, with the dates each version was true. It is the pattern behind questions like "which plan was this customer on when they placed that order?", and it is simple to describe and fiddly to build.

This guide builds one customer dimension three ways, each ready to paste: a plain SQL MERGE for BigQuery, Snowflake and Postgres, a dbt snapshot, and Weld history tables with no code. Then it covers the parts most answers leave out: hard deletes, rows that arrive late or out of order, history you never captured, and querying the table "as of" a date.

If you want the overview of Type 1, 2, 3 and 4 first, start with slowly changing dimensions explained. This post is the hands-on part.

How we tested it. Every query below was run on Postgres 18 and DuckDB against the same four loads of sample data, and the BigQuery versions were dry run on BigQuery. The dbt snapshot was run with dbt Core 1.12 on DuckDB. The Snowflake syntax was checked against Snowflake's MERGE reference but not run on Snowflake.

What a Type 2 table stores

Start with a source table, customers, that your app updates in place:

customer_idnameplancountryupdated_at
42AcmefreeDK2026-01-05 09:00
43BirchproSE2026-01-05 09:00

When Acme upgrades to pro on 20 February, the app overwrites the row. A dimension built the same way (Type 1) forgets the free plan. A Type 2 dimension closes the old version and adds a new one:

One change stored two ways. Customer 42 upgrades from free to pro on 20 February 2026. Type 1 overwrites the dimension row with pro, so the free value is lost and old orders now say pro. Type 2 closes the free row with valid_to 2026-02-20 and is_current false, and adds a pro row with valid_from 2026-02-20, valid_to null and is_current true.

Over the next months more happens: Cobalt signs up on 27 February, Acme moves to Norway on 14 May, and Birch is deleted from the app before the load on 1 June. The source table only ever shows the latest state. The Type 2 dimension, dim_customer, shows all of it:

customer_idplancountryvalid_fromvalid_tois_current
42freeDK2026-01-05 09:002026-02-20 14:30false
42proDK2026-02-20 14:302026-05-14 11:00false
42proNO2026-05-14 11:00nulltrue
43proSE2026-01-05 09:002026-06-01 00:00false
44freeNO2026-02-27 08:00nulltrue

Drawn on a time axis, every version is a window, and the windows of one customer line up end to end:

Timeline of dim_customer versions from January to June 2026. Acme has three versions: free in DK from 5 January to 20 February, pro in DK to 14 May, and pro in NO from 14 May, still current. Birch has one version, pro in SE, closed on 1 June when the record was deleted. Cobalt has one current version, free in NO from 27 February. A dashed line at 1 April marks an as-of query, which returns Acme on pro in DK, Birch and Cobalt.

Three columns do the work:

  • valid_from is when this version became true. Use the source's own change timestamp (updated_at) when you have one you trust, and the load time when you don't.
  • valid_to is when the next version replaced it. The current version has null here. Some teams use 9999-12-31 instead so range filters need no null check; both work, pick one and use it everywhere.
  • is_current is redundant with valid_to is null, but it makes the common "latest version" filter cheap and obvious.

The windows are half open: a version is true from valid_from up to, but not including, valid_to. That way a timestamp exactly on a boundary belongs to one version only.

Two rules keep the table correct. Each customer_id has at most one row with is_current = true. And the windows for one customer never overlap and leave no gaps, except after a delete. Every implementation below is a way of keeping those two rules on each load.


1. SCD Type 2 with MERGE (BigQuery, Snowflake, Postgres)

The table:

SQL
1create table dim_customer (
2  customer_id integer,
3  name        varchar,
4  plan        varchar,
5  country     varchar,
6  valid_from  timestamp,
7  valid_to    timestamp,
8  is_current  boolean
9);

In BigQuery use int64, string and timestamp, and qualify the table with its dataset (analytics.dim_customer). In Snowflake varchar and timestamp work as written.

Each load lands the latest copy of the source in a staging table, stg_customers, with one row per customer_id. The tricky part is that one change needs two actions on the dimension: close the current row and insert the new version. A single MERGE can only do one thing per source row, so the usual trick is to feed it changed rows twice:

SQL
1merge into dim_customer as d
2using (
3  -- every incoming row, keyed on its id
4  select c.customer_id as merge_key, c.*
5  from stg_customers as c
6
7  union all
8
9  -- changed rows a second time, with a key that never matches
10  select null as merge_key, c.*
11  from stg_customers as c
12  join dim_customer as cur
13    on cur.customer_id = c.customer_id
14   and cur.is_current
15  where c.updated_at > cur.valid_from
16    and (c.name    is distinct from cur.name
17      or c.plan    is distinct from cur.plan
18      or c.country is distinct from cur.country)
19) as s
20on d.customer_id = s.merge_key
21and d.is_current
22
23-- 1. a tracked column changed: close the current version
24when matched
25  and s.updated_at > d.valid_from
26  and (s.name    is distinct from d.name
27    or s.plan    is distinct from d.plan
28    or s.country is distinct from d.country)
29then update set
30  valid_to   = s.updated_at,
31  is_current = false
32
33-- 2. new customer, or the new version of a changed one
34when not matched then insert
35  (customer_id, name, plan, country, valid_from, valid_to, is_current)
36values
37  (s.customer_id, s.name, s.plan, s.country, s.updated_at, null, true);

How each kind of row flows through it, using the 1 March load:

How one MERGE closes and inserts. Branch 1 of the staged source has every incoming row keyed on its id: Acme with merge_key 42 matches its current row and closes the free version because the plan changed; Birch with merge_key 43 matches but is unchanged, so nothing happens; Cobalt with merge_key 44 does not match and is inserted. Branch 2, joined with union all, repeats only the changed rows with a null merge_key, so Acme's pro version never matches and is inserted as the new current row.
  • Unchanged customer. The first branch matches its current row, the change condition is false, nothing happens. The second branch doesn't return it.
  • Changed customer. The first branch matches and closes the current row at the new updated_at. The second branch returns it again with a null key, which matches nothing, so it is inserted as the new current version.
  • New customer. The first branch finds no current row, so it is inserted.

A few details matter more than they look:

  • is distinct from instead of !=. With !=, a change from null to a value (or back) is never detected, because comparing with null is never true. All three warehouses support is distinct from. With many tracked columns, compare one hash of them instead, built so that a null and an empty string hash differently.
  • List the columns you track. A Type 2 table only needs a new version when a column you care about changes. Track plan and country, and a typo fix in name can be updated in place (Type 1) without a new version.
  • c.updated_at > cur.valid_from stops an older copy of a row from closing a newer version. More on that under late rows below.
  • One row per key in staging. If stg_customers holds two versions of the same customer, the MERGE fails. BigQuery and Postgres raise an error, and Snowflake does too by default (ERROR_ON_NONDETERMINISTIC_MERGE). Deduplicate first; see edge cases.

Hard deletes in the MERGE

If the staging table is a full snapshot of the source, a customer missing from it was deleted. BigQuery and Postgres 17 and later can close those rows in the same statement with when not matched by source. Add this clause before the insert:

SQL
1-- 3. gone from the full snapshot: close it
2when not matched by source
3  and d.is_current
4then update set
5  valid_to   = current_timestamp,
6  is_current = false

The and d.is_current matters: closed versions never match the on condition either, and you don't want to touch them.

Snowflake's MERGE only has when matched and when not matched, so run the close as its own statement after the merge, in the same transaction:

SQL
1update dim_customer
2set valid_to   = current_timestamp,
3    is_current = false
4where is_current
5  and not exists (
6    select 1
7    from stg_customers as s
8    where s.customer_id = dim_customer.customer_id
9  );

The same update works in every warehouse, so use it if you'd rather keep one version of the code. Only ever run it against a full snapshot: on an incremental load, every customer that simply didn't change would be closed.


2. SCD Type 2 with dbt snapshots

dbt snapshots generate that MERGE for you. Since dbt 1.9 you define a snapshot in YAML in the snapshots folder:

YAML
1snapshots:
2  - name: customers_snapshot
3    relation: source('app', 'customers')
4    config:
5      unique_key: customer_id
6      strategy: timestamp
7      updated_at: updated_at
8      hard_deletes: invalidate

Run dbt snapshot on a schedule and dbt keeps a table with the source columns plus four of its own. After the same three loads as above:

customer_idplancountrydbt_valid_fromdbt_valid_to
42freeDK2026-01-05 09:002026-02-20 14:30
42proDK2026-02-20 14:302026-05-14 11:00
42proNO2026-05-14 11:00null
43proSE2026-01-05 09:00time of the run
44freeNO2026-02-27 08:00null

The columns dbt adds:

  • dbt_valid_from and dbt_valid_to: the validity window. dbt_valid_to is null on the current row unless you set dbt_valid_to_current, for example to "to_date('9999-12-31')" in Snowflake.
  • dbt_updated_at: the change timestamp dbt used for the row.
  • dbt_scd_id: a unique id per version, useful as the dimension's surrogate key.
  • dbt_is_deleted: only with hard_deletes: new_record.

There is no is_current column. Filter on dbt_valid_to is null instead, or add it in a model on top of the snapshot.

Timestamp vs check strategy

  • strategy: timestamp compares updated_at with the stored version. When it is newer, dbt closes the old row and inserts the new one, and both window edges come from updated_at, so the history reflects when things changed at the source. dbt recommends it whenever the source has an updated_at you can trust.
  • strategy: check compares the columns in check_cols and records a new version when any of them differ. Use it when the source has no reliable updated_at. The windows then come from the time dbt snapshot ran, unless you also configure an updated_at column. In our run, every dbt_valid_from was the run time, not the date the plan actually changed.
timestampcheck
Detects a change bya newer updated_ata different value in any of check_cols
dbt_valid_from / _tothe source's updated_atthe time of the snapshot run
Needsan updated_at you can trustthe list of columns to compare
A column added to the sourceno new versionsnew versions only if it is in check_cols
Use whenthe source has a reliable updated_atit doesn't
YAML
1snapshots:
2  - name: customers_check_snapshot
3    relation: source('app', 'customers')
4    config:
5      unique_key: customer_id
6      strategy: check
7      check_cols: [plan, country]
8      hard_deletes: new_record

List check_cols explicitly. check_cols: all works, but then any column, including ones added to the source later, opens a new version, and dbt itself advises against it.

Deletes in a snapshot

hard_deletes decides what happens when a row disappears from the source:

  • ignore (the default) leaves the last version open forever, so a deleted customer looks current.
  • invalidate sets dbt_valid_to on it, like the update above. It replaces the older invalidate_hard_deletes: true.
  • new_record closes the row and inserts a new version with dbt_is_deleted set to true. If the record comes back, it gets another new version with dbt_is_deleted false. This is the only option that tells you when a record came back.

Two things to know before you rely on snapshots. They only see what exists at the moment they run, so a record that changes twice between runs keeps only the second change. And they ignore --full-refresh: there is no rebuilding a snapshot, which is exactly why dbt suggests keeping them in their own schema.


3. Edge cases forum answers skip

Hard deletes

Covered above, with one caveat that applies to all three approaches: you can only detect a delete by comparing against a full copy of the source. An incremental load that reads "rows changed since yesterday" never sees a deleted row, because it is not there to read. Either run a periodic full snapshot to reconcile, or get deletes from somewhere that records them: a database change log (CDC), a deleted-records endpoint in the source API, or Capture deletes in Weld.

Also decide what a delete means in your reports. Closing the row (invalidate) says "this customer stopped existing on this date". An explicit deleted version (new_record) also lets you count deletions and restores, at the cost of an extra row per delete.

Late arriving and out of order rows

A load can contain a version that is older than what the dimension already holds: a retried batch, a slow replica, a backfill script. Without the updated_at > valid_from guard, the MERGE above would close Acme's May version with a February date and insert February as current, so the table claims Acme went back to the free plan.

With the guard, stale rows are ignored, which keeps the current state right. But the history is still missing the late version if it never got in. When order genuinely varies, stop treating the dimension as something you update and start treating it as something you derive: keep an append-only log of every version you have seen, and compute the windows from it with window functions.

SQL
1with changes as (
2  select
3    customer_id, name, plan, country, updated_at,
4    row_number() over (partition by customer_id order by updated_at) as version_number,
5    lag(name)    over (partition by customer_id order by updated_at) as prev_name,
6    lag(plan)    over (partition by customer_id order by updated_at) as prev_plan,
7    lag(country) over (partition by customer_id order by updated_at) as prev_country
8  from customers_log
9),
10
11versions as (
12  select customer_id, name, plan, country, updated_at as valid_from
13  from changes
14  where version_number = 1
15     or name    is distinct from prev_name
16     or plan    is distinct from prev_plan
17     or country is distinct from prev_country
18)
19
20select
21  customer_id,
22  name,
23  plan,
24  country,
25  valid_from,
26  lead(valid_from) over (partition by customer_id order by valid_from) as valid_to,
27  lead(valid_from) over (partition by customer_id order by valid_from) is null as is_current
28from versions;

The changes step drops rows identical to the version before them, so repeated loads of an unchanged record don't create versions. The lead() makes each version end where the next one starts, so a row that arrived late slots into its right place on the next rebuild. A rebuild costs more than a merge on very large dimensions, but customer and product dimensions are rarely that large, and a table you can recompute from its inputs is far easier to trust. If the steps are new to you, see CTEs in SQL.

Several changes inside one load

If a record changed twice since the last load and your staging has both versions, the MERGE errors (see above). If you only need the latest, keep one row per key before merging. In BigQuery, Snowflake and DuckDB:

SQL
1select *
2from raw_customers
3qualify row_number() over (partition by customer_id order by updated_at desc) = 1;

In Postgres, select distinct on (customer_id) * from raw_customers order by customer_id, updated_at desc does the same. If you need every version, use the log-and-rebuild query above, which handles any number of versions per key.

Backfilling history you never captured

A Type 2 table only knows what it has seen since it started. Before that, there is nothing to recover from the current table, so be explicit about it:

  • Open the first version at the record's real start. Use created_at for the first valid_from rather than the date you started tracking, but know that this asserts the values never changed before you looked. Some teams use the tracking start date instead to make the gap visible; either way, write it down.
  • Look for history the source already keeps. Many systems log changes themselves: Salesforce field history, HubSpot property history, a billing system's events, an audit table in your own database. Load that into the log and the rebuild query turns it into windows.
  • Warehouse time travel is short. BigQuery keeps between two and seven days (seven by default), and Snowflake one day by default and up to 90 on Enterprise edition. It can rescue last week, not last year.
  • Old exports and backups can be loaded into the log as extra versions, with updated_at set to the export date.

The real fix is to start capturing before anyone asks. History is the one thing you can't add later.

Querying "as of" a date

To see every customer as they were on 1 April 2026:

SQL
1select customer_id, name, plan, country
2from dim_customer
3where valid_from <= timestamp '2026-04-01 00:00:00'
4  and (valid_to > timestamp '2026-04-01 00:00:00' or valid_to is null);

That returns Acme on pro in Denmark, Birch (not yet deleted) and Cobalt: the versions the dashed line crosses in the timeline above. More often you join facts to the version that was true when they happened, which is what a Type 2 table is for:

SQL
1select o.order_id, o.ordered_at, d.plan, d.country
2from orders as o
3join dim_customer as d
4  on d.customer_id = o.customer_id
5 and o.ordered_at >= d.valid_from
6 and (o.ordered_at < d.valid_to or d.valid_to is null);

Note >= on one side and < on the other, matching the half-open windows, so an order placed at the exact moment of a change joins to one version, not two. If you store 9999-12-31 instead of null in valid_to, drop the or ... is null. For a table without valid_to at all, such as a history table, take the latest version on or before the date:

SQL
1select *
2from customers__history
3where updated_at <= timestamp '2026-04-01 00:00:00'
4qualify row_number() over (partition by id order by updated_at desc) = 1;

qualify works in BigQuery, Snowflake, Databricks and DuckDB. In Postgres, compute the row_number() in a subquery and filter on it outside.


4. SCD Type 2 without code: Weld history tables

All of the above assumes you control the load. If the data comes from SaaS tools through an ELT pipeline, the pipeline can keep the log for you.

History tables in Weld do this per table. Turn on History table in a table's settings in your data source, and Weld writes every version of each row to a second table next to it, named with __history (for example customers__history), with the same columns as the original. The main table keeps the current state. When you turn it on for a table that is already syncing, Weld fills the history table from the current table straight away, so every record starts with its current version. There is no SQL to write and no snapshot job to schedule. Setup is in the history tables docs.

A history table is the append-only log from the edge cases section, already in your warehouse. Turning it into a Type 2 dimension with valid_from and valid_to is the rebuild query pointed at it, plus Capture deletes for records the source deletes. With Capture deletes on, a full sync keeps a row the source stopped returning and stamps it with _weld_deleted_at, which closes the last version:

SQL
1with changes as (
2  select
3    id, name, plan, country, updated_at,
4    row_number() over (partition by id order by updated_at, _weld_synced) as version_number,
5    lag(name)    over (partition by id order by updated_at, _weld_synced) as prev_name,
6    lag(plan)    over (partition by id order by updated_at, _weld_synced) as prev_plan,
7    lag(country) over (partition by id order by updated_at, _weld_synced) as prev_country
8  from customers__history
9),
10
11versions as (
12  select id, name, plan, country, updated_at as valid_from
13  from changes
14  where version_number = 1
15     or name    is distinct from prev_name
16     or plan    is distinct from prev_plan
17     or country is distinct from prev_country
18)
19
20select
21  v.id as customer_id,
22  v.name,
23  v.plan,
24  v.country,
25  v.valid_from,
26  coalesce(
27    lead(v.valid_from) over (partition by v.id order by v.valid_from),
28    c._weld_deleted_at
29  ) as valid_to,
30  lead(v.valid_from) over (partition by v.id order by v.valid_from) is null
31    and c._weld_deleted_at is null as is_current
32from versions as v
33left join customers as c
34  on c.id = v.id;

Use the source's updated_at for the windows where it has one, and _weld_synced (the time Weld synced the version) where it doesn't. Only the columns you list in changes and versions open a new version, so the dimension can track plan and country while the history table keeps everything. Save the query as a model in Weld's SQL workspace, or in your dbt project, and it rebuilds after each sync.

Three things to know:

  • History starts when you turn it on. Like every approach here, a history table can't recover changes from before it existed. Turn it on for the tables you might report on historically, before you need them.
  • It captures what each sync sees. If a record changes twice between two syncs, the history table holds the version that was current at each sync, the same limit as a dbt snapshot.
  • History rows count toward active rows, at 50% of a regular active row from 1 December 2026. Capture deletes is in beta and available on the Premium and Business plans.

Which one to use

MERGEdbt snapshotWeld history tables
What you writethe staging step and the MERGEa YAML confignothing to capture; one query for the windows
A change is detected bythe columns you compareupdated_at, or the columns in check_colseach sync, against the warehouse copy
Window timestampsupdated_at, or the load timeupdated_at, or the run timeupdated_at or _weld_synced, chosen in your query
Hard deletesnot matched by source, or an update, on full snapshotshard_deletes: invalidate or new_recordCapture deletes (_weld_deleted_at)
Several changes between runsthe latest, after deduplicationthe version current at each runthe version current at each sync
Can be rebuilt from scratchno, it is updated in placeno, dbt never rebuilds a snapshotyes, the windows are a query over the log
  • You load the data yourself and want full control: the MERGE, with the updated_at guard and a delete step that runs on full snapshots only.
  • You already run dbt: snapshots, with strategy: timestamp when the source has a trustworthy updated_at, and hard_deletes set to invalidate or new_record.
  • The data comes from SaaS tools through ELT: history tables plus Capture deletes in Weld, with the rebuild query on top.

Whichever you pick, keep a log you can rebuild from. A MERGE and a snapshot both update the dimension in place, and a bad load stays in it. A dimension derived from a log can be fixed by fixing the query and running it again.

FAQ

Additional resources