SnowDDL

SnowDDL is a declarative-style tool designed for automating object management in Snowflake. It is built to address practical data engineering challenges such as managing exponentially growing object schemas, handling multiple Snowflake accounts (dev, stage, prod), resolving developer conflicts in shared accounts, and dynamically generating configurations using Python.

Key capabilities include:

• Statelessness: Operates without maintaining a local state file.
• Reversibility: Can revert changes made to the Snowflake environment.
• Schema Evolution: Supports ALTER COLUMN operations.
• Environment Isolation: Supports creating isolated environments for individual developers and CI/CD processes.
• Dynamic Configuration: Allows configuration to be generated via Python code.
• Native Package Management: Manages packages for Java and Python UDF scripts.

SnowDDL uses environment prefixes to isolate different environments (e.g., DEV, PROD) within the same Snowflake account. This prevents accidental modification of production objects when working in development.

• --env-prefix: The string used to prefix object names (e.g., DEV).
• --env-prefix-separator: The character(s) used to separate the prefix from the object name. Supported values are __ (default), _, or $.

Constraints:

• The prefix must only contain ASCII uppercase letters, digits, and single underscores.
• The prefix cannot end with an underscore.
• The prefix cannot contain the chosen separator.

Example: If prefix is DEV and separator is __, an object named TABLE_A becomes DEV__TABLE_A.

The engine tracks DDL statements in two main lists: executed_ddl and suggested_ddl.

When executing DDL (that is not a metadata operation), the engine stores the formatted SQL in a thread-local buffer (_executed_ddl_buffer). To move these buffered statements into the main executed_ddl list, you must call flush_thread_buffers().

Similarly, statements that were 'suggested' instead of executed are stored in _suggested_ddl_buffer and must be moved to suggested_ddl via flush_thread_buffers().

# After running operations in threads:
engine.flush_thread_buffers()

# Now you can access the full lists:
print(engine.executed_ddl)
print(engine.suggested_ddl)

Permission models define how privileges (like OWNERSHIP, SELECT, INSERT) are granted on various object types.

• add_permission_model(name: str, permission_model: PermissionModel): Registers a custom PermissionModel under a specific name.
• get_permission_model(name: Optional[str]): Retrieves a permission model by name. If name is None, it returns the DEFAULT_PERMISSION_MODEL (which is `

If a value passed to format_sql is a list, the SnowDDLFormatter will automatically apply the specified transformation to every element in the list and join them with a comma (, ). Note that this behavior is skipped for the dp (dynamic_param) transformation.

Example: Formatting a list of integers with d results in a comma-separated string of safe decimals.

# Assuming formatter is an instance of SnowDDLFormatter
sql = "INSERT INTO table (ids) VALUES ({val:d})"
params = {"val": [1, 2, 3]}
# Result: INSERT INTO table (ids) VALUES (1, 2, 3)

Placeholders allow you to inject dynamic values (bool, float, int, str, or lists of these) into your configuration.

• add_placeholder(name: str, value: Union[...]): Registers a new placeholder with a specific name and value.
• get_placeholder(name: str): Retrieves the value of a registered placeholder. Raises ValueError if the name is unknown.

config = SnowDDLConfig()
config.add_placeholder("max_retries", 5)
config.add_placeholder("is_prod", False)

val = config.get_placeholder("max_retries") # returns 5

Use SnowDDLConfig to manage project-wide configuration, including blueprints, placeholders, and permission models. When initializing, you can optionally provide an env_prefix to handle environment-specific identifiers.

Important: If an env_prefix is provided, it must end with a valid separator: __ (double underscore), _ (single underscore), or $ (dollar sign). Failure to include a separator will raise a ValueError.

from snowddl.config import SnowDDLConfig

# Valid initialization with separator
config = SnowDDLConfig(env_prefix="DEV__")

# This will raise ValueError if no separator is provided
# config = SnowDDLConfig(env_prefix="DEV")

The SnowDDLContext class is used to manage the execution state and environment of a SnowDDL session. Upon initialization with a SnowDDLEngine instance, it automatically queries the Snowflake session to capture metadata such as the current account, region, user, role, warehouse, and administrative privileges (e.g., is_account_admin, is_sys_admin). It also determines the Snowflake version and edition (using the Edition enum) based on bootstrap data.

It performs internal validation to ensure the session has the necessary privileges for configured operations, such as execute_replace_table or execute_account_params.

from snowddl.engine import SnowDDLEngine
from snowddl.context import SnowDDLContext

# Assuming engine is already initialized
context = SnowDDLContext(engine)

Use describe_meta(sql, params=None) to perform metadata operations (like DESCRIBE TABLE ...). This method uses the Snowflake describe capability via a DictCursor and returns the result. If the operation fails, it raises a SnowDDLExecuteError.

# Example of describing a table
metadata = engine.describe_meta("DESCRIBE TABLE my_table")

The SnowDDLQueryBuilder includes utility methods to attach and verify short hashes (based on a SHA1 digest of the builder's state) to comments. This can be used to track the state of a query.

• add_short_hash(comment): Returns the provided comment appended with a unique short hash (e.g., "my comment #abc123def456").
• compare_short_hash(comment): Returns True if the provided comment string ends with the current builder's short hash, otherwise False.

The SnowDDLQueryBuilder provides the following methods for query construction:

• append(sql, params=None): Formats the provided SQL (and optional parameters) using the internal formatter and adds it to the current fragment line.
• append_nl(sql, params=None): Formats the provided SQL and starts a new line (fragment) with this SQL.
• fragment_count(): Returns the total number of SQL fragments added across all lines.
• __str__(): Returns the complete query as a single string, joining fragments with spaces and lines with newlines.

The SnowDDLSettings class is used to manage application-wide configuration for SnowDDL. It inherits from BaseModelWithConfig and allows you to control execution behavior, object filtering, and specific features like cloning or refreshing credentials. Many settings are boolean flags that determine which types of DDL (Data Definition Language) statements are executed or which objects are included/excluded during operations.

from snowddl.settings import SnowDDLSettings
from snowddl.blueprint import ObjectType

settings = SnowDDLSettings(
    execute_safe_ddl=True,
    include_object_types=[ObjectType.TABLE, ObjectType.VIEW],
    max_workers=16,
    clone_table=True
)