Stop 05 · Data Analysis for Business AnalystsTopics · 10/10

Data Quality & Validation

In short

A beautiful dashboard built on messy data gives confident, wrong answers. Checking data quality first is boring, but it's what separates a BA people trust from one whose numbers get questioned.

Checking that data is accurate, complete, and consistent before drawing conclusions from it — analysis built on bad data produces confident-sounding, wrong recommendations.

The purple blob sits with a wide steel plate of rice, carefully picking out small stones.

Think of it like this

Picking stones from rice

Before cooking, you spread the rice on a plate and pick out the small stones. Skip it once and someone breaks a tooth at dinner. Checking data is that plate: a few minutes of sorting before anyone 'eats' your analysis.

Watch it happen

Cleaning a city column

Pune

pune

PUNE

Mumbai

Pnue

1/5A city column from a sign-up form. A count by city would show five different 'cities' for Pune and blanks.

The big ideas

What you need to know

Accurate

Values match reality. A customer's age shouldn't be 212, and an order date can't be in the future.

Complete

Required fields are filled. Check what share of rows have blanks in the columns you need.

Unique and consistent

No duplicate records, and the same thing is written the same way everywhere, like city names and date formats.

Prevent at the source

Write validation requirements, like dropdowns instead of free text, so bad data can't get in.

See it in code

Walk through the code

sql

1-- Blank or missing cities2SELECT COUNT(*) FROM customers3WHERE city IS NULL OR TRIM(city) = '';4 5-- Emails used by more than one customer6SELECT email, COUNT(*) AS copies7FROM customers8GROUP BY email9HAVING COUNT(*) > 1;10 11-- Orders pointing to a customer that doesn't exist12SELECT o.id FROM orders o13LEFT JOIN customers c ON c.id = o.customer_id14WHERE c.id IS NULL;

1/3Completeness: count rows where city is empty or only spaces.

Three quick checks to run before any analysis: missing values, duplicates and broken links between tables.

Save yourself the pain

Common mistakes

Oops

Quietly deleting rows that look wrong.

Do this instead

Flag them, count them and ask the data owner. Note every cleaning step so others can repeat it.

Oops

Cleaning the same export by hand every week.

Do this instead

Fix the source with validation rules, or script the cleaning so it's repeatable.

Your mission

Clean a messy sheet

~30 min

Quick check

Your report shows 1,200 customers, but the CRM says 1,050. What should you check first?

Go deeper

1Audit a sample dataset for missing values, duplicates, or inconsistent formatting.
2Learn to build a quick data quality checklist you run before starting any analysis.
3Practice flagging a data quality concern to a stakeholder before presenting findings built on it.