02.07 · Concept
SQL Testing and Assertions
Write SQL-based assertions that validate uniqueness, null rates, accepted values, freshness, and label distribution constraints.
No video curated for this lesson yet
This lesson is written, ordered and part of the path - the video slot is the only thing still open. We are working through Everything Data lesson by lesson; 55 of 85 have their video so far.
The written notes below cover this idea in full - you lose nothing by reading instead of watching.
SQL assertions turn data assumptions into executable checks on tables, catching failures that queries often tolerate but ML systems cannot. They validate keys, missingness, categorical domains, recency, and label balance before data reaches training, evaluation, or serving, with failures shaped to explain what changed and where.
What this lesson answers
- how to test SQL feature tables for duplicates
- how to write null rate assertions in SQL
- how to detect label distribution shift with SQL
Notes
SQL assertions are checks you run directly against tables to prove that the data meets the assumptions your pipeline, features, or model depend on. For an ML engineer, these tests are especially important because many data failures do not break a query; they silently change the training set or inference inputs. A uniqueness assertion might confirm that each user_id appears once in a feature table, while a null-rate assertion might verify that missing values in an important feature stay below an acceptable threshold.
Common questions
- What are SQL assertions in an ML data pipeline?
- SQL assertions are validation queries that check whether a table still satisfies assumptions used by features, labels, or model inputs. They can fail a pipeline when data violates rules such as unique identifiers, bounded null rates, known category values, recent timestamps, or expected label mix.
- Which data quality checks matter most for ML tables?
- The most useful checks are tied to model risk: primary key uniqueness, missing values in important columns, allowed categorical values, data freshness, and label distribution. These catch silent upstream changes that may not break SQL transformations but can distort training data or production predictions.
- What should a failed SQL assertion return?
- A failed assertion should return enough detail to diagnose the issue quickly, not just a pass or fail. Useful output includes counts, invalid values, affected date ranges, duplicate keys, and sample bad rows. That makes the failure actionable for the pipeline owner.
Short definition: what is SQL Testing and Assertions?