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, hierarchies, segments, joins between cubes, pre-aggregations, and data access policies.

YAML

JavaScript

cube(`users`, {
  sql_table: `users`,
 
  joins: {
    organizations: {
      relationship: `many_to_one`,
      sql: `${users.organization_id} = ${organizations.id}`
    }
  },
 
  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  },
 
  dimensions: {
    organization_id: {
      sql: `organization_id`,
      type: `number`,
      primary_key: true
    },
 
    created_at: {
      sql: `created_at`,
      type: `time`
    },
 
    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.

YAML

JavaScript

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

`sql_alias`

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

YAML

JavaScript

cube(`order_facts_about_literally_everything_in_the_world`, {
  sql_table: `orders`,
  sql_alias: `order_facts`
})

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

`extends`

You can use the extends parameter to 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:

YAML

JavaScript

cube(`order_facts`, {
  sql_table: `orders`,
 
  measures: {
    count: {
      type: `count`,
      sql: `id`
    }
  }
})
 
cube(`extended_order_facts`, {
  extends: order_facts,
 
  measures: {
    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`
    }
  }
})

`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:

YAML

JavaScript

cube(`order_facts`, {
  data_source: `prod_db`,
  sql_table: `orders`
})

`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.

YAML

JavaScript

cube(`orders`, {
  sql: `SELECT * FROM orders`
})

You can also reference other cubes' SQL statements for code reuse using the {cube.sql()} function:

YAML

JavaScript

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

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

`sql_table`

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

YAML

JavaScript

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

`title`

Use the title parameter 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.

YAML

JavaScript

cube(`orders`, {
  sql_table: `orders`,
  title: `Product 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.

YAML

JavaScript

cube(`orders`, {
  sql_table: `orders`,
  title: `Product Orders`,
  description: `All orders-related information`
})

`public`

The public parameter 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.

YAML

JavaScript

cube(`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 the refresh_key parameter. 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.

YAML

JavaScript

cube(`order_facts`, {
  sql_table: `orders`,
 
  // With this refresh_key Cube will only refresh the data if
  // the value of previous MAX(updated_at_timestamp) changed.
  // By default Cube will check this refreshKey every 10 seconds
  refresh_key: {
    sql: `SELECT MAX(updated_at_timestamp) FROM orders`
  }
})

You can use interval-based refresh_key. For example:

YAML

JavaScript

cube(`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 parameter. It takes precedence over the query timezone.

For example:

YAML

JavaScript

cube(`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.

Cube supports two different formats of CRON expressions: standard and advanced with support for seconds.

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)

`meta`

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

YAML

JavaScript

cube(`orders`, {
  sql_table: `orders`,
  title: `Product Orders`,
  meta: {
    any: `value`
  }
})

`pre_aggregations`

The pre_aggregations parameter is used to configure pre-aggregations.

`joins`

The joins parameter is used to configure joins.

`dimensions`

The dimensions parameter is used to configure dimensions.

`hierarchies`

The hierarchies parameter is used to configure hierarchies.

`segments`

The segments parameter is used to configure segments.

`measures`

The measures parameter is used to configure measures.

`access_policy`

The access_policy parameter is used to configure data access policies.

Execution environment (JavaScript models)Views