Multi-source fan-in patterns in dbt - Step-by-Step Execution

Concept Flow - Multi-source fan-in patterns

Source Table A

↓

Transform A

↓

Fan-in Join

↓

Source Table B

↓

Transform B

↓

Final Output Table

Data flows from multiple source tables through transformations, then joins together in a fan-in pattern to create a combined output.

Execution Sample

dbt

with a as (
  select id, value from source_a
), b as (
  select id, value from source_b
)
select a.id, a.value, b.value as b_value
from a
join b on a.id = b.id

This SQL code combines data from two sources by joining on a common id.

Execution Table

Step	Action	Evaluation	Result
1	Read source_a	Table source_a loaded	Rows: 3 (id=1,2,3)
2	Read source_b	Table source_b loaded	Rows: 3 (id=2,3,4)
3	Transform a	Select id, value	a: [(1,10), (2,20), (3,30)]
4	Transform b	Select id, value	b: [(2,200), (3,300), (4,400)]
5	Join a and b on id	Match ids in both	Joined rows: [(2,20,200), (3,30,300)]
6	Output final table	Combined data	Final rows: 2 with columns (id, value, b_value)

💡 Join only includes ids present in both sources, so id=1 and id=4 are excluded.

Variable Tracker

Variable	Start	After Step 3	After Step 4	After Step 5	Final
a	empty	[(1,10), (2,20), (3,30)]	[(1,10), (2,20), (3,30)]	[(2,20), (3,30)]	[(2,20), (3,30)]
b	empty	empty	[(2,200), (3,300), (4,400)]	[(2,200), (3,300)]	[(2,200), (3,300)]
joined	empty	empty	empty	[(2,20,200), (3,30,300)]	[(2,20,200), (3,30,300)]

Key Moments - 2 Insights

Why are some ids missing from the final output?

Because the join only includes ids present in both tables, ids like 1 (only in a) and 4 (only in b) are excluded as shown in execution_table step 5.

What happens if one source has more rows than the other?

Only matching rows based on the join condition appear in the final output, so extra rows without matches are dropped, as seen in variable_tracker for 'joined' after step 5.

Visual Quiz - 3 Questions

Look at the execution_table at step 5, how many rows are in the joined result?

A 2

B 3

C 4

D 1

💡 Hint

Check the 'Joined rows' column in execution_table step 5.

According to variable_tracker, what is the value of variable 'a' after step 3?

A [(2,200), (3,300), (4,400)]

B empty

C [(1,10), (2,20), (3,30)]

D [(2,20), (3,30)]

💡 Hint

Look at the row for 'a' under 'After Step 3' in variable_tracker.

If source_b had an extra row with id=5, how would the final output change?

A Final output would include id=5

B Final output would remain the same

C Final output would include all ids from source_b

D Final output would include all ids from source_a

💡 Hint

Recall the join only includes ids present in both sources as shown in execution_table step 5.

Concept Snapshot

Multi-source fan-in pattern:
- Extract data from multiple sources
- Transform each source separately
- Join transformed data on common keys
- Result is combined dataset with matching records only
- Useful for merging related data from different tables

Full Transcript

This visual execution shows how multi-source fan-in patterns work in dbt. We start by reading two source tables, source_a and source_b. Each is transformed by selecting relevant columns. Then, these transformed tables are joined on a common id column. The join keeps only rows where ids match in both tables, excluding unmatched rows. Variables 'a' and 'b' hold transformed data, and 'joined' holds the combined result. This pattern helps combine data from multiple sources into one clean output table.

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