Cleaning 1.99M rows without losing the signal

When we started Ledgr for the MCCIA AI Hackathon, the dataset looked clean. 1.99 million rows of FMCG distribution records across 320 outlets and 40 SKUs. Sales figures, dates, outlet IDs. Nothing obviously broken.

Then we plotted daily sales per SKU and found something like 60% of cells were zero.

True zero vs. missing data

A zero in a sales table means one of two completely different things:

• The outlet was open, stocked, and genuinely sold none of that SKU that day. A real observation. Demand data.
• The outlet was closed, the SKU wasn't stocked, or the row simply never got written. Absence of data. Not demand.

Train a forecaster without separating those and you teach it that demand is near-zero everywhere. Your MAPE looks fine on paper because you're predicting a lot of zeros correctly, and your reorder engine confidently recommends nothing.

The Medallion split

We used a three-layer structure and gave each layer exactly one job.

Bronze ingests raw records and does nothing clever. Structure, type, index, make it queryable in SQL. No judgement calls. If Bronze is wrong you've lost the ability to audit anything downstream.

Silver is where the judgement lives. This is where we resolved the zero problem.

Gold holds aggregated views the dashboard reads directly. No application-layer aggregation, no recomputing the same rollup in six places.

What it bought us

Forecasting on the cleaned set with LightGBM landed at 10.4% MAPE across all SKUs with 95% confidence intervals from residual-based estimation. More usefully, the reorder engine could distinguish "this SKU doesn't sell here" from "we have no idea whether this SKU sells here" — the difference between a sane recommendation and a guess.

The pipeline surfaced ₹19.1M of revenue at risk from stockouts and generated ₹13.9M in reorder recommendations, with zero MOQ, lead-time or shelf-life violations.

The takeaway

The modelling was the easy part. LightGBM is four lines. The work — the part that decided whether the numbers meant anything — was two days of arguing about what a zero meant.