$ dbt build --select fct_orders # tests run before anyone sees the data

Data Quality

Data quality is how well data fits the job it is used for: are all the rows there, is each one recorded once, are values allowed and true, do systems agree, and did it arrive in time? You can’t improve “quality” in general, so data teams break it into dimensions, write each as a testable rule, and run those rules inside the pipeline. Rows that fail are stopped and reported before they reach a dashboard, a model or a customer.

A batch through the checks

$ soda scan -d warehouse checks/orders.yml
Six dimensions, one batch of orders
completenessuniquenessvalidity accuracyconsistencytimeliness order_idcustomeramountstatus 1001c-1240.00paid 1002NULL25.50paid 1003c-3112.00shipped 1001c-1240.00paid 1004c-0818.20shiped 1005c-19990.00paid 1006c-7733.10paid 1007c-1264.90placed completeness ✗ customer_id is null uniqueness ✗ order_id 1001 appears twice validity ✗ status 'shiped' not allowed accuracy ✗ payments system says 99.00 consistency ✗ c-77 not in customers table timeliness ✓ batch landed 05:12 · SLA 06:00 fct_orders 3 rows loaded ✓ quarantine.orders 5 rows + failing rule ALERT #orders-data owner: checkout team
  1. A batch of eight orders lands. Each check below tests one dimension and flags the rows that break its rule.
  2. Completeness: required fields are present. Order 1002 has no customer.
  3. Uniqueness: each order is recorded once. Order 1001 arrived twice, a typical retry or replay bug.
  4. Validity: values match the allowed format, type or set. shiped is not a known status.
  5. Accuracy: the value matches reality. Order 1005 says 990.00, but the payments system charged 99.00.
  6. Consistency: datasets agree with each other. Customer c-77 does not exist in the customers table.
  7. Timeliness is checked per batch, not per row: the data landed at 05:12, before the 06:00 SLA.
  8. Three clean rows load into fct_orders. The five bad rows go to quarantine with the rule they broke, and the owner gets an alert.

The dimensions

Dimension Question Example rule
Completeness Is everything there? customer_id is not null; today’s row count is within 30% of the 7-day average
Uniqueness Is each thing recorded once? order_id is unique
Validity Does it follow the format and allowed values? status in (‘placed’, ‘paid’, ‘shipped’, ‘cancelled’); amount >= 0; email matches a pattern
Accuracy Is it true? amount matches the payment provider’s record; spot checks against the source
Consistency Do datasets agree? every customer_id exists in customers; sum of order lines equals order total
Timeliness / freshness Is it recent enough to use? max(loaded_at) within the last 2 hours; daily batch lands before 06:00

Accuracy is the hardest: a value can be complete, valid and consistent and still wrong. It usually needs a trusted reference to compare with (the source system, a ledger, a sample checked by a person). Volume and distribution checks (sudden drops in row count, a column’s null rate jumping from 1% to 40%) catch the problems no one thought to write a rule for.

Where to check

  • At ingestion: schema, required fields, types and duplicates on raw data, as close to the source as possible. Cheapest place to catch a broken producer.
  • After transformation: uniqueness of keys, referential integrity, business rules (revenue is never negative, totals reconcile). Joins and aggregations are where duplicates and fan-out bugs appear.
  • Before serving: freshness, volume and reconciliation on the tables people actually read. Use write-audit-publish: build into a staging table or branch, run the checks, then swap it in atomically so consumers never see a bad version.

Decide what each failure does. Blocking checks stop the pipeline (duplicate primary keys, broken schema). Warning checks alert but let data through (a slightly late batch). Quarantining routes bad rows aside so good rows still load. Every check needs an owner who receives the alert.

Tools

Tool Style Notes
dbt data tests SQL tests in the transformation project unique, not_null, accepted_values, relationships built in; packages like dbt-expectations add more
Great Expectations Python “expectations” Rich rule library, data docs, works with pandas, Spark and SQL engines
Soda YAML checks (SodaCL) Readable checks run from the CLI, an orchestrator or CI
Monte Carlo, Elementary, Bigeye Data observability Learn baselines for freshness, volume and schema, and alert on anomalies without hand-written rules
Pipeline-native Delta Live Tables / Lakeflow expectations, Spark checks with Deequ Checks declared next to the transformation

A dbt example for the figure above:

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]
      - name: customer_id
        data_tests:
          - not_null
          - relationships: {to: ref('dim_customers'), field: customer_id}
      - name: status
        data_tests:
          - accepted_values: {values: ['placed', 'paid', 'shipped', 'cancelled']}

Data contracts

Most bad data is created upstream, by a team that didn’t know anyone depended on a column. A data contract is an agreement, written as code, between a producer and its consumers: the schema, the meaning of each field, quality rules, a freshness SLO and how breaking changes are announced. The producer’s CI checks changes against the contract, and the pipeline enforces the rules at runtime. Contracts move quality checks to the place where problems start. The Open Data Contract Standard is one open format for writing them.

Common mistakes

  • Testing only what is easy. not_null on every column, nothing on business rules or reconciliation with the source.
  • Alerts no one owns. A failing test that pings a shared channel is ignored within a week. Route each alert to an owner.
  • Checks that only warn. If bad data still reaches the dashboard, the test only documents the problem. Block or quarantine.
  • Too many noisy checks. Hundreds of flaky warnings train people to ignore all of them. Fewer, sharper checks win.
  • Dropping bad rows silently. Filtering them out hides the problem and skews totals. Quarantine and count them.
  • Forgetting freshness. A perfectly valid table that stopped updating three days ago is the most common incident of all.

Learning Resources

Books

  • Data Quality Fundamentals (O’Reilly): Barr Moses, Lior Gavish and Molly Vorwerck
  • Data Contracts: Chad Sanderson, Mark Freeman and B. E. Schmidt

Docs

See also: Data Governance, DataOps, Pipeline Orchestration