Multi-source fan-in patterns in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this dbt model SQL?

Given two source tables sales and returns, this dbt model combines them using a UNION ALL and calculates net sales. What will be the output of the final query?

dbt

with sales_data as (
  select customer_id, sum(amount) as total_sales
  from {{ source('raw', 'sales') }}
  group by customer_id
),
returns_data as (
  select customer_id, sum(amount) as total_returns
  from {{ source('raw', 'returns') }}
  group by customer_id
),
combined as (
  select customer_id, total_sales, 0 as total_returns from sales_data
  union all
  select customer_id, 0 as total_sales, total_returns from returns_data
)
select customer_id, sum(total_sales) - sum(total_returns) as net_sales
from combined
group by customer_id
order by customer_id

A [{'customer_id': 1, 'net_sales': 100}, {'customer_id': 2, 'net_sales': -50}]

B [{'customer_id': 1, 'net_sales': 150}, {'customer_id': 2, 'net_sales': 0}]

C [{'customer_id': 1, 'net_sales': 100}, {'customer_id': 2, 'net_sales': 50}]

D [{'customer_id': 1, 'net_sales': 0}, {'customer_id': 2, 'net_sales': 50}]

💡 Hint

Think about how UNION ALL combines rows and how sums are aggregated after grouping.

The UNION ALL stacks sales and returns rows. Returns have positive amounts but are subtracted in the final sum, so net sales can be negative if returns exceed sales.

❓ data_output

intermediate

How many rows will the final output have?

Consider a dbt model that merges three source tables orders, shipments, and invoices using UNION ALL without filtering duplicates. If orders has 100 rows, shipments has 80 rows, and invoices has 120 rows, how many rows will the combined output have?

dbt

select * from {{ source('raw', 'orders') }}
union all
select * from {{ source('raw', 'shipments') }}
union all
select * from {{ source('raw', 'invoices') }}

A 120

B 280

C 100

D 300

💡 Hint

UNION ALL stacks all rows without removing duplicates.

UNION ALL combines all rows from the three tables, so total rows equal the sum of rows from each source.

🔧 Debug

advanced

Identify the error in this multi-source fan-in model

This dbt model tries to join two sources and then union with a third source. What error will this code raise?

dbt

with joined as (
  select a.id, a.value, b.status
  from {{ source('raw', 'table_a') }} a
  join {{ source('raw', 'table_b') }} b on a.id = b.id
),
unioned as (
  select * from joined
  union all
  select id, value, status from {{ source('raw', 'table_c') }}
)
select * from unioned

A Runtime error: column mismatch in UNION ALL

B SyntaxError due to missing comma

C No error, query runs successfully

D Runtime error: ambiguous column reference

💡 Hint

Check if the columns selected in both parts of the UNION ALL match exactly.

The first part selects three columns: id, value, status. The second part must select the same three columns in the same order and compatible types. If table_c has different columns or order, UNION ALL fails.

🚀 Application

advanced

Which approach best combines multiple sources with different schemas?

You have three source tables with different columns. You want to create a unified table with columns id, date, and amount. Which dbt SQL snippet correctly uses multi-source fan-in pattern to achieve this?

A

select id, date, amount from {{ source('raw', 'table1') }}
union all
select id, date from {{ source('raw', 'table2') }}
union all
select id, date, amount from {{ source('raw', 'table3') }}

B

select id, date, amount from {{ source('raw', 'table1') }}
union all
select id, date, amount from {{ source('raw', 'table2') }}
union all
select id, date, amount from {{ source('raw', 'table3') }}

C

select id, date, amount from {{ source('raw', 'table1') }}
union
select id, date, amount from {{ source('raw', 'table2') }}
union
select id, date, amount from {{ source('raw', 'table3') }}

D

select id, date, amount from {{ source('raw', 'table1') }}
union all
select id, date, amount, extra_col from {{ source('raw', 'table2') }}
union all
select id, date, amount from {{ source('raw', 'table3') }}

💡 Hint

All SELECT statements in UNION ALL must have the same number of columns and compatible types.

Option B selects the same columns from all tables, ensuring the UNION ALL works correctly. Option B has an extra column in one SELECT, causing error. Option B uses UNION which removes duplicates, not always desired. Option B misses the amount column in one SELECT, causing column mismatch.

🧠 Conceptual

expert

Why use multi-source fan-in patterns in dbt?

Which of the following best explains the main benefit of using multi-source fan-in patterns in dbt projects?

A To enforce strict schema validation on each source table individually

B To improve query performance by splitting data into multiple smaller tables

C To combine data from multiple sources into a single model for easier analysis and reporting

D To automatically generate documentation for all source tables

💡 Hint

Think about why you would want to merge data from different places into one place.

Multi-source fan-in patterns help combine data from different sources into one unified model, making it easier to analyze and report on combined data.

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