CODEWITHSUNDEEP
google-bigquerydbt
Gora Bhattacharya
Gora Bhattacharya

Reputation: 21

Folder level schema config in DBT using variable

I have a use case in DBT where I need to set schema name based on folder structure since there are many folders and many models. I have done that folder level in DBT_project.yml file and it is working fine. But I wanted to make it a bit more generic and assign values to variable and then use the variable to populate the schema name.

Since DBT doesn't support referring variable which are also defined in DBT_project.yml , I can't assign schema using a variable on folder specific. Also other than DBT_project.yml , DBT doesn't support configuring on resource/folder level.

is there any suggestion for this ? is there any way to define variable outside of DBT_project.yml and use it in the YML file or configuring resource/folder level other than DBT_project.yml.

I tried to configure schema on model level as well as folder level and it worked fine. But defining variables in project.yml and then using is for folder level is not working.

Same problem as https://stackoverflow.com/questions/75698936/how-to-reference-variables-defined-in-dbt-project-yml

Upvotes: 1

Views: 433

Answers (1)

Kliment Merzlyakov
Kliment Merzlyakov

Reputation: 1083

You can solve the issue by customizing the way dbt generates schema names. To do this, you need to put generate_schema_name.sql macro in your macros folder (see details here).

There, you can do two things:

  1. Access the full path of the model using fqn method. fqn contains the list of path values: [project, sub_folder, sub_sub_folder, ..., model_name]. See details here
  2. Access the variables defined in dbt_project.yml

Solution

dbt_project.yml:

vars:
    schema_names: {"folder_1": "schema_1", "folder_2": "schema_2"}

macros/generate_schema_name.sql:

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}

    {%- if node.fqn|length > 2 -%}
        {{ default_schema }}_{{ var("schema_names")[node.fqn[1]] | trim }}
    {%- else -%}
        {{ default_schema }}
    {%- endif -%}

{%- endmacro %}

Upvotes: 0

Related Questions