Data quality checks that catch customer-visible problems before the customer does

Most "the dashboard is wrong" tickets come from one of five failure types. Check for each one automatically.

#1. Freshness: did new data arrive?

# dbt sources.yml
sources:
  - name: erp
    loaded_at_field: _loaded_at
    freshness:
      warn_after: {count: 6, period: hour}
      error_after: {count: 12, period: hour}
    tables:
      - name: orders

dbt source freshness

Plain SQL version:

SELECT MAX(_loaded_at) AS last_load, DATEDIFF('hour', MAX(_loaded_at), CURRENT_TIMESTAMP()) AS hours_old
FROM raw.erp_orders;

#2. Volume: is the row count normal?

WITH daily AS (
  SELECT CAST(order_date AS DATE) d, COUNT(*) n FROM sales.orders
  WHERE order_date >= CURRENT_DATE - 29 GROUP BY 1
)
SELECT d, n, AVG(n) OVER () AS avg_n,
       CASE WHEN n < 0.5 * AVG(n) OVER () THEN 'LOW' WHEN n > 2 * AVG(n) OVER () THEN 'HIGH' END AS flag
FROM daily ORDER BY d DESC;

A day at 40% of the usual volume is the classic sign of a partial load.

#3. Schema: did a column change?

• Use contracts in dbt (contract: {enforced: true}) on models that customers consume.
• Alert on new or removed columns in landing tables, by comparing information_schema.columns snapshots.

#4. Keys and nulls

models:
  - name: dim_customer
    columns:
      - name: customer_id
        tests: [unique, not_null]
      - name: country_code
        tests:
          - accepted_values: {values: ['IN', 'US', 'GB', 'DE', 'AE']}
  - name: fact_orders
    columns:
      - name: customer_id
        tests:
          - relationships: {to: ref('dim_customer'), field: customer_id}

#5. Business rules

The rules the customer actually cares about:

-- Revenue should reconcile with the source system within 0.5%
SELECT ABS(w.revenue - s.revenue) / NULLIF(s.revenue, 0) AS diff_pct
FROM (SELECT SUM(amount) revenue FROM mart.fact_orders WHERE order_date = CURRENT_DATE - 1) w,
     (SELECT SUM(amount) revenue FROM raw.erp_orders  WHERE order_date = CURRENT_DATE - 1) s;

#Block or warn?

#Make it operational

• Every check has an owner and a runbook link.
• Results go to the same alerting channel as infrastructure alerts.
• Publish the latest check status next to the dashboard, so users can see the data was validated today.

Tip

Start with freshness and volume on your 10 most-used tables. That catches most incidents for very little effort. Add finer rules once those are reliable.