Cubes

A cube represents a table of data in Cube.

Cubes are typically declared in separate files with one cube per file. Within each cube are definitions of measures, dimensions, segments, joins between cubes, and pre-aggregations.

cubes:
  - name: users
    sql_table: users
 
    joins:
      - name: organizations
        relationship: many_to_one
        sql: "{CUBE.organization_id} = {organizations.id}"
 
    measures:
      - name: count
        type: count
        sql: id
 
    dimensions:
      - name: organization_id
        sql: organization_id
        type: number
        primary_key: true
 
      - name: created_at
        sql: created_at
        type: time
 
      - name: country
        sql: country
        type: string

Parameters

name

The name parameter serves as the identifier of a cube. It must be unique among all cubes and views within a deployment and follow the naming conventions.

cubes:
  - name: orders
    sql_table: orders

data_source

Each cube can have its own data_source name to support scenarios where data should be fetched from multiple databases. The value of the data_source parameter will be passed to the driverFactory() function as part of the context parameter. By default, each cube has a default value for its data_source; to override it you can use:

cubes:
  - name: order_facts
    data_source: prod_db
    sql_table: orders

description

This parameter provides a human-readable description of a cube. When applicable, it will be displayed in Playground and exposed to data consumers via APIs and integrations.

A description can give a hint both to your team and end users, making sure they interpret the data correctly.

cubes:
  - name: orders
    sql_table: orders
    title: Product Orders
    description: All orders-related information

meta

Custom metadata. Can be used to pass any information to the frontend.

cubes:
  - name: orders
    sql_table: orders
    title: Product Orders
    meta:
      any: value
 

extends

You can extend cubes in order to reuse all declared members of a cube. In the example below, extended_order_facts will reuse the sql and count measures from order_facts:

cubes:
  - name: order_facts
    sql_table: orders
 
    measures:
      - name: count
        type: count
        sql: id
 
  - name: extended_order_facts
    extends: order_facts
 
    measures:
      - name: double_count
        type: number
        sql: "{count} * 2"

You can also omit the cube name while defining a cube in JavaScript. This way, Cube doesn't register this cube globally; instead it returns a reference which you can use while combining cubes. It makes sense to use it for dynamic data model generation and reusing with extends. Previous example without defining order_facts cube globally:

const order_facts = cube({
  sql: `orders`,
 
  measures: {
    count: {
      type: `count`,
      sql: `id`,
    },
  },
});
 
cube(`extended_order_facts`, {
  extends: order_facts,
 
  measures: {
    double_count: {
      type: `number`,
      sql: `${count} * 2`,
    },
  },
});

public

The public property is used to manage the visibility of a cube. Valid values for public are true and false. When set to false, this cube cannot be queried through the API. Defaults to true.

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

To learn more about using public to control visibility based on security context, read the Controlling access to cubes and views recipe.

refresh_key

Cube's caching layer uses refresh_key queries to get the current version of content for a specific cube. If a query result changes, Cube will invalidate all queries that rely on that cube.

The default values for refresh_key are

• every: '2 minute' for BigQuery, Athena, Snowflake, and Presto.
• every: '10 second' for all other databases.

Refresh key of a query is a concatenation of all cubes refresh keys involved in query. For rollup queries pre-aggregation table name is used as a refresh key.

You can set up a custom refresh check SQL by changing refresh_key property. Often, a MAX(updated_at_timestamp) for OLTP data is a viable option, or examining a metadata table for whatever system is managing the data to see when it last ran. timestamp in that case.

cubes:
  - name: order_facts
    sql_table: orders
    refresh_key:
      sql: SELECT MAX(updated_at_timestamp) FROM orders

You can use interval-based refresh_key. For example:

cubes:
  - name: order_facts
    sql_table: orders
    refresh_key:
      every: 1 hour

every - can be set as an interval with granularities second, minute, hour, day, and week or accept CRON string with some limitations. If you set every as CRON string, you can use the timezone property.

For example:

cubes:
  - name: order_facts
    sql_table: orders
 
    refresh_key:
      every: 30 5 * * 5
      timezone: America/Los_Angeles

every can accept only equal time intervals - so "Day of month" and "month" intervals in CRON expressions are not supported.

Such refresh_key is just a syntactic sugar over refresh_key SQL. It's guaranteed that refresh_key change it's value at least once during every interval. It will be converted to appropriate SQL select which value will change over time based on interval value. Values of interval based refresh_key are tried to be checked ten times within defined interval but not more than once per 1 second and not less than once per 5 minute. For example if interval is 10 minute it's refreshKeyRenewalThreshold will be 60 seconds and generated refresh_key SQL (Postgres) would be:

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 600)

For 5 second interval refreshKeyRenewalThreshold will be just 1 second and SQL will be:

SELECT FLOOR(EXTRACT(EPOCH FROM NOW()) / 5)

Supported cron formats

• Standard cron syntax

*    *    *    *    *
┬    ┬    ┬    ┬    ┬
│    │    │    │    |
│    │    │    │    └ day of week (0 - 7) (0 or 7 is Sun)
│    │    │    └───── month (1 - 12)
│    │    └────────── day of month (1 - 31, L)
│    └─────────────── hour (0 - 23)
└──────────────────── minute (0 - 59)

• Advanced cron format with support for seconds

*    *    *    *    *    *
┬    ┬    ┬    ┬    ┬    ┬
│    │    │    │    │    |
│    │    │    │    │    └ day of week (0 - 7) (0 or 7 is Sun)
│    │    │    │    └───── month (1 - 12)
│    │    │    └────────── day of month (1 - 31, L)
│    │    └─────────────── hour (0 - 23)
│    └──────────────────── minute (0 - 59)
└───────────────────────── second (0 - 59, optional)

sql

The sql parameter specifies the SQL that will be used to generate a table that will be queried by a cube. It can be any valid SQL query, but usually it takes the form of a SELECT * FROM my_table query. Please note that you don't need to use GROUP BY in a SQL query on the cube level. This query should return a plain table, without aggregations.

cubes:
  - name: orders
    sql: SELECT * FROM orders

With JavaScript models, you can also reference other cubes' SQL statements for code reuse:

cube(`companies`, {
  sql: `
    SELECT users.company_name, users.company_id
    FROM ${users.sql()} AS users
  `,
});

It is recommended to prefer the sql_table property over the sql property for all cubes that are supposed to use queries like this: SELECT * FROM table.

sql_table

The sql_table property is used as a concise way for defining a cube that uses a query like this: SELECT * FROM table. Instead of using the sql property, use sql_table with the table name that this cube will query.

cubes:
  - name: orders
    sql_table: public.orders

sql_alias

Use sql_alias when auto-generated cube alias prefix is too long and truncated by DB such as Postgres:

cubes:
  - name: order_facts
    sql_table: orders
    sql_alias: ofacts

It'll generate aliases for members such as ofacts__count. sql_alias affects all member names including pre-aggregation table names.

title

Use title to change the display name of the cube. By default, Cube will humanize the cube's name, so for instance, users_orders would become Users Orders. If default humanizing doesn't work in your case, please use the title parameter. It is highly recommended to give human readable names to your cubes. It will help everyone on a team better understand the data structure and will help maintain a consistent set of definitions across an organization.

cubes:
  - name: orders
    sql_table: orders
    title: Product Orders