About project definition files¶

Using variables in SQL¶

You can also use project files to define variables that other commands, such as snow sql, can use. The env section in the project definition file(typically, snowflake.yml) lets you define variables as shown:

definition_version: 1.1
env:
  database: "dev"
  role: "eng_rl"

After adding the env section to the project definition file, you can pass the variables to the snow sql command instead of specifying the variable and value on the command line.

Instead specifying the database and role on the command line with the --variable option, as shown:

snow sql \
-q "grant usage on database &{ database } to &{ role }" \
-D "database=dev" \
-D "role=eng_rl"

you can specify the variables defined in the env section as shown:

snow sql -q "grant usage on database &{ ctx.env.database } to &{ ctx.env.role }"

You can include the env section in addition to any other sections you include in the project definition file.

definition_version: 1.1
snowpark:
  project_name: "my_snowpark_project"
  stage_name: "dev_deployment"
  src: "app/"

  functions:
    - name: test_function
      handler: "functions.hello_function"
      signature: ""
      returns: string

  procedures:
    - name: hello_procedure
      handler: "procedures.hello_procedure"
      signature:
        - name: "name"
          type: "string"
      returns: string

env:
  database: "dev"
  role: "eng_rl"

You can override any variable defined the in snowflake.yml project definition file by setting a shell environment variable by the same name (case-sensitive). For example, to override the database value defined in the example, you can execute the following shell command:

export database="other"

For more information about using env variables, see Storing variables in the snowflake.yml project definition file.

Templating the project definition file¶

In some situations, you might want to reference information already present in a project definition file in another place of the file. Snowflake CLI supports templating the project definition file.

Project definition file templating uses the <% … %> syntax for specifying the templates. The following example uses the env section to define a name for a Streamlit application:

definition_version: "1.1"
env:
  name: "my-app"
streamlit:
  name: <% ctx.env.name %>

The <% ctx.env.name %> syntax references a global context object that provides access to the project definition. The ctx object has the same structure as the project definition. You can access attributes of defined objects using dot notation. Example uses include:

• <% ctx.native_app.name %> to access application name.

• <% ctx.snowpark.stage_name %> to access stage name usage for snowpark UDFs and procedures.

• <% ctx.streamlit.name %> to access the Streamlit dashboard name.

You can override any variable defined in the snowflake.yml project definition file env section by setting a shell environment variable by the same case-sensitive name. For example, to override the name value defined in the example, you can execute the following shell command:

export name="other"

Altering command behavior using templates¶

You can use templates to alter the definition using environment variables. For example, the following project definition templates the schema for a Streamlit dashboard:

definition_version: "1.1"
env:
  schema: "test"
streamlit:
  name: "MY_APP"
  schema: <% ctx.env.schema %>

This feature lets you to alter the behavior of the snow streamlit deploy command by setting a schema environment variable. Using this approach, you can deploy the same dashboard to multiple different schemas by entering the following commands to deploy different schemas:

schema="staging"; snow streamlit deploy
schema="prod"; snow streamlit deploy

You can also use the templating feature without defining variables in the env section. If a variable is not present in env section, Snowflake CLI looks for corresponding environment variables. For example, if you define a Streamlit application similar to the following, you can still alter the behavior of snow streamlit deploy by specifying a schema environment variable.

definition_version: "1.1"
streamlit:
  name: "MY_APP"
  schema: <% ctx.env.schema %>

Differences between SQL and project definition file templating¶

SQL and project definition templates use different templating syntaxes:

• SQL uses the &{ var } syntax.

• Project definition files uses the <% var %> syntax to maintain compliance with the YAML specification.