Using variable arrays in models

Question

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.

Answer 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.