Data-quality dimensions and checks in code

The original broken export has 23 orders: one repeated key, an unknown customer, invalid quantities and a stale newest timestamp. A data-quality check compares those rows with a written expectation and records its observation, status and sample. This lesson keeps the six quality dimensions, ten original checks and two-file result table, then uses their severities to decide whether a load proceeds.

Six dimensions define different expectations

A data-quality check is an expectation about data, written as code: order ids are unique, every order has a customer, a status is one of five words, the newest row is less than a day old. Run against a table, a check either passes or names the rows that break it. Checks matter because bad data rarely raises an error. An order loaded twice or a feed that stopped at dawn still produces a plausible number, and the report goes out with it. This lesson turns the six dimensions of data quality into checks for an orders export, collects their results in one table, stops a load when a critical check fails, and keeps the results of every run so that a slow drift shows up as clearly as a sudden break.

The quality expectationquality expectation: A stated rule about data for a defined use, including the observation and acceptance condition that a check evaluates. states the rule and intended use. The UK government’s Data Quality Framework describes six core dimensions, taken from the data management association DAMA UK: completeness, uniqueness, consistency, timeliness, validity and accuracy. The framework presents them as measurable features of data and says the list may be adapted to your data and your users. Each dimension becomes a question you can ask of a table, and most questions become a check:

Accuracy is the odd one out. A table can be complete, unique, valid, consistent and fresh, and still be wrong: a mistyped price passes every rule if it falls inside the allowed range. Checks on the table itself can only make accuracy problems less likely. Proving accuracy needs a comparison with an independent source, which is the subject of reconciliation later in this module.

Distinguish required values, valid ranges and independent accuracy evidence before designing the suite.

A profile describes values while a check states an expectation

Keep the profile and the acceptance rule as separate records. An approximate distinct count is useful for exploration, but the decision to load needs the exact rule chosen for that key. Check an explicit repeated occurrence and a missing key as different cases. A sample of distinct offending keys can be short even when many rows fail, so do not infer the failing-row count from the sample length.

A profile describes what the data looks like. DuckDB’s SUMMARIZE computes, for every column, the minimum and maximum, an approximate count of distinct values, the average, the standard deviation, the quartiles, the row count and the percentage of missing values, and pandas’ describe() does much the same. A check is different: it states what the data should look like, and it fails. The difference shows on the broken export used in this lesson, a file of 23 orders with problems planted in it:

"""A profile describes the data; a check compares it with an expectation. The same file, both ways."""
import duckdb
import pandas as pd

profile = duckdb.sql("SUMMARIZE SELECT * FROM read_csv('orders_broken.csv', types = {'customer_id': 'VARCHAR'})").df()
pd.set_option("display.width", 130)
print(profile[["column_name", "column_type", "min", "max", "approx_unique", "count", "null_percentage"]].to_string(index=False))

orders = pd.read_csv("orders_broken.csv", dtype={"order_id": "str", "customer_id": "str"})
print("\nrepeated order ids:", orders["order_id"].duplicated().sum())
print("quantities outside 1 to 50:", (~orders["quantity"].between(1, 50)).sum())

column_name              column_type                    min                    max  approx_unique  count  null_percentage
   order_id                  VARCHAR                O-24001                O-24022             23     23             0.00
customer_id                  VARCHAR                  C1001                  C1100             18     23             4.35
   order_ts TIMESTAMP WITH TIME ZONE 2026-03-14 05:01:00+00 2026-03-15 01:12:00+00             15     23             0.00
       city                  VARCHAR              Bengaluru                   Pune              9     23             0.00
     status                  VARCHAR              Delivered                shipped              6     23             0.00
   quantity                   BIGINT                      0                    120              7     23             0.00
 unit_price                   DOUBLE                  149.0                 1599.0              5     23             0.00
     amount                   DOUBLE                    0.0                29940.0             16     23             0.00
      email                  VARCHAR    aarav15@example.com       om24@example.com             16     23            17.39

repeated order ids: 1
quantities outside 1 to 50: 2

The profile is useful, and it raises questions: a quantity of 0 and one of 120, a status spelt Delivered with a capital letter, 17.39 % of emails missing. It cannot answer them, because it does not know the rules. Its order-id line even looks reassuring, 23 distinct values in 23 rows, yet one id appears twice. approx_unique is an estimate; DuckDB computes approximate distinct counts with HyperLogLog, a sketch that trades exactness for speed, and on this file it is off by one for the order ids and by seven for the timestamps. Use profiles to decide which checks to write, then let the checks decide whether a file is fit to load.

Compare the original approximate 23 with the exact repeat count before accepting key uniqueness.

A results row makes each check’s outcome visible

The check result rowcheck result row: A structured record of a check's name, observation, status, severity and relevant sample under its stated counting policy. records its observation and outcome. Each check below takes a DataFrame and a few parameters and returns one dictionary: the check’s name, its dimension, its severity, a status, the number of failing rows and a sample of what failed. Ten of them make a suite for the orders export, and the suite runs on two files, a clean export and the broken one:

"""The same ten checks on a clean export and on an export with planted problems."""
import pandas as pd

from orders_suite import load, run_checks

customers = pd.read_csv("customers.csv", dtype={"customer_id": "str"})
pd.set_option("display.width", 130)
for name in ["orders_clean.csv", "orders_broken.csv"]:
    results = run_checks(load(name), customers)
    failed = results["status"].eq("FAIL").sum()
    warned = results["status"].eq("warn").sum()
    print(f"{name}: {failed} failed, {warned} warnings")
    print(results.to_string(index=False))
    print()

orders_clean.csv: 0 failed, 0 warnings
                      check    dimension severity status  failing       sample
            columns present completeness critical   pass        0             
            order_id unique   uniqueness critical   pass        0             
    customer_id not missing completeness critical   pass        0             
          customer_id known  consistency critical   pass        0             
             status allowed     validity critical   pass        0             
               city allowed     validity     warn   pass        0             
           quantity 1 to 50     validity critical   pass        0             
  amount = quantity x price  consistency critical   pass        0             
        email missing <= 5% completeness     warn   pass        1 2.5% missing
newest order_ts <= 26 h old   timeliness critical   pass        0   10.5 h old

orders_broken.csv: 7 failed, 2 warnings
                      check    dimension severity status  failing                sample
            columns present completeness critical   pass        0                      
            order_id unique   uniqueness critical   FAIL        1               O-24017
    customer_id not missing completeness critical   FAIL        1               O-24005
          customer_id known  consistency critical   FAIL        2          C1099, C1100
             status allowed     validity critical   FAIL        2 Delivered, dispatched
               city allowed     validity     warn   warn        1              Gurugram
           quantity 1 to 50     validity critical   FAIL        2                0, 120
  amount = quantity x price  consistency critical   FAIL        1               O-24018
        email missing <= 5% completeness     warn   warn        4         17.4% missing
newest order_ts <= 26 h old   timeliness critical   FAIL        1            26.8 h old

"""The ten checks of one table, the orders export, built from the reusable checks in checks.py."""
import pandas as pd

import checks

REQUIRED = ["order_id", "customer_id", "order_ts", "city", "status", "quantity", "unit_price", "amount", "email"]
STATUSES = ["placed", "shipped", "delivered", "cancelled", "returned"]
CITIES = ["Bengaluru", "Chennai", "Hyderabad", "Kolkata", "Mumbai", "New Delhi", "Pune"]
# The run time, fixed so that the example prints the same output every time; a job uses pd.Timestamp.now(tz="UTC").
NOW = pd.Timestamp("2026-03-16 09:30", tz="Asia/Kolkata")


def load(path):
    df = pd.read_csv(path, dtype={"order_id": "str", "customer_id": "str"})
    df["order_ts"] = pd.to_datetime(df["order_ts"], utc=True)
    return df


def run_checks(df, customers):
    present = checks.columns_present(df, REQUIRED)
    if present["failing"]:
        return pd.DataFrame([present])  # the other checks need these columns
    rows = [
        present,
        checks.unique(df, "order_id"),
        checks.not_missing(df, "customer_id", key="order_id"),
        checks.found_in(df, "customer_id", customers["customer_id"]),
        checks.allowed(df, "status", STATUSES),
        checks.allowed(df, "city", CITIES, severity="warn"),
        checks.between(df, "quantity", 1, 50),
        checks.amount_matches(df, key="order_id"),
        checks.missing_share(df, "email", limit=0.05),
        checks.fresh(df, "order_ts", NOW, max_hours=26),
    ]
    return pd.DataFrame(rows)

"""Reusable data-quality checks. Each states an expectation and returns one row of the results table."""
import pandas as pd


def result(check, dimension, severity, failing, sample=(), ok=None):
    """One row of the results table. A failed check stops the run when it is critical and warns otherwise."""
    if ok is None:
        ok = failing == 0
    status = "pass" if ok else ("FAIL" if severity == "critical" else "warn")
    shown = ", ".join(str(v) for v in list(sample)[:3])
    return {"check": check, "dimension": dimension, "severity": severity, "status": status, "failing": int(failing), "sample": shown}


def columns_present(df, required):
    missing = [c for c in required if c not in df.columns]
    return result("columns present", "completeness", "critical", len(missing), missing)


def unique(df, column):
    repeated = df[column].notna() & df[column].duplicated()
    return result(f"{column} unique", "uniqueness", "critical", repeated.sum(), df.loc[repeated, column].unique())


def not_missing(df, column, key):
    gap = df[column].isna()
    return result(f"{column} not missing", "completeness", "critical", gap.sum(), df.loc[gap, key])


def missing_share(df, column, limit):
    """A soft completeness check: some gaps are normal, too many are a warning."""
    gap = df[column].isna()
    return result(f"{column} missing <= {limit:.0%}", "completeness", "warn", gap.sum(), [f"{gap.mean():.1%} missing"], ok=gap.mean() <= limit)


def allowed(df, column, values, severity="critical"):
    bad = df[column].notna() & ~df[column].isin(values)
    return result(f"{column} allowed", "validity", severity, bad.sum(), df.loc[bad, column].unique())


def between(df, column, low, high):
    bad = df[column].notna() & ~df[column].between(low, high)
    return result(f"{column} {low} to {high}", "validity", "critical", bad.sum(), df.loc[bad, column].unique())


def found_in(df, column, keys):
    """Referential integrity: every value of `column` exists in `keys` (an anti-join that counts the orphans)."""
    orphan = df[column].notna() & ~df[column].isin(keys)
    return result(f"{column} known", "consistency", "critical", orphan.sum(), df.loc[orphan, column].unique())


def amount_matches(df, key):
    off = (df["amount"] - df["quantity"] * df["unit_price"]).abs() > 0.005
    return result("amount = quantity x price", "consistency", "critical", off.sum(), df.loc[off, key])


def fresh(df, column, now, max_hours):
    """Timeliness: the newest row is at most max_hours older than the run time (both time-zone aware)."""
    age = (now - df[column].max()) / pd.Timedelta(hours=1)
    stale = not age <= max_hours  # also true when the table is empty and age is NaN
    return result(f"newest {column} <= {max_hours} h old", "timeliness", "critical", int(stale), [f"{age:.1f} h old"])

On the clean file every check passes. One email of 40 is missing, which the soft completeness check accepts because 2.5 % is under its limit, and the newest order is 10.5 hours old. On the broken file the samples say where to look: order O-24017 was loaded twice, O-24005 has no customer, customers C1099 and C1100 are not in the customers table, Delivered is a spelling problem rather than a new status, dispatched is not a status of this shop, quantities of 0 and 120 are impossible, and O-24018 charges 200 more than one item at 399. The check for unknown customers is an anti-join that counts the orders without a partner instead of listing them.

A results table is worth more than printed messages:

• Passes are recorded too. A row that says pass proves that the check ran; a missing row is a check that was skipped.
• It can be filtered, sorted, saved and compared. The same table drives the decision to load, the alert and the history.
• One problem is reported once. Under the stated missingness policymissingness policy: The rule for how absent values are counted, rejected, allowed or excluded from a particular calculation., every check except those that look for gaps skips missing values, so an order without a customer is counted by customer_id not missing and not again by customer_id known.
• Samples are keys and values, not whole rows. An alert that pastes rows would copy customers’ emails into chat messages and logs; an order id is enough to find the row.
• Checks that need a column wait for it. If a column is missing, the suite reports that and runs nothing else.

Read the ten result rows as expectations, observations, statuses and samples.

Severity controls whether the load continues

Set the continuation policy before a failure appears. The same unexpected city can be a plausible expansion in one feed and a forbidden code in another. Record who should confirm the observation and what is allowed while that confirmation is pending. A warning should retain enough context to investigate, while a critical outcome should identify the boundary that must remain unpublished. Repeated failures may reveal a source problem rather than automatically proving that the threshold is wrong.

A check’s severity policyseverity policy: The declared action after a check fails, such as blocking a load or recording a warning for confirmation. says what a failure does. A critical failure stops the load: a repeated order id can overcount revenue in a sum, and an order without a customer cannot be assigned to a known customer without a declared policy. A warning is logged and kept, and the load goes on: a new city may be a real expansion, and the declared missing-email allowance does not change this suite’s revenue total, though it can affect contactability and email-based analyses. This is what a scheduled job does with the broken file’s results:

"""What a scheduled job does with the results: save them, warn on soft failures, stop on critical ones."""
import sys

import pandas as pd

from orders_suite import NOW, load, run_checks

customers = pd.read_csv("customers.csv", dtype={"customer_id": "str"})
results = run_checks(load("orders_broken.csv"), customers)
results.insert(0, "run_at", NOW)
results.to_csv("dq_results.csv", index=False)  # kept with every run, passes included

for row in results[results["status"] == "warn"].itertuples():
    print(f"warning: {row.check}: {row.failing} failing ({row.sample})")
failed = results[results["status"] == "FAIL"]
if len(failed):
    lines = [f"  {row.check}: {row.failing} failing ({row.sample})" for row in failed.itertuples()]
    sys.exit("\n".join([f"stopped before loading: {len(failed)} critical checks failed", *lines]))
print("all critical checks passed: loading the orders")

warning: city allowed: 1 failing (Gurugram)
warning: email missing <= 5%: 4 failing (17.4% missing)

stopped before loading: 7 critical checks failed
  order_id unique: 1 failing (O-24017)
  customer_id not missing: 1 failing (O-24005)
  customer_id known: 2 failing (C1099, C1100)
  status allowed: 2 failing (Delivered, dispatched)
  quantity 1 to 50: 2 failing (0, 120)
  amount = quantity x price: 1 failing (O-24018)
  newest order_ts <= 26 h old: 1 failing (26.8 h old)

The job saves the results table first, passes included, so the history has this run whatever happens next. It prints the two warnings and then calls sys.exit() with a message, which Python prints to standard error before ending with exit status 1. Shells and schedulers read a non-zero exit status as a failed run, so the load stops, nobody builds a report from the broken export, and yesterday’s tables stay as they were. Choose each severity from the damage a failure would do, not from how often the check fails: a check that stops ten loads a week is usually a check with the wrong threshold or the wrong severity, and people soon stop reading its messages.

Retain the original two warnings and exit 1 transcript while examining the seven critical failures.

Compare aware timestamps before grouping local dates

A timeliness check compares the newest timestamp with the time of the run. Both must carry a time zone, or the comparison silently shifts by the difference between them. The orders store their times in UTC, and the job runs at 09:30 in Indian Standard Time, five and a half hours ahead:

"""How old is the newest order? Stored in UTC, judged at a run time in Indian Standard Time (UTC+05:30)."""
import pandas as pd

orders = pd.read_csv("orders_broken.csv")
ts = pd.to_datetime(orders["order_ts"], utc=True)
now = pd.Timestamp("2026-03-16 09:30", tz="Asia/Kolkata")  # the run time, fixed for the example
newest = ts.max()
print("newest order:", newest, "=", newest.tz_convert("Asia/Kolkata"))
print("run time:    ", now.tz_convert("UTC"), "=", now)

hours = pd.Timedelta(hours=1)
print(f"age, both aware:              {(now - newest) / hours:5.1f} h  (over the 26 h limit: stale)")
# Two ways to lose the time zone. The server's clock in UTC against order times turned into IST wall-clock time:
utc_clock = now.tz_convert("UTC").tz_localize(None)
ist_orders = newest.tz_convert("Asia/Kolkata").tz_localize(None)
print(f"age, UTC clock - IST times:   {(utc_clock - ist_orders) / hours:5.1f} h  (5.5 h too young: the stale feed passes)")
# An IST wall clock against order times left in UTC:
ist_clock = now.tz_localize(None)
utc_orders = newest.tz_localize(None)
print(f"age, IST clock - UTC times:   {(ist_clock - utc_orders) / hours:5.1f} h  (5.5 h too old: fresh feeds fail)")

clean = pd.to_datetime(pd.read_csv("orders_clean.csv")["order_ts"], utc=True)
per_day = pd.DataFrame({
    "UTC date": clean.dt.date.value_counts().sort_index(),
    "IST date": clean.dt.tz_convert("Asia/Kolkata").dt.date.value_counts().sort_index(),
}).fillna(0).astype(int)
per_day.index.name = None
print("\norders per day in the clean file:")
print(per_day.to_string())

newest order: 2026-03-15 01:12:00+00:00 = 2026-03-15 06:42:00+05:30
run time:     2026-03-16 04:00:00+00:00 = 2026-03-16 09:30:00+05:30
age, both aware:               26.8 h  (over the 26 h limit: stale)
age, UTC clock - IST times:    21.3 h  (5.5 h too young: the stale feed passes)
age, IST clock - UTC times:    32.3 h  (5.5 h too old: fresh feeds fail)

orders per day in the clean file:
            UTC date  IST date
2026-03-14        21        15
2026-03-15        19        25

With both times aware, the newest order is 26.8 hours old and the feed is stale. Turning the order times into IST wall-clock times and subtracting them from a server clock in UTC makes the same feed 5.5 hours younger, and it passes. Doing the opposite makes every feed 5.5 hours older, so a feed that is 21 hours old looks 26.5 hours old and fails. The rule that avoids both: parse stored times with pd.to_datetime(..., utc=True), take the run time with pd.Timestamp.now(tz="UTC"), and convert to Asia/Kolkata only to display a time or to group rows by Indian dates.

The last table shows why grouping matters. The clean file’s 40 orders split 21 and 19 over its two days by UTC date, and 15 and 25 by IST date, because the orders placed between 18:30 and midnight UTC already belong to the next day in India. A check that compares each day’s order count with a normal range must count days in the calendar its report uses.

Use the original aware 26.8-hour age before comparing its 21.3 and 32.3-hour mistakes.

Recent baselines and fixed limits catch different changes

The gate saved its results table before deciding anything. Kept over many runs, those tables answer questions that no single run can: what is normal for this feed, when a check started failing, and whether a problem is a break or a drift. This history holds two numbers per run for the full feed, about 400 orders a day:

"""Results kept from every run make slow problems visible: two metrics of the full orders feed over twelve runs."""
import pandas as pd

history = pd.read_csv("dq_history.csv")
# The baseline of each run is the median of the seven runs before it.
baseline = history[["orders", "email_missing_pct"]].shift(1).rolling(7).median()
history["orders_vs_baseline"] = (history["orders"] / baseline["orders"]).round(2)
history["email_rise_pts"] = (history["email_missing_pct"] - baseline["email_missing_pct"]).round(1)

drop = history["orders_vs_baseline"] < 0.7  # a sudden fall in volume
jump = history["email_rise_pts"] >= 2.0  # a sudden rise in missing emails
over = history["email_missing_pct"] > 5.0  # the fixed limit of the suite
history["flags"] = (
    drop.map({True: "volume drop ", False: ""}) + jump.map({True: "email jump ", False: ""}) + over.map({True: "email over 5 %", False: ""})
).str.strip()
print(history.tail(5).to_string(index=False))

  run_date  orders  email_missing_pct  orders_vs_baseline  email_rise_pts          flags
2026-03-12     419                3.4                1.04             0.4               
2026-03-13     212                3.6                0.52             0.5    volume drop
2026-03-14     398                4.1                0.98             0.9               
2026-03-15     410                4.7                1.03             1.5               
2026-03-16     406                5.3                1.02             1.9 email over 5 %

Each run is compared with the median of the seven runs before it. On one run the orders fall to 212, half the usual number, and the rule flags the drop at once. The missing emails behave differently: they rise from 3.4 % to 5.3 % over the last five runs, about half a point a day, and no single run is 2 points above its baseline, because the baseline climbs with the data. Only the suite’s fixed limit of 5 % notices, on the last run. Use both kinds of rule: a comparison with recent runs for sudden breaks, and a fixed limit, agreed with the people who use the data, for drifts. A chart of the history can help a reader inspect drift before a configured threshold fires; whether it is apparent depends on the scale, noise and pattern.

Keep the original 12-run history while comparing its volume break and missingness drift.

Validate inputs, outputs and stored-data constraints

A check’s location defines which writers and transformations it protects. An incoming-file check can retain source context before a transformation changes the representation. An output check assesses the consumer’s actual contract. A database constraint applies to supported writes to the table, including writes that bypass the pipeline. When a rule is repeated across boundaries, keep its definition aligned and test the same relevant cases instead of assuming that similar names make the implementations equivalent.

Run checks at the edges of a pipeline: on raw data as it arrives, before anything is built from it, and on outputs before anyone sees them. Measure vectorised checks alongside loading for the relevant workload and job budget. Some rules can also live in the database: DuckDB tables accept NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK constraints, which reject a bad row when it is inserted. Constraints suit rules that must never be broken, such as a unique key; they cannot express a soft limit such as “at most 5 % of emails missing”. The next lesson compares constraints with schema validation libraries such as pandera.

Separate incoming, produced and stored-data contracts before choosing where each rule lives.

The exercise counts failing rows and samples distinct examples

The return shape is part of the original contract. Count every violating occurrence, then choose a bounded sample of distinct nonmissing values under the exercise’s sorting policy. An empty frame, a duplicated key and a missing value test different branches of that contract. Freshness compares aware instants with the inclusive maximum-age boundary. Keep the original tests and inputs while implementing the functions so that a completed assertion evaluates the intended rule. A repeated key can contribute several violating rows while appearing only once in the short sample. Treat those two outputs as different quantities.

from datetime import datetime


def missing(value):
    """A cell read from a CSV is missing when it is empty or holds only spaces."""
    return value is None or str(value).strip() == ""


def result(check, failing, sample):
    """One row of the results table: the check's name, how many rows fail it, and up to 3 sorted examples."""
    return {"check": check, "failing": failing, "sample": sorted(sample)[:3]}


def check_unique(rows, column):
    # Count the rows whose value appeared in an earlier row; skip missing values.
    return result(f"{column} unique", 0, [])


def check_not_missing(rows, column, key):
    # Count the rows where `column` is missing; the sample holds their `key` values.
    return result(f"{column} not missing", 0, [])


def check_allowed(rows, column, allowed):
    # Count the values that are not missing and not in `allowed`.
    return result(f"{column} allowed", 0, [])


def check_between(rows, column, low, high):
    # Count the values that are not missing and are not numbers from low to high.
    return result(f"{column} {low} to {high}", 0, [])


def check_known(rows, column, keys):
    # Count the values that are not missing and not in `keys`.
    return result(f"{column} known", 0, [])


def check_fresh(rows, column, now, max_hours):
    # Fail (1) when the newest timestamp is more than max_hours before `now`, or when there is none.
    return {"check": f"newest {column} <= {max_hours} h old", "failing": 0, "sample": []}

from datetime import datetime, timedelta, timezone

from order_checks import check_allowed, check_between, check_fresh, check_known, check_not_missing, check_unique

IST = timezone(timedelta(hours=5, minutes=30))
NOW = datetime(2026, 3, 16, 9, 30, tzinfo=IST)
STATUSES = {"placed", "shipped", "delivered", "cancelled", "returned"}
CUSTOMERS = {"C1001", "C1002", "C1003", "C1004"}


def order(order_id, customer_id, status, quantity, order_ts):
    return {"order_id": order_id, "customer_id": customer_id, "status": status, "quantity": quantity, "order_ts": order_ts}


CLEAN = [
    order("O-1", "C1001", "placed", "1", "2026-03-15T20:15:00Z"),
    order("O-2", "C1002", "shipped", "50", "2026-03-16T01:40:00Z"),
    order("O-3", "C1003", "delivered", "3", "2026-03-16T02:05:00+00:00"),
    order("O-4", "C1001", "returned", "2", "2026-03-16T08:55:00+05:30"),
]
DUPLICATES_AND_GAPS = [
    order("O-7", "C1001", "placed", "1", "2026-03-16T01:00:00Z"),
    order("O-7", "C1001", "placed", "1", "2026-03-16T01:00:00Z"),
    order("O-8", "", "shipped", "2", "2026-03-16T01:10:00Z"),
    order("O-7", "C1002", "placed", "1", "2026-03-16T01:20:00Z"),
    order("O-9", "   ", "placed", "4", "2026-03-16T01:30:00Z"),
    order("O-6", "C1009", "placed", "1", "2026-03-16T01:40:00Z"),
    order("O-5", "C1009", "placed", "1", "2026-03-16T01:50:00Z"),
    order("", "C1003", "placed", "1", "2026-03-16T02:00:00Z"),
    order("", "C1004", "placed", "1", "2026-03-16T02:10:00Z"),
    order("O-6", "C1004", "placed", "1", "2026-03-16T02:20:00Z"),
]
BAD_VALUES = [
    order("O-11", "C1001", "Delivered", "0", "2026-03-16T01:00:00Z"),
    order("O-12", "C1002", "dispatched", "120", "2026-03-16T01:00:00Z"),
    order("O-13", "C1003", "delivered", "two", "2026-03-16T01:00:00Z"),
    order("O-14", "C1004", "", "", "2026-03-16T01:00:00Z"),
    order("O-15", "C1001", "placed ", "-1", "2026-03-16T01:00:00Z"),
    order("O-16", "C1002", "Delivered", "50.5", "2026-03-16T01:00:00Z"),
]
STALE = [
    order("O-21", "C1001", "placed", "1", "2026-03-14T23:00:00Z"),
    order("O-22", "C1002", "placed", "1", "2026-03-15T01:12:00Z"),
    order("O-23", "C1003", "placed", "1", ""),
]


def test_clean_file_passes():
    """every check passes on a clean file"""
    assert check_unique(CLEAN, "order_id") == {"check": "order_id unique", "failing": 0, "sample": []}
    assert check_not_missing(CLEAN, "customer_id", "order_id") == {"check": "customer_id not missing", "failing": 0, "sample": []}
    assert check_allowed(CLEAN, "status", STATUSES) == {"check": "status allowed", "failing": 0, "sample": []}
    assert check_between(CLEAN, "quantity", 1, 50) == {"check": "quantity 1 to 50", "failing": 0, "sample": []}
    assert check_known(CLEAN, "customer_id", CUSTOMERS) == {"check": "customer_id known", "failing": 0, "sample": []}
    assert check_fresh(CLEAN, "order_ts", NOW, 26) == {"check": "newest order_ts <= 26 h old", "failing": 0, "sample": [0.6]}


def test_repeated_keys():
    """counts every repeat after the first, ignores missing keys, samples sorted distinct values"""
    assert check_unique(DUPLICATES_AND_GAPS, "order_id") == {"check": "order_id unique", "failing": 3, "sample": ["O-6", "O-7"]}


def test_missing_values():
    """treats empty and blank cells as missing and samples the keys of those rows"""
    assert check_not_missing(DUPLICATES_AND_GAPS, "customer_id", "order_id") == {"check": "customer_id not missing", "failing": 2, "sample": ["O-8", "O-9"]}
    assert check_not_missing(DUPLICATES_AND_GAPS, "order_id", "customer_id")["failing"] == 2


def test_unknown_customers():
    """counts rows whose customer is not in the customer table, but not the missing ones"""
    assert check_known(DUPLICATES_AND_GAPS, "customer_id", CUSTOMERS) == {"check": "customer_id known", "failing": 2, "sample": ["C1009"]}


def test_values_outside_the_allowed_set():
    """is case-sensitive, ignores missing values, and does not strip spaces"""
    assert check_allowed(BAD_VALUES, "status", STATUSES) == {"check": "status allowed", "failing": 4, "sample": ["Delivered", "dispatched", "placed "]}


def test_values_outside_the_range():
    """keeps both ends inside the range and counts text that is not a number"""
    assert check_between(BAD_VALUES, "quantity", 1, 50) == {"check": "quantity 1 to 50", "failing": 5, "sample": ["-1", "0", "120"]}
    edges = [order("O-31", "C1001", "placed", "1", ""), order("O-32", "C1001", "placed", "50", "")]
    assert check_between(edges, "quantity", 1, 50)["failing"] == 0


def test_stale_feed():
    """compares time-zone-aware times, ignores missing timestamps, and fails when there are none"""
    assert check_fresh(STALE, "order_ts", NOW, 26) == {"check": "newest order_ts <= 26 h old", "failing": 1, "sample": [26.8]}
    assert check_fresh(STALE, "order_ts", NOW, 27) == {"check": "newest order_ts <= 27 h old", "failing": 0, "sample": [26.8]}
    exactly = [order("O-41", "C1001", "placed", "1", "2026-03-15T02:00:00Z")]
    assert check_fresh(exactly, "order_ts", NOW, 26)["failing"] == 0
    assert check_fresh(STALE[2:], "order_ts", NOW, 26) == {"check": "newest order_ts <= 26 h old", "failing": 1, "sample": []}

Keep the original six functions and seven tests while separating failing rows from sample values.

Practise the original quality contracts

Interview questions

Fresher · What is the difference between profiling a dataset and checking it?

Profiling describes what the data looks like: column types, minimums and maximums, distinct counts, quartiles and the share of missing values, as pandas’ describe() or DuckDB’s SUMMARIZE report them. Checking compares the data with an expectation and fails when the expectation is broken: order ids are unique, quantities are from 1 to 50, the newest row is at most 26 hours old under this suite’s inclusive boundary. A profile cannot fail, because it does not know the rules, and some of its numbers are estimates: DuckDB’s distinct counts are approximate. You profile a new dataset to learn what to expect, write the expectations down as checks, and then run the checks on every load. A profile answers “what is in this file?”; a check answers “may we use this file?”.

Key takeaways

• A check is an expectation written as code that passes or names the rows that break it; a profile only describes.
• The six dimensions are completeness, uniqueness, validity, consistency, timeliness and accuracy. Accuracy needs a source outside the table.
• Return one results row per check, with severity, status, failing count and a sample of keys, and save the table on every run, passes included.
• Critical failures stop the load and keep the last good output; warnings are logged. Set severity by the damage a failure does.
• Compare time-zone-aware times only: losing the zone on one side moves a feed’s age by the full offset, 5.5 hours for IST.
• Keep a history: compare each run with recent runs to catch breaks, and with fixed limits to catch slow drifts.