Slowly Changing Dimension: Choose and Build Type 2

A slowly changing dimension (SCD) is a dimension table in a data warehouse whose attributes are mostly stable but change from time to time, such as a customer’s city or a supplier’s state. The SCD type is the rule for what happens to the old value when a change arrives, and you pick it per attribute: use Type 2, which adds a new row with effective dates, when reports must show the value as it was at the time of each fact, and use Type 1, which overwrites the value, when they never do. Type 3 adds a column for the previous value, and Type 4 moves history into a separate table.

This guide is for data engineers and analytics engineers deciding which SCD type to give a dimension attribute, and how to build and query it. It closes the gap between the type definitions and working code: a decision table for Types 0 to 7, Type 2 in plain SQL, with a Delta Lake MERGE, and with dbt snapshots, and a query that reads a Type 2 dimension as of a date. It also shows a real run in which Loglune, a feature-flag platform, lays out flag versions as Type 2 rows. It does not cover how to build dimensions in a specific BI tool such as Power BI.

What is a slowly changing dimension, and why does the type matter?

A slowly changing dimension is a dimension whose attribute values change rarely and unpredictably, compared with the facts that reference it. It stores data that is generally stable but may change over time. Ralph Kimball explains the name in Slowly Changing Dimensions (August 21, 2008): the changes arrive “unexpectedly, sporadically and far less frequently than fact table measurements”.

The type matters when facts are reported by a dimension attribute. An example makes this concrete: customer 1042 moves from New York to Chicago. If the dimension row is simply updated, last quarter’s sales for that customer now appear under Chicago, and a report of New York revenue for last quarter returns the wrong number. The SCD type is the decision that prevents or accepts that outcome.

According to Kimball Group Design Tip #152 (Margy Ross, February 5, 2013), Ralph Kimball introduced SCD attributes in 1996. The type numbers are a shared vocabulary, so “SCD2” or “SCD Type 2” means the same pattern in dbt, Delta Lake, Microsoft Fabric, and most warehouse tools.

Slowly changing dimension types at a glance

There are eight commonly cited SCD types, numbered 0 to 7, and Types 1, 2, and 3 are the basic ones. The table summarizes what each type does with a change.

Type	What happens on a change	History kept	Typical use
Type 0	Nothing; the original value is retained	Original value only	Date of birth, original credit score, most date dimension attributes
Type 1	The old value is overwritten	None	Correcting errors, attributes nobody reports on historically
Type 2	A new row is added with a new surrogate key and effective dates	Full	Attributes that reports group or filter by over time
Type 3	The previous value moves to an extra column	One prior value	Before-and-after comparisons
Type 4	History table (Wikipedia, Weld) or mini-dimension (Kimball)	Full, in a separate table	Large or volatile dimensions
Type 5	Type 4 mini-dimension plus a Type 1 outrigger key	Full plus current profile	Reporting by current profile values
Type 6	Type 2 rows plus current-value columns overwritten as Type 1	Full plus current value on every row	Reporting by either historical or current value
Type 7	Dual Type 1 and Type 2 keys in the fact table	Full plus current view	Same as Type 6, using two keys

Type 4 has two meanings in the sources. One meaning is “add history table”: one table holds the current data and another holds a record of some or all changes, and Weld and ThoughtSpot use this meaning. Design Tip #152 titles Type 4 “Add Mini-Dimension”, where volatile attributes are split into a separate mini-dimension. When someone says “SCD Type 4”, ask which of the two they mean.

Different attributes in the same dimension can use different types. Weld’s guide observes that most teams use a mix, with Type 1 for non-critical fields and Type 2 or Type 4 for the dimensions that matter.

How to choose an SCD type

Choose the SCD type per attribute by asking whether reports need the value as it was when each fact happened. If they do, use Type 2. If they never do, use Type 1. If they need just the previous value, Type 3 is enough.

Question about the attribute	Type
Is the value fixed forever by definition, such as an original score?	Type 0
Is the change a correction of bad data, or is history irrelevant?	Type 1
Do reports group or filter facts by the value at the time?	Type 2
Do users need the current value and one prior value side by side?	Type 3
Is the dimension large, with frequent changes, and are current-state queries critical?	Type 4 (history table)
Do users need both historical and current values for the same facts?	Type 6 or Type 7

ThoughtSpot frames this as a business decision as much as a technical one, and SaaS use cases include tracking subscription plan changes over time for churn analysis. Regulation can make the choice for you: Kimball notes that Type 1 may be outlawed where compliance applies.

The limit of any table like this is that the same attribute can need different answers for different reports. In that case, a Type 6 or Type 7 design, or a Type 2 dimension with a current-value view, avoids building two dimensions.

Type 0 and Type 1: retain or overwrite

Type 0 never changes the value, and Type 1 overwrites it, so neither keeps a history of changes. Type 0 fits attributes labeled “original”, which Design Tip #152 describes as values that facts should always be grouped by in their original form. In short, SCD0 retains the original value, SCD1 overwrites the old value and loses history, and SCD2 adds a row per change and keeps it.

Type 1 is the default in many warehouses because it is the simplest. Kimball’s 2008 article notes that “most data warehouses start out with Type 1 as the default”, and ease of maintenance is its advantage. In a supplier example, supplier ABC (Acme Supply Co) moves from CA to IL, and the single row’s Supplier_State is overwritten from CA to IL. The SQL is one statement:

UPDATE dim_customers
SET city = 'Chicago'
WHERE customer_id = 1042;

The limits are about history and aggregates. Kimball writes that Type 1 “destroys the history of a particular field”, that aggregates grouped by the changed field must be recomputed, and that in environments subject to regulatory or legal compliance, Type 1 changes “may be outlawed”, so Type 2 must be used. Use Type 1 for corrections, not for changes that really happened in the business.

SCD Type 2: add a new row for each change

SCD Type 2 keeps full history by inserting a new row whenever a tracked attribute changes, closing the previous row with an end date, and giving the new row its own surrogate key. Each fact then points at the row that was current when the fact happened, so historical reports keep the value from that time. Each row carries a valid-from date, a valid-to date, and usually a current flag, so a query can pick the row that was valid on any date. Weld calls Type 2 the most widely used SCD approach, and the DEV Community article states that most analytics organizations use Type 2 as the default and Type 1 for error corrections.

SCD Type 2 columns

A Type 2 dimension adds housekeeping columns to the business attributes. Kimball’s Slowly Changing Dimensions, Part 2 (September 2008) lists five extra fields, and ThoughtSpot’s guide to slowly changing dimensions uses the same ideas under different names:

Column	Purpose	Notes from the sources
Surrogate key (customer_key)	Identifies one version of the member	Kimball: “You must make a new surrogate primary key whenever you process a Type 2 change”
Natural key (customer_id)	Identifies the member across versions	Appears on several rows
Valid from (effective_date, Start_Date)	When this version starts	Kimball calls it the begin-effective-datetime
Valid to (expiry_date, End_Date)	When this version ends	Equal to the next version’s valid-from; NULL or a high date such as 9999-12-31 for the current row
Current flag (is_current)	Marks the current version	Lets current-state queries avoid date logic
Change reason	Why the row was added	Optional text field in Kimball’s list

A supplier example shows the effective-date form. The CA row has Start_Date 2000-01-01 and End_Date 2004-12-22, the IL row starts on 2004-12-22 with a NULL end date, and the null end date marks the current row. Kimball’s rule for the boundary is strict: a record’s end-effective-datetime “must be exactly equal to the begin-effective-datetime of the next change” for the same member.

Implementing SCD Type 2 in SQL

A Type 2 change in plain SQL is two statements in one transaction: close the current row, then insert the new version. The sketch below uses dim_customers columns, where the original row for customer 1042 gets expiry_date 2025-03-01 and the new Chicago row gets surrogate key 5001:

-- 1. Close the current version
UPDATE dim_customers
SET expiry_date = '2025-03-01',
    is_current = FALSE
WHERE customer_id = 1042
  AND is_current = TRUE;

-- 2. Insert the new version with a new surrogate key
INSERT INTO dim_customers (customer_key, customer_id, city, effective_date, expiry_date, is_current)
VALUES (5001, 1042, 'Chicago', '2025-03-01', '9999-12-31', TRUE);

The UPDATE marks the original row as expired. In a real load, you compare incoming rows with the current rows first, and hashing all tracked columns into one fingerprint is a common way to detect changes instead of comparing columns one by one.

SCD Type 2 with MERGE in Delta Lake

Delta Lake documents a single MERGE that performs the close-and-insert pair. In Table deletes, updates, and merges, the “SCD Type 2 operation into Delta tables” example stages the updates twice: once with the customer ID as mergeKey, and once with NULL as mergeKey for customers whose address changed. Rows that match close the current row, and rows with a NULL key never match, so they are inserted as the new version:

• whenMatchedUpdate, when customers.current = true AND customers.address <> staged_updates.address: set current to false and endDate to the staged row’s effectiveDate.
• whenNotMatchedInsert: insert the customer ID, the new address, current = true, the effectiveDate, and endDate = null.

Each changed customer therefore appears twice in the staged source: the copy keyed by customer ID matches and closes the current row, and the copy with a NULL key matches nothing and becomes the new row. In words, old records are closed out by updating their end date.

SCD Type 2 with dbt snapshots

dbt snapshots generate Type 2 rows from a mutable source table without hand-written merge logic. The dbt snapshots documentation states that “Snapshots implement type-2 Slowly Changing Dimensions over mutable source tables”. A snapshot adds dbt_valid_from and dbt_valid_to columns, and dbt_valid_to is NULL for current rows unless you set dbt_valid_to_current (dbt v1.9 and later), for example to 9999-12-31. The docs’ YAML example for dbt v1.9 and later:

snapshots:
  - name: orders_snapshot_timestamp
    relation: source('jaffle_shop', 'orders')
    config:
      schema: snapshots
      unique_key: id
      strategy: timestamp
      updated_at: updated_at

The timestamp strategy compares an updated_at column with the last snapshot run and is the recommended strategy. The check strategy compares a list of columns (check_cols) for sources without a reliable timestamp. The hard_deletes config handles rows deleted at the source, with the options ignore, invalidate, and new_record.

Snapshots have two limits worth planning for. A snapshot sees only the state at each run, so a value that changes twice between runs keeps just the last one. The docs also state that “Snapshots can’t be rebuilt”, and recommend a separate schema so users know snapshots are special.

Querying an SCD Type 2 dimension as of a date

To read a Type 2 dimension as of a date, select the row whose valid-from is at or before the date and whose valid-to is after it. An example puts customer 101 in NY from 2022-01-01 to 2024-03-15 and in SF from 2024-03-15 to 9999-12-31, and answers “Where did customer 101 live on 2023-06-01?” with the NY row, where valid_from <= 2023-06-01 < valid_to. The same query on the supplier table:

SELECT supplier.supplier_code, supplier.supplier_state
FROM supplier
WHERE supplier.supplier_code = 'ABC'
  AND supplier.start_date <= '2012-01-01T00:00:00'
  AND supplier.end_date > '2012-01-01T00:00:00';

For current values, filter on the current flag instead. For reports that join facts to the dimension by date rather than by surrogate key, join on delivery.delivery_date >= supplier.start_date AND delivery.delivery_date < supplier.end_date. The half-open range (inclusive start, exclusive end) is what makes Kimball’s rule work: when one row’s end equals the next row’s start, each instant matches exactly one row.

The limit is query complexity and size. The table grows with every change, and every query must either filter on is_current or join on date ranges.

Type 3: add a previous-value column

SCD Type 3 keeps one level of history by moving the old value into a separate column on the same row. For example, previous_city sits next to city, which supports before-and-after comparisons. A supplier version adds Original_Supplier_State next to Current_Supplier_State, and a variant uses Previous_Supplier_State to keep the most recent prior value. The same idea in SQL:

ALTER TABLE dim_customers ADD COLUMN previous_city VARCHAR(100);
UPDATE dim_customers
SET previous_city = city, city = 'Chicago'
WHERE customer_id = 1042;

Kimball’s Part 2 article describes Type 3 as a way to support “alternate realities”, such as reporting by both an old and a new category assignment during a reorganization. The limit is depth. An example of Alice moving from NY to SF to LA shows that the NY value is lost after the second move, because the table has one column for prior values.

Type 4: history table or mini-dimension

In the history-table meaning, SCD Type 4 keeps the main dimension with current values only and writes every change to a separate append-only history table. The history table is a separate, append-only table that captures every version of a record over time, and a supplier example pairs a Supplier table with a Supplier_History table that carries a Create_Date. This method resembles how database audit tables and change data capture techniques function; for the audit-table side, see the audit logging guide.

The reason to choose it is fast current-state queries next to a complete history. A product price example reads a Shopify product_variant_history table and uses LAG() to list each price change with the previous price, which is a question a Type 1 table cannot answer.

The limit is that facts no longer point at a version row directly, so point-in-time joins have to go through the history table. In Kimball’s numbering, the same “Type 4” label instead means splitting volatile attributes into a mini-dimension, which is a different technique with a different purpose.

Types 5, 6, and 7: hybrid SCD types

Types 5, 6, and 7 combine the basic types so that facts can be reported by either the historical value or the current value. Design Tip #152 describes them as hybrids that “provide more analytic flexibility, albeit with greater complexity”.

• Type 5 builds on the Type 4 mini-dimension by embedding a current-profile mini-dimension key in the base dimension, overwritten as a Type 1 attribute (4 + 1 = 5).
• Type 6 adds current-value columns to a Type 2 dimension and overwrites them on every row of the member. It combines Types 1, 2, and 3 (1 + 2 + 3 = 6), with columns such as Current_State, Historical_State, and Current_Flag.
• Type 7 keeps a Type 2 dimension and gives the fact table both the surrogate key and the durable natural key, so one join gives historical values and the other gives current values.

Use these when users regularly ask for both “as it was” and “as it is now” in the same report. Otherwise, Type 2 with a current flag usually answers both questions with less maintenance.

How to implement slowly changing dimensions without breaking history

A working SCD pipeline needs reliable change detection, trustworthy timestamps, and validity periods that never overlap. The following steps are a practical order to follow:

1. Audit what you have: which dimension tables exist, which ones change, and what their natural keys are.
2. Prioritize dimensions that change but are currently overwritten, such as a customer segment or product pricing.
3. Choose the SCD type for each attribute.
4. Decide on legacy data: start fresh, or backfill history from snapshots or audit logs.
5. Implement the logic in the pipeline: detect changes, add metadata columns, and manage effective dates.

The common pitfalls come from time, not from SQL syntax:

• Overlapping periods. The ETL must ensure there are no overlaps in the time periods for each distinct item of reference data. An overlap makes an as-of query return two rows.
• Inconsistent timestamps. ThoughtSpot advises using system-generated fields from the source, because otherwise “you risk gaps, overlaps, and broken history”. The valid-from must be the time the change took effect in the business, which is why bitemporal data keeps that valid time apart from the time the warehouse recorded the row.
• Missing surrogate keys. Fact loads must look up the surrogate key of the version that was current at the fact’s date, which Kimball calls the surrogate key pipeline.
• Retroactive changes. A retroactive change, or a new attribute with a different effective date, can require existing transactions to be updated.
• Snapshot gaps. A daily dbt snapshot misses changes that are reverted within the day.

Batch ETL differs from change data capture (CDC), which reads changes from source systems as they happen and suits high-frequency updates and Type 2 tracking.

Slowly changing dimensions vs temporal tables and change data capture

An SCD is a modeling pattern you maintain in the warehouse load, while a temporal table is a database feature that keeps row versions for you. The temporal database guide explains SQL:2011 system-versioned tables and FOR SYSTEM_TIME AS OF queries. Apache Iceberg’s FOR SYSTEM_TIME AS OF does not replace SCD Type 2: table time travel shows what the table contained at a past time, while a Type 2 dimension records when each attribute value was valid in the business and gives each version a key that facts can reference.

Change data capture and event logs sit upstream of both. Weld’s comparison table contrasts history tables (periodic sync snapshots, full record per sync, latency in minutes) with CDC (real-time event stream, field-level changes, latency in seconds). An event log can rebuild any of these views, and the event sourcing guide covers rebuilding state by replaying events.

Example: reading feature flag history like an SCD Type 2 table

Feature flag configuration has the same shape as a Type 2 dimension: one flag key, several versions over time, and the question “which version was live when this happened”. When a customer reports that a pricing page looked wrong on August 20, the useful answer is the flag version live on August 20, not the version live today. Loglune keeps every flag edit as a change event and defines the state at time T as the state rebuilt from the history up to T. Publishing a draft creates an immutable version with a number that increases monotonically and an optional note, and the version list returns the lineage with the current version marked.

The run below used Loglune’s shipped flag store code on a local Cloudflare D1 database in Vitest, on October 6, 2026. The flag new-pricing-page is on for customers whose plan is in a list. The data is an editorial example, not a customer case:

1. July 1, 2026: an editor creates the flag for enterprise at 09:00 UTC, and version 1 is published at 10:00 with the note “Enterprise only”.
2. August 1, 2026: the list becomes pro and enterprise at 09:00, and version 2 is published at 10:00 with the note “Add pro”.
3. September 1, 2026: the list becomes free, pro, and enterprise at 09:00, and version 3 is published at 10:00 with the note “All plans”.

The test script then listed the versions, printed them in a Type 2 layout, asked for the version live at five times, and diffed version 1 against version 3. This is the output, unedited:

publish version=1 publishedAt=2026-07-01T10:00:00.000Z
publish version=2 publishedAt=2026-08-01T10:00:00.000Z
publish version=3 publishedAt=2026-09-01T10:00:00.000Z
--- version list (as returned, newest first)
version=3 publishedAt=2026-09-01T10:00:00.000Z label="All plans" isCurrent=true
version=2 publishedAt=2026-08-01T10:00:00.000Z label="Add pro" isCurrent=false
version=1 publishedAt=2026-07-01T10:00:00.000Z label="Enterprise only" isCurrent=false
--- SCD Type 2 layout: version | valid_from | valid_to | is_current | plans
1 | 2026-07-01T10:00:00.000Z | 2026-08-01T10:00:00.000Z | false | ["enterprise"]
2 | 2026-08-01T10:00:00.000Z | 2026-09-01T10:00:00.000Z | false | ["pro","enterprise"]
3 | 2026-09-01T10:00:00.000Z | null | true | ["free","pro","enterprise"]
--- as-of-T lookups
as of 2026-06-30T00:00:00.000Z: null (no version live)
as of 2026-07-15T00:00:00.000Z: version=1 publishedAt=2026-07-01T10:00:00.000Z plans=["enterprise"]
as of 2026-08-01T09:30:00.000Z: version=1 publishedAt=2026-07-01T10:00:00.000Z plans=["enterprise"]
as of 2026-08-20T00:00:00.000Z: version=2 publishedAt=2026-08-01T10:00:00.000Z plans=["pro","enterprise"]
as of 2026-09-15T00:00:00.000Z: version=3 publishedAt=2026-09-01T10:00:00.000Z plans=["free","pro","enterprise"]
--- diff version 1 -> version 3 (flag new-pricing-page)
flag new-pricing-page change=changed plans before=["enterprise"] after=["free","pro","enterprise"]
segments changed=0 findings=[]

How the output maps to SCD Type 2 terms:

• The version number plays the role of the surrogate key, the flag key new-pricing-page plays the role of the natural key, the publish time is the valid-from, and isCurrent is the current flag.
• The valid_to column is not stored by Loglune. The test script derived it from the next version’s publish time, which is exactly Kimball’s rule that one version’s end equals the next version’s begin. Because the versions are numbered in publish order, the periods cannot overlap.
• The as-of lookups follow the half-open range. On July 15 and August 20, each time falls inside exactly one row’s range. Before the first version, on June 30, the answer is null, and Loglune’s docs map a reproduction request with no version at T to a 404.
• On August 1 at 09:30 UTC, the answer is still version 1. The edit to add pro was recorded at 09:00, but version 2 was not published until 10:00. A query on the edit time would have said pro was included while customers were still served the enterprise-only rule, which is the timestamp pitfall ThoughtSpot warns about: the valid-from must be the time the change took effect.
• The diff between version 1 and version 3 shows the plan list going from ["enterprise"] to ["free","pro","enterprise"], with no segment changes and no findings from the publish guard, Loglune’s check on a version’s contents.

Loglune is not a data warehouse tool. It does not build dimension tables, run MERGE statements, or export SCD rows. It applies the Type 2 way of reading history to the flag versions it stores: the projection shows the version at T with its notes, and the diff compares two versions. Both are read-only operations that leave stored data and delivery unchanged and do not count toward billing. A reproduction from time T, which builds a reproduction version from the state at T, is a separate operation and counts as one reproduction. Version history is kept for a limited time on some plans; see pricing before relying on a lineage that spans months.