Thootje
commented
1 year ago The above issue arises probably because the create_external_schema macro that is being used for BigQuery projects only specifies the dataset name to be used, but doesn't explicitly mention in which GCP project the external table should be outputted to.
This is only an issue for DBT Cloud projects where only 1 project is used to output to several GCP projects. When running a DBT Job the create schema DDL will have to infer the GCP project from the DBT Cloud connection, which in our case is the dbt-project-prod GCP project. So even though we specified to which database our external table should be outputted to, the create_external_schema macro doesn't take that into account and ignore the database property. The solution might be something along the lines of changing the DDL statement from:
create schema if not exists {{ source_node.schema }}to:
create schema if not exists `{{ target.database }}`.{{ source_node.schema }}
jeremyyeo
Describe the bug
We are using DBT Cloud (Team plan) to build a DBT project in Google BigQuery and use the
dbt-external-tablespackage to be able to generate external tables for several Google Sheets. When having the Team plan, you are allowed to have only 1 project and 1 connection. So, to be able to manipulate the target database (or the target project in the case of BigQuery), we rely on some Jinja to output the external tables to dev and prod projects in Google Cloud.Specifically, we set the
databaseproperty in the source .yml files to be able to control where the external tables should be outputted to when runningdbt run-operation stage_external_sources:database: "{% if target.name == 'dev' %}dev-dbt-project{% elif target.name == 'prod' %}prod-dbt-project{% endif %}"However, when we execute this command in a job with a
target.nameofdev, the external tables are still outputted to the production project. This is despite having logging set up that clearly shows our target.name is dev. Furthermore, the schema is correctly generated depending on the target.name using exactly the same logic as the jinja being used to generate the schema name.Steps to reproduce
To reproduce you need to do the following:
sources:
devthat executes adbt run-operation stage_external_sourcescommand.Expected results
I expect the external table to be generated in the
dbt-project-devproject with a schema name ofdev_stagingand a table name ofcustomers. Because thedev_stagingschema is not there yet, it will first have to create that schema before being able to create the external table.Actual results
The
dbt run-operation stage_external_sourcesfails and mentions that thedbt-project-dev.dev_stagingschema can't be found. However, when you check the DDL statement is uses to create the table, both the database name and the schema name is correctly generated by the jinja in the source .yml file, however the dataset can't be found.create or replace external table dbt-project-dev.dev_staging.customersAlso, a dataset of
dev_stagingis created in the dbt-project-prod project, which is the wrong GCP project.Screenshots and log output
Complete log after running a DBT job with target.name
devand commanddbt run-operation stage_external_sources(redacted to remove any real project references)System information
The contents of your
packages.ymlfile:Which database are you using dbt with?
The output of
dbt --version: In DBT Cloud we are using version 1.4 (latest) for all environments. (We can't rundbt --versionin DBT Cloud).In DBT Core where we develop most of our models the output is:
The operating system you're using: macOS 13.3.1
The output of
python --version: Python 3.8.16 (remember the bug occurs in DBT Cloud, so this is from my local setup)Additional context
This problem seems to be a DBT Cloud, single-project setup problem.