Dynamic Variables

Transformation variables allow you to create special types of content that can then be referenced via a placeholder in SQL code.

Create a Dynamic Variable

1. Click the ‘+’ button in the variable section.
   
   
   
2. Choose a variable type: String, Integer, or Float.
3. Enter a unique variable name. It should consist of alphanumeric characters and underscores, should always start with a letter, and should not have any kind of leading sigil (that is, they look like var_name).
4. Enter a value.
5. Click the ‘Save variable’ button.

List of Dynamic Variables

The list of Transformation variables is displayed in the left sidebar.

Variable highlighting in the list:

The list of variables includes color-coded highlights to indicate their usage within the current Operation. Variables that are actively utilized in the Operation are highlighted in blue.

Variables not utilized in any Operation are displayed in light gray text.

Additionally, when you hover over a variable name, a tooltip provides information about the Operations in which the variable is utilized.

Default values for variables in the OWOX BI template

If you've created a Transformation from one of the templates, you will see a red highlight for variables that you need to customize:

Read more instructions about customizing templates in this article.

Changing the order:

You can modify the order of variables by simply clicking on the icon next to the variable name and dragging it up or down the list.

Using Variables in SQL Code

An output statement is a set of double curly braces containing a variable name. Here is a simple example: {{var_name}}.

Example SQL code with variables:

WITH
  --Inputs
    dates AS
      ( 
        SELECT
          '{{start_suffix}}' AS s,
          '{{end_suffix}}' AS e
      ),
    streaming AS
      (
        (
          SELECT
            PARSE_DATE('%Y%m%d', _TABLE_SUFFIX) AS date,
            'site.com' AS dataSource,
            s.* EXCEPT(date)
          FROM
            dates AS d, `{{project_id}}.{{dataset_id_streaming}}.owoxbi_sessions_*` AS s
          WHERE
            _TABLE_SUFFIX BETWEEN d.s AND d.e
        )
      ),

When the Transformation is run, an output statement in SQL code gets replaced with the value of that variable. The compiled SQL code you can find in the “Run history” tab. Read more in this article.

Edit Variable Settings

To edit a variable, click on it in the list. Then you can change the variables settings: type, name, and value. Click the ‘Save variable’ button to apply changes and close the popup.

Delete Variable

To delete a variable, click on it in the list. Then click the Delete button in the opened popup. After that check, your code does not use this variable.