ExamFinch.com My Study

Study guide

3.7 Slowly Changing Dimensions (SCD) Patterns

Practice this exam

Databricks Certified Data Engineer Associate Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction

About the Databricks Certified Data Engineer Associate Exam Study Plan and Strategies

2 Domain 1: Databricks Intelligence Platform (10%)

1.1 The Lakehouse Architecture 1.2 Databricks Workspace and Components 1.3 Delta Lake Fundamentals 1.4 Databricks Repos, Secrets, and Development Tools 1.5 Databricks SQL and the Photon Engine 1.6 Databricks Runtime and Compute Configuration

3 Domain 2: Development and Ingestion (30%)

2.1 Auto Loader (cloudFiles) 2.2 Reading Data with Spark SQL and PySpark 2.3 Writing Data to Delta Tables 2.4 Spark SQL Query Fundamentals 2.5 Complex Data Types and Higher-Order Functions 2.6 User-Defined Functions (UDFs) 2.7 Structured Streaming Fundamentals 2.8 Databricks Notebooks and Code Execution 2.9 Data Extraction from External Sources 2.10 Table Management and Properties

4 Domain 3: Data Processing & Transformations (31%)

3.1 The Medallion Architecture (Bronze, Silver, Gold) 3.2 Delta Lake Optimization: OPTIMIZE, VACUUM, and Liquid Clustering 3.3 Advanced SQL Transformations 3.4 Incremental Data Processing Patterns 3.5 Lakeflow Declarative Pipelines (Delta Live Tables) 3.6 Implementing a Multi-Hop Pipeline 3.7 Slowly Changing Dimensions (SCD) Patterns 3.8 Data Deduplication Strategies

5 Domain 4: Productionizing Data Pipelines (18%)

4.1 Databricks Workflows and Lakeflow Jobs 4.2 Databricks Asset Bundles (Declarative Automation Bundles) 4.3 Pipeline Monitoring, Alerting, and Error Handling 4.4 Testing and Deployment Best Practices 4.5 Pipeline Performance and Cost Optimization 4.6 Designing Idempotent and Recoverable Pipelines

6 Domain 5: Data Governance & Quality (11%)

5.1 Unity Catalog: Architecture and Three-Level Namespace 5.2 Access Control and Permissions 5.3 Data Lineage and Audit Logging 5.4 Delta Sharing 5.5 Lakehouse Federation 5.6 Data Quality Best Practices 5.7 Row-Level and Column-Level Security 5.8 Compliance and PII Data Management

3.7 Slowly Changing Dimensions (SCD) Patterns

Key Takeaways Explain This Topic AI

Slowly Changing Dimensions track how reference/dimension data (addresses, prices, statuses) changes over time.

SCD Type 1 overwrites the old value, keeping only the current state and losing history.

SCD Type 2 preserves history by adding a new row per change with __START_AT / __END_AT effective-date columns and a current flag.

AUTO CDC INTO (the renamed APPLY CHANGES INTO) computes SCD Type 1 or Type 2 automatically with STORED AS SCD TYPE 1/2 and SEQUENCE BY.

SEQUENCE BY handles out-of-order CDC events so changes are applied in the correct logical order.

Last updated: June 2026

What SCD Solves

Slowly Changing Dimensions (SCD) describe how to handle updates to dimension (reference) data that changes infrequently over time — a customer's address, a product's price, an employee's department. The question each SCD type answers is: when a dimension value changes, do we overwrite history or preserve it?

SCD Type 1: Overwrite

SCD Type 1 simply overwrites the existing value with the new one. There is exactly one row per business key, always showing the current value. History is lost — you cannot tell what the value used to be. MERGE INTO dim_customer t USING updates s ON t.cust_id = s.cust_id

  • WHEN MATCHED THEN UPDATE SET *
  • WHEN NOT MATCHED THEN INSERT *;

Type 1 keeps the table small and simple and is appropriate when historical values do not matter (correcting a typo, storing a latest-known phone number).

SCD Type 2: Version History

SCD Type 2 preserves history by inserting a new row each time a tracked value changes. Each version carries effective-date columns and usually a current-flag:

  • cust_id city __START_AT __END_AT is_current
  • 42 Reno 2024-01-01 2025-03-01 false
  • 42 Boise 2025-03-01 null true

The open record has a null __END_AT and is_current = true . Type 2 lets you answer point-in-time questions ("which city did customer 42 live in last March?") at the cost of more rows and bigger tables.

Automating SCD with AUTO CDC INTO

Doing SCD Type 2 by hand with MERGE is error-prone (closing old rows, handling out-of-order events). Databricks automates it in declarative pipelines with AUTO CDC INTO , the renamed and recommended successor to APPLY CHANGES INTO (same syntax; APPLY CHANGES still works but AUTO CDC is preferred). CREATE OR REFRESH STREAMING TABLE dim_customer;

  • CREATE FLOW cdc_flow AS AUTO CDC INTO dim_customer
  • FROM STREAM(cdc_source)
  • KEYS (cust_id)
  • APPLY AS DELETE WHEN operation = 'DELETE'
  • SEQUENCE BY event_ts
  • STORED AS SCD TYPE 2;

The clauses you must recognize:

KEYS — the business key identifying a record.

SEQUENCE BY — the ordering column; the engine uses it to apply changes in correct logical order even when CDC events arrive out of order .

APPLY AS DELETE WHEN — treats matching rows as deletes (tombstones).

STORED AS SCD TYPE 1 or SCD TYPE 2 — chooses overwrite vs full history; Type 2 auto-populates __START_AT and __END_AT .

TRACK HISTORY ON (optional) — version only when specific columns change.

This collapses what would be 100+ lines of hand-written MERGE logic into a few declarative lines, and it natively understands streaming and late-arriving data without you managing watermarks.

Type 1 vs Type 2: Choosing and Comparing

  • Aspect SCD Type 1 SCD Type 2
  • History Lost (overwrite) Preserved (new row per change)
  • Rows per key One Many (one per version)
  • Extra columns None __START_AT, __END_AT, current flag
  • Use when Only current value matters; corrections Point-in-time / audit history needed
  • AUTO CDC clause STORED AS SCD TYPE 1 STORED AS SCD TYPE 2
  • Exam pointers

If a scenario says "we must report what a customer's status was at the time of each order," that requires Type 2 — Type 1 cannot answer it.

If it says "just keep the latest address; we don't care about old ones," Type 1 is correct and cheaper.

For SCD Type 2 target tables, the schema must include the __START_AT and __END_AT columns with the same data type as the SEQUENCE BY column.

SEQUENCE BY is what makes AUTO CDC robust to out-of-order events — a distractor answer often credits this to watermarking instead.

Manual SCD Type 2 with MERGE (for contrast)

Understanding why AUTO CDC is valuable means seeing the manual alternative. Implementing SCD Type 2 by hand with MERGE INTO requires two logical steps in one statement: close the currently-open row when a tracked value changes, and insert the new version. Because a single MERGE cannot both update the old row and insert a new one for the same key cleanly, the common pattern unions the source with a marker that forces a non-match for the insert: MERGE INTO dim_customer t

  • USING staged_updates s
  • ON t.cust_id = s.cust_id AND t.is_current = true
  • WHEN MATCHED AND t.city <> s.city THEN
  • UPDATE SET is_current = false, __END_AT = s.event_ts
  • WHEN NOT MATCHED THEN
  • INSERT (cust_id, city, __START_AT, __END_AT, is_current)
  • VALUES (s.cust_id, s.city, s.event_ts, null, true);

This hand-written approach is fragile: it does not natively handle out-of-order events, requires careful staging, and grows long fast. AUTO CDC INTO replaces this entire pattern, which is exactly why Databricks recommends it.

Other SCD types you may see referenced

  • Type Behavior
  • Type 0 Retain original, never change (e.g., date-of-birth)
  • Type 1 Overwrite, no history
  • Type 2 New row per change, full history (most common)
  • Type 3 Add a 'previous value' column (limited history)

The Associate exam focuses overwhelmingly on Type 1 vs Type 2 and on driving them with AUTO CDC INTO ; Types 0 and 3 appear mainly as distractors.