Common data quality tests

Data quality tests are automated checks that fail (or warn) when a table violates expectations. Freshers should name the classics and how they look in SQL.

1. Null / completeness

Required columns must not be null.

select count(*) as null_orders
from fct_orders
where order_id is null;
-- expect 0

2. Unique / grain

Primary key or business key must be unique.

select order_id, count(*)
from fct_orders
group by order_id
having count(*) > 1;
-- expect 0 rows

3. Referential / relationships

Foreign keys must exist in the parent table.

select o.order_id
from fct_orders o
left join dim_customers c on o.customer_id = c.customer_id
where c.customer_id is null;

4. Range / accepted values

Numbers and enums stay in bounds.

select *
from fct_orders
where amount < 0
   or status not in ('placed', 'shipped', 'cancelled');

5. Freshness

Latest data is not older than a threshold.

select max(loaded_at) as latest
from fct_orders;
-- fail if now() - latest > interval '2 hours'

Tools: dbt tests, Great Expectations, custom monitors in the orchestrator.

Interview tip: Walk through null → unique → ref → range → freshness with one SQL idea each. Mention warn vs error severity for production.