Search code examples
sqljinja2dbt

How to load a list from a file in DBT?

Suppose we want to have a long list of fruits to use in some queries:

apple
banana
... # more 100
melon

We want to use this list in queries, for example:

select
   fruit
from
   {{ ref( 'other_model' ) }}
where
   fruit in {{ list_of_fruits() }}

We can then create a macro, for example:

{% macro list_of_fruits() %}
    
    {% set fruits = # how to read the list of the fruits from the file? %}
    fruit IN ({{ fruits | join(', ') }})

{% endmacro %}

How can we achieve this in DBT?

Thanks.

Solution

  • You could use a seed file: https://docs.getdbt.com/docs/build/seeds

    Once you load the seed file with dbt seed you could then use:

    select
   fruit
from
   {{ ref( 'other_model' ) }}
where
   fruit in (select fruit from {{ ref('seed_model_here') }} )