Data Quality Audit

Data Quality Audit

When to use this skill

Use before trusting data for analysis, modeling, or reporting. Triggers:

• "Audit this table"
• "Check the data quality of…"
• "Can I trust this data?"
• "Is fct_orders fresh?"
• "Are there duplicates in…"
• Before kicking off any modeling skill (logistic-regression, survival-analysis, etc.)

Required inputs

Workflow

Run these in order. Stop and report if any check FAILS critically.

1. Row count and time range

select
    count(*) as row_count,
    min(<time_col>) as earliest,
    max(<time_col>) as latest,
    datediff('hour', max(<time_col>), current_timestamp) as hours_since_latest
from <table>;

Expected: non-zero rows, latest timestamp within freshness SLA.
Fail if: row count = 0, or hours_since_latest > freshness SLA × 1.5.

2. Primary key uniqueness

select <pk_cols>, count(*) as dupes
from <table>
group by <pk_cols>
having count(*) > 1
limit 100;

Expected: zero rows.
Fail if: any duplicates. Investigate before proceeding.

3. Null check on critical columns

select
    sum(case when <col_1> is null then 1 else 0 end) as nulls_col_1,
    sum(case when <col_2> is null then 1 else 0 end) as nulls_col_2,
    ...
from <table>;

Expected: zero nulls in PK and critical columns.
Warn if: > 1% nulls in non-critical columns.

4. Schema check

-- Snowflake
describe table <table>;
-- or
select column_name, data_type
from information_schema.columns
where table_name = '<table_upper>';

Expected: columns and types match documentation.
Fail if: columns added/removed/retyped since last documented schema.

5. Value distribution sanity

-- For categorical columns
select <col>, count(*), count(*) * 1.0 / sum(count(*)) over () as share
from <table>
group by 1
order by 2 desc
limit 20;

-- For numeric columns
select
    min(<col>),
    percentile_cont(0.25) within group (order by <col>) as p25,
    median(<col>) as p50,
    percentile_cont(0.75) within group (order by <col>) as p75,
    percentile_cont(0.99) within group (order by <col>) as p99,
    max(<col>),
    avg(<col>),
    stddev(<col>)
from <table>;

Expected: distributions match prior periods / business expectations.
Warn if: new category dominates, or numeric tail spans multiple orders of magnitude unexpectedly.

6. Referential integrity

select count(*) as orphan_rows
from <child_table> c
left join <parent_table> p on c.<fk_col> = p.<pk_col>
where p.<pk_col> is null;

Expected: zero orphans.
Warn if: > 0.1% orphans (may indicate join logic issues downstream).

7. Time-series gaps (for event tables)

with daily_counts as (
    select date_trunc('day', <time_col>) as d, count(*) as cnt
    from <table>
    where <time_col> >= dateadd('day', -30, current_date)
    group by 1
)
select d, cnt,
    lag(cnt) over (order by d) as prev_cnt,
    cnt * 1.0 / lag(cnt) over (order by d) as pct_change
from daily_counts
order by d;

Warn if: any day's count is < 50% or > 200% of prior day's count.
Fail if: any day has 0 rows when prior days have data (pipeline failure).

Output format

# Data Quality Audit: <schema.table>

## TL;DR
**Status:** <PASS | WARN | FAIL>

<one-line summary of any issues>

## Checks
| Check | Result | Status | Notes |
|---|---|---|---|
| Row count | 4,872,109 | PASS | |
| Freshness | 2.1h since latest | PASS | SLA: 24h |
| PK uniqueness | 0 dupes on (user_id, event_date) | PASS | |
| Critical nulls | 0 nulls in user_id, event_at | PASS | |
| Schema | 14 cols, matches docs | PASS | |
| Value distributions | event_name top 3 = login (62%), view (28%), purchase (8%) | PASS | normal |
| Referential integrity | 0 orphan user_id vs dim_users | PASS | |
| Time-series gaps | No missing days; max daily delta +18% | PASS | |

## Issues found
<none, or:>
- **FAIL: 142 duplicate PK rows.** Pipeline likely double-loaded 2026-04-12. Investigate `dbt_upload_log` for that date.
- **WARN: 5.2% nulls in `device_type`.** Recent mobile SDK update may have changed event schema.

## Recommended actions
- <e.g., "Block analysis until dedup is fixed">
- <e.g., "Coerce null device_type to 'unknown' downstream">

## Caveats
- <e.g., "Last 24h of data has pipeline-lag and shouldn't be trusted for freshness check">

Validation checks (meta — does the audit itself work)

• All seven checks ran without query errors
• Each check has an explicit pass/warn/fail status
• Status reasoning is reproducible (numbers + thresholds, not vibes)

Edge cases & failure modes

• Slowly-changing-dimension tables: PK may not be uniqueness check candidate. Audit on (pk, valid_from) instead.
• Append-only event tables: late-arriving events are normal; treat as "data delay" not "data missing" if within reasonable bounds.
• Sampled tables: row count will be low by design. Document the sample rate and adjust expectations.
• Test/internal data mixed in: include an is_test = false filter in the audit if applicable.
• Sparse columns: high null % may be expected (e.g., referrer_url for organic users). Warn but don't fail.

Scripts

• scripts/audit_table.sql — Parameterized Snowflake audit script
• scripts/audit_table.py — Run audit against any pandas DataFrame

python scripts/audit_table.py --input data.csv --pk user_id,event_date --critical user_id,event_at --time-col event_at

Related skills

• modular-sql-ctes — fix sources structurally rather than band-aid downstream
• metric-definition — pin down expected values so the audit has thresholds
• Any modeling skill — always audit inputs first