How to fix "Insufficient privileges… CREATE SCHEMA" in dbt

The run stops before building anything:

003001 (42501): SQL access control error:
Insufficient privileges to operate on database 'ANALYTICS'

Before running the models, dbt checks that every schema they need exists and creates the missing ones with create schema if not exists. The role in your profile is not allowed to create schemas in that database, so the statement fails.

Copy and paste

Option 1: let the dbt role create schemas (as the owner of the database, or SYSADMIN):

grant usage, create schema on database ANALYTICS to role TRANSFORMER;

Option 2: create the schema yourself and hand it over:

create schema if not exists ANALYTICS.FINANCE;
grant ownership on schema ANALYTICS.FINANCE to role TRANSFORMER copy current grants;

Option 3: grant rights on an existing schema without ownership:

grant usage on database ANALYTICS to role TRANSFORMER;
grant usage, create table, create view on schema ANALYTICS.FINANCE to role TRANSFORMER;

Check what the role has:

show grants to role TRANSFORMER;

Why dbt wants a new schema

• A custom schema with a prefix. With +schema: finance, dbt’s default naming produces ANALYTICS_FINANCE or DBT_NICOLA_FINANCE, a schema nobody created. See how to change the target schema if you meant FINANCE.
• A schema the role cannot see. dbt lists the existing schemas with a show command, which returns only the schemas the role has privileges on. A schema that exists but gives the role no USAGE looks missing, so dbt tries to create it and gets this error. Option 3 fixes that.
• A new developer. Each developer’s target schema, such as DBT_NICOLA, is created on their first run. Their role needs CREATE SCHEMA on the development database, or the schema prepared in advance.

Which option

• Development databases: option 1. Developers get their own schemas without asking anyone.
• Production: often option 2 or 3, so that schemas are created on purpose, by the team that manages access, and dbt only writes into them.

Make sure the profile uses the role you granted. dbt debug prints the connection settings, role included, and the role in profiles.yml overrides the user’s default role.

The schema creation step comes from the code of dbt Core 1.12; the error and the grants follow Snowflake’s documentation.