> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-feat-druid-driver-streaming.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`
    }
  }
})
```

## `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/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