'Using variable arrays in models

Is it possible to define an array in the vars section and use it inside the SQL syntax of a model?

Something like this

dbt_project.yml:

vars:
  active_country_codes: ['it','ge']

model.sql

SELECT ... 
  FROM TABLE WHERE country_code IN ('{{ var("active_country_codes") }}')

I've tried with a single value, i.e:['it'], and works but if I add another it starts failing.

I am using the SQL Server Data connector.

sql-serverdbt


Solution 1:[1]

The query that you are writing is correct. You just need to pass the variable as a string with a comma also as a string character.

vars:
  active_country_codes: 'it'',''ge'

You can do something like this :

SELECT ... 
  FROM TABLE WHERE country_code IN ('{{ var("active_country_codes") }}')

And it will create query for you like this:

SELECT ... 
  FROM TABLE WHERE country_code IN ('it,'ge')

I have tested this and it's working fine. I'm using Bigquery Connection but it shouldn't matter as it's dbt generation.

Solution 2:[2]

My educated guess is that the result of {{ var("active_country_codes") }} is to insert a comma separated string. In that case, you'll need a string splitting function. You will have to roll your own if you haven't already, unless you have SQL Server 2016 or later. Then you can use string_split. Below is code using it. I use the exists approach as opposed to in due to performance.

select    ...
from      table t
where     exists (
              select   0
              from     string_split('{{ var("active_country_codes") }}', ',') ss
              where    t.country_code = ss.value
          )

Solution 3:[3]

I would use:

vars:
   var_name: "'one','two','three'"

where field_name in ({{ var("var_name") }})

Looks a little bit clearer than:

  active_country_codes: 'it'',''ge'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 pkd
Solution 2 pwilcox
Solution 3 Olha Lysak

Related Questions