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

  1. A batch of rows reaches orders_silver
  2. Evaluate each expectation on each row
  3. Does the row fail a FAIL UPDATE rule? Yes: Stop the whole update with an error. No: Continue.
  4. Does the row fail a DROP ROW rule? Yes: Drop the row and count it. No: Keep the row.
  5. Count rows failing warn-only rules but keep them
  6. 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

Databricks topics

Databricks Fundamentals

Compute

Notebooks & Development

Apache Spark on Databricks

Delta Lake

Unity Catalog & Governance

Data Ingestion

Declarative Pipelines (Lakeflow)

Lakeflow Jobs

Databricks SQL & BI

Architecture, Performance & Cost

Machine Learning & AI

Certification & Interview

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Databricks

The lakehouse platform from the ground up: Apache Spark, Delta Lake, Unity Catalog, Lakeflow pipelines and jobs, Databricks SQL and MLflow. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice SQL / Spark SQL Sharpen the SQL you use in Databricks SQL and Spark SQL: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Databricks  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Databricks needs a real workspace — there's no in-browser engine for it. Click Copy above, then paste it into a notebook or the SQL editor in Databricks Free Edition (free, no credit card) and run it on serverless compute.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic