Lakeflow pipelines with data quality controls: block, quarantine and prove
Bad data is usually found once it reaches a report. How to use expectations, quarantine tables and the event log to stop it early and keep evidence.
Most data incidents do not start with a system failure but with bad records slipping through a pipeline: a transaction with no account ID, a posting date in the future, a load that arrived with half its rows. By the time the business notices an odd number on a report, the bad data has passed through three layers and fed several other reports.
A pipeline with quality controls has to answer three questions: where are bad records stopped, who finds out and when, and where is the evidence when an auditor asks. This article shows how to do that with Lakeflow Spark Declarative Pipelines, Databricks’ pipeline framework.
1. Three ways to handle an invalid record
In Lakeflow, quality rules are declared in the table definition as expectations: a name, a SQL condition that is true or false for each record, and an action when the condition fails.
| Action | SQL syntax | Result |
|---|---|---|
| Warn (default) | EXPECT |
The record is still written to the target; passed and failed counts are recorded |
| Drop | EXPECT ... ON VIOLATION DROP ROW |
The record is dropped before writing; the dropped count is recorded |
| Fail | EXPECT ... ON VIOLATION FAIL UPDATE |
The update is cancelled and rolled back; manual intervention is needed before rerunning |
A Silver transactions table can combine all three:
CREATE OR REFRESH STREAMING TABLE silver_transactions(
CONSTRAINT valid_account EXPECT (account_id IS NOT NULL) ON VIOLATION DROP ROW,
CONSTRAINT positive_amount EXPECT (amount > 0),
CONSTRAINT valid_posting_date EXPECT (posting_date <= current_date()) ON VIOLATION FAIL UPDATE
) AS SELECT * FROM STREAM(bronze_transactions);A transaction without an account ID cannot be tied to any customer, so it is dropped. A zero or negative amount is kept but counted, so the data team can watch the trend. A posting date in the future means something is seriously wrong at the source, so the pipeline stops rather than keep writing.
Conditions must be standard SQL: no custom Python functions, external service calls or subqueries against other tables. Rules that compare against another table belong in a validation table, covered in section 4.
2. Which layer each check belongs in
Databricks’ lakehouse delivery material gives each medallion layer its own role, and quality rules should follow it:
- Bronze keeps source data as it arrived, with audit columns such as load date and batch ID, for history and reprocessing. Use only warn here: measure source quality without losing the original.
- Silver is the cleansing layer: null checks, referential integrity against other tables, deduplication. Most drop and quarantine rules live here.
- Gold holds aggregated data for reporting. It needs whole-table checks, such as row counts matching the previous layer or primary keys being unique, with the fail action.
3. Quarantine instead of drop
Drop leaves only a number: how many records were removed. For financial data that is not enough, because analysts need to see each record to fix it at the source or explain it.
Databricks describes a quarantine pattern: add a column to an intermediate table that flags records failing the rules, partition the table by that column, and create two views, one for valid records and one for quarantined ones. Valid records flow on to Silver; quarantined records sit in a table the operations team reviews daily. Nothing disappears without a trace.
4. Stopping downstream tables when a check fails
This is the most commonly misunderstood point. A validation table using FAIL UPDATE, for example one comparing row counts between two tables, fails when it finds a mismatch, but it does not stop other tables in the same pipeline from updating. Expectations decide which records enter a table; they do not orchestrate what runs.
In a triggered pipeline, a failed flow rolls back only that flow, and parallel flows keep running. In a continuous pipeline, the failed flow stops along with every flow that depends on it.
If reports must never refresh from unchecked data, Databricks recommends splitting the validation and the downstream work into two pipelines and orchestrating them with a job, so the downstream step runs only when the validation step succeeds.
5. Monitoring and evidence
Every check result is written to the pipeline event log: passed and failed counts per expectation, dropped counts, along with progress, lineage and user actions on the pipeline. Databricks recommends querying it through the pipeline_events system table, which holds events for every pipeline in the region (currently in Beta). A question like “how many transactions were quarantined last month, and why” becomes a query rather than a hand-built report.
Expectations only check data passing through a pipeline. To catch a table that stops updating or receives fewer rows than usual, Databricks offers Data Quality Monitoring: its anomaly detection learns each table’s history to predict the next update and the expected row count over 24 hours, and flags tables that are late or incomplete. It runs on serverless and is billed separately, so include it in the cost estimate.
What Sancire does here
In the Modernize stage, Sancire builds pipelines with quality rules at each layer, quarantine tables for bad records and a reconciliation report against the source systems for every migration wave. The Migration package takes 4 to 6 weeks per wave and starts at $12,000 per wave. If the numbers on your reports do not match your source systems, the 90-minute discovery workshop is the place to start.
Sources: Databricks documentation on expectations, advanced expectation patterns, the pipeline event log and Data Quality Monitoring (updated 11 and 21 September 2026, read on 29 September 2026). Databricks documentation on managing data quality with expectations and on Data Quality Monitoring anomaly detection. Feature names and status change quickly; check the current documentation before designing.