Stop 08 · Data Cleaning & WranglingTopics · 7/9

Data Validation Rules

In short

Cleaning fixes yesterday's mess. Validation rules stop tomorrow's mess from getting in. Write the rules once and every new batch of data gets checked automatically.

Defining explicit rules a dataset should satisfy (dates in a valid range, no negative prices, foreign keys that actually exist) and checking new data against them automatically.

The jaadu, as a gate guard, checks a visitor's card with a torch.

Think of it like this

The hostel gate guard

The guard at the hostel gate checks every visitor: ID card, entry time, name in the register. No ID, no entry. He doesn't wait for trouble inside. Validation rules are that guard, checking each row before it reaches your dashboards.

Watch it happen

A new batch arrives

New batch

Validator

Clean table

Quarantine

5,000 rows

1/5Today's orders file lands. Nothing touches the reporting tables yet.

The big ideas

What you need to know

Types of rules

Not null, unique, range, allowed values, format like a 6-digit pincode, and links to other tables.

Quarantine, don't delete

Keep failed rows with a reason. You'll need them to fix the source and to explain the numbers.

Thresholds and alerts

A few bad rows may be normal. A sudden 20% failure rate means something upstream broke.

Tools exist

Simple asserts in Pandas work. Tools like dbt tests or Great Expectations run checks in pipelines.

See it in code

Walk through the code

sql

1SELECT 'missing_id'   AS rule, COUNT(*) AS failures2FROM staging.orders WHERE order_id IS NULL3UNION ALL4SELECT 'negative_amount', COUNT(*)5FROM staging.orders WHERE amount < 06UNION ALL7SELECT 'bad_pincode', COUNT(*)8FROM staging.orders WHERE pincode !~ '^[1-9][0-9]{5}$'9UNION ALL10SELECT 'unknown_customer', COUNT(*)11FROM staging.orders o12LEFT JOIN customers c ON c.customer_id = o.customer_id13WHERE c.customer_id IS NULL;

1/4Required field: every order must have an ID.

A validation report in plain SQL (PostgreSQL regex syntax). Each row is a rule and how many records break it.

Save yourself the pain

Common mistakes

Oops

Rules that only live in your head, checked when you remember.

Do this instead

Write them as code that runs on every load, with results saved or alerted.

Oops

Rules so strict that half the data fails on day one.

Do this instead

Start with rules that matter most, measure failure rates, then tighten with the data owner.

Your mission

Write five rules

~25 min

Quick check

A daily load suddenly fails the 'customer exists' rule for 30% of rows. Best first step?

Go deeper

1Write 5 validation rules for a dataset you're working with (e.g. 'age between 0 and 120').
2Practice writing a query or script that flags rows violating each rule.
3Learn to build this into a repeatable check, not a one-time manual review.