Search code examples
sql-serverdbt

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.

Solution

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