Multi-source fan-in patterns in dbt - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a multi-source fan-in pattern in dbt?

It is a design where data from multiple sources is combined into a single model or table to create a unified view.

beginner

Why use multi-source fan-in patterns?

To simplify analysis by merging related data from different sources, making it easier to work with a single dataset.

intermediate

In dbt, how do you typically implement a multi-source fan-in pattern?

By creating a model that selects and joins data from multiple source tables or models using SQL.

intermediate

What is a common challenge when working with multi-source fan-in patterns?

Handling different data formats, keys, or update frequencies from each source to ensure consistent and accurate merging.

beginner

How can dbt's ref() function help in multi-source fan-in patterns?

It helps reference other models easily, ensuring dependencies are clear and dbt builds models in the right order.

What does a multi-source fan-in pattern do?

A Deletes duplicate data from a single source

B Splits one source into many models

C Creates backups of source data

D Combines data from multiple sources into one model

Multi-source fan-in means merging data from many sources into a single model.

Which dbt function helps manage dependencies in multi-source fan-in?

A ref()

B source()

C config()

D run()

ref() references other models and manages build order.

What is a key challenge in multi-source fan-in patterns?

A Writing Python code

B Joining data with different keys and formats

C Creating dashboards

D Deleting source tables

Different keys and formats make merging data tricky.

Why is multi-source fan-in useful?

A To create a single, unified dataset for analysis

B To split data into smaller pieces

C To archive old data

D To speed up data deletion

It simplifies analysis by unifying data.

In dbt, what language do you use to implement multi-source fan-in?

A Python

B HTML

C SQL

D JavaScript

dbt models are written in SQL.

Explain what a multi-source fan-in pattern is and why it is useful in dbt projects.

Think about merging data to make analysis easier.

Describe common challenges you might face when implementing multi-source fan-in patterns and how dbt helps address them.

Consider data differences and dbt features.

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