Data quality ownership

Why it matters

In SQL for Analysis you wrote one-off queries to find duplicates and nulls. At mid-level, the job changes: you own tables and dashboards that other people rely on, and you are responsible when they are wrong. Bad data that reaches a dashboard costs trust that takes months to rebuild. "Data quality ownership" means problems are caught automatically before stakeholders see them, and when something does slip through, everyone knows who fixes it and how.

Dimensions of data quality

Most checks fall into a few families:

The first four are rules about individual rows. Freshness, volume and consistency catch pipeline failures, such as a load that silently delivered half the data.

Write checks as queries that return failures

A useful convention (used by dbt tests and most data quality tools): each check is a query that counts or returns failing rows; zero means pass. Combining them gives a single report a pipeline can act on:

WITH checks AS (
  SELECT 'null_customer_id' AS check_name,
         (SELECT COUNT(*) FROM orders WHERE customer_id IS NULL) AS failing_rows
  UNION ALL
  SELECT 'orphan_customer_id',
         (SELECT COUNT(*) FROM orders o
          WHERE o.customer_id IS NOT NULL
            AND NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = o.customer_id))
)
SELECT check_name, failing_rows,
       CASE WHEN failing_rows = 0 THEN 'pass' ELSE 'fail' END AS status
FROM checks;

Note the use of NOT EXISTS rather than NOT IN, which misbehaves when the subquery contains NULLs.

Severity and thresholds

Not every failure should stop the pipeline:

• Error (block): duplicate primary keys, a load with zero rows, revenue off by more than 5% from finance. Stop publishing and alert the owner; it is better to show yesterday's data with a "delayed" banner than wrong data.
• Warn: a handful of orphan rows, a volume dip of 15%. Publish, but alert and investigate.

Some checks need a tolerance: "fewer than 0.1% of rows with a null country" is a realistic rule when a few nulls are expected. dbt supports this with severity, warn_if and error_if settings.

Where checks run

1. At ingestion: schema checks (expected columns and types).
2. After each transformation: uniqueness, nulls, relationships on the models you build.
3. On the final tables BI reads: freshness, volume, reconciliation against a trusted source.

Tools include dbt tests, Great Expectations, Soda and SQL assertions in an orchestrator such as Airflow or Dagster. Data observability platforms (Monte Carlo, Bigeye, and features built into some warehouses) learn normal patterns and alert on anomalies in freshness, volume and distributions without a hand-written rule for each.

Ownership and incidents

Technology is half of it. The other half is people:

• A named owner for each important table and dashboard, listed in the documentation.
• A data contract with upstream teams: which columns they promise, what they will announce before changing.
• An incident routine: detect, tell affected users quickly ("Revenue dashboard is delayed; we are investigating"), fix, backfill, and write a short blameless review with a new check so the same failure is caught next time.
• Visible status: a freshness timestamp and known-issues note on dashboards.

Common mistakes

• Checks that nobody receives alerts from.
• Alerting on everything, so people ignore the noise. Tune thresholds.
• Only checking row-level rules and missing a half-empty load.
• Fixing data by hand in the dashboard instead of upstream.
• Silent fixes. Tell users when published numbers changed.

Quick recap

• Quality dimensions: uniqueness, completeness, validity, referential integrity, freshness, volume, consistency.
• Write each check as a query that returns failures; zero means pass.
• Decide severity: block on errors, warn on smaller issues, with tolerances.
• Run checks at every stage, name owners, and handle incidents openly.

Try it

In the Do tab, write one SQL query that runs five quality checks on a load and reports pass or fail for each.