Cubes

Cubes represent the tables in your database. Each cube maps to a single table in your data source and contains the business logic — measures, dimensions, joins, and pre-aggregations — that defines how that data can be queried.

See the cube reference for the full list of parameters and configuration options.

Defining a cube

A cube points to a table in your data source using sql_table:

cubes:
  - name: orders
    sql_table: orders

cube(`orders`, {
  sql_table: `orders`
})

You can also use the sql property for more complex queries:

cubes:
  - name: orders
    sql: |
      SELECT *
      FROM orders, line_items
      WHERE orders.id = line_items.order_id

cube(`orders`, {
  sql: `
    SELECT *
    FROM orders, line_items
    WHERE orders.id = line_items.order_id
  `
})

If you're using dbt, see this recipe to streamline defining cubes on top of dbt models.

Cube members

Each cube contains definitions for its members: dimensions, measures, and segments.

Dimensions

Dimensions represent the properties of a single data point — the attributes you group by and filter on, such as status, city, or created_at:

cubes:
  - name: orders
    sql_table: orders

    dimensions:
      - name: id
        sql: id
        type: number
        primary_key: true

      - name: status
        sql: status
        type: string

      - name: created_at
        sql: created_at
        type: time

cube(`orders`, {
  sql_table: `orders`,

  dimensions: {
    id: {
      sql: `id`,
      type: `number`,
      primary_key: true
    },

    status: {
      sql: `status`,
      type: `string`
    },

    created_at: {
      sql: `created_at`,
      type: `time`
    }
  }
})

Time dimensions enable grouping by granularity (year, quarter, month, week, day, hour, minute, second) and are essential for partitioned pre-aggregations.

Measures

Measures represent aggregated values over a set of data points — counts, sums, averages, and custom calculations:

cubes:
  - name: orders
    # ...

    measures:
      - name: count
        type: count

      - name: total_amount
        sql: amount
        type: sum

      - name: average_amount
        sql: amount
        type: avg

cube(`orders`, {
  // ...

  measures: {
    count: {
      type: `count`
    },

    total_amount: {
      sql: `amount`,
      type: `sum`
    },

    average_amount: {
      sql: `amount`,
      type: `avg`
    }
  }
})

Measures can reference other measures to create calculated measures, and you can apply filters to create filtered aggregations like "count of completed orders."

Segments

Segments are predefined filters on a cube. They allow you to define commonly used filter logic once and reuse it across queries:

cubes:
  - name: orders
    # ...

    segments:
      - name: completed
        sql: "{CUBE}.status = 'completed'"

cube(`orders`, {
  // ...

  segments: {
    completed: {
      sql: `${CUBE}.status = 'completed'`
    }
  }
})

Joins

Joins define relationships between cubes, forming the data graph that Cube uses to generate multi-table SQL queries:

cubes:
  - name: orders
    sql_table: orders

    joins:
      - name: users
        relationship: many_to_one
        sql: "{CUBE}.user_id = {users.id}"

      - name: line_items
        relationship: one_to_many
        sql: "{CUBE}.id = {line_items.order_id}"

cube(`orders`, {
  sql_table: `orders`,

  joins: {
    users: {
      relationship: `many_to_one`,
      sql: `${CUBE}.user_id = ${users.id}`
    },

    line_items: {
      relationship: `one_to_many`,
      sql: `${CUBE}.id = ${line_items.order_id}`
    }
  }
})

Cube supports one_to_one, many_to_one, and one_to_many relationship types. See working with joins for advanced patterns like cross-database joins and join direction control.

Pre-aggregations

Pre-aggregations are materialized summaries of cube data that dramatically speed up query execution. Cube automatically matches incoming queries to the best available pre-aggregation:

cubes:
  - name: orders
    # ...

    pre_aggregations:
      - name: main
        measures:
          - count
          - total_amount
        dimensions:
          - status
        time_dimension: created_at
        granularity: day

cube(`orders`, {
  // ...

  pre_aggregations: {
    main: {
      measures: [count, total_amount],
      dimensions: [status],
      time_dimension: created_at,
      granularity: `day`
    }
  }
})

Pre-aggregations support partitioning by time and incremental refreshes to keep materialized data up-to-date efficiently.

Designing effective cubes

One cube per entity

Map each cube to a single business entity — orders, users, products, line_items. Use joins to connect them rather than creating wide cubes with data from multiple tables.

Keep naming consistent

Use clear, consistent naming for members. Dimensions should describe attributes (status, city, created_at), and measures should describe aggregations (count, total_revenue, average_order_value). Add description and title for user-friendly display.

Control visibility

Use public to hide cubes that should not be directly queried by end-users. In most data models, cubes are internal building blocks and views are the public interface:

cubes:
  - name: base_orders
    public: false
    sql_table: orders

    # ...

cube(`base_orders`, {
  public: false,
  sql_table: `orders`,

  // ...
})

Scale with extension and polymorphism

When cubes share common members, use extends to avoid duplication. For data models with many similar entities, polymorphic cubes let you define a base cube and specialize it per entity.

Next steps

• See the cube reference for the full list of parameters
• Learn about views to expose cubes to end-users
• Explore calculated measures for derived metrics
• Use the Semantic Model IDE to develop cubes interactively