GuideBeginner
Data Quality And Sql Testing
Learn to measure and guarantee data quality with SQL, and to test the SQL itself. Data quality shows up in a large share of data job postings: it's about detecting incomplete, invalid, duplicate, and inconsistent data, encoding business rules as queries that return the violations, and proving your queries and migrations produce the correct result. You work on Reservo's database, populated on purpose with real dirty data, and come out knowing how to build a data quality checking system that runs and reports.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Yes
- certificate
- Free
- access
Outcomes
What you'll be able to do
- Understand the dimensions of data quality (completeness, validity, uniqueness, consistency) and the difference between preventing with a constraint and detecting with a check
- Measure completeness: analyze NULLs, calculate the percentage of completeness per column, and tell apart 0, NULL, and an empty string
- Detect validity: values out of range or domain, and invalid date formats, expressed as a SELECT that represents the rule
- Find duplicates with GROUP BY... HAVING COUNT(*) > 1, including "near-duplicates," and decide which record to keep
- Hunt down inconsistencies: orphaned rows with no referential integrity, fields that don't match each other, and totals that don't add up
- Encode business rules as SQL assertions that return the violations, where zero rows means everything is healthy
- Test the SQL itself: fixtures with an expected result, PASS/FAIL assertions, and testing a migration by comparing before and after
- Build Reservo's complete data quality system: checks per dimension, an aggregated health report, and tests for the key queries
Before you start
What you need to bring
It's for you if...
- Data analysts and devs who inherit databases with dirty data and need a systematic process to detect it
- Anyone preparing for data roles where data quality shows up explicitly in the job posting
- Teams who encode business rules, like "a refund never exceeds the charge," but don't verify them with SQL
- Devs who want to test their queries and migrations before trusting the result
Requirements and materials
- Basic SQL: SELECT, WHERE, GROUP BY
- Python 3 installed (the `sqlite3` module ships with the standard library)
- Having seen JOINs and CTEs is recommended, or taking the advanced SQL guide alongside this one
Content
The syllabus, module by module
Open any of them to see its lessons.
- Module 2 — Completeness: is everything that should be there, there?
- What completeness is, and why a `NULL` hurts
- `IS NULL`: listing incomplete rows
- `COUNT(*)` vs `COUNT(col)`: counting what's missing
- The completeness percentage per column
- The trio `0` vs `NULL` vs `''`: three different things
- A table's completeness profile
- Mini-project: Reservo's completeness report
- Module 3 Introduction: Validity — ranges, formats, and domains
- Range validity: numbers out of bounds
- Domain validity: values outside the allowed set with `NOT IN`
- Format validity: ISO 8601 dates with `date()`
- The `WHERE NOT (rule)` pattern: the single mold behind every check
- Validity with `GLOB` and patterns: validating the shape of text
- Why check validity even when there's a `CHECK`
- Mini-project: Reservo's validity checks
- Module 4 Introduction: Uniqueness and duplicates
- What a duplicate is and why it hurts
- The canonical pattern: `GROUP BY ... HAVING COUNT(*) > 1`
- The `UNIQUE` that was missing: preventing vs detecting
- Exact vs near duplicates: `LOWER(TRIM())`
- Seeing the full duplicate rows
- Criterion for deciding which one to keep
- Mini-project: find and resolve duplicates in Reservo
- Module 5 Introduction: Consistency and Referential Integrity
- What consistency and referential integrity are
- Orphan rows with `LEFT JOIN ... IS NULL`
- `PRAGMA foreign_key_check` and why SQLite let them in
- Cross-field consistency: `end_at > start_at`
- Recomputing `price_cents` with the rule
- Totals that must reconcile: `payments` vs. `price_cents`
- Mini-project: Reservo's consistency checks
- Module 6 Introduction: Business Rules as SQL Assertions
- What a quality assertion is (the "0 rows = PASS" pattern)
- Rule: a refund never exceeds the charge
- Rule: no two confirmed bookings overlap in the same room
- Rule: the price matches the member's tier
- Rule: coherence between a booking's state and its payments
- Quality assertion vs. table `CHECK`: detecting vs. preventing
- Mini-project: Reservo's business rule battery
- Module 7 — Testing Your SQL: does your query give the right answer?
- Why test a query: the plausible but false number
- The known fixture: controlled input, expected output
- The SQL assertion: let the database issue the verdict
- Testing a migration: the before, the after, and the invariants
- A Python data test runner
- Data testing frameworks: the same pattern, with a name
- Mini-project: a test suite for Reservo's queries
- Module 8: Reservo's Data Quality System
- Gathering the checks by dimension
- The pattern for a reusable, named check
- The health dashboard that aggregates with UNION ALL
- The global health score and how to interpret it
- The Python test runner that validates the checks
- Packaging the system: quality.sql + run_checks.py
- Project: Reservo's data quality system
Where it fits
This guide is part of something bigger
It's studied inside these programs, with support and dates.
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!