🦀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:

GRANT USAGE ON DATABASE <database> TO ROLE <singledb_role>;
GRANT CREATE SCHEMA ON DATABASE <database> TO ROLE <singledb_role>;

GRANT OWNERSHIP ON FUTURE SCHEMAS IN DATABASE <database> TO ROLE <singledb_role>;

-- Repeat for every object type you need (TABLES, VIEWS, etc.)
GRANT OWNERSHIP ON FUTURE <object_type_plural> IN DATABASE <database> TO ROLE <singledb_role>;

-- Apply only if you want to ALTER tables via SnowDDL
GRANT USAGE,OPERATE ON WAREHOUSE <warehouse_name> TO ROLE <singledb_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)
  --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
  --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-masking-policy     Additionally apply changes to MASKING POLICIES
  --apply-row-access-policy  Additionally apply changes to ROW ACCESS POLICIES

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.