Validation checks you can automate

You fixed the joins. You labeled the time zone. You even wrote “partial day” on the chart. Then a pipeline quietly loaded half a file, a foreign key pointed at a deleted customer, and yesterday’s revenue sat 40% under last week for a reason nobody coded as an alert. Leadership found it first. That is not a tooling failure. That is a missing safety net.

This is Part 6 of Data quality for people who ship numbers. Part 5 made dates and fiscal calendars explicit. Now you automate the boring checks that protect every number you ship: row counts, referential integrity, freshness, and simple sanity comparisons. If you profiled data in Part 2, this is profiling on a schedule with a pass or fail. SQL skills from the SQL series and small scripts from Python for analytics are enough. You do not need a platform purchase to start. Foundations still matter: checks should serve a decision, not theater (Analytics foundations). Spreadsheet handoffs from From spreadsheets to real data need the same gates when they become warehouse tables.

What you’ll learn

• A small checklist of automated checks every analyst can own
• How to implement row counts, uniqueness, null rates, and referential integrity
• Freshness and “yesterday versus last week” sanity without fake precision
• SQL and Python patterns you can paste into a morning job
• How to fail loud, log clearly, and avoid alert fatigue

Validation is not perfection. It is early news.

What a night-run status board can look like:

Validation here means automated tests that say “this table is safe enough to use for today’s decisions,” or “stop, something drifted.” It is not a claim that every field matches reality forever. Part 1’s dimensions (completeness, accuracy, consistency, timeliness, uniqueness) become concrete predicates you can code.

Good checks share three traits:

• Cheap to run so you actually schedule them
• Clear on failure so a human knows what broke and where
• Tied to a consumer so you fix what dashboards and exports care about first

If a check never fails and never gets read, delete it. If a check fails daily and everyone ignores it, fix the threshold or the pipeline. Noise is the enemy of trust.

The starter checklist (ship this first)

Start with three checks on your most blamed table. Expand after they save you once. A short list that runs is better than a beautiful framework that never ships.

Row counts: the canary that still works

Row counts are blunt and valuable. They catch empty loads, double loads, and filters applied twice. Prefer a band over a single magic number when volume naturally moves (weekends, promotions, seasonality from foundations Part 5 style thinking).

Two useful flavors:

• Absolute floor: “Daily orders fact must have more than 0 rows after the morning load.”
• Relative band: “Yesterday’s row count should sit between 60% and 140% of the median of the last 28 same-weekdays,” adjusted for known holidays later.

Document the grain. Counting order lines is not counting orders. If Part 3 taught you to respect grain for dedupe, the same grain sentence belongs on the check name.

Referential integrity without enterprise theater

Referential integrity means child rows point at parents that exist. Orders reference customers. Line items reference products. Events reference sessions. When the parent is missing, joins drop rows or inflate “unknown” buckets and people argue about marketing performance.

You do not need database foreign keys enforced on every analytics table to care. You need a daily query that counts orphans and fails when the count exceeds a threshold (often zero for core facts, or a tiny rate for soft-deleted dimensions).

-- Orphan orders: customer_id not found in customers dimension
SELECT
  COUNT(*) AS orphan_orders
FROM analytics.orders_fact o
LEFT JOIN analytics.customers_dim c
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
  AND o.order_date >= CURRENT_DATE - INTERVAL '7' DAY;

-- Soft-delete aware version: parent exists but is inactive
SELECT
  COUNT(*) AS orders_pointing_at_inactive_customers
FROM analytics.orders_fact o
JOIN analytics.customers_dim c
  ON c.customer_id = o.customer_id
WHERE c.is_active = FALSE
  AND o.order_date >= CURRENT_DATE - INTERVAL '7' DAY;

Example output:

Decide the policy: block the dashboard, open a ticket, or quarantine rows into an exceptions table. Silent drop in a join is the worst policy because it looks like a clean number.

Freshness: timeliness you can measure

Timeliness is a quality dimension from Part 1. Operationalize it as freshness: how old is the newest trustworthy row relative to now?

• max(event_time) for “when did reality last show up?”
• max(loaded_at) for “when did the pipeline last write?”
• Compare both. Fresh load of stale events is a different failure than no load at all.

Set an SLA that matches the decision. Hourly ops boards may need data less than 90 minutes old. Weekly executive packs may tolerate a T+1 close. Part 5’s clock rules still apply: freshness in UTC versus local business day can disagree near midnight.

SELECT
  MAX(ordered_at_utc) AS max_event_utc,
  MAX(loaded_at_utc) AS max_load_utc,
  EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(ordered_at_utc))) / 3600.0
    AS event_lag_hours,
  EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - MAX(loaded_at_utc))) / 3600.0
    AS load_lag_hours
FROM analytics.orders_fact
WHERE order_date >= CURRENT_DATE - INTERVAL '3' DAY;

Yesterday versus last week: sanity without superstition

Example:

Seasonality is real. Mondays do not look like Sundays. Black Friday does not look like a normal Friday. A good sanity check compares like with like and uses wide bands, not two-decimal “anomaly scores” you cannot explain in a meeting.

A practical pattern:

• Compute yesterday’s metric at the agreed grain and clock (Part 5).
• Compare to the same weekday last week, or to the median of the last four same weekdays.
• Fail only on extreme ratios or absolute floors you would bet a reputation on.
• Allow an override table for known events (product launch, outage, holiday).

This is not a substitute for proper monitoring systems. It is a seatbelt. Seatbelts are boring until the day they are not.

Worked example: morning checks on a toy orders table

Below is a compact Python checklist you can adapt. It uses pandas so you can run it on a warehouse extract or a CSV while you build trust in the pattern. Later you can push the same predicates into SQL jobs, dbt tests, or a scheduler you already have.

Customers dimension: C1, C2, C3 only. Statuses allowed: paid, pending, refunded. You should expect failures for duplicate 503, orphan C9, and null amount on 504.

from dataclasses import dataclass
from datetime import date, datetime, timezone
from typing import Callable, List

import pandas as pd

orders = pd.DataFrame(
    {
        "order_id": [501, 502, 503, 503, 504, 505],
        "customer_id": ["C1", "C2", "C9", "C2", "C1", "C3"],
        "order_date": pd.to_datetime(
            ["2024-06-10", "2024-06-10", "2024-06-10", "2024-06-10", "2024-06-11", "2024-06-11"]
        ),
        "amount": [40.0, 12.5, 30.0, 30.0, None, 22.0],
        "status": ["paid", "paid", "paid", "paid", "pending", "refunded"],
        "loaded_at_utc": pd.to_datetime(
            ["2024-06-12 06:00:00"] * 6, utc=True
        ),
    }
)
customers = pd.DataFrame({"customer_id": ["C1", "C2", "C3"]})
ALLOWED_STATUS = {"paid", "pending", "refunded"}

@dataclass
class CheckResult:
    name: str
    ok: bool
    detail: str

def run_checks(checks: List[Callable[[], CheckResult]]) -> List[CheckResult]:
    return [fn() for fn in checks]

def check_row_count_floor(df: pd.DataFrame, minimum: int) -> CheckResult:
    n = len(df)
    return CheckResult(
        "row_count_floor",
        n >= minimum,
        f"rows={n}, minimum={minimum}",
    )

def check_unique_key(df: pd.DataFrame, col: str) -> CheckResult:
    dupes = int(df[col].duplicated().sum())
    return CheckResult(
        f"unique_{col}",
        dupes == 0,
        f"duplicate_rows={dupes}",
    )

def check_null_rate(df: pd.DataFrame, col: str, max_rate: float) -> CheckResult:
    rate = float(df[col].isna().mean())
    return CheckResult(
        f"null_rate_{col}",
        rate <= max_rate,
        f"null_rate={rate:.3f}, max={max_rate:.3f}",
    )

def check_referential(df: pd.DataFrame, dim: pd.DataFrame, key: str) -> CheckResult:
    orphans = int((~df[key].isin(set(dim[key]))).sum())
    return CheckResult(
        f"referential_{key}",
        orphans == 0,
        f"orphan_rows={orphans}",
    )

def check_accepted_values(df: pd.DataFrame, col: str, allowed: set) -> CheckResult:
    bad = int((~df[col].isin(allowed)).sum())
    return CheckResult(
        f"accepted_{col}",
        bad == 0,
        f"invalid_rows={bad}",
    )

def check_freshness(df: pd.DataFrame, col: str, max_lag_hours: float) -> CheckResult:
    max_ts = df[col].max()
    now = datetime.now(timezone.utc)
    lag_h = (now - max_ts.to_pydatetime()).total_seconds() / 3600.0
    # For the toy run, treat max_lag loosely; in prod use real now vs SLA
    return CheckResult(
        f"freshness_{col}",
        lag_h <= max_lag_hours,
        f"lag_hours={lag_h:.1f}, max={max_lag_hours}",
    )

def check_yesterday_vs_last_week(
    df: pd.DataFrame,
    day: date,
    value_col: str,
    ratio_min: float = 0.5,
    ratio_max: float = 1.8,
) -> CheckResult:
    y = df.loc[df["order_date"].dt.date == day, value_col].sum(min_count=1)
    prior = date.fromordinal(day.toordinal() - 7)
    p = df.loc[df["order_date"].dt.date == prior, value_col].sum(min_count=1)
    if pd.isna(y) or pd.isna(p) or p == 0:
        return CheckResult(
            "yesterday_vs_last_week",
            False,
            f"insufficient_data y={y}, prior={p}, prior_day={prior}",
        )
    ratio = float(y) / float(p)
    ok = ratio_min <= ratio <= ratio_max
    return CheckResult(
        "yesterday_vs_last_week",
        ok,
        f"ratio={ratio:.2f}, y={y}, prior_week={p}",
    )

results = run_checks(
    [
        lambda: check_row_count_floor(orders, minimum=1),
        lambda: check_unique_key(orders, "order_id"),
        lambda: check_null_rate(orders, "amount", max_rate=0.0),
        lambda: check_referential(orders, customers, "customer_id"),
        lambda: check_accepted_values(orders, "status", ALLOWED_STATUS),
        lambda: check_freshness(orders, "loaded_at_utc", max_lag_hours=48),
        lambda: check_yesterday_vs_last_week(
            orders, day=date(2024, 6, 11), value_col="amount"
        ),
    ]
)

for r in results:
    flag = "PASS" if r.ok else "FAIL"
    print(f"{flag:4}  {r.name:28}  {r.detail}")

failed = [r for r in results if not r.ok]
if failed:
    raise SystemExit(f"{len(failed)} checks failed")
print("all checks passed")

Example:

When you run this toy, uniqueness, null rate, and referential checks should fail. That is the point. A green suite on dirty data is a liability. Wire SystemExit or an equivalent non-zero status into your scheduler so a red build blocks the “data ready” message in Slack.

SQL twin for the same ideas

-- Bundle check outputs into one result set for a log table
WITH metrics AS (
  SELECT
    (SELECT COUNT(*) FROM analytics.orders_fact
      WHERE order_date = DATE '2024-06-11') AS rows_yesterday,
    (SELECT COUNT(*) - COUNT(DISTINCT order_id)
      FROM analytics.orders_fact
      WHERE order_date >= DATE '2024-06-01') AS duplicate_order_id_extra_rows,
    (SELECT AVG(CASE WHEN amount IS NULL THEN 1.0 ELSE 0.0 END)
      FROM analytics.orders_fact
      WHERE order_date >= DATE '2024-06-01') AS null_amount_rate,
    (SELECT COUNT(*)
      FROM analytics.orders_fact o
      LEFT JOIN analytics.customers_dim c ON c.customer_id = o.customer_id
      WHERE c.customer_id IS NULL
        AND o.order_date >= DATE '2024-06-01') AS orphan_orders
)
SELECT
  *,
  CASE WHEN rows_yesterday > 0 THEN 'PASS' ELSE 'FAIL' END AS row_count_status,
  CASE WHEN duplicate_order_id_extra_rows = 0 THEN 'PASS' ELSE 'FAIL' END AS uniqueness_status,
  CASE WHEN null_amount_rate = 0 THEN 'PASS' ELSE 'FAIL' END AS null_status,
  CASE WHEN orphan_orders = 0 THEN 'PASS' ELSE 'FAIL' END AS referential_status
FROM metrics;

Where to run checks (pick boring infrastructure)

Use tools you already operate:

• A scheduled SQL script in the warehouse with results written to dq_check_log
• A Python job next to your pipeline scripts (Part 8 style pipelines in the Python series)
• dbt tests or similar if your team already transforms with them
• A notebook only as a prototype, not as the long-term production gate

Log at least: check name, dataset, as-of timestamp, status, measured value, threshold, and a short owner. That log becomes input for Part 7’s scorecard. Without history, every failure feels brand new.

Build a check log you can audit later

A check that prints to stdout and vanishes is half a check. Write a log table or append-only CSV with enough columns to reconstruct a Monday morning:

That log is how you prove the dashboard was green when leadership screenshotted it, or red when someone shipped anyway. It also feeds Part 7’s scorecard without heroic archaeology.

Sampling and volume: when full scans hurt

On huge facts, daily full-table distinct counts can be expensive. Stay honest without melting the warehouse:

• Run heavy uniqueness checks on the rolling last 7 to 30 days, plus a weekly full scan if needed
• Partition filters on order_date or load date so checks touch only new slices
• Use approximate distincts only as WARN signals, never as the sole FAIL gate for financial keys
• Keep a cheap always-on set (row count, freshness, null rate on critical columns) separate from deep weekly audits

Cost control is part of quality operations. A suite that gets disabled after the cloud bill arrives helps nobody.

Alert design for humans

Bad alerts train people to mute you. Good alerts are rare, specific, and actionable.

• Name the consumer: “Sales daily dashboard blocked” beats “check failed.”
• Include the query or link: one click to the failing metric.
• Separate warn and fail: warn on soft bands, fail on empty loads and broken keys.
• Deduplicate: one incident per dataset per morning, not fifty messages for fifty partitions.
• Close the loop: when fixed, post the recovery so trust rebuilds.

Rule of thumb: If you would not wake someone for it on a Saturday, it is a log line, not a page. If you would stake a QBR on it, it is a gate.

Common mistakes

What “good enough” checks look like for different consumers

Not every table deserves the same gates. Match intensity to blast radius, the same judgment call foundations teach for analysis quality.

Write the consumer on the check suite name. “orders_fact_exec_gate” tells a story. “misc_tests_v3” does not.

How to practice this week

1. Choose one dataset that feeds a visible dashboard. Write the grain sentence and the primary key.
2. Implement three checks: row count floor, unique key, and freshness on load time.
3. Add one referential check to the most important dimension parent.
4. Run the suite on purpose against a known bad extract (duplicate a key) and confirm it fails.
5. Log results to a table or CSV with timestamps. Tomorrow, add yesterday-versus-last-week for one metric.
6. Tell one stakeholder what “green” means in plain language. Invite them to distrust red less than silence.

More learning paths sit on the Learn hub. If SQL is still rusty, the filtering and aggregation parts of the SQL series are enough to write these queries. If Python is your hammer, keep scripts short and boring like the pipeline habits in Python for analytics.

Quick recap

• Automated validation is early news about safety, not a promise of perfect data.
• Start with row counts, uniqueness, null rates, referential integrity, freshness, and wide sanity bands.
• Code checks in SQL or Python; schedule them; fail with clear detail and an owner.
• Respect grain, clocks, and same-weekday comparisons so checks do not cry wolf.
• Log history. Next, turn that history into a trust document others can read.

Part 7 closes the series: a light data quality scorecard (dataset, owner, metrics, known issues, next check) so teammates can trust your numbers without a bureaucracy. Program-level governance still matters later; this is the hands-on layer you can publish this month.

Sources

Research and further reading used for this article:

• Wang, R. Y., and Strong, D. M. (1996), Beyond Accuracy: What Data Quality Means to Data Consumers: http://mitiq.mit.edu/Documents/Publications/TDQMpub/14_Beyond_Accuracy.pdf
• DAMA NL, Dimensions of Data Quality research: https://www.dama-nl.org/wp-content/uploads/2020/09/DDQ-Dimensions-of-Data-Quality-Research-Paper-version-1.2-d.d.-3-Sept-2020.pdf
• IBM, Data quality dimensions: https://www.ibm.com/docs/en/ws-and-kc?topic=quality-data-dimensions
• Collibra, The 6 data quality dimensions: https://www.collibra.com/blog/the-6-dimensions-of-data-quality
• dbt Labs, About tests: https://docs.getdbt.com/docs/build/tests
• Great Expectations documentation: https://docs.greatexpectations.io/
• Analytics Made Simple, Learn hub: https://analyticsmadesimple.com/learn/
• Analytics Made Simple, Analytics foundations: https://analyticsmadesimple.com/series/analytics-foundations/
• Analytics Made Simple, From spreadsheets to real data: https://analyticsmadesimple.com/series/spreadsheets-to-data/
• Analytics Made Simple, SQL series: https://analyticsmadesimple.com/series/sql/
• Analytics Made Simple, Python for analytics: https://analyticsmadesimple.com/series/python/
• Analytics Made Simple, Data Governance 101: https://analyticsmadesimple.com/key-terms/data-governance/

More definitions in Key Terms →

Merch on Etsy

Open store →

Y Hat Mouse Pad, Data Science Mouse Pad, Analytics Mouse Pad, Y Pred Mouse Pad View on Etsy → You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → You're On Mute White Ceramic Coffee Mug, Data Science Mug, Data Mug, Analytics Mug, Statistics Mug, Programming Mug, Gift Mug, Coffee Mug View on Etsy → Dashboard to Excel Ceramic Coffee Mug View on Etsy →