Pass string with quotes as variables to dbt/Jinja

Question

I try to run some dbt code and hand over a variable that includes quotes:

SELECT
    field_1,
    field_2    
FROM
    {{ source('schema', 'table') }}
WHERE
    region IN ({{ var("regions") }}) -- ( 'NY', 'WA' )

This is the command with which I call it:

dbt compile -s model --vars '{"regions": "'NY', 'WA'"}'

but it always get compiled to this and the quotes get stripped:

region IN ( NY, WA )

I tried to escape it using "/'NY/', /'WA/'" or using brackets "{'NY', 'WA'}". But couldnt get it to work.

Answer 1

I recommend changing your input slightly to pass in the array and building the in list.

{%- macro build_in_list(fields, prefix='', postfix='') -%}
  (
  {%- for element in fields -%}
    {%- if element is number -%}
      {{ element }}
    {%- else -%}
      {%- set element = prefix ~ element ~ postfix -%}
      '{{ element }}'
    {%- endif -%}

    {%- if not loop.last -%} , {%- endif -%}
  {%- endfor -%}
  )
{%- endmacro -%}

Then call it in your model with

SELECT
    field_1,
    field_2    
FROM
    {{ source('schema', 'table') }}
WHERE
    region IN {{ build_in_list(fields=var('regions', [])) }} -- ( 'NY', 'WA' )

Finally, run it using

dbt compile -s model --vars '{"regions": ["NY", "WA"]}'

If you need just a simple quoted input outside of an in list, you can use a much simper version:

{%- macro quoted_var(field) -%}
  '{{ var(field) }}'
{%- endmacro -%}