Search code examples
google-bigquerydbt

Defining big query dbt sources with characters in table name?


After reviewing both of the below resources:

I was unable to find an answer to this question:

Given a standard dbt project directory, I am defining a sources.yml which points to pre-existing bigquery tables that contain character names.

sources.yml:

version: 2

sources:
    - name: biqquery
      tables: 
        - name: `fa--task.dataset.addresses`
        - name: `fa--task.dataset.devices`
        - name: `fa--task.dataset.orders`
        - name: `fa--task.dataset.payments`

Using tilde as in ` was successful directly from a select statement:

(select * from `fa--task.dataset.orders`) 

but is not recognized as valid yaml in sources.

The desired result would be something like:

{{ sources('bigquery','`fa--task.dataset.addresses`') }}

Edit: Updated source.yml as requested: enter image description here


Solution

  • Try this!

    version: 2
    
    sources:
      - name: bigquery # are you sure you want to name it this? usually we name things after the data source, like 'stripe', or 'saleforce'
        schema: dataset
        database: fa--task
        tables: 
          - name: addresses
          - name: devices
          - name: orders
          - name: payments
    

    Then in your models can do:

    select * from {{ source('bigquery', 'addresses') }}
    

    It might worth checking out the guide on sources to wrap your head around what's happening here, as well as the docs for source properties which contains the list of the keys available under the source: keys.