Env Prefix explained

Env prefix is a way to apply different versions of the same config multiple times and create independent "environments" on the same Snowflake account.

Env prefix is a string which is added to the beginning of all account-level object names created by SnowDDL.

For example, developer Alice uses prefix ALICE and developer Bob uses prefix BOB. When both Alice and Bob apply the same config to the same Snowflake account, they'll get two separate copies of all objects.

Object type	Original	ALICE	BOB
DATABASE	DB	ALICE__DB	BOB__DB
SCHEMA	DB.SCHEMA	ALICE__DB.SCHEMA	BOB__DB.SCHEMA
TABLE	DB.SCHEMA.TBL	ALICE__DB.SCHEMA.TBL	BOB__DB.SCHEMA.TBL
WAREHOUSE	COMPUTE_WH	ALICE__COMPUTE_WH	BOB__COMPUTE_WH
ROLE	ANALYST__B_ROLE	ALICE__ANALYST__B_ROLE	BOB__ANALYST__B_ROLE

In order to use env prefix, set argument --env-prefix when calling CLI interface.

This feature provides two main advantages:

1. It helps to reduce conflicts during development and testing.

2. It is easy to "destroy" and re-apply environment again. For example, when you switch from one branch to another.

Limitations

• Some object types do not support "env prefix", e.g. ACCOUNT_PARAMETER. Changes to these object types will NOT be applied automatically, unless you specifically ask for it using a special argument for each type.

• Maximum length of Snowflake identifier is 255 characters. Very long prefixes may cause SnowDDL to build identifiers longer than that limitation. Try to keep "env prefixes" short and simple.

• If you have too many environments created on the same Snowflake account, it may start causing performance issues in Web-interface and in SHOW ... commands. Make sure to destroy environments which are no longer needed to prevent this problem.

Env prefix and administration role

Env prefix is also applied to the administration role which is used by SnowDDL to create all objects.

For example, if your administration role is called SNOWDDL_ADMIN, and if you use "env prefix" ALICE, a new role called ALICE__SNOWDDL_ADMIN will be created, if it does not exist yet. This role will have grant for the original SNOWDDL_ADMIN role.

This trick is necessary to address two problems:

1. Correct OWNERSHIP management.

2. Avoiding the limit of 10,000 rows returned by SHOW <object> commands.

If all environments are "owned" by the same role, and if there are many objects in each environment, the system will eventually hit the limit, and SHOW commands will start crashing.

But if each environment is "owned" by its own role, the limit will not be reached.

Env prefix placeholder

Placeholder ${{ env_prefix }} is created automatically and always available for YAML configs. It might be useful for raw SQL fragments to access objects from another database, especially for VIEW definitions.

More information placeholders is available in YAML placeholders guide.

Env prefix separator

You may change separator between env prefix value and identifiers using CLI option --env-prefix-separator. It allows to chose from three pre-defined options:

• __ - double underscore, default, example: ALICE__DB

• _ - single underscore, example: ALICE_DB

• $ - dollar sign, example: ALICE$DB

Please note, using single underscore separator might seem attractive, but it is inherently risky and may cause some name clashing with objects in different environments.