SnowDDL
  • 👋Introduction
  • ðŸšĐGetting started
  • 📋Main features
  • ðŸŠĪSnowDDL vs. Declarative DCM
  • In-depth guides
    • 👓Object identifiers
    • 📐Data types
    • ðŸ“ĶObject types
    • 🎭Role hierarchy
    • 🚧Permission model
    • ðŸ”ĶOther guides
      • Administration user
      • Integrations
      • Inbound shares
      • Object OWNERSHIP
      • Safe & unsafe DDL
      • Dependency management
      • Short hash explained
      • Env Prefix explained
      • Team workflow
      • Limitations & workarounds
      • Fivetran
      • Airbyte
      • Encrypt user passwords
      • Iceberg Tables
  • Basic usage (CLI + YAML)
    • ðŸ’ŧCLI interface
    • ðŸ“ĶYAML configs
      • ACCOUNT PARAMETER
      • ACCOUNT POLICY
      • AGGREGATION POLICY
      • ALERT
      • AUTHENTICATION POLICY
      • BUSINESS ROLE
      • DATABASE
      • DYNAMIC TABLE
      • EVENT TABLE
      • EXTERNAL ACCESS INTEGRATION
      • EXTERNAL FUNCTION
      • EXTERNAL TABLE
      • FILE FORMAT
      • FUNCTION
      • HYBRID TABLE
      • ICEBERG TABLE
      • MASKING POLICY
      • MATERIALIZED VIEW
      • NETWORK POLICY
      • NETWORK RULE
      • PERMISSION MODEL
      • PIPE
      • PLACEHOLDER
      • PROCEDURE
      • PROJECTION POLICY
      • RESOURCE MONITOR
      • ROW ACCESS POLICY
      • SCHEMA
      • SECRET
      • SEQUENCE
      • SHARE (outbound)
      • STAGE
      • STAGE FILE
      • STREAM
      • TABLE
      • TASK
      • TECHNICAL ROLE
      • USER
      • VIEW
      • WAREHOUSE
    • 🏷ïļYAML placeholders
    • 📎YAML tag !include
    • 🔐YAML tag !decrypt
  • Single DB
    • ðŸĶ€Overview
  • Advanced usage (Python)
    • ⚙ïļProgrammatic config
    • 🐍Architecture overview
      • ðŸ”ĩBlueprints
      • ðŸŸĢConfig
      • 🟠Parsers
      • ðŸŸĒResolvers
      • ðŸ”īEngine
    • 🏗ïļQuery builder & formatter
  • Breaking changes log
    • 0.45.0 - March 2025
    • 0.41.0 - January 2025
    • 0.37.0 - December 2024
    • 0.36.0 - November 2024
    • 0.33.0 - October 2024
    • 0.27.0 - May 2024
  • Links
    • GitHub repository
    • PyPI package
    • YouTube tutorials
    • Changelog
    • LinkedIn profile
Powered by GitBook
On this page
  • 1) SnowDDL is "stateless"
  • 2) SnowDDL can revert changes
  • 3) SnowDDL supports ALTER COLUMN
  • 4) SnowDDL provides built-in "role hierarchy" model
  • 5) SnowDDL re-creates invalid views automatically
  • 6) SnowDDL simplifies code review
  • 7) SnowDDL supports creation of isolated "environments" for individual developers and CI/CD scripts
  • 8) SnowDDL strikes a good balance between dependency management overhead and parallelism
  • 9) SnowDDL configuration can be generated dynamically in Python code
  • 10) SnowDDL can manage packages for Java and Python UDF scripts natively

Main features

PreviousGetting startedNextSnowDDL vs. Declarative DCM

Last updated 1 year ago

1) SnowDDL is "stateless"

Unlike and , 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 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 , but it does not provide any real world examples.

SnowDDL 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

"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

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

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

10) SnowDDL can manage packages for Java and Python UDF scripts natively

DDL queries are classified into categories.

Multiple independent versions of the same configuration can be applied to one Snowflake account using . 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.

Different object types are resolved sequentially. But objects of the same type are resolved . It provides great performance for configurations with large number of objects, but it also simplifies dependency management.

When basic YAML files are no longer enough, you may build and modify configuration by adding Python modules to your config.

Recently, Snowflake introduced and 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 .

📋
schemachange
Terraform
ALTER TABLE ... ALTER COLUMN
role hierarchy
offers
"safe" and "unsafe"
env prefix
in parallel
programmatically
Snowpark
UDF functions
STAGE FILE