Role for managing all tables in a schema

[jimmyzzxhlh]

I'm looking into creating roles to be able to create/modify/drop any tables in a schema. The roles can be either used by automated data pipelines to create and modify tables, or by data analysts to create and modify tables for testing purposes. In this case, ideally I'd like to define databases and schemas in SnowDDL but preferably not tables, so that it's flexible for the roles to create whatever tables they like, without pre-defining the tables in SnowDDL.

Any recommendations how I should create SnowDDL? Or maybe there is a better practice? I might be missing something as I can't find a way to grant usage/create/drop/alter on all and future tables in a schema.

Thanks!

[littleK0i]

Try configuring a business role like this:

my_fancy_loader:
  schema_owner:
    - my_db.my_schema

And while defining a schema, create nested sub-directories for database and for schema. Put file params.yaml in schema directory with the following content:

is_sandbox: true

Full path: /my_db/my_schema/params.yaml.

So.. now your schema is a "sandbox" where tables can be created by external tools.

And your business role is an "owner" of this schema, so it can create / drop / alter objects in it.

[jimmyzzxhlh]

Ah this is brilliant. Thanks!