Dbt Model Not Picking Up Source Schema From source.yml File

Help
,
1

The problem I’m having

I have source properties defined in a sources.yml file stored under a subfolder in the models folder that defines the source name, database, schema, and tables. However when I run my dbt model, it keeps defaulting to the information stored in the profile.yml file stored in a separate folder.

The context of why I’m trying to do this

I have a unique situation where the name of my source schema may change unexpectedly over time so I’m compensating by generating the sources.yml file with updated source information (schema name). So I’m trying to have my dbt models refer to the sources.yml file for that updated schema name using the source() jinja function.

What I’ve already tried

Example source.yml file code (stored in “/models/sources”)

version: 1

sources:
  - name: my_source_name
    database: my_source_db
    schema: updated_schema_name123
    tables:
      - name: my_source_table
        identifier: my_source_table123

Example profiles.yml code (stored in “/profiles”)

example_project:
  outputs:
    dev:
      dbname: dev_database
      host: localhost
      pass: my_password
      port: 5432
      schema: default_schema
      threads: 4
      type: postgres
      user: db_user
  target: dev

Example dbt model code (model name “my_test_model.sql”)

{{ config(materialized='table') }}

SELECT 
        col1,
	count(*)
FROM {{ source('my_source_name', 'my_source_table') }}
group by col1

dbt run command

dbt run --profiles-dir=/profiles --target dev

When running the dbt run command, the result is a new target schema created named “default_schema_test_dbt_schema” since “test_dbt_schema” is defined in dbt_project.yml. But my expectation is a new target schema named “updated_schema_name123_test_dbt_schema” according to this documentation where referring to the source name should automatically map to the database, schema, and table(s) defined in the sources.yml file. Does the “–profiles-dir” parameter override looking at the sources.yml file somehow.

Thanks,
Glen

2

Source schemas and the schema that dbt writes your models to are different things.

Note: @Jeremy Yeo originally posted this reply in Slack. It might not have transferred perfectly.

3

Ah ok, I guess I misinterpreted the documentation. So the schema defined in the profiles.yml file is meant for destination only and has nothing to do with source schema references. Thanks for the clarification.

4

You bet.

Note: @Jeremy Yeo originally posted this reply in Slack. It might not have transferred perfectly.

5

For sources - if the schema config is not set on the source - then the schema resolve to the “name” of the source. It does not resolve to the profile.yml schema config at all.

Note: @Jeremy Yeo originally posted this reply in Slack. It might not have transferred perfectly.

6

Interesting, so in my previous source.yml example:

version: 1

sources:
  - name: my_source_name
    database: my_source_db
    schema: updated_schema_name123
    tables:
      - name: my_source_table
        identifier: my_source_table123

You’re saying that if the schema entry “updated_schema_name123” did not exist in the yml file, dbt will default to the “name” field, resulting in “my_source_name” as the schema it will look for in the source database?

7

Yup

Note: @Jeremy Yeo originally posted this reply in Slack. It might not have transferred perfectly.

8

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