Modern analytics systems are no longer built directly on top of raw SQL queries. As organizations scale, analytics teams struggle with duplicated business logic, inconsistent KPIs, fanout issues, complex joins, and governance problems across dashboards and BI tools.
This is where semantic layers become extremely important.
In this tutorial series, we will learn how to model a complete semantic layer using Cube and PostgreSQL by using a realistic food delivery platform schema.
The schema contains real-world operational entities such as:
· customers
· restaurants
· drivers
· orders
· order items
· payments
· refunds
· promotions
· delivery event streams
Using this schema, we will deeply explore:
· cube definitions
· dimensions
· measures
· joins
· relationship modeling
· transitive joins
· fan traps
· chasm traps
· pre-aggregations
· semantic governance
· reusable analytics logic
· query generation behavior
By the end of this series, you will understand how enterprise grade semantic models are designed to support scalable analytics, BI tools, AI-generated SQL, and self-service reporting.
1. Understanding the Food Delivery Platform Schema
Before we start building semantic models and defining joins in Cube, it is important to first understand the business domain and the underlying relational schema.
In real enterprise analytics systems, semantic models are only as good as the operational data model beneath them. Understanding how the business operates helps us design meaningful dimensions, measures, joins, and reusable metrics.
For this tutorial series, we will use a realistic food delivery platform schema modeled in PostgreSQL.
The schema represents a modern online food delivery business similar to applications like:
· food ordering marketplaces
· restaurant aggregators
· hyperlocal delivery platforms
The goal of this schema is not just to store transactional data, but also to support:
· operational analytics
· business intelligence
· real-time dashboards
· delivery monitoring
· customer analytics
· driver performance tracking
· financial reporting
· event stream analysis
This makes it an excellent example for learning semantic modeling concepts.
1.1 Business Overview of the Platform
The platform connects multiple participants together:
|
Participant |
Responsibility |
|
Customers |
Place food orders |
|
Restaurants |
Prepare food |
|
Drivers |
Deliver food |
|
Platform |
Coordinates orders, payments, promotions, and logistics |
At a high level, the business flow looks like this:
· A customer opens the application.
· The customer selects a restaurant and places an order.
· The restaurant accepts and prepares the order.
· A delivery driver is assigned.
· The driver picks up the order and delivers it.
· Payment is processed.
· Refunds may occur if issues happen.
· Drivers receive payouts from the platform.
· Delivery events are continuously captured for operational monitoring.
This workflow naturally creates multiple entities and relationships, making it ideal for demonstrating:
· many_to_one joins
· one_to_many joins
· event stream analytics
· transitive joins
· aggregation challenges
1.2 Operational Workflow
Let us understand the complete lifecycle of an order in the platform.
a. Customer Places an Order
A customer browses restaurants and places an order containing one or more food items.
Relevant tables:
· customers
· restaurants
· orders
· order_items
· promotions
Example:
· Customer: Rahul Sharma
· Restaurant: Spice Garden
· Items: Paneer Biryani, Butter Naan
· Promotion applied: WELCOME50
At this stage, the platform captures:
· order value
· taxes
· discounts
· delivery fees
· timestamps
· payment status
This becomes the foundation for:
· revenue analytics
· customer behavior analysis
· basket analysis
· promotion effectiveness
b. Restaurant Prepares the Order
Once the order is placed:
· the restaurant accepts the order
· food preparation begins
· the platform tracks preparation milestones
Relevant tables:
· orders
· delivery_events
Delivery events may include:
· ORDER_PLACED
· RESTAURANT_ACCEPTED
· PREPARATION_STARTED
This operational data helps measure:
· restaurant SLA performance
· kitchen preparation delays
· order acceptance efficiency
c. Driver Delivers the Order
A delivery driver gets assigned to the order.
Relevant tables:
· drivers
· orders
· delivery_events
· driver_payouts
The platform continuously records delivery tracking events such as:
· DRIVER_ASSIGNED
· DRIVER_REACHED_RESTAURANT
· ORDER_PICKED_UP
· DRIVER_REACHED_CUSTOMER
· ORDER_DELIVERED
This enables:
· real-time operational dashboards
· driver productivity analytics
· delivery time analysis
· geographic tracking
· ETA prediction systems
d. Payment Processing
Payments are handled separately from orders.
Relevant tables:
· payments
· orders
This separation is important because:
· payments may retry
· external gateways may fail
· refunds may occur later
· reconciliation systems require separate tracking
Captured details include:
· payment method
· provider
· transaction reference
· payment status
· payment timestamps
This supports:
· financial reconciliation
· payment success rate analysis
· gateway monitoring
· failed payment tracking
e. Refund Lifecycle
Sometimes orders fail or customers request refunds.
Relevant tables:
· refunds
· payments
· orders
Refund reasons may include:
· late delivery
· incorrect items
· poor food quality
· cancelled orders
This data supports:
· refund trend analysis
· restaurant quality monitoring
· operational issue detection
· fraud analytics
One of the biggest advantages of this schema is that it supports both:
· transactional analytics
· operational event analytics
This makes it highly realistic for enterprise semantic modeling.
2. Understanding Relationships in Analytics Systems
Relationships are one of the most important concepts in data modeling, analytics engineering, and semantic layer design.
In systems like Cube, relationships determine:
· how tables are connected
· how SQL joins are generated
· how aggregations behave
· whether measures remain accurate
· how fan traps and chasm traps are detected
Without properly understanding relationships, it becomes very easy to produce:
· duplicated revenue
· inflated counts
· incorrect averages
· misleading dashboards
This is why semantic modeling is fundamentally about understanding relationships between business entities.
2.1 Why Relationships Matter in Analytics
In transactional databases, relationships primarily help maintain:
· data integrity
· referential consistency
But in analytics systems, relationships become much more important because they directly affect:
· aggregations
· joins
· metrics
· query correctness
For example:
· one order may contain many items
· one customer may place many orders
· one driver may complete many deliveries
If these relationships are modeled incorrectly, metrics such as:
· total revenue
· total orders
· average order value
can become completely inaccurate.
This is one of the biggest differences between application database design and analytics semantic modeling
2.2 Understanding Primary Keys
A primary key uniquely identifies each row in a table. Think of it as the identity column of a business entity
Every table should ideally have a column that uniquely identifies each record.
Examples from our schema:
|
Table |
Primary Key |
|
customers |
customer_id |
|
orders |
order_id |
|
order_items |
order_item_id |
|
payments |
payment_id |
|
delivery_events |
event_id |
Why Primary Keys Are Critical in Cube?
In Cube, primary keys are not just database concepts. They are extremely important for:
· join correctness
· deduplication
· fan trap prevention
· aggregation accuracy
Cube uses primary keys to detect:
· row multiplication
· duplicated measures
· aggregation inflation
For example, one order contains many order items. When joining orders with order_items, order rows get repeated. Cube uses the primary key of orders to deduplicate rows before calculating any measures.
Without a proper primary key, aggregations can become incorrect. This is why Cube strongly recommends defining primary key for a cube.
Example
cubes: - name: orders dimensions: - name: order_id sql: order_id type: number primary_key: true
2.3 Understanding Foreign Keys
A foreign key creates a relationship between two tables. It references the primary key of another table. Foreign keys represent business relationships.
Examples from our schema:
|
Child Table |
Foreign Key |
Parent Table |
|
orders |
customer_id |
customers |
|
orders |
restaurant_id |
restaurants |
|
orders |
driver_id |
drivers |
|
order_items |
order_id |
orders |
|
payments |
order_id |
orders |
|
refunds |
payment_id |
payments |
Foreign keys help:
· connect business entities
· define join paths
· establish relationship cardinality
2.4 Understanding Cardinality
Cardinality describes, how many rows from one table relate to another table. This is one of the most important concepts in semantic modeling.
Common cardinality types:
· one-to-one
· one-to-many
· many-to-one
Understanding cardinality helps to determine:
· correct join types
· aggregation behavior
· query generation logic
a. One-to-One Relationships
A one-to-one relationship means, one row in Table A matches exactly one row in Table B. These relationships are relatively rare in analytics systems.
For example, one user always has one user profile. In our current food delivery schema, we do not have a strong natural one-to-one example because operational systems are usually highly transactional.
b. One-to-Many Relationships
A one-to-many relationship means:
· one row in the parent table
· matches many rows in the child table
This is extremely common in transactional systems. For example, One order can contain multiple food items.
We can define the relation in orders cube like below.
joins:
- name: order_items
relationship: one_to_many
sql: "{CUBE}.order_id = {order_items.order_id}"
Why One-to-Many Relationships Are Dangerous?
One-to-many joins can multiply rows.
Orders table:
| order_id | final_amount |
| -------- | ------------ |
| 101 | 1000 |
order_items table:
| order_id | item |
| -------- | ----- |
| 101 | Pizza |
| 101 | Coke |
After joining:
| order_id | final_amount | item |
| -------- | ------------ | ----- |
| 101 | 1000 | Pizza |
| 101 | 1000 | Coke |
Now the revenue appears twice. his is called row multiplication or fanout. This is one of the most important analytics problems.
Cube automatically handles many of these cases using:
· primary keys
· deduplication queries
c. Many-to-One Relationships
Many-to-one is essentially the reverse side of a one-to-many relationship. It means many rows in the current table relate to one row in another table
This is one of the most common relationships in semantic models. For example, Many orders can belong to one customer.
| order_id | customer_id |
| -------- | ----------- |
| 101 | 1 |
| 102 | 1 |
| 103 | 1 |
In Orders cube, we can define the relationship like below.
joins: - name: customers relationship: many_to_one sql: "{CUBE}.customer_id = {customers.customer_id}"
d. Many-to-Many Relationships
Another extremely important relationship type in analytics systems is many-to-many.
A many-to-many relationship means:
· many rows in Table A
· can relate to many rows in Table B
This type of relationship is very common in real-world business systems. However, unlike one-to-many relationships, relational databases usually cannot model many-to-many relationships directly using a single foreign key.
Instead, they require, an intermediate bridge table also called junction table or mapping table or association table.
3. Understanding One-to-One Relationships in Cube Using Orders and Payments
A one-to-one relationship means, one row in Cube A matches exactly one row in Cube B. Although real-world transactional systems often contain one-to-many relationships, one-to-one relationships are extremely useful for:
· understanding join semantics
· learning Cube modeling basics
· understanding query generation
· understanding join direction
For learning purposes, we will intentionally model orders and payments as a one-to-one relationship.
Orders Cube
cubes: - name: orders sql_table: orders dimensions: - name: order_id sql: order_id type: number primary_key: true - name: order_status sql: order_status type: string - name: payment_status sql: payment_status type: string - name: ordered_at sql: ordered_at type: time measures: - name: total_orders type: count - name: total_revenue sql: final_amount type: sum joins: - name: payments relationship: one_to_one sql: "{CUBE}.order_id = {payments.order_id}"
Payments Cube
cubes: - name: payments sql_table: payments dimensions: - name: payment_id sql: payment_id type: number primary_key: true - name: payment_method sql: payment_method type: string - name: payment_provider sql: payment_provider type: string - name: transaction_reference sql: transaction_reference type: string - name: payment_status sql: payment_status type: string - name: paid_at sql: paid_at type: time measures: - name: total_payment_amount sql: payment_amount type: sum
Understanding the Join
The important section is:
joins: - name: payments relationship: one_to_one sql: "{CUBE}.order_id = {payments.order_id}"
This tells cube that,
· each order matches exactly one payment
· joining payments will not multiply order rows
· aggregations remain safe
Join direction is important. In our example, it is orders → payments, this means:
· orders is the LEFT side
· payments is joined onto orders
Conceptually, Cube generates:
FROM orders LEFT JOIN payments ON orders.order_id = payments.order_id
This ensures:
· all orders appear
· payment details are attached if available
Example 1: Revenue by Payment Method
How much revenue came from each payment method?
measures: - orders.total_revenue dimensions: - payments.payment_method
Above spec generates following query.
SELECT "payments".payment_method "payments__payment_method", sum("orders".final_amount) "orders__total_revenue" FROM orders AS "orders" LEFT JOIN payments AS "payments" ON "orders".order_id = "payments".order_id GROUP BY 1 ORDER BY 2 DESC
Example 2: Orders by Payment Provider
How many orders were processed by each payment provider?
measures: - orders.total_orders dimensions: - payments.payment_provider
Above spec generates following query.
SELECT "payments".payment_provider "payments__payment_provider", count("orders".order_id) "orders__total_orders" FROM orders AS "orders" LEFT JOIN payments AS "payments" ON "orders".order_id = "payments".order_id GROUP BY 1 ORDER BY 2 DESC
Example 3: Revenue by Payment Provider and Order Status Over Time
Show daily revenue grouped by payment provider and order status.
measures: - orders.total_revenue dimensions: - payments.payment_provider - orders.order_status timeDimensions: - dimension: orders.ordered_at granularity: day order: orders.ordered_at: asc
Generated Query
SELECT "payments".payment_provider "payments__payment_provider", "orders".order_status "orders__order_status", date_trunc('day', ("orders".ordered_at::timestamptz AT TIME ZONE 'UTC')) "orders__ordered_at_day", sum("orders".final_amount) "orders__total_revenue" FROM orders AS "orders" LEFT JOIN payments AS "payments" ON "orders".order_id = "payments".order_id GROUP BY 1, 2, 3 ORDER BY 3 ASC
4. Understanding One-to-Many Relationships in Cube Using Cities and Restaurants
Now that we understand one-to-one relationships, we can move to one of the most important relationship types in analytics systems, One-to-Many Relationships
A one-to-many relationship means, one row in the parent table can match many rows in the child table
This is extremely common in:
· transactional systems
· operational systems
· analytics platforms
Examples:
· one customer → many orders
· one order → many order items
· one driver → many payouts
· one city → many restaurants
Let’s use cities to restaurants example for the demo.
Suppose our food delivery platform operates across multiple cities.
|
city_id |
city_name |
|
1 |
Bangalore |
|
2 |
Hyderabad |
|
3 |
Chennai |
Now consider restaurants:
|
restaurant_id |
restaurant_name |
city_id |
|
101 |
Spice Garden |
1 |
|
102 |
Pizza Hub |
1 |
|
103 |
Biryani Palace |
2 |
|
104 |
Dosa Corner |
2 |
Notice:
· Bangalore has multiple restaurants
· Hyderabad has multiple restaurants
This creates one city → many restaurants
This relationship powers many business questions:
· How many restaurants exist per city?
· Which cities have the highest restaurant growth?
· Which cities generate the highest revenue?
· Which cuisines dominate each city?
· Which cities have the best restaurant ratings?
These are common:
· BI dashboard queries
· marketplace analytics queries
· operational reports
Let’s define city cube.
cities.yml
cubes: - name: cities sql_table: cities dimensions: - name: city_id sql: city_id type: number primary_key: true - name: city_name sql: city_name type: string - name: state_name sql: state_name type: string - name: country_name sql: country_name type: string measures: - name: total_cities type: count joins: - name: restaurants relationship: one_to_many sql: "{CUBE}.city_id = {restaurants.city_id}"
restaurants.yml
cubes: - name: restaurants sql_table: restaurants dimensions: - name: restaurant_id sql: restaurant_id type: number primary_key: true - name: restaurant_name sql: restaurant_name type: string - name: average_rating sql: average_rating type: number - name: is_open sql: is_open type: boolean measures: - name: total_restaurants type: count - name: avg_restaurant_rating sql: average_rating type: avg
Understanding the Join
joins: - name: restaurants relationship: one_to_many sql: "{CUBE}.city_id = {restaurants.city_id}"
This tells Cube that one city row can match many restaurant rows.
Conceptually:
FROM cities LEFT JOIN restaurants ON cities.city_id = restaurants.city_id
4.1 Examples
Example 1: Total Restaurants by City
How many restaurants exist in each city?
Query Spec
measures: - restaurants.total_restaurants dimensions: - cities.city_name
Generated Query
SELECT "cities".city_name "cities__city_name", count("restaurants".restaurant_id) "restaurants__total_restaurants" FROM cities AS "cities" LEFT JOIN restaurants AS "restaurants" ON "cities".city_id = "restaurants".city_id GROUP BY 1 ORDER BY 2 DESC
Example 2: Average Restaurant Rating by City
measures:
measures: - restaurants.avg_restaurant_rating dimensions: - cities.city_name order: restaurants.avg_restaurant_rating: desc
Generated Query
SELECT "cities".city_name "cities__city_name", avg("restaurants".average_rating) "restaurants__avg_restaurant_rating" FROM cities AS "cities" LEFT JOIN restaurants AS "restaurants" ON "cities".city_id = "restaurants".city_id GROUP BY 1 ORDER BY 2 DESC
Example 3: Open vs Closed Restaurants by City
How many restaurants are currently open in each city?
measures: - restaurants.total_restaurants dimensions: - cities.city_name - restaurants.is_open
Generated Query
SELECT "cities".city_name "cities__city_name", "restaurants".is_open "restaurants__is_open", count("restaurants".restaurant_id) "restaurants__total_restaurants" FROM cities AS "cities" LEFT JOIN restaurants AS "restaurants" ON "cities".city_id = "restaurants".city_id GROUP BY 1, 2 ORDER BY 3 DESC
5. Understanding Many-to-One Relationships in Cube Using Orders and Drivers
Now let us move to another extremely important relationship type in semantic modeling, Many-to-One Relationships.
A many-to-one relationship means many rows in the current table relate to one row in another table. This is one of the most common relationship types in:
· transactional systems
· analytics systems
· semantic layers
Examples:
· many orders → one customer
· many orders → one restaurant
· many payments → one provider
· many orders → one driver
In this section, we will use the following business scenario from our food delivery platform: many orders → one driver.
In a food delivery platform:
· drivers complete deliveries
· one driver handles multiple orders during the day
Orders cube
cubes: - name: orders sql_table: orders dimensions: - name: order_id sql: order_id type: number primary_key: true - name: order_status sql: order_status type: string - name: payment_status sql: payment_status type: string - name: final_amount sql: final_amount type: number - name: actual_delivery_minutes sql: actual_delivery_minutes type: number - name: ordered_at sql: ordered_at type: time measures: - name: total_orders type: count - name: total_revenue sql: final_amount type: sum - name: avg_delivery_time sql: actual_delivery_minutes type: avg joins: - name: drivers relationship: many_to_one sql: "{CUBE}.driver_id = {drivers.driver_id}"
drivers cube
cubes: - name: drivers sql_table: drivers dimensions: - name: driver_id sql: driver_id type: number primary_key: true - name: full_name sql: full_name type: string - name: vehicle_type sql: vehicle_type type: string - name: rating sql: rating type: number - name: is_active sql: is_active type: boolean measures: - name: total_drivers type: count
Understanding the Join
joins: - name: drivers relationship: many_to_one sql: "{CUBE}.driver_id = {drivers.driver_id}"
This tells Cube:
· many orders may reference one driver
· orders is the LEFT side
· drivers is the RIGHT side
· driver attributes can enrich orders safely
Conceptual SQL Generated by Cube
FROM orders LEFT JOIN drivers ON orders.driver_id = drivers.driver_id
5.1 Examples
Example 1: Revenue Generated by Drivers
Which drivers generated the highest delivery revenue?
Query Spec
measures: - orders.total_revenue dimensions: - drivers.full_name order: orders.total_revenue: desc
Generated Query
SELECT "drivers".full_name "drivers__full_name", sum("orders".final_amount) "orders__total_revenue" FROM orders AS "orders" LEFT JOIN drivers AS "drivers" ON "orders".driver_id = "drivers".driver_id GROUP BY 1 ORDER BY 2 DESC
Example 2: Orders Delivered by Each Driver
How many deliveries did each driver complete?
Query Spec
measures: - orders.total_orders dimensions: - drivers.full_name
Generated Query
SELECT "drivers".full_name "drivers__full_name", count("orders".order_id) "orders__total_orders" FROM orders AS "orders" LEFT JOIN drivers AS "drivers" ON "orders".driver_id = "drivers".driver_id GROUP BY 1 ORDER BY 2 DESC
Example 3: Average Delivery Time by Driver
Which drivers complete deliveries fastest?
Query Spec
measures: - orders.avg_delivery_time dimensions: - drivers.full_name filters: - member: orders.order_status operator: equals values: - DELIVERED order: orders.avg_delivery_time: asc
Generated Query
SELECT "drivers".full_name "drivers__full_name", avg("orders".actual_delivery_minutes) "orders__avg_delivery_time" FROM orders AS "orders" LEFT JOIN drivers AS "drivers" ON "orders".driver_id = "drivers".driver_id WHERE ("orders".order_status = 'DELIVERED') GROUP BY 1 ORDER BY 2 DESC
6. Transitive Joins
One of the most powerful features of Cube is its ability to automatically resolve joins across multiple cubes. This capability is called Transitive Joins.
Transitive joins allow Cube to:
· traverse multiple relationships
· automatically discover join paths
· generate multi-table SQL queries
· simplify semantic modeling
This is one of the core reasons semantic layers are so powerful.
Without transitive joins:
· every query would require manual SQL joins
· semantic models would become repetitive
· analytics logic would be harder to maintain
With transitive joins Cube automatically navigates the relationship graph for you.
What Is a Transitive Join?
A transitive join occurs when:
· Cube joins Cube A to Cube B
· then joins Cube B to Cube C
· Even if Cube A does NOT directly join Cube C
Suppose we have orders → restaurants → cuisines, Here:
· orders directly joins restaurants
· restaurants directly joins cuisines
But orders does NOT directly join cuisines, Still Cube can automatically answer "Revenue by cuisine".
Orders Cube
cubes: - name: orders sql_table: orders dimensions: - name: order_id sql: order_id type: number primary_key: true measures: - name: total_revenue sql: final_amount type: sum joins: - name: restaurants relationship: many_to_one sql: "{CUBE}.restaurant_id = {restaurants.restaurant_id}"
Restaurants Cube
cubes: - name: restaurants sql_table: restaurants dimensions: - name: restaurant_id sql: restaurant_id type: number primary_key: true - name: restaurant_name sql: restaurant_name type: string joins: - name: cuisines relationship: many_to_one sql: "{CUBE}.cuisine_id = {cuisines.cuisine_id}"
Cuisines cube
cubes: - name: cuisines sql_table: cuisines dimensions: - name: cuisine_id sql: cuisine_id type: number primary_key: true - name: cuisine_name sql: cuisine_name type: string
Example : Revenue by Cuisine
This is one of the best examples of transitive joins. Here we are going to answer "How much revenue does each cuisine generate?".
Query Spec
measures: - orders.total_revenue dimensions: - cuisines.cuisine_name
Generated Query
SELECT "cuisines".cuisine_name "cuisines__cuisine_name", sum("orders".final_amount) "orders__total_revenue" FROM orders AS "orders" LEFT JOIN restaurants AS "restaurants" ON "orders".restaurant_id = "restaurants".restaurant_id LEFT JOIN cuisines AS "cuisines" ON "restaurants".cuisine_id = "cuisines".cuisine_id GROUP BY 1 ORDER BY 2 DESC
In summary, Semantic layers are rapidly becoming foundational components of modern analytics architectures because they allow organizations to centralize business logic, standardize metrics, and expose reusable analytics-ready models across dashboards, APIs, BI tools, and AI systems. Instead of repeatedly writing fragile SQL scattered across reports and applications, teams can define relationships, joins, measures, dimensions, and governance rules once and reuse them consistently everywhere.
Previous Next Home
No comments:
Post a Comment