Joins

Joins define relationships between cubes, allowing Cube to automatically generate multi-table SQL queries when views combine data from multiple cubes.

Joins define how cubes connect to each other. When a view includes members from multiple cubes, Cube uses these relationships to automatically generate SQL JOIN clauses — so end-users can explore data across tables without writing SQL.

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

Relationship types

Cube supports three relationship types: one_to_one, one_to_many, and many_to_one. The relationship type determines which table becomes the left side of the LEFT JOIN in the generated SQL.

Consider two cubes, orders and customers. An order belongs to one customer, but a customer can have many orders:

YAML

cubes:
  - name: orders
    sql_table: orders

    joins:
      - name: customers
        relationship: many_to_one
        sql: "{CUBE}.customer_id = {customers.id}"

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

      - name: status
        sql: status
        type: string

    measures:
      - name: count
        type: count

  - name: customers
    sql_table: customers

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

      - name: company
        sql: company
        type: string

The many_to_one join on orders means: many orders belong to one customer. When a view includes members from both cubes, Cube generates SQL with orders on the left and customers on the right:

SELECT
  "orders".status,
  "customers".company,
  COUNT("orders".id)
FROM orders AS "orders"
LEFT JOIN customers AS "customers"
  ON "orders".customer_id = "customers".id
GROUP BY 1, 2

Because orders is on the left side of the LEFT JOIN, all orders are preserved — including guest checkouts with no matching customer.

As a rule of thumb, define joins on the fact table (e.g., orders) pointing toward the dimension table (e.g., customers) using many_to_one. This ensures the fact table is always the base of the query, preserving all its rows.

Many-to-many relationships

A many-to-many relationship requires an associative (junction) table. For example, posts and topics are connected through a post_topics table:

Model this with an associative cube, chaining the joins so they flow in one direction (posts → post_topics → topics):

YAML

cubes:
  - name: posts
    sql_table: posts

    joins:
      - name: post_topics
        relationship: one_to_many
        sql: "{CUBE}.id = {post_topics.post_id}"

  - name: post_topics
    sql_table: post_topics

    joins:
      - name: topics
        relationship: many_to_one
        sql: "{CUBE}.topic_id = {topics.id}"

    dimensions:
      - name: id
        sql: "CONCAT({CUBE}.post_id, {CUBE}.topic_id)"
        type: string
        primary_key: true

  - name: topics
    sql_table: topics

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

      - name: name
        sql: name
        type: string

A view can then expose this through the join_path:

views:
  - name: posts_with_topics
    cubes:
      - join_path: posts
        includes:
          - title
          - count

      - join_path: posts.post_topics.topics
        prefix: true
        includes:
          - name

Direction of joins

All joins are directed. They flow from the source cube (where the join is defined) to the target cube (the one referenced). Cube places the source cube on the left side of the LEFT JOIN and the target on the right.

This matters because the left table preserves all its rows, while the right table contributes matching rows or NULL. The direction you choose affects which records appear in the result set.

For example, if orders defines a many_to_one join to customers:

• orders is the base → all orders are preserved, even guest checkouts
• customers without orders won’t appear

If instead customers defined a one_to_many join to orders:

• customers is the base → all customers are preserved, even those without orders
• Guest checkout orders (with no matching customer) won’t appear

Using views to control direction

Views let you control which join path is followed via the join_path parameter. This is the recommended way to handle cases where you need different join directions for different use cases:

YAML

cubes:
  - name: orders
    sql_table: orders

    joins:
      - name: customers
        sql: "{CUBE}.customer_id = {customers.id}"
        relationship: many_to_one

    measures:
      - name: count
        type: count

      - name: total_revenue
        sql: revenue
        type: sum

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

  - name: customers
    sql_table: customers

    joins:
      - name: orders
        sql: "{CUBE}.id = {orders.customer_id}"
        relationship: one_to_many

    measures:
      - name: count
        type: count

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

      - name: name
        sql: name
        type: string

Now you can create two views for two different analytical needs:

YAML

views:
  - name: revenue_per_customer
    description: All orders with customer details. Includes guest checkouts.
    cubes:
      - join_path: orders
        includes:
          - count
          - total_revenue

      - join_path: orders.customers
        includes:
          - name

  - name: customer_activity
    description: All customers with their order activity. Includes customers without orders.
    cubes:
      - join_path: customers
        includes:
          - name
          - count

      - join_path: customers.orders
        prefix: true
        includes:
          - count
          - total_revenue

The revenue_per_customer view follows the orders → customers path, so all orders are preserved. The customer_activity view follows customers → orders, so all customers are preserved.

Diamond subgraphs

A diamond subgraph occurs when there’s more than one join path between two cubes — for example, users.schools.countries and users.employers.countries. This can lead to ambiguous query generation.

Views resolve this ambiguity by specifying the exact join_path for each included cube. For example, if cube a joins to both b and c, and both b and c join to d, a view can specify which path to follow:

views:
  - name: a_with_d_via_b
    cubes:
      - join_path: a
        includes: "*"

      - join_path: a.b.d
        prefix: true
        includes:
          - value

  - name: a_with_d_via_c
    cubes:
      - join_path: a
        includes: "*"

      - join_path: a.c.d
        prefix: true
        includes:
          - value

Each view follows a specific, unambiguous path through the data graph.

Join paths in calculated members

When referencing a member of another cube in a calculated member, you can use a join path to specify the exact route. This uses dot-separated cube names:

YAML

cubes:
  - name: orders
    # ...

    dimensions:
      - name: customer_country
        sql: "{customers.country}"
        type: string

      - name: shipping_country
        sql: "{shipping_addresses.country}"
        type: string

Troubleshooting

Can't find join path

The error Can't find join path to join 'cube_a', 'cube_b' means the cubes included in a view or query can’t be connected through the defined joins.

Check that:

• Joins are defined with the correct direction
• There is a continuous path from the source cube to the target cube
• You’re using the join_path parameter in views to specify the exact path

Primary key is required when join is defined

Cube uses primary keys to avoid fanouts — when rows get duplicated during joins and aggregates are over-counted. Define a primary key dimension in every cube that participates in joins.

If your data doesn’t have a natural primary key, create a composite one:

cubes:
  - name: events
    # ...

    dimensions:
      - name: composite_key
        sql: CONCAT(column_a, '-', column_b, '-', column_c)
        type: string
        primary_key: true