Multi-source fan-in patterns in dbt - Mini Project: Build & Apply

Multi-source Fan-in Patterns with dbt

📖 Scenario: You work as a data analyst at a retail company. You have sales data coming from two different sources: online_sales and store_sales. Your goal is to combine these two sources into a single table that shows total sales per product.

🎯 Goal: Build a dbt model that uses multi-source fan-in patterns to combine online_sales and store_sales tables into one table showing total sales per product.

📋 What You'll Learn

Create source definitions for online_sales and store_sales

Create a staging model for each source

Create a final model that combines both staging models using UNION ALL

Aggregate total sales per product in the final model

💡 Why This Matters

🌍 Real World

Retail companies often have sales data from multiple channels. Combining these sources helps get a complete view of product performance.

💼 Career

Data analysts and engineers use multi-source fan-in patterns in dbt to build clean, combined datasets for reporting and analysis.

1

Define sources for online_sales and store_sales

Create a sources.yml file with source definitions for online_sales and store_sales tables under the schema raw. Use source names online and store respectively.

dbt

# Create source definitions for online_sales and store_sales
# Your code here

Hint

Use the sources key and define two sources named online and store with their respective tables.

2

Create staging models for each source

Create two staging models: stg_online_sales.sql and stg_store_sales.sql. In each, select all columns from their respective sources using the source() function. For example, in stg_online_sales.sql, select all from source('online', 'online_sales').

dbt

version: 2

sources:
  - name: online
    schema: raw
    tables:
      - name: online_sales
  - name: store
    schema: raw
    tables:
      - name: store_sales

-- In stg_online_sales.sql
-- Your code here

-- In stg_store_sales.sql
-- Your code here

Hint

Use the source() function inside your SQL models to select all data from each source table.

3

Create a final model combining both staging models

Create a model called final_sales.sql that combines stg_online_sales and stg_store_sales using UNION ALL. Select product_id and sales_amount from both staging models.

dbt

version: 2

sources:
  - name: online
    schema: raw
    tables:
      - name: online_sales
  - name: store
    schema: raw
    tables:
      - name: store_sales

-- stg_online_sales.sql
select * from {{ source('online', 'online_sales') }}

-- stg_store_sales.sql
select * from {{ source('store', 'store_sales') }}

-- Create final_sales.sql model combining both staging models
-- Your code here

Hint

Use UNION ALL to combine the two staging models. Use ref() to refer to the staging models.

4

Aggregate total sales per product in the final model

Modify final_sales.sql to aggregate total sales per product_id. Use a SELECT statement that sums sales_amount grouped by product_id from the union of both staging models.

dbt

version: 2

sources:
  - name: online
    schema: raw
    tables:
      - name: online_sales
  - name: store
    schema: raw
    tables:
      - name: store_sales

-- stg_online_sales.sql
select * from {{ source('online', 'online_sales') }}

-- stg_store_sales.sql
select * from {{ source('store', 'store_sales') }}

-- Modify final_sales.sql to aggregate total sales per product
-- Your code here

Hint

Use a subquery to combine both staging models with UNION ALL, then group by product_id and sum sales_amount.

Practice

1.

What is the main purpose of a multi-source fan-in pattern in dbt?

easy

A. To create indexes for faster queries

B. To split data into multiple smaller tables

C. To delete duplicate records from a single table

D. To combine data from multiple sources into one unified table

Solution

1. Step 1: Understand multi-source fan-in pattern

   This pattern is used to bring together data from different tables or sources.

2. Step 2: Identify the goal of combining data

   The goal is to create a single, unified view that contains all relevant information for analysis.

3. Final Answer:

   To combine data from multiple sources into one unified table -> Option D

4. Quick Check:

   Multi-source fan-in = combine data [OK]

Hint: Think 'fan-in' as merging many into one [OK]

Common Mistakes:

• Confusing fan-in with splitting data
• Thinking it deletes duplicates
• Assuming it creates indexes

2.

Which of the following is the correct syntax to perform a full outer join in dbt's SQL model?

SELECT * FROM source_a a
FULL OUTER JOIN source_b b
ON a.id = b.id

easy

A. SELECT * FROM source_a a FULL OUTER JOIN source_b b ON a.id = b.id

B. SELECT * FROM source_a a INNER JOIN source_b b ON a.id = b.id

C. SELECT * FROM source_a a LEFT JOIN source_b b ON a.id = b.id

D. SELECT * FROM source_a a CROSS JOIN source_b b

Solution

1. Step 1: Identify the join type needed for fan-in

   Multi-source fan-in requires a full outer join to keep all records from both sources.

2. Step 2: Match the syntax for full outer join

   The correct syntax is FULL OUTER JOIN with the join condition.

3. Final Answer:

   SELECT * FROM source_a a FULL OUTER JOIN source_b b ON a.id = b.id -> Option A

4. Quick Check:

   Full outer join syntax = SELECT * FROM source_a a FULL OUTER JOIN source_b b ON a.id = b.id [OK]

Hint: Full outer join keeps all rows from both tables [OK]

Common Mistakes:

• Using INNER JOIN which drops unmatched rows
• Using LEFT JOIN which keeps only left table rows
• Using CROSS JOIN which creates Cartesian product

3.

Given the following dbt model SQL, what will be the output for final_table?

WITH source_a AS (
  SELECT 1 AS id, 'A' AS val
  UNION ALL
  SELECT 2, 'B'
),
source_b AS (
  SELECT 2 AS id, 'X' AS val
  UNION ALL
  SELECT 3, 'Y'
)
SELECT
  COALESCE(a.id, b.id) AS id,
  COALESCE(a.val, b.val) AS val
FROM source_a a
FULL OUTER JOIN source_b b ON a.id = b.id
ORDER BY id

medium

A. [{id:1, val:'A'}, {id:2, val:'B'}, {id:3, val:null}]

B. [{id:1, val:'A'}, {id:2, val:'X'}, {id:3, val:'Y'}]

C. [{id:1, val:'A'}, {id:2, val:'B'}, {id:3, val:'Y'}]

D. [{id:1, val:null}, {id:2, val:'B'}, {id:3, val:'Y'}]

Solution

1. Step 1: Perform full outer join on id

   Ids 1 and 3 appear only in one source, id 2 appears in both.

2. Step 2: Use COALESCE to pick non-null values

   For id=2, COALESCE(a.val, b.val) picks 'B' from source_a since it is first.

3. Final Answer:

   [{id:1, val:'A'}, {id:2, val:'B'}, {id:3, val:'Y'}] -> Option C

4. Quick Check:

   COALESCE picks first non-null val = [{id:1, val:'A'}, {id:2, val:'B'}, {id:3, val:'Y'}] [OK]

Hint: COALESCE picks first non-null value in order [OK]

Common Mistakes:

• Assuming COALESCE picks from source_b first
• Ignoring full outer join rows
• Mixing up values for id=2

4.

Identify the error in this dbt model SQL that attempts a multi-source fan-in:

WITH source_a AS (
  SELECT id, val FROM table_a
),
source_b AS (
  SELECT id, val FROM table_b
)
SELECT
  COALESCE(a.id, b.id) AS id,
  COALESCE(a.val, b.val) AS val
FROM source_a a
JOIN source_b b ON a.id = b.id

medium

A. COALESCE cannot be used with JOINs

B. The JOIN should be a FULL OUTER JOIN to keep all rows

C. The SELECT statement is missing a GROUP BY clause

D. The table aliases 'a' and 'b' are not defined

Solution

1. Step 1: Check join type for fan-in pattern

   Using INNER JOIN drops rows not matching in both tables, losing data.

2. Step 2: Correct join type for full data retention

   FULL OUTER JOIN keeps all rows from both sources, essential for fan-in.

3. Final Answer:

   The JOIN should be a FULL OUTER JOIN to keep all rows -> Option B

4. Quick Check:

   Fan-in requires FULL OUTER JOIN = The JOIN should be a FULL OUTER JOIN to keep all rows [OK]

Hint: Use FULL OUTER JOIN to keep all rows from both sources [OK]

Common Mistakes:

• Using INNER JOIN instead of FULL OUTER JOIN
• Thinking COALESCE is invalid here
• Forgetting to alias tables

5.

You have three sources: sales_2022, sales_2023, and sales_2024. Each has columns order_id and amount. You want a unified table with all orders and amounts, preferring the latest year's amount if duplicates exist. Which approach best implements this multi-source fan-in pattern?

hard

A. Use FULL OUTER JOINs on order_id and COALESCE with order: 2024, 2023, 2022

B. Use INNER JOINs on order_id and SUM amounts

C. Use UNION ALL without any joins

D. Use LEFT JOINs starting from 2022 to 2024

Solution

1. Step 1: Combine all sources keeping all orders

   FULL OUTER JOINs ensure no orders are lost from any year.

2. Step 2: Use COALESCE to prefer latest year's amount

   COALESCE(amount_2024, amount_2023, amount_2022) picks the newest available amount.

3. Final Answer:

   Use FULL OUTER JOINs on order_id and COALESCE with order: 2024, 2023, 2022 -> Option A

4. Quick Check:

   Fan-in with full joins + COALESCE = Use FULL OUTER JOINs on order_id and COALESCE with order: 2024, 2023, 2022 [OK]

Hint: Full outer joins + COALESCE picks latest non-null value [OK]

Common Mistakes:

• Using INNER JOIN loses unmatched orders
• Using UNION ALL duplicates orders without preference
• Using LEFT JOINs misses some data