Tackling the complexity of joining snapshots

In-Depth Discussions
1

This is a companion discussion topic for the original entry at Tackling the complexity of joining snapshots | dbt Developer Blog

2

Why are using coalesce within a greatest/least? Wouldnt nulls be overridden anyways by any supplied values. Additionally, couldn’t you add the coalesce(large date value/env variable) into the join logic itself? You could also simply add the large date value as a static parameter to the Macro, this would containerize the macro to not be dependent on any external factors and also force the desired end user behavior to handle the VALID_TO = NULL issue.

	{{cte_join}}.*,
       	greatest(
			{{cte_join}}.{{cte_join_valid_from}},
			{{cte_join_on}}.{{cte_join_on_valid_from}}
		) as add_{{cte_join_on}}_valid_from,
       	least(
			{{cte_join}}.{{cte_join_valid_to}},
			{{cte_join_on}}.{{cte_join_on_valid_to}}, 
			'infinity'::timestamptz
		) as add_{{cte_join_on}}_valid_to
  
   from {{cte_join}}
   left join {{cte_join_on}} on {{cte_join}}.{{cte_join_id}} = {{cte_join_on}}.{{cte_join_on_id}}
      and ({{cte_join_on}}.{{cte_join_on_valid_from}} <= coalesce({{cte_join}}.{{cte_join_valid_to}},'infinity'::timestamptz)
      and coalesce({{cte_join_on}}.{{cte_join_on_valid_to}}, 'infinity'::timestamptz) >= {{cte_join}}.{{cte_join_valid_from}})