# Introduction SnowDDL is a [declarative-style](https://www.snowflake.com/blog/embracing-agile-software-delivery-and-devops-with-snowflake/) tool for object management automation in [Snowflake](http://snowflake.com). It is not intended to replace other tools entirely, but to provide an alternative approach focused on practical data engineering challenges. You may find SnowDDL useful if: * complexity of object schema grows exponentially, and it becomes hard to manage; * your organization maintains multiple Snowflake accounts (dev, stage, prod); * your organization has multiple developers sharing the same Snowflake account and suffering from conflicts; * it is necessary to generate some part of configuration dynamically using Python; ### SingleDB (upd: Jun 2022) A new simplified [SingleDB mode](/single-db/overview) is now available for SnowDDL. It can be used to manage schemas and schema objects in a single database only. # Getting started ## System requirements * Python 3.9+ * [Snowflake Connector Python](https://docs.snowflake.com/en/user-guide/python-connector-install.html) * [PyYAML](https://github.com/yaml/pyyaml) * [JSONSchema](https://github.com/Julian/jsonschema) ## Hand-on example It will take about 10 minutes. 1\) Install SnowDDL. ``` pip install snowddl ``` 2\) Create a new [Snowflake Trial Account](https://signup.snowflake.com/) or create a [new account within your organization](https://docs.snowflake.com/en/sql-reference/sql/create-account.html). {% hint style="warning" %} Do NOT use existing production account with real data to test object management tools. {% endhint %} 3\) [Generate private and public key](https://docs.snowflake.com/en/user-guide/key-pair-auth#configuring-key-pair-authentication) for key-pair authentication of SnowDDL administrator user. {% hint style="info" %} It is still possible to use single-factor PASSWORD authentication for testing purpose, but it is NOT recommended due to updated Snowflake security guidelines: {% endhint %} 4\) Create administration user for SnowDDL. Replace `RSA_PUBLIC_KEY` with contents of generated public key. It should look like a single line without header & footer and without line-breaks. ```sql USE ROLE ACCOUNTADMIN; CREATE ROLE SNOWDDL_ADMIN; GRANT ROLE SYSADMIN TO ROLE SNOWDDL_ADMIN; GRANT ROLE SECURITYADMIN TO ROLE SNOWDDL_ADMIN; CREATE USER SNOWDDL TYPE = SERVICE RSA_PUBLIC_KEY = 'MIIBIjANBgkqh...' DEFAULT_ROLE = SNOWDDL_ADMIN; GRANT ROLE SNOWDDL_ADMIN TO USER SNOWDDL; GRANT ROLE SNOWDDL_ADMIN TO ROLE ACCOUNTADMIN; ``` 5\) Apply [first version](https://github.com/littleK0i/snowddl/tree/master/snowddl/_config/sample01_01) of sample config (provided with SnowDDL installation). Replace `` placeholder with Snowflake [account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html). Replace `` with path to private key file generated in step 3. ``` snowddl \ -c sample01_01 \ -a \ -u snowddl \ -k \ --apply-unsafe \ apply ``` Check database `SNOWDDL_DB` in Snowflake account. Check list of warehouses and roles. 6\) Apply [second version](https://github.com/littleK0i/snowddl/tree/master/snowddl/_config/sample01_02) of sample config (provided with SnowDDL installation). ``` snowddl \ -c sample01_02 \ -a \ -u snowddl \ -k \ --apply-unsafe \ apply ``` Check logs. Some objects will be altered, some objects will be dropped. 7\) Reset Snowflake account to the original state. All objects created earlier by SnowDDL will be dropped. ``` snowddl \ -c sample01_02 \ -a \ -u snowddl \ -k \ --apply-unsafe \ --destroy-without-prefix \ destroy ``` ### Congratulations! Now you are ready to create your own config and start experimenting.
# Main features ### 1) SnowDDL is "stateless" Unlike [schemachange](https://github.com/Snowflake-Labs/schemachange) and [Terraform](https://github.com/chanzuckerberg/terraform-provider-snowflake), SnowDDL does not maintain any kind of "state". Instead, it reads current metadata from Snowflake account, compares it with desired configuration and generates DDL commands to apply changes. > *You may use one configuration for multiple accounts. You may repair problems caused by human errors, incorrect manual interventions, unexpected bugs, system outages, etc.* ### 2) SnowDDL can revert changes SnowDDL can revert object schema to any point in the past. You may simply checkout previous version of configuration from Git and apply it with SnowDDL. > *Lack of option to revert changes is one of the biggest problems of imperative-style object management tools. It does not exist in SnowDDL.* ### 3) SnowDDL supports ALTER COLUMN Most changes to Snowflake table structure require full re-creation of table and micro-partitions with data. For large tables it may incur significant additional costs. But some changes are possible with [ALTER TABLE ... ALTER COLUMN](https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html) statement, which executes instantly and costs nothing. SnowDDL detects if it is possible to use ALTER TABLE before suggesting costly CREATE OR REPLACE TABLE ... AS SELECT. > *New columns can be added and some data types can be changed instantly, without full table rewrite and at no extra cost.* ### 4) SnowDDL provides built-in "role hierarchy" model Snowflake documentation mentions benefits of [role hierarchy](https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#roles), but it does not provide any real world examples. SnowDDL [offers](/guides/role-hierarchy) a well thought 3-tier role hierarchy model. It is easy to understand, largely automated and requires minimal configuration. Also, it is crystal clear for security officers and external auditors. > *GRANTS will no longer be a problem when organization complexity grows.* ### 5) SnowDDL re-creates invalid views automatically Views may become invalid when underlying objects were changed. SnowDDL detects such views using a free `.describe()` call, and re-creates such views if necessary. > *You'll get less complaints from users about invalid views. There is no need to maintain a separate script to fix views.* ### 6) SnowDDL simplifies code review DDL queries are classified into ["safe" and "unsafe"](/guides/other-guides/safe-unsafe) categories. "Safe" queries can be applied and reverted with little to no risk (e.g. `CREATE`). "Safe" queries usually do not require code review and can be applied immediately. "Unsafe" queries may potentially cause loss of data or security issues (e.g. `ALTER`, `DROP`). "Unsafe queries" usually do require more attention. This classification helps to manage code review process better, but it is optional. > *You decide which DDL categories to "apply" immediately and which categories to "suggest" for manual review and manual application by `ACCOUNTADMIN` later.*\ > \ > *SnowDDL will not accidentally DROP your database, unless you explicitly allow it to happen.* ### 7) SnowDDL supports creation of isolated "environments" for individual developers and CI/CD scripts Multiple independent versions of the same configuration can be applied to one Snowflake account using [env prefix](/guides/other-guides/env-prefix). Unique prefix will be added to name of each account-level object, which allows multiple developers to work on the same code simultaneously without conflicts. It is also helpful for automated testing, when each set of tests will be executed in a separate "environment". > *For example, you have a production database called `BOOKINGS`. Alice can create her own dev copy called `ALICE__BOOKINGS`, and Bob can create his own dev copy called `BOB__BOOKINGS`. Alice and Bob will never clash during development. Such "environments" can be created and destroyed instantaneously* *at any time.* ### 8) SnowDDL strikes a good balance between dependency management overhead and parallelism Different object types are resolved sequentially. But objects of the same type are resolved [in parallel](/guides/other-guides/dependency-management). It provides great performance for configurations with large number of objects, but it also simplifies dependency management. > *All views are created after all tables. All tables are created after all schemas. Only rare dependencies within the same object type should be maintained (e.g. view depends on another view).* ### 9) SnowDDL configuration can be generated dynamically in Python code When basic YAML files are no longer enough, you may build and modify configuration [programmatically](/advanced/programmatic-config) by adding Python modules to your config. ### 10) SnowDDL can manage packages for Java and Python UDF scripts natively Recently, Snowflake introduced [Snowpark](https://docs.snowflake.com/en/developer-guide/snowpark/index.html) and [UDF functions](https://docs.snowflake.com/en/sql-reference/sql/create-function.html) written in Java, Scala, Python. Such functions may rely on external packages and libraries, which should be uploaded to internal stages. Changes in packages should be synchronised with changes in UDF function code, and SnowDDL can do it for you using special object type [`STAGE FILE`](/basic/yaml-configs/stage-file). # SnowDDL vs. Declarative DCM In June 2024 Snowflake released native "Declarative DCM" feature. You may read more about it here: * Blog post with feature description and examples: * CREATE OR ALTER TABLE usage notes: * CREATE OR ALTER TASK usage notes: This is how SnowDDL declarative approach compares with native "Declarative DCM". | Feature | SnowDDL | Declarative DCM | | ---------------------------------------------- | -------------------------------- | -------------------------------------- | | Object type support | Most object types are supported. | Only `TABLE` and `TASK` are supported. | | Preview of specific changes before applying | Yes | No | | Capable of CREATE OR REPLACE TABLE | Yes | No | | Supports creation of `TASKS` in specific order | Yes | No | As of June 2024, "Declarative DCM" feature seems to be incomplete. We do not recommend using it in its current form. SnowDDL does not rely on it internally. # Object identifiers ## Identifier rules SnowDDL takes a slightly different approach to resolution of [Snowflake identifiers](https://docs.snowflake.com/en/sql-reference/identifiers-syntax.html). * Identifiers are always upper case. * Identifiers are always enclosed in `"` (double-quotes) when formatted for SQL queries. * Only `ascii_letters`(a-z), `digits` (0-9), `_` (underscore) and `$` (dollar) characters are allowed. * For complex identifiers (with [env prefix](/guides/other-guides/env-prefix) or role suffix), the delimiter is always `__` (double underscore). ## Rationale * Lower case identifiers are usually created by accident. Unlike upper case identifiers, lower case identifiers must be enclosed in `"` to access. Mix of lower case and lower case identifiers cause a lot of confusion. SnowDDL does not allow lower case identifiers to prevent this confusion from happening.
* Some words in Snowflake are [reserved](https://docs.snowflake.com/en/sql-reference/reserved-keywords.html) (e.g. `CREATE`, `TABLE`, `WHEN`). Such words cannot be used as identifiers without being enclosed in `"`. However, the list of reserved words is being updated all the time, and a word which is not reserved today may become reserved tomorrow. SnowDDL makes sure all identifiers will be valid in DDL queries regardless of current state of "reserved words".
* Snowflake has a session parameter which is called [`QUOTED_IDENTIFIERS_IGNORE_CASE`](https://docs.snowflake.com/en/sql-reference/parameters.html#quoted-identifiers-ignore-case). It applies upper case transformation to all identifiers, including identifiers enclosed in `"`. SnowDDL will work the same way regardless of value of this parameter.
* For complex identifiers a single `_` underscore is not good enough to identify multiple parts of identifier. Collisions are possible. For example, if you have schema `AAA.BBB_CCC` and schema `AAA_BBB.CCC`, generation of schema role name with `_` single underscore as glue will return exactly the same identifier for both. SnowDDL uses `__` (double underscore) in order to mitigate this and improve readability. ## Examples [Env prefix](/guides/other-guides/env-prefix) in examples is `ALICE`. * Warehouse name in config: `analytics_wh` * Identifier in SQL query: `"ANALYTICS_WH"` * Identifier with env prefix: `"ALICE__ANALYTICS_WH"`
* Table name in config: `my_db.bookings.airports` * Identifier in SQL query: `"MY_DB"."BOOKINGS"."AIRPORTS"` * Identifier with env prefix: `"ALICE__MY_DB"."BOOKINGS"."AIRPORTS"` * Function name in config: `my_db.bookings.lang(varchar)` * Identifier in SQL query: `"MY_DB"."BOOKINGS"."LANG"(VARCHAR)` * Identifier with env prefix: `"ALICE__MY_DB"."BOOKINGS"."LANG"(VARCHAR)` * Business role name in config: `bookings_analyst` * Identifier in SQL query: `"BOOKINGS_ANALYST__B_ROLE"` * Identifier with env prefix: `"ALICE__BOOKINGS_ANALYST__B_ROLE"` * Owner schema role for schema: `MY_DB.BOOKINGS` * Identifier in SQL query: `"MY_DB__BOOKINGS__OWNER__S_ROLE"` * Identifier with env prefix: `"ALICE__MY_DB__BOOKINGS__OWNER__S_ROLE"` # Data types SnowDDL supports [native Snowflake data types](https://docs.snowflake.com/en/sql-reference/intro-summary-data-types.html) only. Synonyms and aliases are not allowed. Timestamp data types should be defined explicitly. If data type has additional parameters (e.g. length), the full form should be used in all cases. ## Supported data types * `NUMBER(x,y)` * `FLOAT` * `BINARY(x)` * `BOOLEAN` * `VARCHAR(x)` * `DATE` * `TIME(x)` * `TIMESTAMP_LTZ(x)` * `TIMESTAMP_NTZ(x)` * `TIMESTAMP_TZ(x)` * `VARIANT` * `OBJECT` * `ARRAY` * `GEORGRAPHY` * `VECTOR(x,y)` * `FILE` * `DECFLOAT(x)` * `UUID` ## Rationale * SnowDDL uses an actual data types obtained from `SHOW` and `DESC` commands to compare config with Snowflake metadata. Values should be exactly the same. * It is much easier to find & replace data types in config when exactly the same syntax is being used in all cases. Especially when you have 1000+ tables to manage. * TIMESTAMP data types have significant differences and are affected by various ACCOUNT PARAMETERS. It is very important for data engineers to understand it and to pick the correct data type depending on specific use case. # Object types Object types are sorted in execution order. See explanation after the table.
Object typeCREATECOMPAREDROPNotes
ACCOUNT_PARAMETERN/AUNSAFE +N/AACCOUNTADMIN, skip on empty config
RESOURCE_MONITORUNSAFEUNSAFEUNSAFEACCOUNTADMIN
WAREHOUSESAFESAFESAFEACCOUNTADMIN to assign RESOURCE_MONITOR
WAREHOUSE_ROLESAFESAFESAFE
DATABASESAFEUNSAFEUNSAFE
SCHEMASAFEUNSAFEUNSAFE
SCHEMA_ROLESAFESAFESAFE
SECRETSAFESAFESAFE
NETWORK_RULESAFESAFE and UNSAFEUNSAFEALTER is safe, REPLACE is unsafe
EXTERNAL_ACCESS_INTEGRATIONSAFESAFESAFE
FILE_FORMATSAFESAFESAFEShort hash
STAGESAFESAFE and UNSAFEUNSAFE
SEQUENCESAFESAFEUNSAFE
FUNCTIONSAFESAFESAFEShort hash
EXTERNAL_FUNCTIONSAFESAFESAFEShort hash
PROCEDURESAFESAFESAFEShort hash
TABLESAFESAFE and UNSAFE +UNSAFE
EVENT_TABLESAFESAFE and UNSAFEUNSAFE
HYBRID_TABLESAFEUNSAFEUNSAFEShort hash
DYNAMIC_TABLESAFESAFE and UNSAFEUNSAFE
EXTERNAL_TABLESAFESAFESAFEShort hash
PRIMARY_KEYSAFESAFESAFE
UNIQUE_KEYSAFESAFESAFE
FOREIGN_KEYSAFESAFESAFE
MATERIALIZED_VIEWSAFEUNSAFEUNSAFE
VIEWSAFESAFESAFE
SEMANTIC_VIEWSAFESAFESAFEShort hash
PIPESAFEUNSAFEUNSAFEShort hash
STREAMSAFEUNSAFEUNSAFE
TASKSAFESAFESAFEShort hash
ALERTSAFESAFESAFE
SHARE (outbound)UNSAFE +UNSAFE +UNSAFE +
TECHNICAL_ROLESAFESAFESAFE
BUSINESS_ROLESAFESAFESAFE
USER_ROLESAFESAFESAFE
USERSAFEUNSAFEUNSAFE
AGGREGATION_POLICYUNSAFE +UNSAFE +UNSAFE +
AUTHENTICATION_POLICYUNSAFE +UNSAFE +UNSAFE +Short hash
JOIN_POLICYUNSAFE +UNSAFE +UNSAFE +
MASKING_POLICYUNSAFE +UNSAFE +UNSAFE +
NETWORK_POLICYUNSAFEUNSAFEUNSAFE
PROJECTION_POLICYUNSAFE +UNSAFE +UNSAFE +
ROW_ACCESS_POLICYUNSAFE +UNSAFE +UNSAFE +
BACKUP_POLICYUNSAFEUNSAFEUNSAFE
BACKUP_SETUNSAFEUNSAFEUNSAFE
### Notes * Safe & unsafe operations are explained in ["Safe & unsafe DDL" guide](/guides/other-guides/safe-unsafe). * Some role types are generated dynamically. You may read more in [Role hierarchy guide](/guides/role-hierarchy). * `+` means that additional setting or [CLI argument](/basic/cli) might be required to apply this change. * `ACCOUNTADMIN` note means that only user with this role can apply this change. It should be applied by human administrator manually or by SnowDDL user having this role automatically. * `Short hash` note means that objects of this type will have a short hash added to `comment`. It is explained in [Short hash guide](/guides/other-guides/short-hash). * In some cases both "safe" and "unsafe" changes can be applied to the same object type and operation type. Please refer to specific object type documentation for more details. # Role hierarchy Snowflake documentation mentions benefits of [role hierarchy](https://docs.snowflake.com/en/user-guide/security-access-control-overview.html#roles), but it does not provide any real world examples. SnowDDL offers a well thought 3-tier role hierarchy model. It is easy yo understand, largely automated and requires minimal configuration. ## General overview All roles are separated into 3 major tiers. * **Tier 1:** roles granting access privileges for specific objects in Snowflake account.\ It includes: `DATABASE_ACCESS_ROLE`, `SCHEMA_ACCESS_ROLE`, `SHARE_ACCESS_ROLE`, `WAREHOUSE_ACCESS_ROLE`, `TECHNICAL_ROLE`. * **Tier 2:** roles granting access for Tier 1 roles according to specific business functions.\ It includes: `BUSINESS_ROLE`. * **Tier 3**: roles granting access for Tier2 business roles to specific users.\ It includes: `USER_ROLE`. ![](/files/BAOWuWEYJJm74IbrPt1a) `BUSINESS_ROLE` and `TECHNICAL_ROLE` are configured manually. Other role types are created automatically based on configuration of corresponding objects. ## Naming convention ![](/files/eSuXYtKKwiKRcmqcE17A) Role names are complex identifiers with parts separated by `__` (double underscore). Each role name starts with optional [env prefix](/guides/other-guides/env-prefix). The next part is a role name (for manually configured roles) or name of entity (for automatically generated roles). The next optional part is a sub-type of role, which may or may not exist, depending on role type. And the final part is a role type suffix. * `DATABASE_ACCESS_ROLE` => `__D_ROLE` * `SCHEMA_ACCESS_ROLE` => `__S_ROLE` * `SHARE_ACCESS_ROLE` ⇒ `__SH_ROLE` * `WAREHOUSE_ACCESS_ROLE` => `__W_ROLE` * `BUSINESS_ROLE` => `__B_ROLE` * `TECHNICAL_ROLE` => `__T_ROLE` * `USER_ROLE` => `__U_ROLE` Ultimately, this naming convention makes it easier to select specific subsets of roles using command: ```sql SHOW ROLES LIKE ''; ``` ## Tier 1 roles ### Database access roles
Database access roles are created automatically when [permission model](/basic/yaml-configs/permission-model) is configured with ruleset `DATABASE_OWNER`. This is helpful when you have external software which is hardcoded to create its own schemas, like Fivetran or Airbyte. In this case we do not know names of schemas beforehand, so schema roles cannot be created in advance. But we know database name, and permission can be managed on database level. Similar to schema access roles, SnowDDL creates 3 types of database access roles: * `__OWNER__D_ROLE` - provides OWNERSHIP privileges for objects in database; * `__READ__D_ROLE` - provides generic READ and USAGE access for objects in database; * `__WRITE__D_ROLE` - provides generic WRITE access for objects in database; ### Schema access roles ![](/files/1ZMZ52SUMx8k2fkZKEbg) Schema access roles are created automatically for each schema which is present in config. Grants and future grants to schema access roles are applied based on [permission model](/basic/yaml-configs/permission-model). SnowDDL creates 3 types of schema access roles: * `__OWNER__S_ROLE` - provides OWNERSHIP privileges for objects in schema; * `__READ__S_ROLE` - provides generic READ and USAGE access for objects in schema; * `__WRITE__S_ROLE` - provides generic WRITE access for objects in schema; ### Share access roles Share access roles are created automatically for each share mentioned in [business role config](/basic/yaml-configs/business-role) or [schema config](/basic/yaml-configs/schema). ShowDDL creates 1 type of share access roles: * `__SH_ROLE` - provides IMPORTED PRIVILEGES for inbound share; ### Warehouse access roles ![](/files/iSvxlHffUO0ws9C6AMoK) Warehouse access roles are created automatically for each warehouse. SnowDDL creates 2 types of warehouse access roles: * `__USAGE__W_ROLE` - USAGE and OPERATE privileges for warehouse; * `__MONITOR__W_ROLE` - MONITOR and OPERATE privileges for warehouse; ### Technical roles ![](/files/0ioSbrAOGr5l2zXG3s2v) Technical roles are [configured manually](/basic/yaml-configs/technical-role). Each tole role provides specific privileges to specific objects. Only normal grants are supported, not future grants. Tech roles are useful when you need to provide specific privileges for a few specific objects. Schema roles should be preferred to tech roles if possible. ## Tier 2 roles ### Business roles ![](/files/DJRVCzJkqsVHHnj5XuHH) Business roles are [configured manually](/basic/yaml-configs/business-role). Business roles combines multiple Tier 1 roles into specific business function. For example: `ANALYST`, `DEVELOPER`, `EXTERNAL_AUDITOR`, `ETL_SCRIPT`, `DBT`, `FIVETRAN`, etc. Normally each business role should have access to at least one schema and be able to use at least one warehouse. Also, it is possible to assign a "global role" to business role, which is created outside of SnowDDL. If you have a very specific use-case which is not covered by SnowDDL, you may always implement it manually by creating a custom `ROLE` using `ACCOUNTADMIN` and assigning this role to business role via `global_roles` config option. ## Tier 3 roles ### User roles ![](/files/CwfHhphU3IkwYusEOeUn) User roles are created automatically for each user. User roles provide access to one or more business roles. Specific business roles provided to user are configured in [USER](/basic/yaml-configs/user) object type. ## Rationale This 3-tier role model provides a tremendous advantage when it comes to real world maintenance of permissions in Snowflake. * Model is easy to understand and explain to non-technical users. It helps to pass security audits. * Typical changes produce a very small SQL footprint. For example, when you want to grant access for a schema to 50 analysts, SnowDDL may produce only 1 line of SQL. * Native Snowflake [secondary roles](https://medium.com/snowflake/secondary-roles-in-snowflake-c74587cbb5fa) are not needed thanks to each user having a dedicated user role by design. * You may easily "impersonate" specific user and test their permissions by running the following command: `USE ROLE __U_ROLE;` * Unused roles for non-existent schemas, warehouses, shares, users are dropped automatically. It helps to reduce amount of "orphan" roles in account. # Permission model ## What is permission model? Permission model is a special concept introduced by SnowDDL to help managing permissions for databases and schemas. Permission model holds information about `CREATE GRANTS` and `FUTURE GRANTS`. Permission models are optional. If you do not set it explicitly, `DEFAULT` model will be used for all schemas. You may check the specific configuration of `DEFAULT` model on [this page](/basic/yaml-configs/permission-model). ## Grants Permission model allows you to specify the following types of grants: * OWNER create grants - which types of objects can be created by OWNER role; * OWNER future grants - which future grants should be applied to OWNER role (usually `OWNERSHIP`) * WRITE future grants - which future grants should be applied to WRITE role * READ future grants - which future grants should be applied to READ role ## Rulesets Additionally, you may choose one of two "rulesets" defining how grants should be applied. Currently available rulesets are called: `SCHEMA_OWNER` and `DATABASE_OWNER`. Here is the comparison table: | SCHEMA\_OWNER | DATABASE\_OWNER | | ---------------------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------ | | Selected by default | Must be configured explicitly | | `OWNER` role is created on schema level | `OWNER` role is created on database level | | `OWNER` cannot create new schemas | `OWNER` can create new schemas | | Schemas are owned by SnowDDL admin | Schemas are owned by `OWNER` role | | Schemas are created with `MANAGED ACCESS` | Schemas are created without `MANAGED ACCESS` | | `READ` and `WRITE` roles are created on schema level | `READ` and `WRITE` roles are created on database level, but additionally on schema level for each explicitly configured schema | `DATABASE_OWNER` ruleset is typically used for external tools which absolutely require ability to create their own schemas, such as Fivetran, Airbyte, etc. ## Setting permission model 1. Define permission models(s) in [config](/guides/permission-model). 2. Set permission model(s) in [DATABASE](/basic/yaml-configs/database) or [SCHEMA](/basic/yaml-configs/schema) configs. If permission model is set on DATABASE level, all its SCHEMAS inherit it by default. You can mix & match different permission models across different schemas, but the ruleset on all models in one DATABASE must be the same. # Other guides # Administration user Administration permissions in Snowflake is a [complicated topic](https://docs.snowflake.com/en/user-guide/security-access-control-considerations.html). SnowDDL suggests two main options to set up the admin user, each having its own pros and cons. ## 1) Role with SYSADMIN + SECURITYADMIN Create a dedicated `USER` and `ROLE` for SnowDDL. Grant roles `SYSADMIN` and `SECURITYADMIN` to it. ```sql USE ROLE ACCOUNTADMIN; CREATE ROLE SNOWDDL_ADMIN; GRANT ROLE SYSADMIN TO ROLE SNOWDDL_ADMIN; GRANT ROLE SECURITYADMIN TO ROLE SNOWDDL_ADMIN; CREATE USER SNOWDDL TYPE = SERVICE RSA_PUBLIC_KEY = '' DEFAULT_ROLE = SNOWDDL_ADMIN; GRANT ROLE SNOWDDL_ADMIN TO USER SNOWDDL; GRANT ROLE SNOWDDL_ADMIN TO ROLE ACCOUNTADMIN; ``` Pros: * SnowDDL will only manage objects created by `SNOWDDL_ADMIN` role. You will be able to have other databases, warehouses, users, roles created manually or by other tools (e.g. Fivetran). * `ACCOUNTADMIN` will have full access to objects created by `SNOWDDL_ADMIN`. * SnowDDL will be able to create and alter most objects types. Cons: * SnowDDL will not be able to apply changes for some object types, like `ACCOUNT_PARAMETER`, `RESOURCE_MONITOR`. But SnowDDL will be able to "suggest" changes, which can be reviewed and applied manually by user with `ACCOUNTADMIN` role. ## 2) Role with ACCOUNTADMIN Create a dedicated `USER` and `ROLE` for SnowDDL. Grant role `ACCOUNTADMIN` to it. ```sql USE ROLE ACCOUNTADMIN; CREATE ROLE SNOWDDL_ADMIN; GRANT ROLE ACCOUNTADMIN TO ROLE SNOWDDL_ADMIN; CREATE USER SNOWDDL TYPE = SERVICE RSA_PUBLIC_KEY = '' DEFAULT_ROLE = SNOWDDL_ADMIN; GRANT ROLE SNOWDDL_ADMIN TO USER SNOWDDL; ``` Pros: * SnowDDL will be able to apply changes to all object types. * It will be possible to manage Snowflake accounts in fully automated way, without any kind of "review" and manual application of DDL queries by humans. Cons: * You may apply some unwanted changes to `ACCOUNT_PARAMETER`, `NETWORK_POLICY` or `RESOURCE_MONITOR`, which may cause security issues, damage your data or make you spend too much credits. * `ACCOUNTADMIN` role will no longer be an "ultimate admin" role. It is not possible to grant `ACCOUNTADMIN` to `SNOWDDL_ADMIN` and grant `SNOWDDL_ADMIN` to `ACCOUNTADMIN` at the same time due to circular dependency. `ACCOUNTADMN` will not be able to access objects created by `SNOWDDL_ADMIN`. {% hint style="danger" %} Do not grant `ACCOUNTADMIN` role directly to `SNOWDDL` user. It may cause SnowDDL to wipe all users and data on your account, if not configured properly.\ \ Always create a dedicated role for SnowDDL. {% endhint %} ## Administration WAREHOUSE SnowDDL does not require an active warehouse for most operations. But a very few use cases still require an active warehouse, such as: * `CREATE OR REPLACE TABLE AS SELECT ...` when `ALTER TABLE` is not available. * Management of `MASKING POLICIES` and `ROW ACCESS POLICIES`, due to calls to table function `POLICY_REFERENCES`. In order to cover these use cases, you should create a `WAREHOUSE` and assign it to SnowDDL user and role. Please adjust warehouse size accordingly. ```sql USE ROLE ACCOUNTADMIN; CREATE WAREHOUSE SNOWDDL_WH WAREHOUSE_SIZE = 'XSMALL' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE; GRANT USAGE, OPERATE ON WAREHOUSE SNOWDDL_WH TO ROLE SNOWDDL_ADMIN; ALTER USER SNOWDDL SET DEFAULT_WAREHOUSE = SNOWDDL_WH; ``` {% hint style="danger" %} Do not create administration warehouse using SnowDDL config. Unlike warehouses created by `ACCOUNTADMIN`, warehouses from config can be created or dropped automatically at any time. {% endhint %} ## Shares Additional privileges are required to process [outbound shares](/basic/yaml-configs/share-outbound): ```sql GRANT CREATE SHARE ON ACCOUNT TO ROLE SNOWDDL_ADMIN; ``` It may be also required to grant `OVERRIDE SHARE RESTRICTIONS` if you want to share data from Business Critical edition account to lower edition account(s): ```sql GRANT OVERRIDE SHARE RESTRICTIONS ON ACCOUNT TO ROLE SNOWDDL_ADMIN; ``` # Integrations Currently most types of [INTEGRATION](https://docs.snowflake.com/en/sql-reference/sql/create-integration.html) objects are not managed by SnowDDL and should be created manually by `ACCOUNTADMIN`. Setting up an integration normally require additional steps to be performed outside of Snowflake, which is out of scope of SnowDDL. Integration should be granted to SnowDDL [administration role](/guides/other-guides/admin) to make it possible to use it in definition of other objects (e.g. `STORAGE INTEGRATION` for `STAGE`, `NOTIFICATION INTEGRATION` for `PIPE`, etc.). ## Example of storage integration ```sql CREATE STORAGE INTEGRATION TEST_STORAGE_INTEGRATION TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = GCS ENABLED = TRUE STORAGE_ALLOWED_LOCATIONS = ('*'); GRANT USAGE ON INTEGRATION TEST_STORAGE_INTEGRATION TO ROLE SNOWDDL_ADMIN; ``` ## Example of API integration ```sql CREATE API INTEGRATION TEST_API_INTEGRATION API_PROVIDER=aws_api_gateway API_AWS_ROLE_ARN='arn:aws:iam::123456789012:role/my_cloud_account_role' API_ALLOWED_PREFIXES=('https://xyz.execute-api.us-west-2.amazonaws.com/production') ENABLED=TRUE; GRANT USAGE ON INTEGRATION TEST_API_INTEGRATION TO ROLE SNOWDDL_ADMIN; ``` ## Example of Notification Integration ```sql CREATE NOTIFICATION INTEGRATION TEST_NOTIFICATION_INTEGRATION DIRECTION = OUTBOUND TYPE = QUEUE NOTIFICATION_PROVIDER=AWS_SNS AWS_SNS_ROLE_ARN='arn:aws:iam::123456789012:role/my_cloud_account_role' AWS_SNS_TOPIC_ARN='arn:aws:sns:us-east-1:123456789012:MyTopic' ENABLED=TRUE; GRANT USAGE ON INTEGRATION TEST_NOTIFICATION_INTEGRATION TO ROLE SNOWDDL_ADMIN; ``` # Inbound shares Currently inbound shares are not managed by SnowDDL and should be created manually by `ACCOUNTADMIN`. Setting up inbound shares normally requires some additional steps performed outside of Snowflake. Also, only one `DATABASE` can be created for each inbound share, which limits [env prefix](/guides/other-guides/env-prefix) functionality. Creation of inbound shares and databases are explained in [Snowflake documentation](https://docs.snowflake.com/en/user-guide/data-share-consumers). 1. Configure outbound share to target your account. Make sure it is available using `SHOW SHARES` command. 2. Run `CREATE DATABASE FROM SHARE `. Once the initial configuration is done, you may grant access to objects in share to [business roles](/basic/yaml-configs/business-role) using `share_read` parameter: * Use share name to grant access to all objects using IMPORTED PRIVILEGES; * Use database role name in share to grant access to this database role only; For example: ```yaml share_read: - snowflake # this grants access to an entire SNOWFLAKE share - snowflake.object_viewer # this grants access to OBJECT_VIEWER database role only ``` # Object OWNERSHIP OWNERSHIP is a special privilege in Snowflake. You should read & fully understand the [Access Control Snowflake](https://docs.snowflake.com/en/user-guide/security-access-control-configure.html) documentation first. SnowDDL implementation of OWNERSHIP is following: * Account-level object types are typically owned by SnowDDL admin role * Schema-level objects are typically owned by `SCHEMA ROLE (OWNER)` or `DATABASE ROLE (OWNER)` which are created automatically. For schema-level objects OWNERSHIP is assigned using [FUTURE GRANTS.](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html#future-grants-on-database-or-schema-objects) There is no "race condition" between creation of object and change of OWNERSHIP, which is common for other object management tools. SnowDDL typically creates schemas with `MANAGED ACCESS`. It means that users having `SCHEMA ROLE (OWNER)` can create / alter / drop objects in such schema, but they cannot grant access to objects in this schema to some other role (e.g. to `PUBLIC`). This is very important for security. ## Practical example ``` MY_DB (owned by SNOWDDL_ADMIN) |-- MY_SCHEMA (owned by SNOWDDL_ADMIN) |-- MY_TABLE (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE) |-- MY_VIEW (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE) |-- MY_FUNCTION (owned by MY_DB__MY_SCHEMA__OWNER__S_ROLE) MY_WAREHOUSE (owned by SNOWDDL_ADMIN) MY_USER (owned by SNOWDDL_ADMIN) MY_RESOURCE_MONITOR (owned by SNOWDDL_ADMIN) ``` # Safe & unsafe DDL DDL queries are classified into "safe" and "unsafe" categories. * "Safe" queries can be applied and reverted with little to no risk (e.g. `CREATE`). "Safe" queries usually do not require code review. * "Unsafe" queries may potentially cause loss of data or security issues (e.g. `ALTER`, `DROP`). "Unsafe queries" usually do require more attention from reviewers. By default only "safe" queries are applied by SnowDDL. Add argument `--apply-unsafe` when calling SnowDDL [CLI interface](/basic/cli) to apply unsafe queries as well. ## Special cases On top of that, some "unsafe" queries require an additional argument to be applied. * `--apply-replace-table` is a safety check to prevent overspending of credits. Unlike `ALTER TABLE`, `CREATE OR REPLACE TABLE ... AS SELECT` requires an active warehouse and full rewrite of original table, which may cost a lot if table is big. * `--apply-account-params` is a safety check to prevent accidental changes in ACCOUNT PARAMETERS. It may cause security issues if applied to account-level NETWORK POLICY without review. It may cause timeouts during query execution, it may break timestamp-related settings, etc. etc. * `--apply-network-policy` is a safety check to prevent changes in NETWORK POLICIES. Unlike "on premise" DWH systems, Snowflake is exposed to anyone on the Internet, and the only thing preventing a free access is NETWORK POLICY. Updates to NETWORK POLICY should not be applied without review. * `--apply-resource-monitor` is a safety check to prevent changes in RESOURCE MONITORS. It may cause all sorts of issues from overspending to inability of business users to run even a single query due to resource monitors running out of credits quota. * `--apply-masking-policy` and `--apply-row-access-policy` is a safety check for data security "policies". The main issue with policies is related to lack of transactional DDL in Snowflake. Currently it is not possible to re-create the whole policy "atomically". First all references must be dropped, after that policy can be re-created, and all refs should be re-applied once again. But during all these operations your data remains potentially exposed. All changes to policies should be applied under the strict control. ## Suggested queries All queries which are not "applied" due to safety settings, are "suggested" instead. Suggested queries are outputted to `STDOUT` and are available for copy-paste and manual review by administrator. You can apply such queries manually using `ACCOUNTADMIN` role. All "suggested" queries use fully-qualified identifiers, and the "[OWNERSHIP](/guides/other-guides/ownership)" of schema-level object will remain intact due to FUTURE GRANTS. # Dependency management SnowDDL dependency management system is simple and straightforward. ## Dependencies across different object types There is NO NEED for dependency management across different object types. All object types are resolved sequentially in order described in [Object types](/guides/object-types) guide. It means: * all views are created after all tables * all tables are created after all schemas * all schemas created after all databases * etc. If one object type relies on another object type (for example, `EXTERNAL_TABLE` relies on `FILE_FORMAT`), you may assume that it will be available. ## Dependencies across the same object type Sometimes objects of the same type depend on each other. In this case an explicit dependency must be added to make sure that objects will be created in the right order. Currently the following object types support explicit dependencies: * `TASKS` - property `after` is a reference to another task; * `VIEWS` - property `depends_on` is a reference to another view; In future dependency management for `FUNCTIONS` and `PROCEDURES` might be added, if it becomes necessary. You may have any number of "levels" of dependencies. {% hint style="warning" %} If dependency could not be resolved (e.g. ref object was removed from config or renamed), SnowDDL will try to process such object anyway, so you'll have a chance to see an SQL error. {% endhint %} # Short hash explained When SnowDDL compares desired config vs. actual metadata in Snowflake, it normally checks every property of every object. But in some cases it is not practical due to: * lack of all object properties in output of `SHOW` and `DESC` commands; * a large number of properties and frequent changes in format of such properties; SnowDDL implements a simplified method to compare objects of the following types: * `EXTERNAL_FUNCTION` * `EXTERNAL_TABLE` * `FILE_FORMAT` * `FUNCTION` * `PIPE` * `PROCEDURE` * `STAGE` * `TASK` ## How does short hash work 1. When object is created for the first time, SnowDDL builds `CREATE OR REPLACE ` SQL and executes it. 2. Right after that SnowDDL calculates a **short hash** based on SQL text and stores it at the end of `comment` of created object. The original `comment` is preserved, but it is now a bit longer. 3. Later on, when existing object is "compared" by SnowDDL, it builds `CREATE ` SQL again, calculates a new **short hash** and tries to match it with existing **short hash** stored in object `comment` earlier. Unlike other properties, all `SHOW` commands always return `comment`. 4. If calculated **short hash** from SQL and existing **short hash** from `comment` are the same, SnowDDL assumes that object remains the same and returns `NOCHANGE` result. 5. If calculated **short hash** from SQL and existing **short hash** are different, SnowDDL assumes that at least one property was changed, and object is fully re-created. ## How short hash is calculated ```python import base64 import hashlib def _short_hash(self): sha1_digest = sha1(str(self).encode('UTF-8')).digest() return f"#{urlsafe_b64encode(sha1_digest[:12])}" ``` Short hash is the first 12 bytes (out of 20) from SHA1, additionally encoded with url-safe base64 encode. It is a good balance between length and quality of hash. The collisions are possible, but are extremely unlikely in real-world scenarios. If Snowflake makes `SHOW` commands better in future, it will be possible to replace short hashes with full property checks, but it is not practical at this moment. # 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 typeOriginalALICEBOB
DATABASEDBALICE__DBBOB__DB
SCHEMADB.SCHEMAALICE__DB.SCHEMABOB__DB.SCHEMA
TABLEDB.SCHEMA.TBLALICE__DB.SCHEMA.TBLBOB__DB.SCHEMA.TBL
WAREHOUSECOMPUTE_WHALICE__COMPUTE_WHBOB__COMPUTE_WH
ROLEANALYST__B_ROLEALICE__ANALYST__B_ROLEBOB__ANALYST__B_ROLE
In order to use env prefix, set argument `--env-prefix` when calling [CLI interface](/basic/cli). 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](/guides/other-guides/ownership) management. 2. Avoiding the limit of 10,000 rows returned by `SHOW ` 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](/basic/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. # Team workflow {% hint style="info" %} This page will be updated with more detailed information soon. Please stay tuned! {% endhint %} ![](/files/4gIHSCJXnWUfmsXBguH6) The intended setup and workflow for data engineering team using SnowDDL is the following: 1. Create a separate Snowflake account for development purposes. You may do it using [CREATE ACCOUNT](https://docs.snowflake.com/en/sql-reference/sql/create-account.html) and "organizations" feature. 2. Create an initial SnowDDL configuration, keep it in Git or another version control system. 3. When developer starts to work on new ticket, he or she creates a new branch, pulls config with latest changes and creates a separate "environment" in DEV Snowflake account using SnowDDL with [env prefix](/guides/other-guides/env-prefix). "Environment" can be destroyed and fully re-created at any moment. 4. When developer finishes coding, he or she pushes the code and sends it for review. Reviewer validates changes in config and approves or declines it. 5. All approved branches are merged into single "release" branch and automatically tested in a separate "environment" on the same DEV Snowflake account. This step is necessary to make sure that changes from multiple different branches are compatible with each other. 6. If "release" branch test was successful, config can now be deployed to production. SnowDDL should apply config to PROD Snowflake account without [env prefix](/guides/other-guides/env-prefix). 7. "Safe" changes are applied automatically. "Unsafe" changes are reviewed by release engineer once again and applied manually. \--- This workflow provides the robust, flexible and controlled release cycle. Developers have their own personal copy of DEV environment. Release branches are always tested before deployment. The process of rolling out config changes is as "safe" or "unsafe" as you want it to be. # Limitations & workarounds ## Partial application of config One of fundamental limitations of Snowflake is lack of transaction support for DDL commands. All DDL commands are executed 1-by-1 and are committed immediately. In practice, it means that only some DDL commands might be executed, and the object schema will be stuck somewhere in between the original state and desired state described in config. All object management tools are affected by this problem, but SnowDDL is generally in better position. You may fix technical issues and restart it any number of times, and SnowDDL will do its best to "repair" the object schema and bring it to the final state. Please make sure to always keep & check SnowDDL execution logs and detect warnings early. ## Renaming of objects Renaming is currently not supported. Full object names are used as unique identifiers to match configuration entries with existing objects in Snowflake account. If you already use [env prefix](/guides/other-guides/env-prefix) feature for DEV and QA, usually it is not a problem. You may destroy & create all objects with specific env prefix from scratch, which handles renaming automatically. For PROD it's a bit more tricky. As a workaround, we suggest to create a file with "release notes" for each release describing actions which should be performed manually. Manual renames should be applied before SnowDDL "apply" command. Also, it is highly recommended to avoid `--apply-unsafe` option for PROD and review all ["unsafe"](/guides/other-guides/safe-unsafe) changes manually. It will help to prevent potential loss of data even if release engineer forgets to apply renaming prior to SnowDDL launch. ## Lower case identifiers Lower case identifiers cause a lot of problems down the line and are not supported on purpose. Please check rationale on [Object Identifiers](/guides/object-identifiers) documentation page. ## Tags Object tagging is currently not supported due to latency of up to 2h on [`TAG_REFERENCES`](https://docs.snowflake.com/en/sql-reference/account-usage/tag_references.html) view and [`TAG_REFERENCES_WITH_LINEAGE`](https://docs.snowflake.com/en/sql-reference/functions/tag_references_with_lineage.html) function. There is no way to find all objects referenced by specific tag reliably. As soon as Snowflake improves this situation, the SnowDDL will support tags. ## Masking policy, row access policy Snowflake has a fundamental limitation related to policies. In certain cases policy has to be re-created entirely from scratch. When this happens, old policy has to be detached from all associated objects first, and new policy has to be reattached to all objects afterwards. It opens the opportunity for potential race condition, when users are able to access objects "unprotected" by policy. In order to mitigate this problem, it is advised to have a small "maintenance" window when business users cannot login and access protected objects at all. And you should apply changes to existing policies only during this window. Alternatively, basic secure `VIEWS` with checks on `CURRENT_ROLE()` might be a better option to achieve the same result. ## Snowpark & UDF (latest features) Snowpark and UDF functions are currently in a very active development by Snowflake. There are many "preview" and "undocumented" features. If you notice a UDF feature which is currently missing, please [raise a ticket](https://github.com/littleK0i/SnowDDL/issues) on GitHub, and I'll add it in a few days. ## File format It is possible to set file format options for [EXTERNAL TABLES](/basic/yaml-configs/external-table), [PIPES](/basic/yaml-configs/pipe) and [STAGES](/basic/yaml-configs/stage) using name references to [FILE FORMAT](/basic/yaml-configs/file-format) objects. Currently it is not possible to use inline format options. It is an intentional design decision. The reasons are following: * it helps to improve clarity by storing format options in one object type only; * it helps to reduce duplication of format options in config; * it helps to reduce complexity of resolvers, especially when it comes to edge cases; * it makes it easier to get rid of [short hash](/guides/other-guides/short-hash) eventually; Unfortunately, it may force you to create a few named [FILE FORMAT](/basic/yaml-configs/file-format) objects when you would normally have none. But, in my opinion, it is a small price for all the benefits provided by this approach. # Fivetran Since SnowDDL v0.27, it is possible to natively configure database, user and permissions for Fivetran. ## How to configure Fivetran with SnowDDL? #### 1) Create custom permission model Configuration guide for [PERMISSION MODEL](/basic/yaml-configs/permission-model) ```yaml fivetran: ruleset: DATABASE_OWNER owner_create_grants: - STAGE - TABLE - VIEW owner_future_grants: STAGE: [OWNERSHIP] TABLE: [OWNERSHIP] VIEW: [OWNERSHIP] read_future_grants: STAGE: [READ] TABLE: [SELECT, REFERENCES] VIEW: [SELECT, REFERENCES] ``` #### 2) Create database Configuration guide for [DATABASE](/basic/yaml-configs/database) ``` is_sandbox: true permission_model: fivetran ``` #### 3) Create business role Configuration guide for [BUSINESS ROLE](/basic/yaml-configs/business-role) ``` fivetran_owner: database_owner: - fivetran_db ``` #### 4) Create user ``` ext_fivetran: rsa_public_key: ... business_roles: - fivetran_owner ``` ## How to grant access to objects created by Fivetran? You may grant read access on all objects inside Fivetran database by using `database_read` parameter for [BUSINESS ROLE](/basic/yaml-configs/business-role). For example: ``` fivetran_reader: database_read: - fivetran_db ``` If you want to provide granular on per-schema basis, it requires a bit more work. 1. Explicitly configure schema(s) in Fivetran database by creating corresponding sub-directories with `params.yaml` files inside. 2. Use parameter `schema_read` for [BUSINESS ROLE](/basic/yaml-configs/business-role). For example: ``` fivetran_specific_reader: schema_read: - fivetran_db.braintree - fivetran_db.paypal ``` SnowDDL can only create "schema roles" for schemas which are explicitly defined in config. # Airbyte Since SnowDDL v0.27, it is possible to natively configure database, user and permissions for Airbyte. ## How to configure Airbyte with SnowDDL? #### 1) Create custom permission model Configuration guide for [PERMISSION MODEL](/basic/yaml-configs/permission-model) ```yaml airbyte: ruleset: DATABASE_OWNER owner_create_grants: - STAGE - TABLE - VIEW owner_future_grants: STAGE: [OWNERSHIP] TABLE: [OWNERSHIP] VIEW: [OWNERSHIP] read_future_grants: STAGE: [READ] TABLE: [SELECT, REFERENCES] VIEW: [SELECT, REFERENCES] ``` #### 2) Create database Configuration guide for [DATABASE](/basic/yaml-configs/database) ``` is_sandbox: true permission_model: airbyte quoted_identifiers_ignore_case: true ``` {% hint style="info" %} It is recommended to enable `quoted_identifiers_ignore_case` parameter to prevent issues with Airbyte using lower-case characters for internal stage names. {% endhint %} #### 3) Create business role Configuration guide for [BUSINESS ROLE](/basic/yaml-configs/business-role) ``` airbyte_owner: database_owner: - airbyte_db ``` #### 4) Create user ``` ext_airbyte: rsa_public_key: ... business_roles: - airbyte_owner ``` #### 5) Finish configuration in Airbyte GUI It is highly recommended to change name of schema `airbyte_internal` to UPPER-case, e.g. `AIRBYTE_INTERNAL`. Lower-cased identifiers are not supported by SnowDDL, so you won't be able to set granular access on schema with lower-cased name. ## How to grant access to objects created by Airbyte? You may grant read access on all objects inside Airbyte database by using `database_read` parameter for [BUSINESS ROLE](/basic/yaml-configs/business-role). For example: ``` airbyte_reader: database_read: - airbyte_db ``` If you want to provide granular on per-schema basis, it requires a bit more work. 1. Explicitly configure schema(s) in Airbyte database by creating corresponding sub-directories with `params.yaml` files inside. 2. Use parameter `schema_read` for [BUSINESS ROLE](/basic/yaml-configs/business-role). For example: ``` airbyte_specific_reader: schema_read: - airbyte_db.braintree - airbyte_db.paypal ``` SnowDDL can only create "schema roles" for schemas which are explicitly defined in config. # Encrypt user passwords SnowDDL provides functionality to encrypt user passwords, secrets and other sensitive information stored in YAML config. SnowDDL uses [Fernet](https://cryptography.io/en/latest/fernet/) symmetric encryption. ### 1) How to generate a key? Run command in terminal: ```bash snowddl-fernet generate-key --export ``` You'll get output similar to this: ``` export SNOWFLAKE_CONFIG_FERNET_KEYS=2jVoIQgHAbtVkj04J-NZp-x69DLaXyd7KKg4pfEn6qA= ``` This is your encryption key with added "export" command for convenience. Make sure to store this encryption key securely. Run this command in order to set environment variable `SNOWFLAKE_CONFIG_FERNET_KEYS`, which is uses by other `snowddl-fernet` and `snowddl` commands. Also, make sure to add this environment variable to CI/CD pipelines running `snowddl`. ### 2) How to encrypt a value? Run command in terminal: ```bash snowddl-fernet encrypt "my_secret_value" ``` You'll get output similar to this: ``` gAAAAABmlTl8AHfqJXFfDI4jqOGiaBLZ2dDMbMCqkNyOH_EzcRYGmFxSr_fvx8mgGBWD7sOYYIFCp5AqvG8k5kGM5R5ssYZgwA== ``` This is a value encrypted by key generated earlier. ### 3) How to use encrypted value in SnowDDL config? Add encrypted value to SnowDDL config using [custom YAML tag](/basic/yaml-tag-decrypt) `!decrypt`: ```yaml john_doe: first_name: John last_name: Doe password: !decrypt gAAAAABmlTl8AHfqJXFfDI4jqOGiaBLZ2dDMbMCqkNyOH_EzcRYGmFxSr_fvx8mgGBWD7sOYYIFCp5AqvG8k5kGM5R5ssYZgwA ``` You may use `!decrypt` tag with any string config parameters. As long as valid Fernet key is present in `SNOWFLAKE_CONFIG_FERNET_KEYS` environment variable, SnowDDL will automatically decrypt values with `!decrypt` tag. ### 4) How to rotate encryption key? Normally only account administrators should know the encryption key(s). If one of administrators leaves the company, keys should be rotated and values should be encrypted again in order to prevent this administrator from being able to decrypt future passwords. In order to perform key rotation, please do the following steps: 1. Generate new key using command: `snowddl-fernet generate-key --export --prepend`.\ \ Option `--prepend` means that newly generated key will be added at the beginning of key sequence stored in`SNOWFLAKE_CONFIG_FERNET_KEYS`.
2. Output of previous command will look like this:\ `export SNOWFLAKE_CONFIG_FERNET_KEYS=,`\ \ Run it in terminal to update environment variable.
3. Run command to rotate keys: `snowddl-fernet config-rotate -c `.\ \ Encrypted values in YAML files will be decrypted by old key and encrypted once again with newly generated key.\ \ All values with `!decrypt` tag should be changed. Review & commit these changes to Git repository.
4. Update config of CI/CD pipelines with `SNOWFLAKE_CONFIG_FERNET_KEYS=` ### Full command reference * `snowddl-fernet generate-key` - generate new encryption key * `snowddl-fernet encrypt ` - encrypt string value with first key * `snowddl-fernet decrypt ` - decrypt string value with any key * `snowddl-fernet rotate ` - decrypt string value with any key and encrypt it again with first key * `snowddl-fernet config-encrypt -c ` - encrypt and replace all values starting with YAML custom tag `!encrypt` in config * `snowddl-fernet config-decrypt -c ` - decrypt and replace all values starting with YAML custom tag `!decrypt` in config * `snowddl-fernet config-rotate -c ` - rotate and replace all values starting with YAML custom tag `!decrypt` in config ### Usage notes * Encryption keys for `snowddl` command can ONLY be specified with environment variable `SNOWFLAKE_CONFIG_FERNET_KEYS`. * Replacing values in YAML document is difficult if we want to preserve original formatting. The current approach relies on regular expressions looking for `!encrypt` and `!decrypt` tags. Only normal single-line scalar values are supported. No literal block scalars, no folded scalars. # Iceberg Tables At this moment SnowDDL supports UNMANAGED Iceberg tables using external catalog only. If you are looking for MANAGED Iceberg tables support, please leave a comment in this thread describing the use case: ## How to create unmanaged Iceberg table with SnowDDL ### Step 1: Create permission model which includes permissions for Iceberg tables Example of custom [permission model](/basic/yaml-configs/permission-model): ```yaml iceberg: inherit_from: default owner_create_grants: - ICEBERG_TABLE owner_future_grants: ICEBERG_TABLE: [OWNERSHIP] write_future_grants: ICEBERG_TABLE: [INSERT, UPDATE, DELETE, TRUNCATE] read_future_grants: ICEBERG_TABLE: [SELECT, REFERENCES] ``` Iceberg tables are not included in `default` permission model, since adding it to all schemas seems to introduce noticeable additional overhead during schema role creation. ### Step 2: Create external volume Similar to [integration](/guides/other-guides/integrations) objects, EXTERNAL VOLUME should be created by `ACCOUNTADMIN`. Documentation: Example: ```sql SET STORAGE_BASE_URL = 's3://my-bucket/iceberg_glue/'; SET STORAGE_ROLE_ARN = 'arn:aws:iam::123:role/snowflake_role'; SET STORAGE_EXTERNAL_ID = '...'; CREATE OR REPLACE EXTERNAL VOLUME TEST_EXTERNAL_VOLUME_GLUE STORAGE_LOCATIONS = ( ( NAME = 'iceberg_glue' STORAGE_PROVIDER = 'S3' STORAGE_BASE_URL = $STORAGE_BASE_URL STORAGE_AWS_ROLE_ARN = $STORAGE_ROLE_ARN STORAGE_AWS_EXTERNAL_ID = $STORAGE_EXTERNAL_ID ) ) ALLOW_WRITES = FALSE; ``` ### Step 3: Create catalog Similar to [integration](/guides/other-guides/integrations) objects, CATALOG should be created by `ACCOUNTADMIN`. Documentation: Example: ```sql SET GLUE_CATALOG_NAMESPACE = 'iceberg_glue' SET GLUE_ROLE_ARN = 'arn:aws:iam::123:role/snowflake_glue'; SET GLUE_CATALOG_ID = '123'; SET GLUE_REGION = 'us-east-1'; CREATE OR REPLACE CATALOG INTEGRATION TEST_CATALOG_GLUE CATALOG_SOURCE = GLUE CATALOG_NAMESPACE = $GLUE_CATALOG_NAMESPACE TABLE_FORMAT = ICEBERG GLUE_AWS_ROLE_ARN = $GLUE_ROLE_ARN GLUE_CATALOG_ID = $GLUE_CATALOG_ID GLUE_REGION = $GLUE_REGION ENABLED = TRUE; ``` ### Step 4: Create schema with references to EXTERNAL VOLUME and CATALOG Add parameters to config of [schema](/basic/yaml-configs/schema) which is supposed to contain Iceberg tables: ```yaml permission_model: iceberg external_volume: text_external_volume_iceberg catalog: test_catalog_glue ``` At this moment one schema may contain tables from one EXTERNAL VOLUME and one CATALOG only. If you have more external volumes and catalogs, please create more schemas. ### Step 5: Create Iceberg tables Create [configs for individual Iceberg tables](/basic/yaml-configs/iceberg-table). Example 1: ```yaml catalog_table_name: test_iceberg_table_1 ``` Example 2: ```yaml metadata_file_path: test_iceberg_table_1/metadata/00001-cc112050-1448-4c2a-9e03-504e7f5fc62a.metadata.json replace_invalid_characters: true ``` ## Rationale: Why is it not possible to specify EXTERNAL VOLUME and CATALOG individually for each table? SnowDDL has built-in [role hierarchy](/guides/role-hierarchy). It includes special "owner" roles which are created automatically for each schema. Objects in schema are supposed to be "owned" by schema owner role. By specifying EXTERNAL VOLUME and CATALOG on schema level, we achieve two goals: 1. Usage on EXTERNAL VOLUME and CATALOG are granted to schema owner role automatically. 2. Each schema contains objects from one EXTERNAL VOLUME and one CATALOG, which helps to keep things clear and prevents mixture of various Iceberg table sub-types within one schema. # CLI interface The easiest way to use SnowDDL is to create and apply YAML configs with CLI interface. SnowDDL registers `snowddl` CLI entry-point using [setuptools](https://setuptools.pypa.io/en/latest/userguide/entry_point.html). It should be available in your terminal immediately after installation. ## Quick help ``` usage: snowddl [-h] [-c CONFIG_PATH] [-a ACCOUNT] [-u USER] [-p PASSWORD] [-k PRIVATE_KEY] [-r ROLE] [-w WAREHOUSE] [--authenticator AUTHENTICATOR] [--passphrase PASSPHRASE] [--env-prefix ENV_PREFIX] [--env-prefix-separator {__,_,$}] [--env-admin-role ENV_ADMIN_ROLE] [--max-workers MAX_WORKERS] [--query-tag QUERY_TAG] [--log-level LOG_LEVEL] [--show-sql] [--show-timers] [--show-unused-files] [--placeholder-path] [--placeholder-values] [--exclude-object-types] [--include-object-types] [--apply-unsafe] [--apply-replace-table] [--apply-all-policy] [--apply-account-level-policy] [--apply-aggregation-policy] [--apply-authentication-policy] [--apply-masking-policy] [--apply-projection-policy] [--apply-row-access-policy] [--apply-account-params] [--apply-network-policy] [--apply-resource-monitor] [--apply-outbound-share] [--refresh-user-passwords] [--refresh-future-grants] [--refresh-stage-encryption] [--refresh-secrets] [--clone-table] [--destroy-without-prefix] {plan,apply,destroy,validate} ... Object management automation tool for Snowflake positional arguments: {plan,apply,destroy,validate} plan Resolve objects, apply nothing, display suggested changes apply Resolve objects, apply safe changes, display suggested unsafe changes destroy Drop objects with specified --env-prefix, use it to reset dev and test environments validate Validate config only, do not connect to Snowflake options: -h, --help show this help message and exit -c CONFIG_PATH Path to config directory OR name of bundled test config (default: current directory) -a ACCOUNT Snowflake account identifier (default: SNOWFLAKE_ACCOUNT env variable) -u USER Snowflake user name (default: SNOWFLAKE_USER env variable) -p PASSWORD Snowflake user password (default: SNOWFLAKE_PASSWORD env variable) -k PRIVATE_KEY Path to private key file (default: SNOWFLAKE_PRIVATE_KEY_PATH env variable) -r ROLE Snowflake active role (default: SNOWFLAKE_ROLE env variable) -w WAREHOUSE Snowflake active warehouse (default: SNOWFLAKE_WAREHOUSE env variable) --authenticator AUTHENTICATOR Authenticator: snowflake, externalbrowser, oauth, oauth_snowpark, workload_identity (default: SNOWFLAKE_AUTHENTICATOR env variable or 'snowflake') --oauth-token OAUTH_TOKEN Oauth access token (default: SNOWFLAKE_OAUTH_TOKEN env variable) --workload-identity-token Workload identity token (default: SNOWFLAKE_WORKLOAD_IDENTITY_TOKEN env variable) --workload-identity-provider Workload identity provider (default: SNOWFLAKE_WORKLOAD_IDENTITY_PROVIDER env variable) --passphrase PASSPHRASE Passphrase for private key file (default: SNOWFLAKE_PRIVATE_KEY_PASSPHRASE env variable) --env-prefix ENV_PREFIX Env prefix added to global object names, used to separate environments (e.g. DEV, PROD) --env-prefix-separator {__,_,$} Custom separator for Env prefix (supported values are: '__', '_', '$') --env-admin-role ENV_ADMIN_ROLE Super administration role which should inherit env prefixed SnowDDL role --max-workers MAX_WORKERS Maximum number of workers to resolve objects in parallel --query-tag QUERY_TAG Add QUERY_TAG to all queries produced by SnowDDL --log-level LOG_LEVEL Log level (possible values: DEBUG, INFO, WARNING; default: INFO) --show-sql Show executed DDL queries --show-timers Show debug timers --show-unused-files Show warnings for unused config files --placeholder-path Path to config file with environment-specific placeholders --placeholder-values Environment-specific placeholder values in JSON format --exclude-object-types Comma-separated list of object types NOT to resolve --include-object-types Comma-separated list of object types TO resolve, all other types are excluded --apply-unsafe Additionally apply unsafe changes, which may cause loss of data (ALTER, DROP, etc.) --apply-replace-table Additionally apply REPLACE TABLE when ALTER TABLE is not possible --apply-all-policy Additionally apply changes for all types of policies --apply-account-level-policy Additionally apply changes for ACCOUNT-level policies --apply-aggregation-policy Additionally apply changes to AGGREGATION POLICIES --apply-authentication-policy Additionally apply changes to AUTHENTICATION POLICIES --apply-masking-policy Additionally apply changes to MASKING POLICIES --apply-projection-policy Additionally apply changes to PROJECTION POLICIES --apply-row-access-policy Additionally apply changes to ROW ACCESS POLICIES --apply-account-params Additionally apply changes to ACCOUNT PARAMETERS --apply-network-policy Additionally apply changes to NETWORK POLICIES --apply-resource-monitor Additionally apply changes to RESOURCE MONITORS --apply-outbound-share Additionally apply changes to OUTBOUND SHARES --refresh-user-passwords Additionally refresh passwords of users --refresh-workload-identity Additionally refresh workload identites of users --refresh-future-grants Additionally refresh missing grants for existing objects derived from future grants --refresh-stage-encryption Additionally refresh stage encryption parameters for existing external stages --refresh-secrets Additionally refresh secrets --clone-table Clone all tables from source databases to destination databases (with env_prefix) --clone-source-env-prefix Clone from another environment with different env_prefix --destroy-without-prefix Allow {destroy} action without --env-prefix ``` ### Usage notes * Action argument is mandatory: * Use **plan** action to preview changes. * Use **apply** action to apply OR suggest changes, depending on other settings. * Use **destroy** action to drop all objects created by SnowDDL previously. * Use **validate** action to validate the declared config only without connecting to Snowflake. * SnowDDL provides [a few bundled configs](https://github.com/littleK0i/snowddl/tree/master/snowddl/_config/) for testing and demonstration purposes. You may use such config by passing its name to **-c** argument. * [Account identifier](https://docs.snowflake.com/en/user-guide/admin-account-identifier.html) should be passed to **-a** argument without `.snowflakecomputing.com`. * You may use password OR private key for authentication. Private key is recommended for production environment. * Object types for `--include` and `--exclude` arguments can be found [here](/basic/yaml-configs). * Suggested & optionally executed DDL queries are outputted to `STDOUT`. Logs are outputted to `STDERR`. # YAML configs SnowDDL config is a directory with YAML files describing desired state of objects in Snowflake. You may find a [sample config](https://github.com/littleK0i/SnowDDL/tree/master/snowddl/_config/sample01_01) in SnowDDL GitHub repository. Fundamentally, there are two types of objects in Snowflake: ### Account-level objects DATABASE, WAREHOUSE, ROLE, etc. Account-level objects are normally described by single YAML file located in the root level of config directory. For example, all warehouses are described in `/warehouse.yaml`. ### Schema-level objects TABLE, VIEW, FUNCTION, etc. Schema-level objects are normally described by one YAML file per object located in sub-directories representing database and schema where object is located, as well as an object type. For example: `/test_db/test_schema/table/test_table.yaml`. This object: * belongs to database `TEST_DB` * belongs to schema `TEST_DB.TEST_SCHEMA` * it is a `TABLE` * its name is `TEST_TABLE` Some schema-level objects support overloading (FUNCTION, PROCEDURE). It means that you may have multiple objects of this type in the same schema and with the same name, but with different arguments. In this case you must include base data types of arguments in the name of YAML config file. For example: `/test_db/test_schema/function/my_function(number,varchar).yaml`. ## Documentation structure The following pages describe configuration format for each object type. Every page starts with **config path**, followed by some **examples**, followed by detailed **schema description**, followed by **usage notes** and **links**. Links always contain URL's to relevant Snowflake commands and to the code of specific YAML parser with JSON schema. You should be able to check all technical details using provided links. Small number of properties are highlighted with red. Such properties are "required". ## Object identifiers in YAML configs When you see data type `(ident)` in schema description, it means this is the name of another object. For example, when one table refers to another using FOREIGN KEY. For account-level objects, column names, parameter names - use name "as is". For schema-level objects you may use fully-qualified names (`..`), but you may also use a short name (``). It means that ref object is located in the same schema as current object. This is very handy when you need to move some objects from one schema to another. There is no need to maintain fully qualified references all the time. ## YAML format and data types YAML format is a bit complicated. You may find some good external tutorials: * * YAML data types are important for SnowDDL parsers, especially for account, session, copy parameters. ```yaml # Correct use_cached_results: true # this is bool unsupported_ddl_action: fail # this is string week_start: 1 # this is int # Incorrect use_cached_results: "true" # this is now string unsupported_ddl_action: false # this is now bool week_start: "1" # this is now string ``` Incorrect data type will cause DDL query to fail. SnowDDL does not store "correct" data types for each parameter to make sure it will be compatible with any future changes. ## Config files extensions Since version 0.36.0 supports both YAML file extensions: `.yml` and `.yaml`. If SnowDDL encounters two files with the same names, but different extensions, validation error will be raised. # ACCOUNT PARAMETER Config path: `/account_params.yaml` Example: ```yaml ABORT_DETACHED_QUERY: true CLIENT_TIMESTAMP_TYPE_MAPPING: "TIMESTAMP_NTZ" ENABLE_UNREDACTED_QUERY_SYNTAX_ERROR: true ERROR_ON_NONDETERMINISTIC_MERGE: true ERROR_ON_NONDETERMINISTIC_UPDATE: true JDBC_TREAT_TIMESTAMP_NTZ_AS_UTC: true LOCK_TIMEOUT: 3600 PREVENT_UNLOAD_TO_INLINE_URL: true REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_CREATION: true REQUIRE_STORAGE_INTEGRATION_FOR_STAGE_OPERATION: true STATEMENT_QUEUED_TIMEOUT_IN_SECONDS: 10800 STATEMENT_TIMEOUT_IN_SECONDS: 10800 TIMESTAMP_NTZ_OUTPUT_FORMAT: "YYYY-MM-DD HH24:MI:SS" TIMESTAMP_OUTPUT_FORMAT: "YYYY-MM-DD HH24:MI:SS TZHTZM" TIMESTAMP_TYPE_MAPPING: "TIMESTAMP_NTZ" TIMEZONE: "Etc/UTC" TRANSACTION_ABORT_ON_ERROR: true UNSUPPORTED_DDL_ACTION: "FAIL" WEEK_START: "1" ALLOW_CLIENT_MFA_CACHING: true ALLOW_ID_TOKEN: true ``` ## Schema * *{key}* (ident) - parameter name * *{value}* (bool, float, int, str) - parameter value ## Usage notes 1. Data type of parameter value is important! Correct data type for each parameter can be obtained from output of `SHOW PARAMETERS IN ACCOUNT`. ## Links * [Parameters](https://docs.snowflake.com/en/sql-reference/parameters.html) * [ALTER ACCOUNT SET](https://docs.snowflake.com/en/sql-reference/sql/alter-account.html) * [SHOW PARAMETERS IN ACCOUNT](https://docs.snowflake.com/en/sql-reference/sql/show-parameters.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/snowddl/blob/master/snowddl/parser/account_params.py) # ACCOUNT POLICY Config path: `/account_policy.yaml` Example: ```yaml authentication_policy: my_db.my_schema.my_auth_policy network_policy: my_network_policy ``` ## Schema * **authentication\_policy** (ident) - assign [AUTHENTICATION POLICY](/basic/yaml-configs/authentication-policy) to ACCOUNT * **network\_policy** (ident) - assign [NETWORK POLICY](/basic/yaml-configs/network-policy) to ACCOUNT ## Usage notes 1. It is highly recommended to apply ACCOUNT-level policies manually. SnowDDL will only suggest SQL by default. If you want to apply ACCOUNT-level policies automatically, please use `--apply-account-policy` CLI option. Additional privileges for SnowDDL admin user might be required. 2. ACCOUNT-level policies are ignored when SnowDDL runs with [env\_prefix](/guides/other-guides/env-prefix). ## Links * [ALTER ACCOUNT SET](https://docs.snowflake.com/en/sql-reference/sql/alter-account.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/account_policy.py) # AGGREGATION POLICY Config path: `///aggregation_policy/.yaml` Example: ```yaml body: |- CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN NO_AGGREGATION_CONSTRAINT() ELSE AGGREGATION_CONSTRAINT(MIN_ROW_COUNT => 5, MIN_ENTITY_COUNT => 2) END references: - object_type: TABLE object_name: test_table_1 columns: [id] - object_type: VIEW object_name: test_view_1 columns: [id] comment: my aggregation policy ``` ## Schema * **body** (str) - policy SQL expression * ~~**references**~~ (dict) * **object\_type** (str) - reference object type (e.g. `TABLE`, `VIEW`) * **object\_name** (ident) - reference object name * **columns** (list) * *{items}* (ident) - optional reference column names defining "entity" * **comment** (str) ## Usage notes 1. Management of aggregation policies requires active warehouse due to unavoidable [POLICY\_REFERENCES](https://docs.snowflake.com/en/sql-reference/functions/policy_references.html) table function calls. 2. Make sure to allow aggregation for role `SYSADMIN`, especially if aggregation policy is being applied to views. Otherwise SnowDDL will have to re-create VIEW on every run due to inability to verify column data types.\ \ Example of check: `CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN NO_AGGREGATION_CONSTRAINT() ELSE ... END` 3. Parameter `references` is deprecated since `0.33.0`. Use policy reference parameters directly in [TABLE](/basic/yaml-configs/table) or [VIEW](/basic/yaml-configs/view) configs instead. ## Links * [CREATE AGGREGATION POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-aggregation-policy) * [SHOW AGGREGATION POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-aggregation-policies) * [DESC AGGREGATION POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-aggregation-policy) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/aggregation_policy.py) # ALERT Config path: `///alert/.yaml` Example: ```yaml warehouse: al001_wh1 schedule: 1 minute condition: |- SELECT gauge_value FROM ${{ env_prefix }}db1.sc1.gauge WHERE gauge_value>200 action: |- INSERT INTO ${{ env_prefix }}db1.sc1.gauge_value_exceeded_history VALUES (current_timestamp()) ``` ## Schema * **warehouse** (ident) - warehouse used to executed alert * **schedule** (str) - schedule for periodically evaluating the condition for the alert * **condition** (str) - SQL statement that represents the condition for the alert * **action** (str) - SQL statement that should be executed if the condition returns one or more rows * **comment** (str) ## Usage notes 1. SnowDDL only creates alerts. Alerts are initially suspended. You should execute `ALTER ALERT ... RESUME` via different means to enable alert execution. 2. Schema objects should be referred using fully-qualified identifiers, with `${{ env_prefix }}` placeholder, database name, schema name and object name. It is currently required due to limitations of `ALTER ALERT` command missing scope during validation of SQL statements. 3. Alerts can only be created via SnowDDL config. Users with `OWNER` privilege on specific schemas cannot create custom alerts in such schemas. 4. Alerts are executed with full privileges of [SnowDDL Administrator User](/guides/other-guides/admin) role. ## Additional privileges In order for `ALERT` objects to operate properly, the following additional grants should be added to OWNER role in [schema config](/basic/yaml-configs/schema): * `owner_warehouse_usage` - list warehouses used to execute alerts * `owner_integration_usage` - if you send alert notifications, add name of notification integration here * `owner_account_grants` - Snowflake requires `EXECUTE ALERT` or `EXECUTE MANAGED ALERT` privilege to run alerts ## Links * [CREATE ALERT](https://docs.snowflake.com/en/sql-reference/sql/create-alert.html) * [ALTER ALERT](https://docs.snowflake.com/en/sql-reference/sql/alter-alert.html) * [SHOW ALERTS](https://docs.snowflake.com/en/sql-reference/sql/show-alerts.html) * [Setting up Alerts](https://docs.snowflake.com/en/user-guide/alerts.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/alert.py) # AUTHENTICATION POLICY Config path: `///authentication_policy/.yaml` Example: ```yaml authentication_methods: [SAML, KEYPAIR] mfa_authentication_methods: [SAML] mfa_enrollment: REQUIRED client_types: [SNOWFLAKE_UI, DRIVERS] security_integrations: [ALL] comment: "my custom policy" ``` ## Schema * **authentication\_methods** (list) * *{items}* (str) * **mfa\_authentication\_methods** (list) * *{items}* (str) * **mfa\_enrollment** (str) * **mfa\_policy** (dict) * *{key}* (str) - parameter name * *{value}* (array, bool, float, int, str) - parameter value * **client\_types** (list) * *{items}* (str) * **client\_policy** (dict) * {key} (str) - client name * {value} (dict) * {key} (str) - parameter name * *{value}* (array, bool, float, int, str) - parameter value * **security\_integrations** (list) * *{items}* (str) * **pat\_policy** (dict) * *{key}* (str) - parameter name * *{value}* (array, bool, float, int, str) - parameter value * **workload\_identity\_policy** (dict) * *{key}* (str) - parameter name * *{value}* (array, bool, float, int, str) - parameter value * **comment** (str) ## Usage notes 1. Snowflake makes dramatic and frequent changes to `AUTHENTICATION_POLICY` object type. Backwards compatibility is not guaranteed. The current implementation should work with Snowflake changes bundle up to `2025_06`. If you encounter issues with future bundles, please raise an issue on GitHub. ## Links * [CREATE AUTHENTICATION POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-authentication-policy) * [ALTER AUTHENTICATION POLICY](https://docs.snowflake.com/en/sql-reference/sql/alter-authentication-policy) * [DESC AUTHENTICATION POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-authentication-policy) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/authentication_policy.py) # BACKUP POLICY Config path: `///backup_policy/.yaml` Example: ```yaml schedule: 90 minutes expire_after_days: 30 comment: My snapshot policy ``` ## Schema * **schedule** (str) - schedule for creating backups of an object * **expire\_after\_days** (int) - number of days until a backup expires * **comment** (str) ## Links * [CREATE BACKUP POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-backup-policy) * [ALTER BACKUP POLICY](https://docs.snowflake.com/en/sql-reference/sql/alter-backup-policy) * [SHOW BACKUP POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-backup-policies) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/backup_policy.py) # BACKUP SET Config path: `///backup_set/.yaml` Example for database: ```yaml object_type: DATABASE object_name: my_database ``` Example for schema: ```yaml object_type: SCHEMA object_name: my_database.my_schema ``` Example for table: ```yaml object_type: TABLE object_name: my_table backup_policy: my_snapshot_policy ``` ## Schema * **object\_type** (str) - type of objects to create backups for: database, schema or table * **object\_name** (ident) - name of object to create backups for * **backup\_policy** (ident) - name of [backup policy](/basic/yaml-configs/backup-policy) * **comment** (str) ## Links * [CREATE BACKUP SET](https://docs.snowflake.com/en/sql-reference/sql/create-backup-set) * [ALTER BACKUP SET](https://docs.snowflake.com/en/sql-reference/sql/alter-backup-set) * [SHOW BACKUP SETS](https://docs.snowflake.com/en/sql-reference/sql/show-backup-sets) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/backup_set.py) # BUSINESS ROLE Config path: `/business_role.yaml` Example: ```yaml bookings_analyst: schema_read: - snowddl_db.bookings warehouse_usage: - bookings_analyst_wh comment: "Business analyst working on data in Bookings schema" sakila_analyst: schema_read: - snowddl_db.sakila schema_owner: - snowddl_db.sakila_sandbox warehouse_usage: - sakila_analyst_wh comment: "Business analyst working on data in Sakila schema" ``` ## Schema * *{key} (ident)* - business role name * *{value}* (dict) * **database\_owner** (list) * *{items}* (ident) - grant OWNERSHIP privileges for objects in database * **database\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in database * **database\_read** (list) * *{items}* (ident) - grant READ privileges for objects in database * **schema\_owner** (list) * *{items}* (ident) - grant OWNERSHIP privileges for objects in schema * **schema\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in schema * **schema\_read** (list) * *{items}* (ident) - grant READ privileges for objects in schema * **share\_read** (list) * *{items}* (ident) - grant IMPORTED PRIVILEGES or DATABASE ROLE for inbound share * **warehouse\_usage** (list) * *{items}* (ident) - grant USAGE privileges for warehouses * **warehouse\_monitor** (list) * *{items}* (ident) - grant MONITOR privileges for warehouses * **application\_roles** (list) * *{items}* (ident) - grant APPLICATION ROLES * **technical\_roles** (list) * *{items}* (ident) - grant TECHNICAL ROLES * **global\_roles** (list) * *{items}* (ident) - grant external roles with custom permissions created outside of SnowDDL (e.g. `FIVETRAN_ROLE`) * **comment** (str) ## Usage notes 1. Schema names should be fully qualified (`.`). 2. It is possible to specify database and schema names as wildcards (e.g. `.*`). It is helpful for large number of objects with similar names sharing similar access patterns. 3. Global roles are managed outside of SnowDDL and are applied without env prefix. 4. You may grant database roles of inbound shares using **share\_read** parameter. For example: `SNOWFLAKE.OBJECT_VIEWER`. ## Links * [CREATE ROLE](https://docs.snowflake.com/en/sql-reference/sql/create-role.html) * [GRANT PRIVILEGE](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/business_role.py) # DATABASE Config path: `//params.yaml` Example: ```yaml is_transient: true retention_time: 60 comment: "Test database" ``` ## Schema * **is\_transient** (bool) - make database TRANSIENT * **retention\_time** (int) - data retention time in days * **is\_sandbox** (bool) - custom schemas and schema objects created in sandbox database will not be dropped if not present in config * **permission\_model** (str) **-** name of custom [permission model](/basic/yaml-configs/permission-model) * **external\_volume** (ident) - name of EXTERNAL VOLUME used for [Iceberg tables](/guides/other-guides/iceberg-tables) * **catalog** (ident) - name of CATALOG used for [Iceberg tables](/guides/other-guides/iceberg-tables) * **event\_table** (ident) - name of [EVENT\_TABLE](/basic/yaml-configs/event-table) * **log\_level** (str) - logging parameter [LOG\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#label-log-level) * **log\_event\_level** (str) - logging parameter LOG\_EVENT\_LEVEL * **metric\_level** (str) - logging parameter [METRIC\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#label-metric-level) * **trace\_level** (str) - logging parameter [TRACE\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#trace-level) * **quoted\_identifiers\_ignore\_case** (bool) * **owner\_database\_read** (list) * *{items}* (ident) - grant READ privileges for objects in another database to OWNER role of this database * **owner\_database\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in another database to OWNER role of this database * **owner\_schema\_read** (list) * *{items}* (ident) - grant READ privileges for objects in another schema to OWNER role of this database * **owner\_schema\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in another schema to OWNER role of this database * **owner\_share\_read** (list) * *{items}* (ident) - grant IMPORTED PRIVILEGES or DATABASE ROLE for inbound share to OWNER role of this database * **owner\_integration\_usage** (list) * *{items}* (ident) - grant USAGE privilege on global integration to OWNER role of this database * **owner\_warehouse\_usage** (list) * *{items}* (ident) - grant USAGE privilege on warehouse to OWNER role of this database * **owner\_account\_grants** (list) * *{items}* (str) - grant account-level privilege to OWNER role of this database * **owner\_global\_roles** (list) * *{items}* (ident) - grant external roles with custom permissions created outside of SnowDDL to OWNER roles in this database * **comment** (str) ## Usage notes 1. File `params.yaml` is optional. All parameters are set to default if file is omitted. 2. Schema `PUBLIC` is dropped from newly created databases automatically. 3. Inbound `SHARES` are currently not managed by SnowDDL, but can be created manually by `ACCOUNTADMIN`. You may read more about inbound shares in [the relevant guide](/guides/other-guides/inbound-shares). 4. Use **is\_sandbox** parameter if you are planning to let external software to create `SCHEMAS` inside this database. Otherwise schemas which are not mentioned in SnowDDL config will be dropped. 5. **owner\_\*** parameters are designed to provide additional privileges which are required for some object types to operate properly. These parameters require [permission model](/basic/yaml-configs/permission-model) with `database_owner` ruleset. ## Links * [CREATE DATABASE](https://docs.snowflake.com/en/sql-reference/sql/create-database.html) * [ALTER DATABASE](https://docs.snowflake.com/en/sql-reference/sql/alter-database.html) * [SHOW DATABASES](https://docs.snowflake.com/en/sql-reference/sql/show-databases.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/database.py) # DATABASE ROLE {% hint style="warning" %} DATABASE\_ROLE is intended to be used for [outbound SHAREs](/basic/yaml-configs/share-outbound) only. It is not a part of SnowDDL [role hierarchy](/guides/role-hierarchy) and should not be used to grant access outside of shares. {% endhint %} Config path: `//database_role.yaml` Example: ``` test_database_role: grants: DATABASE:USAGE: - test_db SCHEMA:USAGE: - test_db.test_schema TABLE:SELECT: - test_db.test_schema.* FUNCTION:USAGE: - test_db.test_schema.test_secure_udf(varchar) comment: Test share role ``` ## Schema * *{key}* (ident) - database role name * *{value}* (dict) * **grants** (str) * *{key}* (str) - `:` * *{value}* (list) * *{items}* (ident) - full objects names or name patterns to grant privilege; * **comment** (str) ## Usage notes 1. Data roles are processed only if at least one database role exists in config. 2. All limitations related to [`GRANT ... TO SHARE`](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege-share.html) command applies to database role **grants**. Please read it carefully. 3. It is possible to use [Unix-style wildcard patterns](https://docs.python.org/3/library/fnmatch.html) for grant object names. 4. Grants created externally and matching Unix-style wildcard patterns **will not be dropped** if objects are not explicitly defined in config. It is an intentional workaround for lack of future grants on database roles used in shares. ## Links * [CREATE DATABASE ROLE](https://docs.snowflake.com/en/sql-reference/sql/create-database-role) * [GRANT DATABASE ROLE](https://docs.snowflake.com/en/sql-reference/sql/grant-database-role) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/database_role.py) # DYNAMIC TABLE Config path: `///dynamic_table/.yaml` Example: ```yaml text: |- SELECT timezone , count(*) AS cnt FROM ${{ env_prefix }}snowddl_db.bookings.airports_data GROUP BY 1 target_lag: 1 hour warehouse: test_wh comment: Number of airports by timezone ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - column comment * **text** (str) - SQL query text * **scheduler** (str) - ENABLE or DISABLE Snowflake scheduler (default: ENABLE) * **target\_lag** (str) - "X seconds / minutes / hours / days" or "downstream" * **warehouse** (ident) - warehouse used to refresh dynamic table * **refresh\_mode** (str) - AUTO / FULL / INCREMENTAL * **initialize** (str) - ON\_CREATE / ON\_SCHEDULE * **cluster\_by** (list) * *{items}* (str) - SQL expressions for CLUSTER BY * **is\_transient** (bool) - make table TRANSIENT * **retention\_time** (int) - data retention time in days * **depends\_on** (list) * *{items}* (ident) - names of other dynamic tables which this dynamic table depends on * **comment** (str) ## Policy reference parameters * **aggregation\_policy** (dict) * **policy\_name** (ident) - name of [AGGREGATION POLICY](/basic/yaml-configs/aggregation-policy) * **columns** (list) * *{items}* (ident) - optional reference column names defining "entity" * **join\_policy** (dict) * **policy\_name** (ident) - name of [JOIN POLICY](/basic/yaml-configs/join-policy) * **columns** (list) * *{items}* (ident) - optional allowed join keys * **masking\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [MASKING POLICY](/basic/yaml-configs/masking-policy) * **columns** (list) * *{items}* (ident) - reference column names * **projection\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [PROJECTION POLICY](/basic/yaml-configs/projection-policy) * **column** (ident) - reference column name * **row\_access\_policy** (dict) * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy) * **columns** (list) * *{items}* (ident) - reference column names ## Usage notes 1. Only normal tables and event tables can be used in SQL query text. Views are not supported due to SnowDDL [object type execution order](/guides/object-types). 2. All tables referred by SQL query text should have change tracking enabled. 3. Schema objects should be referred using fully-qualified identifiers, with `${{ env_prefix }}` placeholder, database name, schema name and object name. It is currently required due to lack of scope during validation of SQL statement. 4. You may use [custom YAML tag](/basic/yaml-tag-include) `!include` to store SQL text in a separate file instead of storing it inside YAML. ## Additional privileges Dynamic tables are executed with "schema owner role" privileges. If you want to access objects in other schemas or use a warehouse, make sure to specify additional owner grant parameters in [SCHEMA](/basic/yaml-configs/schema) config. For example: * `owner_schema_read` - to read objects in other schemas; * `owner_warehouse_usage` - to use a warehouse; * `owner_integration` - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION; ## Links * [CREATE DYNAMIC TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-dynamic-table) * [SHOW DYNAMIC TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-dynamic-tables) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/dynamic_table.py) # EVENT TABLE Config path: `///event_table/.yaml` Example: ```yaml change_tracking: true comment: Event table tracking UDF execution logs ``` ## Schema * **change\_tracking** (bool) - enable CHANGE TRACKING * **comment** (str) ## Usage notes 1. **retention\_time, cluster\_by, search\_optimization** parameters are currently not supported due to unclear value specifically for event tables with logs. These parameters can be supported in future after event tables reach "general availability". 2. You may attach event table to [DATABASE](/basic/yaml-configs/database) using parameter `event_table`. As of March 2026, this operation requires ACCOUNTADMIN privileges. ## Links * [CREATE EVENT TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-event-table) * [ALTER TABLE (event table)](https://docs.snowflake.com/en/sql-reference/sql/alter-table-event-table) * [SHOW EVENT TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-event-tables) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/table.py) # EXTERNAL ACCESS INTEGRATION Config path: `/external_access_integration.yaml` Example: ```yaml test_access_integration: allowed_network_rules: - db1.sc1.network_rule_1 - db1.sc1.network_rule_2 allowed_api_authentication_integrations: - TEST_API_SECURITY_INTEGRATION allowed_authentication_secrets: - db1.sc1.secret_1 - db1.sc1.secret_2 ``` ## Schema * *{key}* (ident) - name of external access integration * *{value}* (dict) * **allowed\_network\_rules** (list) * *{items}* (ident) - fully-qualified name of network rule * **allowed\_api\_authentication\_integrations** (list) * *{items}* (ident) - name of Snowflake [security integration](https://docs.snowflake.com/en/sql-reference/sql/create-security-integration) * **allowed\_authentication\_secrets** (list) * *{items}* (ident) - fully-qualified name of secret * **comment** (str) ## Links * [CREATE EXTERNAL ACCESS INTEGRATION](https://docs.snowflake.com/en/sql-reference/sql/create-external-access-integration) * [SHOW EXTERNAL ACCESS INTEGRATIONS](https://docs.snowflake.com/en/sql-reference/sql/show-integrations) * [DESC EXTERNAL ACCESS INTEGRATION](https://docs.snowflake.com/en/sql-reference/sql/desc-integration) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/external_access_integration.py) # EXTERNAL FUNCTION Config path: `///external_function/().yaml` Example: ```yaml arguments: string_col: VARCHAR(255) returns: VARIANT api_integration: test_api_integration url: https://xyz.execute-api.us-west-2.amazonaws.com/production/remote_echo ``` ## Schema * **arguments** (dict) * *{key}* (ident) - argument name * *{value}* (str) - argument data type * **returns** (str) - for single return value, return data type * **api\_integration** (ident) - name of API integration * **url** (str) - invocation URL of proxy service * **is\_secure** (bool) - is function SECURE * **is\_strict** (bool) - is function STRICT (always returns NULL on NULL input) * **is\_immutable** (bool) - is function IMMUTABLE (same input always produced the same output) * **headers** (dict) * *{key}* (str) - header name * *{value}* (str) - header value * **context\_headers** (list) * *{items}* (ident) - context function name * **max\_batch\_rows** (int) - maximum number of rows in each batch sent to the proxy service * **comment** (str) ## Usage notes 1. Snowflake supports [overloading](https://docs.snowflake.com/en/sql-reference/udf-overview.html#overloading-of-udf-names) of function names. Multiple functions may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names.\ \ For example: `my_function(number).yaml`, `my_function(varchar,number).yaml` 2. API integration should be [created and granted](/guides/other-guides/integrations) to SnowDDL admin role manually prior to execution. ## Additional privileges In order for `EXTERNAL FUNCTION` objects to operate properly, the following additional grants should be added to OWNER role in [schema config](/basic/yaml-configs/schema): * `owner_integration_usage` - please specify names of API INTEGRATION objects used by external functions. Functions may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever. ## Links * [CREATE EXTERNAL FUNCTION](https://docs.snowflake.com/en/sql-reference/sql/create-external-function.html) * [SHOW EXTERNAL FUNCTIONS](https://docs.snowflake.com/en/sql-reference/sql/show-external-functions.html) * [CREATE API INTEGRATION](https://docs.snowflake.com/en/sql-reference/sql/create-api-integration.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/external_function.py) # EXTERNAL TABLE Config path: `///external_table/.yaml` Example: ```yaml columns: dt: type: DATE expr: "to_date(split_part(metadata$filename, '/', 2))::date" comment: "Date of ingestion" id: type: NUMBER(38,0) NOT NULL expr: "$1:id::number(38,0)" name: type: VARCHAR(255) NOT NULL expr: "$1:name::varchar(255)" location: stage: test_external_stage file_format: test_parquet_format partition_by: [dt] ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (dict) * **type** (str) - full [data type](/guides/data-types) with optional "NOT NULL" constraint (not enforced) * **expr** (str) - SQL expression describing column * **comment** (str) * **location** (dict) * **stage** (ident) - stage name * **path** (str) - path prefix for files stage * **pattern** (str) - regular expression to filter files in stage * **file\_format** (ident) - [file format](/basic/yaml-configs/file-format) for files in stage * **partition\_by** (list) * *{items}* (ident) - column names for PARTITION BY * **partition\_type** (str) - example: `USER_DEFINED` * **auto\_refresh** (bool) - enable `AUTO_REFRESH` (default: `False`) * **refresh\_on\_create** (bool) - refresh once immediately after creation (default: `False`) * **aws\_sns\_topic** (str) - SNS topic for S3 bucket * **table\_format** (str) - example: `DELTA` * **integration** (ident) - notification [integration](/guides/other-guides/integrations) name for Azure * **comment** (str) * **primary\_key** (list) * *{items}* (ident) - column names for PRIMARY KEY constraint * **unique\_keys** (list) * *{items}* (list) * *{items}* (ident) - column names for UNIQUE KEY constraint * **foreign\_keys** (list) * *{items} (dict)* - FOREIGN KEY definitions * **columns** (list) * *{items}* (ident) - column names from current table * **ref\_table** (ident) - reference table * **ref\_columns** (list) * *{items}* (ident) - column names from reference table ## Policy reference parameters * **row\_access\_policy** (dict) * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy) * **columns** (list) * *{items}* (ident) - reference column names ## Usage notes 1. If automatic metadata refresh it not available for your cloud provider, it should be implemented separately. SnowDDL cannot refresh external table metadata for you. 2. **file\_format** can only be specified by name referencing to `FILE_FORMAT` object. ## Links * [CREATE EXTERNAL TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-external-table.html) * [SHOW EXTERNAL TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-external-tables.html) * [DESC EXTERNAL TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-external-table.html) * [Working with External Tables](https://docs.snowflake.com/en/user-guide/tables-external.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/external_table.py) # FILE FORMAT Config path: `///file_format/.yaml` Example: ```yaml type: CSV format_options: compression: GZIP record_delimiter: "\n" field_delimiter: "," skip_header: 1 trim_space: true ``` ## Schema * **type** (str) - file format type (CSV, AVRO, PARQUET, etc.) * **format\_options** (dict) * *{key}* (ident) - format option name * *{value}* (bool, float, int, list, str) - format option value * **comment** (str) ## Usage notes 1. Data type of ***{value}*** is important! Correct data type for each parameter can be obtained from output of `DESC FILE FORMAT` once you have at least one file format object. Alternatively, data type is available in `CREATE FILE FORMAT` documentation. 2. `NULL_IF` format options should be passed as list of strings. ## Links * [CREATE FILE FORMAT](https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html) * [SHOW FILE FORMATS](https://docs.snowflake.com/en/sql-reference/sql/show-file-formats.html) * [DESC FILE FORMAT](https://docs.snowflake.com/en/sql-reference/sql/desc-file-format.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/file_format.py) # FUNCTION Config path: `///function/().yaml` Example: ```yaml arguments: input_val: OBJECT returns: VARCHAR(1000) body: |- GET(input_val, COALESCE('BOOKINGS_LANG', 'en'))::varchar(1000) ``` ```yaml arguments: x: VARCHAR(10000) returns: VARCHAR(255) language: java imports: - stage: test_internal_stage path: /lib/zero-allocation-hashing-0.15.jar handler: SnowHash.xxHash body: |- import net.openhft.hashing.LongHashFunction; class SnowHash { public static LongHashFunction hash_func = LongHashFunction.xx(); public static String xxHash(String x) { return Long.toHexString(hash_func.hashChars(x)); } } ``` ```yaml language: python runtime_version: "3.8" returns: VARIANT packages: - numpy - pandas - xgboost==1.5.0 handler: udf body: |- import numpy as np import pandas as pd import xgboost as xgb def udf(): return [np.__version__, pd.__version__, xgb.__version__] ``` ## Schema * **language** (str) - language of function (default: SQL) * **runtime\_version** (str) - used to specify version of Python, Java, etc. * **arguments** (dict) * *{key}* (ident) - argument name * *{value}* (str) - argument [data type](/guides/data-types)\ \--- OR --- * *{value}* (dict) * **type** (str) - argument [data type](/guides/data-types) * **default** (str) - default SQL expression for optional argument * **returns** (str) - for single return value, return data type\ \--- OR --- * **returns** (dict) - for table return values * *{key}* (ident) - return column name * *{value}* (str) - return column data type * **body** (str) - function body * **is\_secure** (bool) - is function SECURE * **is\_aggregate** (bool) - is function AGGREGATE * **is\_strict** (bool) - is function STRICT (always returns NULL on NULL input) * **is\_immutable** (bool) - is function IMMUTABLE (same input always produced the same output) * **is\_memoizable** (bool) * **imports** (list) - files to import (usually JAR packages) * *{items}* (dict) * **stage** (ident) - name of stage * **path** (str) - path to file * **packages** (list) - Snowflake system packages to import as dependencies * *{items}* (str) - name of package, with optional version of package * **handler** (str) - name of class and method to be called * **external\_access\_integrations** (list) * *{items}* (ident) - name of [external access integration](/basic/yaml-configs/external-access-integration) * **secrets** (dict) * *{key}* (str) - secret variable name used in function code * *{value}* (ident) - name of [secret](/basic/yaml-configs/secret) object * **comment** (str) ## Usage notes 1. Snowflake supports [overloading](https://docs.snowflake.com/en/sql-reference/udf-overview.html#overloading-of-udf-names) of function names. Multiple functions may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names.\ \ For example: `my_function(number).yaml`, `my_function(varchar,number).yaml` 2. Files for `imports` should be maintained using [STAGE FILES](/basic/yaml-configs/stage-file). 3. If function `body` is empty, `handler` and `imports` with pre-compiled JAR or Python code are required. 4. `runtime_version` should be specified as string with explicit double-quotes (e.g. `"3.8"`). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects. 5. You may use [custom YAML tag](/basic/yaml-tag-include) `!include` to store function body in a separate file instead of storing it inside YAML. ## Links * [CREATE FUNCTION](https://docs.snowflake.com/en/sql-reference/sql/create-function.html) * [SHOW USER FUNCTIONS](https://docs.snowflake.com/en/sql-reference/sql/show-user-functions.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/function.py) # HYBRID TABLE Config path: `///hybrid_table/.yaml` Example: ```yaml columns: actor_id: NUMBER(10,0) NOT NULL first_name: VARCHAR(45) NOT NULL last_name: VARCHAR(45) NOT NULL last_update: TIMESTAMP_NTZ(3) primary_key: [actor_id] ``` ```yaml columns: ticket_no: type: VARCHAR(13) NOT NULL comment: "Ticket number" flight_id: type: NUMBER(10,0) NOT NULL comment: "Flight ID" boarding_no: type: NUMBER(10,0) NOT NULL comment: "Boarding pass number" seat_no: type: VARCHAR(4) NOT NULL comment: "Seat number" primary_key: [ticket_no, flight_id] unique_keys: - [flight_id, boarding_no] - [flight_id, seat_no] foreign_keys: - columns: [ticket_no, flight_id] ref_table: ticket_flights ref_columns: [ticket_no, flight_id] indexes: - columns: [flight_id] ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - full [data type](/guides/data-types) with optional "NOT NULL" constraint\ \--- OR --- * *{value}* (dict) * **type** (str) - full [data type](/guides/data-types) with optional "NOT NULL" constraint * **default** (str) - default SQL expression * **default\_sequence** (ident) - sequence used for "auto increment" * **collate** (str) - column [collation](https://docs.snowflake.com/en/sql-reference/collation.html#label-collation-specification) for string comparison * **comment** (str) * **comment** (str) * **primary\_key** (list) * *{items}* (ident) - column names for PRIMARY KEY constraint * **unique\_keys** (list) * *{items}* (list) * *{items}* (ident) - column names for UNIQUE KEY constraint * **foreign\_keys** (list) * *{items} (dict)* - FOREIGN KEY definitions * **columns** (list) * *{items}* (ident) - column names from current table * **ref\_table** (ident) - reference table * **ref\_columns** (list) * *{items}* (ident) - column names from reference table * **indexes** (list) * *{items}* (dict) - INDEX definition * **columns** (list) * *{items}* (ident) - column names to be indexed * **include** (list) * *{items}* (ident) - column names to additionally include for covering index ## Usage notes 1. **Columns** definition has two possible syntax options: a) Short syntax (str) with column **type** definition only.\ b) Full syntax (dict) with **type** definition as well as other properties. 2. Column **type** is a full native Snowflake data type definition, exactly how it appears in output of [DESC TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html) command. Aliases and short forms [are not allowed](/guides/data-types). 3. Column **default** is an SQL expression, not value. `VARCHAR` values should be enclosed in quotes. `TIMESTAMP_*` values should be casted explicitly. 4. Anonymous auto-increment is not supported. All [sequences](/basic/yaml-configs/sequence) must be created explicitly and assigned to `default_sequence` of relevant table columns. It helps to preserve sequence value when table is re-created. ## Safe & unsafe operations At this moment only the following operations are "safe" for hybrid tables: * Create a new hybrid table * Add or remove unique key from hybrid table * Add or remove foreign key from hybrid table All other changes would be resolved as "unsafe" `CREATE OR REPLACE TABLE ... AS SELECT` command. In order to apply this command, you also need to specify the following CLI option: `--apply-replace-table` Hybrid tables are designed to be relatively small (less than 100Gb), so replacement should be a relatively cheap operation. We may consider implementing basic table and secondary index transformations in future once Hybrid table feature is available in all regions and leaves "Public preview". ## Links * [CREATE HYBRID TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-hybrid-table) * [SHOW HYBRID TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-hybrid-tables) * [DESC TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/hybrid_table.py) # ICEBERG TABLE {% hint style="warning" %} At this moment only UNMANAGED Iceberg tables are supported. Please read a guide about using [Iceberg tables](/guides/other-guides/iceberg-tables) with SnowDDL first. {% endhint %} Config path: `///iceberg_table/.yaml` Examples: ```yaml metadata_file_path: test_iceberg_table_1/metadata/00001-cc112050-1448-4c2a-9e03-504e7f5fc62a.metadata.json replace_invalid_characters: true ``` ```yaml catalog_table_name: test_iceberg_table_1 comment: abc ``` ## Schema * **catalog\_table\_name** (str) - name of Iceberg table in externally managed catalog * **catalog\_namespace** (str) - namespace of Iceberg table in externally managed catalog, inherited from CATALOG object by default * **metadata\_file\_path** (str) - path to metadata file * **base\_location** (str) - path to base location of table files * **replace\_invalid\_characters** (bool) * **auto\_refresh** (bool) * **comment** (str) ## Usage notes 1. EXTERNAL VOLUME name and CATALOG name should be specified in [SCHEMA](/basic/yaml-configs/schema) config. 2. It is required to specify one of the following parameters: **catalog\_table\_name**, **metadata\_file\_path**, **base\_location**. 3. Unmanaged Iceberg tables are similar to Snowflake External tables. These tables are read-only and fully re-created in case of any changes in definition. ## Links * [CREATE ICEBERG TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-iceberg-table) * [ALTER ICEBERG TABLE](https://docs.snowflake.com/en/sql-reference/sql/alter-iceberg-table) * [SHOW ICEBERG TABLES](https://docs.snowflake.com/en/sql-reference/sql/show-iceberg-tables) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/iceberg_table.py#L9-L40) # JOIN POLICY Config path: `///join_policy/.yaml` Example: ```yaml body: |- CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN JOIN_CONSTRAINT(JOIN_REQUIRED => FALSE) ELSE JOIN_CONSTRAINT(JOIN_REQUIRED => TRUE) END ``` ## Schema * **body** (str) - policy SQL expression * **comment** (str) ## Usage notes 1. Management of join policies requires active warehouse due to unavoidable [POLICY\_REFERENCES](https://docs.snowflake.com/en/sql-reference/functions/policy_references.html) table function calls. 2. Row access policies always return `JOIN_CONSTRAINT`. ## Links * [CREATE JOIN POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-join-policy) * [SHOW JOIN POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-join-policies) * [DESC JOIN POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-join-policy) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/join_policy.py) # MASKING POLICY Config path: `///masking_policy/.yaml` Example: ```yaml arguments: name: VARCHAR(255) returns: VARCHAR(255) body: |- REPLACE(name, 'A', '*') references: - object_type: TABLE object_name: test_table_1 columns: [name] ``` ## Schema * **arguments** (dict) * *{key}* (ident) - argument name * *{value}* (str) - argument data type * **returns** (str) - return data type * **body** (str) - policy SQL expression * **exempt\_other\_policies** (bool) * ~~**references**~~ (dict) * **object\_type** (str) - reference object type (e.g. `TABLE`, `VIEW`) * **object\_name** (ident) - reference object name * **columns** (list) * *{items}* (ident) - reference column names * **comment** (str) ## Usage notes 1. Management of masking policies requires active warehouse due to unavoidable [POLICY\_REFERENCES](https://docs.snowflake.com/en/sql-reference/functions/policy_references.html) table function calls. 2. If **arguments** or **returns** of policy was changed, all references will be dropped, policy will be re-created from scratch, and all references will be restored. Also, when other objects are being re-created, such objects will initially lack policy references.\ \ Business users might be able to access objects without protection of policy in such case. There is no way to avoid it due to fundamental lack of transaction support for DDL queries in Snowflake.\ \ You may consider having weekly "safe maintenance" time slots to apply DDL when business users won't be able to access Snowflake account. 3. Parameter `references` is deprecated since `0.33.0`. Use policy reference parameters directly in [TABLE](/basic/yaml-configs/table) or [VIEW](/basic/yaml-configs/view) configs instead. ## Links * [CREATE MASKING POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-masking-policy.html) * [SHOW MASKING POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-masking-policies.html) * [DESC MASKING POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-masking-policy.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/masking_policy.py) # MATERIALIZED VIEW Config path: `///materialized_view/.yaml` Example: ```yaml text: |- SELECT id, name FROM test_ext_table_1 is_secure: true ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - column comment * **text** (str) - view text * **is\_secure** (bool) - is view secure * **cluster\_by** (list) * *{items}* (str) - SQL expressions for CLUSTER BY * **comment** (str) ## Usage notes 1. If you include **cluster\_by**, **columns** are also required. 2. Maintenance of materialized views implicitly incurs [additional costs](https://docs.snowflake.com/en/user-guide/views-materialized.html#label-materialized-views-maintenance-billing). 3. Invalid materialized views will be re-created automatically. ## Links * [CREATE MATERIALIZED VIEW](https://docs.snowflake.com/en/sql-reference/sql/create-materialized-view.html) * [SHOW MATERIALIZED VIEWS](https://docs.snowflake.com/en/sql-reference/sql/show-materialized-views.html) * [Working with Materialized Views](https://docs.snowflake.com/en/user-guide/views-materialized.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/materialized_view.py) # NETWORK POLICY Config path: `/network_policy.yaml` Example: ```yaml test_network_policy_1: allowed_ip_list: - 0.0.0.0/0 blocked_ip_list: - 1.1.1.1 - 8.8.8.8 test_network_policy_2: allowed_network_rule_list: - my_db.my_schema.my_rule_1 - my_db.my_schema.my_rule_2 blocked_network_rule_list: - my_db.my_schema.my_rule_3 - my_db.my_schema.my_rule_4 ``` ## Schema * *{key}* (ident) - name of network policy * *{value}* (dict) * **allowed\_network\_rule\_list** (list) * *{items}* (ident) - network rules allowing access to Snowflake account * **blocked\_network\_rule\_list** (list) * *{items}* (ident) - network rules blocking access to Snowflake account * **allowed\_ip\_list** (list) * *{items}* (str) - IPv4 addresses that are allowed access to Snowflake account * **blocked\_ip\_list** (list) * *{items}* (str) - IPv4 addresses that are denied access to Snowflake account * **comment** (str) ## Usage notes 1. It is recommended to review and apply changes to `NETWORK POLICIES` manually due to high security risk. 2. Since `NETWORK POLICIES` are account-level objects and `NETWORK RULES` are schema-level objects, names of network rules should be fully qualified `..`. ## Links * [CREATE NETWORK POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-network-policy.html) * [ALTER NETWORK POLICY](https://docs.snowflake.com/en/sql-reference/sql/alter-network-policy.html) * [DESC NETWORK POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-network-policy.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/network_policy.py) # NETWORK RULE Config path: `///network_rule/.yaml` Example: ```yaml type: IPV4 mode: INGRESS value_list: - 192.168.2.0/24 - 192.168.1.99 ``` ```yaml type: HOST_PORT mode: EGRESS value_list: - example.com - company.com:443 ``` ## Schema * **type** (str) - network rule type (`IPV4`, `HOST_PORT`, etc.) * **mode** (str) - restriction mode (`INGRESS`, `EGRESS`, `INTERNAL_STAGE`) * **value\_list** (list) * *{items}* (str) - network identifiers that will be allowed or blocked * **comment** (str) ## Links * [CREATE NETWORK RULE](https://docs.snowflake.com/en/sql-reference/sql/create-network-rule) * [SHOW NETWORK RULES](https://docs.snowflake.com/en/sql-reference/sql/show-network-rules) * [DESC NETWORK RULE](https://docs.snowflake.com/en/sql-reference/sql/desc-network-rule) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/network_rule.py) # PERMISSION MODEL Config path: `/permision_model.yaml` ## What is permission model? Permission model is a special concept introduced by SnowDDL to help managing permissions for databases and schemas. Permission model holds information about `CREATE GRANTS` and `FUTURE GRANTS`. This page describes the practical configuration of permission models. You may consider to reading [this guide](/guides/permission-model) first explaining the concept in greater detail. ## Default model `DEFAULT` model is always present and does not require configuration. It is automatically applied to databases and schemas without `permission_model` parameter being explicitly set. `DEFAULT` model will most likely evolve in future versions of SnowDDL in response to Snowflake adding more object types and changing privileges. ```yaml default: ruleset: SCHEMA_OWNER owner_create_grants: - FILE_FORMAT - FUNCTION - PROCEDURE - TABLE - VIEW owner_future_grants: ALERT: [OWNERSHIP] DYNAMIC_TABLE: [OWNERSHIP] EVENT_TABLE: [OWNERSHIP] EXTERNAL_TABLE: [OWNERSHIP] FILE_FORMAT: [OWNERSHIP] FUNCTION: [OWNERSHIP] MATERIALIZED_VIEW: [OWNERSHIP] PIPE: [OWNERSHIP] PROCEDURE: [OWNERSHIP] SEQUENCE: [OWNERSHIP] STAGE: [OWNERSHIP] STREAM: [OWNERSHIP] TABLE: [OWNERSHIP] TASK: [OWNERSHIP] VIEW: [OWNERSHIP] write_future_grants: STAGE: [READ, WRITE, USAGE] SEQUENCE: [USAGE] TABLE: [INSERT, UPDATE, DELETE, TRUNCATE] read_future_grants: DYNAMIC_TABLE: [SELECT] EXTERNAL_TABLE: [SELECT, REFERENCES] FILE_FORMAT: [USAGE] FUNCTION: [USAGE] MATERIALIZED_VIEW: [SELECT, REFERENCES] PROCEDURE: [USAGE] STAGE: [READ, USAGE] STREAM: [SELECT] TABLE: [SELECT, REFERENCES] VIEW: [SELECT, REFERENCES] ``` ## Schema * *{key} (ident)* - permission model role name * *{value}* (dict) * **inherit\_from** (str) - inherit all settings from `DEFAULT` or from another permission model defined earlier in the same config file. This feature helps to reduce code repetition when you have a lot of similar models with minor differences. * **ruleset** (str) - specific rules how "create grants" and "future grants" should be applied, currently supported values are: `SCHEMA_OWNER`, `DATABASE_OWNER` * **owner\_create\_grants** (list) - object types which `OWNER` role can create * *{items}* (str) - object type * **owner\_future\_grants** (dict) - future grants for `OWNER` role * *{key}* (str) - object type * *{value}* (list) * *{items}* (str) - privilege name * **write\_future\_grants** (dict) - future grants for `WRITE` role * *{key}* (str) - object type * *{value}* (list) * *{items}* (str) - privilege name * **read\_future\_grants** (dict) - future grants for `READ` role * *{key}* (str) - object type * *{value}* (list) * *{items}* (str) - privilege name ## Usage notes 1. If you define permission model with name `default`, it will completely override default permission model which exists in SnowDDL. 2. `OWNERSHIP` privilege can only be granted to `OWNER` role. If you do not grant this privilege for specific object type, objects of this type will be owned by SnowDDL admin role or user role of user who created this object. Normally it is highly advised to define `OWNERSHIP` privilege for all object types you are planning to use. 3. You may change `create_grants` and `future_grants` at any time, but changing `ruleset` may require some additional considerations. Documentation page about changing `ruleset` for existing databases will be added shortly. ## Examples Example which creates a slightly extended version of `DEFAULT` model with additional grants: ```yaml my_custom_model: inherit_from: default owner_create_grants: - STAGE read_future_grants: STAGE: [USAGE] ``` Example which creates a custom permission model for Fivetran with `DATABASE_OWNER` ruleset: ```yaml my_custom_fivetran_model ruleset: DATABASE_OWNER owner_create_grants: - STAGE - TABLE - VIEW owner_future_grants: STAGE: [OWNERSHIP] TABLE: [OWNERSHIP] VIEW: [OWNERSHIP] read_future_grants: STAGE: [READ] TABLE: [SELECT, REFERENCES] VIEW: [SELECT, REFERENCES] ``` ## Links * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/permission_model.py) # PIPE Config path: `///pipe/.yaml` Example: ```yaml copy: table: test_table_2 stage: test_external_stage transform: id: "GET($1, 'id')" name: "GET($1, 'name')" auto_ingest: false ``` ## Schema * **copy** (dict) * **table** (ident) - target table for COPY INTO * **stage** (ident) - source stage for COPY INTO * **path** (str) - path prefix for files in stage * **pattern** (str) - regular expression to filter files in stage * **file\_format** (ident) - [file format](/basic/yaml-configs/file-format) for files in stage * **match\_by\_column\_name** (str) - case\_sensitive / case\_insensitive * **include\_metadata** (dict) * *{key}* (ident) - column name in target table * *{value}* (ident) - column name in metadata, e.g. `METADATA$FILENAME` * **transform** (dict) * *{key}* (ident) - column name in target table * *{value}* (str) - SQL expression to extract column value from source stage * **options** (dict) * *{key}* (ident) - COPY option name * *{value}* (bool, float, int, list, str) - COPY option value * **auto\_ingest** (bool) - enable `AUTO_INGEST` * **aws\_sns\_topic** (str) - SNS topic for S3 bucket * **integration** (ident) - notification [integration](/guides/other-guides/integrations) name for Azure * **error\_integration** (ident) - notification integration to monitor ingestion errors * **comment** (str) ## Usage notes 1. Re-creating pipes correctly is complicated. Make sure you read & fully understand [pipe recreation considerations](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-manage.html#recreating-pipes). SnowDDL can only suggest or apply DDL queries to change pipes, but it cannot pause, monitor and refresh pipes for you. 2. Maintenance of active pipes implicitly incurs [additional costs](https://docs.snowflake.com/en/user-guide/data-load-snowpipe-billing.html). 3. **file\_format** can only be specified by name referencing to `FILE_FORMAT` object. 4. Notification integration mentioned in **error\_integration** parameter must be additionally specified in [schema](/basic/yaml-configs/schema) parameter **owner\_integration\_usage**. Otherwise schema owner role will not be able to send notifications. ## Additional privileges In order for `PIPE` objects to operate properly, the following additional grants should be added to OWNER role in [schema config](/basic/yaml-configs/schema): * `owner_integration_usage` - please specify names of INTEGRATION objects used by pipes. Pipes may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever. ## Links * [CREATE PIPE](https://docs.snowflake.com/en/sql-reference/sql/create-pipe.html) * [ALTER PIPE](https://docs.snowflake.com/en/sql-reference/sql/alter-pipe.html) * [SHOW PIPES](https://docs.snowflake.com/en/sql-reference/sql/show-pipes.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/pipe.py) # PLACEHOLDER Config path: `/placeholder.yaml` Example: ```yaml wh_size: SMALL wh_auto_suspend: 300 bucket_name: dev-test-bucket ``` ## Schema * *{key}* (ident) - placeholder name * *{value}* (bool, float, int, str) - placeholder value ## Usage notes 1. Usage of placeholders is described in [YAML placeholders](/basic/yaml-placeholders) guide. 2. Data types of placeholder values are preserved. ## Links * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/placeholder.py) # PROCEDURE Config path: `///procedure/().yaml` Example: ```yaml arguments: ticket_no: VARCHAR(13) flight_id: NUMBER(10,0) boarding_no: NUMBER(10,0) seat_no: VARCHAR(4) returns: BOOLEAN body: |- BEGIN INSERT INTO boarding_passes (ticket_no, flight_id, boarding_no, seat_no) VALUES (:TICKET_NO, :FLIGHT_ID, :BOARDING_NO, :SEAT_NO); RETURN TRUE; END; ``` ## Schema * **language** (str) - language of function (default: SQL) * **arguments** (dict) * *{key}* (ident) - argument name * *{value}* (str) - argument [data type](/guides/data-types)\ \--- OR --- * *{value}* (dict) * **type** (str) - argument [data type](/guides/data-types) * **default** (str) - default SQL expression for optional argument * **returns** (str) - for single return value, return data type\ \--- OR --- * **returns** (dict) - for table return values * *{key}* (ident) - return column name * *{value}* (str) - return column data type * **body** (str) - procedure body * **is\_strict** (bool) - is procedure STRICT (always returns NULL on NULL input) * **is\_execute\_as\_caller** (bool) - is function executed "as caller" (default "as owner") * **imports** (list) - files to import (usually JAR packages) * *{items}* (dict) * **stage** (ident) - name of stage * **path** (str) - path to file * **packages** (list) - Snowflake system packages to import as dependencies * *{items}* (str) - name of package, with optional version of package * **handler** (str) - name of class and method to be called * **external\_access\_integrations** (list) * *{items}* (ident) - name of [external access integration](/basic/yaml-configs/external-access-integration) * **secrets** (dict) * *{key}* (str) - secret variable name used in procedure code * *{value}* (ident) - name of [secret](/basic/yaml-configs/secret) object * **comment** (str) ## Usage notes 1. Snowflake supports [overloading](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#overloading-of-names) of procedure names. Multiple procedures may have the same name as long as they have different arguments. It is required to use comma-separated base data types of arguments in config names.\ \ For example: `my_procedure(number).yaml`, `my_procedure(varchar,number).yaml` 2. Make sure to read & fully understand ["caller rights" and "owners rights"](https://docs.snowflake.com/en/sql-reference/stored-procedures-rights.html) for stored procedures. It is very important for security. 3. Files for `imports` should be maintained using [STAGE FILES](/basic/yaml-configs/stage-file). 4. If function `body` is empty, `handler` and `imports` with pre-compiled JAR or Python code are required. 5. `runtime_version` should be specified as string with explicit double-quotes (e.g. `"3.8"`). Otherwise YAML parser may confuse it with number, which may cause some unwanted effects. 6. You may use [custom YAML tag](/basic/yaml-tag-include) `!include` to store procedure body in a separate file instead of storing it inside YAML. 7. In order to omit return values for table procedures, use the "empty dict syntax": `returns: {}` ## Additional privileges Procedures with without `is_execute_as_caller: True` are executed with "schema owner role" privileges. If you want to access objects in other schemas, make sure to specify additional owner grant parameters in [SCHEMA](/basic/yaml-configs/schema) config. For example: * `owner_schema_read` - to read objects in other schemas; * `owner_integration` - to access objects in EXTERNAL STAGE linked to STORAGE INTEGRATION; ## Links * [CREATE PROCEDURE](https://docs.snowflake.com/en/sql-reference/sql/create-procedure.html) * [SHOW PROCEDURES](https://docs.snowflake.com/en/sql-reference/sql/show-procedures.html) * [DESC PROCEDURE](https://docs.snowflake.com/en/sql-reference/sql/desc-procedure.html) * [Working with Stored Procedures](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/procedure.py) # PROJECTION POLICY Config path: `///projection_policy/.yaml` Example: ```yaml body: |- CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN PROJECTION_CONSTRAINT(ALLOW => true) ELSE PROJECTION_CONSTRAINT(ALLOW => true) END references: - object_type: TABLE object_name: test_table_1 column: id - object_type: VIEW object_name: test_view_1 column: id comment: my projection policy ``` ## Schema * **body** (str) - policy SQL expression * ~~**references**~~ (dict) * **object\_type** (str) - reference object type (e.g. `TABLE`, `VIEW`) * **object\_name** (ident) - reference object name * **column** (ident) - reference column name * **comment** (str) ## Usage notes 1. Management of projection policies requires active warehouse due to unavoidable [POLICY\_REFERENCES](https://docs.snowflake.com/en/sql-reference/functions/policy_references.html) table function calls. 2. Make sure to allow projections for role `SYSADMIN`, especially if projection policy is being applied to views. Otherwise SnowDDL will have to re-create VIEW on every run due to inability to verify column data types.\ \ Example of check: `CASE WHEN IS_ROLE_IN_SESSION('SYSADMIN') THEN PROJECTION_CONSTRAINT(ALLOW => true) ELSE ... END` 3. Parameter `references` is deprecated since `0.33.0`. Use policy reference parameters directly in [TABLE](/basic/yaml-configs/table) or [VIEW](/basic/yaml-configs/view) configs instead. ## Links * [CREATE PROJECTION POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-projection-policy) * [SHOW PROJECTION POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-projection-policies) * [DESC PROJECTION POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-projection-policy) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/projection_policy.py) # RESOURCE MONITOR Config path: `/resource_monitor.yaml` Example: ```yaml test_res_monitor_1: credit_quota: 125 frequency: monthly triggers: 50: notify 75: notify 100: suspend 110: suspend_immediate ``` ## Schema * *{key}* (ident) - name of resource monitor * *{value}* (dict) * **credit\_quota** (int) - he number of credits allocated to the resource monitor per frequency interval * **frequency** (str) - `MONTHLY | DAILY | WEEKLY | YEARLY | NEVER` * **triggers** (dict) * {key} (int) - quota threshold in percent * {value) (str) - action to take when threshold was reached `SUSPEND | SUSPEND_IMMEDIATE | NOTIFY` ## Usage notes 1. Setting custom `START_TIMESTAMP` and `END_TIMESTAMP` is currently not supported, since it will cause your config to depend on wall clock time. `START_TIMESTAMP` is automatically set to `IMMEDIATELY` on creation and on change of frequency of resource monitor. 2. Comments for resource monitors are not supported by Snowflake. ## Links * [CREATE RESOURCE MONITOR](https://docs.snowflake.com/en/sql-reference/sql/create-resource-monitor.html) * [ALTER RESOURCE MONITOR](https://docs.snowflake.com/en/sql-reference/sql/alter-resource-monitor.html) * [SHOW RESOURCE MONITORS](https://docs.snowflake.com/en/sql-reference/sql/show-resource-monitors.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/resource_monitor.py) # ROW ACCESS POLICY Config path: `///row_access_policy/.yaml` Example: ```yaml arguments: status: VARCHAR(255) body: |- status = 'Active' references: - object_type: TABLE object_name: test_table_2 columns: [status] ``` ## Schema * **arguments** (dict) * *{key}* (ident) - argument name * *{value}* (str) - argument data type * **body** (str) - policy SQL expression * ~~**references**~~ (dict) * **object\_type** (str) - reference object type (e.g. `TABLE`, `EXTERNAL_TABLE`) * **object\_name** (ident) - reference object name * **columns** (list) * *{items}* (ident) - reference column names * **comment** (str) ## Usage notes 1. Management of row access policies requires active warehouse due to unavoidable [POLICY\_REFERENCES](https://docs.snowflake.com/en/sql-reference/functions/policy_references.html) table function calls. 2. Row access policies always return `BOOLEAN`. 3. Snowflake documentation is incorrect. It is only possible to have one row access policy per object. 4. If **arguments** of policy was changed, all references will be dropped, policy will be re-created from scratch, and all references will be restored. Also, when other objects are being re-created, such objects will initially lack policy references.\ \ Business users might be able to access objects without protection of policy in such case. There is no way to avoid it due to fundamental lack of transaction support for DDL queries in Snowflake.\ \ You may consider having weekly "safe maintenance" time slots to apply DDL when business users won't be able to access Snowflake account. 5. Parameter `references` is deprecated since `0.33.0`. Use policy reference parameters directly in [TABLE](/basic/yaml-configs/table) or [VIEW](/basic/yaml-configs/view) configs instead. ## Links * [CREATE ROW ACCESS POLICY](https://docs.snowflake.com/en/sql-reference/sql/create-row-access-policy.html) * [SHOW ROW ACCESS POLICIES](https://docs.snowflake.com/en/sql-reference/sql/show-row-access-policies.html) * [DESC ROW ACCESS POLICY](https://docs.snowflake.com/en/sql-reference/sql/desc-row-access-policy.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/row_access_policy.py) # SCHEMA Config path: `///params.yaml` Example: ```yaml retention_time: 7 is_sandbox: true owner_schema_read: - another_db.another_schema_1 - another_db.another_schema_2 owner_warehouse_usage: - my_warehouse owner_integration_usage: - my_storage_integration owner_account_grants: - EXECUTE ALERT ``` ## Schema * **is\_transient** (bool) - make schema TRANSIENT * **retention\_time** (int) - data retention time in days * **is\_sandbox** (bool) - custom objects created in sandbox schema will not be dropped if not present in config * **permission\_model** (str) **-** name of custom [permission model](/basic/yaml-configs/permission-model) * **external\_volume** (ident) - name of EXTERNAL VOLUME used for [Iceberg tables](/guides/other-guides/iceberg-tables) * **catalog** (ident) - name of CATALOG used for [Iceberg tables](/guides/other-guides/iceberg-tables) * **log\_level** (str) - logging parameter [LOG\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#label-log-level) * **log\_event\_level** (str) - logging parameter LOG\_EVENT\_LEVEL * **metric\_level** (str) - logging parameter [METRIC\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#label-metric-level) * **trace\_level** (str) - logging parameter [TRACE\_LEVEL](https://docs.snowflake.com/en/sql-reference/parameters#trace-level) * **quoted\_identifiers\_ignore\_case** (bool) * **owner\_database\_read** (list) * *{items}* (ident) - grant READ privileges for objects in a database to OWNER role of this schema * **owner\_database\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in a database to OWNER role of this schema * **owner\_schema\_read** (list) * *{items}* (ident) - grant READ privileges for objects in another schema to OWNER role of this schema * **owner\_schema\_write** (list) * *{items}* (ident) - grant WRITE privileges for objects in another schema to OWNER role of this schema * **owner\_share\_read** (list) * *{items}* (ident) - grant IMPORTED PRIVILEGES or DATABASE ROLE for inbound share to OWNER role of this schema * **owner\_integration\_usage** (list) * *{items}* (ident) - grant USAGE privilege on global integration to OWNER role of this schema * **owner\_warehouse\_usage** (list) * *{items}* (ident) - grant USAGE privilege on warehouse to OWNER role of this schema * **owner\_account\_grants** (list) * *{items}* (str) - grant account-level privilege to OWNER role of this schema * **owner\_global\_roles** (list) * *{items}* (ident) - grant external roles with custom permissions created outside of SnowDDL to OWNER role of this schema * **comment** (str) ## Usage notes 1. File `params.yaml` is optional. All parameters are set to default if file is omitted. 2. **is\_transient** and **retention\_time** are inherited from [DATABASE](/basic/yaml-configs/database) object if omitted. 3. Objects in database or schema marked with **is\_sandbox** flag will not be dropped by SnowDDL if not defined in config. 4. When defining custom **permission\_model**, the ruleset of database permission\_model and schema permission\_model must be the same. 5. **owner\_schema\_read** and **owner\_schema\_write** parameters are helpful when dealing with `VIEWS` and `PROCEDURES`, which require access to objects in another schemas. Usually only objects in the current schema are available to the `OWNER` role of this schema. 6. **owner\_integration\_usage** parameter helps to provide additional `USAGE` privileges on various externally defined [integration](/guides/other-guides/integrations) objects to schema `OWNER` role, which helps to resolve various permission-related issues. For example, it is required in order for error notification integrations to work properly. ## Links * [CREATE SCHEMA](https://docs.snowflake.com/en/sql-reference/sql/create-schema.html) * [ALTER SCHEMA](https://docs.snowflake.com/en/sql-reference/sql/alter-schema.html) * [SHOW SCHEMAS](https://docs.snowflake.com/en/sql-reference/sql/show-schemas.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/schema.py) # SECRET Config path: `///secret/.yaml` Example: ```yaml type: oauth2 api_authentication: TEST_API_SECURITY_INTEGRATION oauth_refresh_token: RjY2NjM5NzA2OWJjuE7c oauth_refresh_token_expiry_time: "2030-01-01 00:00:00" ``` ```yaml type: generic_string secret_string: very secret string! ``` ## Schema * **type** (str) - secret type (`OAUTH2`, `PASSWORD`, `GENERIC_STRING`) * **api\_authentication** (str) - name of Snowflake [security integration](https://docs.snowflake.com/en/sql-reference/sql/create-security-integration) * **oauth\_scopes** (list) * *{items}* (str) - list of scopes to use when making a request from the OAuth server * **oauth\_refresh\_token** (str) - token that is used to obtain a new access token from the OAuth authorization server when the access token expires * **oauth\_refresh\_token\_expiry\_time** (str) - timestamp when the OAuth refresh token expires * **username** (str) - username value to store in the secret * **password** (str) - password value to store in the secret * **secret\_string** (str) - string to store in the secret * **algorithm** (str) * **comment** (str) ## Usage notes 1. In order to avoid storing secrets in config files as plain text, you may use [placeholders](/basic/yaml-placeholders) or [programmatic config](/advanced/programmatic-config). 2. Security integrations are not managed by SnowDDL and should be [created separately](/guides/other-guides/integrations). 3. Snowflake does not return actual secret values from `SHOW` and `DESC` commands, so SnowDDL is unable to properly detect changes in config secret values. If you update any secret values in config, you should use additional CLI option `--refresh-secrets` to enforce update in Snowflake account. ## Links * [CREATE SECRET](https://docs.snowflake.com/en/sql-reference/sql/create-secret) * [SHOW SECRETS](https://docs.snowflake.com/en/sql-reference/sql/show-secrets) * [DESC SECRET](https://docs.snowflake.com/en/sql-reference/sql/desc-secret) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/secret.py) # SEMANTIC VIEW Config path: `///semantic_view/.yaml` Example: ```yaml tables: - table_alias: tb1 table_name: sv001_tb1 primary_key: [author_id, book_id] with_synonyms: [aaa, bbb] comment: "Link between authors and books" - table_alias: tb2 table_name: sv001_tb2 primary_key: [author_id] with_synonyms: [ccc, ddd] comment: "List of authors" - table_alias: tb3 table_name: sv001_tb3 primary_key: [book_id] unique: - [book_isbn] comment: "List of books" relationships: - table_alias: tb1 columns: [author_id] ref_table_alias: tb2 ref_columns: [author_id] - table_alias: tb1 columns: [book_id] ref_table_alias: tb3 ref_columns: [book_id] facts: - table_alias: tb1 name: unique_id sql: CONCAT(author_id, '-', book_id) with_synonyms: [aaa, bbb] comment: "Unique ID for link between authors and books" - table_alias: tb3 name: number_of_pages sql: number_of_pages comment: "Number of pages in a book" dimensions: - table_alias: tb1 name: author_book_link_create_dt sql: CAST(create_ts AS DATE) comment: "Date when link between author and book was established" - table_alias: tb2 name: author_name sql: author_name comment: "Name of author" - table_alias: tb3 name: book_name sql: book_name comment: "Name of book" metrics: - table_alias: tb1 name: count_distinct_author sql: count(distinct author_id) comment: "Number of unique authors" - table_alias: tb1 name: count_distinct_book sql: count(distinct book_id) comment: "Number of unique books" comment: abc ``` ## Schema * **tables** (list) * *{items}* (dict) * **table\_alias** (ident) - optional alias for logical table * **table\_name** (ident) * **primary\_key** (list) - columns defining primary key * *{items}* (ident) - column name * **unique** (list) - columns defining unique keys * *{items}* (list) * *{items}* (ident) - column name * **with\_synonyms** (list) - synonyms for logical table * *{items}* (str) * **comment** (str) * **relationships** (list) * {items} (dict) * **relationship\_identifier** (str) - optional alias for relationship * **table\_alias** (ident) - first logical table * **columns** (list) - columns of first logical table * *{items}* (ident) * **ref\_table\_alias** (ident) - second logical table * **ref\_columns** (list) - columns of second logical table * *{items}* (ident) * **facts** (list) * *{items}* (dict) * **table\_alias** (ident) - name or alias for logical table * **name** (ident) - name of fact * **sql** (str) - SQL expression defining fact * **with\_synonyms** (list) - synonyms for fact * *{items}* (str) * **comment** (str) * **dimensions** (list) * *{items}* (dict) * **table\_alias** (ident) - name or alias for logical table * **name** (ident) - name of dimension * **sql** (str) - SQL expression defining dimension * **with\_synonyms** (list) - synonyms for dimension * *{items}* (str) * **comment** (str) * **metrics** (list) * *{items}* (dict) * **table\_alias** (ident) - name or alias for logical table * **name** (ident) - name of metric * **sql** (str) - SQL expression defining metric * **with\_synonyms** (list) - synonyms for metric * *{items}* (str) * **comment** (str) * **comment** (str) ## Links * [CREATE SEMANTIC VIEW](https://docs.snowflake.com/en/sql-reference/sql/create-semantic-view) * [SHOW SEMANTIC VIEWS](https://docs.snowflake.com/en/sql-reference/sql/show-semantic-views) * [DESC SEMANTIC VIEW](https://docs.snowflake.com/en/sql-reference/sql/desc-semantic-view) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/semantic_view.py) # SEQUENCE Config path: `///sequence/.yaml` Example: ```yaml start: 1 interval: 1 ``` ## Schema * **start** (int) - starting value for sequence (default: 1) * **interval** (int) - sequence step (default: 1) * **is\_ordered** (bool) - enforce ordered mode (true) or non-ordered (false) mode for newly created sequence. If omitted, value of `NOORDER_SEQUENCE_AS_DEFAULT` account parameter is automatically used as default. * **comment** (str) ## Usage notes 1. **Start** is applied on creation of sequence only. Existing sequences always retain their current value. 2. All sequences must be created explicitly and assigned to `default_sequence` of relevant table columns. It helps to preserve sequence value when table must be re-created to apply changes which cannot be applied via `ALTER TABLE`. 3. It is possible to convert an existing ordered sequence into a non-ordered mode. But it is not possible to do it in reverse. ## Links * [CREATE SEQUENCE](https://docs.snowflake.com/en/sql-reference/sql/create-sequence.html) * [ALTER SEQUENCE](https://docs.snowflake.com/en/sql-reference/sql/alter-sequence.html) * [SHOW SEQUENCES](https://docs.snowflake.com/en/sql-reference/sql/show-sequences.html) * [DESC SEQUENCE](https://docs.snowflake.com/en/sql-reference/sql/desc-sequence.html) * [Using Sequences](https://docs.snowflake.com/en/user-guide/querying-sequences.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/sequence.py) # SHARE (outbound) Config path: `/outbound_share.yaml` Example: ```yaml test_share: accounts: - SFSALESSHARED.SFC_SAMPLES_AWS_EU_WEST_2 grants: DATABASE:USAGE: - test_db SCHEMA:USAGE: - test_db.test_schema TABLE:SELECT: - test_db.test_schema.* FUNCTION:USAGE: - test_db.test_schema.test_secure_udf(varchar) DATABASE_ROLE:USAGE: - test_db.test_database_role comment: Test share ``` ## Schema * *{key}* (ident) - share name * *{value}* (dict) * **accounts** (list) * {items} (ident) - identifiers of consumer accounts: `.`. * **grants** (str) * *{key}* (str) - `:` * *{value}* (list) * *{items}* (ident) - full objects names or name patterns to grant privilege; * **share\_restrictions** (bool) - should be set to `false` in order to create SHARE from Business Critical account to accounts with lower edition; * **comment** (str) ## Usage notes 1. Outbound shares are processed if at least one share exists in config. 2. All changes to outbound shares are ["unsafe"](/guides/other-guides/safe-unsafe) and should be reviewed carefully, since it may expose data from your account to 3rd parties. 3. Outbound shares require additional privileges for [SnowDDL administration user](/guides/other-guides/admin): `IMPORT SHARE`, `CREATE SHARE`. 4. Parameter **share\_restrictions** requires additional privilege: `OVERRIDE SHARE RESTRICTIONS`. 5. All limitations related to [`GRANT ... TO SHARE`](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege-share.html) command applies to **grants**. Please read it carefully. 6. It is possible to use [Unix-style wildcard patterns](https://docs.python.org/3/library/fnmatch.html) for grant object names. 7. Grants created externally and matching Unix-style wildcard patterns **will not be dropped** if objects are not explicitly defined in config. It is an intentional workaround for lack of future grants on shares. 8. You may create and grant [DATABASE ROLE](/basic/yaml-configs/database-role) to share using `DATABASE_ROLE:USAGE` privilege. ## Links * [CREATE SHARE](https://docs.snowflake.com/en/sql-reference/sql/create-share.html) * [ALTER SHARE](https://docs.snowflake.com/en/sql-reference/sql/alter-share.html) * [Override share restrictions](https://docs.snowflake.com/en/user-guide/override_share_restrictions.html) * [Data providers](https://docs.snowflake.com/en/user-guide/data-sharing-core-tasks-providers.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/outbound_share.py) # STAGE Config path: `///stage/.yaml` Example for internal stage: ```yaml file_format: test_csv_format copy_options: on_error: ABORT_STATEMENT enforce_length: true ``` Example of external stage: ```yaml url: "gcs://test-bucket" storage_integration: test_storage_integration file_format: test_avro_format ``` ## Schema * **url** (str) - bucket URL for external stage * **storage\_integration** (ident) - name of [storage integration](/guides/other-guides/integrations) for external stage * **encryption** (dict) * *{key}* (ident) - name of encryption parameter * *{value}* (bool, float, int, str) - value of encryption parameter * **directory** (dict) * *{key}* (ident) - name of directory parameter * *{value}* (bool, float, int, str) - value of directory parameter * **file\_format** (ident) - [file format](/basic/yaml-configs/file-format) for files in stage * **copy\_options** (dict) * *{key}* (ident) - COPY option name * *{value}* (bool, float, int, list, str) - COPY option value * **comment** (str) ## Usage notes 1. Stages with **url** and **storage\_integration** are EXTERNAL. Stages without these parameters are INTERNAL. 2. When INTERNAL stage is being dropped, all files will be lost. When EXTERNAL stage is being dropped, nothing happens. EXTERNAL stage is just a metadata pointing to location in bucket. 3. Business users should only access buckets through stages. Business users should never use `STORAGE INTEGRATION` objects directly for security reasons. 4. SnowDDL is able to maintain not only stages in general, but also [specific files in stages](/basic/yaml-configs/stage-file). 5. **file\_format** can only be specified by name referencing to `FILE_FORMAT` object. ## Additional privileges In order for `STAGE` objects to operate properly, the following additional grants should be added to OWNER role in [schema config](/basic/yaml-configs/schema): * `owner_integration_usage` - please specify names of STORAGE INTEGRATION objects used by external stages. Stages may work without explicit INTEGRATION USAGE grant to OWNER role, but it is not guaranteed for Snowflake keep it this way forever. ## Encryption updates Encryption on existing EXTERNAL stages cannot be updated by SnowDDL. Snowflake does not provide meta-data, so it is not possible to compare config with existing object. In order to update encryption information manually, you should use an additional CLI argument: `--refresh-stage-encryption` If you want to remove an encryption from EXTERNAL stage, you should set encryption type to `NONE` explicitly. It is not enough to remove `encryption` section from config, since default encryption settings are different for each cloud provider and may change in future. ## Safe & unsafe operations All operations on stages are considered as "safe", EXCEPT: * Replace existing stage due to change of stage type (INTERNAL to EXTERNAL or vice versa) or due to change of INTERNAL stage encryption method. * Drop existing stage, both INTERNAL and EXTERNAL. "Unsafe" operations on stages lead to loss of data or meta-data. Also, it may affect other objects depending on stages, like [PIPES](/basic/yaml-configs/pipe) or [EXTERNAL TABLES](/basic/yaml-configs/external-table). ## Links * [CREATE STAGE](https://docs.snowflake.com/en/sql-reference/sql/create-stage.html) * [ALTER STAGE](https://docs.snowflake.com/en/sql-reference/sql/alter-stage.html) * [SHOW STAGES](https://docs.snowflake.com/en/sql-reference/sql/show-stages.html) * [DESC STAGE](https://docs.snowflake.com/en/sql-reference/sql/desc-stage.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/stage.py) # STAGE FILE Stage files are used primarily to maintain packages (e.g. JAR files) for Snowpark functions. You may put any directories or files in `///stage//*` SnowDDL will synchronise files with internal stage automatically. Example: [GitHub link](https://github.com/littleK0i/SnowDDL/tree/master/snowddl/_config/sample02_01/test_db/test_schema/stage/test_internal_stage) (`test_internal_stage`). ## Usage notes 1. Pre-compiled JAR files for popular libraries can be downloaded directly from [MVN repository](https://mvnrepository.com/). 2. Directory structure is maintained. 3. It is recommended to have multiple small files VS. one large file. Each file is uploaded individually in a separate thread. 4. For each stage file SnowDDL puts an additional empty technical file. It is used to compare local version of file on disk with remote version of file in stage. Technical file is named `..md5`. Unfortunately, it is not possible to use `md5` provided by Snowflake `LIST` command, since files in internal stages seems to be encrypted or modified in some other way. ## Links * [PUT](https://docs.snowflake.com/en/sql-reference/sql/put.html) * [LIST](https://docs.snowflake.com/en/sql-reference/sql/list.html) * [REMOVE](https://docs.snowflake.com/en/sql-reference/sql/remove.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/stage.py) # STREAM Config path: `///stream/.yaml` Example: ```yaml object_type: TABLE object_name: test_table_1 append_only: true show_initial_rows: true ``` ## Schema * **object\_type** (str) - object type for stream (e.g. `TABLE`, `EXTERNAL_TABLE`) * **object\_name** (ident) - object name for stream * **append\_only** (bool) - is stream `APPEND_ONLY` mode * **insert\_only** (bool) - is stream `INSERT_ONLY` mode (for `EXTERNAL_TABLE`) * **show\_initial\_rows** (bool) - return existing rows when stream is consumed for the first time * **comment** (str) ## Usage notes 1. Stream on top of `TABLE`, `EVENT_TABLE` or `VIEW` requires change tracking to be enabled explicitly for this object. 2. SnowDDL detects "stale" streams and suggests REPLACE command. ## Links * [CREATE STREAM](https://docs.snowflake.com/en/sql-reference/sql/create-stream.html) * [ALTER STREAM](https://docs.snowflake.com/en/sql-reference/sql/alter-stream.html) * [SHOW STREAMS](https://docs.snowflake.com/en/sql-reference/sql/show-streams.html) * [DESC STREAM](https://docs.snowflake.com/en/sql-reference/sql/desc-stream.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/stream.py) # TABLE Config path: `///table/.yaml` Example: ```yaml columns: actor_id: NUMBER(10,0) NOT NULL first_name: VARCHAR(45) NOT NULL last_name: VARCHAR(45) NOT NULL last_update: TIMESTAMP_NTZ(3) primary_key: [actor_id] ``` ```yaml columns: ticket_no: type: VARCHAR(13) NOT NULL comment: "Ticket number" flight_id: type: NUMBER(10,0) NOT NULL comment: "Flight ID" boarding_no: type: NUMBER(10,0) NOT NULL comment: "Boarding pass number" seat_no: type: VARCHAR(4) NOT NULL comment: "Seat number" primary_key: [ticket_no, flight_id] unique_keys: - [flight_id, boarding_no] - [flight_id, seat_no] foreign_keys: - columns: [ticket_no, flight_id] ref_table: ticket_flights ref_columns: [ticket_no, flight_id] ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - full [data type](/guides/data-types) with optional "NOT NULL" constraint\ \--- OR --- * *{value}* (dict) * **type** (str) - full [data type](/guides/data-types) with optional "NOT NULL" constraint * **default** (str) - default SQL expression * **default\_sequence** (ident) - sequence used for "auto increment" * **collate** (str) - column [collation](https://docs.snowflake.com/en/sql-reference/collation.html#label-collation-specification) for string comparison * **comment** (str) * **is\_transient** (bool) - make table TRANSIENT * **retention\_time** (int) - data retention time in days * **cluster\_by** (list) * *{items}* (str) - SQL expressions for CLUSTER BY * **change\_tracking** (bool) - enable CHANGE TRACKING * **search\_optimization** (bool) - enable SEARCH OPTIMIZATION on the whole table\ \--- OR --- * **search\_optimization** (dict) - enable SEARCH OPTIMIZATION on specific columns * *{key}* (str) - search optimization method (e.g. EQUALITY, SUBSTRING, GEO); * *{value}* (list) * *{items}* (str) - search optimization targets (column names and VARIANT column paths) * **comment** (str) * **primary\_key** (list) * *{items}* (ident) - column names for PRIMARY KEY constraint * **unique\_keys** (list) * *{items}* (list) * *{items}* (ident) - column names for UNIQUE KEY constraint * **foreign\_keys** (list) * *{items} (dict)* - FOREIGN KEY definitions * **columns** (list) * *{items}* (ident) - column names from current table * **ref\_table** (ident) - reference table * **ref\_columns** (list) * *{items}* (ident) - column names from reference table ## Policy reference parameters * **aggregation\_policy** (dict) * **policy\_name** (ident) - name of [AGGREGATION POLICY](/basic/yaml-configs/aggregation-policy) * **columns** (list) * *{items}* (ident) - optional reference column names defining "entity" * **join\_policy** (dict) * **policy\_name** (ident) - name of [JOIN POLICY](/basic/yaml-configs/join-policy) * **columns** (list) * *{items}* (ident) - optional allowed join keys * **masking\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [MASKING POLICY](/basic/yaml-configs/masking-policy) * **columns** (list) * *{items}* (ident) - reference column names * **projection\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [PROJECTION POLICY](/basic/yaml-configs/projection-policy) * **column** (ident) - reference column name * **row\_access\_policy** (dict) * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy) * **columns** (list) * *{items}* (ident) - reference column names ## Usage notes 1. **Columns** definition has two possible syntax options: a) Short syntax (str) with column **type** definition only.\ b) Full syntax (dict) with **type** definition as well as other properties. 2. Column **type** is a full native Snowflake data type definition, exactly how it appears in output of [DESC TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html) command. Aliases and short forms [are not allowed](/guides/data-types). 3. Column **default** is an SQL expression, not value. `VARCHAR` values should be enclosed in quotes. `TIMESTAMP_*` values should be casted explicitly. 4. Anonymous auto-increment is not supported. All [sequences](/basic/yaml-configs/sequence) must be created explicitly and assigned to `default_sequence` of relevant table columns. It helps to preserve sequence value when table is re-created to apply changes which cannot be applied via `ALTER TABLE`. 5. **is\_transient** and **retention\_time** are inherited from parent [SCHEMA](/basic/yaml-configs/schema) and [DATABASE](/basic/yaml-configs/database) objects if omitted. 6. Currently only **standard column names are supported** for [SEARCH OPTIMIZATION on specific columns](https://docs.snowflake.com/en/sql-reference/sql/alter-table.html#search-optimization-actions-searchoptimizationaction). Paths for VARIANT columns are not supported due to high complexity of parsing the output of `DESC SEARCH OPTIMIZATION ON ...` command. We expect it to be improved in future, once this features leaves the "Public preview" status. ## Safe & unsafe operations The following operations on tables are considered as "safe": * Create a new table from scratch; * Add a new column to the end of existing table; * Change comment on table; * Change comment on specific column; All other operations are "unsafe". Additionally, in order to execute `CREATE OR REPLACE TABLE ... AS SELECT` , the following CLI option must be specified: `--apply-replace-table` ## Links * [CREATE TABLE](https://docs.snowflake.com/en/sql-reference/sql/create-table.html) * [ALTER TABLE](https://docs.snowflake.com/en/sql-reference/sql/alter-table.html) * [ALTER TABLE ... ALTER COLUMN](https://docs.snowflake.com/en/sql-reference/sql/alter-table-column.html) * [SHOW TABLE](https://docs.snowflake.com/en/sql-reference/sql/show-tables.html) * [DESC TABLE](https://docs.snowflake.com/en/sql-reference/sql/desc-table.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/table.py) # TASK Config path: `///task/.yaml` Example: ```yaml body: |- CALL test_procedure_1(1) schedule: 5 MINUTE warehouse: task_wh ``` ```yaml body: |- CALL test_procedure_1(2) after: - test_task_1 warehouse: task_wh ``` ## Schema * **body** (str) - SQL statement to be executed by task * **schedule** (str) - schedule for period running tasks * **after** (list) * *{items}* (ident) - one or more predecessor tasks for the current task * **finalize** (str) **-** name of root task associated with finalizer (this task) * **when** (str) - SQL expression returning `BOOLEAN` * **warehouse** (ident) - warehouse used to execute task * **user\_task\_managed\_initial\_warehouse\_size** (str) - initial warehouse size for serverless task execution * **config** (str) - Specifies a string representation of key value pairs that can be accessed by all tasks in the DAG, must be in JSON format * **allow\_overlapping\_execution** (bool) - allow multiple instances of the task tree to run concurrently * **session\_params** (dict) * *{key}* (ident) - session parameter name * *{value}* (bool, float, int, str) - session parameter value * **user\_task\_timeout\_ms** (int) - time limit on a single run of the task before it times out * **suspend\_task\_after\_num\_failures** (int) - number of consecutive failed task runs after which the current task is suspended automatically * **error\_integration** (ident) - notification integration to monitor task errors * **success\_integration** (ident) - notification integration to monitor task executions * **log\_level** (str) * **task\_auto\_retry\_attempts** (int) * **user\_task\_minimum\_trigger\_interval\_in\_seconds** (int) * **target\_completion\_interval** (str) * **serverless\_task\_min\_statement\_size** (str) * **serverless\_task\_max\_statement\_size** (str) * **comment** (str) ## Usage notes 1. SnowDDL only creates tasks. Tasks are initially suspended. You should execute `ALTER TASK ... RESUME` via different means to enable execution. 2. Tasks should be suspended via `ALTER TASK ... SUSPEND` before they can be altered by SnowDDL. Tasks are not suspended automatically. 3. Task is executed with privileges of task owner, which is `schema_owner` role. It will have full access to all objects in the same schema, but no access to objects in other schemas. This behaviour may improve in future. 4. Notification integration mentioned in **error\_integration** parameter must be additionally specified in [schema](/basic/yaml-configs/schema) parameter **owner\_integration\_usage**. Otherwise schema owner role will not be able to send notifications. ## Additional privileges In order for `TASK` objects to operate properly, the following additional grants should be added to OWNER role in [schema config](/basic/yaml-configs/schema): * `owner_warehouse_usage` - list warehouses used to execute tasks * `owner_integration_usage` - if your tasks require integration objects to operate (e.g. via `PROCEDURE`), add names of integrations here * `owner_account_grants` - Snowflake requires `EXECUTE TASK` privilege to run tasks ## Links * [CREATE TASK](https://docs.snowflake.com/en/sql-reference/sql/create-task.html) * [ALTER TASK](https://docs.snowflake.com/en/sql-reference/sql/alter-task.html) * [SHOW TASKS](https://docs.snowflake.com/en/sql-reference/sql/show-tasks.html) * [Introduction to Tasks](https://docs.snowflake.com/en/user-guide/tasks-intro.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/task.py) # TECHNICAL ROLE Config path: `/technical_role.yaml` Example: ```yaml restricted_bookings: grants: DATABASE:USAGE: - snowddl_db SCHEMA:USAGE: - snowddl_db.bookings VIEW:SELECT: - snowddl_db.bookings.aircrafts - snowddl_db.bookings.airports FUNCTION:USAGE: - snowddl_db.bookings.lang(object) future_grants: TABLE:SELECT,REFERENCES: - snowddl_db.bookings account_grants: - MONITOR EXECUTION comment: "Access to some specific views and functions in Bookings schema" ``` ## Schema * *{key}* (ident) - tech role name * *{value}* (dict) * **grants** (dict) * *{key}* (str) - `:` * *{value}* (list) * *{items}* (ident) - full objects names to grant privilege for existing objects * **future\_grants** (dict) * *{key}* (str) - `:` * *{value}* (list) * *{items}* (ident) - full objects names to grant privilege for existing and future objects * **account\_grants** (list) * {items} (str) - account-level privilege * **comment** (str) ## Usage notes 1. List of possible privileges is available in [Access Control documentation](https://docs.snowflake.com/en/user-guide/security-access-control-privileges.html). 2. Long object types should be specified with underscore (e.g. `EXTERNAL_TABLE`). 3. Object names for grants should be fully qualified:`..`. Functions and procedures should also have data types in parenthesis: `..(,)`. 4. Future grants should be specified as `` for future grant on DATABASE, or as `.` for future grant on SCHEMA. 5. It is possible to specify object names as wildcards (e.g. `.*`). It is helpful for large number of objects with similar names sharing similar access patterns. 6. OWNERSHIP privilege is not allowed for TECHNICAL ROLES. It is controlled by [permission model instead](/basic/yaml-configs/permission-model). ## Links * [CREATE ROLE](https://docs.snowflake.com/en/sql-reference/sql/create-role.html) * [GRANT PRIVILEGE](https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/tech_role.py) # USER Config path: `/user.yaml` Example: ```yaml damian_edwards: password: "password" first_name: "Damian" last_name: "Edwards" email: "damian.edwards@example.com" session_params: query_tag: "Queries from Damian" error_on_nondeterministic_merge: true statement_timeout_in_seconds: 900 business_roles: - sakila_analyst comment: "Analyst with read access to Sakila data and full access to sandbox schema" ``` ```yaml etl_script: rsa_public_key: >- MIIBIjANBgkqhkiG1a0BAQEFAAOCAQ8AMIIBCgKCAQEAx4INStnNQshPamlDe5te +sF/J3zbY9BCMgcl/B11NndFRuXZjKBAyVJyJdjm2XpHGyJZrpIf1kBVJbfxpNSi qN/VLMm1nsqtEnLJsvHWT4AyJ8GG1ahYY34ody9SjLTCisSRpjzh7ZLajbyNtwbH ukOCAhy1R7RzyEmuqz3rRmnx0MUb+1wdSYfMAnVwxT11otmClhXVe3Hj9hdNmljk pw2rezWlKyeywkDpvh00/tuIFdCJD2gWcb3rAUC3e9iR6RJ4o/LFIEBlyktUPOqF d4A3+Wp/pkTiYUh2GvjHTZrGViZXBPRjciP+6ktLMuXP4bW2DeS1xEYIUeYhxaNI IwIDAQAB business_roles: - etl_script ``` ## Schema * *{key}* (ident) - user name * *{value}* (dict) * **login\_name** (str) * **display\_name** (str) * **first\_name** (str) * **last\_name** (str) * **email** (str) * **disabled** (bool) * **type** (str) - user type for security: PERSON, SERVICE, LEGACY\_SERVICE, etc. * **password** (str) * **rsa\_public\_key** (str) * **rsa\_public\_key\_2** (str) * **default\_warehouse** (ident) * **default\_namespace** (str) * **session\_params** (dict) * *{key}* (ident) - session param name * *{value}* (bool, float, int, str) - session param value * **workload\_identity** (dict) * *{key}* (ident) - workload identity param name * *{value}* (array, bool, float, int, str) - workload identity param value * **business\_roles** (list) * *{items}* (ident) - names of business roles * **comment** (str) ## Policy reference parameters * **authentication\_policy** (ident) - assign [AUTHENTICATION POLICY](/basic/yaml-configs/authentication-policy) to USER * **network\_policy** (ident) - assign [NETWORK POLICY](/basic/yaml-configs/network-policy) to USER ## Usage notes 1. `password` is stored as plain text, which can be [encrypted with fernet](/guides/other-guides/encrypt-user-passwords); 2. Changes in `password` will NOT be applied automatically due to lack of ability to compare current password in config with existing password stored in Snowflake metadata. Please use `--refresh-user-passwords` argument to refresh passwords for all existing users, if necessary. 3. `rsa_public_key` should be passed [without public key delimiters](https://docs.snowflake.com/en/user-guide/key-pair-auth.html#step-4-assign-the-public-key-to-a-snowflake-user). 4. If `default_warehouse` is omitted, it will be derived automatically from first warehouse mentioned in `business_roles -> warehouse_usage`. 5. `default_role` cannot be changed. SnowDDL automatically creates USER ROLE and sets it as `default_role`. 6. `default_secondary_roles` are not supported on purpose. You should never need secondary roles with [role hierarchy](/guides/role-hierarchy) provided by SnowDDL. 7. `middle_name` is not supported due to lack of this column in `SHOW USERS` output. 8. As of December 2025, Snowflake has a bug related to changing authentication policy attached to user. SnowDDL generates correct SQL, but sometimes user may end up without authentication policy after its execution. You should report this bug to Snowflake and run SnowDDL again to make sure the new policy is being correctly attached. ### Workload Identity Usage Notes 1. Changes in `workload_identity` will NOT be applied automatically due to lack of ability to compare current values in config with existing values stored in Snowflake metadata. Please use `--refresh-workload-identity` argument to refresh passwords for all existing users, if necessary. 2. Snowflake uses combination of `ISSUER` and `SUBJECT` fields as unique key. It is not possible to create more than one user with the same values. It causes problems for [env prefix](/guides/other-guides/env-prefix). In order to mitigate these problems, SnowDDL adds env prefix value to the end of `SUBJECT` , like this: `:ENV_PREFIX`. ## Links * [CREATE USER](https://docs.snowflake.com/en/sql-reference/sql/create-user.html) * [ALTER USER](https://docs.snowflake.com/en/sql-reference/sql/alter-user.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/user.py) # VIEW Config path: `///view/.yaml` Example: ```yaml columns: aircraft_code: "Aircraft code, IATA" model: "Aircraft model" range: "Maximal flying distance, km" text: |- SELECT ml.aircraft_code, lang(ml.model) AS model, ml.range FROM aircrafts_data ml comment: >- Each aircraft model is identified by its three-digit code (aircraft_code). The view also includes the name of the aircraft model (model) and the maximal flying distance, in kilometers (range). ``` ## Schema * **columns** (dict) * *{key}* (ident) - column name * *{value}* (str) - column comment * **text** (str) - view text * **is\_secure** (bool) - is view secure * **change\_tracking** (bool) - enable change tracking for this VIEW and underlying tables * **depends\_on** (list) * *{items}* (ident) - names of other views which this view depends on * **comment** (str) ## Policy reference parameters * **aggregation\_policy** (dict) * **policy\_name** (ident) - name of [AGGREGATION POLICY](/basic/yaml-configs/aggregation-policy) * **columns** (list) * *{items}* (ident) - optional reference column names defining "entity" * **join\_policy** (dict) * **policy\_name** (ident) - name of [JOIN POLICY](/basic/yaml-configs/join-policy) * **columns** (list) * *{items}* (ident) - optional allowed join keys * **masking\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [MASKING POLICY](/basic/yaml-configs/masking-policy) * **columns** (list) * *{items}* (ident) - reference column names * **projection\_policies** (list) * *{items}* (dict) * **policy\_name** (ident) - name of [PROJECTION POLICY](/basic/yaml-configs/projection-policy) * **column** (ident) - reference column name * **row\_access\_policy** (dict) * **policy\_name** (ident) - name of [ROW ACCESS POLICY](/basic/yaml-configs/row-access-policy) * **columns** (list) * *{items}* (ident) - reference column names ## Usage notes 1. Invalid views will be re-created automatically, even if view definition remains exactly the same. 2. If you want to access objects from another database in VIEW definition, and if you want to preserve [env prefix](/guides/other-guides/env-prefix) support for such views, please use the `env_prefix` [placeholder](/basic/yaml-placeholders).\ \ For example: `${{ env_prefix }}db_name.schema_name.object_name`.\ \ You may access objects in the same database by omitting database name altogether. 3. You may use [custom YAML tag](/basic/yaml-tag-include) `!include` to store view SQL text in a separate file instead of storing it inside YAML. ## Links * [CREATE VIEW](https://docs.snowflake.com/en/sql-reference/sql/create-view.html) * [SHOW VIEWS](https://docs.snowflake.com/en/sql-reference/sql/show-views.html) * [DESC VIEW](https://docs.snowflake.com/en/sql-reference/sql/desc-view.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/view.py) # WAREHOUSE Config path: `/warehouse.yaml` Example: ```yaml task_wh: size: XSMALL auto_suspend: 60 ``` ```yaml multi_custer_wh: size: SMALL generation: 2 min_cluster_count: 1 max_cluster_count: 4 auto_suspend: 60 ``` ## Schema * *{key} (ident) -* warehouse name * {value} (dict) * **size** (str) - warehouse size * **type** (str) - warehouse [type](https://docs.snowflake.com/en/user-guide/warehouses.html) * **generation** (str) - (default: "1") * **min\_cluster\_count** (int) - (default: 1) * **max\_cluster\_count** (int) - (default: 1) * **scaling\_policy** (str) - (default: STANDARD) * **auto\_suspend** (int) - number of idle seconds before warehouse is automatically suspended (default: 60) * **resource\_monitor** (ident) - name of resource monitor managed by [SnowDDL config](/basic/yaml-configs/resource-monitor) * **global\_resource\_monitor** (ident) - name of resource monitor created externally * **enable\_query\_acceleration** (bool) - enable [query acceleration service](https://docs.snowflake.com/en/user-guide/query-acceleration-service.html) * **query\_acceleration\_max\_scale\_factor** (int) - (default: 8) * **resource\_constraint** (str) - (default `MEMORY_X16` for Snowpark-optimized warehouses) * **warehouse\_params** (dict) * *{key}* (ident) - warehouse param name * *{value}* (bool, float, int, str) - warehouse param value * **comment** (str) ## Usage notes 1. Values for `size` are available in [CREATE WAREHOUSE](https://docs.snowflake.com/en/sql-reference/sql/create-warehouse.html#optional-properties-objectproperties) documentation. 2. Scaling policy and multi-cluster warehouses require [Enterprise Edition or higher](https://docs.snowflake.com/en/user-guide/intro-editions.html). 3. `resource_monitor` can only be applied by user with `ACCOUNTADMIN` role. 4. All warehouses are created with `INITIALLY_SUSPENDED` and `AUTO_RESUME` enabled. 5. When creating Snowpark-optimized warehouse, please pay attention to `resource_constraint` values depending on warehouse size. Default is `MEMORY_16X`, which requires at least `MEDIUM` warehouse size. For small warehouse sizes you should set `MEMORY_1X` or similar constraint explicitly. ## Links * [CREATE WAREHOUSE](https://docs.snowflake.com/en/sql-reference/sql/create-warehouse.html) * [ALTER WAREHOUSE](https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse.html) * [SHOW WAREHOUSES](https://docs.snowflake.com/en/sql-reference/sql/show-warehouses.html) * [Parser & JSON Schema (GitHub)](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/parser/warehouse.py) # YAML placeholders SnowDDL supports placeholders in YAML configs. Placeholder format is similar to GitHub Actions: ```yaml ${{ }} ``` Examples: ```yaml my_warehouse: size: ${{ wh_size }} auto_suspend: ${{ wh_auto_suspend }} ``` ```yaml url: s3://${{ bucket_name }}/my_path/ storage_integration: test_storage_integration file_format: test_avro_format ``` ## Configuration Placeholders should be defined in config path: `/placeholder.yaml` Example: ```yaml wh_size: SMALL wh_auto_suspend: 300 bucket_name: dev-test-bucket ``` Additionally, you may specify a path to file with environment-specific placeholder values. For example, you may use it to override `bucket_name` from `dev-test-bucket` in DEV environment to `prod-test-bucket` in PROD environment. ```yaml bucket_name: prod-test-bucket ``` CLI option is `--placeholder-path` : ``` snowddl \ -c \ -a \ -u \ -p \ --placeholder-path= apply ``` ## Placeholders from command line argument Alternatively, it is possible to specify custom placeholder values using CLI argument `--placeholder-values`. This argument accepts JSON string. For example: ``` snowddl \ -c \ -a \ -u \ -p \ --placeholder-values="{\"bucket_name\": \"prod-test-bucket\"}" apply ``` Data types of JSON values are important! For example, placeholders for BOOLEAN values should be specified as JSON `true` or `false` without quotes. Numeric values should not have quotes as well. ## Technical placeholders A small number of placeholders are created automatically and always available. These placeholders can still be overloaded by config if necessary. * **env\_prefix** (str) - contains current [env prefix](/guides/other-guides/env-prefix) value. For example, if env prefix is `ALICE`, this placeholder will contain `ALICE__`. It should be used as a part of raw SQL fragments when it is necessary to access object in another database. It is especially useful for [VIEW](/basic/yaml-configs/view) definitions.\ \ For example: `${{ env_prefix }}db_name.schema_name.object_name`.
* **target\_db** (str) - contains target database name in [SingleDB mode](/single-db/overview), including env prefix. Requires `--target-db` CLI argument to be set explicitly. ## Usage notes 1. Data types of placeholder values are preserved. You may use integers, booleans, floats, etc. 2. All placeholders should be defined explicitly. Undefined placeholders will raise an exception and stop further execution to prevent an accidental damage. # YAML tag !include SnowDDL supports custom `!include` tag in YAML configs. When parser encounters this tag, it loads specified file and uses its contents as value for YAML parameter. It helps to store SQL or UDF code into separate files instead of putting everything inside YAML, which leads to significant improvement of developer experience by providing syntax highlighting and auto-completion. It is primarily intended to be used with [VIEW](/basic/yaml-configs/view) parameter `text` and [FUNCTION](/basic/yaml-configs/function) & [PROCEDURE](/basic/yaml-configs/procedure) parameter `body`. ## Example **my\_view\.yaml** config file: ```yaml text: !include my_view.sql ``` **my\_view\.sql** file: ```sql SELECT id, name FROM my_table ``` ## Usage notes * Include paths are relative to source YAML file. It is possible to "include" from the same directory or from any sub-directory relative to YAML file. Examples of valid paths: `my_view.sql`, `sql/my_view_sql`. * Loading files from parent directory or using absolute paths are not allowed due to security concerns. * `!include` can be used with files of any type, including `*.py`, `*.java`, `*.sql`, etc. * [YAML placeholders](/basic/yaml-placeholders) in included files are correctly resolved. # YAML tag !decrypt SnowDDL supports custom `!decrypt` tag in YAML configs. When parser encounters this tag, it decrypts string previously encrypted with [Fernet](https://cryptography.io/en/latest/fernet/) using keys stored in env variable `SNOWFLAKE_CONFIG_FERNET_KEYS`. It is primarily intended to be used for USER passwords and for SECRETs. You may read more about config value encryption in [Encrypt user passwords](/guides/other-guides/encrypt-user-passwords) guide. ## Example **user.yaml** config file: ```yaml john_doe: first_name: John last_name: Doe password: !decrypt gAAAAABmlTWBmENR0wIG2naG3PW8B3Li-9tw2UQAb9yB22V_R-SEEq6Vli5m9-w5_tI3jlftIGxlXxPhsLNMngxnjG6XdySHpQ== ``` **my\_secret.yaml** config file: ```yaml type: generic_string secret_string: !decrypt gAAAAABmlTXPlpJotMpEkuH44ptENk_9Uh1k8mqLPdP2Bi_0dJyk1Qdjr36Cq2mvhRpxRRIvuh8X3zYC--sBveOWPEjmKtb2UQ== ``` # Overview SingleDB is a simplified version of SnowDDL. It uses the same config structure, but it resolves schemas and schema objects in a **single database only**. Account-level objects, roles, grants, warehouses, users are NOT resolved. It is very useful when your organization has a lot of pre-existing automation for Snowflake, and it is not feasible to move everything to SnowDDL. But it is still beneficial to use SnowDDL to manage schema objects in some isolated and specific use cases. ## Administration user Naturally, this mode does NOT require `SYSADMIN` and `SECURITYADMIN` privileges. Only the following grants are required: ```sql GRANT USAGE ON DATABASE TO ROLE ; GRANT CREATE SCHEMA ON DATABASE TO ROLE ; GRANT OWNERSHIP ON FUTURE SCHEMAS IN DATABASE TO ROLE ; -- Repeat for every object type you need (TABLES, VIEWS, etc.) GRANT OWNERSHIP ON FUTURE IN DATABASE TO ROLE ; -- Apply only if you want to ALTER tables via SnowDDL GRANT USAGE,OPERATE ON WAREHOUSE TO ROLE ; ``` All grants should be created manually or by using other tools. ## CLI interface SingleDB uses a separate entry-point: `snowddl-singledb`. It has slightly different arguments: ``` usage: snowddl-singledb [-h] [-c CONFIG_PATH] [-a ACCOUNT] [-u USER] [-p PASSWORD] [-k PRIVATE_KEY] [-r ROLE] [-w WAREHOUSE] [--config-db CONFIG_DB] [--target-db TARGET_DB] [--passphrase PASSPHRASE] [--env-prefix ENV_PREFIX] [--max-workers MAX_WORKERS] [--log-level LOG_LEVEL] [--show-sql] [--placeholder-path] [--placeholder-values] [--exclude-object-types] [--include-object-types] [--apply-unsafe] [--apply-replace-table] [--apply-masking-policy] [--apply-row-access-policy] {plan,apply,destroy} ... Special SnowDDL mode to process schema objects of single database only positional arguments: {plan,apply,destroy} plan Resolve objects, apply nothing, display suggested changes apply Resolve objects, apply safe changes, display suggested unsafe changes destroy Drop objects with specified --env-prefix, use it to reset dev and test environments optional arguments: -h, --help show this help message and exit -c CONFIG_PATH Path to config directory OR name of bundled test config (default: current directory) -a ACCOUNT Snowflake account identifier (default: SNOWFLAKE_ACCOUNT env variable) -u USER Snowflake user name (default: SNOWFLAKE_USER env variable) -p PASSWORD Snowflake user password (default: SNOWFLAKE_PASSWORD env variable) -k PRIVATE_KEY Path to private key file (default: SNOWFLAKE_PRIVATE_KEY_PATH env variable) -r ROLE Snowflake active role (default: SNOWFLAKE_ROLE env variable) -w WAREHOUSE Snowflake active warehouse (default: SNOWFLAKE_WAREHOUSE env variable) --config-db CONFIG_DB Source database name in config (default: detected automatically if only one database is present in config) --target-db TARGET_DB Target database name in Snowflake account (default: same as --config-db) --authenticator AUTHENTICATOR Authenticator: 'snowflake' or 'externalbrowser' (to use any IdP and a web browser) (default: SNOWFLAKE_AUTHENTICATOR env variable or 'snowflake') --passphrase PASSPHRASE Passphrase for private key file (default: SNOWFLAKE_PRIVATE_KEY_PASSPHRASE env variable) --env-prefix ENV_PREFIX Env prefix added to global object names, used to separate environments (e.g. DEV, PROD) --max-workers MAX_WORKERS Maximum number of workers to resolve objects in parallel --log-level LOG_LEVEL Log level (possible values: DEBUG, INFO, WARNING; default: INFO) --show-sql Show executed DDL queries --show-timers Show debug timers --show-unused-files Show warnings for unused config files --placeholder-path Path to config file with environment-specific placeholders --placeholder-values Environment-specific placeholder values in JSON format --exclude-object-types Comma-separated list of object types NOT to resolve --include-object-types Comma-separated list of object types TO resolve, all other types are excluded --apply-unsafe Additionally apply unsafe changes, which may cause loss of data (ALTER, DROP, etc.) --apply-replace-table Additionally apply REPLACE TABLE when ALTER TABLE is not possible --apply-all-policy Additionally apply changes to all types of POLICIES --apply-aggregation-policy Additionally apply changes to AGGREGATION POLICIES --apply-masking-policy Additionally apply changes to MASKING POLICIES --apply-projection-policy Additionally apply changes to PROJECTION POLICIES --apply-row-access-policy Additionally apply changes to ROW ACCESS POLICIES --refresh-stage-encryption Additionally refresh stage encryption parameters for existing external stages --refresh-secrets Additionally refresh secrets --clone-table Clone all tables from source databases to destination databases (with env_prefix) --clone-source-env-prefix Clone from another environment with different env_prefix ``` ### Usage notes * Argument `--config-db` is the name of specific source database in config. If you have only one database in config, this argument can be omitted. Otherwise, it is required to prevent accidental mistakes. * Argument `--target-db` is the name of target database in Snowflake account to compare with `--config-db` and apply changes. It is the same as `--config-db` by default. * Target DB should be created manually or by using other tools. SingleDB mode does not create databases, it can only use an existing pre-configured database. * Argument `--destroy-without-prefix` does not exist in SingleDB mode. Destroy action is allowed without prefix, since the potential damage is low and limited to a single database. # Programmatic config It is possible to extend and modify SnowDDL [**config**](/advanced/architecture-overview/config) programmatically using pure Python. A few examples of real business use cases which can be implemented with this technique: * Get list of users dynamically from single sign-on data provider; * Generate a view for each table in specific schemas; * Generate masking policy for each table containing columns named "email" and "phone"; * Skip certain types of objects in DEV environment; There are not restrictions. Any external data source and any Python package can be used. ### Implementation steps 1. Create a standard directory with [YAML config](/basic/yaml-configs). You may optionally fill it with YAML files. 2. Create a sub-directory with name `__custom` (starting with two underscores) in config directory. 3. Place one or more python modules (`.py` files) in `__custom` sub-directory. During SnowDDL execution YAML configs are resolved first. After that Python modules are resolved one-by-one in alphanumeric order. It is highly recommended to start module names with zero-padded numbers to make sure you have a precise control of resolution order, for example: `01_foo.py`, `02_bar.py`, `03_baz.py`. ### Module requirements * Each module should have a function with name `handler`, which accepts instance of [`SnowDDLConfig`](/advanced/architecture-overview/config) as a single argument. This function does not return anything. * In `handler` function you may build [**blueprint**](/advanced/architecture-overview/blueprints) objects representing the desired state of objects in Snowflake, and use [**config**](/advanced/architecture-overview/config) methods `.add_blueprint()` and `.remove_blueprint()` to manipulate the collection of blueprints. * You may access existing [**blueprints**](/advanced/architecture-overview/blueprints) using methods `.get_blueprints_by_type()` and `.get_blueprints_by_type_and_pattern()`. ### Examples * Complete example of config with `__custom` sub-directory: * Example of Python module adding a few custom tables: ```python from snowddl import DataType, Ident, TableBlueprint, TableColumn, SchemaObjectIdent, SnowDDLConfig def handler(config: SnowDDLConfig): # Add custom tables for i in range(1, 5): bp = TableBlueprint( full_name=SchemaObjectIdent(config.env_prefix, "test_db", "test_schema", f"custom_table_{i}"), columns=[ TableColumn( name=Ident("id"), type=DataType("NUMBER(38,0)"), ), TableColumn( name=Ident("name"), type=DataType("VARCHAR(255)"), ), ], is_transient=True, comment="This table was created programmatically", ) config.add_blueprint(bp) ``` * Example of Python module which scans current config for custom tables and generates a consolidated view dynamically: ```python from snowddl import SchemaObjectIdent, SnowDDLConfig, TableBlueprint, ViewBlueprint def handler(config: SnowDDLConfig): # Add view combining all custom tables parts = [] for full_name, bp in config.get_blueprints_by_type_and_pattern(TableBlueprint, "test_db.test_schema.custom_table_*").items(): parts.append(f"SELECT id, name FROM {full_name}") bp = ViewBlueprint( full_name=SchemaObjectIdent(config.env_prefix, "test_db", "test_schema", "custom_view"), text="\nUNION ALL\n".join(parts), comment="This view was created programmatically", ) config.add_blueprint(bp) ``` # Architecture overview ### Components SnowDDL consists of the following components: * [**Blueprint**](/advanced/architecture-overview/blueprints) - pydantic model representing the desired state of an object in Snowflake account; * [**Config**](/advanced/architecture-overview/config) - collection of blueprints; * [**Parsers**](/advanced/architecture-overview/parsers) - used to parse YAML config files into blueprints, one parser class per object type; * [**Resolvers**](/advanced/architecture-overview/resolvers) - used to compare blueprints with existing metadata in Snowflake account and generate DDL commands to apply changes, one resolver class per object type; * [**Engine**](/advanced/architecture-overview/engine) - initialized with Snowflake connection and SnowDDL config, used to build, format and execute commands by resolvers. ### Workflow These components are combined into "applications" with the following workflow: 1. Initialize an empty **config**. 2. Generate individual **blueprints** using **parsers** or custom Python code, add blueprints to **config**. 3. Validate **config**. If encountered any errors, display error messages and stop execution. 4. Open connection to Snowflake using [Python connector](https://docs.snowflake.com/en/user-guide/python-connector.html). 5. Initialize **engine** using Snowflake connection and **config**. 6. Execute **resolvers** to generate DDL commands. Suggest or apply DDL commands, depending on settings. 7. Display statistics, close Snowflake connection and finish the application. Default application used by [CLI interface](/basic/cli) is available on GitHub: [`base.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/app/base.py) You may extend the default application to build even more sophisticated automation. For example, [Single DB mode](/single-db/overview) is an extension of default application, which is available in [`singledb.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/app/singledb.py). ## # Blueprints **Blueprints** are [Pydantic V2 models](https://docs.pydantic.dev/latest/concepts/models/) representing the desired state of objects in Snowflake account. All standard blueprints are located in [`/blueprint/blueprint.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/blueprint/blueprint.py). For example: ```python class TableBlueprint(SchemaObjectBlueprint): columns: List[TableColumn] cluster_by: Optional[List[str]] = None is_transient: bool = False retention_time: Optional[int] = None change_tracking: bool = False search_optimization: Union[bool, List[SearchOptimizationItem]] = False ``` ### Inheritance All blueprints are derived from `AbstractBlueprint` class. Blueprints of schema objects (`TABLE`, `VIEW`, etc.) are derived from `SchemaObjectBlueprint`. Blueprints of objects supporting dependency management within the same object type are derived from additional `DependsOnMixin`. ### Identifiers Blueprints always use special objects called "identifiers" to describe unique object names in Snowflake. Identifiers are stored in [`/blueprint/ident.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/blueprint/ident.py). The following types of identifiers are currently available: * **Ident** - basic identifier with no additional features, normally used for column names; * **AccountObjectIdent** - basic identifier which supports [env prefix](/guides/other-guides/env-prefix), used for account-level object names like `ROLE`, `WAREHOUSE`, `USER`. * **DatabaseIdent** - identifier used specifically for `DATABASE` object type. * **SchemaIdent** - identifier used specifically for `SCHEMA` object type. * **SchemaObjectIdent** - complex identifier with multiple parts separated by `.` (dots), represents fully qualified name of schema-level objects like `TABLE`, `VIEW`. * **SchemaObjectIdentWithArgs** - complex identifier with additional data types of arguments, represents fully qualified name of `FUNCTION`, `PROCEDURE` and other object types supporting [overloading](https://docs.snowflake.com/en/sql-reference/udf-overview.html#overloading-of-udf-names) of names. * **StageFileIdent** - complex identifier with additional path, represents fully qualified name for [`STAGE FILE`](/basic/yaml-configs/stage-file) special object type. * **TableConstraintIdent** - complex identifier with additional list of columns, represents fully qualified name for table constraints, such as `PRIMARY KEY`, `UNIQUE KEY`, `FOREIGN KEY`. It is very important to use the right type of identifier depending on specific use case. Identifier object is the core feature which makes it possible for [env prefix](/guides/other-guides/env-prefix) to work correctly. # Config **Config** is a collection of **blueprints**. Config is represented by class `SnowDDLConfig`, which is located in [`config.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/config.py). ### Methods * `__init__(env_prefix=None)` \ Initialize config with optional [env prefix](/guides/other-guides/env-prefix), which should be applied to all blueprints.
* `get_blueprints_by_type(cls: Type[T_Blueprint]) -> Dict[str,T_Blueprint]`\ Accepts blueprint type (class). Returns all blueprints of this type.
* `get_blueprints_by_type_and_pattern(cls: Type[T_Blueprint], pattern: IdentPattern) -> Dict[str,T_Blueprint])`\ Accepts blueprint type (class) and [Unix-style pattern](https://docs.python.org/3/library/fnmatch.html). Returns all blueprints of this type with full names matching pattern. Example of pattern: `db1.sc1.my_tables_*`
* `add_blueprint(bp: AbstractBlueprint)`\ Accept instance of blueprint. Add this blueprint to collection. If blueprint of this type with the same `full_name` already exists, it will be replaced.
* `remove_blueprint(bp: AbstractBlueprint)`\ Accepts instance of blueprint. Removes this blueprint from collection. Throws `ValueError` exception if blueprint does not exist in config.
### Properties * **.env\_prefix** (str) - normalized [env prefix](/guides/other-guides/env-prefix), upper cased and with `__` at the end. It should be used to build object identifiers. * **.blueprints** (dict) - blueprints in collection. * *{key}* (type) - blueprint type (class). * *{value}* (dict) * *{key}* (str) - full name of object, stored as string. * *{value}* (blueprint) - blueprint dataclass object. * **.placeholders** (dict) - [placeholders](/basic/yaml-placeholders) used by **parsers** during processing of YAML configs. * *{key}* (str) - name of placeholder. * *{value}* (bool, float, int, str) - value of placeholder. # Parsers **Parsers** are used to process [YAML config files](/basic/yaml-configs) into **blueprints**, one parser per object type. All standard parsers are located in [`/parser/`](https://github.com/littleK0i/SnowDDL/tree/master/snowddl/parser) directory. ### Inheritance All parsers are derived from `AbstractParser` class. ### JSON schema SnowDDL uses [jsonschema](https://github.com/Julian/jsonschema) Python library to validate YAML configs. JSON Schema for each object type is stored in the same file as parser for your convenience. ### Methods * `__init__(config: SnowDDLConfig, base_path: Path)`\ Initialize **parser** with [**config**](/advanced/architecture-overview/config) and Path object containing path to config directory.
* `load_blueprints()`\ Abstract method, it should be implemented by each parser class. Normally it reads YAML files, builds **blueprints** and adds **blueprints** to **config**.
* `parse_single_file(path: Path, json_schema: dict, callback: Callable = None)`\ Accepts path to YAML file, JSON schema definition and optional callback function. Parses YAML file, validates it with provided JSON schema, process it using callback function.\ \ If any `Exception` is being raised inside callback function, it is treated as config validation error, "muted" and added to `.errors` property of [**config**](/advanced/architecture-overview/config).\ \ If callback function was not defined, it returns processed YAML file as basic Python dict.
* `parse_schema_object_files(object_type: str, json_schema: dict, callback: Callable)`\ Accepts name of object type (as string), JSON schema definition, mandatory callback function. Finds and parses all files of specified object type, validate each file with provided JSON schema, process each file with callback function.\ \ If any `Exception` is being raised inside callback function, it is treated as config validation error, "muted" and added to `.errors` property of [**config**](/advanced/architecture-overview/config). # Resolvers **Resolvers** are used to compare **blueprints** with existing metadata in Snowflake account and generate DDL commands. DDL commands are suggested or immediately applied using **engine**, depending on settings. One resolver per object type. All standard resolvers are located in [`/resolver/`](https://github.com/littleK0i/SnowDDL/tree/master/snowddl/resolver) directory. ### Inheritance All **resolvers** are derived from `AbstractResolver` class. Resolvers for schema objects (`TABLE`, `VIEW`, etc.) are derived from `AbstractSchemaObjectResolver`, which implements additional logic related to "sandbox" schemas and parallel metadata fetching. ### Resolver workflow Internally each resolver implements the following workflow: 1. Get object **blueprints** (desired state); 2. Load existing objects from Snowflake metadata (current state); 3. Compare full names of **blueprints** VS full names of existing objects and.. * "create" new objects; * "compare" existing objects; * "drop" existing objects without blueprints; 4. Execute "create" / "compare" / "drop" operations in parallel using [ThreadPoolExecutor](https://docs.python.org/3/library/concurrent.futures.html#concurrent.futures.ThreadPoolExecutor). 5. Update caches (if necessary). ### Resolve result Each object may "resolve" in one of the following ways: * **CREATE** - object was created, it did not exist before; * **ALTER** - existing object was updated; * **DROP** - existing object was dropped; * **REPLACE** - existing object was replaced entirely; * **SKIP** - object was not changed, it was skipped; * **GRANT** - grants were updated (used for various types of ROLES); * **NOCHANGE** - object was not changed, did not require any change; * **ERROR** - something went wrong while resolving this object, check logs; * **UNSUPPORTED** - object should be updated, but it is not possible due to lack of Snowflake support for such operation (e.g. converting TRANSIENT schema to normal schema is not possible without full data rewrite); Resolve result by object name is available in property `.resolved_objects`. Intercepted exceptions by object name are available in property `.errors`. ### Methods (base) * `__init__(engine: SnowDDLEngine)`\ Initialize resolver with **engine**.
* `get_object_type()`\ Abstract method. Returns object type, which is processed by this resolver.
* `get_blueprints()`\ Abstract method. Returns **blueprints** to be processed by resolver. Normally it reads blueprints from **config**, but it may also generate blueprints on the fly based on some other blueprints. For example, "schema roles" are generated automatically based on schema blueprints.
* `get_existing_objects()`\ Abstract method. Returns dict with objects currently existing in Snowflake account. Normally this method calls for `SHOW ...` metadata commands.
* `create_object(self, bp: AbstractBlueprint)`\ Abstract method. Accepts instance of blueprint. Creates a new object which currently does not exist in Snowflake.
* `compare_object(self, bp: AbstractBlueprint, row: Dict)`\ Abstract method. Accepts blueprint and metadata of existing object. Compares blueprint with existing object and updates or recreates it. Alternatively, it "does nothing" (skip) if object blueprint matches the existing metadata precisely.
* `drop_object(self, row: Dict)`\ Abstract method. Accepts metadata of existing object, which does not have a corresponding blueprint. Drops this object. ### Methods (schema objects) * `get_existing_objects_in_schema(schema: dict)`\ Abstract method. Use it instead of `get_existing_objects()`. Accepts dict describing schema. Returns dict in the same format as `get_existing_objects()`. ### Properties * **.resolved\_objects** (dict) - resolve result for processed objects; * *{key}* (str) - full name of object; * *{value}* (ResolveResult) - enum value; * **.errors** (dict) - exceptions for processed objects; * *{key}* (str) - full name of object; * *{value}* (Exception) - exception thrown while processing object; # Engine **Engine** is initialized with Snowflake connection and **config**. It is used to build, format and execute DDL commands by **resolvers**. Engine is represented by class `SnowDDLEngine`, which is located in [`engine.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/engine.py). One **engine** is expected to be reused by multiple **resolvers** executed in the correct order. ### Engine settings Settings are represented by class `SnowDDLSettings`. You may find the most recent version of settings in [`settings.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/settings.py). Settings starting with `execute_` control the execution behaviour. If value is `True`, DDL commands of this type will be executed. If value is `False`, commands will be "suggested" (dumped for manual execution) instead. ### Engine context Engine collects information about Snowflake connection context during initialization. You should check which context properties are available in [`context.py`](https://github.com/littleK0i/SnowDDL/blob/master/snowddl/context.py). ### Methods * `__init__(connection: SnowflakeConnection, config: SnowDDLConfig, settings: SnowDDLSettings)`\ Initialize **engine** with Snowflake connection, **config** and settings.
* `format(sql, params=None)`\ Formats SQL query using SnowDDL [formatter](/advanced/query-builder-and-formatter).
* `query_builder()`\ Create sand returns new [query builder](/advanced/query-builder-and-formatter).
* `describe_meta(sql, params=None)`\ Describes SQL query, compiles it without actually executing and returns information about result columns. If SQL query cannot be compiled, throws an exception.
* `execute_meta(sql, params=None)`\ Executes SQL query returning metadata information, usually `SHOW` commands.
* `execute_context_ddl(sql, params=None)`\ Executes DDL command which is used to initialize the current context. It is normally used to create technical role for [env prefix](/guides/other-guides/env-prefix) feature.
* `execute_safe_ddl(sql, params=None, condition=True)`\ Executes DDL command which is classified as ["safe"](/guides/other-guides/safe-unsafe), usually `CREATE`. Optionally checks the expression in `condition` argument and executes command only if expression is `True`. Otherwise, it only "suggests" DDL command.
* `execute_unsafe_ddl(sql, params=None, condition=True)`\ Executes DDL command which is classified as ["unsafe"](/guides/other-guides/safe-unsafe), usually `ALTER`, `DROP`. Optionally checks the expression in `condition` argument and executes command only if expression is `True`. Otherwise, it only "suggests" DDL command. ### Properties * **.connection** (SnowflakeConnection); * **.config** (SnowDDLConfig); * **.settings** (SnowDDLSettings); * **.logger** (Logger) - pre-initialized logger with name `snowddl.engine`; * **.executor** (ThreadPoolExecutor) - pre-initialized pool executor; * **.executed\_ddl** (list) - list of raw DDL commands which were executed by engine; * *{items}* (str) - SQL query; * **.suggested\_ddl** (list) - list of raw DDL commands which were suggested for manual execution; * *{items}* (str) - SQL query; * **.context** (SnowDDLContext) - pre-initialized context information (version, edition, current warehouse, etc.) extracted from Snowflake account; * **.schema\_cache** (SnowDDLSchemaCache) - pre-initialized cache with databases and schemas, used to reduce the number of metadata queries performed by **resolvers**; # Query builder & formatter SnowDDL builds a lot of raw SQL queries. In order to simplify this task while keeping the full flexibility of SQL, SnowDDL provides query builder and query formatter via its **engine**. ## Query builder Query builder is used to build complex SQL queries from small fragments. Each fragment can be added to the current line or it may start a new line. For example, consider the following query fragments: ``` ['SELECT', 'id AS user_id', 'name AS user_name'] ['FROM', '"MY_TABLE"'] ['WHERE', 'country_id = 10'] ``` Final query: ```sql SELECT id AS user_id, name AS user_name FROM "MY_TABLE" WHERE country_id = 10 ``` Example code: ```python query = engine.query_builder() # Fill the first line query.append("SELECT") query.append("id AS user_id") query.append("name AS user_name") # Add a new line with identifier as parameter query.append_nl("FROM {table_name:i}", { "table_name": Ident('MY_TABLE') }) # Add another line with filters query.append_nl("WHERE") query.append_nl("country_id = {country_id:d}", { "country_id": 10 }) # Display query print(query) # Execute query engine.execute_meta(query) ``` In order to create a new empty query builder, call function `.query_builder()` of **engine** class. Alternatively, you may create an instance of class `SnowDDLQueryBuilder` directly. ### Methods * `append(sql, params=None)`\ Appends a fragment to the current line. Optionally formats it using placeholders and dictionary with params.
* `append_nl(sql, params=None)`\ Starts a new line and append a fragment to it. Optionally formats it using placeholders and dictionary with params.
* `fragment_count()`\ Returns a number of fragments across all lines.
* `add_short_hash(comment)`\ Part of [short hash](/guides/other-guides/short-hash) feature. Takes the comment for object and adds a short hash at the end. Returns updated comment.
* `compare_short_hash(comment)`\ Part of [short hash](/guides/other-guides/short-hash) feature. Extracts short hash from existing comment and compares it with expected short hash. Returns `True` if hashes are the same, returns `False` otherwise.
* `__str__()`\ Query builder objects can be used as normal strings. ## Formatter SnowDDL formatter uses its own custom syntax for placeholders and supports a wide range of placeholder types. ### Syntax Only named placeholders are supported. Positional placeholders are not allowed on purpose. It is important to reduce the amount of accidental mistakes. The placeholder syntax is: `{name:type}` Type is optional. Default type is `:s` (string value). ### Usage notes Query formatting is performed only when `params` are explicitly defined as `dict`. It allows you to use your own query formatter if it is necessary. All placeholders defined in SQL query text must be present in `params`, there are no "defaults". It helps to prevent typos and accidental damage associated with it. If you pass list of values for placeholder instead of single value, it will be represented as concatenated comma-separated string of individually formatted values, which is useful for `IN (val1, val2, val3)` syntax. ### Placeholder types * `:s` - common value, enclosed in single quotes and escaped\ NULL value is returned as `NULL` without quotes\ normally used for VARCHAR, TIMESTAMP, etc. * `:d` - safe decimal value, validated and formatted "as is"\ normally used for NUMBER, raw integer values in LIMIT, etc. * `:f` - safe float value, validated and formatted "as is"\ used for FLOAT values only, supports optional exponent * `:b` - safe boolean value, validated and formatted as `TRUE` or `FALSE` string\ used for BOOLEAN values only * `:i` - identifier, enclosed in double quotes and escaped\ used for identifiers of all types * `:ia` - identifier, but additionally enclosed in single quotes and escaped\ used for arguments of table functions which accept identifiers in this form * `:r` - raw value, formatted "as is", not validated, dangerous (!)\ used to add dynamic query fragments which cannot be constructed by other means * `:lf` - LIKE pattern looking for full match (`LIKE '{val}'`) * `:ls` - LIKE pattern looking for match starting with (`LIKE '{val}%'`) * `:le` - LIKE pattern looking for match ending with (`LIKE '%{val}'`) * `:lse` - LIKE pattern looking for match starting and ending with (`LIKE '{start}%{end}'`)\ accepts tuple with two elements Example of SQL with placeholders: ```sql SELECT {common_val} AS common_val , {null_val} AS {col_name:i} , u.user_id , sum(gross_amt) AS gross_amt FROM {table_name:i} u WHERE u.user_rating >= {user_rating:d} AND u.user_score > {user_score:f} AND u.is_female IS {is_female:b} AND u.status IN ({user_statuses}) AND u.user_rating NOT IN ({exclude_user_score:d}) GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT {limit:d} ``` Query formatting code: ```python # SQL with formatting params = { 'common_val': 'abc', 'null_val': None, 'col_name': Ident('NULL_VAL') 'table_name': IdentWithPrefix(env_prefix='ALICE__', 'MY_DB', 'MY_SCHEMA', 'USERS'), 'user_rating': '0.5', 'user_score': 1e1, 'is_female': True, 'user_statuses': ['ACTIVE', 'PASSIVE', 'SUSPENDED'], 'exclude_user_score': [10, 20], 'limit': 10 } print(engine.format(query, params)) ``` Result: ```sql SELECT 'abc' AS common_val , NULL AS "NULL_VAL" , u.user_id , sum(gross_amt) AS gross_amt FROM "ALICE__MY_DB"."MY_SCHEMA"."USERS" u WHERE u.user_rating >= 0.5 AND u.user_score > 1e1 AND u.is_female IS TRUE AND u.status IN ('ACTIVE', 'PASSIVE', 'SUSPENDED') AND u.user_rating NOT IN (10, 20) GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 10 ``` ### Overloading You may extend or replace standard formatter by overloading `SnowDDLFormatter` class and by explicitly setting it into overloaded property `.formatter` of `SnowDDLEngine`. # 0.66.0 - March 2026 This release introduced a major change related to WAREHOUSE parameters `generation` and `resource_constraint`. ### Changes * It is no longer possible to use parameter `resource_constraint` to specify warehouse generation using values `STANDARD_GEN_1` and `STANDARD_GEN_2`. Parameter `generation` should be used instead. ### Reason This change is requires in order to support the upcoming change in bundle 2026\_02: ### How to adapt config? Edit `warehouses.yaml` config file. 1. Find and replace values `resource_constraint: STANDARD_GEN_1` with `generation: 1`. 2. Find and replace values `resource_constraint: STANDARD_GEN_2` with `generation: 2`. SnowDDL default warehouse generation is still 1. Warehouses without explicitly set generation remain on "generation 1" and are not affected by this change. # 0.64.0 - March 2026 This release introduced a major change related to order of commands generated & executed by resolvers. ### Changes * All resolvers now generate `DROP` commands before `CREATE` and `ALTER`. ### Reason This change helps to alleviate collision issues with `FUNCTION`, `PROCEDURE` and some policies. For example, when trying to create a new procedure with an argument using DEFAULT, Snowflake may fail with error: > SQL compilation error: Cannot overload PROCEDURE 'XXX' as it would cause ambiguous PROCEDURE overloading The best way to fix this error is to identify and drop another procedure with the same name and similar arguments. Looking for potential conflicts is relatively costly when you have large number of objects to create. But the problem can by fully mitigated at zero cost if we swap execution order of commands and execute all `DROP` commands prior to `CREATE` and `ALTER` commands. Other object types are sometimes affected by this problem too, especially when referencing other objects (e.g. policies). ### Expected impact We do not expect this change to break anything inside SnowDDL itself or affect any existing configs. But it may break some review automation a few customers built on top of SnowDDL. # 0.61.0 - December 2025 This release introduced breaking changes related to Snowflake renaming "SNAPSHOTS" to "BACKUPS": ### Changes * `SNAPSHOT_POLICY` object type renamed to `BACKUP_POLICY`; * `SNAPHOT_SET` object type renamed to `BACKUP_SET`; * Parameter `snaphot_policy` renamed to `backup_policy`; ### How to adapt config? 1. Rename schema object config directories `snapshot_policy` to `backup_policy`; 2. Rename schema object config directories `snapshot_set` to `backup_set`; 3. Rename parameter `snapshot_policy` to `backup_policy` in individual backup set YAML configs; # 0.45.0 - March 2025 This release introduced a few breaking changes related to **custom resolver sequences**. If you do not override standard SnowDDL classes and resolver sequences, nothing should change. ### Changes * Separated `DatabaseAccessRoleResolver` into: * `DatabaseOwnerRoleResolver` * `DatabaseReadRoleResolver` * `DatabaseWriteRoleResolver` * Separated `SchemaAccessRoleResolver` into: * `SchemaOwnerRoleResolver` * `SchemaReadRoleResolver` * `SchemaWriteRoleResolver` * Separated `WarehouseAccessRoleResolver` into: * `WarehouseMonitorRoleResolver` * `WarehouseUsageRoleResolver` ### How to adapt resolver sequences? Find: 
    DatabaseAccessRoleResolver,
    SchemaAccessRoleResolver,
Replace with: ``` DatabaseReadRoleResolver, DatabaseWriteRoleResolver, SchemaReadRoleResolver, SchemaWriteRoleResolver, DatabaseOwnerRoleResolver, SchemaOwnerRoleResolver, ``` "Owner" roles should always be processed AFTER "read" and "write" roles. \--- Find: ``` WarehouseAccessRoleResolver, ``` Replace with: ``` WarehouseMonitorRoleResolver, WarehouseUsageRoleResolver, ``` This changes was made in order to unify code for objects with multiple role types. ### Using owner\_schema\_\* parameters for DATABASE object type It is now possible to define `owner_schema_read` and `owner_schema_write` for [DATABASE](/basic/yaml-configs/database) object type. Previously these parameters were not available due to resolver execution order. # 0.41.0 - January 2025 Some automatically created roles were renamed in SnowDDL code and documentation to avoid naming collisions with native Snowflake object type `DATABASE ROLE`. * `DatabaseRole` ⇒ `DatabaseAccessRole` * `SchemaRole` ⇒ `SchemaAccessRole` * `ShareRole` ⇒ `ShareAccessRole` * `WarehouseRole` ⇒ `WarehouseAccessRole` It does not affect config or business logic. Everything remains the same, except Python file names, class names and role suffix variable names. This change may affect you only if SnowDDL classes were overloaded with custom logic. In this case please apply the same renames to your code accordingly. # 0.37.0 - December 2024 This update is significant overhaul for config parsing and validation. Most changes are internal and should have no impact on existing valid YAML configs. But if you use programmatic config to add or change DATABASE, SCHEMA, BUSINESS ROLE, TECHNICAL ROLE objects, please make sure to read this page and update your code accordingly. ### YAML config change: grant of database roles for inbound shares Previously in order to grant DATABASE ROLE on INBOUND SHARE you had to use parameter `global_roles` or `owner_global_roles` in DATABASE, SCHEMA, BUSINESS\_ROLE object types. After this update you should use `share_read` or `owner_share_read` parameter. Since native Snowflake DATABASE ROLES are not used for anything but shares in SnowDDL paradigm, it should make config more readable. If you still need to grant a native Snowflake DATABASE ROLE, you may always do it granting it to global role first and assigning global role to specific objects using `global_roles`. Before: ```yaml share_read: - snowflake global_roles: - snowflake.object_viewer # share database roles were in global_roles before ``` After: ```yaml share_read: - snowflake - snowflake.object_viewer # share database roles are now in share_read ``` ### Parsing error handling Property `.errors` and method `.add_error` were moved from `SnowDDLConfig` to individual parsers. Parsers now have their own loggers and print errors. In general, parser errors now operate similar to resolver errors. If you implemented your own custom parsers, replace `config.add_error()` calls with `self.add_error()` calls. ### Ident patterns Config method `.get_blueprints_by_type_and_pattern()` was changed. Now it accepts newly introduced object `IdentPattern` as second argument instead of `str`. Pattern format is still the same. This change helps to make it easier to understand when we expect single identifier and when we expect identifier pattern, which may potentially match a large number of objects. It also help to keep pattern validation checks inside parsers. Before: ``` config.get_blueprints_by_type_and_pattern(TableBlueprint, "my_db.my_schema.*") ``` After: ``` config.get_blueprints_by_type_and_pattern(TableBlueprint, IdentPattern("my_db.my_schema.*")) ``` ### Blueprint changes, parsers and validators Several blueprints were significantly reworked, especially blueprints for the following object types: * DATABASE * SCHEMA * BUSINESS ROLE * TECHNICAL ROLE Previously all grant-building and most validation was happening in parsers. Now blueprints mostly hold parameters from config, validation is happening later in newly introduced validators, and grant-building is mostly happening in resolvers. What does it mean in practice? Previously you had: ```python SchemaBlueprint( full_name=SchemaIdent(self.env_prefix, "my_db", "my_schema"), ... grants=[Grant(...), Grant(...), Grant(...)], ) ``` Now you have: ``` SchemaBlueprint( full_name=SchemaIdent(self.env_prefix, "my_db", "my_schema"), owner_schema_read=[IdentPattern(...), IdentPattern(...)], owner_warehouse_usage=[AccountObjectIdent(...)], owner_account_grants=[AccountGrant("EXECUTE TASK")], ) ``` Now blueprint objects are much closer to what you see in YAML configs. This change helps to improve user experience with programmatic config. It also helps to perform full validation when YAML config and programmatic config are mixed together. Previously it was not possible, since validation was happening mostly in parsers before programmatic had a chance to run. Technical roles now use newly introduced objects `GrantPattern`. Before: ``` TechnicalRoleBlueprint( full_name=AccountObjectIdent(self.env_prefix, "my_tech_role"), grants=[Grant(privilege="USAGE", on=ObjectType.DATABASE, name=DatabaseIdent(...))], ) ``` After: ``` TechnicalRoleBlueprint( full_name=AccountObjectIdent(self.env_prefix, "my_tech_role"), grant_patterns=[GrantPattern(privilege="USAGE", on=ObjectType.DATABASE, name=IdentPattern(...))], ) ``` ### Stage file blueprints Stage file blueprints now use `Path` objects instead of `str` for paths. It helps to mitigate various issues related to path handling on Windows OS. Usually you should be able to safely convert strings into paths using basic `Path(str)`. # 0.36.0 - November 2024 This update introduces an extra layer between config files and parsers called `DirectoryScanner`. It helps to support the following improvements related to treatment of config files: * Both file extensions `.yml` and `.yaml` are now supported. * It is now possible to detect and emit warnings for unused config files using CLI option `--show-unused-files`. This feature is OFF by default. * Repeated grep() calls were removed from parsers. Now all config files are scanned only once per run, and results are re-used by parsers. It should improve performance while working with very large configs. ### Technical changes in parsers This section applies only if you implemented your own custom parsers. 1. Direct `.iterdir` calls should be replaced with wrappers `.get_database_names()` and `.get_schema_names_in_database(database_name)`. These wrappers automatically normalise names to upper-case and ignore technical directory names starting with double udnerscore (e.g. `__custom`). 2. `parse_single_file()` calls how accept string `config_key` instead of path config file. Config key does not have file extension. For example, replace `business_role.yaml` with `business_role`. Replace `db1/sc1/params.yaml` with `db1/sc1/params`. 3. Following string properties of `ParsedFiles` are now upper-cased automatically: `database`, `schema`, `name`. It may have some impact on formatting of error messages. Property `path` is unchanged. # 0.33.0 - October 2024 This update introduces significant changes related to management of policies. ## NETWORK POLICY rework [NETWORK POLICY](/basic/yaml-configs/network-policy) object type was significantly reworked. Now it behaves similarly to other types of policies. Internally it uses `POLICY_REFERENCES` table function to get connected objects (ACCOUNT, USER, etc.). Env prefix is now supported for network policies. #### Account-level NETWORK POLICY Setting `NETWORK POLICY` on ACCOUNT now requires [ACCOUNT POLICY](/basic/yaml-configs/account-policy) config. Setting it via [ACCOUNT PARAMETERS](/basic/yaml-configs/account-parameter) no longer works. Before: ``` account_params.yaml --- network_policy: MY_NETWORK_POLICY ``` After: ``` account_policy.yaml --- network_policy: MY_NETWORK_POLICY ``` #### User-level NETWORK POLICY Setting `NETWORK POLICY` on USER now requires explicit `network_policy` parameter in [USER](/basic/yaml-configs/user) config. Setting it via `session_params` no longer works. Before: ```yaml my_user: first_name: John last_name: Doe session_params: network_policy: MY_NETWORK_POLICY ``` After: ```yaml my_user: first_name: John last_name: Doe network_policy: my_network_policy ``` ## Policy references rework Previously it was required to specify references for most types of policies in policy config using `references` parameter. This parameter is still working, but it is now deprecated. Policy references can now be specified directly in [TABLE](/basic/yaml-configs/table) or [VIEW](/basic/yaml-configs/view) config using new policy reference parameters. Before: ```yaml test_masking_policy_1.yaml --- arguments: name: VARCHAR(255) returns: VARCHAR(255) body: |- REPLACE(name, 'A', '*') references: - object_type: TABLE object_name: test_table_1 columns: [name] --- test_table_1.yaml --- columns: name: VARCHAR(255) ``` After: ``` test_masking_policy_1.yaml --- arguments: name: VARCHAR(255) returns: VARCHAR(255) body: |- REPLACE(name, 'A', '*') --- test_table_1.yaml --- columns: name: VARCHAR(255) masking_policies: - policy_name: test_masking_policy_1 columns: [name] ``` ## Execution sequence rework This update introduced new `destroy_sequence` for SnowDDL applications. Original sequences were renamed: * `parser_sequence` -> `parse_sequence` * `resolver_sequence` -> `resolve_sequence` If you implemented custom application using SnowDDL code, please update it accordingly. # 0.27.0 - May 2024 This update introduces significant changes to SnowDDL permission model. ## Potentially breaking changes * `OWNERSHIP` on `ALERT`, `DYNAMIC_TABLE`, `EVENT_TABLE`, `STAGE` are now granted to corresponding schema owner role. Previously `OWNERSHIP` for these objects types was assigned to SnowDDL admin role. * New parameters were added for `SCHEMA` object type to support additional types of grants. ## Config migration guide * For schemas with `ALERT` and `DYNAMIC_TABLE` objects, use new parameter `owner_warehouse_usage` to specify warehouses which can be used by schema owner role. * For schemas with `ALERT` and `STAGE` object types, use existing parameter `owner_integration_usage` to specify names of global integrations which can be used by schema owner role. * For schemas with `ALERT` object type, add value `- EXECUTE ALERT` to new parameter `owner_account_grants` to allow execution of alerts in schema. * For schemas with `TASK` object type, add value `- EXECUTE TASK` to new parameter `owner_account_grants` to allow execution of task in schema. * For schemas with `ALERT`, `DYNAMIC_TABLE`, `PROCEDURE` accessing data in other schemas, make sure to use existing parameter `owner_schema_read` to specify these schemas. ## Code migration guide * If you use [programmatic config](/advanced/programmatic-config), which creates `DatabaseBlueprint` or `SchemaBlueprint`, make sure to add a new required parameter `permission_model`. This is how you can get default permission model, which is close to settings in previous SnowDDL versions:
```python bp.permission_model = config.get_permission_model(config.DEFAULT_PERMISSION_MODEL) ``` * `FutureGrant` object was changed. Now it accepts 4 parameters instead of 3 in the past. Parameter `on` was renamed to `on_future`, and new parameter `in_parent` was added, which takes `DATABASE` or `SCHEMA` object type:
``` class FutureGrant(BaseModelWithConfig): privilege: str on_future: ObjectType in_parent: ObjectType -- SCHEMA or DATABASE name: Union[DatabaseIdent, SchemaIdent] ``` ## Any questions or problems? You may create a [new discussion on GitHub](https://github.com/littleK0i/SnowDDL/discussions). Please provide as much context as possible. Sample configs and logs are very helpful as well. Thank you!