Bigquery datasets don't always get created

[Robin-C]

Describe the bug

The dataset where external tables are created does not always get automatically created.

Steps to reproduce

Manually create a dataset where your external table is supposed to be created in then immediately drop it, stage your external table and you should get an error saying the dataset does not exist.

I'm thinking there may be a lag between the dataset being dropped and when the data gets updated in the SCHEMATA table (which this macro uses) to determine if a dataset exists or not.

Expected results

dataset gets created before external table is created

Actual results

Screenshots and log output

System information

The contents of your packages.yml file:

Which database are you using dbt with?

• redshift
• snowflake
• bigquery
• other (specify: ____________)

The output of dbt --version:

<output goes here>

The operating system you're using:

The output of python --version:

Additional context

[katieclaiborne-duet]

@Robin-C, could you try to reproduce your issue? Google has reverted the rollout that I believe was causing it (comment).

[Robin-C]

@Robin-C, could you try to reproduce your issue? Google has reverted the rollout that I believe was causing it (comment).

Hello @katieclaiborne-duet,

Yes, it seems to have resolved the issue!

Thanks for the communication, much appreciated <3.

I still think the create_external_schema macro could be improved because depending on the region of the project, the macro should be querying another SCHEMATA table.

https://docs.cloud.google.com/bigquery/docs/information-schema-datasets-schemata#scope_and_syntax

Curious to hear maintainer thoughts on this