# Joining snapshot tables (time range based joins)

**URL:** <https://discourse.getdbt.com/t/joining-snapshot-tables-time-range-based-joins/3226>\
**Category:** Archive\
**Created:** [October 22, 2021, 11:39pm UTC](https://discourse.getdbt.com/t/joining-snapshot-tables-time-range-based-joins/3226 "2021-10-22T23:39:31Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gnilrets](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/gnilrets/32/166_2.png) [@gnilrets](https://discourse.getdbt.com/u/gnilrets)\
**Post date:** [October 22, 2021, 11:39pm UTC](https://discourse.getdbt.com/t/joining-snapshot-tables-time-range-based-joins/3226/1 "2021-10-22T23:39:31Z")

</div>

## dbt snapshots and time-range joins

dbt Snapshots are the foundation of our data modeling layer. They are used to preserve the history of how data has changed over time. Capturing this history is invaluable for debugging and critical for certain business functions where we either need to track how an attribute changed that wasn’t captured in the source system, or when we need a particular report to **not** change as subsequent data updates happen (e.g., billing). However, snapshot data can be more difficult to work with, particularly when it comes to joining two different tables that have been subject to snapshotting.

To demonstrate the issue, we’ll consider a somewhat uninteresting case of having to join an “engagement” snapshot with a “partner” snapshot, where the names of the records were changed at different times, and we want to capture those name changes.

Given an `engagements` snapshot that looks like (note that for practical purposes, the NULL has been replaced by actual date far in the future `2999-12-31 23:59:59` – aka, the Y3K bug):

| engagement\_sfid | partner\_sfid | engagement\_name | dbt\_valid\_from | dbt\_valid\_to |
| --- | --- | --- | --- | --- |
| eng1 | part1 | Engagement Old | 2021-10-08 00:01:01 | 2021-10-10 00:00:01 |
| eng1 | part1 | Engagement New | 2021-10-10 00:00:01 | 2999-12-31 23:59:59 |

and a `partners` snapshot that looks like:

| partner\_id | partner\_name | dbt\_valid\_from | dbt\_valid\_to |
| --- | --- | --- | --- |
| part1 | Partner Old | 2021-10-08 00:00:01 | 2021-10-12 00:00:01 |
| part1 | Partner New | 2021-10-12 00:00:01 | 2999-12-31 23:59:59 |

How do we join the two tables together to capture the combined record effective dates? Simply joining `engagements.partner_sfid` to `partners.partner_sfid` would give us the cartesian product of all the records, including a record where “Engagement Old” is paired with “Partner New”, which never actually happened because “Engagement Old” was not effective when “Partner Old” was changed to “Partner New”. What we want is a joined table that looks like:

| engagement\_sfid | partner\_sfid | engagement\_name | partner\_name | valid\_from | valid\_to |
| --- | --- | --- | --- | --- | --- |
| eng1 | part1 | Engagement Old | Partner Old | 2021-10-08 00:01:01 | 2021-10-10 00:00:01 |
| eng1 | part1 | Engagement New | Partner Old | 2021-10-10 00:00:01 | 2021-10-12 00:00:01 |
| eng1 | part1 | Engagement New | Partner New | 2021-10-12 00:00:01 | 2999-12-31 23:59:59 |

Fortunately, most of this problem has [already been solved](https://www.oraylis.de/blog/2014/combining-multiple-tables-with-valid-from-to-date-ranges-into-a-single-dimension), and I’ve implemented it in a [dbt macro](https://gist.github.com/gnilrets/48886b4c8945dde1da13547c2373df73). To use it, you need to do some light data preparation of the tables involved as CTEs and then use the macro to specify the CTEs involved and how they join together:

```auto
WITH engagements AS (
  SELECT
    engagement_sfid,
    engagement_name,
    partner_sfid,
    engagement_dotdotdot,
    dbt_scd_id,
    dbt_valid_from,
    dbt_valid_to
  FROM
    {{ ref('engagements') }}
),

partners AS (
  SELECT
    partner_sfid,
    partner_name,
    partner_dotdotdot,
    dbt_scd_id,
    dbt_valid_from,
    dbt_valid_to
  FROM
    {{ ref('partners') }}
),

{{
  trange_join(
    left_model='engagements',
    left_fields=['engagement_sfid', 'engagement_name', 'partner_sfid', 'engagement_dotdotdot'],
    left_primary_key='engagement_sfid',
    right_models={
      'partners': {
        'fields': ['partner_sfid', 'partner_name', 'partner_dotdotdot'],
        'left_on': 'partner_sfid',
        'right_on': 'partner_sfid',
      }
    }
  )
}},

SELECT * FROM trange_final

```

---

<div class="post-metadata">

**Author:** ![ron.chipman](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/ron.chipman/32/891_2.png) [@ron.chipman](https://discourse.getdbt.com/u/ron.chipman)\
**Post date:** [October 23, 2021, 4:43am UTC](https://discourse.getdbt.com/t/joining-snapshot-tables-time-range-based-joins/3226/2 "2021-10-23T04:43:27Z")

</div>

Interesting approach @gnilrets . Generally, in dwh modeling, the SCDs are at the end of the process, and by rule, you do not join these together directly – but through the interesting facts that you want to measure. So, you might have a simple fact of ‘number\_of\_engagements’ in which both partners and engagements are connected to in a table like “partner\_sfid, engagement\_sfid, nb\_engagements”. in this manner, the right partner and the right engagement record is linked to the fact (in this case 1 – “factless fact table”), and as a result, together.

In dbt, they are encouraging us to put snapshots very early on, and I’m trying to leverage these years of modeling experience to wrap my head around this approach.

cc: @claire (who has posted tons of great modeling articles).

---

<div class="post-metadata">

**Author:** ![peterh](https://avatars.discourse-cdn.com/v4/letter/p/919ad9/32.png) [@peterh](https://discourse.getdbt.com/u/peterh)\
**Post date:** [October 23, 2021, 10:13pm UTC](https://discourse.getdbt.com/t/joining-snapshot-tables-time-range-based-joins/3226/3 "2021-10-23T22:13:34Z")

</div>

Thanks for posting this. We use the same pattern on our team though we haven’t yet made it a macro - definitely will take a look at yours. We call the pattern a change date spine.