dbt - more stuff

The more I used dbt the more I like it. I am finding many of its features really useful and I haven't even done the training on macros and packages yet so I feel there is more to come yet. In the meantime I have now start to, just of the fun of it, create some downstream views with dependencies on other steps and a function in SQL. Happy to say it is all working really well and using jinja (and my Snowflake function) has saved me heap of time coding. 

Sources yml: 

	version: 2
	sources:
	- name: exercise_data
	description: Source table from Snoflake
	database: KEBOOLA_7127
	schema: WORKSPACE_15661914
	tables:
	- name: EXERCISE_FACT
	- name: V_EXERCISE_STAR

view raw source.yml hosted with ❤ by GitHub

View using the source function (results in SQL)

	{%- set activities = ['Pull_Up', 'Crunch','Push_Up','Plank_Time','Dumbbells','Box_Jump'] -%}
	select {% 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') }}

view raw view_using_source.sql hosted with ❤ by GitHub

View that references the output from previous steps, allows them to be linked: 

	{{ config(materialized='view') }}
	{%- set activities = ['Pull_Up_count', 'Crunch_count','Push_Up_count','Plank_Time_count','Dumbbells_count','Box_Jump_count'] -%}
	select pf.*,
	mf.*,
	{% for activity in activities -%}
	f_exercise_out({{ activity }},{{ activity }}_avg, {{ activity }}_sd , 1) as {{ activity }}_status
	{%- if not loop.last -%}
	,
	{% endif -%}
	{%- endfor %}
	FROM
	{{ ref('exercise_pivot_fact') }} pf
	CROSS JOIN {{ ref('stg_total_metrics') }} mf
	order by 1 desc

view raw reference_prev_step.sql hosted with ❤ by GitHub

Assuming you create your sources in the yml file and reference previous steps using the reference function rather than calling the resulting table (dbt handles that for you) (as shown above) it will automatically work out the dependencies, run things in the right order and produces a lovely lineage graph like so. 

I am hoping to stop playing with what I know of dbt and might make some visuals based on the extra processing I have done before playing with dbt some more by working through some more of the training.