Data Quality Checks in Databricks: Expectations, DQX and SQL

Data quality checks in Databricks come from four places, and picking the wrong one costs weeks. Lakeflow Declarative Pipelines expectations enforce row level rules as data is written, but only inside a pipeline. DQX, the Databricks Labs framework, applies checks to PySpark DataFrames anywhere you can run Spark. Unity Catalog anomaly detection needs no rules at all and watches freshness and completeness across a whole schema. Plain SQL assertions in a job cover everything else. This is the working code for each, and the documented limit that decides which one fits.

If you are comparing approaches at a buyer level rather than writing code today, the full side by side with every documented ceiling lives on our Databricks data quality monitoring page.

Expectations: rules enforced inside a pipeline

An expectation has three parts: a name used for tracking, a SQL conditional that must evaluate to true or false for each record, and an action taken when a record violates it. In SQL that reads almost like a table constraint.

CREATE OR REFRESH STREAMING TABLE orders_clean(
  CONSTRAINT valid_order_id   EXPECT (order_id IS NOT NULL) ON VIOLATION FAIL UPDATE,
  CONSTRAINT valid_amount     EXPECT (amount >= 0)          ON VIOLATION DROP ROW,
  CONSTRAINT plausible_region EXPECT (region IN ('NA','EMEA','APAC','LATAM'))
)
AS SELECT * FROM STREAM(source.raw.orders);

The Python form uses decorators, one per action:

@dp.table
@dp.expect_or_fail("valid_order_id", "order_id IS NOT NULL")
@dp.expect_or_drop("valid_amount", "amount >= 0")
@dp.expect("plausible_region", "region IN ('NA','EMEA','APAC','LATAM')")
def orders_clean():
    return spark.readStream.table("source.raw.orders")

The detail that trips people up is the default. Plain expect, with no suffix, is a warn: invalid records are still written to the target and simply counted. Only expect_or_drop removes them before the write, and only expect_or_fail stops the update and demands manual intervention. Teams routinely add a dozen expectations, watch the Data quality tab fill with violations, and never notice that every bad row landed in the table anyway.

Three constraints are worth knowing before you design around expectations. They work only within pipelines, on streaming tables, materialized views, and temporary views, so a Delta table written by a notebook or an ordinary job cannot carry one. A constraint clause cannot contain custom Python functions, external service calls, or subqueries, which rules out any check that needs a lookup against another table. And they are not supported with AUTO CDC FROM SNAPSHOT.

DQX: checks on any PySpark DataFrame

That subquery limitation is the usual reason teams reach for DQX. It is a data quality framework for Apache Spark from Databricks Labs, and it applies row and column level rules to DataFrames in batch or structured streaming, defined either as code or as a config file.

from databricks.labs.dqx.engine import DQEngine

checks = [
    {"name": "amount_not_negative", "criticality": "error",
     "check": {"function": "is_not_less_than",
               "arguments": {"column": "amount", "limit": 0}}},
    {"name": "region_known", "criticality": "warn",
     "check": {"function": "is_in_list",
               "arguments": {"column": "region",
                             "allowed": ["NA", "EMEA", "APAC", "LATAM"]}}},
]

dq = DQEngine(spark)
valid_df, quarantine_df = dq.apply_checks_by_metadata_and_split(df, checks)

The split is the useful part. Rather than choosing between dropping a bad record and failing the whole run, you get a clean DataFrame and a quarantine DataFrame, so bad rows are preserved for someone to look at instead of vanishing. DQX also carries warning and error severity levels, profiles data to generate candidate rules rather than making you write every one by hand, can build checks from ODCS data contracts, and ships a browser UI and quality dashboards.

Two things to be clear eyed about. DQX is a Databricks Labs project rather than a supported Databricks product, so upgrades, breakages, and operational failures are yours. And it operates on PySpark DataFrames, meaning it covers the code you deliberately route through it. Tables written by SQL warehouses, external tools, or a colleague notebook are outside it entirely.

Unity Catalog anomaly detection: the checks you did not write

Both of the above share a property that becomes a problem at scale: every check exists because a person thought of it. Unity Catalog anomaly detection inverts that. You enable it on a schema, which requires the MANAGE privilege on that schema or its parent catalog, and Databricks builds a model per table from history.

Freshness works by analyzing the history of commits to a table and predicting when the next one should arrive. If a commit is unusually late, the table is marked stale. Completeness analyzes historical row counts, predicts the range of rows expected in a 24 hour window, and marks a table incomplete when the actual count falls below the lower bound. A percent null check is documented as beta. For each unhealthy table it then uses Unity Catalog lineage to work out whether the problem started there or upstream.

Results land in a system table, which is where you read them programmatically:

SELECT table_catalog, table_schema, table_name,
       freshness_status, completeness_status, evaluated_at
FROM   system.data_quality_monitoring.table_results
WHERE  freshness_status = 'STALE'
   OR  completeness_status = 'INCOMPLETE'
ORDER  BY evaluated_at DESC;

Three documented boundaries matter. Anomaly detection does not support views or foreign tables, which is awkward because views are frequently what analysts and BI tools query. Completeness does not account for the fraction of nulls, zero values, or NaN, so a load that arrives with the right row count and an empty column reads as perfectly healthy. And scanning is deliberately uneven: Databricks scans each table at roughly the frequency it is updated and reduces frequency for tables it judges less critical by popularity and downstream usage, with the documented consequence that health indicators for a skipped table can be delayed by up to two weeks.

Data profiling for distribution, with a 30 day memory

The fourth route is data profiling, the feature that reached general availability in August 2024 as Lakehouse Monitoring. You create a monitor on one specific table, choose a time series, inference, or snapshot analysis, and Databricks generates a profile metric table, a drift metrics table, and a dashboard. It is the only native way to watch how a column distribution moves.

Its limits are size and memory. Snapshot profiles cap at a 4TB table size, above which Databricks recommends time series profiles. Time series and inference profiles compute metrics over the last 30 days only, and when first created they analyze only the 30 days prior to creation, so there is no deep backfill of history you already hold. Monitors on materialized views do not support incremental processing. Notifications support at most five email addresses per event type.

Which check catches which failure

Read down that table and the pattern is clear enough. Rule based checks catch the failures you predicted. Learned baselines catch the failures you did not. Neither catches schema drift on a table outside a pipeline, which is the one gap none of the four close.

A practical starting order

Enable Unity Catalog anomaly detection first, on the schemas that carry the business. It needs no rules, no thresholds, and no per table work, so it is the highest coverage per hour of effort available on the platform, and it will tell you things about your own tables you did not know.

Then add expectations, but only for invariants that a statistical baseline could never know: a discount percentage that must never exceed 40, an order that must carry a valid region, a status field with a closed set of values. Use ON VIOLATION FAIL UPDATE on the handful where a bad record must never land, and warn everywhere else. Resist the urge to encode the general shape of the data as rules, because that rule set only ever grows, and the person who wrote each rule leaves before it needs revisiting.

Reach for DQX when you need quarantine behavior or a check that requires a lookup, both of which pipeline expectations cannot do. Add a data profiling monitor on the small number of tables whose distributions genuinely matter, accepting the 30 day window.

Pay particular attention to data arriving from outside your own systems. A partner feed or a source you collect from the public web can restructure overnight with no warning and no release note, and a loader will happily write the mess into Delta, which is why teams that pull external sources tend to put the cleaning step at the point where they turn raw web pages into structured records rather than trying to repair it after landing.

Where this stops being a code problem

Everything above is detection. What none of it provides is the operational layer around detection, and that is where most of the effort actually goes. There is no deduplication, so a table that has been stale for three days produces an event on every scan. There is no ownership model, so findings reach whoever enabled the feature rather than whoever owns the dataset. Native alerting notifies selected workspace users by email, capped at five addresses per event type on the profiling side, which is a notification rather than an on call path. There is no acknowledgement or resolution state, so nobody can see whether an incident is being handled. And nothing monitors the monitoring, so a scan that quietly stops running looks exactly like a warehouse where nothing is wrong.

You can build all of it. A scheduled job over system.data_quality_monitoring.table_results, a state table so repeats do not re alert, an ownership map, a webhook into Slack. None of it is hard, which is precisely why it gets left off the estimate. It is also permanent work, and it competes directly with the pipelines you were hired to build.

If you would rather not own that layer, Dataobservability connects to Databricks with a read only service principal, learns each Delta table normal freshness, row count, null rate, and distribution from its own history, catches the schema drift none of the native routes watch, and routes deduplicated alerts to Slack and PagerDuty by dataset owner. Setup is a read-only connection, pricing starts at 99 dollars a month, and the trial needs no credit card. The wider picture of how the platform fits a lakehouse is on our Databricks data observability page, and if you want the same treatment for another warehouse, we have written up Snowflake anomaly detection and BigQuery anomaly detection the same way.