Expectations (Data Quality)
Databricks · Declarative Pipelines (Lakeflow)
Theory
Expectations are data quality rules you attach to a pipeline dataset. Each one has a name, a boolean condition and an action for rows that fail. In SQL you write CONSTRAINT name EXPECT (condition) followed by an optional ON VIOLATION clause. With no clause the bad row is kept but counted (warn). ON VIOLATION DROP ROW removes failing rows, and ON VIOLATION FAIL UPDATE stops the update so nothing bad is written. The pass and fail counts for every expectation are recorded in the event log and shown in the pipeline UI.
Example: orders_silver expects order_id IS NOT NULL (drop) and amount > 0 (drop). An incoming batch of five orders with one missing id and one negative amount writes three rows, and the UI shows one failure for each rule.
Why we use it
Bad data silently flowing into gold tables breaks dashboards and trust. Expectations make quality rules part of the pipeline code, block or filter bad rows automatically and give you metrics to prove data quality over time.
When to use it
Use warn for rules you want to monitor, drop for rows that are safe to discard, and fail for critical rules where wrong data is worse than late data. To keep the bad rows for review, write a separate quarantine table with the inverse condition instead of only dropping them.
How it works
- A batch of rows reaches orders_silver
- Evaluate each expectation on each row
- Does the row fail a FAIL UPDATE rule? Yes: Stop the whole update with an error. No: Continue.
- Does the row fail a DROP ROW rule? Yes: Drop the row and count it. No: Keep the row.
- Count rows failing warn-only rules but keep them
- Write kept rows and log pass/fail metrics
Syntax
SQL: CONSTRAINT <name> EXPECT (<condition>) [ON VIOLATION DROP ROW | FAIL UPDATE]
Python: @dp.expect("name", "cond") | @dp.expect_or_drop(...) | @dp.expect_or_fail(...)
@dp.expect_all_or_drop({"name": "cond", ...})Example
CREATE OR REFRESH STREAMING TABLE orders_silver (
CONSTRAINT valid_id EXPECT (order_id IS NOT NULL) ON VIOLATION DROP ROW,
CONSTRAINT valid_amount EXPECT (amount > 0) ON VIOLATION DROP ROW,
CONSTRAINT known_region EXPECT (region IN ('North','South','East','West')) -- warn only
) AS
SELECT * FROM STREAM(orders_bronze);
# Python version
from pyspark import pipelines as dp
@dp.table(name="orders_silver")
@dp.expect_or_drop("valid_id", "order_id IS NOT NULL")
@dp.expect_or_drop("valid_amount", "amount > 0")
def orders_silver():
return spark.readStream.table("orders_bronze")Key points
- No ON VIOLATION clause = warn: the row is kept and the failure is counted
- DROP ROW filters failing rows before they are written
- FAIL UPDATE stops the update; fix the data or rule and rerun
- Expectation metrics are in the event log and the table's data quality view
- Use a quarantine table (NOT condition) when you must keep bad rows
- Conditions are normal SQL boolean expressions; keep them simple and deterministic