GuideIntermediate

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
Sign in to get started
NIEVA

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 1 Introduction: Bad data and the dimensions of quality
  • Why data quality matters: the cost of bad data
  • What is a quality dimension
  • Completeness and validity
  • Uniqueness and consistency
  • Accuracy and timeliness
  • Preventing with constraints vs detecting with checks
  • Mini-project: loading the dirty Reservo

  • 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.

SQL Mini-BootcampFree

Common questions

What people usually ask

Start whenever you like

Sign in to get started
Reviews

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!