# Data Quality Checks That Catch Real Incidents

> Freshness, volume, schema, uniqueness, nulls and referential integrity — what to check, where to check it, and what to do when a check fails.

- Source: https://akshaythakare.com/notes/data-warehousing/data-quality-checks
- Topic: Data Warehousing
- Level: beginner
- Reading time: 3 min
- Tags: data-quality, testing, warehousing, observability
- Updated: 2026-10-11

## Six checks that cover most failures

| Check | Question it answers | Example rule |
| --- | --- | --- |
| Freshness | Did new data arrive on time? | max(loaded_at) within the last 2 hours |
| Volume | Is the amount of data plausible? | today's row count within ±30% of the 7-day average |
| Schema | Did columns or types change? | expected columns present with expected types |
| Uniqueness | Is the key really a key? | no duplicate `order_id` |
| Completeness | Are required values present? | `customer_id` never null |
| Referential integrity | Do references resolve? | every `customer_id` exists in `dim_customer` |

## Written as SQL

```sql
-- uniqueness: should return zero rows
SELECT order_id, COUNT(*) FROM fact_orders GROUP BY order_id HAVING COUNT(*) > 1;

-- referential integrity: orphan facts
SELECT f.customer_id
FROM fact_orders f
LEFT JOIN dim_customer d ON d.customer_id = f.customer_id
WHERE d.customer_id IS NULL;

-- freshness
SELECT MAX(loaded_at) < CURRENT_TIMESTAMP - INTERVAL '2 hours' AS is_stale FROM fact_orders;
```

Frameworks such as dbt tests, Great Expectations or Soda express the same rules declaratively; the rules matter more than the tool.

## Where to check

```mermaid
flowchart TB
  accTitle: Where data quality checks run
  accDescr: Checks run on arrival in the raw zone, before publishing to the warehouse, and continuously on published tables.
  R[Raw arrival: schema, volume] --> T[Before publish: keys, nulls, references] --> P[Published tables: freshness monitoring]
```

- **On arrival**: schema and volume, so bad files never enter the pipeline.
- **Before publishing**: keys, nulls and references, so consumers never see broken data.
- **After publishing**: freshness and drift monitoring, so you learn about silent failures before users do.

## What to do when a check fails

> **Tip: Decide severity up front**
>
> Mark each check as **blocking** (stop the publish and alert) or **warning** (publish, alert and track). A duplicate primary key should block; a small dip in volume on a holiday might only warn.

- Quarantine failing records with the reason, instead of silently dropping them.
- Keep the last good version published so dashboards don't break.
- Record check results per run, so you can see trends rather than single failures.

## A sensible starting set

For every important table: one freshness check, one volume check, a uniqueness test on its key and not-null tests on required columns. Add referential checks for fact-to-dimension joins. That small set catches most incidents.