[Solved] What will be the result of this dbt model SQL snippet?SELECT id, COUNT(*) AS cnt FROM ( SELECT id FROM {{ ref('source_a') }} UNION ALL SELECT id FROM {{ ref('source_b') }} ) combined GROUP BY id | dbt

dbt - Advanced Patterns

What will be the result of this dbt model SQL snippet?

SELECT id, COUNT(*) AS cnt
FROM (
  SELECT id FROM {{ ref('source_a') }}
  UNION ALL
  SELECT id FROM {{ ref('source_b') }}
) combined
GROUP BY id

AA table with unique ids and their total count from both sources combined

BA table with duplicate ids removed from both sources

CA table with ids only from source_a

DA table with ids only from source_b

Step-by-Step Solution

Solution:

Step 1: Understand UNION ALL behavior
UNION ALL stacks all rows from both sources including duplicates.
Step 2: Analyze aggregation
Grouping by id and counting rows gives total occurrences of each id across both sources.
Final Answer:
A table with unique ids and their total count from both sources combined -> Option A
Quick Check:
UNION ALL + GROUP BY = count per id [OK]

Quick Trick: UNION ALL keeps duplicates; GROUP BY counts them [OK]

Common Mistakes:

MISTAKES

Master "Advanced Patterns" in dbt

9 interactive learning modes - each teaches the same concept differently

More dbt Quizzes

What will be the result of this dbt model SQL snippet?