Data Engineering Interview Questions: Data Warehouse Design

Data Warehouse Design for a Food Delivery App

Source

Data engineering for food delivery systems can appear straightforward on the surface — after all, it’s “just orders and deliveries,” right? Yet, when examining the various data points involved (customers, restaurants, couriers, menu items, and more), it quickly becomes clear how many moving parts exist in the business. Each piece represents a significant part of the puzzle, from capturing a customer’s location details and payment methods to tracking every menu item sold and rating the delivery people's performance. Structuring these data interactions in a well-designed warehouse is essential for good reporting, historical analysis, and future performance like predictive ordering or optimized delivery routing.

A robust warehouse design ensures that operational metrics such as average delivery times, popular dishes, and loyal customers are readily available to analysts. It also prevents chaos when new features are made online, like dynamic pricing or special promotions. This introduction highlights the value of carefully mapping out fact and dimension tables, distinguishing between numeric measures (like total revenue or distance travelled) and descriptive attributes (like restaurant categories or customer segments). By building these components into a coherent schema, you help both technical and business stakeholders gain immediate, actionable insights.

1. Core Entities

Building a data warehouse for a food delivery system usually starts with singling out the main entities:

• Customers: People who place orders using the app.
• Restaurants: Food providers with menus displayed on the platform.
• Orders: Records of all transactions, including payments and item details.
• Delivery Personnel: Couriers who pick up restaurant orders and drop them off to customers.
• Menu Items: Individual dishes or drinks offered by each restaurant.
• Time: Various timestamps representing order creation, delivery, or special events (like holidays).
• Locations: Addresses and coordinates for both customers and restaurants.
• Payments: Information about transactions, methods used, and any relevant statutes.

Having these entities sketched out helps shape the data model.

2. Fact Tables

Fact tables house numeric or event-based details that can be tallied or averaged. In a food delivery scenario, there are typically three:

2.1. Order Fact Table

Tracks the moment an order is placed:

Measures:

• Order Amount: The total billed cost.
• Delivery Fee: Additional fee for transporting the food.
• Discount Applied: Any coupons or promotions.
• Total Tax: All taxes applied to the order.
• Tip Amount: Gratuity for couriers.
• Order Quantity: Total items in the order.

Analysts can figure out overall revenue, discount impacts, or average order size from these measures.

2.2. Delivery Fact Table

Captures details about delivering orders:

Measures:

• Delivery Time: Total duration from pickup to drop-off.
• Distance Traveled: How far the courier had to go.
• Delivery Rating: Score from customers on the courier’s service.
• Pickup Time: When the courier arrives at the restaurant.
• Drop-off Time: When the courier handed over the order to the customer.
• Average Speed: Calculated distance/time measure.

Great for monitoring delivery performance and courier efficiency.

2.3. Menu Item Sales Fact Table

Records of which items are sold and how often:

Measures:

• Quantity Sold: Number of each menu item sold.
• Item Total Price: Revenue from selling that item.
• Special Instructions Count: How many orders had custom requests?
• Preparation Time: How long it takes to cook or prepare a dish.

Helps measure menu popularity and revenue by item.

Diagram of the Warehouse

3. Dimension Tables

3. Dimension Tables

Dimension tables add descriptive fields that give context and depth to the numeric measures in the fact tables.

3.1. Customer Dimension

Attributes:

• Customer ID
• Full Name
• Email
• Phone Number
• Registration Date
• Loyalty Level
• Preferred Payment Method

Links to fact tables to reveal spending patterns or segmentation by loyalty.

3.2. Restaurant Dimension

Attributes:

• Restaurant ID
• Name
• Cuisine Type
• Average Rating
• Operating Hours
• Price Range
• Location

Useful for seeing which restaurants perform best or have the highest ratings.

3.3. Delivery Personnel Dimension

• Attributes:
• Delivery Personnel ID
• Name
• Vehicle Type
• Employment Status
• Average Rating
• Total Deliveries

Connects with the Delivery Fact Table, so teams can figure out who’s delivering quickly, who might need coaching, or how to optimize scheduling.

3.4. Menu Item Dimension

• Attributes:
• Menu Item ID
• Item Name
• Description
• Category
• Base Price
• Ingredients
• Allergen Info

Combined with Menu Item Sales Fact Table for analyzing dish performance and trends.

3.5. Time Dimension

• Attributes:
• Time ID
• Date
• Day of Week
• Week Number
• Month
• Year
• Holiday Flag

Allows slicing data by periods (daily, weekly, etc.) to detect seasonal or hourly patterns.

3.6. Location Dimension

• Attributes:
• Location ID
• Street Address
• City
• State/Province
• Country
• ZIP Code
• Latitude/Longitude

Let analysts do region-based breakdowns or track distribution coverage.

3.7. Payment Dimension

• Attributes:
• Payment ID
• Payment Method
• Transaction Status
• Card Type
• Payment Provider

Paired with fact tables to see which payment modes are most popular or if certain methods cause higher failures.

For the next stories, we will answer some of these interview questions.

• What is the grain of the Order Fact Table? A single row per order? Or do you save each item on that table?
• You record both Pickup and Drop-off times in the Delivery Fact Table. How are they mapped to the Time Dimension?
• If a restaurant changes its menu or operating hours, Do you handle dimension updates and which is the best way to do it?
• What indexing strategies or partitioning schemes do you adopt if your system generates large orders daily?
• If your system logs unusual values (like a negative delivery fee), how do you handle that?
• Do you transform data before loading or after it’s in the warehouse? Why choose one approach over the other?
• Which insights might you get by analyzing payment methods (fraud detection, etc.)?
• If you track both restaurants and customers, do you store them in the same dimension or separate them?
• Can you Build the SQL code for the Dimension and Fact Tables?
• What is the difference between Facts and Dimensions?
• What is the difference between Snowflake and Star Schema?
• When would you consider an extra fact table, for example, marketing campaigns or loyalty rewards?

Conclusion

This design outlines a coherent architecture for a food delivery data warehouse. You divide your measures across three main fact tables — Orders, Deliveries, and Menu Item Sales — and enrich them with dimension tables like Customer, Restaurant, Delivery Personnel, Menu Item, Time, Location, and Payment. This approach simplifies queries for a wide range of analytics: from real-time visibility into peak delivery times to go down into a specific menu item’s performance. By breaking down each process into facts and dimensions, you can scale your pipelines to handle large volumes of data while still providing meaningful dashboards and reports for users.