[Solved] You have three sources: sales_2022, sales_2023, and sales_2024. Each has columns order_id and amount.... Which approach best implements this multi-source fan-in pattern? | dbt

dbt - Advanced Patterns

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?

AUse FULL OUTER JOINs on <code>order_id</code> and COALESCE with order: 2024, 2023, 2022

BUse INNER JOINs on <code>order_id</code> and SUM amounts

CUse UNION ALL without any joins

DUse LEFT JOINs starting from 2022 to 2024

Step-by-Step Solution

Solution:

Step 1: Combine all sources keeping all orders
FULL OUTER JOINs ensure no orders are lost from any year.
Step 2: Use COALESCE to prefer latest year's amount
COALESCE(amount_2024, amount_2023, amount_2022) picks the newest available amount.
Final Answer:
Use FULL OUTER JOINs on order_id and COALESCE with order: 2024, 2023, 2022 -> Option A
Quick Check:
Fan-in with full joins + COALESCE = Use FULL OUTER JOINs on order_id and COALESCE with order: 2024, 2023, 2022 [OK]

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

Common Mistakes:

MISTAKES

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

Master "Advanced Patterns" in dbt

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More dbt Quizzes

Which approach best implements this multi-source fan-in pattern?

Step 1: Combine all sources keeping all orders

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

Final Answer:

Quick Check:

Want More Practice?