SCD Type 2 SQL Generator

Slowly changing dimension Type 2 tracking is easy to describe and easy to get subtly wrong — a missed NULL comparison, a MERGE that closes a row but never inserts its replacement, or two current rows for one business key. This generator writes the whole pattern for you: the dimension DDL with surrogate key, validity range and is_current flag, the optional row hash used for change detection, and the load logic in both the safe two-step form and the single MERGE form. Pick PostgreSQL, Snowflake or BigQuery and the syntax adapts. Everything runs in your browser; nothing is uploaded.

Dimension tableStaging tableBusiness keyDialectUse row hash for change detection

Tracked attribute columns (comma or newline separated)

Dimension DDL

-- SCD Type 2 dimension: one row per version of each business key
CREATE TABLE dim_customer (
  sk bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id text NOT NULL,
  name text,
  email text,
  city text,
  segment text,
  row_hash text NOT NULL,
  valid_from timestamptz NOT NULL,
  valid_to timestamptz NOT NULL,
  is_current boolean NOT NULL
);

-- One current row per business key. Enforced in Postgres with a partial unique index;
-- Snowflake and BigQuery do not enforce constraints, so assert this in a dbt/DQ test.
CREATE UNIQUE INDEX dim_customer_current_uq
  ON dim_customer (customer_id) WHERE is_current;
CREATE INDEX dim_customer_bk_valid_idx
  ON dim_customer (customer_id, valid_from DESC);

Load SQL (two-step + MERGE)

-- Step 1: close out current rows whose tracked attributes changed
UPDATE dim_customer AS d
SET valid_to = now(),
    is_current = false
FROM stg_customer AS s
WHERE d.customer_id = s.customer_id
  AND d.is_current
  AND (d.row_hash <> md5(COALESCE(CAST(s.name AS text), '') || '|' || COALESCE(CAST(s.email AS text), '') || '|' || COALESCE(CAST(s.city AS text), '') || '|' || COALESCE(CAST(s.segment AS text), '')));


-- Step 2: insert a new version for changed keys AND for brand-new keys
INSERT INTO dim_customer (customer_id, name, email, city, segment, row_hash, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.email, s.city, s.segment, md5(COALESCE(CAST(s.name AS text), '') || '|' || COALESCE(CAST(s.email AS text), '') || '|' || COALESCE(CAST(s.city AS text), '') || '|' || COALESCE(CAST(s.segment AS text), '')), now(), TIMESTAMP '9999-12-31 00:00:00', true
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;

-- Single-statement MERGE alternative (Snowflake / BigQuery / Postgres 15+):
MERGE INTO dim_customer AS d
USING (
  SELECT s.*, md5(COALESCE(CAST(s.name AS text), '') || '|' || COALESCE(CAST(s.email AS text), '') || '|' || COALESCE(CAST(s.city AS text), '') || '|' || COALESCE(CAST(s.segment AS text), '')) AS row_hash
  FROM stg_customer AS s
) AS s
ON d.customer_id = s.customer_id AND d.is_current
WHEN MATCHED AND d.row_hash <> s.row_hash THEN
  UPDATE SET valid_to = now(), is_current = false
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, email, city, segment, row_hash, valid_from, valid_to, is_current)
  VALUES (s.customer_id, s.name, s.email, s.city, s.segment, md5(COALESCE(CAST(s.name AS text), '') || '|' || COALESCE(CAST(s.email AS text), '') || '|' || COALESCE(CAST(s.city AS text), '') || '|' || COALESCE(CAST(s.segment AS text), '')), now(), TIMESTAMP '9999-12-31 00:00:00', true);
-- NOTE: a MERGE can only act once per matched row, so the closed-out key still needs
-- its new version inserted. Run Step 2's INSERT after the MERGE, or keep the two-step form.

Notes & pitfalls

• Grain: one row per (customer_id, version). Query the current view with WHERE is_current, and point-in-time with WHERE ts >= valid_from AND ts < valid_to.

• row_hash compares all tracked attributes in one equality check — much cheaper than a long IS DISTINCT FROM chain, and it makes adding a column a one-line change.

• valid_to uses 9999-12-31 rather than NULL so BETWEEN-style range predicates work without OR valid_to IS NULL, and so a unique index on the range stays simple.

• Run the load in one transaction: if Step 1 commits and Step 2 fails, the key has no current row at all.

• Deduplicate the staging table first — two rows for the same business key in one batch will both be treated as 'the' new version.

• Type 1 (overwrite) vs Type 2 (new row): only put slowly-changing, history-worthy attributes in the tracked list. A column that changes daily will explode the dimension.

Do more than scd type 2 sql generator — meet Chat2DB

Chat2DB is an AI-powered SQL client for Windows, macOS and Linux. Write SQL in natural language, format and optimize queries automatically, and manage MySQL, PostgreSQL, Oracle and 20+ other databases in one workspace.

How to use

Enter the dimension table, the staging table holding the incoming snapshot, and the business (natural) key column.
List the attributes whose changes should create a new version, and choose your SQL dialect.
Copy the generated DDL and the load SQL, and read the notes for the NULL, transaction and deduplication pitfalls.

Frequently asked questions

What is a slowly changing dimension Type 2?

A Type 2 dimension keeps history by adding a new row each time a tracked attribute changes, instead of overwriting the old value. Each row carries a surrogate key, the business key, a validity range (valid_from / valid_to) and an is_current flag. Facts join to the surrogate key that was current when the event happened, so a sales report from last year still shows the customer's segment as it was then. Type 1 simply overwrites and keeps no history; Type 3 stores only a single 'previous value' column.

Should I use a row hash or compare columns to detect changes?

A row hash (MD5 over the concatenated tracked attributes) is one equality check no matter how many columns you track, which keeps the load SQL short and makes adding a column a one-line edit. Compare columns individually when you need to know which attribute changed, or when the warehouse charges for the hash computation over very wide rows. If you do compare columns, use IS DISTINCT FROM rather than <>: plain <> returns NULL when either side is NULL, so a value changing to or from NULL would never be detected.

Why does a single MERGE statement not fully implement SCD Type 2?

Because MERGE acts at most once per matched target row. Closing the old version is an UPDATE on the matched row, and inserting the new version is a second action on the same source row — most engines will not do both. The reliable pattern is two statements in one transaction: UPDATE the current rows that changed to set valid_to and is_current = false, then INSERT new versions for changed keys and brand-new keys. The generator emits both forms so you can compare. Chat2DB lets you run and inspect these statements against your warehouse — download it at https://chat2db.ai/download or use the web version at https://app.chat2db.ai.