🦀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)
  --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
  --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 database (without env_prefix) to destination database (with 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.