Choosing a slowly changing dimension type
Slowly Changing Dimensions (SCDs) define how a dimension table handles changes to source data over time. On Azure Databricks, SCDs are typically implemented using Delta Lake tables with MERGE operations, and the Delta Live Tables (DLT) framework provides built-in AUTO CDC (APPLY CHANGES) support for SCD Type 1 and Type 2 without hand-written merge logic. Choosing the right type depends on whether you need to preserve history or just keep the latest state.
1 · Learn the must-know
- SCD Type 1 overwrites old attribute values with new ones, so no history is kept and it's used when historical accuracy doesn't matter (e.g., correcting a typo).
- SCD Type 2 preserves full history by inserting a new row for each change, typically using columns like __START_AT/__END_AT (or effective/expiration dates) and a current-flag to track validity periods.
- In Delta Live Tables, the AUTO CDC (formerly APPLY CHANGES INTO) API lets you implement SCD Type 1 or Type 2 declaratively by specifying keys, sequencing column, and STORED AS SCD TYPE 1 or 2.
- SCD Type 2 tables grow larger over time since every change adds a new row, so storage and query performance considerations matter more than with Type 1.
- Outside DLT, SCD Type 2 is commonly implemented manually using Delta Lake's MERGE INTO statement combined with WHEN MATCHED/WHEN NOT MATCHED clauses to expire old rows and insert new ones.
- Choosing SCD type is a business/analytical requirement decision: pick Type 1 when only current state matters (e.g., dashboards needing latest values) and Type 2 when auditability or point-in-time analysis is required.
2 · Check your understanding
A retail company maintains a Customers dimension table in Delta Lake. Analysts need historical sales reports that correctly show which sales region a customer belonged to at the time each past transaction occurred, even after the customer later moves to a different region. Which SCD type should the engineering team apply to the region attribute?
What you have tried across DP-750's objectives, not a readiness score.
Set up and configure an Azure Databricks environment15-20% of the exam0 of 13 tried
Secure and govern Unity Catalog objects15-20% of the exam0 of 12 tried
Prepare and process data30-35% of the exam0 of 28 tried
Deploy and maintain data pipelines and workloads30-35% of the exam0 of 24 tried
3 · Keep going
Ready for more? Take a weighted mock or try free practice questions.