SQL Variables

SDF supports simple variables and parameterization in SQL queries. This is useful for dynamic queries and for reusing values in multiple queries. These differ from variables in Jinja in the following ways:

1. They are only available for use in SQL, not in YML.
2. They are typed (bool, int, float, string, and null)
3. They are used with the @ syntax, instead of \{\{ \}\}
4. They can be easily set per run with the --vars flag on CLI commands like sdf compile and sdf run

Let’s go through a simple example where we use a variable in a SQL query.

​

Example

In this example, we will create a variable @my_variable and use it in a SQL query.

SELECT * FROM my_table WHERE my_column = @my_variable

In order to enable variable substitution, we’ll need to set the preprocessor option to sql-vars in the workspace.sdf.yml like so:

     workspace:
        defaults:
          preprocessor: sql-vars
        ...

Next, to set the value of @my_variable, let’s add a vars yml block to the workspace like so:

workspace:
  defaults:
    preprocessor: sql-vars
  vars:
    my_variable: "my_value"
  ...

You can use the --vars flag on the CLI. For example:

sdf run --vars '{my_variable: "my_value"}'

or

sdf compile --vars '{my_variable: "my_value"}'

This will replace @my_variable with my_value in the SQL query. The resulting query will look like this:

SELECT * FROM my_table WHERE my_column = 'my_value'

SDF vars also respect types, so if you set a variable to a number, it will be treated as a number in the SQL query. For example:

sdf run --vars '{my_variable: 123}'

This will replace @my_variable with 123 in the SQL query. The resulting query will look like this:

SELECT * FROM my_table WHERE my_column = 123

SDF vars also support boolean, float, and null types along with strings and integers.

​

Vars in Jinja

SQL variables can also be referenced and utilized in Jinja. For example, if we modify the query above to use jinja substitution syntax, it would look like this:

SELECT * FROM my_table WHERE my_column = {{ my_variable }}

You can then set the variable in the vars block in the workspace.sdf.yml file like so:

workspace:
  defaults:
    preprocessor: all
  vars:
    my_variable: "my_value"
  ...

​

Vars in YML

You can also set variables in a vars.yml file. This is useful when you have a lot of variables to set. Here is an example of a vars.yml file:

my_variable: "my_value"

You can then use this file with the --vars flag like so:

sdf run --vars vars.yml

where vars.yml is the path to the file.