Templates in development

Written for Databricks, Snowflake and Fabric and tested on DuckDB. None has run on a real platform yet, and none is released.

SCD Type 2

SCD Type 2: history, versioned.

Keep every version of a dimension record with its own validity range, so a report shows the value that was true at the time. One current row per key, with no overlaps and no gaps.

Template in development
Sync and history
Databricks first
Platform notesSQL sketch
ShareLinkedInXRedditBluesky

Shape card

04 / 10

  1. Source
  2. Bronze
  3. Silver (used)
  4. Gold
  5. Apps
Group
Sync and history
Works across
Silver
Reads
Current dimension records
Writes
A dimension with every version
Slots
8 typed slots
Tested by
Golden tests on DuckDB
Platforms
Databricks first, then Snowflake and Microsoft Fabric

Overview

What SCD Type 2 does.

Keep the full history of a dimension, with a validity range for every version.

What it does

When a tracked attribute of a record changes, SCD Type 2 closes the current version by setting its end date and inserts a new version with a new start date and a current flag. Each version gets its own surrogate key, so a fact can point to the version that was true when it happened.

Columns you do not track are updated in place, as in SCD Type 1, so a corrected typo does not create a new version. Open-ended rows are marked with a far-future end date or a null, as you choose.

Use it when

  • Dimensions where reports must show the value that was true at the time: a customer's region, a product's category, an account's owner.
  • Audits and regulatory reports that must reproduce a past state.
  • Any table where the question is what you knew on a given date.

Use another shape when

  • Only the latest value matters, and an overwrite (SCD Type 1) is enough:

    03CDC merge

  • You need a full copy of a table as of a date, not a history per record:

    05Snapshot

  • The source still has duplicates and mixed types to clean up first:

    08Dedupe / conform

Slots

Inputs, outputs and slots.

What the SCD Type 2 template reads, what it writes and the typed slots it asks you for. The agent fills most slots from your catalog and your request.

In

Inputs

  • Current dimension records from a source or silver table, with an effective date
  • The existing dimension table with its versions

SCD Type 2

  1. Source
  2. Bronze
  3. Silver (used)
  4. Gold
  5. Apps

Out

Outputs

  • Closed versions, each with its end date
  • New current versions, each with its own surrogate key
  • Untracked columns updated in place

Template slots

8 slots · names and types may change before release

source_table
table
The current records, one row per business key.
target_table
table
The dimension table that keeps the versions.
business_key
columns
The columns that identify the real-world record.
tracked_columns
columns
The attributes whose change creates a new version.
effective_date
expression
Where a version's start date comes from: a source timestamp or the load time.
open_end_value
expression
How open-ended rows are marked, for example 9999-12-31 or null.
current_flag
column
The boolean column that marks the current version.
surrogate_key
enum
How each version's key is made: an identity column, a sequence or a hash.

Slot types are identifier, table, column, columns, expression, expressions, string, integer, boolean, duration and enum. What is a template slot.

Testing

Tested before it ships.

Every template comes with golden tests that run on DuckDB on your machine before anything reaches your platform.

How a run goes

The golden test changes attributes across several runs and then checks the dimension's invariants, not only a few values.

  1. 01Fixtures go in: small tables with the hard cases for the shape.
  2. 02The template runs on DuckDB on your machine, with no cluster and no warehouse compute.
  3. 03The result is compared with the expected output, row for row.
  4. 04Green can deploy. Red sends the agent back to the template.
What is a golden testGolden tests in the docs

What the SCD Type 2 test checks

  1. Case 01

    Run 1 loads three customers. Run 2 changes one customer's region. Run 3 changes it back. That customer ends with three versions and exactly one current.

  2. Case 02

    A change in an untracked column updates the current row and adds no version.

  3. Case 03

    A change from a value to null, and from null to a value, creates a version.

  4. Case 04

    Every key has exactly one current version, and no two versions of a key overlap or leave a gap.

  5. Case 05

    Re-running the same load adds no version.

Each template is also judged per platform: yellow when it passes on DuckDB, green when it also passes on the real platform. Nothing is released, so no template has a green mark yet.

Platforms

Written for the platform you run.

The same shape has a different correct form on each platform. This is what the SCD Type 2 template uses on each one, and the platform facts that decide it.

Databricks

In development

The Databricks template uses MERGE INTO on Delta tables, or the SCD Type 2 option of the change-data APIs in Lakeflow Declarative Pipelines.

  • The MERGE form feeds one statement a union of rows that close the current version and rows that open a new one. The sketch below shows the idea.
  • The AUTO CDC flow can store a target as SCD Type 2, add the start and end columns for you and track history on a chosen set of columns.
  • Identity columns are an option for surrogate keys, but they restrict concurrent writes to the table. A hash of the business key and the start date avoids that.
  • Facts join to the version valid at the event time with a range join on the start and end columns.
Emolysis for Databricks

Snowflake

Planned

The Snowflake template uses MERGE fed by a stream on the staging table, run by a task.

  • The stream captures the staged changes, and the task runs the MERGE when the stream has data, so the dimension updates without a separate scheduler.
  • Surrogate keys can come from a sequence, an autoincrement column or a hash. The surrogate_key slot decides.
  • Unquoted identifiers fold to uppercase, so tracked column names match without regard to case unless they are quoted.
  • A task is created suspended. The deploy script has to resume it, and the template includes that step.
Emolysis for Snowflake

Microsoft Fabric

Planned

The Fabric template uses MERGE on lakehouse Delta tables from a notebook.

  • The notebook runs the same two-part MERGE as on Databricks against a Delta table in OneLake.
  • A Data Pipeline schedules the notebook after the staging load has finished.
  • In a warehouse, T-SQL UPDATE and INSERT statements in one transaction give the same result. Check which statements your warehouse supports, because its T-SQL surface differs from SQL Server's.
  • Reports read the dimension through the SQL analytics endpoint like any other table, filtering on the current flag or joining facts on the validity range.
Emolysis for Microsoft Fabric

Whatever the platform, the template is tested on DuckDB first. The platform notes describe each product in general terms, so check the details against the version you run.

Mistakes

Mistakes it prevents.

The failures that most often hit a pipeline of this shape in production. Each one is a case in the template's golden test or a rule in its design.

  1. Overlapping validity ranges

    Two versions that both claim the same date make a fact join twice and get counted twice. The template closes a version at the start of the next, and the golden test checks every key for overlaps and gaps.

  2. Two current rows for one key

    A re-run or a concurrent load can open a second current version. The template checks for exactly one current row per key, and so does its test.

  3. A re-run that creates duplicate versions

    Loading the same change again inserts the same version again. The template compares against the current version first, so an unchanged record adds nothing.

  4. Comparing nullable columns with a plain equals sign

    NULL = NULL is not true, so a change to or from null is missed, or every null row looks changed on every run. The template compares with a null-safe operator.

  5. Tracking every column

    Each irrelevant change creates a version and the dimension grows without bound. The tracked_columns slot makes the choice explicit, and everything else updates in place.

SQL sketch

The idea in SQL.

Close the current version, open a new one

Illustrative sketch in generic SQL. The surrogate key is left to the table's default. The platform templates use each platform's merge syntax and key strategy.

Illustrative sketch · SQL
-- Business key: customer_id. Tracked columns: region and tier.
MERGE INTO gold.dim_customer AS d
USING (
  -- Rows that match the current version by key: they close it, or add a brand-new key.
  SELECT s.customer_id AS merge_key, s.customer_id, s.region, s.tier, s.effective_at
  FROM staged.customers AS s

  UNION ALL

  -- Rows for keys whose tracked columns changed: a NULL merge key never matches,
  -- so these always insert the new version.
  SELECT NULL AS merge_key, s.customer_id, s.region, s.tier, s.effective_at
  FROM staged.customers AS s
  JOIN gold.dim_customer AS d
    ON d.customer_id = s.customer_id AND d.is_current
  WHERE s.region IS DISTINCT FROM d.region
     OR s.tier IS DISTINCT FROM d.tier
) AS u
  ON d.customer_id = u.merge_key AND d.is_current
WHEN MATCHED AND (u.region IS DISTINCT FROM d.region OR u.tier IS DISTINCT FROM d.tier) THEN
  UPDATE SET valid_to = u.effective_at, is_current = FALSE
WHEN NOT MATCHED THEN
  INSERT (customer_id, region, tier, valid_from, valid_to, is_current)
  VALUES (u.customer_id, u.region, u.tier, u.effective_at, DATE '9999-12-31', TRUE);

Related

Shapes that work alongside it.

Real pipelines are several shapes in a row. These are the ones that most often sit next to this one.

03

Sync and history

CDC merge

To keep the current state of a table, or to feed the history.

Explore CDC merge
07

Repair

Late-arriving

For facts that arrive after the version they belong to has changed.

Explore Late-arriving
09

Model and serve

Aggregation / gold

To report on facts joined to the version valid at the time.

Explore Aggregation / gold

Glossary

Keep reading

FAQ

Questions about SCD Type 2.

What is SCD Type 2?

A slowly changing dimension of Type 2 keeps the full history of a record. When a tracked attribute changes, the current row is closed with an end date and a new row is added with a new start date, so every version stays queryable. Reports can then show the value that was true at the time of each fact.

How do I implement SCD Type 2 with MERGE on Databricks?

The usual form builds one source with two kinds of rows. Rows keyed by the business key match the current version and close it. Rows with a null key never match, so they insert the new version. The sketch on this page shows the pattern. The Databricks template writes it for Delta tables, and the AUTO CDC flow in Lakeflow Declarative Pipelines offers an SCD Type 2 option as an alternative.

SCD Type 2 or SCD Type 1?

Use Type 1 when only the latest value matters, such as fixing a misspelled name, because it updates the row in place and keeps no history. Use Type 2 when a report must show what was true at the time. Many dimensions mix both, which is why the template takes the tracked columns as a slot.

How do facts join to the right version?

A fact joins to the dimension on the business key and on its event time falling inside the version's validity range: event time at or after the start, and before the end. Because the template prevents overlaps and gaps, each fact matches exactly one version.

In development · Not released

Test first, then deploy.

The SCD Type 2 template is written for Databricks, Snowflake and Microsoft Fabric, and its golden tests pass on DuckDB. Nothing is released yet. Early access is by invitation, for teams that want to help shape it.

Apply for early accessAll ten shapes