QuestionQ44

Verified

You have written the following new agg_completed_tasks dbt model:

with tasks as (  
select * from \{\{ ref('stg_tasks') \}\}  
)  
select user_id, \{% for task in tasks %\}  
sum(  
case when task_name = '\{\{ task \}\}' and state = 'completed'  
then 1  
else 0  
end  
) as \{\{ task \}\}_completed  
\{% endfor %\}  
from tasks group by 1  

The dbt model compiles into:

with tasks as (  
select * from analytics.dbt_user.stg_tasks  
)  
select user_id, from tasks group by 1  

The case when statement did not appear in the compiled SQL. Why?

Explanation

The tasks CTE is a SQL relation, not a Jinja variable. The Jinja loop needs an iterable defined in the template context, such as a list assigned with {% set tasks = ... %}. Without it, the loop produces no case expressions. (docs.getdbt.com)

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!