In-Depth: Declarative Snowflake RBAC

Autoschematic can be a great fit for Snowflake if you have existing resources created manually, or if your team is still modifying resources manually via the Snowsight console.

You can import your existing Snowflake resources, manage them as code, and "pull" remote state to resolve drift, building a source-of-truth that can tolerate out-of-band changes.

Of course, it's better to manage everything as code - but this can be a good starting point to iterate towards "best practices".

Add the Snowflake connector

Starting from a blank Git repo, create the main autoschematic.ron config file:

AutoschematicConfig(
    prefixes: {
        "main": Prefix(
            connectors: [
                Connector(
                    shortname: "snowflake",
                    spec: Pip(
                        name: "autoschematic-connector-snowflake",
                        version: "0.14.6"
                        binary: "autoschematic-connector-snowflake",
                    ),
                )
            ]
        )
    }
)

If you haven't already, install it:

autoschematic install

Since this is a Pip(...) connector, autoschematic install will really do

pip install autoschematic-connector-snowflake~=14.6

...under the hood. Other connectors might be installed with Cargo, for example.

Snowflake authentication

The Snowflake connector will seamlessly work with your existing Snowflake CLI configuration, such as config.toml or connections.toml.

Optional: enable "safe mode" to prevent modifying resources

autoschematic safety lock

Safety lock set. It is now not possible to modify infrastructure with `autoschematic apply` or task execution.

Disable with autoschematic safety unlock .

Import existing Snowflake resources

The easiest way to get started is to import your real Snowflake state before you hand-write anything.

autoschematic import

After import, you should see a repo layout along these lines:

main/
└── snowflake/
    ├── warehouses/
    │   └── TRANSFORMING.sql
    ├── databases/
    │   └── ANALYTICS/
    │       ├── database.sql
    │       └── MART/
    │           ├── schema.sql
    │           └── ORDERS/
    │               └── table.sql
    ├── roles/
    │   └── analyst.ron
    └── users/
        └── jane_doe.ron

Make a change locally

Once you edit a file, you'll need to stage it in git with git add before running Autoschematic:

git add main/snowflake/user/TEA_MIKE.ron
autoschematic plan
╔═════════════════════════════════════════════════════════════════════════════╗
║ At main/snowflake/users/TEA_MIKE.ron:
║  ⟣ Create Snowflake user `TEA_MIKE`
║  ⟣ Grant role `TEA_MASTER` to user `TEA_MIKE`
╚═════════════════════════════════════════════════════════════════════════════╝
 ◇ Plan complete.

autoschematic apply

If you delete a staged Snowflake file, the connector will plan a corresponding DROP ... operation for that object, for example.

Create new users and roles

When adding new users or roles, autoschematic create can scaffold these RBAC resources for you. Note that no real resources will be created in Snowflake until you run autoschematic apply, so the resulting templates are only files that exist locally in your repo.

autoschematic create
Select prefix: main
Select connector: snowflake
Select object: snowflake/roles/[name].ron
name: analyst
Writing main/snowflake/roles/analyst.ron

Managing Snowflake with Infrastructure-as-Code