Data Quality Checks and Quality Gates

"Validated" is not a wish, it is a set of checks

The Silver layer earned the words "cleaned, validated, deduplicated" in the last lesson, and this lesson is where that word gets teeth. A pipeline can finish with a green status and still write garbage: a broken upstream export sends half the rows, a join fans out and duplicates every order, a bug lets NULLs into a key column. "The job succeeded" and "the data is correct" are two different questions, and a data engineer is expected to check the second one. "How do you know your data is correct, and how would you test a pipeline?" is one of the most common junior DE interview questions.

The check families

Data-quality checks fall into a handful of families, and almost every real test is one of these:

Table

Every data-quality test is one of these families, and each is a plain SQL query underneath a tool like dbt or Great Expectations.

check family	catches	SQL shape
uniqueness / duplicates	a key that repeats (broken dedup, fan-out join)	GROUP BY key HAVING COUNT(*) > 1
completeness / null rate	missing values in a required column	SUM(CASE WHEN col IS NULL ...) / COUNT(*)
validity / range	values outside the allowed domain	WHERE col < 0 OR col NOT IN (...)
volume anomaly	a load far bigger or smaller than usual	compare COUNT(*) to a recent baseline
referential integrity	a foreign key with no parent row	LEFT JOIN dim WHERE dim.id IS NULL

• Uniqueness / duplicates. A key that should be unique (one row per order_id) but is not signals a broken dedup or a fan-out join. This is also how you catch a non-idempotent re-run: run a job twice without an idempotent write and every key doubles. You find it with GROUP BY key HAVING COUNT(*) > 1.
• Completeness / null rate. A column that should always be present but is NULL or blank. You measure the null rate and alert when it crosses a threshold.
• Validity / range. A value outside its allowed domain: a negative price, a status not in the allowed set, a date in the future.
• Volume / row-count anomaly. Today's load is a tenth of yesterday's, or triple. The row count itself is a signal (the rows_out your run log records).
• Referential integrity. A user_id in the fact table that does not exist in the users dimension (an orphan).

Quality gates (naming what you already built)

You have already written both halves of this. In Level 4 you wrote the four dbt tests (unique, not_null, accepted_values, relationships) as zero-rows-means-pass queries, and in Level 5 you built a write-audit-publish (WAP) gate that staged a batch, audited it with checks, and published only on a clean pass. The vocabulary consolidates here: a quality gate runs those checks and fails the pipeline (or quarantines the batch) when one breaches its threshold, so bad data never reaches the Gold tables the dashboards read. Frameworks like dbt tests or Great Expectations are how you wire these into production, but every one of them is a SQL query underneath, exactly the ones you wrote by hand.

Writing the checks with SQL

The checks are exactly the SQL you already know: a GROUP BY ... HAVING for duplicates, a conditional SUM for a null rate, a CASE for a pass or fail gate. The exercises run real checks over a staged_events batch: find the duplicated ids, then compute a null rate and decide whether the batch passes its gate.

Common mistake: running quality checks only after the data is published. By then the bad data is already in the Gold tables the dashboards read, so the checks belong in a gate that runs before publish (write-audit-publish).

Interview nuance: "how would you test this pipeline" wants the check families by name (uniqueness, completeness, validity, volume, referential integrity) and the idea of a quality gate that fails the run. Bonus points for naming dbt tests or Great Expectations, and for saying the checks run in a write-audit-publish step before the data is published.

On a real platform this differs. Real quality checks run as dbt tests or Great Expectations suites wired into the pipeline, emitting pass or fail per check with row-level samples of the failures. The staged_events table here lets you write the same checks as plain SQL so you can see what each framework runs for you underneath.