Data Quality: How to Test and Monitor Your Data Pipelines

Data quality testing is the practice of checking data against explicit expectations, such as no duplicate keys, no missing required fields, and fresh arrivals, at each stage of a pipeline. It works best as two layers: deterministic tests that block known failure modes before bad data is published, and monitoring that catches the unknown ones after it lands. This guide covers the dimensions to measure, where tests belong, how data contracts help, and how to alert without drowning your team.

Why does data quality testing matter?

Application bugs usually fail loudly with an exception or a 500 error. Data bugs fail quietly. A join that fans out, an upstream team renaming a column, or a source that silently stops sending one region's rows will still produce a table that looks healthy. The first person to notice is often a stakeholder asking why a dashboard number changed overnight, and by then the bad data may have fed reports, machine learning features, and downstream extracts.

Testing moves detection closer to the cause. Every hour that bad data sits in a published table widens the blast radius and makes the cleanup more expensive, because you have to find and rebuild everything that consumed it.

What are the dimensions of data quality?

"Quality" is too vague to test directly. Breaking it into dimensions gives you concrete checks you can write.

Accuracy is the hardest to test because you rarely have ground truth. In practice you approximate it with reconciliation checks against a trusted system, or with invariants that must always hold, such as totals adding up.

Where should data quality tests run in a pipeline?

Tests are cheapest where they catch problems before anything downstream consumes the data. A useful layout:

1. At ingestion. Validate schema and basic shape as raw data lands: expected columns exist, types parse, the file is not empty. Reject or quarantine files that fail rather than loading them.
2. After transformation, before publishing. Run uniqueness, not-null, referential integrity, and business-rule tests on the models you are about to expose. This is where most tests live.
3. On published tables. Monitor freshness, volume, and distribution continuously, because some problems only show up over time.

A pattern that makes stage 2 safe is write-audit-publish: build the new version of a table in a staging location, run tests against it, and only swap it into the production name if the tests pass. Consumers never see a half-validated table. Table formats that support branches or atomic swaps make this straightforward, and it maps directly to the practices in data pipeline best practices.

Writing tests in SQL

Most data tests reduce to a query that returns the rows that violate a rule. Zero rows means pass. This convention is simple, works in any warehouse, and makes failures easy to debug because the query shows you the offending records.

-- Uniqueness: any order_id appearing more than once
SELECT order_id, COUNT(*) AS copies
FROM analytics.fct_orders
GROUP BY order_id
HAVING COUNT(*) > 1;

-- Validity: status outside the allowed set
SELECT order_id, status
FROM analytics.fct_orders
WHERE status NOT IN ('placed', 'shipped', 'delivered', 'cancelled')
   OR status IS NULL;

-- Referential integrity: orders pointing to unknown customers
SELECT o.order_id, o.customer_id
FROM analytics.fct_orders AS o
LEFT JOIN analytics.dim_customer AS c
  ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Frameworks package this idea so you do not hand-write every query. dbt, for example, ships generic tests like unique, not_null, accepted_values, and relationships that you declare in YAML next to the model; see dbt explained for how that fits into a transformation workflow. Libraries such as Great Expectations and Soda offer similar declarative checks for other environments.

Severity: block or warn?

Not every failure should stop the pipeline. Give each test a severity:

• Error (blocking): primary key duplicates, nulls in join keys, schema breaks. Publishing would produce wrong numbers.
• Warn (non-blocking): a small share of rows with an unexpected value, a slightly late source. Worth investigating but not worth an outage.

Tests that fail constantly and get ignored are worse than no tests, because they train people to skip alerts. If a test is noisy, fix the threshold or delete it.

What is a data contract?

Many quality incidents start upstream: an application team changes a field and has no idea an analytics pipeline depends on it. A data contract is an explicit agreement between a data producer and its consumers about what a dataset will contain: schema, types, semantics, freshness, and who owns it.

dataset: orders_events
owner: checkout-team
freshness:
  max_delay_minutes: 30
schema:
  - name: order_id
    type: string
    required: true
    unique: true
  - name: amount_cents
    type: integer
    required: true
    constraints:
      min: 0
  - name: currency
    type: string
    allowed_values: [USD, CAD, EUR]
breaking_change_policy: announce 30 days ahead, publish new version

The format matters less than two properties: the contract lives in version control, and it is enforced automatically, ideally in the producer's CI so a breaking change fails their build before it ships. A contract that only exists in a wiki page will drift. Schema registries for event streams, such as those used with Kafka, enforce the schema portion of a contract at write time.

How do you monitor data quality in production?

Tests only catch what you thought to check. Monitoring catches the rest by watching metrics over time and flagging deviations. This is often called data observability, and it borrows directly from service observability.

Metrics worth tracking per table

• Freshness: time since the last successful load or the newest event timestamp.
• Volume: row count per load or per partition.
• Null and distinct rates: per important column.
• Distribution: min, max, mean, or percentiles of key numeric fields.
• Schema: columns added, removed, or retyped.

Static thresholds vs anomaly detection

A static threshold such as "row count must exceed 10,000" is easy to reason about but breaks when volume naturally grows or has weekly seasonality. Anomaly detection compares today's value against a baseline built from history, for example the same weekday over the last several weeks, and flags values outside an expected band.

A reasonable default is static checks for hard rules (freshness SLAs, zero duplicates) and anomaly detection for volume and distribution, where "normal" moves over time.

How should data quality alerts be handled?

Alerting is where many programs fail. A few rules keep it useful:

• Route to the owner. Every table needs a named owning team, and alerts go to that team's channel, not a shared firehose.
• Include context. The alert should say which table, which test, how many rows failed, a sample query, and what is downstream.
• Use lineage for impact. Knowing that a failed source feeds three executive dashboards changes the priority. Lineage tools make this visible.
• Track incidents. Record what broke, why, and which test would have caught it earlier. Add that test.

Key takeaways

• Break data quality into testable dimensions: accuracy, completeness, freshness, uniqueness, validity, and consistency.
• Write tests as queries that return violating rows, and run them before publishing using write-audit-publish.
• Separate blocking errors from warnings, and remove tests that nobody acts on.
• Data contracts shift quality left by making producers responsible for schema and semantics, enforced in CI.
• Monitoring with anomaly detection catches the problems your tests did not anticipate.
• Alerts need owners, context, and lineage, or they become noise.

Frequently asked questions

What is the difference between data testing and data observability?

Data testing checks explicit rules you define in advance, such as a column being unique. Data observability continuously monitors metrics like freshness, volume, and distributions to detect unexpected changes. You need both: tests for known failure modes, observability for unknown ones.

How many data quality tests should a table have?

Start with primary key uniqueness, not-null on required and join columns, and a freshness check on every published table. Add business-rule tests where a wrong value would cause a real decision error. Coverage of critical tables matters more than raw test count.

Should failed data quality tests stop the pipeline?

Blocking tests should stop publishing for the affected table and anything downstream of it, since shipping known-bad data is usually worse than shipping late data. Warning-level tests should notify the owner without halting the run.

Who is responsible for data quality?

The team that produces a dataset owns its quality, and consumers own clearly stating what they depend on. Data contracts formalize this split so problems are caught at the source instead of discovered downstream.