Variables

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

Create variable

1. Click the ‘+’ button in the variable section.
   
   
   
2. Choose 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 do not have any kind of leading sigil (that is, they look like var_name).
4. Enter a value.
5. Click the ‘Save variable’ button.

The list of Transformation variables displays in the left sidebar.

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

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.