> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sdf.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Jinja Variables

> Work with Variables in Jinja to parameterize your SQL and YML files

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)
* [Namespaced Variables](#namespace-variables)
* [Environment Variables](#environment-variables)

## 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.

<CodeGroup>
  ```sql start_date_preprocessed.sql theme={null}
  {% 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
  ```

  ```sql start_date_postprocessed.sql theme={null}
  with total_revenue as (
      select
          sum(revenue) as total_revenue
      from
          sales
      where
          date >= '2021-01-01'
  ),

  total_sales as (
      select
          count(*) as total_sales
      from
          sales
      where
          date >= '2021-01-01'
  ),

  average_revenue_per_sale as (
      select
          (sum(revenue) / count(*)) as avg_revenue_per_sale
      from
          sales
      where
          date >= '2021-01-01'
  ),

  total_customers as (
      select
          count(distinct customer_id) as total_customers
      from
          sales
      where
          date >= '2021-01-01'
  )

  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
  ```
</CodeGroup>

[SDF Variables](/guide/macro-processing/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.

<Warning>
  Local variables are prioritized over global variables if they share the same name.
</Warning>

<Note>
  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.
</Note>

## 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:

<CodeGroup>
  ```jinja countries.jinja theme={null}
  {% set countries = "(
      'United States',
      'Canada',
      'Mexico',
      'Brazil',
      'Argentina',
      'Chile'
  )" %}
  ```

  ```sql countries_preprocessed.sql theme={null}
  select
      country,
      count(*) as total_sales
  from
      sales
  where
      country in {{ transformations.countries }}
  ```

  ```sql countries_postprocessed.sql theme={null}
  select
      country,
      count(*) as total_sales
  from
      sales
  where
      country in (
      'United States',
      'Canada',
      'Mexico',
      'Brazil',
      'Argentina',
      'Chile'
  )
  ```
</CodeGroup>

<Tip>
  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.
</Tip>

## 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:

<Steps>
  <Step title="Navigate to Your Workspace">
    Navigate to your SDF workspace. To create a new one, see our getting started section [here](/guide/macro-processing/jinja#getting-started-with-jinja).
  </Step>

  <Step title="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.

    ```yml theme={null}
        workspace:
          defaults:
            preprocessor: jinja
          ...
    ```
  </Step>

  <Step title="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:

    ```bash theme={null}
    sdf run -q "select '{{env_var('PWD')}}' as current_directory;"
    ```

    You'll see as output:

    ```shell theme={null}
    Table sdf.pub.query
    +---------------------------------------------+
    | current_directory                           |
    +---------------------------------------------+
    | /Users/name/Documents/workspaces/jinja-demo |
    +---------------------------------------------+
    ```
  </Step>
</Steps>

### Give it a try in YML:

<Steps>
  <Step title="Navigate to Your Workspace">
    Navigate to your SDF workspace. To create a new one, see our getting started section [here](/guide/macro-processing/jinja#getting-started-with-jinja).
  </Step>

  <Step title="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.

    ```yml theme={null}
        workspace:
          defaults:
            preprocessor: jinja
          ...
    ```

    Let's also add a default catalog name:

    ```yml theme={null}
        workspace:
          defaults:
            preprocessor: jinja
            catalog: my_catalog  <-- Add this line
          ...
    ```
  </Step>

  <Step title="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:

    ```yml theme={null}
        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.

    <Warn>
      Note that when using environment variables in YML, they will be required to be set whenever running SDF commands. For these reason, unless they are critical, we providing them a default value with: `{{env_var('VAR', 'default_value')}}`.
    </Warn>

    <Tip>
      You can run `printenv` in your terminal to view all available environment variables on your machine.
    </Tip>
  </Step>

  <Step title="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:

    ```shell theme={null}
    export DEFAULT_CATALOG="my_default_catalog"
    ```

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

    ```yml theme={null}
        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`.
  </Step>
</Steps>

### 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:

```yml creds.sdf.yml theme={null}
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:

```yml workspace.sdf.yml theme={null}
    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.