dbt tries to create schema which already exists, as user with no create permissions

Help
1

I’m trying to create a model in an existing schema. However, on run, dbt runs “CREATE SCHEMA {} IF NOT EXISTTS”. The user doesn’t have privileges to create a schema in the target database and i don’t want to enable so. As a pre-hook the user assumes a role which can operate on the target schema but it appears as if dbt tries to create it before it gets to the pre-hook
I’m using version 0.19.1, I saw a PR from 2020 suggesting the issue should have been fixed but I still have it

1 Like
2

How many schemas are you trying to create? If you create the schema separately in advance, then your dbt user won’t need to try and make its own.

I think that pre-hooks run inside their own transaction by default, which would be why assuming the role in advance doesn’t work.

As a side note, dbt 0.19 is about 2 years old now - you should consider moving to at least v1.0 if you can and ideally 1.4 (the latest version).

3

I’m not trying to create a schema myself. The schema already exists, but dbt runs ‘CREATE SCHEMA IF NOT EXISTS’ that’s the problem! It’s this problem I’m having:

1 Like
4

Have you checked whether this unwanted behaviour still happens in version 1.x of dbt? Versions 0.x of dbt Core are no longer supported

5

I am having the same problem. dbt (dbt=1.3.0) trying to create a schema which already exist. The user doesn’t have privileges to create a schema in the target database and i don’t want to enable so.

airflow@airflow-854558f444-r9rdn:/opt/airflow/dags/dbt/projects/sdna_us$ dbt run --select last_updt_dt --profiles-dir .
20:47:46  Running with dbt=1.3.0
20:47:47  Found 335 models, 20 tests, 0 snapshots, 0 analyses, 764 macros, 0 operations, 6 seed files, 6 sources, 0 exposures, 0 metrics
20:47:47  
20:47:50  Encountered an error:
Database Error
  003041 (42710): SQL compilation error:
  Schema 'SDNA_US_SDNA_UI_DEV' already exists, but current role has no privileges on it. If this is unexpected and you cannot resolve this problem, contact your system administrator. ACCOUNTADMIN role may be required to manage the privileges on the object.

dbt_project.yml files has the following section:

    sdna_ui:
      +tag: sdna_ui
      +transient: false
      +schema: SDNA_US_SDNA_UI_{{ env_var('AIRFLOW_ENVIRONMENT')|upper }}
      intermediate:
        materialized: view
      transformations:
        materialized: table
        +transient: false
7

Thanks for confirming that it’s still happening in new versions of dbt Core @rmenendezm! I asked around internally, and here’s what I learned:

  • dbt only considers creating schemas for models that are actually selected to run (relevant lines of code)
  • dbt first determines what schemas exist, and will run create schema ... if not exists only if it doesn’t think the schema exists.

With this in mind, our hypothesis is:

  • the schema does exist (already confirmed)
  • The database user that is using dbt doesn’t have permission to see the schema at all, even that it exists
  • One or more of the selected models in your run are intended to land in the SDNA_US_SDNA_UI_DEV schema
  • dbt can’t see that the schema already exists (it’s not allowed to!) so it tries to create it, and hits an error.

I would suggest that you check what permissions the database user has, to ensure it can at least see the schemas.

From there, if you have a different situation then it would be worth opening a GitHub issue with the details

1 Like
8

I found the role set by default in the profiles.yml, which was the one used by dbt to initial query the schema, did not have the appropriate privilege, so it could not see the schema. And that’s why it try to create an already existing schema.

1 Like