Building an End-to-End Store Order Forecasting System on Databricks: From Zero to Automated Pipeline

Aafaq_Mohammed — Thu, 27 Aug 2026 20:24:51 GMT

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 Automation

Each 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 Transit

Key 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.

Re: Building an End-to-End Store Order Forecasting System on Databricks: From Zero to Automated Pipe

davidwarner344 — Fri, 28 Aug 2026 09:50:30 GMT

Really solid project—especially the focus on turning ML forecasts into practical order recommendations. The data quality and decision-engine approach makes it genuinely useful.

topic Building an End-to-End Store Order Forecasting System on Databricks: From Zero to Automated Pipeline in Community Articles

Building an End-to-End Store Order Forecasting System on Databricks: From Zero to Automated Pipeline

Why I Built This

The Goal

Architecture Overview: Medallion + ML + Decision Engine

Stage 1: Synthetic Data Generation

Stage 2: Bronze Layer — Raw Ingestion with Delta

Stage 3: Silver Layer — Clean Visibly, Never Silently

Stage 4: Gold Layer — Feature Engineering for ML and Business

Stage 5: ML Forecasting — LightGBM with MLflow Tracking

Stage 6: Decision Engine — From Forecast to Action

Stage 7: Dashboard — Making the Forecast Visible

Stage 8: Genie Space — Natural Language Analytics

Stage 9: Workflow Automation — Making It Repeatable

Errors I Hit and How I Fixed Them

What It Would Take to Go to Production

Key Takeaways

Final Thoughts

Re: Building an End-to-End Store Order Forecasting System on Databricks: From Zero to Automated Pipe