In progress · data through September 30, 2026
ShelfCase
A spirits supplier wants to know why its Iowa business is shrinking and what its sales team should do about it. ShelfCase is the engagement I'd run on Databricks to answer that, built on real public data: every bottle a licensed Iowa store bought from the state since 2024.
A simulated engagement. Diageo Americas is featured as if it were the customer; ShelfCase is not affiliated with it, any retailer, or the State of Iowa.
- real order lines, 2024 to now
- duplicate rows caught in the 2025 file
- rows with no list price, repaired
- requirement tests passing
The question
Iowa is a control state, so every case a store buys passes through the state's wholesale operation, and the state publishes each one. For a supplier, that is store-level sell-in for an entire state. Read for Diageo Americas, the first thing it shows is a puzzle: from 2024 to 2026, January–September sell-in fell 8.7% ($64.7M to $59.1M) while the number of stores ordering rose 4.5%. More stores, less per store.
Is that a few big accounts, a few brands, stores that quietly stopped carrying the core range, competitors taking share, or price? Every phase below exists to answer part of it.
Diageo Americas in Iowa, January–September, indexed to 2024 = 100
Show as a table
| Year | Sell-in | Stores ordering |
|---|---|---|
| 2024 | $64.7M | 2,100 |
| 2025 | $59.9M | 2,156 |
| 2026 | $59.1M | 2,195 |
Who it's for
VP Sales, Iowa
“Why did our sales at Casey's drop this month, and what do my reps do Monday?”
Account-recovery agents
Demand planning
“How many cases will each chain order per item over the next 8 weeks?”
Many-models forecasting
Field sales reps
“Which stores stopped ordering products they used to carry?”
Lapse alerts
Category and revenue growth
“Where are competitors gaining share, and did price changes do it?”
Share and price metrics
Sales analysts
“Can I ask anything about our Iowa business without filing a ticket, and trust the answer?”
Context layer and Genie
What the raw data gets wrong
Before any agent can be trusted, the numbers underneath have to be right. I profiled all 7,326,440 rows first, and each problem became a requirement with a test.
429,996 rows appear twice
15% of the 2025 export repeats whole rows, which silently inflates every total. Silver drops exact duplicates by row hash.
There's no natural key
Distinct lines share the same invoice and item, so deduplicating on those would delete real orders. Only identical rows are removed.
3.8M rows have no list price
Most of 2024–2025 is missing the state's shelf price. Unit price falls back to dollars ÷ bottles, and the row says which one it used.
The types drift by year
Prices are text in 2024–2025 and numbers in 2026; liters flip between integer and decimal within a year. One schema, cast per year.
Billed price ≠ list price
About 275,000 late-2025 lines were billed above the listed price, mostly by 1–8%. The cause isn't established, so both prices are kept and the gap is reported, not hidden.
Every download looks new
The state's portal rebuilds the ZIP on each request, so hashing the file would always say "changed". Change detection fingerprints the CSV inside.
A feed that misbehaves on purpose
The state refreshes a full-year file monthly, so each download is a snapshot that can rewrite earlier rows. To test the incremental path, a replayer splits 2026 into daily files and feeds one simulated day per hour, with faults and business incidents planted and logged to a ground-truth file. On a real replay of 34 order days, every planted event showed up in silver exactly as expected.
Planted feed faults
- A late file. A day's orders arrive after the next day's. Silver still picks them up.
- A resend with corrections. One invoice line comes back as 300 bottles instead of 360. It replaces the original line; the invoice's other 8 lines stay put.
- An added column. The schema grows mid-stream. Silver conforms every file to one schema, so the load keeps going instead of breaking.
- A missing day. One day never arrives. The ground-truth log records it, so a dip from missing data can be told apart from a real one.
Planted business incidents
- Lapsed distribution. A store's orders of one item are removed for several weeks.
- A price increase. An item's list price goes up 6%.
- A competitor surge. Sazerac's orders at Casey's go from $43.7k to $62.4k per order day.
These are the cases the account-recovery agents will be graded on: did they find the planted cause?
The obvious alert is wrong 7 times in 10
Reps want a weekly list of stores that stopped ordering items they used to carry. The obvious rule flags gaps that are unusually long for that store. Graded in hindsight on 233,653 store × item series, its top 50 alerts each week were right only 30% of the time, because the biggest gaps in sell-in data heal on their own.
Precision of the top 50 alerts per week · 35 held-out weeks of 2026 · stores still ordering
Flag unusually long gapsthe obvious rule
0.29Longest gap firsta simple ranking
0.81Trained classifier12 features, trained on 2024–2025
0.82
A trained classifier fixes the rule, but it barely beats simply ranking by gap length. So the plan changed: the list is now ordered by expected recovered value (chance of a real lapse × the store's weekly value), and a separate alert catches stores that went quiet on everything.
Where it stands
Each phase starts with a test written first and ends with its measurements logged. The headline result comes next: the same 40 supplier questions asked against raw tables, the governed context layer and Genie, with SQL-computed answers. I'll publish the number whatever it turns out to be.
- BuiltScopingStakeholders, current-state pains and success metrics, fixed before any code
- BuiltFeedsMonthly snapshot fetcher and a daily replay with planted faults and incidents
- BuiltLakehouseSilver and gold transforms, chain mapping, price history as SCD Type 2; Databricks pipelines next
- MeasuredLapse alertsSpike measured on real history; the trained model is next
- NextContext layerKPIs compiled to Unity Catalog metric views, a glossary, domains and a Genie agent
- NextContext-lift benchmark40 supplier questions: raw tables vs. governed context vs. Genie, scored in MLflow
- PlannedForecastingDatabricks' many-models accelerator at item × chain weekly, promoted only if it beats seasonal-naive
- PlannedAccount-recovery agentsA supervisor that investigates a drop and proposes rep tasks for a person to approve
- PlannedMCP serverThe same governed definitions served to any agent
- PlannedPackagingOne-command bundle install, CI/CD and a handoff runbook
Built with
- Python 3.12
- PySpark 4.1
- DuckDB
- pytest
- ruff + mypy
- gradient-boosted classifier
Lands on Databricks
- Unity Catalog metric views
- Lakeflow Declarative Pipelines
- Genie
- MLflow
- Lakebase
- Databricks Apps
- Many-models forecasting
Data: State of Iowa, Iowa Liquor Sales (CC BY 4.0), through September 30, 2026. Duplicate rows removed. Planted faults and incidents exist only in the replay and are never mixed into the reported figures above.