Data quality audit: a 10-step checklist

An executive opens two reports and sees different revenue for the same month. Analysts spend half a day reconciling the numbers by hand, find the cause, fix it, and a week later it all happens again. When this sounds familiar, the problem is not the reports but the data they are built on.

A data quality audit helps you stop fighting fires one at a time. It shows exactly where data loses quality, why it happens and what it costs the business. Below is a ten-step checklist you can use to run an audit yourself or to check a contractor's work.

What an audit checks

Data quality is not abstract "cleanliness" but fitness for a specific purpose. It is measured across several dimensions:

• Completeness: are all the required records and values present?
• Accuracy: do the values match reality?
• Consistency: does the same data match across systems?
• Timeliness: is the data refreshed by the time it is used?
• Uniqueness: are there duplicate customers, products or counterparties?

The outcome of an audit is a map of issues: a list of specific defects with their causes, scale and business impact, plus a plan for what to fix first.

Step 1. Pick 3–5 key reports

Do not try to check all of the company's data at once. Start with the reports that drive important decisions: revenue and margin, inventory and turnover, the sales funnel, receivables. A good sign you have chosen well is that these reports already cause arguments about the numbers.

Step 2. Trace the data path

For each report, find out where the numbers come from: which systems, exports, tables and formulas. This is data lineage. Draw a simple diagram: source → transformations → report. This step alone often uncovers a manual Excel export that one employee runs once a week, or two different tables used to calculate the same metric.

Step 3. Agree on definitions

Before looking for errors, make sure everyone understands the metrics the same way. What is an "active customer": someone who bought in the last 30 days or the last 90? Is revenue counted with or without VAT, by order date or by payment date? Write the definitions down in a business glossary. Many "data discrepancies" turn out to be discrepancies in definitions.

Step 4. List the checks

For each key dataset, define checks across the quality dimensions. For example:

Dimension	Example check
Completeness	Every order has a customer, an amount and a date
Accuracy	The sale price is not below zero and not above a reasonable limit
Consistency	Daily sales in the accounting system match the CRM
Timeliness	Yesterday's sales are loaded into the warehouse by 7:00
Uniqueness	No two customers share a tax ID or phone number

Start with 10–20 checks on the most important fields. Hundreds of rules at the start only scatter attention.

Step 5. Run the checks on real data

Most checks are simple SQL queries. For example, the share of orders without a customer and duplicate phone numbers:

-- Share of orders without a customer
SELECT
  AVG(CASE WHEN customer_id IS NULL THEN 1.0 ELSE 0 END) AS no_customer_share
FROM orders;

-- Phone numbers shared by several customers
SELECT phone, COUNT(*) AS customers
FROM customers
GROUP BY phone
HAVING COUNT(*) > 1;

Record not only the fact of an error but its scale: how many records are affected, in which periods, departments or branches.

Step 6. Reconcile numbers across systems

Take one metric, such as last month's sales, and calculate it in every system where it exists. If the numbers differ, break the difference down by day, branch and transaction type. The cause usually narrows down quickly to a specific document type, period or loading rule.

Step 7. Find causes, not just symptoms

Duplicate customers are a symptom. The cause may be that the order form has no search for existing customers, or that two systems create customers independently of each other. For each major issue, ask "why" several times in a row until you reach the process or system that creates it. That is where the fix belongs; otherwise the errors will be back within a month.

Step 8. Assess the business impact

Management needs an answer to "so what?". Translate the issues into money and time:

• how many hours a month analysts spend on manual reconciliation;
• which decisions were made on wrong numbers;
• what errors in shipments, invoices or marketing campaigns caused by wrong addresses and duplicates cost.

Even a rough estimate is better than none: it helps set priorities and justify the budget for fixes.

Step 9. Set priorities

Place the issues on two axes: business impact and difficulty of fixing. Start with what has high impact and is relatively easy to fix. Often this means making fields mandatory, agreeing on a single calculation rule for a metric or fixing one data load. Plan complex issues, such as a single customer reference (MDM), as a separate project.

Step 10. Make the results stick

An audit without follow-up quickly goes stale. To keep the results:

1. Turn the checks from steps 4–5 into automated ones and run them on a schedule.
2. Set up alerts: a failed check should reach the person responsible, not an executive reading a report.
3. Assign data owners for key datasets — the people who decide on rules and disputed cases.
4. Describe the error resolution process: who fixes what, by when and in which system.

These are the first steps toward data governance — a system that keeps quality under control all the time, not just from one audit to the next.

Common mistakes

• Checking everything at once. The audit drags on for months with no result for the business.
• Fixing data by hand. A one-off cleanup without removing the cause works for a few weeks.
• Leaving the audit to IT alone. Without the business, you cannot agree on definitions or assess impact.
• Not measuring the baseline. If you do not record how many errors there were before the changes, you cannot show the result later.

Summary

A good data quality audit answers three questions: where the issues are, why they occur and what to fix first. Start with a few key reports, agree on definitions, check the data across quality dimensions and make the results stick with automated checks and clear roles.

If you would like an outside view, we run data quality audits along these lines. An audit usually takes about two weeks and ends with a map of issues and a remediation plan.