Multi-source fan-in patterns in dbt

Introduction

Multi-source fan-in patterns help combine data from many places into one spot. This makes it easier to analyze and understand your data.

When you have sales data from different stores and want to see total sales.

When combining customer info from multiple systems into one view.

When merging product details from various suppliers to get a full catalog.

When you want to create a report that uses data from different departments.

When cleaning and joining data from different sources before analysis.

Syntax

dbt

with source1 as (
    select * from {{ ref('source_table_1') }}
),
source2 as (
    select * from {{ ref('source_table_2') }}
)

select
    coalesce(s1.id, s2.id) as id,
    s1.value as value1,
    s2.value as value2
from source1 s1
full outer join source2 s2 on s1.id = s2.id

Use {{ ref('model_name') }} to refer to other dbt models or sources.

Full outer join helps keep all data from both sources, even if some ids don't match.

Examples

This example combines sales data from US and EU stores by order ID.

dbt

with sales_us as (
    select * from {{ ref('sales_us') }}
),
sales_eu as (
    select * from {{ ref('sales_eu') }}
)

select
    coalesce(us.order_id, eu.order_id) as order_id,
    us.amount as amount_us,
    eu.amount as amount_eu
from sales_us us
full outer join sales_eu eu on us.order_id = eu.order_id

This example merges customer info from two systems, keeping all customers.

dbt

with customers_a as (
    select * from {{ ref('customers_a') }}
),
customers_b as (
    select * from {{ ref('customers_b') }}
)

select
    coalesce(a.customer_id, b.customer_id) as customer_id,
    a.name as name_a,
    b.email as email_b
from customers_a a
full outer join customers_b b on a.customer_id = b.customer_id

Sample Program

This dbt model combines product names and quantities from two sources by matching their IDs.

dbt

with source1 as (
    select 1 as id, 'apple' as product
),
source2 as (
    select 1 as id, 100 as quantity
)

select
    coalesce(s1.id, s2.id) as id,
    s1.product,
    s2.quantity
from source1 s1
full outer join source2 s2 on s1.id = s2.id

Important Notes

Always check if your join keys match in data type and meaning.

Full outer join keeps all records, but you can use inner join if you want only matching data.

Use coalesce() to handle missing values from either source.

Summary

Multi-source fan-in patterns combine data from multiple tables or sources.

They help create a unified view for easier analysis.

Use full outer joins and coalesce() to keep all data and handle missing values.