In the Snowflake get_external_build_plan macro, we first check if the given relation exists. If the external table does not yet exist, then the get_external_build_plan macro will call the create_external_schema macro, even if the schema already exists. The create_external_schema macro uses create schema if not exists. This statement requires "CREATE SCHEMA" permissions. Ideally, we should not need "CREATE SCHEMA" permission to create an external table in a schema that already exists.
Steps to reproduce
Using dbt 1.5.0, python3.9 and version 0.8.4 of this package.
Using a role that does not have "CREATE SCHEMA" permissions, and a snowflake profile, try to run stage_external_sources to create an external table in a pre-existing database/schema.
Expected results
The external table should be created successfully.
Actual results
Encountered an error while running operation: Database Error
003001 (42501): SQL access control error:
Insufficient privileges to operate on database 'XXX'
Describe the bug
In the Snowflake get_external_build_plan macro, we first check if the given relation exists. If the external table does not yet exist, then the get_external_build_plan macro will call the create_external_schema macro, even if the schema already exists. The create_external_schema macro uses
create schema if not exists
. This statement requires "CREATE SCHEMA" permissions. Ideally, we should not need "CREATE SCHEMA" permission to create an external table in a schema that already exists.Steps to reproduce
Using dbt 1.5.0, python3.9 and version 0.8.4 of this package.
Using a role that does not have "CREATE SCHEMA" permissions, and a snowflake profile, try to run
stage_external_sources
to create an external table in a pre-existing database/schema.Expected results
The external table should be created successfully.
Actual results
System information
The contents of your
packages.yml
file:Which database are you using dbt with?
Additional context