Data quality and monitoring

Green jobs and wrong numbers

A job can succeed every night and still deliver wrong data. A source sends half the rows, a currency column changes meaning, a join starts to duplicate. The scheduler sees success, and the CFO sees a revenue figure that is off by a fifth. Data quality work closes that gap: it checks the data, not only the job.

In this lesson we separate five kinds of check, place each one at the layer where it belongs, and set up ownership and alerts. We do not cover data catalogue tooling or master data management.

Five kinds of check

Put the check where the failure is cheapest to understand. A bad value is visible on one row, so check it per record in silver. A missing delivery is visible only over time, so monitor it on the table. A mismatch with the source is visible only against an outside number, so compare in the job.

Per-record checks: expectations

You met expectations in lesson 3. They check every record against a SQL condition and either warn, drop or fail. For quality work, two habits matter.

• Count what you drop. Expectation metrics for warn and drop are recorded. Put the drop rate on a dashboard. A drop rate that jumps from 0.1 to 8 per cent tells you a source changed before any user does.
• Promote rules over time. Start new rules as warn, observe the violation counts, then promote to drop or fail with the data owner's agreement.

Table-level checks: data quality monitoring

Databricks provides data quality monitoring for assets in Unity Catalog. The documentation describes two capabilities.

Anomaly detection monitors all tables in a schema with one click. It prioritises important tables, skips low-impact ones, and analyses historical patterns to judge each table's freshness and completeness. Freshness measures how recently a table was updated: a per-table model predicts the next commit, and an unusually late commit marks the table as stale. Completeness compares the rows committed in the last 24 hours against a predicted range, and marks the table incomplete when the count falls below the lower bound.

Data profiling gives summary statistics for a table and tracks how the data distribution changes over time. The documentation says it was formerly known as Lakehouse Monitoring, and that it can also monitor ML models and inference tables.

Account and metastore admins can review data quality health, such as the percentage of healthy tables, in Governance Hub. The documentation notes that monitoring does not modify the monitored tables and adds no overhead to the jobs that populate them. It runs on serverless compute and is billed as serverless DBUs under the DATA_QUALITY_MONITORING billing product, with spend depending on the number and size of monitored tables and how often they are evaluated.

The page does not state whether these capabilities are generally available, in preview or in beta, so check the release notes for the status in your region before you build a client commitment on them. Switching on anomaly detection for a schema is a low-risk first step in any case, because it only reads metadata and history.

Reconciliation: the check that finds the big errors

Validity checks and anomaly detection do not know the correct total. Only an outside number does. In every domain pipeline we add a reconciliation task that compares one or two control figures, for example:

-- Compare the row count loaded today with the control figure the supplier sent.
-- control.supplier_totals is a small table that ingestion fills from the supplier's control file.
SELECT
  s.delivery_date,
  s.expected_rows,
  COUNT(o.order_id)                          AS loaded_rows,
  COUNT(o.order_id) - s.expected_rows        AS difference
FROM   dev_sales.control.supplier_totals s
LEFT JOIN dev_sales.silver.orders o
       ON DATE(o.order_ts) = s.delivery_date
GROUP  BY s.delivery_date, s.expected_rows
HAVING COUNT(o.order_id) <> s.expected_rows;

If the query returns rows, the task fails and the downstream publication stops (lesson 4). The task is simple, and it catches the failure that costs the most.

Data contracts

A data contract writes down what a producer promises and a consumer can rely on: schema, meaning of fields, freshness, allowed values and who to call. It turns quality from a hope into an agreement. Our post on enforcing data contracts before you publish shows how we check the contract in the pipeline, so a breaking change is caught before consumers see it.

A contract is only as good as its enforcement. Put each promise in an expectation, a monitor or a reconciliation, and keep the contract next to the code in the same repository.

Ownership and alerts

Every dataset needs a named owner group. A check without an owner fails quietly forever.

• Route alerts to the owner. A quality alert for the sales silver table goes to the sales data team, not to a shared inbox.
• Alert on a short list. Failed expectations of type fail, a stale or incomplete table, a failed reconciliation, a drop rate above its limit. More alerts mean more ignored alerts.
• Write the response. For each alert, say what the owner does first. Our runbook habit from the agents course applies here as well: one action per line.
• Close the loop. Every incident ends with a new check that would have caught it.

Lineage helps you find impact

When a check fails, you need to know who is affected. Unity Catalog captures lineage automatically for queries run on Databricks, down to column level, and exposes it in the system.access.table_lineage and system.access.column_lineage system tables. We use these to tell consumers which dashboards sit downstream of a failed table. Lesson 5 of the governance course covers lineage in more detail.

Common mistakes

• Alert fatigue. Dozens of checks that all page the same person.
• Silent drops. Rows disappear and nobody counts them.
• Checking only validity. You miss missing data.
• No control figures. You cannot tell a quiet day from a broken source.
• Fixing the data and not the check. The next incident is the same one.

Summary

Use expectations for each record, table monitoring for freshness and completeness, and reconciliation for the totals. Write contracts, assign owners, alert on a short list and add a check after every incident.

Next lesson: cost and compute choices, where the same discipline meets the bill.