A food delivery analytics architecture requires two complementary fact tables: an atomic line-item transaction fact to evaluate menu item sales, and an accumulating snapshot fact to monitor the end-to-end fulfillment lifecycle. These facts link to shared dimensions for customers, restaurants, delivery partners, and geography, with a bridge table handling many-to-many promotional discounts.
Food delivery lifecycle and grain
Orders progress through rapid, sequential milestones from basket checkout to doorstep delivery. Tracking this requires two fact grains:
fact_order_lines (atomic sales grain: one row per item ordered) order_id (degenerate dimension) item_id restaurant_key customer_key date_key quantity item_price_amount customization_addon_amount line_discount_amount fact_order_fulfillment (accumulating snapshot: one row per order instance) order_id customer_key restaurant_key delivery_partner_key zone_key order_placed_time_key restaurant_accepted_time_key food_ready_time_key rider_picked_up_time_key delivered_time_key cancellation_time_key cancellation_reason_code total_order_amount delivery_fee_amount tip_amount prep_duration_minutes transit_duration_minutes total_fulfillment_minutes
A short look at the functional division between these tables:
fact_order_linesanswers commercial questions: which menu items produce the highest profit margin, how meal combos perform, and which food items are frequently co-purchased.fact_order_fulfillmentupdates its milestone timestamps as the order progresses through kitchen prep and courier transit, giving dispatchers clear visibility into fulfillment lag.- Rider tips are recorded separately from meal totals so post-delivery tipping adjustments reconcile with courier payouts without inflating food gross merchandise value.
Dimension hierarchy and promotion bridges
The core dimensions are:
dim_restaurant: Captures cuisine types, restaurant partner tier, kitchen preparation capacity, and operating zone.dim_delivery_partner: Uses SCD Type 2 to track courier vehicle category (bicycle, motorbike, electric scooter), current rating band, and active onboarding status.dim_customer: Captures ordering frequency, subscription membership (like Swiggy One or Zomato Gold), and primary delivery coordinates.
Promotions frequently apply across multiple levels (such as ten percent off the order plus free delivery plus a restaurant-sponsored menu discount). To model promotions accurately, attach bridge_order_promotions between fact_order_fulfillment and dim_promotion. The bridge table stores an allocation factor representing the percentage share absorbed by the platform versus the restaurant merchant, avoiding double counting discount totals.
Production analytics queries
This dual-model design directly answers vital business questions. Operations managers query fact_order_fulfillment joined with dim_zone to track average delivery time by zone and identify bottlenecks where delivery partners wait excessively outside merchant kitchens. When orders fail, analyzing cancellation_reason_code against stage duration reveals whether cancellations stem from kitchen backlogs, courier shortages, or customer cancellations after checkout. Correlating restaurant prep lag with peak dinner demand informs dynamic delivery radius throttling, protecting service levels during severe kitchen delays.