Why I Built This
After nearly 15 years working in enterprise supply chain and data engineering — most recently as a Staff Engineer at a large national grocery retailer — I have seen firsthand how retail organizations struggle with one persistent problem: store-level demand forecasting at scale.
The consequences of getting it wrong are real. Overstock leads to waste, especially in perishable categories like meat, dairy, and produce. Understock leads to empty shelves, missed sales, and frustrated customers. Most enterprise retailers have forecasting systems, but they are often built on legacy infrastructure that is slow to adapt, difficult to extend, and opaque to the business users who rely on them.
I wanted to explore whether modern cloud-native tooling — specifically Databricks — could make it meaningfully easier to build a forecasting system that was not just technically sound, but operationally useful: one that translates ML forecasts directly into order recommendations, flags waste and stockout risks, and gives buyers a natural language interface to ask questions without writing SQL.
So I built one from scratch. This article documents what I built, how I built it, what broke along the way, and what I learned.
The Goal
Build a fully automated prototype retail order forecasting pipeline on Databricks that:
- Ingests and cleans synthetic retail data (sales, inventory, deliveries, promotions)
- Engineers features for ML training using Medallion Architecture
- Trains per-category demand forecasting models with experiment tracking
- Translates forecasts into actionable order recommendations with risk labels
- Surfaces insights through an operational dashboard and natural language analytics
- Runs automatically on a daily schedule
Scope: 10 stores, 20 products, 52 weeks of synthetic data — small enough to build fast, structured enough to reflect real enterprise patterns.
Architecture Overview: Medallion + ML + Decision Engine
The system follows the Medallion Architecture — an industry-standard data engineering pattern that organizes data into progressively refined layers — extended with an ML layer, a decision engine, and operational outputs.
Raw CSV Files
↓
Bronze Layer → Raw Delta tables (never modified)
↓
Silver Layer → Cleaned, validated, quality-flagged data
↓
Gold Layer → Feature-engineered ML-ready + business-ready tables
↓
ML Models → LightGBM per category, tracked with MLflow
↓
Decision Engine → Order recommendations, risk labels, financial estimates
↓
Dashboard + Genie Space + Workflow AutomationEach layer has a distinct contract: Bronze preserves raw data exactly as received. Silver fixes quality issues visibly without silently dropping records. Gold produces model-ready and business-consumable tables. This separation makes the pipeline auditable, debuggable, and extensible.
Stage 1: Synthetic Data Generation
Since this was a prototype built on a trial Databricks environment, I generated all input data synthetically using Python and Pandas — no proprietary or real-world data was used.
Seven CSV datasets were generated and stored in a Databricks Volume:
File Purpose
| sales.csv | 10,400 rows — 10 stores × 20 SKUs × 52 weeks |
| inventory.csv | Perpetual stock on hand plus in-transit quantities |
| persistent_stock.csv | Physical counts every 4 weeks |
| deliveries.csv | Supplier delivery records with dates and quantities |
| stores.csv | Store reference data |
| products.csv | Product catalogue with shelf life and supplier lead times |
| promotions.csv | Promotional calendar |
One important consistency challenge: the stock_on_hand value in inventory.csv and the perpetual_stock value in persistent_stock.csv needed to align across datasets. I solved this by using a shared lookup dictionary during generation — a small but critical detail that would cause downstream join failures if ignored.
First lesson learned: Data consistency between input files is not automatic. Even in synthetic data generation, you need to explicitly enforce referential integrity across datasets.
Stage 2: Bronze Layer — Raw Ingestion with Delta
The Bronze layer reads each CSV using Apache Spark with schema inference and saves each file as a Delta table in the bronze schema.
Two design decisions here that matter in production contexts:
1. Added an ingestion timestamp at write time:
python
df.withColumn("_ingested_at", current_timestamp())This creates a simple audit trail — you always know when data arrived, which is essential for debugging pipeline failures.
2. Used schema-safe overwrite:
python
.mode("overwrite").option("overwriteSchema", "true")During iterative development, schema changes between runs are common. Without overwriteSchema, Databricks will throw a DELTA_METADATA_MISMATCH error every time the schema evolves. This single option makes the notebook safely re-runnable.
Key concept: Delta tables are not just storage — they support versioning, ACID transactions, and time travel. Bronze equals raw data: ingest it, timestamp it, never modify it.
Stage 3: Silver Layer — Clean Visibly, Never Silently
The Silver layer is where data quality work happens. The critical design principle here: never silently drop bad records. Always flag them.
For each source table, I applied targeted cleaning:
- Sales: Deduplicated on store + SKU + week; fixed negative sales values; added a stockout_flag; joined store and product reference data; added calendar features (week of year, month, quarter)
- Inventory: Fixed negative stock values; joined perpetual stock with physical counts; calculated days_of_cover and shrinkage_pct
- Deliveries: Calculated days_late, fill_rate_pct, and is_on_time per delivery record
- Products: Added perishability_tier based on shelf life (critical for downstream waste risk scoring)
- Promotions: Added promo_duration_days and validated date ranges for consistency
Rather than dropping rows with quality issues, each table gets a _quality_flag column marking records as clean, warning, or error. This means downstream teams can always trace data quality issues back to the source — an essential capability in any production data pipeline.
Key concept: The Silver rule is auditability. If something goes wrong in ML training or order recommendations, you need to be able to trace it back to the raw data. Silent drops make that impossible.
Stage 4: Gold Layer — Feature Engineering for ML and Business
The Gold layer is where raw cleaned data becomes model-ready features. This is the most complex and highest-value layer in the pipeline.
The primary ML training table — gold.weekly_sales_features — contains 35+ features per store, SKU, and week, including:
- Temporal features: Rolling 4-week and 13-week averages, year-over-year growth, seasonality index
- Demand volatility: Rolling standard deviation of weekly sales
- Trend features: 4-week vs. 13-week trend comparison (short-term vs. long-term momentum)
- Promotion features: Whether a promotion was active, promotion duration
- Inventory features: Days of cover, stockout flag from Silver layer
- Holiday flags: Week-level holiday indicators
In addition to the ML training table, the Gold layer produces two business-consumption tables:
- gold.inventory_health — latest stock position, waste risk score, and stockout risk label per store/SKU
- gold.supplier_performance — OTIF %, fill rate %, lead-time mean and p90, reliability score per supplier/SKU
Key concept: Window functions are essential here. Rolling averages, volatility calculations, and trend features all require looking back at prior rows — window functions let you do this entirely in SQL without leaving the table. Gold = model-ready and business-readable data in one layer.
Stage 5: ML Forecasting — LightGBM with MLflow Tracking
With 35+ features per store/SKU/week available in the Gold layer, I trained one LightGBM model per product category.
Why LightGBM? It handles tabular data well, trains fast, and works naturally with the feature set I had — including categorical columns (store, SKU, region) encoded with LabelEncoder.
The most important decision: time-based train/test split.
python
# First 80% of weeks for training, last 20% for evaluation
cutoff_week = sorted_weeks[int(len(sorted_weeks) * 0.8)]
train = df[df['week'] <= cutoff_week]
test = df[df['week'] > cutoff_week]
In forecasting, a random train/test split causes data leakage — the model sees future data during training. Always split by time.
Model results across 8 category models:
Category MAPE
| Meat | 10.5% |
| Snacks | 10.8% |
| Produce | 11.6% |
| Dairy | 14.8% |
| Average | 12.3% |
A 12.3% average MAPE on synthetic data is a reasonable baseline. In production retail forecasting, MAPE targets typically range from 15–25% for perishable categories, so this prototype is in a credible range.
Quantile forecasting for uncertainty: Rather than a single point forecast, I generated p10/p50/p90 forecasts using rolling standard deviation as the uncertainty measure. This is important for order recommendations — you want to know not just the expected demand, but the range of outcomes.
All models were registered in Unity Catalog Model Registry with MLflow signatures. MLflow tracks every experiment run so you can compare models, roll back to prior versions, and audit exactly which model version produced which recommendations.
Key concept: Never use random splits in time-series forecasting. And always track experiments with MLflow — without it, you cannot reproduce or audit your results.
Stage 6: Decision Engine — From Forecast to Action
A forecast has no business value until it drives a decision. The Decision Engine translates ML forecasts into actionable order recommendations using a standard inventory replenishment formula:
Order Qty = Demand over lead time
+ Safety Stock
- Stock on Hand
- In TransitKey design decisions:
- Service level by perishability: High-tier perishable items use a 90% service level (tighter — less buffer to avoid waste); other items use 95% service level (more buffer to avoid stockouts)
- Supplier reliability adjustment: Order quantity is adjusted upward based on the supplier's historical fill rate from gold.supplier_performance
- Risk-based order status labels:
- URGENT — immediate action required
- PENDING_APPROVAL — needs review before placing
- REVIEW_BEFORE_ORDERING — marginal case
- NO_ORDER_NEEDED — sufficient stock
The output table gold.order_recommendations contains order quantity, waste risk score, stockout risk score, and financial estimates (unit cost × order quantity) for each store/SKU combination.
Key concept: The decision engine is where supply chain domain knowledge meets ML output. The formula, service levels, and risk labels all encode business rules — and these rules matter as much as model accuracy.
Stage 7: Dashboard — Making the Forecast Visible
I built a 9-tile operational dashboard using Databricks AI/BI Dashboards:
- 4 KPI counters: total orders to place, total order value, urgent orders, waste risk count
- Order status breakdown (bar chart)
- Order units by category (bar chart)
- Waste risk distribution by category (pie chart)
- Inventory cover by category (bar chart)
- Urgent orders detail table (full width)
The dashboard is connected directly to the Gold layer tables — it refreshes automatically when the daily workflow runs.
Stage 8: Genie Space — Natural Language Analytics
One of the most practically useful additions was the Databricks Genie Space — an AI-powered natural language interface over the Gold tables.
I connected 5 Gold tables and added detailed context instructions explaining business terminology, table descriptions, and example questions. This enables buyers and operations managers to ask questions like:
- "Which stores have urgent orders today?"
- "Show me high waste risk Dairy SKUs"
- "Which supplier has the lowest fill rate this month?"
Without Genie, answering these questions requires either a SQL analyst or pre-built dashboard tiles for every possible question. Genie makes the data accessible to non-technical users directly.
Key concept: Curated Gold tables with good context instructions are the foundation of useful AI analytics. Garbage in, garbage out — Genie is only as good as the data and context you give it.
Stage 9: Workflow Automation — Making It Repeatable
The final stage ties everything together into an automated daily pipeline using Databricks Workflows:
01_bronze → 02_silver → 03_gold → 04_ml_model → 05_decision
5 tasks chained in dependency order, scheduled at 06:00 AM daily, with email notifications on success and failure. A manual test run confirmed all 5 tasks passed end to end.
This is the difference between a notebook experiment and an operational system. Automation creates a repeatable pipeline that runs without human intervention — and fails loudly when something goes wrong.
Errors I Hit and How I Fixed Them
These are real errors from building the prototype — worth documenting because they will likely affect anyone following a similar path:
Error Root Cause Fix
| DELTA_METADATA_MISMATCH | Schema changed between runs | Add option("overwriteSchema", "true") |
| ModuleNotFoundError: lightgbm | Not pre-installed on serverless compute | Add %pip install lightgbm at top of notebook |
| ValueError: pandas dtypes | Non-numeric columns in LightGBM features | Use pd.to_numeric(...).astype(float) |
| MlflowException: signature required | Unity Catalog requires model schema | Call infer_signature() before log_model() |
| RestException: directory not found | MLflow experiment parent path missing | Use WorkspaceClient().workspace.mkdirs() |
| Window function in WHERE clause | SQL restriction | Move window function into a CTE first |
| timedelta numpy int error | NumPy vs Python int type mismatch | Wrap with int() |
What It Would Take to Go to Production
This prototype was built on synthetic data in a trial environment. Taking it to production at enterprise scale would require:
Step Detail
| Real data | Connect cloud data warehouse (e.g., GCP BigQuery) using Databricks connector |
| Scale | Support 2,000+ stores — the pipeline architecture scales without code changes |
| Governance | Configure Unity Catalog row- and column-level security |
| Model monitoring | Add Lakehouse Monitoring for forecast drift detection |
| ERP integration | Push order_recommendations to procurement systems via REST API |
| CI/CD | Set up Databricks Asset Bundles for dev/staging/production promotion |
The Medallion Architecture and modular notebook design mean that each of these steps can be added incrementally without restructuring the pipeline.
Key Takeaways
- Medallion Architecture works. The Bronze → Silver → Gold separation is not just theoretical — it makes the pipeline debuggable, auditable, and extensible in practice.
- Always split by time in forecasting. Random splits cause data leakage and will produce misleadingly good training metrics that fall apart in production.
- The decision engine is as important as the model. A 12% MAPE model produces no business value without a clear translation from forecast to order recommendation to risk label.
- Flag quality issues visibly. Never silently drop bad records. A _quality_flag column in Silver saves enormous debugging time downstream.
- MLflow tracking is non-negotiable. Without experiment tracking, you cannot reproduce, compare, or audit your model results — especially important when models are making operational recommendations.
- Genie Space lowers the barrier to insight. With well-curated Gold tables and good context instructions, non-technical users can get answers without SQL — which changes how operations teams interact with forecasting data.
Final Thoughts
Building this prototype in a single focused session on Databricks confirmed something I had suspected from years of enterprise supply chain work: the hardest problems in retail forecasting are not the ML algorithms — they are the data engineering foundations that make those algorithms trustworthy and operationally useful.
The Medallion Architecture, MLflow tracking, and the Decision Engine layer are what separate a notebook experiment from a system that a business can actually rely on. Getting those right matters more than squeezing another percentage point of MAPE.
If you are building something similar — whether for retail, logistics, or any demand-driven supply chain — I hope the architecture, the errors, and the lessons documented here save you some of the discovery time I spent.
