data-quality-auditor

Data Quality Auditor

End-to-end data quality (DQ) practice: define DQ dimensions, write rule-based checks, detect schema drift, monitor freshness SLAs, respond to DQ incidents, build a maturity-graded program. Tool-agnostic — works whether you use Great Expectations, dbt tests, Soda Core, Monte Carlo, custom SQL, or hand-rolled scripts.

This skill is audit-focused, not pipeline-focused. For pipeline design, ETL, Spark/dbt, see engineering/senior-data-engineer.

When to use this skill

| Situation | Skill applies | |-----------|---------------| | Setting up DQ from scratch on a new pipeline | Yes — start with DQ dimensions + check catalog | | Auditing existing pipelines for missing DQ | Yes — scripts/dq_check_runner.py against datasets | | Detecting schema drift in upstream sources | Yes — scripts/schema_drift_detector.py | | Monitoring freshness / SLA on data assets | Yes — scripts/freshness_monitor.py | | Responding to a DQ incident (bad data in prod) | Yes — use incident response playbook | | Designing a DQ governance model | Yes — see DQ maturity model | | Compliance evidence (data quality for SOC 2 PI1, GDPR, ISO 27001) | Yes — DQ checks produce auditable artifacts | | Building data pipelines for the first time | Use engineering/senior-data-engineer first |

The six DQ dimensions (and why they're not optional)

Industry-standard taxonomy. Every dataset should have at least one check per dimension when at production stage.

| Dimension | Question | Example check | |-----------|----------|---------------| | Completeness | Are required fields populated? | users.email IS NOT NULL — fail if > 0.1% nulls | | Accuracy | Do values match reality? | Reconciliation against source-of-truth system; sample-based human review | | Consistency | Do values agree across systems / time? | users.email in DB matches Salesforce; row count today within 5% of yesterday | | Timeliness / Freshness | Is data current to expectation? | events_table.max(event_time) is < 1h old; pipeline runs SLA | | Validity | Do values conform to format / schema / business rules? | Email regex matches; country code in ISO 3166-1; status in known enum | | Uniqueness | Are entities not duplicated? | users.user_id is unique; no two rows with same (user_id, day) |

Some teams add: Integrity (referential — FKs resolve), Conformity (matches a published standard), Reasonableness (passes basic sanity checks beyond strict validity).

See [references/data-quality-dimensions.md](references/data-quality-dimensions.md) for per-dimension depth: how to measure, what threshold to set, what to alert on, common pitfalls per dimension.

The DQ check catalog

Five categories of checks, applied per dataset:

| Category | Examples | When | |----------|----------|------| | Volume | Row count is between min/max; row count is within ±N% of yesterday | Always for batch tables | | Freshness | MAX(updated_at) ≤ N minutes ago; pipeline ran in last N minutes | All tables with refresh SLA | | Schema | Column exists; column type matches; column ordinal; column nullable matches | All tables; especially upstream-sourced | | Values | NOT NULL; UNIQUE; in enum; matches regex; min/max; reference exists | Per-column based on semantic role | | Distribution | Mean / median / p99 within expected band; histogram doesn't shift; cardinality stable | Tables where data shape matters (ML features, analytics dimensions) |

See [references/dq-check-catalog.md](references/dq-check-catalog.md) for the full catalog: ~50 specific check patterns with detection heuristics, tool snippets (Great Expectations / dbt / Soda / SQL), and tuning notes.

DQ maturity model

Five levels. Most teams should target Level 3-4.

| Level | What | Effort | |-------|------|--------| | L0 — Reactive | "We find DQ issues when users complain." No automated checks. | None (until incidents pile up) | | L1 — Ad-hoc | Some checks exist on critical tables; engineers write them as needed; no centralized framework. | Low | | L2 — Scheduled | Checks run on every pipeline run; failures alert via Slack / pager. Catalog of checks lives in version control. | Medium | | L3 — Comprehensive | Per-dataset SLAs; checks cover all 6 DQ dimensions; freshness + volume + schema monitoring is automatic for every table; data team owns DQ. | High | | L4 — Data-as-product | DQ is part of every dataset's contract. Producers responsible for quality of what they emit. Consumers can subscribe to DQ events for upstream data. | Very high; org-wide investment |

L0 / L1 teams: read this skill, pick the most-painful 3 datasets, add L2-level checks first. L3 teams: invest in observability tooling; consider data observability vendor or build internal. L4 teams: think about data contracts and data mesh.

DQ incident response playbook

When DQ alerts fire, treat it like a production incident.

Severity classification

| Severity | What | Response time | |----------|------|---------------| | Sev1 — Customer-facing | Bad data is visible to customers OR feeding ML production OR driving billing | < 15 min ack, < 4h fix | | Sev2 — Internal critical | Bad data is feeding executive dashboards, finance close, regulatory reporting | < 1h ack, < 24h fix | | Sev3 — Internal degraded | Bad data is in analytical tables; doesn't immediately affect decisions | < 1 day ack, fix in next release | | Sev4 — Cosmetic / non-critical | Edge case; doesn't affect known consumers | Backlog |

Standard playbook

1. Acknowledge the alert (within ack-SLA).
2. Quarantine affected data — block downstream pipelines, alert consumers via channel/email.
3. Triage — is this a real DQ issue or false positive? Root cause: upstream change? schema drift? bug in transformation? source data corruption?
4. Contain — stop the bleeding. Pause the pipeline; route around the bad data; serve cached known-good data; revert to last known good state.
5. Fix forward — apply the fix in code + reprocess affected data.
6. Notify — affected downstream consumers; update status page if customer-impacting.
7. Post-incident — write up timeline, root cause, action items.

Recovery patterns

• Backfill — re-run pipelines for the affected partition / time range
• Quarantine pattern — keep bad data in a _quarantine schema; clear when reprocessed
• Dead-letter queue (DLQ) — for streaming data, send unprocessable records to DLQ for manual review
• Idempotent reprocessing — every pipeline should be re-runnable for any date range without side effects

See [references/dq-incident-response.md](references/dq-incident-response.md) for full incident playbook including templates for incident channels, post-incident writeup, and consumer notification.

End-to-end workflows

Workflow: Add DQ to a new dataset

1. Profile the data — scripts/dq_check_runner.py --profile --table mydb.mytable runs statistics: row count, null rates per column, distinct count, min/max for numerics, histogram for categoricals.
2. Pick check thresholds based on the profile and product knowledge.
3. Write the checks (in your tool of choice — Great Expectations / dbt tests / Soda / custom SQL).
4. Wire into pipeline — checks run on every load. Failures fail the pipeline (with alerting), don't silently produce bad data downstream.
5. Document the DQ contract in the data catalog: what does this table guarantee?

Workflow: Detect and respond to schema drift

1. Snapshot baseline schema — scripts/schema_drift_detector.py --baseline mydb.mytable > baseline.json.
2. Schedule the detector to run before every consumer pipeline (or hourly).
3. On drift detection — alert + block pipeline; investigate whether the change was intentional (upstream renamed a column) or accidental (data corruption).
4. If intentional: update consumer pi