Data quality · Quality Foundations

Common data quality tests

Easytestsnot_nulluniquerelationshipsfreshness

Interview question

What kinds of data quality tests should you run (null, unique, referential, range, freshness)?

How to answer

The short answer

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.

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

2. Unique / grain

or business key must be unique.

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

3. Referential / relationships

must exist in the parent table.

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

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

5. Freshness

Latest data is not older than a threshold.

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