# Incremental from multiple refs / sources

**URL:** <https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488>\
**Category:** Help\
**Tags:** incremental, best-practice, dbt-core\
**Created:** [May 30, 2023, 9:01pm UTC](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488 "2023-05-30T21:01:08Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ktl](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@ktl](https://discourse.getdbt.com/u/ktl)\
**Post date:** [May 30, 2023, 9:01pm UTC](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488/1 "2023-05-30T21:01:09Z")

</div>

I’d like to setup my fact tables as incremental, but am not sure how to make sure they load correctly from multiple sources / refs.

Say I have a `fact_a` , which pulls from `ref_b` and `ref_c`. I’d like `fact_a` to refresh in cases where _either_ `ref_b` or `ref_c` has been updated according to my incremental logic, but can’t figure out how to do this correctly.

An example of what I want:

### ref\_b table

| ref\_b\_id | ref\_b\_value | ref\_b\_updated\_at |
| --- | --- | --- |
| 1 | abcdef | ‘2023-05-30’ |
| 2 | ghijk | ‘2023-05-15’ |
| 3 | lmno | ‘2023-04-30’ |

### ref\_c table

| ref\_c\_id | ref\_b\_id | ref\_c\_value | ref\_c\_updated\_at |
| --- | --- | --- | --- |
| 1 | 1 | opqr | ‘2023-05-30’ |
| 2 | 2 | stuv | ‘2023-05-30’ |
| 3 | 4 | wxyz | ‘2023-04-30’ |

Let’s say we join on `ref_b_id`, which is the unique key for the `fact_a` table. If I’m generating incremental rows for fact\_a filtering on `updated_at > '2023-05-29'`, I want the new rows to be inserted to be:

### fact\_a

| ref\_b\_id | ref\_b\_value | ref\_c\_id | ref\_c\_value | updated\_at |
| --- | --- | --- | --- | --- |
| 1 | abcdef | 1 | opqr | 2023-05-30 |
| 3 | lmno | 2 | stuv | 2023-05-30 |

We’ve added the rows where the source records were recently updated in either ref\_b or ref\_c.

I feel like this should be pretty straightforward but for some reason I’m having a hard time finding examples of others doing the same. All the examples I’ve seen only show joining on refs where both / all of the refs have been updated within the time window. We have fact tables with quite a few sources so ideally this would work across more than 2 tables

---

<div class="post-metadata">

**Author:** ![ktl](https://avatars.discourse-cdn.com/v4/letter/k/f9ae1b/32.png) [@ktl](https://discourse.getdbt.com/u/ktl)\
**Post date:** [May 31, 2023, 3:12pm UTC](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488/2 "2023-05-31T15:12:10Z")

</div>

**edit** : I think I figured out how to do it using `UNION`. Not sure how fast this will be but seems logically correct to me, using actual tables from my org:

```auto
with users_count as (
    SELECT
        cu.owned_by_organization_id,
        count(*) as user_count,
        max(cu.date_updated) as user_date_updated
    FROM
        user_user cu
    group by cu.owned_by_organization_id
),

sites_count as (
    SELECT
        si.owned_by_organization_id,
        count(*) as site_count,
        max(si.date_updated) as site_date_updated
    FROM
        site_site si
    group by si.owned_by_organization_id
),

user_org_ids as (
    SELECT owned_by_organization_id as org_id
    from users_count
    where users_count.user_date_updated > (select max(date_updated) from {{ this }})
),

sites_org_ids as (
    SELECT
        owned_by_organization_id as org_id
    FROM
        sites_count
    WHERE
        sites_count.site_date_updated > (select max(date_updated) from {{ this }})
),

org_final as (
    select
        org.*,
        users_count.*,
        sites_count.*
    from user_organization org
    left join users_count on users_count.owned_by_organization_id = org.id
    left join sites_count on sites_count.owned_by_organization_id = org.id
    {% if is_incremental() %}
    where org.id in (
        select org_id from user_org_ids
        union
        select org_id from sites_org_ids
    )
    {% endif %}
)

select count(*) from org_final

```

---

<div class="post-metadata">

**Author:** ![satish](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@satish](https://discourse.getdbt.com/u/satish)\
**Post date:** [January 30, 2025, 4:30pm UTC](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488/3 "2025-01-30T16:30:36Z")

</div>

hi , How did you managed this case , i am still struggling to get this use case work , If you have cracked it please let us know what is the best solution

---

<div class="post-metadata">

**Author:** ![marcelo](https://sea2.discourse-cdn.com/flex020/user_avatar/discourse.getdbt.com/marcelo/32/6126_2.png) [@marcelo](https://discourse.getdbt.com/u/marcelo)\
**Post date:** [January 31, 2025, 12:08am UTC](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488/4 "2025-01-31T00:08:43Z")

</div>

Hi @satish, doesn’t [the message just before yours](https://discourse.getdbt.com/t/incremental-from-multiple-refs-sources/8488/2) show how ktl figured out how to solve that?