Data Quality Audit That Produces a Fix List

Your executives are asking which numbers to trust and your team is guessing where to start. Here’s how to run a data quality audit that ends with a prioritized fix list—not a binder. Scope to a small set of critical data products, interview stakeholders to define expectations, reconcile business metrics, map lineage, check freshness and volume history, assess test effectiveness, analyze incident patterns, and confirm ownership and alert routing. Score risk, then produce an audit report with tickets sequenced into a 30/60/90‑day plan. You can run this in‑house; this is the process we use at Vertex Data Consulting when teams call us after the third “why is revenue off?” week.

Scope the audit to critical data products and reconcile metrics

Don’t boil the ocean. Pick 8–12 data products tied to decisions and SLAs: bookings, revenue, active subscribers, marketing attribution, inventory, compliance extracts. In stakeholder interviews (finance, marketing, operations), capture their canonical definitions, acceptable variance, refresh times, and on‑call paths. That is your lightweight data governance backbone for the audit process.

Run metric reconciliation before anything else. Compare source-of-truth measures to modeled outputs for the same windows. This is fast signal on data accuracy and where quality issues concentrate.

-- Reconcile orders and GMV between source and fct model
with src as (
  select order_date, count(*) as src_orders, sum(amount) as src_gmv
  from raw.orders
  group by 1
), m as (
  select order_date, count(*) as m_orders, sum(gmv) as m_gmv
  from analytics.fct_orders
  group by 1
)
select coalesce(m.order_date, src.order_date) as dt,
       src_orders, m_orders,
       m_orders - src_orders as diff_orders,
       (m_gmv - src_gmv) / nullif(src_gmv, 0) as rel_diff_gmv
from src full join m using(order_date)
order by dt desc;

Capture gaps as discrete data issues with owners and clear acceptance criteria. If your business health dashboard needs work, see our notes on setting one up: build a dashboard leaders actually open.

Lineage, freshness, and volume history—plus incident patterns

Map lineage so you know which upstream changes break downstream metrics. If you use dbt, export manifest.json to enumerate dependencies; otherwise, query your database for view references. For freshness, compute max(updated_at) or partition lag versus expected SLAs. Track row‑count deltas to catch silent drops and spikes; that’s basic data profiling that surfaces poor data quality fast.

Pull incident history (pages, Slack alerts, failed DAGs) for the last 90 days. Cluster by root cause: upstream API shifts, late loads, flaky incremental logic, warehouse quota throttling. This guides what to fix versus what to monitor.

# Simple volume & freshness probe (Python + SQL)
import datetime as dt
from sqlalchemy import create_engine

engine = create_engine("<warehouse-connection-string>")
q = """
select current_date as as_of,
       count(*) as rows_today,
       max(updated_at) as max_ts
from analytics.fct_orders
where order_date = current_date;
"""
df = engine.execute(q).fetchone()
lag = dt.datetime.utcnow() - df.max_ts
print({"rows": df.rows_today, "freshness_minutes": int(lag.total_seconds()/60)})

If orchestration is the weak link, route on‑call and SLAs through production DAGs with retries and observability; we build these patterns every week—see Airflow DAGs with SLAs and on‑call routing and our guide to monitoring that warns you before users do.

Evaluate test effectiveness and alert routing (dbt, anomaly, ownership)

Quantity of tests is not the same as quality assurance. Audit test effectiveness: Which failures would have prevented past incidents? Where do you lack coverage on constraints, invariants, and reconciliations? Measure simple data quality metrics: test coverage ratio by model and mean detection lag.

# dbt model tests with owners and SLAs (schema.yml)
version: 2
models:
  - name: fct_orders
    description: Finalized orders; one row per order_id
    config:
      docs: {show: true}
      meta:
        owner: finance_data@company.com
        freshness_sla_minutes: 90
    columns:
      - name: order_id
        tests: [not_null, unique]
      - name: order_date
        tests:
          - not_null
          - accepted_range:
              min_value: '2020-01-01'
              max_value: '2026-01-01T00:00:00.000+00:00'
      - name: gmv
        tests:
          - not_null
          - relationships:
              to: ref('dim_currency')
              field: currency_code

Pair rules‑based tests with anomaly checks on freshness and volume to reduce detection lag; if you need a lightweight pattern, see anomaly detection without alert fatigue. Route alerts by owner (Slack, on‑call rotation), and suppress noisy ones. If you’re using AI in Slack, a triage agent can auto‑enrich failures with lineage and recent deploys; our take on platform wiring is here: AI workflows that actually stick.

Risk-based scoring and the deliverables that create a 30/60/90 plan

Turn findings into a single risk score per data product. Keep it simple and auditable; a clear model beats a fancy one. Weight business impact and detection coverage more than everything else. This is a data quality assessment you can defend to finance and engineering.

Dimension	How to measure	Weight
Business impact	$ at risk / key KPI affected	0.35
Test coverage gap	% critical tables lacking key tests	0.20
Freshness volatility	p95 delay vs SLA	0.15
Incident frequency	pages in last 90 days	0.15
Ownership clarity	has on‑call + doc? (0/1)	0.10
Regulatory exposure	feeds compliance reports? (0/1)	0.05

Deliverables that make the audit helps remediation instead of just diagnosis:

• Audit report: scope, lineage maps, quality metrics, gaps, and a risk register.
• Prioritized backlog: each item has owner, acceptance criteria, and rollback plan.
• 30/60/90 plan: 30 days—patches and alert routing; 60—model/dbt refactors, dbt repo performance fixes; 90—data contracts and training (hands‑on enablement).
• Monitoring: freshness/volume dashboards; on‑call runbook.

If you need deeper repo surgery, we summarized what a useful check includes: dbt project audit coverage.

FAQ: quality standards, audit types, roles, and scope

What are the 5 points of data quality?

Accuracy, completeness, consistency, timeliness, and uniqueness. Those are the quality standards most teams track as data quality metrics.

What are the three types of quality audits?

Process audit (how work happens), product audit (does the data set meet expectations), and system audit (holistic data management across the platform). This data audit focuses on product and system.

What are the five C’s of data quality?

Clean, complete, current, consistent, and contextual. Use them when you profile data sources during discovery.

Data quality vs data integrity?

Quality is fitness for use; integrity is correctness within and across tables (keys, constraints). You need both for reliable data and data reliability in production.

Is monitoring enough to improve data quality?

No. Monitoring finds problems; ownership, alert routing, and remediation backlogs improve data quality. Monitoring still improves data by shrinking detection-to-fix time.

Do I need new staff for quality auditing?

Usually not. You need clear owners, an on‑call rotation, and time-boxed focus. If you want a jumpstart or Slack‑native triage, we can help.

Want deeper testing patterns? See data quality testing strategy. For orchestration specifics, start with Airflow best practices from real incidents.

---

Your next step: pick 8–12 critical products, book two stakeholder sessions, pull lineage and freshness snapshots, run the reconciliation SQL above, and score risk. If you want a co‑pilot to ensure data and alerting land cleanly, start a project with Vertex. We’ll help you ensure data is trustworthy and ship a fix list you can execute.