Search code examples
yamldbt

How to reference variables defined in dbt_project.yml


I defined variables in my dbt_project.yml which I want to reference.

name: "snowflake_dwh"
version: "1.0.0"
config-version: 2

profile: "default"

vars:
  test_results_schema: dbt_test_history
  clean: "{% if target.name == 'prod' %} CLEAN {% else %} {{ target.database }} {% endif %}"
  custom: "{% if target.name == 'prod' %} CUSTOM {% else %} {{ target.database }} {% endif %}"
  test_schema: "{% if target.name == 'prod' %} DBT_TEST_RESULTS {% else %} DBT2_TEST_RESULTS {% endif %}"

seeds:
  data:
  +database: var.custom # -----> DOESN'T WORK
  +schema: seed_data
  +full_refresh: true

snapshots:
  +target_schema: dbt_snapshots
  +target_database: var.clean # -----> DOESN'T WORK

tests:
  snowflake_dwh:
    clean:
      +database: var.clean # -----> DOESN'T WORK
    custom:
      +database: var.custom # -----> DOESN'T WORK
  +store_failures: true
  +schema: dbt_test_results

on-run-start:
  - '{{ remove_empty_tables(var("clean"), var("test_schema")) }}' # -----> WORKS

I want to change this to make it work since now I'm getting an error back

Runtime Error
  Database error while listing schemas in database "var.clean"
  Database Error
    002043 (02000): SQL compilation error:
    Object does not exist, or operation cannot be performed.

This seems like it's reading it as a string and not as a variable.

I have tried changing to this syntax

...
target_database: "{{var('clean')}}"
...

but that throws a different error

Compilation Error
  Could not render {{var('clean')}}: Required var 'clean' not found in config:
  Vars supplied to <Configuration> = {}

I also looked at dbt docs here but there's no example there. Looked at others questions here too but nothing worked.

tl;dr: what is the syntax for accessing variables defined in the yml file?


Solution

  • This is not possible at the moment in dbt 1.4. See https://github.com/dbt-labs/dbt-core/discussions/6170 and https://github.com/dbt-labs/dbt-core/issues/4873