How to configure DBT sources from Big Query EXTERNAL_QUERY

user_13664463 0
Viewed 1374

In Big Query, I am using an external connection/federated SQL query (cloudSQL) from which I can get data with SELECT * FROM EXTERNAL_QUERY("gcp-name.europe-west3.friendly_name", "SELECT * FROM database_name.external_table;")

Now my question is, in DBT, how do I define this source in my schema.yml file and how should my FROM {{source(...,...)}} statement look like?

dbt google-bigquery
1 Answers

From my perspective right now given my above comments on the current state of the dbt-external-tables package (which I don't believe meets your needs), I would say you have two options:

  1. Define your external dependencies as static views in a custom schema and then import as dbt sources.

  2. Define your external dependencies within dbt using something like Evaluate(<select *>) and then ref() those like normal in your transform / stage layer.

Example of #1

* my-bq-project-id
  |
  |_ dbt_schema
  | 
  |_ external_db_schema
    |
    |_ external_table_1
    |_ external_table_2

etc. And then you'd have:

* my-dbt-project-dir
  |
  |_ analysis
  |_ data
  |_ models
  |  |_ sources
  |  | |
  |  | |> my_external_table_1.yml
  |  | |> my_external_table_2.yml
  |  |
  |  |_ transforms
  |  |_ final
  |_ dbt_project.yml
  |_ readme.md

Where "my_external_table_1.yml" looks like:

sources:
  - name: external_db_schema
    database: my-bq-project-id
  
    tables:
      - name: my_external_table_1
        description: "Lorem Ipsum"

And your static view is defined by running a query like:

create view if not exists `my-bq-project-id.external_db_schema.my_external_table_1` as 
( SELECT * FROM EXTERNAL_QUERY("gcp-name.europe-west3.friendly_name",
  "SELECT * FROM database_name.external_table;"))

Example of #2

Just make a base level dbt model that does exactly what you are describing on a 1-1 object mapping:

my_external_table_1.sql

execute immediate (
  SELECT * FROM EXTERNAL_QUERY("gcp-name.europe-west3.friendly_name",
  "SELECT * FROM database_name.external_table;")
)

And then from here you'll be able to ref('my_external_table_1') in your transform layer etc.

user_30733400
Related