command running 'create schema if not exists' when schema exists

[SigridAndersen]

Describe the bug

After version 0.8.3 run-operation stage_external_sources runs a ddl statement create schema if not exists. This is an issue for us because the user does not have permission to create datasets, and we don't want to allow this. The statement is run even if the schema already exists in the database.
Checking if the schema exists, and only running the statement if the schema is missing would fix the problem.

Steps to reproduce

run run-operation stage_external_sources with user/serviceaccount without bigquery.datasets.create permission

Expected results

External source is created. This is the result up to version 0.8.2

Actual results

Error message:

Encountered an error while running operation: Database Error
Access Denied: Project my-bq-project: User does not have bigquery.datasets.create permission in project my-bq-project.

System information

The contents of your packages.yml file:
packages:

• package: dbt-labs/dbt_external_tables
  version: 0.8.3

Which database are you using dbt with?

• redshift
• snowflake
• other (specify: bigquery)

The output of dbt --version:

latest

The operating system you're using:

The output of python --version:

Additional context

Running on dbt cloud

[jeremyyeo]

Checking if the schema exists, and only running the statement if the schema is missing would fix the problem.

We have a schema check here: https://github.com/dbt-labs/dbt-external-tables/blob/main/macros/plugins/bigquery/create_external_schema.sql#L11 and we won't run the create schema SQL if the schema check passes...

Does your user also not have permission to do the schema check?

[SigridAndersen]

Ah, I see. No, the user has permission for the schema check, but this query only returns datasets in the US region if the region qualifier is not set (see https://cloud.google.com/bigquery/docs/information-schema-datasets-schemata#scope_and_syntax). All my datasets are in region-EU

[jeremyyeo]

Ah - that's interesting. Are you setting region anywhere in your dbt project to put to EU? Maybe we can use the target.region var somehow in the macro...

[SigridAndersen]

No, I'm not setting the region anywhere in dbt. I'm using terraform to create datasets, and location EU is set here. I'm also using terraform to create connections to dbt ( https://registry.terraform.io/providers/dbt-labs/dbtcloud/latest/docs/resources/global_connection ). I see there is an optional field "location" I can set, which I'm not using right now since we don't create datasets from dbt. Right now {{ target.location }} returns None for me, but maybe if I set the location?

[SigridAndersen]

Is the check done differently in dbt core? I never have this problem when running regular models

[jeremyyeo]

Not sure actually - looks like it's deeper into the weeds than the simple SQL query we run in this package...

https://github.com/dbt-labs/dbt-adapters/blob/4b3fc1e9c11a6b454672e02bca5af186dc5caf96/dbt-bigquery/src/dbt/adapters/bigquery/impl.py#L218-L234

You could probably override the built in macro in the mean time...

    {% set schema_exists_query %}
        select * from `{{ source_node.database }}`.`region-eu`.INFORMATION_SCHEMA.SCHEMATA where schema_name = '{{ source_node.schema }}' limit 1
    {% endset %}

[SigridAndersen]

Could you maybe use the dbt core method instead?
{{ adapter.check_schema_exists(model.database, model.schema)}}
Would this work?

[SigridAndersen]

So replacing https://github.com/dbt-labs/dbt-external-tables/blob/main/macros/plugins/bigquery/create_external_schema.sql with something like this:

{%- macro bigquery__create_external_schema(source_node) -%}
    {%- set fqn -%}
        {%- if source_node.database -%}
            `{{ source_node.database }}`.{{ source_node.schema }}
        {%- else -%}
            {{ source_node.schema }}
        {%- endif -%}
    {%- endset -%}

    {%- if not adapter.check_schema_exists(source_node.database, source_node.schema) -%}
        {%- set ddl -%}
            create schema if not exists {{ fqn }}
        {%- endset -%}
        {{ return(ddl) }}
    {%- else -%}
        {{ return('') }}
    {% endif %} 
{%- endmacro -%}