What you will be able to do
- Write a named expectation with a valid SQL Boolean constraint in SQL and Python
- Choose the warn, drop, or fail action and write the matching syntax
- Predict what happens to the target table, metrics and other flows when a fail expectation is violated
- Spot where expectations are unsupported or change how a dataset is refreshed
Key concept
Expectation — An expectation is a named true/false SQL condition attached to a streaming table, materialized view or view in a pipeline. It is checked against every record, and its action decides what happens to a record that fails: keep it and count it (warn), remove it (drop), or stop the update (fail).
1.The three parts of an expectation
In Lakeflow Declarative Pipelines, data quality rules live inside the dataset definition. You don't need a separate validation job. An expectation is an optional clause on a materialized view, streaming table or view, and it checks every record that passes through that dataset's query. Each expectation has three parts: a name, a constraint and an action.
The name identifies the expectation in metrics and monitoring, so choose one that says what is being checked. A name must be unique within a dataset. You can reuse the same expectation on other datasets in the pipeline.
The constraint is a SQL expression that must evaluate to true or false for each record. When a record makes it false, the expectation is triggered. The constraint can be a simple comparison, a built-in function such as year(transaction_date) >= 2020, a CASE expression, or a compound AND/OR condition. It cannot contain custom Python functions, calls to external services, or subqueries that reference other tables. In SQL, you declare it as CONSTRAINT name EXPECT (expr) inside the column-list parentheses. In Python, you add a @dp.expect(name, constraint) decorator.
CREATE OR REFRESH STREAMING TABLE customers(
CONSTRAINT valid_customer_age EXPECT (age BETWEEN 0 AND 120)
) AS SELECT * FROM STREAM(datasets.samples.raw_customers);You can put several expectations on one dataset. In SQL, separate the CONSTRAINT clauses with commas. In Python, stack the decorators. The materialized view reference adds more limits on the expression: it can use literals, column identifiers and deterministic built-in functions, but not aggregate functions, window functions, table-valued generator functions or subqueries.
Checkpoint 1 of 7· Check yourself
A team wants an expectation that rejects orders whose customer_id does not exist in a separate customers table. They write CONSTRAINT known_customer EXPECT (customer_id IN (SELECT id FROM customers)). What is the problem?
Subqueries that reference other tables are explicitly not allowed. Do this kind of check another way, for example with a join in a separate validation dataset.
“Constraints must use valid SQL syntax and cannot contain the following:”Source: docs.databricks.com
Sources1
2.Warn, drop, or fail: the action decides the outcome
The action is what you choose for each constraint. The default is warn: a violating record is still written to the target, and the expectation records how many records passed and failed. Drop removes violating records before the write and logs how many were dropped alongside the dataset's other metrics. Fail stops the update and needs manual intervention before you reprocess.
The syntax is consistent across languages. In SQL, warn is a bare EXPECT, and you add ON VIOLATION DROP ROW or ON VIOLATION FAIL UPDATE for the other two. In Python, each action has its own decorator.
| Action | SQL syntax | Python syntax | Effect on invalid records |
|---|---|---|---|
| warn (default) | EXPECT | dp.expect | Written to the target; counted in metrics |
| drop | EXPECT ... ON VIOLATION DROP ROW | dp.expect_or_drop | Dropped before the write; dropped count logged |
| fail | EXPECT ... ON VIOLATION FAIL UPDATE | dp.expect_or_fail | The update does not succeed; manual intervention is required |
-- Warn: write invalid records but track them in metrics
CREATE OR REFRESH STREAMING TABLE orders_raw (
CONSTRAINT valid_order_id EXPECT (order_id IS NOT NULL)
) AS SELECT * FROM STREAM read_files("/volumes/raw/orders", format => "json");
-- Drop: discard invalid records before writing
CREATE OR REFRESH STREAMING TABLE orders_clean (
CONSTRAINT non_negative_amount EXPECT (amount >= 0) ON VIOLATION DROP ROW
) AS SELECT * FROM STREAM(orders_raw);
-- Fail: stop the pipeline on any invalid record
CREATE OR REFRESH STREAMING TABLE orders_critical (
CONSTRAINT required_customer_id EXPECT (customer_id IS NOT NULL) ON VIOLATION FAIL UPDATE
) AS SELECT * FROM STREAM(orders_clean);Checkpoint 2 of 7· Match them up
Match each syntax to what happens to a record that violates it
Tap a term, then the definition that fits it.
Warn keeps records, drop removes them, and fail stops the update.
“Invalid records prevent the update from succeeding. Manual intervention is required before reprocessing.”Source: docs.databricks.com
Checkpoint 3 of 7· Fill the gap
This Python dataset should discard rows with a negative amount and keep processing. Which decorator completes it?
# Drop: discard invalid records before writing
@dp.table
@dp. ? ("non_negative_amount", "amount >= 0")
def orders_clean():
return spark.readStream.table("orders_raw")expect_or_drop takes one name and one constraint and removes violating rows. expect_all_or_drop also drops rows, but it takes a dictionary of expectations.
Source: docs.databricks.comCheckpoint 4 of 7· Exam question
In Lakeflow Declarative Pipelines, when a SQL `CONSTRAINT ... EXPECT (...)` clause is defined without an `ON VIOLATION` clause, what happens to records that violate the constraint?
Correct answer: B — The violating records are still written to the target table, and the violation is counted in the pipeline's quality metrics.
- A. A hard failure and rollback is the behavior of the fail action, which requires an explicit `ON VIOLATION FAIL UPDATE` clause. Without that clause, the pipeline does not stop on a violation.
- B. This describes the default warn behavior: no `ON VIOLATION` clause means violating rows are still written downstream while the count of violations is tracked in the dataset's data quality metrics. This is the least restrictive of the three actions.
- C. Silently dropping rows with no tracked count describes neither the default action nor the drop action, since drop still logs how many rows were removed. The default action does not remove rows at all.
- D. There is no built-in automatic quarantine table; a quarantine destination has to be built manually using a derived flag column and separate downstream views or tables. A bare `EXPECT` clause does not create any such routing.
Sources1
3.What fail really stops, and what it leaves running
Use expect_or_fail when invalid records are unacceptable. Execution stops as soon as a record fails, and if the operation is a table update, the transaction is rolled back atomically. Bad data does not partly land in the table.
How far the failure spreads depends on the run mode. In a triggered pipeline, only the flow that failed is affected. In a continuous pipeline, the failure stops that flow and every flow that depends on it, and the pipeline emits a message explaining why it stopped.
Two more consequences matter in practice. First, the pipeline UI shows tracking metrics for warn and drop, but not for fail, because the update fails before metrics are recorded. Second, re-running the pipeline unchanged doesn't help: you must fix the pipeline code to handle the invalid data before you re-run. To help you find the record, fail expectations change the Spark query plan to track violation details. The error, EXPECTATION_VIOLATION, can report the violated expectation name, the input data and the output record.
Checkpoint 5 of 7· Check yourself
After a fail expectation stops an update, an engineer opens the Data quality tab to see the pass/fail counts for that constraint. What will they find?
Warn and drop record tracking metrics. Fail stops the update when it finds an invalid record, so its metrics are not recorded. Use the error message to find the bad record.
“Because fail causes the update to fail when an invalid record is detected, metrics are not recorded.”Source: docs.databricks.com
Checkpoint 6 of 7· Exam question
A pipeline dataset is defined with the following Python decorator: ```python @dp.expect_or_drop("valid_order_amount", "order_amount > 0") def silver_orders(): return spark.readStream.table("bronze_orders") ``` Which SQL `CONSTRAINT` clause is functionally equivalent to this Python decorator?
Correct answer: A — `CONSTRAINT valid_order_amount EXPECT (order_amount > 0) ON VIOLATION DROP ROW`, which drops rows failing the check and keeps the flow running.
- A. `ON VIOLATION DROP ROW` is the SQL equivalent of the `expect_or_drop` decorator: violating rows are excluded from the target while the flow keeps processing later batches. This matches the drop-and-continue behavior shown in the Python code.
- B. `ON VIOLATION FAIL UPDATE` maps to `expect_or_fail`, not `expect_or_drop`, so it would halt and roll back the update instead of just excluding the bad rows. This is a stricter action than the one in the decorator.
- C. A bare `EXPECT` clause with no `ON VIOLATION` suffix is the SQL equivalent of the plain `expect` decorator, which only warns and still writes invalid rows. That does not match the drop behavior requested.
- D. SQL constraint syntax expresses the action through the `ON VIOLATION` clause rather than through a keyword named after the Python decorator, so `EXPECT_OR_DROP` is not valid Lakeflow Declarative Pipelines SQL syntax.
Sources1
4.Where expectations apply, and what they cost
Only streaming tables, materialized views and temporary views support expectations, so data quality metrics exist only for those objects. Views are computed only when queried, so a view may have no metrics, or several sets if more than one downstream dataset queries it. Sinks don't support expectations, and expectations aren't supported with AUTO CDC FROM SNAPSHOT.
Materialized views have a cost to plan for. The materialized view reference says a materialized view with expectations in its definition is fully recomputed on every update. If you need incremental refresh, remove the expectations or apply them outside the materialized view, for example on the upstream streaming table.
Checkpoint 7 of 7· Check yourself
A large materialized view used to refresh incrementally. After a developer adds a DROP ROW expectation to its definition, every update recomputes the whole view. Why?
Expectations in a materialized view's definition disable incremental refresh for that view only. To keep incremental refresh, move the check outside the view's definition.
“A materialized view whose definition includes expectations is fully refreshed on each update and does not support incremental refresh.”Source: docs.databricks.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.An expectation with no ON VIOLATION clause drops the bad rows.Why is that wrong?
Bare EXPECT is warn, the default. Invalid records are still written to the target and only counted in metrics.
Covered in Warn, drop, or fail: the action decides the outcome
2.A violated expect_or_fail always stops the entire pipeline.Why is that wrong?
In a triggered pipeline only the offending flow fails and parallel flows continue. In a continuous pipeline the failure stops that flow and the flows that depend on it.
Covered in What fail really stops, and what it leaves running
3.Adding expectations to a materialized view has no effect on how it refreshes.Why is that wrong?
A materialized view with expectations in its definition is fully refreshed on every update and loses incremental refresh.
Covered in Where expectations apply, and what they cost
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.databricks.com/aws/en/ldp/expectationsOfficial docs
“Each expectation must have a name, which is used as an identifier to track and monitor the expectation.”
↩︎ The three parts of an expectation“Constraints must use valid SQL syntax and cannot contain the following:”
↩︎ The three parts of an expectation“Invalid records are dropped before data is written to the target.”
↩︎ Warn, drop, or fail: the action decides the outcome“The count of dropped records is logged alongside other dataset metrics.”
↩︎ Warn, drop, or fail: the action decides the outcome“If the operation is a table update, the system atomically rolls back the transaction”
↩︎ What fail really stops, and what it leaves running“you must fix the pipeline code to handle the invalid data correctly before re-running the pipeline”
↩︎ What fail really stops, and what it leaves running“Expectations are not supported with AUTO CDC FROM SNAPSHOT.”
↩︎ Where expectations apply, and what they cost“Expectations use standard SQL Boolean statements to specify constraints.”
↩︎ Key concept“Retaining invalid records is the default behavior for expectations.”
↩︎ Exam trap 1“In a continuous pipeline, an expectation failure stops the flow, all dependent flows, and the pipeline emits a message explaining why it stopped.”
↩︎ Exam trap 2“Retaining invalid records is the default behavior for expectations.”
↩︎ Prediction“Invalid records prevent the update from succeeding. Manual intervention is required before reprocessing.”
↩︎ Checkpoint“In a triggered pipeline, the failure of a single flow does not cause other parallel flows to fail.”
↩︎ Prediction“Because fail causes the update to fail when an invalid record is detected, metrics are not recorded.”
↩︎ Checkpoint - 2.
“A materialized view whose definition includes expectations is fully refreshed on each update and does not support incremental refresh.”
↩︎ Where expectations apply, and what they cost“A materialized view whose definition includes expectations is fully refreshed on each update and does not support incremental refresh.”
↩︎ Exam trap 3