dbt source file with multiple schemas per source

Help
1

Building models against multiple environments.

creating models against different source databases and tables.

Hello,

I have a simple model that needs to run against different environs.

SELECT a.id, b.date 
FROM {{ source(var('environment'), 'TABLE_A' ) }} a 
  INNER JOIN {{ source(var('environment'), 'TABLE_B') }} b
     ON a.id = b.table_a_id

I have a simple sources.yaml

version: 2

sources:
    - name: staging
       database: staging
       schema: finance
          tables:
               -name: table_a     
## what I would think should work?
       schema: sales
          tables:
               -name: table_b

when I call dbt run -m my_cool_model --vars "{'environment': 'staging'}"

I get an error saying that my model expects a reference for table B that does not exist.

Am I doing something stupid or is this a limitation that I need to work around?

essentially I want to have a var for staging, qa, pre-prod & prod that I flip to hit multiple tables in different schemas but referenced from a single source.

any help would be much appreciated.

2

@omarkhan
ur source configuration is not correct. Try to use the below configurations as per ur case

version: 2
sources:
  - name: staging
    database: staging
    schema: finance
    tables:
      - name: table_a   
  - name: staging_two
    database: staging
    schema: sales
    tables:
      - name: table_b

in ur sql file
i have appended _two to environment name in the source function

SELECT a.id, b.date 
FROM {{ source(var('environment'), 'TABLE_A' ) }} a 
  INNER JOIN {{ source(var('environment')_two, 'TABLE_B') }} b
     ON a.id = b.table_a_id
1 Like
3

Brilliant, this didn’t 100% work for me but gave me the inspiration needed to fix my issue. Thanks for your help Surya! p.s for sources I had to use string concentation with the vars. var(‘env’) ~ ‘_two’

1 Like
4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.