dbt training - the next part

 So I enjoyed the first dbt training enough to give the next bunch of training a go. The first part of this training introduces you to jinja and using it to, for example, loop through creating case statements to pivot out rows to columns. 

Jinja Code - 

	{%- set activities = ['Pull_Up', 'Crunch','Push_Up','Plank_Time','Dumbbells','Box_Jump'] -%}
	select date_done,
	{% for activity in activities -%}
	sum(case when replace(description,' ','_') = '{{ activity }}' then act_count else 0 end)
	as {{ activity }}_count
	{%- if not loop.last -%}
	,
	{% endif -%}
	{%- endfor %}
	from {{ source('exercise_data','V_EXERCISE_STAR') }}
	group by 1

view raw jinjaLoop.jinja2 hosted with ❤ by GitHub

I can see there are lots of really useful purposes for this and am hoping that in I will find out later that rather than using a hard coded list you can use a query to generate the list of entries to loop through. This would be a great feature, one of the limitations of a lot of SQL systems is you can't pivot on an unknown list of values. Now onto learning about macros and packages in dbt. 

Generates SQL Code -  

	select date_done,
	sum(case when replace(description,' ','_') = 'Pull_Up' then act_count else 0 end)
	as Pull_Up_count,
	sum(case when replace(description,' ','_') = 'Crunch' then act_count else 0 end)
	as Crunch_count,
	sum(case when replace(description,' ','_') = 'Push_Up' then act_count else 0 end)
	as Push_Up_count,
	sum(case when replace(description,' ','_') = 'Plank_Time' then act_count else 0 end)
	as Plank_Time_count,
	sum(case when replace(description,' ','_') = 'Dumbbells' then act_count else 0 end)
	as Dumbbells_count,
	sum(case when replace(description,' ','_') = 'Box_Jump' then act_count else 0 end)
	as Box_Jump_count
	from KEBOOLA_7127.WORKSPACE_15661914.V_EXERCISE_STAR
	group by 1

view raw jinjagencode.sql hosted with ❤ by GitHub