Step 1: The Client's Existing System and Tables
In my previous food delivery project, the client was using basic operational databases to manage users, restaurants, and drivers:
App Database (MySQL): This handled customer profiles and food orders. The main tables were:
Users: Stored customer names, phone numbers, and delivery addresses.Restaurants: Stored restaurant names, food types, and locations.Orders: Stored order numbers, customer IDs, restaurant IDs, and total bill amounts.
Driver Database (SQL Server): This handled delivery boy details and trips. The main tables were:
Drivers: Stored driver names, phone numbers, and vehicle types.Trips: Stored order numbers, assigned driver IDs, and delivery times.
Step 2: The Problems They Faced
Because orders and driver details were stored in two different databases, the team faced simple daily problems:
Scattered Data: If the manager wanted to check which restaurant had the slowest delivery times, they had to open two different systems and match the data manually in Excel.
Slow Reports: Running daily sales reports or checking top-selling restaurants directly on the live app database made the app slow for customers trying to place orders.
Missing History: If a restaurant changed its food item prices, the old price was completely erased, making it impossible to check last month's exact earnings.
Step 3: The Client's Expectation and Why
The Goal: They wanted a single data warehouse where all order and delivery details could live together.
The Reason: They wanted fast daily reports to see total sales, popular food items, and average delivery times without slowing down the customer app.
Step 4: The Solution Design I Proposed (Star Schema)
1. Defined What to Measure
We focused on two main things: Sales (orders placed) and Deliveries (time taken to deliver).
2. Designed the Dimension Tables (The Details)
Dim_Customer: Stored customer names and addresses.Dim_Restaurant: Stored restaurant names and food categories.Dim_Driver: Stored driver names and vehicle types.Dim_Date: Stored calendar dates so reports could be run by day, week, or month.
3. Designed the Fact Tables (The Numbers)
Fact_Orders: Stored order numbers, item quantities, and total money paid.Fact_Deliveries: Stored pickup times, drop-off times, and total delivery time in minutes.
Interview Questions and Answers: Food Delivery Project
Q1: Can't the restaurant app just run these reports directly on the MySQL database? Why do we need a Data Warehouse?
Answer:
If we run heavy reports on the live MySQL database, it slows down the app for customers trying to place food orders. The live database is built for quick everyday taps and clicks, not for scanning millions of past orders to check monthly sales trends. A Data Warehouse keeps heavy reporting separate so the food app stays fast.
Q2: What if you have to add a new column or change a dimension later? Is it easy to do?
Answer:
Yes, it is easy. If we want to add a new detail—like adding a "Customer Food Preference" column to the customer table—we just update the table structure and change our daily data loading script for future records. It does not break past data.
Q3: What if the fact table data needs to be loaded weekly or monthly instead of daily? How do you handle that?
Answer:
We handle this by scheduling our data loading jobs. While regular order details are loaded every night, monthly summary tables are set to run on the 1st of every month. We also split our large data tables by date so the system only updates the specific week or month needed instead of scanning everything.
Q4: How do you handle late-arriving orders or delivery updates that come in days later?
Answer:
We use the original order date instead of the date the data was loaded. When the late data arrives during our nightly run, our script places it into the correct past date section in the data warehouse, so our old daily and weekly reports update automatically with the correct numbers.
Q5: What is a "Factless Fact Table," and did you use one in the food delivery project?
Answer:
A factless fact table is a table that has no money values or numbers inside it; it only tracks events or connections. Yes, we used one to track Restaurant Promotional Banners (Fact_Promo_Views). It recorded which user viewed which restaurant banner on a specific day, helping marketing check campaign views without tracking money.
Q6: How do you handle changes to a restaurant's details (like a name or location change) when past orders are already linked to it?
Answer:
We use Slowly Changing Dimensions (SCD Type 2). If a restaurant moves to a new street, we don't overwrite the old address. We close the old row with an end date and add a new row with the new address. This way, old orders always point to the restaurant's address as it was on the day the food was ordered.
Q7: What is the difference between an Additive and Non-Additive fact in our food delivery data?
Answer:
Additive Fact: These can be added up across everything. For example,
Total_Bill_Amountis additive—we can add it up by driver, restaurant, or month to get total sales.Non-Additive Fact: These cannot be added up. Ratios or percentages fall here, like a
Delivery_Rating_Average. You cannot add averages together; the data warehouse has to calculate them fresh.
Q8: How do you stop duplicate order records from coming into the data warehouse?
Answer:
We stop duplicates in two ways. First, our daily loading script cleans the data and drops exact technical duplicates. Second, we set unique rules on our fact table using the Order_ID and Order_Timestamp, so if the same file is accidentally loaded twice, the system skips the duplicate rows.
Q9: What happens if an order comes in for a new driver whose ID does not exist in the driver table yet?
Answer:
To prevent the data loading script from crashing, we use a default row called "Unknown Driver" with an ID of -1. If a missing ID comes in, the script assigns it to -1 so the report doesn't break, and it sends an alert to our team to add the new driver to the system.
Q10: If the business asks to add a completely new metric that we never tracked in the past, what do you tell them?
Answer:
If we never collected that data in the past, I would explain to the business team that we cannot magically create old historical data. I would update our data collection script starting from today so we can track that new metric moving forward, and note clearly in the reports that older data is not available.
\\ 4 . Food delivery project data model
Explain about your project
1. About the Food Delivery Project
The core objective was to build a system capable of managing a multi-sided marketplace—connecting customers, restaurants, and delivery drivers in real-time. The primary challenges included managing high-concurrency order placement, real-time location tracking for drivers, and ensuring fair commission structures and timely payouts.
Design Approach
Mapping the Lifecycle: We identified key touchpoints: User Registration, Restaurant Menu Management, Order Placement, Dispatch/Routing, Order Fulfillment, and Driver Payouts.
Normalization (OLTP): Data is organized into 3rd Normal Form (3NF) to ensure consistency between customer profiles, restaurant menus, and order status tracking, preventing inventory and delivery errors.
Handling Complex Relationships: We utilized "bridge" tables (e.g., Order_Items, Driver_Earnings) to manage many-to-many relationships, storing detailed attributes like item modifiers, delivery distance, and variable service fees.
Business Rules & Integrity: Constraints such as order_status_progression and delivery_window_validation ensure that orders move logically through the lifecycle without status conflicts.
2. The Order Fulfillment Lifecycle
User Onboarding: Customer registration with saved addresses and payment methods.
Menu Management: Restaurants publish menus with prices, descriptions, and availability status.
Order Placement: Customers browse and select items, initiating an Order record with a specific status ("Pending").
Dispatch: The system assigns a nearby driver to the order based on geographic proximity and availability.
Fulfillment: Real-time tracking of the order from restaurant pickup to customer doorstep.
Financial Settlement: The Billing stage calculates the order total, delivery fee, restaurant commission, and driver earnings.
3. Food Data Model Entities
Data model image
3.1 OLTP Tables List (Operational Database)
Based on your requirements, here is the list of database tables:
Customers
Customer_Addresses
Restaurants
Restaurant_Hours
Menu_Categories
Menu_Items
Orders
Order_Items
Item_Modifiers
Drivers
Deliveries
Payments
Commission_Splits
Driver_Payouts
3.2 OLAP Tables List (Data Warehouse)
Fact Tables (Metrics)
Fact_Orders: Total volume, average order value, cancellation rates.
Fact_Delivery_Performance: Delivery times, driver performance metrics, fulfillment efficiency.
Fact_Restaurant_Revenue: Commission earnings, daily sales volume, top-selling items.
Dimension Tables (Filters)
Dim_Date: Temporal analysis (Date, Hour, Day of Week, Month).
Dim_Customer: Demographic segmentation (Frequency of use, location).
Dim_Restaurant: Cuisine type, geographical region.
Dim_Driver: Driver performance tier, vehicle type.
4. KPIs and Analytics
5. KPIS and FACTS
Fact_Orders
KPIs: Count of Orders, Average Order Value, Order Cancellation Rate, Gross Merchandise Value (GMV).
Dimensions: Dim_Date, Dim_Customer, Dim_Restaurant, Dim_City.
Fact_Delivery_Performance
KPIs: Average Delivery Time, On-time Arrival Rate, Driver Utilization Rate, Total Distance Traveled.
Dimensions: Dim_Date, Dim_Driver, Dim_Restaurant, Dim_City.
Fact_Restaurant_Revenue
KPIs: Total Commission Earned, Total Sales Volume, Top Performing Categories, Average Item Price.
Dimensions: Dim_Date, Dim_Restaurant, Dim_Category.
Fact_Customer_Activity
KPIs: Customer Retention Rate, New User Signups, Average Orders per Customer, Lifetime Value (CLV).
Dimensions: Dim_Date, Dim_Customer, Dim_City.
6. KPI Definitions
Order Volume: Total number of successful orders placed.
Average Order Value (AOV): Total sales revenue divided by the number of orders.
Order Cancellation Rate: Percentage of orders cancelled by users or restaurants.
Average Delivery Time: Mean duration from order confirmation to delivery completion.
On-time Arrival Rate: Percentage of orders delivered within the estimated window.
Driver Utilization Rate: Percentage of time drivers spend on active delivery tasks.
Gross Merchandise Value (GMV): The total value of food sold through the platform.
Commission Revenue: Total income generated through restaurant commissions and service fees.
Customer Retention Rate: Percentage of customers who repeat orders within a period.
Customer Lifetime Value (CLV): Total predicted revenue attributed to a customer relationship.
No comments:
Post a Comment