How to Catch Bad Data Early With Lakeflow Pipeline Expectations

Three simple choices that keep wrong numbers out of your tables

Every data engineer has lived this morning. A dashboard shows revenue down 40 percent. Leaders start asking questions. After an hour of digging, the cause is simple. A source system sent rows with empty order amounts, and the pipeline loaded them without a word.

The data was not wrong for long. But nobody knew, and trust took a hit.

Lakeflow Declarative Pipelines give you a calm way to stop this. They are called expectations. An expectation is a small rule that sits inside your table definition and checks every row as it arrives.

What an Expectation Really Is

Think of an expectation as a gate. Each row walks up to the gate. The gate asks one question, such as "Is the amount greater than zero?" Then it decides what to do.

You write the question as a normal SQL condition. You choose what happens when a row fails. That is the whole idea.

There are three choices, and picking the right one matters more than the rule itself.

The Three Choices

Warn (keep the row). The row is loaded anyway. The pipeline records how many rows failed. Use this when you want to watch a problem before you act on it.

Drop (skip the row). The bad row is left out of the table. The good rows still load. Use this when one bad row should not block the rest.

Fail (stop the update). The whole update stops. Nothing new is written. Use this when a bad row means something is seriously broken, like a missing primary key.

flowchart LR
    A[Incoming rows] --> B{Expectation check}
    B -->|passes| C[Table]
    B -->|fails, warn| C
    B -->|fails, drop| D[Skipped and counted]
    B -->|fails, fail| E[Update stops]

A simple habit helps. Start with warn. Look at the numbers for a few days. Then move a rule to drop or fail once you understand it.

Try It in Databricks Free Edition

Lakeflow Declarative Pipelines run in Databricks Free Edition. Open Jobs & Pipelines, create an ETL pipeline, and add a SQL file with the code below.

First, a small source table. Run this in a notebook or the SQL editor.

CREATE OR REPLACE TABLE workspace.default.raw_orders AS
SELECT * FROM VALUES
  (1, 'C001', 120.00, DATE'2026-11-10'),
  (2, 'C002', NULL,   DATE'2026-11-10'),
  (3, NULL,   80.50,  DATE'2026-11-11'),
  (4, 'C004', -15.00, DATE'2026-11-11'),
  (5, 'C005', 42.00,  DATE'2026-11-12')
AS t(order_id, customer_id, amount, order_date);

Now the pipeline file. Each CONSTRAINT line is one expectation.

CREATE OR REFRESH MATERIALIZED VIEW clean_orders (
  CONSTRAINT valid_amount     EXPECT (amount IS NOT NULL AND amount > 0) ON VIOLATION DROP ROW,
  CONSTRAINT has_customer     EXPECT (customer_id IS NOT NULL),
  CONSTRAINT has_order_id     EXPECT (order_id IS NOT NULL) ON VIOLATION FAIL UPDATE
)
AS
SELECT
  order_id,
  customer_id,
  amount,
  order_date
FROM workspace.default.raw_orders;

Run the pipeline. Here is what you should see.

Rows 2 and 4 are dropped, because their amount is empty or negative. Row 3 is kept, but it is counted as a warning, because it has no customer. The update does not fail, because every row has an order ID.

Click the clean_orders table in the pipeline graph. The Data quality panel shows how many rows passed and failed each rule. This is the part people miss. You do not only clean data. You get a running record of how healthy your source is.

Python Works the Same Way

If your team writes Python, the rules look like this.

from pyspark import pipelines as dp

@dp.materialized_view
@dp.expect_or_drop("valid_amount", "amount IS NOT NULL AND amount > 0")
@dp.expect("has_customer", "customer_id IS NOT NULL")
@dp.expect_or_fail("has_order_id", "order_id IS NOT NULL")
def clean_orders():
    raw_orders_df = spark.read.table("workspace.default.raw_orders")
    return raw_orders_df

Same three choices. Same results.

Where Should the Rules Live?

A good pattern follows the medallion layers.

In Bronze, keep almost everything. Use warn rules only. You want a true copy of what arrived.

In Silver, drop rows that would break joins or totals, like empty amounts or impossible dates.

In Gold, use fail for anything that would make a business number wrong. A broken Gold table is better than a quietly wrong one.

Do Not Lose the Bad Rows

Dropping a row hides it. Sometimes the business needs to see it. A simple fix is a second table that keeps only the failures.

CREATE OR REFRESH MATERIALIZED VIEW quarantine_orders
AS
SELECT *
FROM workspace.default.raw_orders
WHERE NOT (amount IS NOT NULL AND amount > 0);

Now support teams can review bad orders, and the clean table stays clean.

A Short Checklist

• Name every rule clearly, so the data quality panel reads like a sentence.
• Start new rules with warn, then tighten them.
• Use fail only for rules that mean the data cannot be trusted at all.
• Keep a quarantine table for rows the business may need to fix.
• Check the failure counts each week. A rising number is an early warning about the source.

Why This Matters for Your Career

Anyone can move data from one place to another. The engineers people trust are the ones whose numbers are right on Monday morning. Expectations are a small skill, but they show that you think about quality before someone asks.

They also matter more now that AI agents read your tables. An agent will not notice a negative amount. It will simply repeat it with confidence. Clean, checked tables are how you give AI something worth trusting.

Keep Learning

Chapter by chapter, Thinking in Data Engineering with Databricks walks you through pipelines, Delta tables and data quality using Free Edition. When you feel ready, try the free Data Engineer Associate practice exam. You can also read Delta Lake Change Data Feed Explained and Why AI Projects Fail Before the Model Runs.

Catch bad data early, and the rest of your week gets a lot quieter.

Team BricksNotes