[Solved] 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.... | dbt

dbt - Advanced Patterns

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

ACOALESCE cannot be used with JOINs

BThe JOIN should be a FULL OUTER JOIN to keep all rows

CThe SELECT statement is missing a GROUP BY clause

DThe table aliases 'a' and 'b' are not defined

Step-by-Step Solution

Solution:

Step 1: Check join type for fan-in pattern
Using INNER JOIN drops rows not matching in both tables, losing data.
Step 2: Correct join type for full data retention
FULL OUTER JOIN keeps all rows from both sources, essential for fan-in.
Final Answer:
The JOIN should be a FULL OUTER JOIN to keep all rows -> Option B
Quick Check:
Fan-in requires FULL OUTER JOIN = The JOIN should be a FULL OUTER JOIN to keep all rows [OK]

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

Common Mistakes:

MISTAKES

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

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

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

Step 1: Check join type for fan-in pattern

Step 2: Correct join type for full data retention

Final Answer:

Quick Check:

Want More Practice?