Building a Modern Semantic Layer Using a Food Delivery Platform

  

Most semantic layer tutorials use overly simplistic examples like:

 

·      users

·      products

·      orders

 

Those examples are useful for beginners, but they fail to demonstrate the real problems semantic layers solve in production systems.

 

Modern businesses deal with:

 

·      event streams

·      operational analytics

·      nested metrics

·      many-to-many relationships

·      real-time dashboards

·      access control

·      aggregation correctness

·      AI-ready metrics

·      cross-domain analytics

 

To demonstrate these properly, we’ll use a realistic food delivery platform similar to:

 

·      Swiggy

·      Zomato

·      Uber Eats

·      DoorDash

 

This domain is perfect because it combines:

 

·      transactional analytics

·      operational analytics

·      time-series events

·      marketplace economics

·      logistics

·      user behavior

·      real-time event processing

 

It exposes almost every challenge a semantic layer is designed to solve.

 

1. What We Are Building

We are modeling a complete food delivery ecosystem containing:

 

Customers place orders

    ↓

Restaurants prepare food

    ↓

Drivers deliver orders

    ↓

Payments are processed

    ↓

Events are generated throughout delivery lifecycle

 

This gives us:

 

·      fact tables

·      dimension tables

·      event tables

·      transactional metrics

·      operational metrics

 

all inside one realistic system.

 

Why This Example Is Powerful?

With this single domain, we can demonstrate:

 

Topic	Example
Measures	Total revenue
Dimensions	Restaurant cuisine
Joins	Orders to order_items
Fanout problems	Revenue duplication
Event analytics	Delivery lifecycle
Calculated metrics	Avg delivery time
Time-series	Orders per hour
Nested aggregation	Avg orders per customer
Access control	Region-based analytics
Pre-aggregations	Daily city revenue
Real-time analytics	Active deliveries
AI-ready semantics	Reusable governed metrics

 

This is why food delivery systems are one of the best educational examples for semantic modeling.

 

2. Table-by-Table Explanation

a. customers

This table stores customer information. Represents users placing food orders.

| Column      | Description            |
| ----------- | ---------------------- |
| customer_id | Unique customer        |
| full_name   | Customer name          |
| city_id     | Customer city          |
| signup_date | Registration timestamp |
| is_premium  | Subscription member    |

   

b. restaurants

Represents restaurant partners.

 

| Column         | Description       |
| -------------- | ----------------- |
| restaurant_id  | Unique restaurant |
| cuisine_id     | Cuisine type      |
| city_id        | Restaurant city   |
| average_rating | Restaurant rating |

   

c. drivers

Represents delivery partners.

 

| Column       | Description      |
| ------------ | ---------------- |
| driver_id    | Delivery partner |
| vehicle_type | Bike/Scooter     |
| rating       | Driver rating    |
| is_active    | Current status   |

   

d. orders

This is the central transactional fact table. One row = one order.

 

| Column        | Description        |
| ------------- | ------------------ |
| order_id      | Unique order       |
| customer_id   | Buyer              |
| restaurant_id | Restaurant         |
| driver_id     | Delivery partner   |
| final_amount  | Total charged      |
| ordered_at    | Order timestamp    |
| delivered_at  | Delivery timestamp |

   

e. order_items

This is one of the MOST IMPORTANT tables in the entire model.One order can contain multiple items.

 

| Column        | Description               |
| ------------- | ------------------------- |
| order_item_id | Unique order item record  |
| order_id      | Parent order reference    |
| item_name     | Food item ordered         |
| category_name | Food category             |
| quantity      | Number of units ordered   |
| unit_price    | Price per item            |
| total_price   | quantity × unit_price     |
| created_at    | Record creation timestamp |

   

f. delivery_events

 

| Column          | Description                   |
| --------------- | ----------------------------- |
| event_id        | Unique delivery event         |
| order_id        | Related order                 |
| driver_id       | Assigned driver               |
| event_type      | Delivery lifecycle event      |
| event_timestamp | Time event occurred           |
| latitude        | Driver latitude               |
| longitude       | Driver longitude              |
| event_metadata  | Additional JSON event details |
| created_at      | Record creation timestamp     |

   

g. payments

Tracks financial transactions.

 

| Column                | Description                |
| --------------------- | -------------------------- |
| payment_id            | Unique payment             |
| order_id              | Related order              |
| payment_method        | UPI/Card/Cash              |
| payment_provider      | Razorpay/PhonePe/etc       |
| transaction_reference | External payment reference |
| payment_amount        | Amount paid                |
| payment_status        | PAID/FAILED/PENDING        |
| paid_at               | Payment timestamp          |
| created_at            | Record creation timestamp  |

   

h. refunds

Tracks customer refunds.

 

| Column        | Description                 |
| ------------- | --------------------------- |
| refund_id     | Unique refund               |
| order_id      | Related order               |
| payment_id    | Related payment             |
| refund_reason | Reason for refund           |
| refund_amount | Amount refunded             |
| refund_status | Refund state                |
| requested_at  | Refund request timestamp    |
| processed_at  | Refund completion timestamp |
| created_at    | Record creation timestamp   |

   

i. driver_payouts

Tracks payments made to delivery partners.

 

| Column        | Description               |
| ------------- | ------------------------- |
| payout_id     | Unique payout             |
| driver_id     | Driver receiving payout   |
| order_id      | Related order             |
| payout_amount | Amount paid to driver     |
| payout_status | COMPLETED/PENDING         |
| payout_date   | Payout timestamp          |
| created_at    | Record creation timestamp |

   

g. cities

Represents geographic regions where the platform operates.

 

| Column       | Description               |
| ------------ | ------------------------- |
| city_id      | Unique city               |
| city_name    | City name                 |
| state_name   | State/province            |
| country_name | Country                   |
| timezone     | Region timezone           |
| created_at   | Record creation timestamp |

   

h. cuisines

Represents cuisine classifications.

 

| Column       | Description               |
| ------------ | ------------------------- |
| cuisine_id   | Unique cuisine            |
| cuisine_name | Cuisine type              |
| created_at   | Record creation timestamp |

   

i. promotions

Represents discount campaigns and promotional offers.

 

| Column         | Description               |
| -------------- | ------------------------- |
| promotion_id   | Unique promotion          |
| promo_code     | Coupon/promo code         |
| description    | Promotion details         |
| discount_type  | FIXED/PERCENTAGE          |
| discount_value | Discount amount/value     |
| start_date     | Campaign start            |
| end_date       | Campaign end              |
| is_active      | Active status             |
| created_at     | Record creation timestamp |

 We will use this as our base dataset for modeling with Cube in the upcoming posts.

 

Previous                                                    Next                                                    Home