Why did an expectation drop all my rows?

Summary

• An expectation only removes rows when its action is drop: @dp.expect_or_drop, @dp.expect_all_or_drop, or ON VIOLATION DROP ROW in SQL. The default action, plain @dp.expect or EXPECT, writes invalid records to the target and only counts them.
• If every row disappeared, every row failed the constraint. That usually means the rule is wrong for the data: a comparison against a NULL column, an inverted condition, or a literal that never matches.
• Expectations are a Lakeflow Pipelines feature on Databricks. Databricks lists @dp.expect(...) among the features that are not part of Apache Spark, and the open-source Spark Declarative Pipelines (SDP) programming guide documents no expectations API.
• To debug, switch the decorator to @dp.expect so rows are kept, then read each expectation's pass and fail counts in the Data quality tab, or the passed_records and failed_records fields in the pipeline event log.

Why did an expectation drop all my rows?

Because the expectation's action was drop and every row failed it (or, with expect_all_or_drop, failed at least one of its rules). Only @dp.expect_or_drop, @dp.expect_all_or_drop and SQL's ON VIOLATION DROP ROW remove records. The default action keeps invalid records in the target and logs the count. A rule that rejects every row is almost always a broken rule, not broken data, so check NULL handling, the direction of the condition, and the literals it compares against.
A scoping note for open-source users: expectations aren't part of Apache Spark. Spark Declarative Pipelines (SDP), added in Apache Spark 4.1, documents datasets, flows and sinks but no expectations API, and Databricks lists @dp.expect(...) among the features that are not part of Apache Spark. In an open-source SDP pipeline, row filtering comes from the query that defines each dataset, so look there.

How do expectation actions handle invalid rows?

Action	Python	SQL	What happens to an invalid record
warn (default)	@dp.expect, @dp.expect_all	EXPECT (...)	Written to the target; valid and invalid counts are logged
drop	@dp.expect_or_drop, @dp.expect_all_or_drop	EXPECT (...) ON VIOLATION DROP ROW	Dropped before data is written; the dropped count is logged
fail	@dp.expect_or_fail, @dp.expect_all_or_fail	EXPECT (...) ON VIOLATION FAIL UPDATE	The update stops; only that flow fails, and manual intervention is required before reprocessing

The expect_all variants take a Python dictionary of {description: constraint} pairs. With expect_all_or_drop, a record is dropped when it fails one or more of the rules.

How do I write the expectation correctly?

The constraint is a SQL conditional statement passed as a string, not a PySpark Column. The decorator goes after @dp.table (or @dp.materialized_view / @dp.temporary_view) and before the function that defines the dataset.

from pyspark import pipelines as dp

@dp.table
@dp.expect_or_drop("valid_amount", "amount IS NOT NULL AND amount > 0")
def orders_clean():
    return spark.readStream.table("raw_orders")

Older pipelines use import dlt and @dlt.expect_or_drop. Databricks says the dlt module has been replaced by the pyspark.pipelines module; you may still use dlt, but Databricks recommends pipelines. The SQL form of the same rule:

CREATE OR REFRESH STREAMING TABLE orders_clean (
  CONSTRAINT valid_amount EXPECT (amount IS NOT NULL AND amount > 0) ON VIOLATION DROP ROW
) AS SELECT * FROM STREAM(raw_orders);

What makes a constraint reject every row?

• NULLs. In Spark SQL, amount > 0 returns NULL when amount is NULL, and = NULL never returns true. The expectations docs say a constraint "must evaluate to true or false for each record" but don't say how a NULL result is treated, so spell it out: amount IS NOT NULL AND amount > 0, or amount IS NULL OR amount > 0 if NULL is acceptable.
• An inverted rule. The constraint describes a valid row. Writing amount < 0 to "catch negatives" marks every positive amount invalid.
• Contradictions. status = 'open' AND status = 'closed' can never be true.
• Literals that don't match the data, such as a status value or date format spelled differently upstream.
• Stacked rules. Under expect_all_or_drop, one bad rule in the dictionary drops every row on its own.

How do I find which expectation dropped the rows?

Keep the rows first: change @dp.expect_or_drop to @dp.expect (or remove ON VIOLATION DROP ROW) so invalid records are written and counted. Then read the metrics in the dataset's Data quality tab, or query the event log, where flow_progress events carry per-expectation passed_records and failed_records plus a dropped_records total. Only the owner of a streaming table or materialized view can call the event_log function.

-- Does the rule pass any source rows?
SELECT count(*) AS total_rows,
       count_if(amount > 0) AS passing,
       count_if(amount IS NULL) AS null_amount
FROM raw_orders;

-- Per-expectation metrics from the pipeline event log
SELECT timestamp, details:flow_progress:data_quality:expectations AS expectations
FROM event_log(TABLE(orders_clean))
WHERE event_type = 'flow_progress'
ORDER BY timestamp DESC;

If passing is zero, the rule is the problem, not the pipeline.

How Databricks approaches pipeline data quality

Expectations belong to Lakeflow Pipelines, which supports them on streaming tables, materialized views and views, in Python and SQL. Their metrics land in the pipeline UI and the event log without extra code. A practical policy: start a new rule as @dp.expect until its pass rate looks right, use @dp.expect_or_drop only for records you can safely discard, and reserve @dp.expect_or_fail for rules where one bad record must stop the flow.

FAQs

Are expectations available in open-source Spark Declarative Pipelines?

Not as of the Spark 4.2 documentation. The SDP programming guide covers materialized views, streaming tables, temporary views, flows and sinks, and Databricks lists @dp.expect(...) among the features that are not part of Apache Spark.

Does a warn expectation ever drop rows?

No. With the default action, invalid records are written to the target and counted in the metrics.

Why does failed_records show failures when nothing was dropped?

failed_records tracks whether the expectation was met but doesn't describe what happens to the records (warn, fail or drop). Check dropped_records for rows that were actually removed.

Do I have to rewrite dlt code?

No. dlt still works, but Databricks recommends the pyspark.pipelines module, imported as from pyspark import pipelines as dp.