Granting CREATE SCHEMA on a Database with SCHEMA_OWNER ruleset

[kokorin]

We experiment with data ingestion using dlt with SCD2 strategy.

Using that strategy dlt creates extra schemas in target database like <target_schema>_staging and <target_schema>_20250731023950.
We want the database to have permission model with schema_owner rule set because we want to ingest data from different instances into the same DB but different schemas.

The problem is that dlt user has no permissions to create schemas in the database.

I thought about using technical role like

<database_name>_temp_schemas:
  grants:
    "DATABASE:CREATE SCHEMA":
      - <database_name>

But I'm not sure if it will work.

[littleK0i]

I have vague memories about some settings in DLT which makes it to use schema object suffixes instead of creating new schemas.

Something like: <target_db>.<target_schema>.<target_table>_XXXXX instead of <target_db>.<target_schema>_XXXX.<target_table>. If my memory is correct, I highlt suggest to use this mode.

Creating too many disposable schemas causes a lot of mess. Snowflake permission model is fundamentally not good for this.

---

Alternatively, you may create database with database_owner permission model for raw data. And create another database with schema_owner permissions and with views pointing to database with raw data.

As far as I know, mixing it together is not possible in Snowflake due to the way how OWNERSHIP and FUTURE GRANTS work.