Incremental from multiple refs / sources

Help
, ,
2

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:

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
1 Like