About project definition files¶
When developing Streamlit or Snowpark applications you often work with multiple files and objects, be it python file or stored procedures. Organizing this in a clear and concise way is very important for smooth development experience. That’s the reason why Snowflake CLI is using the concept of project definition files.
A project definition file (usually named snowflake.yml
) is a file containing information about the type of the project you are working on as well as information about the Snowflake objects you are developing. The following snowflake.yml
example shows a Snowpark project with a UDF and a stored procedure.
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
Each project type requires specific information about what you are building. Snowflake CLI currently supports the following project types:
Caution
Files inside a project directory are processed by Snowflake CLI and could be uploaded to Snowflake when executing other snow
commands. You should use caution when putting any sensitive information inside files in a project directory.
Using variables in SQL¶
Note
Support for variables requires project definition version 1.1.
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"
Note
If your current project definition file uses definition_version: 1
, you must update it to definition_version: 1.1
if you want to take advantage of the variables feature. If you do not change the value, Snowflake CLI ignores the env
section, but the other types of projects (snowpark
, in this example) still work as expected.
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.
Create project definition file templates¶
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 templates use 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"
Access template defaults¶
Template defaults let you access default and automatically-generated fields from a project definition file, even if the fields are not explicitly defined. To illustrate, consider the following Snowflake Native App project definition file:
definition_version: 1.1
native_app:
name: myapp
artifacts:
- src: app/*
dest: ./
This definition provides enough information to create a Snowflake Native App, so the default values for the application package and application instance are automatically generated when you create the application. You can then access these values using the following syntax:
In SQL code
&{ ctx.native_app.application.name } &{ ctx.native_app.package.name }
In other code languages
<% ctx.native_app.application.name %> <% ctx.native_app.package.name %>
Alter 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
Note
The variables and environment variables are case-sensitive.
You can also use the template 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 %>
Use template functions¶
To enable the concatenation of SQL identifiers such as database names and schema names, and to provide flexibility in using quoted or unquoted identifiers in different contexts, Snowflake CLI provides the following set of utility functions you can use in project template definition templates:
fn.concat_ids()
¶
Input: one or more string arguments (SQL ID or plain String)
Output: a valid SQL ID (quoted or unquoted)
The fn.concat_ids()
function concatenates multiple string arguments into a single string representing a SQL ID (quoted or unquoted). If any of the input strings is a valid quoted identifier, it will be unescaped before the concatenation. The resulting string is then escaped and quoted if it contains non-SQL safe characters or if any of the input strings was a valid quoted identifier.
Examples:
Calling
fn.concat_ids('id1_', '"quoted_id2"')
outputs"id1_quoted_id2"
because one of the input values is a quoted identifier.Calling
fn.concat_ids('id1_', 'id2')
outputsid1_id2
because none of the input values is a quoted identifier and none of the input values contains non SQL safe characters.
fn.str_to_id()
¶
Input: one or more string arguments (SQL ID or plain String)
Output: a valid SQL ID (quoted or unquoted)
The fn.str_to_id()
function returns a string as a an ID. If the input string contains a valid quoted or unquoted identifier, the function returns it as is. However, if the input string contains unsafe SQL characters that are not properly quoted, the function returns a quoted ID that escapes the unsafe characters.
Examples:
Calling
fn.str_to_id('id1')
returnsid1
because it is a valid unquoted identifier.Calling
fn.str_to_id('unsafe"id')
returns"unsafe""id"
because it contains unsafe SQL characters.
fn.id_to_str()
¶
Input: one string argument (SQL ID or plain String)
Output: a plain string
If the input is a valid SQL ID, the function returns an unescaped plain String. Otherwise, the function returns the input string as is.
Examples:
Calling
:fn.id_to_str('id1')
, returnsid1
because it is already unquoted.Calling :codenowrap:` fn.id_to_str(‘“quoted””id.example”’)` returns
quoted"id.example
.
fn.get_username()
¶
Input: one optional string containing the fallback value
Output: current username detected from the Operating System
Returns the current username from the operating system environment variables. If the current username is not found or is empty, it will either return an empty value or use the fallback value if one is provided.
Examples:
fn.get_username('default_user')
returns the current username if found, otherwise, it returnsdefault_user
.
fn.sanitize_id()
¶
Input: one string argument
Output: a valid non-quoted SQL ID
The function fn.sanitize_id()
removes any unsafe SQL characters from the input and returns it as a valid unquoted SQL ID. If the result does not start with a letter or an underscore, it appends an underscore to it. For very long strings, the function truncates the string to 255 characters.
Examples:
When using
fn.sanitize_id('Some.id"With_Special_Chars')
the output isSomeidWith_Special_Chars
.When using
fn.sanitize_id('1abc')
the output is_1abc
.
Sample use case¶
The following example shows how to use these functions in snowflake.yml
project definition files:
definition_version: 1.1
native_app:
name: myapp_base_name_<% fn.sanitize_id(fn.get_username()) %>
artifacts:
- src: app/*
dest: ./
package:
name: <% fn.concat_ids(ctx.native_app.name, ctx.env.pkg_suffix) %>
application:
name: <% fn.concat_ids(ctx.native_app.name, ctx.env.app_suffix) %>
env:
app_suffix: _app_instance
pkg_suffix: _pkg
The following example illustrates how to use the functions in a SQL file:
DESC APPLICATION &{ fn.str_to_id(ctx.native_app.application.name) };
DESC APPLICATION PACKAGE &{ fn.str_to_id(ctx.native_app.package.name) };
Differences between SQL and project definition file templates¶
SQL and project definition templates use different template syntaxes:
SQL uses the
&{ var }
syntax.Project definition files uses the
<% var %>
syntax to maintain compliance with the YAML specification.