Allow configuration of project in BigQuery

[moseleyi]

Describe the feature

For Snowflake, we can specify in which database the external table will be created but we can't do it in BigQuery. I would like to create external tables only in one place "aka. sources" rather than in the dev project as well, since this is not yet materialised version of data but just a "window" to the source.

In BigQuery I follow the split of prod/dev/sources but usually sources project is owned by data ingestion service/tool and dbt is only a reader.

The difference is that in Snowflake I had to create a "stage" manually (location: "@sources.schema.table"), but that was just a one-liner and I could still use this package to actually read the stage. I'm guessing it's because in Snowflake this is split into two steps but in BigQuery external table is bound to its location like that.

Describe alternatives you've considered

Trying to maybe use generate_schema and database_name macros to modify it based on filters/configuration.

Here are the options I considered:

Leave as is

Bad:

• external tables are duplicated across projects
• doesn't follow the principle that all sources live in the same place
• multiplies source data, technically there's a risk that it might differ

Good:

• the definition of the external table is within the project, codebase, and version control

Force dbt to create external tables in sources

Good:

• only one external table, one source

Bad:

• suddenly dbt and any other user need write privileges to sources project

Force dbt to create external tables in sources + only let dbtCloud to run it

In this case I would create separate service account with write access to sources project and only allow stage_external_sources macro to be run from dbtCloud not from cli of developers.

Good:

• code still within dbt
• one copy of the external table

Bad:

• changes to database/schema name macros (I would like to be able to do it dynamically for any source that has "external" in yml)
• changes in other places to throw an error if somebody tries to run stage_external_sources macro