Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 76 ยท Cleaning and Preparing Data

Auditing Data Quality

Audit a dataset before using it: shape, dtypes, completeness, uniqueness, validity, consistency and timeliness, plus a reusable profiling function in pandas.

Before you clean anything, find out what is wrong. A data quality audit is a structured, read-only pass over a table that produces a list of problems with counts and examples. It stops you from fixing the wrong thing, from fixing something that was not broken, and from presenting numbers built on rows that do not make sense.

An audit also produces something you can show other people: "3% of prices are missing, two orders ship before they were placed, one order id appears twice". That is far more useful to the team that owns the data than "the data is messy". In this lesson you will check six dimensions of quality and then wrap the checks in a function you can reuse on every new table.

The six dimensions

Quality is not one thing. Each dimension asks a different question:

flowchart TD
    Q["Data quality audit"] --> C["Completeness<br/>Is anything missing?"]
    Q --> U["Uniqueness<br/>Is anything repeated?"]
    Q --> V["Validity<br/>Are values allowed?"]
    Q --> K["Consistency<br/>Do columns agree?"]
    Q --> T["Timeliness<br/>Is it recent enough?"]
    Q --> A["Accuracy<br/>Does it match reality?"]

Accuracy is the hardest, because it needs an outside source of truth; the other five can be checked from the table itself.

We will use a small, deliberately flawed table of shop orders. Every problem in it is planted, so you can see each check catch something.

Start with shape, types and completeness

The first look answers: how big is it, what type is each column, and how much is missing? Wrong types are an early warning. A numeric column stored as text means there is a stray symbol somewhere. A date column stored as text means some values did not parse.

Python Overview: shape, dtypes, missing values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

nunique is a cheap way to spot trouble. status has four distinct values but, as we will see, only three real statuses. A column that should be an identifier but has fewer unique values than rows is suspicious too: order_id shows 8 unique values in 9 rows.

Missing values are not automatically errors. A pending order legitimately has no ship date. The audit's job is to count and locate them; deciding what they mean comes in Handling Missing Values.

Uniqueness, validity and consistency

Uniqueness has two forms. A fully duplicated row is the same record loaded twice. A duplicated key (the same order_id with different content) is worse, because you cannot tell which version is right.

Validity means each value is allowed on its own: a quantity must be positive, a country must be in the reference list, a percentage must be between 0 and 100. Write each rule as a boolean expression that is True for bad rows, so counting and listing them is trivial.

Consistency means values agree with each other: a ship date should not be earlier than the order date, a "shipped" order should have a ship date, and one status should be spelled one way.

Python Uniqueness, validity and consistency checks

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the results like an auditor:

  • Order 1006 is duplicated as a whole row, so the duplicate can be dropped safely. Both copies are identical (including the missing unit_price), so dropping one loses nothing; the missing price is a separate finding.
  • Order 1005 has quantity -2. Is that an error, or a return recorded with a negative sign? The data cannot tell you; someone who knows the business must. Flag it instead of silently deleting it.
  • Order 1005 also has country pt in lower case, which fails the check even though it is clearly Portugal. That one is a formatting problem you can repair confidently, and XX for order 1008 is a different kind of problem: an unknown value.
  • Order 1004 shipped three days before it was ordered. Either a date was typed wrongly or the columns were swapped.
  • Shipped and shipped are the same status written twice, which would split a group-by in half.

Warning: An audit finds symptoms. Resist the urge to fix them in the same step. Record the finding, decide with the data owner what the truth is, and fix in a separate, documented step so the cleaning can be reviewed and repeated.

Timeliness and accuracy

Timeliness asks whether the data is recent enough for the decision. A weekly dashboard built on data that stopped updating a month ago is wrong even if every row is valid. A quick check is the age of the newest record. In the table above the oldest order is from November 2024 while the others are in March 2025, which is worth a question: is it a late arrival, or a typo in the year?

Accuracy asks whether values match the real world. You can rarely prove it from the table alone, but you can compare against something trustworthy: totals against the finance report, a sample of rows against the source system, row counts against the previous load. Reconciling a total is often the most valuable single check in an audit.

Data quality in an organization: process, governance and improvement

An audit finds problems once. Keeping quality high needs a process and clear ownership.

flowchart LR
    A["Define<br/>what 'good' means"] --> B["Measure<br/>profile and test"]
    B --> C["Clean and integrate<br/>fix, standardise, merge sources"]
    C --> D["Monitor<br/>alerts and scorecards"]
    D --> A

Quality work is a loop: definitions drive measurements, fixes feed monitoring, and monitoring finds new rules.

What good quality means. Data is good when it is fit for its purpose. The same table can be fine for a monthly trend chart and unusable for customer mailings. Besides the six dimensions above, teams often add relevance (does the data answer the question at all?) and integrity (do links between tables, such as order to customer, hold?).

The usual steps. Profile the data, define rules and thresholds (for example "at most 1% missing emails"), clean and standardize values, integrate records that describe the same entity from several systems (consolidation and de-duplication), optionally enrich them with outside data such as postal codes or company size, then monitor with automated checks on every load. See Testing Data Code for writing those checks.

Data governance is the organizational side: who owns each dataset (a data owner decides what it means, a steward maintains it day to day), written definitions in a shared glossary, access rules, and a process for reporting and fixing problems. It also covers compliance, such as keeping personal data only as long as the law allows (see Ethics and Fairness).

Why it pays. Good data means decisions you can trust, less rework (analysts spend much of their time cleaning, as shown in The Data Science Workflow), lower operating cost (fewer returned parcels, duplicate mailings and failed payments), better customer experience and lower regulatory risk. Poor data quietly damages all of these.

Common challenges.

  • Many sources, many formats: each system encodes dates, units and IDs its own way.
  • No single owner: everyone uses the data, nobody fixes it at the source.
  • Fixing downstream: patching in the notebook leaves the root cause and repeats the work for every analyst; prefer fixing at the source or in one shared pipeline.
  • Change over time: new product codes, new form fields and system migrations introduce new defects, which is why quality has to be measured continuously.
  • Volume and speed: streaming data needs automated checks, because nobody can read every row.

A reusable profiling function

Running these checks by hand on every table is slow. Wrap them in a function that returns one row per check, so you get the same report every time and can compare it between loads. The function below takes optional rules as small functions, a key column, and a date column for freshness.

Python A reusable profile() function

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The today argument is passed in instead of calling the clock inside the function. That makes the report reproducible, and easy to test, which is the same habit you will meet in Testing Data Code and Data Validation.

Some good habits for using an audit in a real project:

  1. Run it on every load, not once. Data quality drifts: a source system changes a code, a field goes empty.
  2. Save the report next to the data. A change in the counts between runs is a signal on its own.
  3. Share the findings with whoever owns the source. Fixing a problem upstream is better than patching it in every analysis.
  4. Decide thresholds. Zero problems is unrealistic. Agree on what level is acceptable (for example under 1% missing prices) and fail loudly when it is exceeded.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which check finds an order whose ship date is earlier than its order date?

    Both dates may be valid on their own, but they disagree with each other. Checking relationships between columns is consistency.
  2. 2. Why write validity rules as expressions that are True for bad rows?

    With True meaning bad, bad.sum() counts the problems and df[bad] lists them. It also matches how you talk about problems.
  3. 3. A column that should be a unique id has 9 rows but only 8 unique values. What should you do first?

    Exact duplicates can be dropped safely; conflicting records with the same key need a decision from someone who knows which is right.
  4. 4. A negative quantity appears in an orders table. What is the best response in an audit?

    The data alone cannot say whether it is an error or a return. Flag it, and confirm the meaning with the data owner before changing anything.
  5. 5. Why pass 'today' into profile() instead of calling the clock inside it?

    A hidden clock changes the result every day. A parameter makes the same inputs give the same output.

Summary

  • Audit before you clean: a read-only pass that lists problems with counts and example ids.
  • Check six dimensions: completeness, uniqueness, validity, consistency, timeliness and (where possible) accuracy.
  • Express validity and consistency rules as boolean conditions that are True for bad rows.
  • Flag ambiguous cases for the data owner instead of silently deleting or fixing them.
  • Wrap the checks in a reusable profile() function, run it on every load and keep the reports.

Next: Handling Missing Values, the most common finding in any audit.