Parameterising the dbt model. Variable (SQL in clause)

Help
1

The problem I’m having

Hi I am actually filtering the multiple countries data and load to the table.
I want to pass the filter condition by run command but how can we pass a list dbt

The context of why I’m trying to do this

Sample code
select * from abc where country_code in (“ind”,“USA”,“UK”)

I tried --vars ‘{“country_code” : { “ind”,“USA”,“UK” } }’

What I’ve already tried

Any one had any requirement like this .
I want to pass this variable through airflow.

Some example code or error messages

Put code inside backticks
  to preserve indentation
    which is especially important 
      for Python and YAML!
2

hi @Manapati

To use a variable passed by command line you have to use the var() method like:

select *
from abc
where country_code in {{ var('country_code' ) }}

You use country_code inside the var because it is your key in your vars dictionary

Additionally, to pass a list use the [] symbol like

dbt run --vars ‘{“country_code”: [“ind”, “USA”, “UK”]}’

try this way, let me know if you get any error

3

Hi @brunoszdl

Thanks for the help,
I think i shoud unnest the fields I checked the target SQL files.

It is showing SQL query like this below

Select * from abc
Where country_code in [“ind”,“USA”,“UK”]

Expected SQL query

Select * from abc
Where country_code in (“ind”,“USA”,“UK”)

If you could suggest any solution it will be helpful

Thanks again.

4

Of course, try something like

{% set country_code_list = var('country_code') %}

select *
from abc
where country_conde in (
{% for code in country_code_list %}
    {% if not loop.last %}
        {{ code }},
    {% else %}
        {{ code }}
    {% endif %}
{% endfor %}
)

You have to manipulate the list with jinja functions

1 Like
5

Hi @brunoszdl
Thanks jinja functions were helpful .

6

Hi @Manapati if this fixed your issue, please mark the answer as solution :D. If not, please let me know

7

hi @brunoszdl ,
sorry i am new to this platform i didn’t see the option to mark it as solution.
for my use case i skipped else part .
Thanks.

8

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