Database privilege error when creating a view in a schema

[datafrog]

The problem I’m having

I’m using dbt-core to build a view in Snowflake. The view is in a separate schema than the data table.

source table: MY_DATABASE.MY_SCHEMA.MY_TABLE
view: MY_DATABASE.STAGING_SCHEMA.RAW_VIEW

The role I have assigned in Snowflake grants me permissions to create a view in STAGING_SCHEMA.

When I run the model file to create the view, I get this error:

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

If I run the compiled code in Snowflake with the same role, I can create the view with no issues. More details below.

Details of environment

I’m running this in a repo in VS Code with

dbt-core==1.8.4
dbt-snowflake==1.8.3

I can connect to Snowflake with no errors when I run dbt debug --connection.

Project structure and files

The my_project folder structure has a single sql file in models/raw_views called raw_view.sql.
My profiles.yml file has:

my_project:
  outputs:
    dev:
      account: ****************
      database: MY_DATABASE
      password: ***************
      role: MY_ROLE
      schema: STAGING_SCHEMA
      threads: 4
      type: snowflake
      user: MYUSERNAME
      warehouse: MY_WAREHOUSE
  target: dev

My dbt_project.yml file looks like:

name: 'my_project'
version: '1.0.0'

profile: 'my_project'

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
target-path: "target"

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"

models:
  my_project:
    +materialized: 'view'
    +database: 'MY_DATABASE'
    +schema: 'STAGING_SCHEMA'

In models/raw_views I have a schema.yml file:

version: 2

models:
  - name: raw_view
    description: "view of source table"

sources:
  - name: source
    database: MY_DATABASE
    schema: MY_SCHEMA
    tables:
      - name: MY_TABLE
        description: "my source table"

and the model file raw_view.sql has:

CREATE OR REPLACE VIEW MY_DATABASE.STAGING_SCHEMA.raw_view AS (
    select * from {{ source('source', 'MY_TABLE') }}
)

This is hard coded so I can grab the compiled code and run it to create the view. Otherwise it was just a select * from my_table.

In Snowflake, MY_ROLE has these privileges:
MY_DATABASE: usage
MY_SCHEMA: usage
STAGING_SCHEMA: usage, create view

Questions

Since the create view code works in Snowflake, I am at a loss to why I need elevated privileges on the database level. My admin does not want to grant them unless absolutely needed. Since I have privileges to create views on the STAGING_SCHEMA, is there something I am doing wrong on dbt? Or do I actually need elevated privileges?

[Surya]

Hi @datafrog

Why i am getting this error:

1. Looks like its creating a schema.
2. By default, all dbt models are built in the schema specified in your environment (dbt Cloud) or profile’s target (dbt Core). This default schema is called your target schema .
3. dbt generates the schema name for a model by appending the custom schema to the target schema. For example, <target_schema>_<custom_schema> . You have configured custom_schema(STAGING_SCHEMA) for your models in dbt_project.yml.
4. When first using custom schemas, it’s a common misunderstanding to assume that a model only uses the new schema configuration; In your case your models have the configuration schema: STAGING_SCHEMA would be built in the STAGING_SCHEMA schema. However, dbt puts it in a schema like <target_schema>_STAGING_SCHEMA .
5. Your target_schema is STAGING_SCHEMA , schema defined in the profiles.yml
6. your custom-schema is STAGING_SCHEMA , schema defined in the dbt_project.yml.
7. so dbt searches for STAGING_SCHEMA_STAGING_SCHEMA schema in your database. if its doesnt find this schema it tries to create the schema. The role you have used in dbt mightn’t have permissions to create schemas.

How to resolve this:

If you prefer to use different logic for generating a schema name, you can change the way dbt generates a schema name.
dbt uses a default macro called generate_schema_name to determine the name of the schema that a model should be built in. You have to override this macro to create models only in the custom schema you have configured for your models.

Copy the following code into a file named macros/generate_schema_name.sql and try building your models.

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

I hope this will resolve your issue.

[datafrog]

Wow, thank you so much! Removing the custom schema from dbt_projects.yml fixed it! I didn’t need a custom schema and didn’t realize that was causing the issue. Thank you!!