Data Checks

For three weeks, a broken data feed reported revenue in cents instead of dollars. The dashboard showed an amazing hundredfold jump, and nobody questioned it. The worst data bugs don't crash anything. They just quietly give you wrong numbers.

• Cents instead of dollars
• An amazing hundredfold jump
• Nobody questioned it

A validation suite

We write down what must always be true about the sales table. Nothing missing. Nothing negative. Discounts within a sensible range. Profit never above revenue. Twelve stores. And a row count that's about right. Run it. Every check passes. Now we damage one row on purpose. Run it. Two checks catch that single bad value, out of seventy thousand rows. This takes minutes to write, and runs each time new data arrives. Libraries such as Great Expectations and Pandera do this at a larger scale.

import pandas as pd

def validate(df):
    checks = {
        "no missing revenue": df["revenue"].notna().all(),
        "revenue not negative": (df["revenue"] >= 0).all(),
        "discount 0 to 50%": df["discount"].between(0, .5).all(),
        "profit below revenue": (df["profit"] <= df["revenue"]).all(),
        "twelve stores": df["store_id"].nunique() == 12,
        "row count stable": len(df) > 60000,
    }
    return checks

sales = pd.read_csv("sales.csv")
for name, ok in validate(sales).items():
    print("PASS" if ok else "FAIL", name)

broken = sales.copy()
broken.loc[0, "revenue"] = -5
print([k for k, ok in validate(broken).items() if not ok])

PASS no missing revenue
PASS revenue not negative
PASS discount 0 to 50%
PASS profit below revenue
PASS twelve stores
PASS row count stable
['revenue not negative', 'profit below revenue']

What to check

Check the schema, so you know the columns and types are right. Check the freshness, meaning that today's data really arrived. Check the volume, as a sudden fall in row count is the most common sign of a broken feed. Check ranges. And check links between tables, so every sale points at a real store. Run them before anybody sees the numbers.

Summary