Data Engineer Associate · Domain 3: Data Transformation and Modeling · 22% of the exam
Task 3.7: Apply data quality checks and validation rules to ensure reliable Silver and Gold datasets.
Pipeline expectations and their warn, drop and fail actions, CHECK and NOT NULL constraints on Delta tables, quarantining bad rows and reading quality metrics.
Study it
Data quality: expectations, constraints and quarantine
Lesson coming
Sample questions
Try each one before opening the answer. Every option is explained, with the Databricks documentation page that proves it.
Question 1 · choose 1
In a Lakeflow pipeline, the silver streaming table orders_clean must leave out rows with a NULL order_id, keep processing the rest, and report how many rows were removed. Which constraint does this?
ACONSTRAINT valid_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW
BCONSTRAINT valid_id EXPECT (order_id IS NOT NULL)
CCONSTRAINT valid_id EXPECT (order_id IS NOT NULL) ON VIOLATION FAIL UPDATE
DALTER TABLE orders_clean ADD CONSTRAINT valid_id CHECK (order_id IS NOT NULL)
Show the answer and why
ACONSTRAINT valid_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW
Correct
The drop action removes invalid records before they are written and logs the count of dropped records with the dataset metrics.
BCONSTRAINT valid_id EXPECT (order_id IS NOT NULL)
Incorrect
Without an action the expectation only warns: invalid records are still written to the target.
CCONSTRAINT valid_id EXPECT (order_id IS NOT NULL) ON VIOLATION FAIL UPDATE
Incorrect
With the fail action an invalid record keeps the update from succeeding, so the valid rows of orders_clean are not written either.
DALTER TABLE orders_clean ADD CONSTRAINT valid_id CHECK (order_id IS NOT NULL)
Incorrect
A table constraint rejects a write that violates it rather than leaving out the bad rows, and it reports no count of removed rows.
Expectations have three actions: warn (default), drop, and fail. Drop keeps the update running, filters the bad rows, and records how many it dropped.
A data engineer runs ALTER TABLE main.silver.orders ADD CONSTRAINT positive_amount CHECK (amount > 0) on an existing Delta table, and the statement fails. What is the most likely cause?
ASome existing rows already have amount less than or equal to 0
BCHECK constraints must be declared in CREATE TABLE, not added later
CCHECK constraints are a feature of pipeline streaming tables
DThe table needs a primary key before it can have other constraints
Show the answer and why
ASome existing rows already have amount less than or equal to 0
Correct
ADD CONSTRAINT verifies that all existing rows satisfy the CHECK before it adds the constraint.
BCHECK constraints must be declared in CREATE TABLE, not added later
Incorrect
CHECK constraints are added and dropped on existing tables with ALTER TABLE ADD CONSTRAINT and DROP CONSTRAINT.
CCHECK constraints are a feature of pipeline streaming tables
Incorrect
CHECK constraints are a Delta table feature; pipelines use expectations instead.
DThe table needs a primary key before it can have other constraints
Incorrect
A CHECK constraint can be added to a Delta table on its own; no other constraint has to exist first.
Enforced constraints on Delta tables, CHECK and NOT NULL, are validated against existing data when added and against every later write.
A Lakeflow pipeline has warn and drop expectations on several datasets. Where can the data engineer see how many records passed and failed each expectation?
AThe SQL tab of the Spark UI for the pipeline's cluster
BDESCRIBE HISTORY on each target table
CThe pipeline UI's Data quality tab or the event log
DThe system.billing.usage table
Show the answer and why
AThe SQL tab of the Spark UI for the pipeline's cluster
Incorrect
The Spark UI shows query plans and stage metrics, not expectation results.
BDESCRIBE HISTORY on each target table
Incorrect
Table history lists operations and metrics for each write, not per-expectation pass and fail counts.
CThe pipeline UI's Data quality tab or the event log
Correct
Expectation metrics for warn and drop actions appear in the Data quality tab and can be queried from the event log.
DThe system.billing.usage table
Incorrect
The billing table records usage and cost, not data quality.
Pipelines record expectation results as metrics. View them in the pipeline UI or query the event log; fail expectations stop the update, so they record no metrics.
In a Lakeflow pipeline, invalid trip records must not reach the silver dataset, but they must be kept so that a data steward can review them, and the pipeline must keep running. Which design meets this?
AAn expect_or_fail expectation on the silver table for each rule
BFlag failing rows, then route flagged and clean rows to separate datasets
CA warn expectation on the silver table so that each invalid row is counted
DA CHECK constraint on the silver Delta table for each rule
Show the answer and why
AAn expect_or_fail expectation on the silver table for each rule
Incorrect
A violation prevents the update from succeeding, and no invalid rows are kept for review.
BFlag failing rows, then route flagged and clean rows to separate datasets
Correct
The quarantine pattern adds an is_quarantined column from the rules, tracks them with expectations, and routes valid and invalid rows to separate datasets.
CA warn expectation on the silver table so that each invalid row is counted
Incorrect
Warn keeps invalid rows in the target, so they would reach silver.
DA CHECK constraint on the silver Delta table for each rule
Incorrect
A CHECK constraint fails the whole write when a row violates it, so nothing is loaded and nothing is kept for review.
To keep bad rows for review without failing or silently dropping them, quarantine them: compute a flag from the rules and send each side to its own dataset.
The Delta table main.silver.customers is written by several jobs outside any pipeline. From now on, no write may insert a NULL customer_id, and a write that tries must fail. What should the data engineer add?
AAn expectation on the table
Bdelta.appendOnly set to true
CA row filter that hides rows where customer_id is NULL
DALTER TABLE ... ALTER COLUMN customer_id SET NOT NULL
Show the answer and why
AAn expectation on the table
Incorrect
Expectations are clauses on pipeline datasets; they do not apply to writes by other jobs to a plain Delta table.
Bdelta.appendOnly set to true
Incorrect
An append-only table blocks updates and deletes of existing rows; it does not check the values that new rows carry.
CA row filter that hides rows where customer_id is NULL
Incorrect
A row filter changes what readers see; it does not stop writes.
DALTER TABLE ... ALTER COLUMN customer_id SET NOT NULL
Correct
A NOT NULL constraint is enforced on every write, so any transaction that writes a NULL into the column fails with an error.
NOT NULL and CHECK are enforced Delta constraints that guard every writer of a table. Pipeline expectations guard only the datasets the pipeline defines.