76 ) Food delivery project

 

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_Amount is 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.