Slowly Changing Dimension: SCD Type 1, 2, 3—What to Use

If you need a quick answer: use type 1 when you only care about the current state and can overwrite; use type 2 when you must keep full history and time-travel your reports; use type 3 when you only need the current value plus the previous value (limited history). Type 0 freezes values, type 4 shifts history to a separate table, and type 6 is a hybrid for both convenience and auditability. For large dimensions, pick the simplest scd type that meets your reporting rules. If your fact table recorded sales while a customer lived in New York, and that customer moved to Chicago, type 2 will show New York for last quarter; type 1 will show Chicago everywhere; type 3 can show both current and previous depending on the column you query.

What is an slowly changing dimension and their types?

A slowly changing dimension is a dimension whose attributes change slowly over time. In a dimensional model, the dimension table (e.g., customer, product, store) describes entities, while the fact table records numeric events. Different scd types define how we store and query those changes in the data warehouse so analytics stays consistent as attributes change over time.

The common types of slowly changing dimensions:

• Type 0: No changes allowed; the record is fixed.
• Type 1: Overwrite with the current value; no history kept.
• Type 2: Create a new row per change with effective dates; full history kept.
• Type 3: Keep current and previous value in the same row; limited history.
• Type 4: Separate history table; current table stays slim.
• Type 6: Hybrid of type 1 and type 2 and type 3 combined.
• Type 7: Hybrid patterns emphasizing dual keys (natural + surrogate) for flexible joins.

We’ll focus on type 1, type 2, and type 3 because they’re the different scd types you’ll actually choose between most often. For type 2 depth, see the Slowly Changing Dimensions Type 2 Explained: Complete Guide.

Quick recommendation

• Choose type 1 when you only need the current state, performance and simplicity matter, and audits based on historical data are out of scope. Type 1 overwrites changed attributes in place.
• Choose type 2 when you must preserve full history, report as-of any date, or tie facts to the state at the time of the event. This requires a surrogate key, effective date ranges, and a new row on change.
• Choose type 3 when you only need to compare the current value vs a previous value (for a small set of attributes) and don’t need arbitrary time-travel.
• Consider type 4 if you want a compact current table plus a history table for archival queries (also called scd type 4).
• Consider type 6 to mix convenience (current columns) and auditability (new rows) without complicated joins. Most teams default to type 1 and type 2, but type 6 is a pragmatic middle ground.

Comparison: SCD types 1, 2, 3 (and friends)

Type 1: Overwrite to keep the current value

Type 1 is the simplest scd type: you overwrite the changed attributes in the same row. No history. If a customer city changes from New York to Chicago, the dimension row now says Chicago everywhere. That answers business questions about the current state quickly, but it cannot answer “what was this customer’s city last quarter?”

Pros:

• Fast and cheap; rows don’t grow.
• Simple sql joins; easy for dashboards and ad hoc analytics.
• Great for fixing data errors, not tracking change over time.

Cons:

• No historical data for audits or as-of analysis.
• “Type 1 overwrites” can confuse downstream users expecting time-travel.

Example MERGE to implement scd type 1:

-- Snowflake/BigQuery/Spark-flavored MERGE (simplified)
MERGE INTO dim_customer AS t
USING stg_customer AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET
  city = s.city,            -- overwrite
  state = s.state,          -- overwrite
  updated_at = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN INSERT (
  customer_id, city, state, created_at, updated_at
) VALUES (
  s.customer_id, s.city, s.state, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP()
);

When you implement scd type 1 in dbt, you can also use a straightforward incremental model. See our advice on dbt project structure for how to lay out staging and marts cleanly.

Type 2: New row with effective dates (full history)

Type 2 creates a new row when a tracked attribute changes. You add an effective date and (optionally) end date columns, a current state flag, and a surrogate key distinct from the natural key. Queries choose either the current row or the correct historical row as-of an event time. This pattern preserves full history while allowing you to reflect the current value for operational exports.

We won’t reteach all mechanics here. Read the Slowly Changing Dimensions Type 2 Explained: Complete Guide for details on keys, as-of joins, late-arriving data, and performance. Use that guide when you need scd type 2 rigor at scale.

Minimal shape for a type 2 dimension table:

dim_customer_scd2
- customer_sk (surrogate key, primary key)
- customer_id (natural key)
- city
- state
- effective_from
- effective_to
- is_current

Query patterns:

• Current row: filter is_current = true.
• As-of row: filter on order_date BETWEEN effective_from AND COALESCE(effective_to, '9999-12-31').

-- Join a fact table as-of event date (NY vs Chicago handled correctly)
SELECT f.order_id, f.order_date, d.city
FROM fact_orders f
JOIN dim_customer_scd2 d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.effective_from
 AND f.order_date < COALESCE(d.effective_to, '9999-12-31');

For dbt, dbt snapshots are the simplest way to implement slowly changing dimensions of type 2 in ELT. They detect changes and write a new record with timestamps for you.

-- dbt snapshot YAML (simplified)
snapshots:
  - name: customer_scd2
    target_schema: analytics
    strategy: timestamp
    updated_at: updated_at
    unique_key: customer_id
    source: ref('stg_customer')
    check_cols: ['city', 'state']

That snapshot will create a new row when city/state changes. You expose current vs historical with thin models. For more on organizing layers, see the dbt project structure article, and for end-user metrics, see the dbt semantic layer guide.

Type 3: Current plus previous value (limited history)

Type 3 stores the current value in its usual column and the previous value in an additional column (sometimes with a last_changed_at timestamp). It answers “what changed compared to before?” without maintaining unlimited history. To implement scd type 3, you overwrite the current column and shift the old value into the previous_* column.

Pros:

• Simple joins and filters; no date-range logic.
• Great for head-to-head comparisons (e.g., current tier vs previous tier).

Cons:

• Only the previous value is kept; if a customer changes three times, older values are lost.
• Expands columns per tracked dimension attribute.

Example update logic:

-- On detected change, shift current -> previous and overwrite current
UPDATE dim_customer
SET previous_city = city,  -- preserve old value
    city = s.city,        -- overwrite to new
    last_changed_at = CURRENT_TIMESTAMP()
FROM stg_customer s
WHERE dim_customer.customer_id = s.customer_id
  AND COALESCE(dim_customer.city, '') != COALESCE(s.city, '');

scd type 3 is attractive when product owners want a delta column on dashboards and the attribute rarely changes.

Type 0, Type 4, Type 6, Type 7 in one minute

Type 0: Freeze values. Use when the dimension data is assigned once and must never change (e.g., birth date). You still may backfill errors, but you don’t treat it as a slowly changing pattern.

Type 4: Keep the current table lean and archive all older rows in a separate history table. You’ll query the current table most of the time, and pivot to the history for investigations. It trades single-table convenience for predictable size and faster current-state dashboards. Also called scd type 4.

Type 6: A practical hybrid stacking type 1 and type 2 and type 3. Each change creates a new row (like type 2), and you also carry “current” convenience columns (like type 3) that you may overwrite for easy filters (like type 1). Analysts can pick columns for current or join by dates for full history. Use type 6 when your consumers need both current and historical in one dimension without multiple models.

Type 7: Dual-key hybrid. You maintain both a natural key and a surrogate key to support flexible joins: current joins via the natural key, historical joins via the surrogate key and dates. Helpful when you must support both styles across tools.

Decision framework: how to pick the scd type

Use this flow when the answer isn’t obvious:

1. Reporting rule: Do stakeholders want last quarter’s sales by the customer’s location at the time of sale? If yes, choose type 2 (or type 6). If they only want the current rollups, type 1 is fine. If they only want current vs previous comparisons, type 3 works.
2. Frequency of change: If the attribute rarely changes, type 3 can be enough. If it changes often, type 2 avoids losing context.
3. Table size: Your orders table has 40M rows and the customer dimension has 12M with frequent address updates. Expect type 2 to multiply rows. If cost or latency is tight, consider type 4 or type 6 to balance write/read trade-offs.
4. Tooling: If you can use dbt snapshots, type 2 is low-friction. If you are MERGE-only, ensure keys and dedup logic are solid.
5. Downstream consumers: If self-serve BI users struggle with as-of joins, provide a current view (type 1/3) and a historical view (type 2) or adopt type 6.

If you’re new to dimensional modeling or need a refresher on star schemas, read Data Warehouse Schema Design: Star Schema to Galaxy and browse the data modeling topic hub.

Implementation patterns: ETL/ELT for slowly changing dimensions

There are two common ways to implement slowly changing dimensions in modern ELT:

1. dbt snapshots (recommended for type 2): Snapshots compare source and target rows and write a new row on change with timestamps. This is the fastest way to implement slowly changing dimensions in dbt. See our snapshot playbook.
2. MERGE-based logic: Use MERGE with a staging model to detect changes and either overwrite (type 1) or insert a new row (type 2/6). Ensure a deterministic change detection strategy to avoid duplicates.

Generic MERGE for scd type 2:

MERGE INTO dim_customer_scd2 t
USING (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) AS rn
  FROM stg_customer
) s
ON t.customer_id = s.customer_id AND t.is_current = TRUE
WHEN MATCHED AND (
     COALESCE(t.city, '') != COALESCE(s.city, '')
  OR COALESCE(t.state, '') != COALESCE(s.state, '')
) THEN UPDATE SET
  is_current = FALSE,
  effective_to = s.updated_at
WHEN NOT MATCHED AND s.rn = 1 THEN INSERT (
  customer_sk, customer_id, city, state, effective_from, effective_to, is_current
) VALUES (
  GENERATE_UUID(), s.customer_id, s.city, s.state, s.updated_at, NULL, TRUE
);

Then expose two models: dim_customer_current (is_current = true) and dim_customer_history (all rows). Use dbt ref() vs source() correctly to connect staging and marts.

How SCD choices affect queries and dashboards

With type 1, your BI metrics always reflect the current value. With type 2, you must join facts as-of the event. With type 3, you select which column to display. This impacts metric definitions in your semantic layer and every query that references the dimension.

Examples:

• Current customer city (type 1 or current view of type 2): simple join on natural key; no date filter.
• Sales by city at order time (type 2): join by natural key and event date into the date-ranged rows.
• Churn uplift by previous tier (type 3): select previous_tier vs tier in the same row.

When you publish metrics, use the dbt semantic layer to codify whether a metric uses the current state or an as-of join. This prevents accidental mixing of current and historical logic across dashboards.

FAQ: SCD types explained

What is SCD type 1, 2, and 3?

Type 1 overwrites the row to keep only the current value. Type 2 inserts a new row with date ranges to keep full history. Type 3 stores both current and previous value in columns for limited history.

What is the difference between SCD type 1 and SCD type 2?

Type 1 is simpler and does not keep history; it overwrites. Type 2 maintains full history with a new row per change and requires date-range logic in queries.

What is SCD and their types?

A slowly changing dimension is a dimension that changes slowly. Types include 0, 1, 2, 3, 4, 6, and 7. See the types of scd list above.

What does "scd type 0" mean?

No changes are applied; the attribute is fixed after initial load.

Are any of them changing over time? Do any require historical tracking?

Type 2 (and type 6) explicitly track changes over time with full history. Type 3 tracks only the previous step. Type 1 does not track history.

Frequency of change: Does the data change often or rarely?

Rare changes can suit type 3 for a quick current vs previous snapshot. Frequent changes usually push you to type 2 to avoid losing context.

How do you implement slowly changing dimensions in your ETL pipeline?

In ELT with dbt, prefer snapshots for scd type 2, and MERGE for type 1 and type 3. Validate change detection and keys. This is how you implement slowly changing with minimal custom code. If your etl jobs are orchestration-first, still centralize change rules inside the warehouse.

How to implement slowly changing dimensions in a data warehouse?

Model the dimension with keys and dates, choose the scd type, and use MERGE or snapshots to manage updates. Then create current and history views and ensure your fact joins match the chosen pattern.

If a fact table recorded sales tied to that customer, what shows for last quarter: New York or Chicago?

Type 2 (and type 6) show New York for last quarter because the join is as-of the order date. Type 1 shows Chicago (current) for all periods. Type 3 allows you to pick current or previous columns.

Pitfalls, tests, and performance tips

• Late-arriving data: Backdating changes in type 2 requires careful effective_from ordering and possibly splitting one change into two rows. Reconcile with your ingest timestamps.
• Key discipline: Use a surrogate key for type 2/6. Natural keys may collide across systems. Keep the natural key as a column for tracing.
• Unique constraints: Enforce one current row per natural key. Add tests in dbt to prevent multiple current rows.
• Incremental merges: Deduplicate staging with window functions before MERGE. Track the latest update per key to avoid multiple updates in one run.
• Column drift: For type 3/6, don’t proliferate columns. Limit which dimension attribute gets a previous value column.
• Query hygiene: For as-of joins, centralize the logic in one model or macro to avoid inconsistencies across queries.
• Know your SCDs: All SCDs solve different reporting rules. Document when to use type 1, when to use type 2, and when to prefer hybrids so dimensional logic is consistent.

End-to-end example: Customer dimension with type 2

Suppose you ingest stg_customer hourly. You want last quarter’s sales by city at order time and current city for CRM exports. You choose type 2:

1. Create dim_customer_scd2 with surrogate key, effective dates, current flag.
2. Build dim_customer_current as a view filtering is_current = true.
3. Join sales to dim_customer_scd2 by customer_id and order_date for accurate history.

-- Current state export (CRM)
SELECT c.customer_id, c.city
FROM dim_customer_current c;

-- As-of analysis (analytics)
SELECT d.city, SUM(f.revenue) AS revenue
FROM fact_orders f
JOIN dim_customer_scd2 d
  ON d.customer_id = f.customer_id
 AND f.order_date >= d.effective_from
 AND f.order_date < COALESCE(d.effective_to, '9999-12-31')
GROUP BY 1;

This pattern gives you both worlds with consistent dimensional logic. If you prefer a hybrid with convenience columns, consider type 6.

When to graduate beyond 1/2/3

If your current table grows too large under type 2, push old rows into a type 4 history table while keeping a slim current view for BI. If user experience demands both current and historical side by side, adopt type 6 with carefully curated previous value columns. If you integrate multiple operational systems and need flexible joins, type 7 provides dual-key options.

Checklist before you implement SCD

• Confirm the business rule for current vs as-of reporting in writing.
• Select the scd type that fits the rule with the least complexity.
• Define keys: natural key and, when needed, a surrogate key.
• Decide which columns are tracked attributes (and which are not).
• Establish change detection: hashes or explicit comparisons.
• Write tests for one current row per key, non-overlapping date ranges, and no null keys.
• Publish a current view and a history view so queries stay simple.
• Document dimensional decisions so analysts know which joins answer which questions.

Related resources

• Slowly Changing Dimensions Type 2 Explained: Complete Guide
• Data Warehouse Schema Design: Star Schema to Galaxy
• dbt Snapshots: dbt snapshot Playbook
• dbt Project Structure: Staging, Intermediate, and Marts Done Right
• dbt Semantic Layer for Analytics Engineers
• Data Modeling topic hub

Use this as your complete guide to the types of slowly changing dimensions when deciding how to implement scd in your warehouse. Most teams will use type 1 and type 2 for 80% of needs; keep type 3, type 4, and type 6 in your toolkit for the rest. Align dimension data rules with stakeholders, prototype the sql join paths, and validate results against production reports before rolling out to production. When in doubt, prototype queries against a small subset and validate with stakeholders before you lock in the pattern.

Want hands-on practice? Try the free graded SCD exercises at /practice.