A Data & AI–Driven Decision Engine for Modern Retail Networks
Introduction
In modern retail, supply chains are no longer static networks — they are living, adaptive systems that must continuously respond to customer demand, fulfillment speed expectations, cost pressures, and evolving logistics infrastructure.
At XYZ – Supply Chain King, we built a large-scale supply chain simulation platform to help business leaders answer one critical question:
“Given demand patterns and customer locations, how should we design, expand, and operate our fulfillment network to optimize cost, speed, and service levels?”
This blog explains how we designed and implemented a production-grade simulation platform using Databricks Lakehouse, Serverless compute, Unity Catalog, Delta Live Tables (DLT), and advanced graph optimization algorithms to simulate millions of fulfillment decisions across a complex logistics network.
Business Problem Statement
XYZ operates a highly distributed retail and fulfillment network consisting of:
- Supercenters
- Distribution Centers (DC)
- Fulfillment Centers (FC)
- Sort Centers (SC)
- Delivery Centers
- Last-Mile Delivery (LMD) hubs
- Third-party courier services (e.g., external carriers)
The business challenges were:
- Where to open new nodes (FC, DC, SC, etc.) based on regional demand
- Which products (fast-moving vs slow-moving) should be stocked at which node
- How to route each customer order through the network optimally
- How to balance competing objectives:
- Fastest delivery
- Lowest cost
- Same-day delivery guarantees
Traditional BI reports and heuristics were insufficient. The business needed a what-if simulation engine that could mimic real-world demand and fulfillment behavior at scale.
High-Level Solution Overview
We built a self-service simulation platform where business users can configure scenarios and run simulations without writing code.
Key Capabilities
- Demand simulation using historical + synthetic ML-generated orders
- End-to-end network modeling using graph-based algorithms
- Cost and delivery optimization using Ant Colony Optimization
- Scalable execution using Databricks Serverless Workflows
- Secure, governed data access using Unity Catalog
- Real-time visualization via Tableau dashboards
Business User Inputs
The simulation starts with a business-driven configuration layer.
- Order Data Source
- Historical customer orders (with latitude & longitude)
- Synthetic demand generated via ML models to simulate future scenarios
- Delivery Channel Selection
- XYZ-owned Last Mile Delivery (LMD)
- Third-party courier services (e.g., FedEx-like providers)
- Rate Card Configuration
- Distance-based pricing by pincode
- Uploaded as Excel files from courier partners
- Current Network Topology
- Locations of all operational nodes (SC, DC, FC, etc.)
- Each node includes geo-coordinates and capacity metadata
- Optimization Strategy
- Fastest delivery
- Lowest cost
- Same-day delivery priority
System Architecture on Databricks
Why Databricks Lakehouse?
The simulation required:
- Massive data processing
- Complex graph computation
- Strong data governance
- Cost-efficient scaling
Databricks Lakehouse provided a single unified platform for all of this.
Data Ingestion & Governance
Delta Live Tables (DLT)
All raw and curated datasets were built using DLT pipelines:
- Historical orders
- Synthetic order data
- Network master data
- Rate cards
- Simulation outputs
DLT ensured:
- Declarative ETL
- Automatic retries
- Data quality checks
- Lineage visibility
Unity Catalog
Unity Catalog provided:
- Fine-grained access control (table, column, row level)
- Secure sharing between data science, engineering, and BI teams
- Centralized governance for sensitive customer location data
Simulation Execution Flow
Step 1: UI-Level Validation
Before execution, the platform validates:
- Input completeness
- File schema correctness
- Geo-coordinate sanity checks
- Configuration compatibility
Step 2: Databricks Workflow Orchestration
Once validated, the simulation is submitted to a Databricks Workflow, executed entirely on Serverless compute for elasticity and cost efficiency.
Step 3: Edge Data Generation (Graph Modeling)
The first backend job generates a graph representation of the supply chain network.
Graph Construction Logic
- Nodes represent:
- Supercenters
- DCs
- FCs
- SCs
- Delivery centers
- Edges represent:
- Possible movement paths between nodes
- Distance-based and cost-based relationships
The system generates N × N combinations, such as:
- DC → FC
- FC → SC
- SC → Delivery Center
- Delivery Center → Customer (lat, long)
This edge data is stored as Delta tables for downstream reuse.
Step 4: Route Optimization Using Ant Colony Optimization
Why Ant Colony Optimization (ACO)?
Supply chain routing is a combinatorial optimization problem with multiple objectives and constraints.
ACO was chosen because:
- It efficiently explores large solution spaces
- It balances exploration vs exploitation
- It adapts well to dynamic constraints (cost, distance, delivery SLAs)
Optimization Logic
For each order:
- The algorithm evaluates all feasible paths
- Applies pheromone-based scoring for:
- Distance
- Cost
- Delivery speed
- Selects the optimal route based on user preference
Example Output
Order ID 101
Route: DFC1 → FC5 → SC3 → Customer
Delivery Mode: Same Day
Total Cost: X
Distance: Y
This output is persisted in Delta tables for auditing and analysis.
Step 5: Cost Calculation Engine
A downstream Databricks job calculates:
- Cost per order
- Cost per package
- Cost per delivery channel
- Regional cost efficiency metrics
The cost engine:
- Joins route data with rate cards
- Applies distance-based pricing
- Accounts for internal vs third-party logistics
Step 6: Analytics & Visualization
The final curated Delta tables power Tableau dashboards, providing:
- Network heatmaps
- Cost vs speed trade-off analysis
- Optimal node placement recommendations
- Capacity utilization insights
Dashboards refresh automatically after each simulation run.
Key Business Outcomes
For Business Teams
- Data-driven decisions on network expansion
- Reduced cost per delivery
- Improved delivery SLA adherence
- Scenario planning without engineering dependency
For Engineering & Data Teams
- Scalable, serverless execution
- Unified Lakehouse architecture
- Strong governance and lineage
- Reusable simulation datasets
