How is a SQL assertion different from a normal SQL query?

A normal query produces data for use; an assertion checks whether data is safe to use. Mechanically it is still SQL, but its result is interpreted as a contract: duplicate keys, excessive nulls, stale timestamps, unexpected categories, or shifted labels become failures that can stop publication or trigger investigation.

Should SQL assertions always fail the pipeline?

It depends on the risk of the violation. Duplicate primary keys in a feature table may justify stopping immediately. A mild null-rate increase in a non-critical feature may warrant an alert instead. The decision should reflect how the downstream system behaves when the assumption is wrong, not how easy the assertion is to write.

What makes a SQL assertion useful in practice?

A useful assertion is specific, measurable, and diagnostic. It names the assumption, checks it directly, and returns enough context to debug the source, such as failing keys, unexpected values, counts, timestamps, and example records. Vague checks that only return pass or fail often save little time during an incident.

SQL Testing and Assertions

SQL testing and assertions are executable checks, written as SQL queries, that verify a table still satisfies assumptions about keys, missingness, allowed categories, recency, and target behaviour. They turn data quality expectations into pass or fail evidence before downstream analytics, feature generation, training, evaluation, or serving consumes the data.

The need comes from a dangerous property of data pipelines: bad data often looks queryable. A join can duplicate entities, a new enum value can appear, labels can arrive late, or an upstream logger can stop sending a feature, and the SQL transformation may still finish successfully. For machine learning systems, that means the model may train or predict on a population that no longer matches the assumptions used when the pipeline was designed.

An assertion is usually a query that returns either no failing rows, a boolean result, or summary diagnostics. A uniqueness check groups by the supposed key and flags keys with more than one row. A null-rate check counts missing values and compares the result with a threshold. Accepted-values checks anti-join against a small reference set. Freshness checks compare the latest timestamp with the expected window. Label checks aggregate class proportions and compare them with tolerated ranges.

The trade-off is that assertions encode judgement, not universal truth. Strict tests catch clear contract violations but can block useful data during schema migrations or legitimate business changes. Threshold tests reduce noise but may hide slow degradation if the threshold is poorly chosen. Every assertion also has runtime and ownership cost: someone must decide what failure means, maintain accepted values, tune ranges, and ensure the error output points to the actual broken records.

Engineers meet SQL assertions in orchestrated pipelines, warehouse build tools, feature stores, model training jobs, and serving-data validation steps. They are commonly placed after transformations and before publishing a table to consumers. A useful failure does not merely say that a test failed; it returns counts, offending keys or categories, relevant date ranges, and sampled rows. A common misunderstanding is treating these as database constraints only. Many are statistical or operational contracts over changing data.

Go deeper

Everything DataSQL Testing and AssertionsWrite SQL-based assertions that validate uniqueness, null rates, accepted values, freshness, and label distribution constraints.Open the lesson

Common questions

How is a SQL assertion different from a normal SQL query?: A normal query produces data for use; an assertion checks whether data is safe to use. Mechanically it is still SQL, but its result is interpreted as a contract: duplicate keys, excessive nulls, stale timestamps, unexpected categories, or shifted labels become failures that can stop publication or trigger investigation.
Should SQL assertions always fail the pipeline?: It depends on the risk of the violation. Duplicate primary keys in a feature table may justify stopping immediately. A mild null-rate increase in a non-critical feature may warrant an alert instead. The decision should reflect how the downstream system behaves when the assumption is wrong, not how easy the assertion is to write.
What makes a SQL assertion useful in practice?: A useful assertion is specific, measurable, and diagnostic. It names the assumption, checks it directly, and returns enough context to debug the source, such as failing keys, unexpected values, counts, timestamps, and example records. Vague checks that only return pass or fail often save little time during an incident.

Every term