> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-claude-gallant-ramanujan-r0eu34.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Context variables

> Context variables like CUBE, FILTER_PARAMS, SQL_UTILS, and COMPILE_CONTEXT are available within cube definitions for dynamic SQL and model generation.

* [`CUBE`](#cube) for [referencing members][ref-syntax-references] of the same cube.
* [`FILTER_PARAMS`](#filter_params) and [`FILTER_GROUP`](#filter_group) for optimizing generated SQL queries.
* [`SQL_UTILS`](#sql_utils) for time zone conversion.
* [`COMPILE_CONTEXT`](#compile_context) for creation of [dynamic data models][ref-dynamic-data-models].

## `CUBE`

You can use the `CUBE` context variable to reference columns or members of
the current cube so you don't have to repeat the its name over and over.

It helps [reference members][ref-syntax-references] while keeping the data
model code DRY and easy to maintain.

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: users
      sql_table: users

      joins:
        - name: contacts
          sql: "{CUBE}.contact_id = {contacts.id}"
          relationship: one_to_one

      dimensions:
        - name: id
          sql: "{CUBE}.id"
          type: number
          primary_key: true

        - name: name
          sql: "COALESCE({CUBE.name}, {contacts.name})"
          type: string

    - name: contacts
      sql_table: contacts

      dimensions:
        - name: id
          sql: "{CUBE}.id"
          type: number
          primary_key: true

        - name: name
          sql: "{CUBE}.name"
          type: string









  ```

  ```javascript title="JavaScript" theme={null}
  cube(`users`, {
    sql_table: `users`,

    joins: {
      contacts: {
        sql: `${CUBE}.contact_id = ${contacts.id}`,
        relationship: `one_to_one`
      }
    },

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

      name: {
        sql: `COALESCE(${CUBE}.name, ${contacts.name})`,
        type: `string`
      }
    }
  })

  cube(`contacts`, {
    sql_table: `contacts`,

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

      name: {
        sql: `${CUBE}.name`,
        type: `string`
      }
    }
  })
  ```
</CodeGroup>

## `FILTER_PARAMS`

`FILTER_PARAMS` context variable allows you to use [filter][ref-query-filter]
values from the Cube query during SQL generation.

This is useful for hinting your database optimizer to use a specific index
or filter out partitions or shards in your cloud data warehouse so you won't
be billed for scanning those. It can also be useful for constructing [links][ref-links].

<Warning>
  Heavy usage of `FILTER_PARAMS` is considered a bad practice. It usually
  leads to hard-to-maintain data models. Good rule of thumb is to use
  `FILTER_PARAMS` only for predicate pushdown performance optimizations.

  If you find yourself relying a lot on `FILTER_PARAMS`, it might mean that
  you need to rethink your approach to data modeling and potentially move
  some transformations upstream. Also, you might reconsider the choice of the
  data source.
</Warning>

`FILTER_PARAMS` has to be a top-level expression in `WHERE` and it has the
following syntax:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: cube_name
      sql: |
        SELECT *
        FROM table
        WHERE {FILTER_PARAMS.cube_name.member_name.filter(sql_expression)}

      dimensions:
        - name: member_name
          # ...



  ```

  ```javascript title="JavaScript" theme={null}
  cube(`cube_name`, {
    sql: `
      SELECT *
      FROM table
      WHERE ${FILTER_PARAMS.cube_name.member_name.filter(sql_expression)}
    `,

    dimensions: {
      member_name: {
        // ...
      }
    }
  })
  ```
</CodeGroup>

The `filter()` function accepts `sql_expression`, which could be either
a string or a function returning a string.

### Example with string

See the example below for the case when a string is passed to `filter()`:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: order_facts
      sql: |
        SELECT *
        FROM orders
        WHERE {FILTER_PARAMS.order_facts.date.filter('date')}

      measures:
        - name: count
          type: count

      dimensions:
        - name: date
          sql: date
          type: time





  ```

  ```javascript title="JavaScript" theme={null}
  cube(`order_facts`, {
    sql: `
      SELECT *
      FROM orders
      WHERE ${FILTER_PARAMS.order_facts.date.filter('date')}
    `,

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

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    }
  })
  ```
</CodeGroup>

This will generate the following SQL...

```sql theme={null}
SELECT COUNT(*) AS orders__count
FROM orders
WHERE
  date >= '2018-01-01 00:00:00' AND
  date <= '2018-12-31 23:59:59'
```

...for the `['2018-01-01', '2018-12-31']` date range passed for the
`order_facts.date` dimension as in following query:

```json theme={null}
{
  "measures": ["order_facts.count"],
  "time_dimensions": [
    {
      "dimension": "order_facts.date",
      "dateRange": ["2018-01-01", "2018-12-31"]
    }
  ]
}
```

### Example with function

You can also pass a function as a `filter()` argument. This way, you can
add BigQuery shard filtering, which will reduce your billing cost.

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: events
      sql: |
        SELECT *
        FROM schema.`events*`
        WHERE {FILTER_PARAMS.events.date.filter(
          lambda x, y: f"""
            _TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP({x})) AND
            _TABLE_SUFFIX <= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP({y}))
          """
        )}

      dimensions:
        - name: date
          sql: date
          type: time
          


  ```

  ```javascript title="JavaScript" theme={null}
  cube(`events`, {
    sql: `
      SELECT *
      FROM schema.\`events*\`
      WHERE ${FILTER_PARAMS.events.date.filter(
        (x, y) => `
          _TABLE_SUFFIX >= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${x})) AND
          _TABLE_SUFFIX <= FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP(${y}))
        `
      )}
    `,

    dimensions: {
      date: {
        sql: `date`,
        type: `time`
      }
    }
  })
  ```
</CodeGroup>

<Info>
  When a function is passed to `filter()`, its arguments are passed as
  strings from the data source driver and it's your responsibility to handle
  type conversions in this case.
</Info>

In the example above, the filter on a time dimension accepts two values: the
lower and the upper bounds of a date range. If a filter accepts multiple values,
they are passed to the function as individual parameters:

```javascript theme={null}
cube(`multi_filter`, {
  sql: `
    SELECT 123 AS value
    -- Multiple values: ${FILTER_PARAMS.multi_filter.dummy.filter(
      (...args) => JSON.stringify(args)
    )}
  `,

  dimensions: {
    dummy: {
      sql: `1`,
      type: `number`
    }
  }
})
```

### Example with segment

A `FILTER_PARAMS` argument can also name a [segment][ref-ref-segments]. A segment
is not compared to a value, so the expression passed to `filter()` is the whole
predicate rather than a column: it is rendered as-is when the query selects that
segment, and as `1 = 1` when it does not.

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: events
      sql: |
        SELECT *
        FROM events
        WHERE {FILTER_PARAMS.events.start_load.filter(
          "evid = 115 AND action_group = 'load'"
        )}

      segments:
        - name: start_load
          sql: "{CUBE}.evid = 115 AND {CUBE}.action_group = 'load'"
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`events`, {
    sql: `
      SELECT *
      FROM events
      WHERE ${FILTER_PARAMS.events.start_load.filter(
        `evid = 115 AND action_group = 'load'`
      )}
    `,

    segments: {
      start_load: {
        sql: `${CUBE}.evid = 115 AND ${CUBE}.action_group = 'load'`
      }
    }
  })
  ```
</CodeGroup>

The segment's own `sql` prefixes its columns with the cube, which is not in
scope inside the `sql` that builds that cube, so the pushed-down predicate is
restated in `filter()` — the same way a dimension's column is.

A function may be passed instead of a string, as long as it takes no arguments:
a segment carries no filter values to pass to it. A function that does take
arguments renders as `1 = 1`.

<Info>
  Naming a segment is only supported by the default SQL planner. With
  [`CUBEJS_TESSERACT_SQL_PLANNER`][ref-env-tesseract] set to `false`, such a
  binding always renders as `1 = 1`.
</Info>

### Example with a time shift

A measure with a [`time_shift`][ref-ref-time-shift] over a
[calendar cube][ref-calendar-cubes] reads a different period than the one the
query reports: which rows those are is held in the calendar's own table, and no
expression over the source column reproduces it. A binding that restated the
reported period there would narrow the scan past the rows that period needs, so
it does not render — the shift has to be addressed explicitly:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: sales
      sql: |
        SELECT *
        FROM sales
        WHERE {FILTER_GROUP(
          FILTER_PARAMS.fiscal_calendar.report_date.filter('day_date'),
          FILTER_PARAMS.fiscal_calendar.report_date.time_shifts.prev_fiscal_year.filter(
            lambda x, y: f"day_date >= {x}::date - 364 AND day_date <= {y}::date - 364"
          )
        )}
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`sales`, {
    sql: `
      SELECT *
      FROM sales
      WHERE ${FILTER_GROUP(
        FILTER_PARAMS.fiscal_calendar.report_date.filter(`day_date`),
        FILTER_PARAMS.fiscal_calendar.report_date.time_shifts.prev_fiscal_year.filter(
          (from, to) => `day_date >= ${from}::date - 364 AND day_date <= ${to}::date - 364`
        )
      )}
    `
  })
  ```
</CodeGroup>

Each binding renders in one place only: the plain one where no calendar shift
is applied, and `time_shifts.prev_fiscal_year` where that shift is. Where the
model declares no binding for the shift a query applies, nothing is pushed down
and the cube's `sql` scans unrestricted — correct, but without the narrowing.

The name is the one the calendar cube gives the shift in its dimension's
`time_shift`, and it is reached whether the measure asks for that shift by name
or by the interval the calendar declares it with. The bounds passed to the
callback are the reported ones, unshifted: the model states which rows its own
table holds for that period. A `time_shifts` binding therefore takes a function
of both bounds — a string column cannot express a mapping, and a query filtering
the member with anything but a date range is an error.

An interval shift on a dimension that is not part of a calendar cube needs none
of this: the plain binding renders there, with the interval applied to the
column.

<Info>
  Addressing a time shift is only supported by the default SQL planner. With
  [`CUBEJS_TESSERACT_SQL_PLANNER`][ref-env-tesseract] set to `false`, such a
  binding always renders as `1 = 1`.
</Info>

## `FILTER_GROUP`

If you use `FILTER_PARAMS` in your query more than once, you must wrap them
with `FILTER_GROUP`.

<Warning>
  Otherwise, if you combine `FILTER_PARAMS` with any logical operators other than
  `AND` in SQL or if you use filters with [boolean operators][ref-filter-boolean]
  in your Cube queries, incorrect SQL might be generated.
</Warning>

`FILTER_GROUP` has to be a top-level expression in `WHERE` and it has the
following syntax:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: cube_name
      sql: |
        SELECT *
        FROM table
        WHERE {FILTER_GROUP(
          FILTER_PARAMS.cube_name.member_name.filter(sql_expression),
          FILTER_PARAMS.cube_name.another_member_name.filter(sql_expression)
        )}

      dimensions:
        - name: member_name
          # ...

        - name: another_member_name
          # ...




  ```

  ```javascript title="JavaScript" theme={null}
  cube(`cube_name`, {
    sql: `
      SELECT *
      FROM table
      WHERE ${FILTER_GROUP(
        FILTER_PARAMS.cube_name.member_name.filter(sql_expression),
        FILTER_PARAMS.cube_name.another_member_name.filter(sql_expression)
      )}
    `,

    dimensions: {
      member_name: {
        // ...
      },
      
      another_member_name: {
        // ...
      }
    }
  })
  ```
</CodeGroup>

### Example

To understand the value of `FILTER_GROUP`, consider the following data model
where two `FILTER_PARAMS` are combined in SQL using the `OR` operator:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: filter_group
      sql: |
        SELECT *
          FROM (
            SELECT 1 AS a, 3 AS b UNION ALL
            SELECT 2 AS a, 2 AS b UNION ALL
            SELECT 3 AS a, 1 AS b
          ) AS data
          WHERE
            {FILTER_PARAMS.filter_group.a.filter("a")} OR
            {FILTER_PARAMS.filter_group.b.filter("b")}

      dimensions:
        - name: a
          sql: a
          type: number

        - name: b
          sql: b
          type: number




  ```

  ```javascript title="JavaScript" theme={null}
  cube(`filter_group`, {
    sql: `
      SELECT *
        FROM (
          SELECT 1 AS a, 3 AS b UNION ALL
          SELECT 2 AS a, 2 AS b UNION ALL
          SELECT 3 AS a, 1 AS b
        ) AS data
        WHERE
          ${FILTER_PARAMS.filter_group.a.filter('a')} OR
          ${FILTER_PARAMS.filter_group.b.filter('b')}
    `,

    dimensions: {
      a: {
        sql: `a`,
        type: `number`
      },

      b: {
        sql: `b`,
        type: `number`
      }
    }
  })
  ```
</CodeGroup>

If the following query is run...

```json theme={null}
{
  "dimensions": [
    "filter_group.a",
    "filter_group.b"
  ],
  "filters": [
    {
      "member": "filter_group.a",
      "operator": "gt",
      "values": ["1"]
    },
    {
      "member": "filter_group.b",
      "operator": "gt",
      "values": ["1"]
    }
  ]
}
```

...the following (logically incorrect) SQL will be generated:

```sql theme={null}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) OR  -- Incorrect logical operator here
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 AND
  "filter_group".b > 1
GROUP BY 1, 2
```

As you can see, since an array of filters has `AND` semantics, Cube has
correctly used the `AND` operator in the "outer" `WHERE`. At the same time,
the hardcoded `OR` operator has propagated to the "inner" `WHERE`, leading to
a logically incorrect query.

Now, if the cube is defined the following way...

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: filter_group
      sql: |
        SELECT *
          FROM (
            SELECT 1 AS a, 3 AS b UNION ALL
            SELECT 2 AS a, 2 AS b UNION ALL
            SELECT 3 AS a, 1 AS b
          ) AS data
          WHERE
            {FILTER_GROUP(
              FILTER_PARAMS.filter_group.a.filter("a"),
              FILTER_PARAMS.filter_group.b.filter("b")
            )}

      # ...
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`filter_group`, {
    sql: `
      SELECT *
        FROM (
          SELECT 1 AS a, 3 AS b UNION ALL
          SELECT 2 AS a, 2 AS b UNION ALL
          SELECT 3 AS a, 1 AS b
        ) AS data
        WHERE
          ${FILTER_GROUP(
            FILTER_PARAMS.filter_group.a.filter('a'),
            FILTER_PARAMS.filter_group.b.filter('b')
          )}
    `,

    // ...
  ```
</CodeGroup>

...the following correct SQL will be generated for the same query:

```sql theme={null}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) AND  -- Correct logical operator here
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 AND
  "filter_group".b > 1
GROUP BY 1, 2
```

You can also use [boolean operators][ref-filter-boolean] in the Cube query
to express more complex filtering logic:

```json theme={null}
{
  "dimensions": [
    "filter_group.a",
    "filter_group.b"
  ],
  "filters": [
    {
      "or": [
        {
          "member": "filter_group.a",
          "operator": "gt",
          "values": ["1"]
        },
        {
          "member": "filter_group.b",
          "operator": "gt",
          "values": ["1"]
        }
      ]
    }
  ]
}
```

With `FILTER_GROUP`, the following correct SQL will be generated:

```sql theme={null}
SELECT
  "filter_group".a,
  "filter_group".b
FROM (
  SELECT *
  FROM (
    SELECT 1 AS a, 3 AS b UNION ALL
    SELECT 2 AS a, 2 AS b UNION ALL
    SELECT 3 AS a, 1 AS b
  ) AS data
  WHERE
    (a > 1) OR
    (b > 1)
) AS "filter_group"
WHERE
  "filter_group".a > 1 OR
  "filter_group".b > 1
GROUP BY 1, 2
```

## `SQL_UTILS`

### `convertTz`

In case you need to convert your timestamp to user request timezone in cube or
member SQL you can use `SQL_UTILS.convertTz()` method. Note that Cube will
automatically convert timezones for `timeDimensions` fields in
[queries](/reference/core-data-apis/rest-api/query-format#query-properties).

<Warning>
  Dimensions that use `SQL_UTILS.convertTz()` should not be used as
  `timeDimensions` in queries. Doing so will apply the conversion multiple times
  and yield wrong results.
</Warning>

In case the same database field needs to be queried in `dimensions` and
`timeDimensions`, create dedicated dimensions in the cube definition for the
respective use:

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: visitors
      # ...

      dimensions:
        # Do not use in timeDimensions query property
        - name: created_at_converted
          sql: "{SQL_UTILS.convertTz(`created_at`)}"
          type: time

        # Use in timeDimensions query property
        - name: created_at
          sql: created_at
          type: time



  ```

  ```javascript title="JavaScript" theme={null}
  cube(`visitors`, {
    // ...

    dimensions: {
      // Do not use in timeDimensions query property
      created_at_converted: {
        sql: SQL_UTILS.convertTz(`created_at`),
        type: `time`
      },

      // Use in timeDimensions query property
      created_at: {
        sql: `created_at`,
        type: "time"
      }
    }
  })
  ```
</CodeGroup>

## `COMPILE_CONTEXT`

<Warning>
  `COMPILE_CONTEXT` is evaluated only once per each key generated by `context_to_app_id`.
  The `securityContext` defined in `COMPILE_CONTEXT` doesn't change
  its value for different users, however, it will change for
  different tenants as defined in `context_to_app_id`.
</Warning>

A global `COMPILE_CONTEXT` contains `securityContext` and any other variables provided by
[`extendContext`][ref-config-ext-ctx].

Use [Jinja][ref-dynamic-jinja] `{{ }}` syntax to access `COMPILE_CONTEXT` variable.

<CodeGroup>
  ```yaml title="YAML" theme={null}
  cubes:
    - name: users
      sql_table: "user_{{ COMPILE_CONTEXT.securityContext.deployment_id }}.users"
  ```

  ```javascript title="JavaScript" theme={null}
  cube(`users`, {
    sql_table: `user_${COMPILE_CONTEXT.securityContext.deployment_id}.users`
  })
  ```
</CodeGroup>

## `SECURITY_CONTEXT`

<Warning>
  **`SECURITY_CONTEXT` is deprecated and can be removed without further notice.**
  Use [`query_rewrite`][ref-config-queryrewrite] instead.
</Warning>

`SECURITY_CONTEXT` global variable holds a security context that is passed to Cube via API.
Please read the [Security Context page][ref-sec-ctx] for more information on how
to provide security context to Cube.

```javascript theme={null}
cube(`orders`, {
  sql: `
    SELECT *
    FROM orders
    WHERE ${SECURITY_CONTEXT.email.filter("email")}
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

To ensure filter value presents for all requests `requiredFilter` can be used:

```javascript theme={null}
cube(`orders`, {
  sql: `
    SELECT *
    FROM orders
    WHERE ${SECURITY_CONTEXT.email.requiredFilter("email")}
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

You can access values of context variables directly in JavaScript in order to
use it during your SQL generation. For example:

<Warning>
  Use of this feature entails SQL injection security risk. Use it with caution.
</Warning>

```javascript theme={null}
cube(`orders`, {
  sql: `
    SELECT *
    FROM ${
      SECURITY_CONTEXT.type.unsafeValue() === "employee" ? "employee" : "public"
    }.orders
  `,

  dimensions: {
    date: {
      sql: `date`,
      type: `time`
    }
  }
})
```

[ref-config-ext-ctx]: /reference/configuration/config#extendcontext

[ref-config-queryrewrite]: /reference/configuration/config#query_rewrite

[ref-sec-ctx]: /docs/data-modeling/access-control/context

[ref-ref-cubes]: /reference/data-modeling/cube

[ref-syntax-references]: /docs/data-modeling/concepts/syntax#references

[ref-dynamic-data-models]: /docs/data-modeling/dynamic/jinja

[ref-query-filter]: /reference/core-data-apis/rest-api/query-format#query-properties

[ref-dynamic-jinja]: /docs/data-modeling/dynamic/jinja

[ref-filter-boolean]: /reference/core-data-apis/rest-api/query-format#boolean-logical-operators

[ref-links]: /reference/data-modeling/dimensions#links

[ref-ref-segments]: /reference/data-modeling/segments

[ref-env-tesseract]: /reference/configuration/environment-variables#cubejs_tesseract_sql_planner

[ref-ref-time-shift]: /reference/data-modeling/measures#time_shift

[ref-calendar-cubes]: /docs/data-modeling/concepts/calendar-cubes