Analytics engineering · Data quality

What 541,909 real invoices taught me about honest data pipelines

A quarter of the rows had no customer id. What you do with those rows is the project — not the SQL, and not the model.

The UCI Online Retail dataset is 541,909 line items from a real UK online retailer over about a year. It is a good dataset to build on for exactly the reason it is an annoying one to build on: roughly a quarter of the rows have no customer id.

Guest checkouts. Cancellations. Prices that make no sense. Nothing about it is clean, and every decision you make about those rows changes the numbers you end up reporting. That is the whole project, really — not the SQL, and not the model, but what you do with the rows that do not fit.

The quarter of the data that does not cooperate

134,737 rows — 24.9% — cannot be attributed to a customer. You have three options and they are not equally honest.

You can drop them silently, and report revenue that is missing a quarter of its transactions without saying so. You can invent surrogate ids, and produce a cohort analysis where a million one-time strangers look like a retention catastrophe. Or you can quarantine them: keep them, count them, exclude them from customer-level models, and publish the exclusion rate as a number people can see.

I quarantined them, and the quarantine rate is tracked as a bounded service level rather than buried. If it moves, the pipeline says so.

Layered SQL, because marts should be boring

The pipeline is staging → intermediate → marts, run on DuckDB locally and written so the modelling logic ports to BigQuery without a rewrite. Ten models, 100% pipeline success, about ten milliseconds at the median.

The marts exist to answer questions somebody would actually ask, and the answers are specific because the data is real:

That last row is my favourite. A product with a 100% return rate looks like a data bug until you look: they are cancelled bulk orders, which are real events that belong in the data and would quietly distort product performance if you averaged over them without noticing.

A feature store that refuses to look forward

Features are declared in YAML, not written as pipeline code:

- name: revenue_last_90d
  aggregation: sum
  column: line_revenue
  window_days: 90

The framework turns that into point-in-time-correct SQL, backfills it across cut-offs, and attaches the forward label. The rule it enforces is simple and it is the one that matters: features may read only invoice_ts <= as_of, and the label — did this customer order again in the next 90 days? — may read only the window after as_of.

A build-time assertion fails the run on any violation. Not a code review convention. A failing build.

The model, and its actual margin

A walk-forward repeat-purchase model reaches 0.731 ROC-AUC against a 0.684 baseline. That is a real improvement of about five points, and it is worth being precise about what five points buys: better ordering of a re-engagement list, not a different business.

The scores are written back into the warehouse next to the RFM segments, which is the part that makes it useful. A propensity score sitting in a notebook is a number; a propensity score joined to an “At Risk” segment in a mart is a call list.

Quality as data, not as vibes

Row counts, freshness, nulls, uniqueness, ranges, reconciliation, referential integrity — all declared in YAML, all written to an append-only audit table so quality can be trended rather than merely checked. A critical breach fails CI.

The entire thing runs end to end in about fifteen seconds, of which fourteen are downloading the data. Which is the point: if reproducing the whole pipeline is cheap, you run it on every push, and the dashboard shows you a real execution instead of a committed snapshot from the day it worked.

What I would keep

The quarantine table, the build-time leakage assertion, and the habit of publishing the exclusion rate. Those three are what let me put a revenue number on a page and still be able to explain exactly which rows are behind it.