Priya Nair·
Turned raw profiling stats from our warehouse into a data quality report leadership actually read. Twelve issues, ranked by blast radius, three fixed the same week.
Converts table profiling statistics into a prioritized data quality report with business impact framing.
Data Quality Audit Report Writer
You are a data quality analyst writing for a mixed audience: the data team AND the business leaders who consume the dashboards. I will paste profiling output for our key tables: row counts, null rates per column, distinct counts, min/max values, freshness timestamps, and any failing test results.
Profiling data:
{{profiling_output}}
Business context — what these tables feed: {{downstream_usage}}
Write the report:
1. **Executive summary** (under 120 words). Overall health verdict, the single worst issue in plain business language ("7% of orders have no region, so the regional revenue split overstates 'Unknown' by ~CHF X"), and what is being done. No jargon: a business leader must understand every sentence.
2. **Issue register.** Every anomaly found, as a table: issue, affected table.column, evidence (the actual stat), blast radius (which dashboards/decisions consume it), severity (Critical = wrong decisions being made today / High = wrong soon / Medium = trust erosion / Low = hygiene), and a proposed fix with effort (S/M/L). Sort by severity, then effort ascending — cheap critical fixes first.
3. **Anomaly reasoning.** For the top 3 issues, show your reasoning: why is this stat anomalous rather than normal? Compare against what healthy would look like (e.g. "null rate jumped from a stable 0.4% to 7.1% on May 14 — a step change, which points to a pipeline or schema event, not gradual drift"). Name the most likely root-cause category: source change, pipeline bug, schema drift, or genuine business change — and how to confirm which.
4. **Monitoring gaps.** Which of these issues would our current tests have caught? For each miss, the exact test to add (dbt test or SQL assertion).
5. **The ask.** One short paragraph: what you need from engineering time or source-system owners, quantified.
Ergebnisse
## Data Quality Report — Core Commerce Tables, Week 26
### Executive Summary
Overall: **degraded but recoverable**. The most consequential issue: since May 14, 7.1% of new orders carry no `billing_region`, so the regional revenue dashboard shifts roughly CHF 84k/month into "Unknown" — the DACH split your pricing review uses is understated by an estimated 4-6%. Root cause is almost certainly the checkout release from that date (step-change pattern). A fix is proposed below at small effort; two further issues need source-owner input. Nothing indicates data loss.
### Issue Register (top rows)
| Issue | Where | Evidence | Blast radius | Severity | Fix (effort) |
|---|---|---|---|---|---|
| Region nulls step-change | orders.billing_region | 0.4% → 7.1% nulls since May 14 | Regional revenue dash, pricing review | **Critical** | Backfill from country_code + checkout fix (S) |
| Duplicate customer rows | customers.email | 1,240 emails with 2+ customer_ids | LTV, churn cohorts | **Critical** | Merge job + unique constraint (M) |
| Freshness slip | events_daily | Loaded 11:40 vs 06:00 SLA, 4 of 7 days | Morning exec dashboard | High | Reschedule upstream dependency (S) |
| Negative quantities | order_items.qty | min = -3, 87 rows | Revenue (minor) | Medium | Filter + source ticket (S) |
### Anomaly Reasoning (top issue)
Healthy `billing_region` nulls ran 0.3-0.5% for 14 months (guest checkouts from unsupported geos). On May 14 the rate jumped to 7.1% in one day and has been flat since — a **step change**, which rules out gradual drift or seasonal mix. Step changes point to a deploy or schema event. The checkout service released v2.31 that morning (changelog: "simplified address form"). Confirm: null rate segmented by checkout version header; if v2.31 sessions carry ~all nulls, done.
### Monitoring Gaps
Current tests would have caught none of the top three (no null-rate anomaly tests, no email uniqueness, freshness alert threshold set to 24h). Add: `dbt_utils.equal_rowcount` variant with accepted null-rate range on billing_region, unique test on customers.email post-merge, freshness SLA at 07:00.
### The Ask
One engineer-day from the checkout team to fix the form regression, and a decision from Finance on whether backfilled regions (from country_code, ~97% accurate) may flow into the official numbers.
Modell: Gemini 2.5 Pro
28 Likes17 SavesScore: 19
1 Kommentar
Maya Patel·
Step-change vs drift reasoning is exactly how anomaly triage should be taught. That distinction alone locates the root cause half the time.