Designing Reusable Analytics Filters Using Cube Segments

  

Modern analytics systems often repeat the same filtering logic across dashboards, APIs, reports, and AI applications.

 

For example:

·      Delivered orders

·      Cancelled orders

·      Premium customers

·      Late deliveries

·      High-value orders

 

If every analyst rewrites these filters manually, the logic becomes duplicated, inconsistent, and difficult to maintain.

 

This is where Cube Segments become extremely useful.

 

Segments allow you to define reusable business filters once inside your semantic layer and reuse them everywhere.

 

In this post, we’ll learn:

 

·      What Cube segments are

·      Why they matter

·      When to use them

·      When NOT to use them

·      How to implement them using a realistic food delivery platform schema

·      Real-world analytics examples using PostgreSQL and Cube

 

We’ll use a production-style PostgreSQL schema containing:

 

·      Customers

·      Orders

·      Restaurants

·      Delivery events

·      Payments

·      Refunds

·      Driver payouts

·      Promotions

 

This makes the examples much closer to real enterprise analytics systems.

 

1. What Are Cube Segments?

In analytics systems, the same filtering conditions are often reused repeatedly across:

 

·      Dashboards

·      KPI reports

·      Embedded analytics

·      APIs

·      AI-generated queries

·      Operational monitoring

·      Data notebooks

 

For example:

 

·      Delivered orders

·      Cancelled orders

·      Premium customers

·      Late deliveries

·      High-value transactions

 

Without a semantic layer, teams usually rewrite these conditions repeatedly using raw SQL:

WHERE order_status = 'DELIVERED'

   

This creates several problems:

·      SQL duplication: Same logic repeated everywhere

·      Inconsistent definitions: Different teams define metrics differently

·      Hard maintenance: Business rule changes require updating many dashboards 

·      BI drift: Tableau, Superset, Power BI may all implement logic differently

·      AI inconsistency: AI-generated queries may produce different business logic

 

1.1 What Is a Segment?

A segment is a named reusable filter defined inside a Cube model. Think of a segment as a reusable WHERE clause with a business-friendly name.

 

Instead of rewriting SQL filters repeatedly, you define them once inside the semantic layer and reuse them everywhere.

 

Suppose we have a food delivery platform. The orders table contains:

 

| order_id | order_status | final_amount |
| -------- | ------------ | ------------ |
| 1        | DELIVERED    | 850          |
| 2        | CANCELLED    | 420          |
| 3        | DELIVERED    | 1200         |

   

We frequently need analytics only for delivered orders. Instead of rewriting:

 

WHERE order_status = 'DELIVERED'

we define a segment once.

 

cubes:
  - name: orders

    sql_table: public.orders

    description: Food delivery orders fact table

    measures:

      - name: count
        type: count
        description: Total number of orders

      - name: total_revenue
        sql: final_amount
        type: sum
        description: Total order revenue

      - name: average_order_value
        sql: final_amount
        type: avg
        description: Average order value

    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: ordered_at
        sql: ordered_at
        type: time

    segments:

      - name: delivered_orders
        description: Orders successfully delivered to customers
        sql: "{CUBE}.order_status = 'DELIVERED'"

   

Let's understand each part in the segment definition.

 

segments:

  - name: delivered_orders
    sql: "{CUBE}.order_status = 'DELIVERED'"

   

 

Property	Meaning
segments	Section where reusable filters are defined
name	Business-friendly reusable filter name
sql	SQL condition injected into generated queries
{CUBE}	Placeholder automatically replaced with cube table alias

 

What Does {CUBE} Mean?

Cube dynamically generates SQL queries. {CUBE} is replaced with the actual SQL table alias.

 

For example:

{CUBE}.order_status becomes orders.order_status

This abstraction makes Cube models portable and maintainable.

 

1.2 Query Specification Using the Segment

Now suppose a dashboard wants "Total revenue from delivered orders only". Instead of manually writing filters, the query simply references the segment.

measures:
  - orders.total_revenue
segments:
  - orders.delivered_orders

How Cube Interprets This Query?

Cube internally understands:

• orders.total_revenue: Aggregate final_amount using SUM
• orders.delivered_orders: Apply predefined delivered filter

Generated SQL

Cube generates SQL similar to:

SELECT
      sum("orders".final_amount) "orders__total_revenue"
    FROM
      public.orders AS "orders"  WHERE ("orders".order_status = 'DELIVERED')

   

Notice something important that the dashboard never explicitly wrote "WHERE order_status = 'DELIVERED'". The semantic layer injected it automatically. This is the power of reusable business logic.

 

1.3 Why This Is Powerful

Imagine your company has:

 

·      40 dashboards

·      15 APIs

·      10 AI applications

·      5 data science notebooks

 

Without segments every system rewrites:

 

WHERE order_status = 'DELIVERED' 

   

If business changes the logic later:

 

WHERE order_status IN ('DELIVERED', 'COMPLETED')

   

You must update dozens of places. With segments, you update only one place.

 

segments:
  - name: delivered_orders
    sql: "{CUBE}.order_status IN ('DELIVERED', 'COMPLETED')"

   

Every dashboard and API instantly inherits the new logic. This is one of the biggest advantages of semantic layers.

 

Note:

·      A segment is NOT a metric.

·      A segment is NOT a dimension.

·      A segment is:

o   a reusable filter

o   reusable business logic

o   centralized WHERE condition

 

Example:

·      WHERE clause is a Segment

·      SUM() is a Measure

·      GROUP BY column is a Dimension

 

2. Examples using orders cube

We are using following cube spec for orders.

cubes:
  - name: orders

    sql_table: public.orders

    measures:

      - name: count
        type: count

      - name: total_revenue
        sql: final_amount
        type: sum

      - name: avg_delivery_time
        sql: actual_delivery_minutes
        type: avg

    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: customer_rating
        sql: customer_rating
        type: number

      - name: estimated_delivery_minutes
        sql: estimated_delivery_minutes
        type: number

      - name: actual_delivery_minutes
        sql: actual_delivery_minutes
        type: number

      - name: ordered_at
        sql: ordered_at
        type: time

   

Example 1: Orders successfully delivered to customers.

 

Segment Definition

segments:

  - name: delivered_orders
    description: Successfully delivered orders
    sql: "{CUBE}.order_status = 'DELIVERED'"

   

Query Spec

measures:
  - orders.total_revenue
segments:
  - orders.delivered_orders

Generated SQL

SELECT
      sum("orders".final_amount) "orders__total_revenue"
    FROM
      public.orders AS "orders"  WHERE ("orders".order_status = 'DELIVERED')

   

Example 2: Cancelled Orders Segment

Cancellation analytics are critical in food delivery systems.

 

Segment Definition

segments:

  - name: cancelled_orders
    description: Orders cancelled before completion
    sql: "{CUBE}.order_status = 'CANCELLED'"

   

Query Spec

measures:
  - orders.count
segments:
  - orders.cancelled_orders

   

Generated SQL

SELECT
      count("orders".order_id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".order_status = 'CANCELLED')

   

Example 3: Late Deliveries Segment

This is where segments become very powerful. Instead of simple equality filters, we now use business logic.

 

Segment Definition

segments:

  - name: late_deliveries
    description: Orders delivered after estimated time
    sql: |
      {CUBE}.actual_delivery_minutes >
      {CUBE}.estimated_delivery_minutes

   

Query Spec

measures:
  - orders.count
segments:
  - orders.late_deliveries

   

Generated Query

SELECT
      count("orders".order_id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".actual_delivery_minutes >
"orders".estimated_delivery_minutes
)

Example 4: High-Value Orders Segment

Many businesses track premium transactions.

 

Segment Definition

segments:

  - name: high_value_orders
    description: Orders above 1000 INR
    sql: "{CUBE}.final_amount >= 1000"

   

Query Spec

measures:
  - orders.total_revenue
segments:
  - orders.high_value_orders

   

Generated Query

SELECT
      sum("orders".final_amount) "orders__total_revenue"
    FROM
      public.orders AS "orders"  WHERE ("orders".final_amount >= 1000)

   

Example 5: Poor Customer Ratings Segment

Customer experience analytics often require reusable filters.

 

Segment Definition

segments:

  - name: poorly_rated_orders
    description: Orders with low customer ratings
    sql: "{CUBE}.customer_rating <= 2"

   

Query Spec

measures:
  - orders.count
segments:
  - orders.poorly_rated_orders

   

Generated Query

SELECT
      count("orders".order_id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".customer_rating <= 2)

   

Example 6: Payment Failure Segment

Operational finance teams frequently monitor failed payments.

 

Segment Definition

segments:

  - name: failed_payments
    description: Orders with failed payments
    sql: "{CUBE}.payment_status = 'FAILED'"

   

Query Spec

measures:
  - orders.count
segments:
  - orders.failed_payments

   

Generated Query

SELECT
      count("orders".order_id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".payment_status = 'FAILED')

   

Example 7: Complex Operational Segment

Orders are operationally problematic if cancelled OR delayed by more than 20 minutes

 

Segment Definition

segments:

  - name: operational_issues
    description: Orders with major operational problems
    sql: |
      {CUBE}.order_status = 'CANCELLED'
      OR (
        {CUBE}.actual_delivery_minutes >
        {CUBE}.estimated_delivery_minutes + 20
      )

Query Spec

measures:
  - orders.count
segments:
  - orders.operational_issues

Generated Query

SELECT
      count("orders".order_id) "orders__count"
    FROM
      public.orders AS "orders"  WHERE ("orders".order_status = 'CANCELLED'
OR (
"orders".actual_delivery_minutes >
"orders".estimated_delivery_minutes + 20
)
)

3. Examples using delivery_events table

One of the most powerful parts of our food delivery platform schema is the delivery_events table.

 

Unlike traditional transactional tables such as orders, the delivery_events table behaves like an event stream.

 

This enables:

·      real-time analytics

·      operational monitoring

·      live tracking dashboards

·      logistics intelligence

·      driver movement analytics

·      SLA monitoring

·      event-driven AI analytics

 

This is where Cube segments become extremely valuable.

 

CREATE TABLE public.delivery_events (
	event_id bigserial NOT NULL,
	order_id int8 NOT NULL,
	driver_id int8 NULL,
	event_type varchar(100) NOT NULL,
	event_timestamp timestamp NOT NULL,
	latitude numeric(10, 6) NULL,
	longitude numeric(10, 6) NULL,
	event_metadata jsonb NULL,
	created_at timestamp DEFAULT CURRENT_TIMESTAMP NULL,
	CONSTRAINT delivery_events_pkey PRIMARY KEY (event_id)
);

   

Let’s create spec for delivery_events.

cubes:
  - name: delivery_events

    sql_table: public.delivery_events

    description: Real-time delivery lifecycle event stream

    measures:

      - name: count
        type: count

    dimensions:

      - name: event_id
        sql: event_id
        type: number
        primary_key: true

      - name: order_id
        sql: order_id
        type: number

      - name: driver_id
        sql: driver_id
        type: number

      - name: event_type
        sql: event_type
        type: string

      - name: latitude
        sql: latitude
        type: number

      - name: longitude
        sql: longitude
        type: number

      - name: event_timestamp
        sql: event_timestamp
        type: time

    segments:

      - name: driver_wait_events
        description: Events where drivers are waiting
        sql: "{CUBE}.event_type = 'DRIVER_WAITING'"

   

Example 1: Driver Waiting Events

This segment identifies events where:

 

·      driver arrived

·      restaurant delayed preparation

·      operational inefficiency occurred

 

Segment Definition

segments:

  - name: driver_wait_events
    description: Events where drivers are waiting
    sql: "{CUBE}.event_type = 'DRIVER_WAITING'"

   

Query Spec

measures:
  - delivery_events.count
segments:
  - delivery_events.driver_wait_events

   

Generated Query

SELECT
      count("delivery_events".event_id) "delivery_events__count"
    FROM
      public.delivery_events AS "delivery_events"  WHERE ("delivery_events".event_type = 'DRIVER_WAITING')

   

Example 2: Failed Delivery Segment

Now let’s create a more advanced reusable operational definition.

 

A delivery is operationally problematic if:

 

order cancelled

OR

customer unreachable

 

Segment Definition

segments:

  - name: failed_delivery_events
    description: Operational delivery failure events
    sql: |
      {CUBE}.event_type IN (
        'ORDER_CANCELLED',
        'CUSTOMER_NOT_RESPONDING'
      )

Query Spec

measures:
  - delivery_events.count
segments:
  - delivery_events.failed_delivery_events

Generated Query

SELECT
      count("delivery_events".event_id) "delivery_events__count"
    FROM
      public.delivery_events AS "delivery_events"  WHERE ("delivery_events".event_type IN (
'ORDER_CANCELLED',
'CUSTOMER_NOT_RESPONDING'
)
)

   

4. When NOT to Use Segments

One of the biggest beginner mistakes in Cube modeling is trying to convert every filter into a segment.

 

At first, segments look extremely convenient because they let you reuse filtering logic.

 

So beginners often start creating segments like:

 

segments:
  - name: bangalore_orders
    sql: "{CUBE}.city = 'Bangalore'"

   

or:

 

segments:
  - name: hyderabad_orders
    sql: "{CUBE}.city = 'Hyderabad'"

   

or:

 

segments:
  - name: mumbai_orders
    sql: "{CUBE}.city = 'Mumbai'"

   

This looks harmless initially. But in real enterprise systems, this becomes a serious modeling problem.

 

4.1 The Core Rule

Use segments for:

·      reusable business logic

·      operational definitions

·      complex filters

·      standardized KPIs

 

Do NOT use segments for:

·      highly dynamic filter values, use dimension filters in Query Spec.

·      user-driven exploration

·      frequently changing values

·      ad hoc slicing

 

 

Dimension filter in Query spec example

measures:
  - orders.total_revenue
filters:
  - member: orders.city
    operator: equals
    values:
      - Bangalore

   

4.2 Another Bad Example

segments:
  - name: orders_above_500
    sql: "{CUBE}.final_amount > 500"

  - name: orders_above_1000
    sql: "{CUBE}.final_amount > 1000"

  - name: orders_above_2000
    sql: "{CUBE}.final_amount > 2000"

   

This is usually a bad design. Why?

Because users may want:

 

700

1500

2500

custom ranges

 

Better solution, use filters dynamically in Query Spec

 

measures:
  - orders.count
filters:
  - member: orders.final_amount
    operator: gt
    values:
      - '1000'

Before creating a segment, it is important to ask whether you are defining reusable business logic or simply hardcoding a filter value. This distinction is one of the most important concepts in semantic modeling. If the condition represents a meaningful business concept that will be reused consistently across dashboards, APIs, reports, and AI queries such as late deliveries, failed payments, or operational issues, then it is a good candidate for a segment.

 

However, if the condition is only a dynamic filter value chosen by users at runtime, such as a city name, date range, cuisine, or amount threshold, then dimensions and query filters are a much better fit. Segments should represent stable, reusable business definitions, while dimensions and filters should handle flexible exploratory filtering and user-driven analysis.

 

5. Final Thoughts

Segments are one of the most powerful features in Cube semantic modeling because they allow organizations to centralize and standardize reusable business logic inside the semantic layer. Instead of repeatedly rewriting the same SQL filters across dashboards, APIs, notebooks, and AI-generated queries, teams can define business rules once and reuse them everywhere consistently. This not only reduces SQL duplication, but also simplifies analytics development, improves governance, and ensures that all consumers of data are working with the same definitions and KPIs.

 

Throughout this guide, we used a realistic food delivery platform schema containing orders, delivery events, payments, refunds, drivers, restaurants, and promotions. Using a real-world domain makes it much easier to understand how segments are applied in enterprise analytics systems compared to overly simplified toy examples. Real production systems frequently require reusable operational filters such as late deliveries, failed payments, SLA breaches, refund scenarios, or delivery event classifications. These are exactly the kinds of business definitions that segments are designed to model.

 

We also explored an equally important concept: knowing when not to use segments. Not every filter belongs in the semantic layer as a reusable segment. Dynamic user-driven filters such as city names, date ranges, cuisines, or arbitrary thresholds are usually better modeled using dimensions and query filters. Strong semantic modeling is not just about creating segments—it is about carefully deciding which logic should become reusable business meaning and which logic should remain dynamic and exploratory.

 

As your semantic layer grows, segments become foundational building blocks for scalable analytics architecture. They help create a shared business vocabulary across engineering, analytics, BI, and AI systems. In modern data platforms where dashboards, APIs, embedded analytics, and AI agents all consume the same semantic layer, reusable segments provide the consistency and governance necessary for trustworthy analytics at scale.

 

Ultimately, segments transform raw SQL filters into governed business concepts. That is one of the core reasons semantic layers like Cube are becoming increasingly important in modern analytics and AI-driven data architectures.

 

 

Previous                                                    Next                                                    Home