Day 8. Profiling and data contracts

⏱ 4h · learn 70m · lab 130m · ship 25m · review 15m🏷 QSR (POS exports)Before: Week 1

By the end of today you can

Profile a new source systematically before writing transforms
State the grain and keys of a table and test them
Write a data contract as code with pandera
Decide what a contract violation should do (fail, quarantine, warn)

1. Why this matters

Juniors start cleaning by writing code. Seniors start by asking the data questions and writing the answers down as rules. Those rules become a contract: what the source promises (columns, types, keys, ranges), checked on every load. When a vendor changes a column at 3 a.m., the contract fails loudly instead of the CFO's dashboard failing quietly.

2. The profiling checklist

For every new source, answer these in docs/profiles/<source>.md before transforming anything:

Grain: what is one row? (One order line? One order? One item per order with qty?)
Candidate key: which columns are unique together? Test it: count(*) = count(distinct key).
Types: what each column should be vs how it arrived (text dates, numbers with commas).
Nulls and blanks: per column, and whether null means "unknown", "none" or "not applicable".
Ranges and domains: min/max for numbers and dates; distinct values for categories.
Volume: rows per file/day, and its normal range.
Relationships: do all menu_item_ids exist in the menu reference? (Orphans.)
Time semantics: which timestamp, in which zone, business date rule.

DuckDB makes this fast, straight on the files:

-- in duckdb CLI or python duckdb.sql(...)
summarize select * from read_csv('data/landing/shared_drive/qsr/*/pos_*.csv', union_by_name=true, filename=true);

select count(*) rows, count(distinct order_id || menu_item_id) keys from read_csv('data/landing/shared_drive/qsr/*/pos_*.csv');

select p.menu_item_id from read_csv('data/landing/shared_drive/qsr/*/pos_*.csv') p
anti join read_csv('data/reference/menu.csv') m using (menu_item_id);

Maths and principle

Principle: a key is a set of columns on which the table is an injective function: no two rows share a key value.

Equation: key \(K\) is valid iff \(|T| = |\pi_K(T)|\), the number of rows equals the number of distinct projections onto \(K\). Duplicates \(= |T| - |\pi_K(T)|\).

Analogy: a function maps each input to one output; a key maps each key value to one row. Where it breaks: if the POS writes one row per item without a line number, order_id + menu_item_id isn't unique when someone orders two separate burgers. Profiling tells you whether you need a line number or must aggregate.

Try it: 10,000 rows, 9,640 distinct (order_id, menu_item_id). How many rows are "extra"? (360; now find whether they're duplicates or genuine repeat lines.)

3. Contracts as code

A contract lives next to the code and runs on every load. pandera works with pandas and Polars.

import pandera.polars as pa
import polars as pl

class PosOrderLine(pa.DataFrameModel):
    restaurant_id: str = pa.Field(str_matches=r"^Q\d{3}$")
    order_id: str = pa.Field(nullable=False)
    order_ts: pl.Datetime = pa.Field(nullable=False)
    channel: str = pa.Field(isin=["counter", "drive_thru", "app", "delivery"])
    menu_item_id: str
    qty: int = pa.Field(ge=1, le=50)
    price: float = pa.Field(gt=0, lt=100)

    class Config:
        strict = True          # unexpected columns fail: catches schema drift
        coerce = True

PosOrderLine.validate(df, lazy=True)   # lazy=True collects all failures, not just the first

The 5-year lens

A contract is a conversation tool. Send the vendor or source team the profile and the contract and ask "is this what you intend?". You'll learn things no data shows: "the price is tax-inclusive in Quebec stores", "channel app includes third-party delivery from March". Write those answers into the contract as comments. That's how five-year people know things juniors don't: they asked.

4. What should a violation do?

Severity	Example	Action
Blocker	Missing required column, wrong file entirely	Fail the load, alert, keep yesterday's tables
Row-level	Bad date in 3 rows, unknown menu item	Quarantine those rows, load the rest, report counts
Warning	Volume 40% below normal, new category value	Load, flag on the DQ report, check tomorrow

Most teams get this wrong in one of two directions: fail everything (reports never arrive) or warn on everything (nobody reads warnings). Write the table down per source.

Lab

pip install polars duckdb pandera[polars]. Profile the QSR POS files with the checklist and DuckDB. Write docs/profiles/qsr_pos.md.
Find and record: the grain, a key that works, the 00/00/0000 dates, duplicated batches, the item_note column, the encoding problem in TacoTrail files (look at Jalape?o).
Write contracts/qsr_pos.py with the pandera model. Validate one clean file (passes) and one with bad dates (fails with a readable report).
Write the severity table for QSR POS in the profile doc.
Ask Claude Code to profile the hotel PMS files with the same checklist. Compare its answers with yours and note anything it got wrong or missed. (It's practice in reviewing AI output.)

Ship

Start a new repo qsr-clean. Commit docs: profile and contract for QSR POS.