PricePoint Dynamics.
Nine and a half million scraped UK supermarket prices, resolved into one product catalogue and turned into a forecasting model, an explainability layer, and a market-structure dashboard, rebuilt from a broken prototype into something reproducible.
Aldi's median sits roughly half of ASDA's, a structural gap the forecasting model learns directly from data.
Price rows analysed
791MB, 5 retailer CSVs, 95 days
Forecast error (MAE)
R² 0.965 on 444K held-out rows
Canonical products
resolved from ~114K raw names
Monthly hosting cost
verified against live pricing pages
Real tests
unit · API-contract · pipeline-integration
CI jobs / push
lint, unit, API, pipeline, frontend e2e
ADRs written
the decisions that shaped the rebuild
Feature-eng. speedup
pandas → Polars, 10% sample
Five retailers, one broken assumption.
Tesco, Sainsbury's, ASDA, Morrisons and Aldi all reprice against each other constantly. Seeing that in data hits a naming problem before it hits a statistics problem: "Tesco Finest British Beef Mince 5% Fat 500g" and "ASDA Extra Special British Beef Steak Mince 5% Fat" are the same category of product, and nothing in a raw scrape says so. Until that's resolved, there's no dataset, just five retailers' worth of unlinked noise.
The second problem only showed up on inspection: a prior Streamlit prototype existed, and going through it line by line, not trusting its README, turned up a long list of things that quietly didn't work. That audit, below, is where most of this project's real engineering value sits.
Raw rows
791MB across 5 retailer CSVs
Date range
Jan 9 – Apr 13, 2024
Unique raw names
before semantic resolution
Duplicate rate
at (product, retailer, date), from different pack sizes clustering together
What the prototype actually got wrong.
The core analysis code was reasonable. Everything wrapped around it had quietly stopped being true, found by reading the code, not by trusting the docs.
2 of 3 Pandera schemas defined, never called
Product matching and feature engineering, the two most complex pipeline stages, had zero validation. A schema drift there would have passed silently.
Dashboard read files no script produced
"canonical_products_lite.parquet" and "feature_data_lite.parquet" were hand-generated once, uploaded to Google Drive, and never documented. A fresh clone could not reproduce what the dashboard read.
~30 of ~40 model inputs were fabricated
The old predictor page invented most of its own features: "price_rol_min_7d = price_rol_mean_7d * 0.9" is a made-up constant, not a real value. The "prediction" had little to do with the actual data.
Headline stats were hardcoded strings
"£0.14", "9.5 Million", "67,000+": not computed from anything. They would silently drift on the next retrain.
Feature engineering: 12+ Python passes over 9.5M rows
groupby().transform(lambda x: x.rolling(...)) ran once per statistic per window size, making it the single slowest stage in the entire pipeline.
Price-leadership: a triple-nested Python loop
One pandas.Series.corr() call per retailer pair per lag: 300,000 individual scalar calls at default config.
No lockfile, two drifted requirements.txt files
No installable package, and the two requirement files had already diverged on their own numpy pin.
One pipeline, a static-first frontend.
9 pipeline stages produce everything the dashboard needs. Only one user action, an arbitrary predict request, ever touches a live backend.
$ python run.py ingest match features train marts precompute web-artifacts
Ingest
Clean + validate 5 raw retailer CSVs (791MB, 9.5M rows) against RAW_DATA_SCHEMA.
Match
Sentence-BERT embeddings → FAISS → bounded-degree mutual-kNN clustering into 68,596 canonical products.
Features
Temporal (rolling/lag), leave-one-out competitive, and cyclical date features, via Polars .over() expressions.
Train
LightGBM, MAE objective, time-series split, with the final 7 days held out chronologically.
Marts
DuckDB analytics marts over Parquet: fact_price_daily, dim_product, dim_retailer.
Precompute
SHAP (8K-sample TreeExplainer), HHI concentration, price-leadership cross-correlation.
Web artifacts
Export page-shaped JSON (committed) and Parquet (R2) for the frontend to read directly.
Next.js static export on Vercel. Reads committed JSON / R2 Parquet directly, with zero backend round-trip.
FastAPI on Cloud Run. Only called when the Predictor page's "Predict" button is actually clicked.
Aldi is the price floor, not subtly.
Real per-retailer quartiles, computed once by the pipeline and shipped as a static artifact, not hardcoded strings.
Aldi's entire interquartile range sits below every other retailer's 25th percentile, not a marginal difference, a structurally separate price tier.
One clustering idea, two failed attempts.
Matching ~114,000 raw product names down to a canonical set looks like a plain embedding-similarity problem. Two attempts at that failed catastrophically before the actual fix.
One generic phrase becomes a hub, chaining everything together
An edge only exists if the match is genuinely mutual
Attempt 1: Greedy top-1 nearest neighbour
non-transitive resultA matches B does not imply B matches A. Order-dependent, and wrong in a way that is hard to detect downstream.
Attempt 2: Unbounded connected components, threshold 0.85
96% % names in one clusterFAISS range_search fixed transitivity but gave every node unbounded degree. Generic short phrases ("0 fat greek style") became hub nodes chaining unrelated products together.
Attempt 3: Same approach, threshold raised to 0.90
60%+ % names in one clusterChaining gets worse, not better, with corpus scale: a threshold that looks safe on a small sample can still fail catastrophically at full size.
Final: Bounded-degree mutual-kNN (threshold 0.95, k=5)
68,596 canonical productsAn edge only exists if each name is genuinely among the other's top-5 nearest neighbours, bounding how many names any single hub can pull in, while still resolving real multi-hop A-B-C chains.
Closing the target-leakage door.
A "how does this price compare to today's market average" feature sounds harmless, until the row's own price is part of that average. The model was, in effect, handed a feature partly derived from the exact value it was trying to predict.
The fix: exclude the row's own price before averaging the rest, and persist only the derived delta, never the raw aggregate, which would let the target be reconstructed by simple subtraction regardless of how the aggregate was computed.
1# Leave-one-out market average: the row's own2# price is excluded before averaging the rest.3other_count = group_count - 14market_avg_others = (5 (group_sum - df["prices"]) / other_count6)7 8# Only the derived delta is persisted, never9# the raw aggregate, which would let the target10# be reconstructed by simple subtraction.11df["price_vs_market_avg"] = (12 df["prices"] - market_avg_others13)LightGBM, trained on 5.15M rows.
Time-series split: the final 7 days held out chronologically, since a random split would leak future rolling-window information into training.
MAE
average error, ~5–10% on a typical £1.50–£3 item
RMSE
penalises rare large misses
R²
high in part because grocery prices are sticky
Train / test rows
final 7 days held out chronologically
What is the model actually picking up on?
SHAP values computed against the trained model, grouped into human-scale categories.
The model's explanatory weight sits overwhelmingly on price persistence, not exotic signal, independent confirmation of the honest R² framing above, computed directly against the artifact rather than taken from the project's own README.
Who leads, and who follows?
Cross-correlation over price series, vectorized across every sampled product pair at once, not a single pandas call per pair.
A sharp one-day dip in mid-January (0.112 vs. a typical ~0.19) aside, the market settles into a stable equilibrium: dispersion neither escalates into a price war nor collapses.
Aldi → everyone: +3 to +4 days. Among the Big Four (Tesco, Sainsbury's, Morrisons, ASDA), lags are far shorter (~1–2 days) and reciprocal, a faster, tighter competitive cluster.
Aldi is the price floor, not subtly
Median £1.49 vs. £3.00 at ASDA, and similar-or-higher at the other three. Directly visible in the raw per-retailer quartiles below.
Aldi sets the pace; the Big Four follow, days later
Aldi leads every other retailer by 3–4 days. Among the Big Four themselves, relationships are far faster (~1–2 days) and reciprocal: a tight, reactive cluster distinct from Aldi's slower-followed leadership.
Tesco leads, Sainsbury's follows, consistently
A measurable, one-directional lag, not simultaneous movement. Not visible by eyeballing a two-line chart. It only emerges from running the lag correlation across enough product pairs to separate signal from noise.
Measured, not estimated.
Every plot below is a real matplotlib figure this project's own benchmark scripts generated, including the one where the rewrite doesn't win on every axis.
Feature-engineering & ingestion: speedup
Log-scale speedup, pandas → Polars/vectorized rewrite. Feature engineering: 151.7× on a 10% sample.
Feature-engineering & ingestion: memory
The honest trade-off: at full 9.5M-row scale, Polars' peak RSS (4,355MB) exceeds the pandas baseline.
DuckDB marts vs. pandas-in-Streamlit: speedup
Basket cost lookup: 15.7×. Market overview: 4.6×, bounded by the query itself being a full-scan.
DuckDB marts vs. pandas-in-Streamlit: memory
Unambiguously better here: basket lookup peak RSS drops from 1,476MB to 243MB.
Every system is a series of trade-offs.
10 real decisions from this rebuild: what was chosen, why, what it cost, and what happened.
Plain FAISS range_search connected-components clustering collapsed 96% of 114K product names into one cluster at threshold 0.85. Raising it to 0.90 still left 60%+ in one supercluster.
ReasoningChaining happens because a single hub node can have unbounded degree. Bounding each node's degree via mutual-k-NN caps how many names any one hub can pull in, while still resolving genuine multi-hop A-B-C chains.
68,596 canonical products, close to the project's historical reference figure (67,341), guarded by a regression test built around the exact hub-chaining failure mode.
Nothing shipped right the first time.
Five things that broke, or would have shipped wrong, caught during this rebuild.
A float rounding error broke the vectorized correlation rewrite
The original "skip products with no price variation" check used variance == 0. Vectorizing it, 90+ identical float rows accumulated rounding error into a spuriously nonzero variance (~7.98e-31), producing arbitrary near-ties on which lag "wins."
An empirically-derived 1e-6 threshold, chosen by checking real variance values on this dataset, not a token change.
A CORS misconfiguration broke deployment, for real
"Works locally, fails in prod": a real incident during deployment, not a hypothetical.
An explicit CORS allow-list, never "*", now covered by a dedicated regression test rather than just fixed and forgotten.
The rolling/groupby stage repeatedly froze a 15GB dev machine
Feature engineering and training's intermediate DataFrames were large enough, held long enough, to exhaust memory on a real development machine, not a theoretical concern.
downcast_dtypes() + explicit collect_garbage() + log_memory() checkpoints at every major stage boundary; large intermediates (e.g. ~467MB of embeddings) explicitly deleted the moment they're no longer needed.
The API was over-scoped before it was cut down
~9 endpoints were originally planned.
Going page-by-page through the actual dashboard found 6 were replaceable by a static file the frontend could just read. The API shipped with 3.
The Polars rewrite is not a strictly-better trade at full scale
At the full 9.5M-row feature-engineering stage, peak memory went up (4,355MB), not down, versus the pandas baseline.
No fix applied. The benchmark plots show this plainly. Speed was the actual constraint being solved for; the memory trade-off is disclosed, not hidden.
£0 a month, stated plainly.
Vercel static export + Cloud Run (request-processing billing, scale-to-zero) + Cloudflare R2 (free egress), independently re-checked against each provider's live pricing page rather than trusted from an earlier note. The write-up says explicitly what couldn't be confirmed, too.
- ·Training window is 95 days (Jan–Apr 2024): no data exists to learn full-year seasonal effects like Christmas pricing.
- ·Promotional price drops are not modeled explicitly; the model treats them as noise.
- ·Retraining is manual (python run.py train): no scheduled job watches for drift yet.
- ·price_vs_market_avg is contemporaneous with the target: useful for describing today's competitive position, not for forecasting moves that precede a market shift.
The audit was the real project.
- The legacy audit, not the model, was where most of the real engineering value was: 7 concrete defects (unvalidated schemas, fabricated model inputs, hardcoded stats, undocumented artifacts) found by reading the actual code, not the README.
- Two clustering failures (96% and 60%+ chaining) came before the working bounded-degree mutual-kNN approach: the interesting engineering story is the iteration, not a single clean insight.
- A high R² (0.965) is not automatically a good story. Grocery prices are sticky, and the honest framing (verified independently via real SHAP values: ~90% of the model's explanatory weight sits on recent price history) matters more than the headline number.
- A structural fix (one shared feature-vector function for train and serve) closes a bug class permanently; a discipline-based fix ("be more careful") only delays the next drift.