What are the most important data-quality checks in production?

Detailed explanation

Check	Question it answers	Example
Freshness	Is the data recent?	Latest loaded_at within 2 hours
Volume	Is the amount plausible?	Today’s rows within ±30% of the 7-day median
Schema	Did the structure change?	Expected columns and types present
Uniqueness	Are keys unique?	COUNT(*) = COUNT(DISTINCT order_id)
Completeness	Are required fields present?	No NULL in order_id, order_date
Validity	Are values sensible?	amount >= 0, status in an allowed set
Referential integrity	Do facts join to dimensions?	No orphan customer_key
Reconciliation	Does it match the source?	Totals equal between staging and source

Example

CREATE TABLE orders (order_id INT, customer_id INT, amount INT);
INSERT INTO orders VALUES (1, 10, 50), (2, 11, -5), (2, 11, -5), (3, NULL, 20);

SELECT
  COUNT(*) - COUNT(DISTINCT order_id)                    AS duplicate_keys,
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END)   AS missing_customer,
  SUM(CASE WHEN amount < 0 THEN 1 ELSE 0 END)            AS negative_amounts
FROM orders;

duplicate_keys	missing_customer	negative_amounts
1	1	2

Each non-zero value is a failed check.

Policy matters as much as checks

• Blocking: uniqueness, schema, required fields. Stop the publish step.
• Warning: volume and distribution anomalies. Alert and investigate.
• Record results over time so thresholds can be based on history.

Common mistakes

1. Checking only after publishing, when consumers have already seen bad data.
2. Static thresholds that alert every weekend.
3. Checks with no owner or response plan.