Data modeling with YAML, Jinja, and Python

Jinja and Python techniques for templating YAML models—loops, includes, and runtime generation—to keep large or multi-tenant schemas maintainable.

Cube supports authoring dynamic data models using the Jinja templating language and Python. This allows de-duplicating common patterns in your data models as well as dynamically generating data models from a remote data source.

Jinja is supported in all YAML data model files.

YAML

It is recommended to default to YAML syntax because of its simplicity and readability.

Folded and literal strings

Sometimes you might want to use multi-line strings in YAML-based data models, e.g., in parameters such as sql or description. It is recommended to use literal (|) string style in such cases as it preserves line breaks.

cubes:
  - name: orders
    description: |
      This cube represents customer orders.
      It includes measures for total sales and order count.
    sql: |
      -- Fetch only relevant columns
      SELECT id, created_at, total_amount
      FROM staging.orders

Jinja

Please check the Jinja documentation for details on Jinja syntax.

Previewing YAML

You can preview the data model code after applying Jinja templates in the Data Model editor by clicking … → Jinja Preview on files that contain Jinja templates in the sidebar.

Currently, there’s no way to preview the data model code in YAML after applying Jinja templates in Cube Core. Please track this issue.

You can also view the resulting data model in Playground and Visual Model. Also, you can introspect the data model using the /v1/meta REST (JSON) API endpoint.

Loops

Jinja supports looping over lists and dictionaries. In the following example, we loop over a list of nested properties and generate a LEFT JOIN UNNEST clause for each one: for each one:

{%- set nested_properties = [
  "referrer",
  "href",
  "host",
  "pathname",
  "search"
] -%}

cubes:
  - name: analytics
    sql: |
      SELECT
      {%- for prop in nested_properties %}
        {{ prop | safe }}_prop.value AS {{ prop | safe }}
      {%- endfor %}
      FROM public.events
      {%- for prop in nested_properties %}
      LEFT JOIN UNNEST(properties) AS {{ prop | safe }}_prop ON {{ prop | safe }}_prop.key = '{{ prop | safe }}'
      {%- endfor %}

Another useful pattern is to loop over a dictionary of values and generate a measure for each one, as in the following example:

{%- set metrics = {
  "mau": 30,
  "wau": 7,
  "day": 1
} %}

cubes:
  - name: orders
    sql_table: public.orders

    measures:
      {%- for name, days in metrics | items %}
      - name: {{ name | safe }}
        type: count_distinct
        sql: user_id
        rolling_window:
          trailing: {{ days }} day
          offset: start
      {% endfor %}

Macros

Cube data models also support Jinja macros, which allow you to define reusable snippets of code. You can read more about macros in the Jinja documentation.

In the following example, we define a macro called dimension() which generates a dimension definition in Cube. This macro is then invoked multiple times to generate multiple dimensions:

{# Declare the macro before using it, otherwise Jinja will throw an error. #}
{%- macro dimension(column_name, type='string', primary_key=False) -%}
      - name: {{ column_name }}
        sql: {{ column_name }}
        type: {{ type }}
        {% if primary_key -%}
        primary_key: true
        {% endif -%}
{% endmacro -%}

cubes:
  - name: orders
    sql_table: public.orders

    dimensions:
      {{ dimension('id', 'number', primary_key=True) }}
      {{ dimension('status') }}
      {{ dimension('created_at', 'time') }}
      {{ dimension('completed_at', 'time') }}

You could also use macros to generate SQL snippets for use in the sql property:

{%- macro cents_to_dollars(column_name, precision=2) -%}
  ({{ column_name | safe }} / 100)::NUMERIC(16, {{ precision | safe }})
{%- endmacro -%}

cubes:
  - name: payments
    sql: |
      SELECT
        id AS payment_id,
        {{ cents_to_dollars('amount') }} AS amount_usd
      FROM app_data.payments

Reusing macros across files

You can define macros in dedicated .jinja files and import them into your data model files using Jinja’s import statement. This is useful for sharing common patterns across multiple cubes and views.

Consider the following project structure:

.
└── cube/
    ├── model/
    │   ├── cubes/
    │   │   └── orders.yml
    │   ├── views/
    │   └── macros/
    │       └── common_dimensions.jinja
    └── cube.py

First, define reusable macros in a .jinja file under the macros/ directory:

model/macros/common_dimensions.jinja

{%- macro dimension(column_name, type='string', primary_key=False) -%}
      - name: {{ column_name }}
        sql: {{ column_name }}
        type: {{ type }}
        {% if primary_key -%}
        primary_key: true
        {% endif -%}
{% endmacro -%}

{%- macro cents_to_dollars(column_name, precision=2) -%}
  ({{ column_name | safe }} / 100)::NUMERIC(16, {{ precision | safe }})
{%- endmacro -%}

Then, import and use those macros in your data model files:

model/cubes/orders.yml

{%- import "macros/common_dimensions.jinja" as common -%}

cubes:
  - name: orders
    sql_table: public.orders

    dimensions:
      {{ common.dimension('id', 'number', primary_key=True) }}
      {{ common.dimension('status') }}
      {{ common.dimension('created_at', 'time') }}

    measures:
      - name: amount_usd
        type: sum
        sql: |-
          {{ common.cents_to_dollars('amount') }}

The import path is relative to the model/ directory. cents_to_dollars expands to a single line, so the block scalar alone is enough. A macro that can emit more than one line also needs the indent filter — see emitting SQL from a macro.

Escaping unsafe strings

Auto-escaping of unsafe string values in Jinja templates is enabled by default. Substituted values are escaped as JSON strings, so they get wrapped in quotes, potentially breaking YAML syntax. This applies to every substituted value — not only to strings coming from Python, but also to loop variables, macro arguments, and values set in the template itself.

You can work around that by using the safe Jinja filter with such string values:

cubes:
  - name: my_cube
    description: {{ get_unsafe_string() | safe }}

Whether you need safe depends on where the value lands. When a value is the whole of a YAML value, the quotes are harmless and it compiles as written: type: {{ type }} renders as type: "sum". As soon as anything is concatenated with it, the quotes end up inside the line and break it: {{ name }}_{{ period }} renders as "revenue"_"week", and the model fails with bad indentation of a mapping entry. Apply safe to every value that is concatenated with other text:

{%- set name = "revenue" -%}

cubes:
  - name: {{ name | safe }}_daily
    description: Daily {{ name | safe }}, by region

Alternatively, you can wrap unsafe strings into instances of the following class in your Python code, effectively marking them as safe. This is particularly useful for library code, e.g., similar to the cube_dbt package.

class SafeString(str):
  is_safe: bool

  def __init__(self, v: str):
    self.is_safe = True

Emitting SQL from a macro

When a macro takes a SQL expression as an argument, emit it as a literal string (|-) rather than inline. A SQL expression is arbitrary text, and inline it has to avoid everything YAML reads as syntax: {CUBE}.amount starts a flow mapping, amount # note truncates at the comment, and wrapping the whole thing in double quotes only moves the problem to expressions that contain one, such as {CUBE}."amount".

A block scalar ends at the first line indented less than its opening, so a multi-line expression also needs the indent filter — without it, the second line of a CASE expression closes the block and is read as a mapping key. Set the width to the indentation of the block’s value line, not to some fixed number: the indent(10) below is 10 because the macro emits sql: |- at 8 spaces and the value two further in.

Apply indent before safe, not after. indent returns a fresh, unmarked string, so sql | safe | indent(10) throws the marker away and the SQL arrives quoted, as "{CUBE}.amount". Marking the result of indent keeps the expression raw:

        sql: |-
          {{ sql | indent(10) | safe }}

Both mistakes surface as a YAML parse error far from the macro that caused them. Render the model first — see previewing YAML.

Python

Template context

You can use Python to declare functions that can be invoked and variables that can be referenced from within a Jinja template. These functions and variables must be defined in model/globals.py file and registered in the TemplateContext instance.

See the TemplateContext reference for more details.

In the following example, we declare a function called load_data that supposedly loads data from a remote API endpoint. We will then use the function to generate a data model:

from cube import TemplateContext
 
template = TemplateContext()

@template.function('load_data')
def load_data():
   client = MyApiClient("example.com")
   return client.load_data()


class MyApiClient:
  def __init__(self, api_url):
    self.api_url = api_url

  # mock API call
  def load_data(self):
    api_response = {
      "cubes": [
        {
          "name": "cube_from_api",
          "measures": [
            { "name": "count", "type": "count" },
            { "name": "total", "type": "sum", "sql": "amount" }
          ],
          "dimensions": []
        },
        {
          "name": "cube_from_api_with_dimensions",
          "measures": [
            { "name": "active_users", "type": "count_distinct", "sql": "user_id" }
          ],
          "dimensions": [
            { "name": "city", "sql": "city_column", "type": "string" }
          ]
        }
      ]
    }
    return api_response

Now that we’ve decorated our function with the @template.function decorator, we can call it from within a Jinja template. In the following example, we’ll call the load_data() function and use the result to generate a data model.

cubes:
  {# Here we use the decorated function from earlier #}
  {%- for cube in load_data()["cubes"] %}

  - name: {{ cube.name }}

  {%- if cube.measures is not none and cube.measures|length > 0 %}
    measures:
      {%- for measure in cube.measures %}
      - name: {{ measure.name }}
        type: {{ measure.type }}
      {%- if measure.sql %}
        sql: {{ measure.sql }}
      {%- endif %}
      {%- endfor %}
  {%- endif %}

  {%- if cube.dimensions is not none and cube.dimensions|length > 0 %}
    dimensions:
      {%- for dimension in cube.dimensions %}
      - name: {{ dimension.name }}
        type: {{ dimension.type }}
        sql: {{ dimension.sql }}
      {%- endfor %}
  {%- endif %}
  {%- endfor %}

Imports

In the model/globals.py file (or the cube.py configuration file), you can import modules from the current directory. In the following example, we import a function from the utils module and use it to populate a variable in the template context:

model/utils.py

def answer_to_main_question() -> str:
  return "42"

model/globals.py

from cube import TemplateContext
from utils import answer_to_main_question

template = TemplateContext()

answer = answer_to_main_question()
template.add_variable('answer', answer)

Dependencies

If you need to use dependencies in your dynamic data model (or your cube.py configuration file), you can list them in the requirements.txt file in the root directory of your Cube deployment. They will be automatically installed with pip on the startup.

cube package is available out of the box, it doesn’t need to be listed in requirements.txt.

If you use dbt for data transformation, you might find the cube_dbt package useful. It provides a set of utilities that simplify defining the data model in YAML based on dbt models.

If you need to use dependencies with native extensions, build a custom Docker image.