Design a Data Quality Framework

Approach

A data quality framework is less about the check library and more about where checks run, what happens when they fail, and who gets told. A framework that only produces red dashboards nobody reads is worse than none. Ask:

• What failures hurt most today? Duplicates, missing data, late data, wrong values, schema breaks?
• Which datasets are critical, and what is the cost of publishing bad data versus publishing late?
• Engines and tools: warehouse with dbt, Spark, streaming? Any existing tests?
• Ownership: does every dataset have an owner who can act on an alert?
• Producers: are upstream application teams willing to own contracts?
• Regulatory needs: audit trails of quality for finance or regulated reporting?

Architecture

1. Checks as code: owners declare expectations in YAML next to each model or table; CI validates the definitions.
2. Ingestion gate: schema and row-level validation (types, required fields, allowed values); invalid rows go to a quarantine table with a reason.
3. Transformation gate: write-audit-publish. Build into a staging table or branch, run table-level checks (keys, nulls, ranges, referential integrity, volume, reconciliation), publish only if blocking checks pass.
4. Consumption checks: freshness SLAs and metric reconciliations on published data.
5. Results store: every check result (dataset, check, run, observed value, threshold, pass or fail, severity) appended to one table.
6. Routing and visibility: alerts to owners with lineage context; badges in the catalog; a quality dashboard with trends.

Tonight the orders pipeline loads 50 million rows. At ingestion, 1,200 rows have an unknown currency code and are quarantined; the batch continues and a warning records the quarantine rate (0.002%). The silver build writes to a staging table, and checks run: order_id unique, amount >= 0, every customer_id exists in dim_customer, row count within 20% of the same weekday’s baseline. Everything passes, so the staging version is published atomically. On another night the duplicate check fails: the table is not published, yesterday’s version remains, the owning team is paged with the failing check and a sample of duplicate keys, and the dashboard shows a “data delayed” banner.

Types of checks

Dimension	Example checks	Typical gate
Schema	Expected columns and types, contract match	Ingestion, build time
Validity	Allowed values, ranges, regex formats	Ingestion (row level)
Completeness	Null rates, required fields, all partitions present	Transformation
Uniqueness	Primary or grain key unique	Transformation (blocking)
Consistency	Foreign keys exist, totals equal sum of lines	Transformation
Volume	Row count within a band of a baseline	Ingestion and transformation
Freshness	Latest record or load within SLA	Consumption, source freshness
Accuracy	Reconciliation with system of record (ledger, source counts)	Consumption
Distribution	Mean, quantiles or category mix drift	Monitoring (usually warn)

Checks, severity and the publish decision

Each check has a severity: block (data must not be published), warn (publish, alert owner), info (record only). The example below runs on PostgreSQL 16: three checks write results, and the publish decision depends only on blocking checks.

CREATE TABLE orders (
  order_id    INT,
  customer_id INT,
  amount      NUMERIC(10,2),
  loaded_at   TIMESTAMP
);
INSERT INTO orders VALUES
  (1, 10, 25.00, '2026-10-05 02:00'),
  (2, 11, NULL,  '2026-10-05 02:00'),
  (2, 11, 40.00, '2026-10-05 02:00'),
  (3, NULL, -5.00, '2026-10-05 02:00');

CREATE TABLE dq_results (
  dataset    TEXT,
  check_name TEXT,
  severity   TEXT,
  observed   NUMERIC,
  threshold  NUMERIC,
  passed     BOOLEAN
);

INSERT INTO dq_results
SELECT 'orders', 'order_id_unique', 'block',
       count(*) - count(DISTINCT order_id), 0,
       count(*) - count(DISTINCT order_id) = 0
FROM orders
UNION ALL
SELECT 'orders', 'customer_id_null_rate', 'warn',
       round(avg(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END), 3), 0.01,
       avg(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) <= 0.01
FROM orders
UNION ALL
SELECT 'orders', 'amount_non_negative', 'block',
       count(*) FILTER (WHERE amount < 0), 0,
       count(*) FILTER (WHERE amount < 0) = 0
FROM orders;

SELECT check_name, severity, observed, threshold, passed FROM dq_results ORDER BY check_name;

SELECT CASE WHEN bool_and(passed) FILTER (WHERE severity = 'block') THEN 'publish' ELSE 'hold' END AS decision
FROM dq_results;

      check_name       | severity | observed | threshold | passed
-----------------------+----------+----------+-----------+--------
 amount_non_negative   | block    |        1 |         0 | f
 customer_id_null_rate | warn     |    0.250 |      0.01 | f
 order_id_unique       | block    |        1 |         0 | f

 decision
----------
 hold

In a real framework, these checks are generated from YAML declarations rather than written by hand, and the results row also carries the run id, timestamp, engine and a link to sample failing rows. Storing the observed value, not just pass or fail, is what makes trends and threshold tuning possible.

Write-audit-publish

The safest gate for critical tables:

1. Write the new data to a staging location: a staging table, a Delta or Iceberg branch, or a new table version that is not yet exposed.
2. Audit: run blocking checks against the staged data.
3. Publish: atomically swap or merge the staged data into the production table (an ALTER TABLE ... SWAP WITH in Snowflake, a fast-forward of an Iceberg branch, or a single MERGE/overwrite commit in Delta).

If audit fails, production still holds the last good version. Pair this with a clear status (“data delayed”) for consumers; holding data is only better than publishing it if users know it is held.

Row-level validation and quarantine

At ingestion, validate each record against the schema and simple rules. Invalid rows go to <table>_quarantine with the raw record, the failed rule and the batch id. Rules:

• Quarantine rate is itself a check: above a threshold (say 1%), escalate to blocking, because many invalid rows usually means an upstream change, not a few bad records.
• Quarantined rows are replayable after a fix.
• Quarantine tables have retention and owners, so they do not become a silent dumping ground.

In streaming pipelines, the same idea is a dead-letter topic with error metadata.

Thresholds without false alarms

Static thresholds (“row count between 1 and 10 million”) break on growth and seasonality. Better:

• Compare with the same weekday over recent weeks, or a rolling median with a tolerance band.
• Use relative thresholds (within 20% of baseline) for volume, absolute ones for invariants (zero duplicates).
• Let owners snooze or adjust checks with a reason recorded.
• Track alert precision: the share of alerts that led to action. Checks that never lead to action are downgraded or removed.

Large tables: checking cheaply

• Check only new partitions (today’s load), not the whole table.
• Compute many checks in one scan: null counts, min/max, distinct-key counts and range violations can all come from one aggregate query; Deequ-style analysers do this in Spark.
• Use approximate distinct counts for very large uniqueness checks where an exact one is too expensive, and an exact check on the key only where duplicates would be harmful.
• Reuse file and table statistics for min/max and null counts when the format keeps them.

Contracts with producers

Many quality problems start upstream, when an application team changes a column or an event. A data contract is an agreement, in code, on schema, semantics, freshness and quality for a dataset the producer publishes:

• For events: a schema-registry subject with compatibility rules, checked in the producer’s CI.
• For tables: a contract file (columns, types, constraints, SLAs) validated against the producer’s database migrations in CI.
• Breaking changes need a new version and a migration period.

Contracts move detection from “a dashboard is wrong” to “the pull request is red”.

Ownership and alert routing

• Every dataset has an owning team in the catalog; every check inherits it.
• Alerts include the dataset, the check, observed versus expected values, a sample of failing rows (masked if sensitive), recent changes upstream (from lineage) and a runbook link.
• Route upstream: if a source-volume check fails, the alert goes to the source owner, and downstream owners get an informational notice.
• Incidents for critical datasets follow the normal on-call process.

Security

Failing-row samples may contain personal data. Store samples in restricted tables, mask sensitive columns in alerts, and keep only identifiers where possible.

Failure modes and recovery

Failure	Effect	Response
Blocking check fails close to SLA	Data held, SLA at risk	Owner paged; options are fix and rerun, or a documented override with sign-off
Check itself is wrong	False blocks or missed issues	Checks are code: reviewed, tested, versioned; track false alarms
Quarantine grows silently	Hidden data loss	Quarantine rate check with escalation
Results store down	No visibility	Pipelines still evaluate checks locally and gate; results are written when the store recovers
Upstream breaking change	Mass failures	Contract checks in producer CI; ingestion schema check stops early

Scaling to 10×

At 150,000 checks a day: generate standard checks automatically from metadata (every primary key gets a uniqueness check, every foreign key a relationship check, every table a freshness check), so owners only write business rules. Partition the results store by date and dataset. Move anomaly detection on observed values to a scheduled job over the results store rather than per pipeline.

Monitoring and SLAs

• Share of critical datasets with checks, and checks per dataset.
• Pass rate per dataset and team; mean time to detect and to resolve quality incidents.
• Alert precision and number of overrides.
• Incidents found by consumers before the framework (the number to drive towards zero).

Capacity estimate

• Results volume: 15,000 checks/day × about 200 bytes per result ≈ 3 MB/day; a year is about 1 GB. The results store is tiny; keep it all.
• Runtime overhead: a combined profile scan of a 2-billion-row daily partition costs roughly one extra read of that partition. If the build itself does several passes, one extra pass is typically within the 10% target; uniqueness on a large key is the expensive check, so run it on the new partition and keys only.
• Quarantine: at 0.01% invalid rows on 2 billion rows, 200,000 rows/day, a small table.
• Alert load: with 15,000 checks and a 0.5% daily failure rate, about 75 failures a day; grouping by dataset and run turns them into perhaps 20–30 actionable alerts for 30 teams, which is manageable.

What a strong answer includes

• Gates at three points (ingestion, transformation, consumption) with different check types.
• Severity levels and a clear publish decision.
• Write-audit-publish so consumers never see data that failed blocking checks.
• Quarantine with monitoring, and DLQs for streaming.
• Adaptive thresholds and alert-precision tracking to avoid alert fatigue.
• Results stored with observed values for trends and audits.
• Ownership and routing, and contracts with producers to prevent problems.

Common mistakes

• Running tests after publishing, so consumers see bad data first.
• Failing the whole pipeline for any warning-level issue.
• Dropping invalid rows silently.
• Static volume thresholds that fire every weekend.
• Alerts with no owner, no context and no runbook.
• Checking full history every run on billion-row tables.
• Treating quality as the data team’s job alone, with no contracts upstream.