SDF supports several variable types and scopes with Jinja. These allow you to parameterize your SQL and YML files, making them more reusable and easier to maintain.

In this guide, we’ll cover the following variable types:

​
Local Variables

Local variables are defined in the same file where they are used, and they are scoped to the file they are defined in and not accessible in other files.

Let’s look at an example, imagine we have a model that calculates a variety of metrics starting from a specific date, then aggregates them into one table. We can define a local variable to store the start date, and use it in our SQL query. Click between the two files below to see the pre and post processed SQL.

{% set start_date = '2021-01-01' %}

with total_revenue as (
    select
        sum(revenue) as total_revenue
    from
        sales
    where
        date >= '{{ start_date }}'
),

total_sales as (
    select
        count(*) as total_sales
    from
        sales
    where
        date >= '{{ start_date }}'
),

average_revenue_per_sale as (
    select
        (sum(revenue) / count(*)) as avg_revenue_per_sale
    from
        sales
    where
        date >= '{{ start_date }}'
),

total_customers as (
    select
        count(distinct customer_id) as total_customers
    from
        sales
    where
        date >= '{{ start_date }}'
)

select
    total_revenue.total_revenue,
    total_sales.total_sales,
    average_revenue_per_sale.avg_revenue_per_sale,
    total_customers.total_customers
from
    total_revenue,
    total_sales,
    average_revenue_per_sale,
    total_customers

SDF Variables specified via the command line, or in the workspace.sdf.yml file, can also be accessed in the same way as local variables. They are namespaced globally, and can be accessed in any file in the workspace.

Local variables are prioritized over global variables if they share the same name.

Since local variables are scoped to the file they are defined in, you can define a variable with the same name in another file without any conflicts. Furthermore, you can reference the variable by name alone without any prefix or namespace.

​
Namespaced Variables

Namespaced variables are defined in a separate file and are accessible in across the entire workspace. This allows you to define variables once and use them across multiple files. These variables are namespaced with the workspace name, and therefore accessed using <workspace-name>.<variable-name>.

Let’s look at an example, imagine we have several models that all use the same list of countries. We can define a namespaced variable to store the list of countries, and use it in our SQL queries.

For the sake of the this example, let’s assume we have a workspace named transformations. We can define a namespaced variable in a file named countries.jinja as follows:

{% set countries = "(
    'United States',
    'Canada',
    'Mexico',
    'Brazil',
    'Argentina',
    'Chile'
)" %}

Macros and variables are both namespaced to the workspace name so that they can be used inside other workspaces. This enables workspaces to act as SDF packages that can be shared and reused across multiple projects.

​
Environment Variables

Environment variables can be accessed via the built in jinja macro env_var(VAR). This function is accessible in both SQL, and YML files.

​
Give it a try in SQL:

1

Navigate to Your Workspace

Navigate to your SDF workspace. To create a new one, see our getting started section here.

2

Turn on the macro preprocessor

SDF will only use the macro-processor when instructed. In your workspace.sdf.yml, in the workspace, block, add the preprocessor configuration.

    workspace:
      defaults:
        preprocessor: jinja
      ...
3

Give it a Try!

Now you have access to environment variables. Try if for yourself. In your terminal, run an ad-hoc query against SDF:

sdf run -q "select '{{env_var('PWD')}}' as current_directory;"

You’ll see as output:

Table sdf.pub.query
+---------------------------------------------+
| current_directory                           |
+---------------------------------------------+
| /Users/name/Documents/workspaces/jinja-demo |
+---------------------------------------------+

​
Give it a try in YML:

1

Navigate to Your Workspace

Navigate to your SDF workspace. To create a new one, see our getting started section here.

2

Turn on the macro preprocessor

SDF will only use the macro-processor when instructed. In your workspace.sdf.yml, in the workspace, block, add the preprocessor configuration.

    workspace:
      defaults:
        preprocessor: jinja
      ...

Let’s also add a default catalog name:

    workspace:
      defaults:
        preprocessor: jinja
        catalog: my_catalog  <-- Add this line
      ...
3

Give it a Try!

First, run sdf compile with the existing configuration. Notice that the catalog name of all assets should be my_catalog as we set it up in the previous step.

Now, let’s introduce an environment variable.

Change the default catalog to point to an environment variable. In this example, we will use the local username:

    workspace:
      defaults:
        preprocessor: jinja
        catalog: "{{env_var('USER')}}_catalog"  <-- Change this line
      ...

Now, when we run sdf compile, the catalog name of all aassets will be {your_username}_catalog, when {your_username} is the username you use for your local machine.

You can run printenv in your terminal to view all available environment variables on your machine.

4

Create a New Environment variable

It is possible to create new environment variables and reference them using SDF.

Let’s create a new variable named DEFAULT_CATALOG. For the sake of this example, we can set it to be my_default_catalog.

In your terminal, run:

export DEFAULT_CATALOG="my_default_catalog"

Now, we need to update the env_var reference we set in the previous step:

    workspace:
      defaults:
        preprocessor: jinja
        catalog: "{{env_var('DEFAULT_CATALOG')}}"  <-- Change this line
      ...

Run sdf compile again. This time, the catalog name of all aassets will match the new environment variable we set up - my_default_catalog.

​
Environment Variables as Credentials

A common use case for environment variables is to inject credentials into .sdf.yml files so that they are not stored in plain text.

Here’s an example of using these to hydrate Snowflake credentials in your project. Let’s start by creating a file creds.sdf.yml and placing it next to your workspace.sdf.yml file.

Next, add the following content to the file:

creds.sdf.yml
credential:
    description: Snowflake Credentials
    name: default
    type: snowflake
    account-id: "{{ env_var('SNOWFLAKE_ACCOUNT_ID') }}"
    username: "{{ env_var('SNOWFLAKE_USERNAME') }}"
    password: "{{env_var('SNOWFLAKE_PASSWORD')}}"
    role: "{{ env_var('SNOWFLAKE_ROLE') }}"
    warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"

Next, add an includes path to your workspace.sdf.yml like so:

workspace.sdf.yml
    includes:
        - path: creds.sdf.yml
        type: spec

Now, compiling or running your SDF workspace will require those environment variables to be set. This protects your credentials from being committed to git while still enabling the flexibility of using them in your project.