DEV Community

Cover image for DP-750: Slowly Changing Dimensions and Data Quality Expectations Explained and with Real Exam Questions
Jin
Jin

Posted on • Originally published at blog.luca-liu.com

DP-750: Slowly Changing Dimensions and Data Quality Expectations Explained and with Real Exam Questions

#azure #databricks #certification #tutorial

When preparing for DP-750: Microsoft Certified: Azure Databricks Data Engineer Associate, you need to understand two related but different topics:

  • Slowly Changing Dimensions, SCD
  • Data quality expectations in Lakeflow Spark Declarative Pipelines

SCD is about how to model changes in dimension data over time.

Data quality expectations are about validating records as they flow through a pipeline.

The related questions in your DP-750 question bank are Q36, Q55, Q57, Q76, Q77, and Q78.

1. What is a Slowly Changing Dimension?

A Slowly Changing Dimension, or SCD, is a data modeling pattern used when dimension records change over time.

A dimension table usually stores descriptive business data, such as:

  • customer profile
  • equipment metadata
  • product information
  • employee information
  • supplier information
  • IoT sensor ownership

Example:

equipment_id = E001
equipment_name = Turbine A
manufacturer = Contoso Energy
owner = Team West
Enter fullscreen mode Exit fullscreen mode

Over time, some values may change:

owner = Team Central
Enter fullscreen mode Exit fullscreen mode

The question is:

Should we overwrite the old value, or should we keep the full history?

That is where SCD types are used.

2. SCD Type 1

SCD Type 1 overwrites existing values.

It does not preserve historical versions.

Example before update:

equipment_id equipment_name manufacturer
E001 Turbine A Contoso Energy

Example after correction:

equipment_id equipment_name manufacturer
E001 Turbine Alpha Contoso Energy

The old value, Turbine A, is replaced.

Use SCD Type 1 when:

  • the old value is not needed
  • the change is a correction
  • the previous value was a data-entry mistake
  • only the latest value matters

Databricks documentation describes SCD Type 1 as history tracking off: outdated records are overwritten when they are updated or deleted in the source.

For DP-750:

Historical values are NOT required = SCD Type 1
Data-entry mistake correction = SCD Type 1
Overwrite existing values = SCD Type 1
Enter fullscreen mode Exit fullscreen mode

3. SCD Type 2

SCD Type 2 keeps history.

Instead of overwriting the old row, it creates a new row and marks which version is current.

Example before ownership change:

sensor_id owner valid_from valid_to is_current
S001 Team West 2025-01-01 null true

Example after ownership change:

sensor_id owner valid_from valid_to is_current
S001 Team West 2025-01-01 2026-03-01 false
S001 Team Central 2026-03-01 null true

Use SCD Type 2 when:

  • business users need historical tracking
  • analysts need to know what was true at a previous time
  • ownership changes over time
  • full history must be preserved

Databricks documentation describes SCD Type 2 as history tracking on: the ingestion pipeline keeps the old row, adds the update as a new row, and marks the old row as inactive.

For DP-750:

Track full history = SCD Type 2
Ownership changes over time = SCD Type 2
Keep old row and add new row = SCD Type 2
Enter fullscreen mode Exit fullscreen mode

4. SCD Type 1 vs SCD Type 2

Requirement SCD type
Correct data-entry mistakes Type 1
Historical values are not required Type 1
Only latest value matters Type 1
Overwrite existing values Type 1
Track ownership changes over time Type 2
Keep full history Type 2
Add a new row for each change Type 2
Support historical analysis Type 2

This is one of the most important DP-750 decision patterns.

If the question says:

Historical values are NOT required.
Enter fullscreen mode Exit fullscreen mode

choose Type 1.

If the question says:

Analysts must track the full history.
Enter fullscreen mode Exit fullscreen mode

choose Type 2.

5. SCD Type 2 and Lakeflow

Lakeflow Spark Declarative Pipelines can support CDC and SCD patterns.

Databricks documentation shows that Lakeflow SDP can create a Type 2 SCD table to track all changes, and uses AUTO CDC ... INTO to apply changes into the final table.

In Lakeflow SQL, SCD Type 2 can be expressed with:

STORED AS SCD TYPE 2
Enter fullscreen mode Exit fullscreen mode

In Python, it can be expressed with:

stored_as_scd_type="2"
Enter fullscreen mode Exit fullscreen mode

For DP-750, you usually do not need to memorize all syntax details. The most important concept is:

SCD Type 2 = persistent record-level history
Enter fullscreen mode Exit fullscreen mode

6. Delta Lake time travel vs SCD Type 2

Delta Lake time travel and SCD Type 2 are related, but they are not the same thing.

Delta Lake time travel lets you query earlier table versions by timestamp or version number. Databricks documentation says time travel supports querying previous table versions based on timestamp or table version recorded in the transaction log.

Example:

SELECT *
FROM customer_profiles VERSION AS OF 10;
Enter fullscreen mode Exit fullscreen mode

SCD Type 2 stores business history directly in the table as rows.

Example:

customer_id status valid_from valid_to is_current
C001 Silver 2025-01-01 2026-01-01 false
C001 Gold 2026-01-01 null true

For DP-750:

Requirement Best answer
Maintain persistent historical record changes SCD Type 2
Query earlier Delta table versions Delta Lake time travel
Correct wrong values without keeping history SCD Type 1

7. What are data quality expectations?

In Lakeflow Spark Declarative Pipelines, expectations are data quality constraints.

They validate records as data flows through the pipeline.

An expectation is usually written as a Boolean condition, such as:

amount > 0
Enter fullscreen mode Exit fullscreen mode

or:

transaction_id IS NOT NULL
Enter fullscreen mode Exit fullscreen mode

Databricks documentation says expectations apply quality constraints that validate data as it flows through ETL pipelines. They can provide metrics, drop invalid records, or fail updates when invalid records are detected.

For DP-750, expectations usually appear in questions about:

  • validating records
  • dropping invalid records
  • failing pipeline updates
  • collecting data quality metrics
  • preventing bad records from being written
  • quarantining invalid records

8. Expectation actions

There are three main expectation behaviors.

Behavior Meaning
Retain invalid records Keep records, but collect metrics
Drop invalid records Do not write invalid records to the target
Fail on invalid records Stop the update when invalid data is detected

In current Lakeflow documentation, expectation decorators are in the pyspark.pipelines module, commonly imported as dp. The exam questions may still use the older dlt naming, but the behavior is the important part. Databricks documentation lists decorators such as expect, expect_or_drop, and expect_or_fail.

9. expect

expect validates data and records metrics, but it does not drop or fail invalid records.

Example:

@dlt.expect("valid_amount", "amount > 0")
Enter fullscreen mode Exit fullscreen mode

Behavior:

Valid records are written.
Invalid records are also written.
Metrics are collected.
Pipeline continues.
Enter fullscreen mode Exit fullscreen mode

Use this when:

  • you want to monitor data quality
  • invalid records can remain in the target
  • you only need metrics

For DP-750:

Collect metrics only = expect
Enter fullscreen mode Exit fullscreen mode

10. expect_or_drop

expect_or_drop drops invalid records before they are written to the target.

Example:

@dlt.expect_or_drop("valid_amount", "amount > 0")
Enter fullscreen mode Exit fullscreen mode

Behavior:

Valid records are written.
Invalid records are dropped.
Metrics are collected.
Pipeline continues.
Enter fullscreen mode Exit fullscreen mode

Databricks documentation says expect_or_drop prevents further processing of invalid records, and records that violate the expectation are dropped from the target dataset.

Use this when:

  • invalid records must not be written
  • the pipeline should continue
  • you want expectation metrics
  • you do not need to preserve invalid records in the same target table

For DP-750:

Invalid records must NOT be written, but pipeline continues = expect_or_drop
Enter fullscreen mode Exit fullscreen mode

11. expect_or_fail

expect_or_fail fails the update when invalid records are detected.

Example:

@dlt.expect_or_fail("rule1", "amount > 0")
Enter fullscreen mode Exit fullscreen mode

Behavior:

If invalid records are detected, the update fails.
The transaction is rolled back.
Invalid data is not committed.
Enter fullscreen mode Exit fullscreen mode

Databricks documentation says expect_or_fail stops execution immediately when a record fails validation. If the operation is a table update, the system atomically rolls back the transaction.

Use this when:

  • invalid records are unacceptable
  • the target table must not be updated
  • the update must not be committed if invalid data exists

For DP-750:

Prevent update from being committed = expect_or_fail
Enter fullscreen mode Exit fullscreen mode

12. Expectation metrics

Lakeflow expectations can produce data quality metrics.

Databricks documentation says metrics for warn or drop actions can be viewed from the pipeline UI, and the Data quality tab is available in the pipeline UI for pipeline datasets with expectations. It also says expectation metrics can be queried from the Lakeflow Spark Declarative Pipelines event log.

For DP-750:

Requirement Answer pattern
Review expectation metrics with minimal effort Pipeline Data Quality tab
Query metrics programmatically Pipeline event log
Invalid records fail update Metrics may not be recorded because update fails

13. Quarantine invalid records

A quarantine pattern means invalid records are preserved somewhere for review.

For example:

valid_records      -> target table
invalid_records    -> quarantine table
Enter fullscreen mode Exit fullscreen mode

This is different from simply dropping invalid records.

If the requirement says:

Invalid records must NOT be written to Table1.
Invalid records must be preserved for review.
Pipeline must continue.
Enter fullscreen mode Exit fullscreen mode

then expect_or_drop alone is not enough, because it drops invalid records and does not preserve them as reviewable records.

The answer is usually to implement a quarantine pattern, such as writing invalid records to a separate quarantine table.

For DP-750:

Drop invalid records from target + preserve invalid records = quarantine logic
Enter fullscreen mode Exit fullscreen mode

14. Expectations vs table constraints

Expectations and table constraints both validate data, but they are used differently.

Feature Typical use
Lakeflow expectations Validate records during pipeline processing
NOT NULL constraint Enforce non-null values at table write time
CHECK constraint Enforce Boolean rule at table write time
Quarantine logic Preserve invalid records for review

For example:

amount > 0
Enter fullscreen mode Exit fullscreen mode

could be implemented as:

  • an expectation in a Lakeflow pipeline

  • a CHECK constraint on a Delta table

But the exam context matters.

If the question says:

Lakeflow Spark Declarative Pipelines pipeline
rule1
expectation
Enter fullscreen mode Exit fullscreen mode

then use expectation decorators.

If the question says:

table-level data quality enforcement
invalid records must be rejected when written to the table
Enter fullscreen mode Exit fullscreen mode

then use table constraints.

15. DP-750 decision table for SCD and expectations

Scenario in the question Best answer pattern
Correct data-entry mistake SCD Type 1
Historical values are not required SCD Type 1
Overwrite name and address values SCD Type 1
Track ownership changes over time SCD Type 2
Keep full history of profile changes SCD Type 2
Query earlier Delta table versions Delta Lake time travel
Monitor data quality only expect
Drop invalid records and continue expect_or_drop
Fail update when invalid data exists expect_or_fail
Prevent table update from being committed expect_or_fail
Preserve invalid records for review Quarantine logic
View expectation metrics with minimal effort Pipeline Data Quality tab
Query expectation metrics Pipeline event log

Real Exam Questions

Question 36

Useful case information

Contoso identifies the following data modeling and optimization requirements:

  • Overwrite equipment metadata attributes, such as name, manufacturer, model, and commissioning date, when the attributes change. Historical values are NOT required.

Contoso also has the following governance issue:

  • Ownership of the IoT sensors changes over time, and analysts must track the full history of the ownership.

  • Occasionally, equipment manufacturers must correct data-entry mistakes in equipment names. Historical values are NOT required.

Which SCD type should you use to support the planned data modeling changes?

Issue Answer
Data-entry mistakes by the equipment manufacturers Type 1 ✅ Correct Answer
Changes to IoT Sensor ownership Type 2 ✅ Correct Answer

Question 55

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a managed Delta table named Table1.

Table1 stores customer profile data.

Business users must analyze how customer profile records change over time. They must also be able to query earlier versions of the table.

You need to implement a solution that:

  • Maintains persistent historical versions of customer profile records for long-term analysis.

  • Allows users to query earlier versions of the Delta table.

  • Minimizes maintenance effort.

What should you do?

Area Answer
To record historical changes Implement a Type 2 slowly changing dimension, SCD ✅ Correct Answer
To support temporal analysis Use Delta Lake time travel ✅ Correct Answer

Question 57

You have an Azure Databricks workspace that is enabled for Unity Catalog and contains a Delta table named Table1. Table1 is used as a dimension table and contains the following columns:

  • id

  • name

  • address

You need to apply a Type 1 slowly changing dimension, SCD, approach when updates are processed in the name and address columns.

What will occur when an update is processed?

A. A new row will be added for each change to a name and address.

B. The value of the id column will be updated.

C. The existing name and address values will be overwritten. ✅ Correct Answer

D. Historical versions of the row and its effective dates will be retained.

Question 76

You have an Azure Databricks workspace that is enabled for Unity Catalog.

You have a Lakeflow Spark Declarative Pipelines, SDP, pipeline that writes numerical data to a table named Table1 by using a data quality validation rule named rule1.

You need to modify rule1 to meet the following requirements:

  • Ensure that amount is always greater than 0.

  • Prevent an update to Table1 from being committed when data that violates rule1 is detected.

Which statement should you execute?

A. @dlt.expect_or_fail("rule1", "amount > 0") ✅ Correct Answer

B. @dlt.expect("rule1", "amount > 0")

C. @dlt.expect_all_or_drop({"rule1": "amount > 0"})

D. @dlt.expect_or_drop("rule1", "amount > 0")

Question 77

You have a Lakeflow Spark Declarative Pipelines, SDP, pipeline in Azure Databricks. The pipeline ingests transaction data into a table named Table1.

You need to ensure that in the event of an invalid record, the pipeline continues to run.

The solution must meet the following requirements:

  • Invalid records must NOT be written to Table1.

  • Invalid records must be preserved for review.

  • Minimize development effort.

What should you do?

A. Add a CHECK constraint to Table1.

B. Run WHERE clauses in downstream queries to filter out invalid records.

C. Define a pipeline expectation.

D. Implement advanced logic to quarantine the invalid records. ✅ Correct Answer

Question 78

You have an Azure Databricks workspace that is enabled for Unity Catalog.

You have a Lakeflow Spark Declarative Pipelines, SDP, pipeline that writes records to a Delta table named Table1 by using a data quality rule named rule1.

You need to meet the following requirements:

  • Records that violate rule1 must NOT be written to Table1, but the pipeline must continue processing valid records.

  • Data engineers must be able to review expectation metrics by using minimal development effort.

What should you do?

Area Answer
Set action for rule1 to dlt.expect_or_drop ✅ Correct Answer
View expectation metrics in The Data Quality tab of the pipeline ✅ Correct Answer

Key takeaways

For DP-750, SCD and expectation questions are mainly decision-pattern questions.

Remember these patterns:

  • SCD Type 1 overwrites existing values.

  • SCD Type 1 is used when historical values are not required.

  • SCD Type 1 is good for correcting data-entry mistakes.

  • SCD Type 2 keeps historical versions.

  • SCD Type 2 is used when analysts must track full history.

  • Delta Lake time travel lets users query earlier table versions.

  • expect keeps invalid records and records metrics.

  • expect_or_drop drops invalid records and continues processing.

  • expect_or_fail fails the update and prevents the transaction from being committed.

  • If invalid records must be preserved for review, use a quarantine pattern.

  • Expectation metrics can be reviewed in the pipeline Data Quality tab or queried from the event log.

  • In current Lakeflow documentation, the decorators use dp, but many exam-style questions still show dlt.

If you can separate overwrite vs history, and monitor vs drop vs fail vs quarantine, these DP-750 questions become much easier.

Explore more

luca1iu image

Jin

Hello there! 👋 I'm Jin, a Business Intelligence Developer with a passion for all things data. Proficient in Python, SQL, Power BI, Tableau

Thank you for taking the time to explore data-related insights with me. I appreciate your engagement.

Connect with me on LinkedIn

Connect with me on X

Top comments (0)