# Tackling the complexity of joining snapshots

**URL:** <https://discourse.getdbt.com/t/tackling-the-complexity-of-joining-snapshots/6705>\
**Category:** In-Depth Discussions\
**Tags:** devblog\
**Created:** [February 8, 2023, 6:21pm UTC](https://discourse.getdbt.com/t/tackling-the-complexity-of-joining-snapshots/6705 "2023-02-08T18:21:06Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [February 8, 2023, 6:21pm UTC](https://discourse.getdbt.com/t/tackling-the-complexity-of-joining-snapshots/6705/1 "2023-02-08T18:21:06Z")

</div>

This is a companion discussion topic for the original entry at [Tackling the complexity of joining snapshots | dbt Developer Blog](https://docs.getdbt.com/blog/joining-snapshot-complexity)

---

<div class="post-metadata">

**Author:** ![wphi](https://avatars.discourse-cdn.com/v4/letter/w/ed655f/32.png) [@wphi](https://discourse.getdbt.com/u/wphi)\
**Post date:** [February 20, 2024, 7:22pm UTC](https://discourse.getdbt.com/t/tackling-the-complexity-of-joining-snapshots/6705/2 "2024-02-20T19:22:50Z")

</div>

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.

```auto
	{{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}})
      

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex020/uploads/getdbt/original/1X/a7a7ca1fe379aaf90952b0e13118a817babcd14f.png) [@system](https://discourse.getdbt.com/u/system)\
**Post date:** [February 20, 2024, 7:22pm UTC](https://discourse.getdbt.com/t/tackling-the-complexity-of-joining-snapshots/6705/3 "2024-02-20T19:22:50Z")

</div>

Listed on February 20, 2024, 7:22pm UTC