SnowDDL
Search…
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. 1.
    It helps to reduce conflicts during development and testing.
  2. 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": ACCOUNT_PARAMETER, NETWORK_POLICY, RESOURCE_MONITOR. But 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. 1.
    Correct OWNERSHIP management.
  2. 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.
​
Copy link
On this page
Limitations
Env prefix and administration role
Env prefix placeholder