Why advanced patterns solve complex analytics in dbt - Challenge Your Understanding

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this dbt model SQL?

Given the following dbt model SQL, what will be the count of unique users in the final output?

dbt

with base as (
  select user_id, event_type
  from events
  where event_type in ('click', 'view')
),
filtered as (
  select distinct user_id
  from base
  where event_type = 'click'
)
select count(*) as unique_click_users from filtered

A The count of all users who had at least one 'click' event

B The count of all users who had at least one 'view' event

C The count of all users who had both 'click' and 'view' events

D The count of all events regardless of user or type

💡 Hint

Look at the filter in the 'filtered' CTE and what distinct does.

The 'filtered' CTE selects distinct user_ids where event_type is 'click'. The final count counts these unique users who clicked.

❓ data_output

intermediate

What does this dbt snapshot produce?

Consider a dbt snapshot that tracks changes in a 'customers' table. If a customer's email changes, what will the snapshot table contain?

dbt

snapshot customers_snapshot {
  strategy = 'timestamp'
  updated_at = 'last_updated'
  unique_key = 'customer_id'
}

select * from customers

A Only the latest row per customer_id with the current email

B A single row per customer_id with the first email ever recorded

C Multiple rows per customer_id showing each email change over time

D Rows only for customers whose email never changed

💡 Hint

Snapshots keep history of changes based on the strategy.

The timestamp strategy tracks changes by updated_at column, so each email change creates a new row with a new timestamp.

❓ visualization

advanced

Which visualization best shows user retention over time?

You have a table with user sign-up dates and their activity dates. Which chart type best shows how many users remain active each week after sign-up?

A Scatter plot of user age vs. activity count

B Bar chart showing total sign-ups per month

C Pie chart showing percentage of users by country

D Line chart showing count of active users by week since sign-up

💡 Hint

Retention is about tracking users over time since an event.

A line chart showing active users by week since sign-up clearly shows retention trends.

🧠 Conceptual

advanced

Why use incremental models in dbt for large datasets?

What is the main advantage of using incremental models in dbt when working with large data?

A They update only new or changed data, reducing processing time

B They create full copies of data every run for backup

C They automatically optimize SQL queries for speed

D They allow running models without any dependencies

💡 Hint

Think about how to avoid reprocessing all data every time.

Incremental models process only new or changed rows, saving time and resources on large datasets.

🔧 Debug

expert

What error does this dbt model produce?

This dbt model SQL is intended to join two tables but fails. What error will it raise?

dbt

select a.user_id, b.order_id
from users a
left join orders b
on a.user_id = b.user_id

A Syntax error near 'left join'

B Column 'a.id' does not exist

C Ambiguous column name 'user_id'

D No error, query runs successfully

💡 Hint

Check if the column names in join condition match the table aliases.

The users table alias 'a' does not have a column named 'id', causing a column not found error.

Practice

1. Why do advanced patterns in dbt help solve complex analytics problems?

easy

A. They break down complex tasks into smaller, manageable steps.

B. They make the code longer and harder to read.

C. They avoid using ref() and CTEs.

D. They remove the need for testing data models.

Solution

1. Step 1: Understand the role of advanced patterns

   Advanced patterns help by dividing complex analytics into smaller parts, making them easier to handle.

2. Step 2: Recognize the benefits in dbt

   Using features like ref() and CTEs supports this breakdown, improving clarity and maintainability.

3. Final Answer:

   They break down complex tasks into smaller, manageable steps. -> Option A

4. Quick Check:

   Advanced patterns simplify complexity = C [OK]

Hint: Think: smaller steps make big tasks easier [OK]

Common Mistakes:

• Assuming advanced patterns make code harder
• Ignoring the use of ref() in dbt
• Believing testing is not needed with advanced patterns

2. Which of the following is the correct way to reference another model in dbt using advanced patterns?

easy

A. SELECT * FROM ref('model_name')

B. SELECT * FROM model_name()

C. SELECT * FROM {{ ref('model_name') }}

D. SELECT * FROM {{ model('model_name') }}

Solution

1. Step 1: Recall dbt syntax for referencing models

   In dbt, the correct way to reference another model is using the Jinja function {{ ref('model_name') }}.

2. Step 2: Identify the correct SQL syntax

   The reference must be inside double curly braces and used as a table name in the FROM clause.

3. Final Answer:

   SELECT * FROM {{ ref('model_name') }} -> Option C

4. Quick Check:

   Use {{ ref() }} with curly braces = B [OK]

Hint: Remember: always use {{ ref('model') }} in dbt SQL [OK]

Common Mistakes:

• Omitting curly braces around ref()
• Using parentheses like a function call without {{ }}
• Confusing ref() with model()

3. Given this dbt model SQL code using CTEs, what will be the final output table's column total_sales?

WITH sales AS (
  SELECT customer_id, amount
  FROM {{ ref('raw_sales') }}
),
filtered_sales AS (
  SELECT customer_id, amount
  FROM sales
  WHERE amount > 100
)
SELECT customer_id, SUM(amount) AS total_sales
FROM filtered_sales
GROUP BY customer_id

medium

A. Sum of all sales amounts per customer including amounts <= 100

B. Sum of sales amounts per customer where each amount is greater than 100

C. Count of sales transactions per customer

D. Average sales amount per customer

Solution

1. Step 1: Analyze the filtering condition in filtered_sales CTE

   The CTE filters sales to only include rows where amount > 100.

2. Step 2: Understand the aggregation in the final SELECT

   The final query sums the filtered amounts per customer, so total_sales is sum of amounts > 100 per customer.

3. Final Answer:

   Sum of sales amounts per customer where each amount is greater than 100 -> Option B

4. Quick Check:

   Filter then sum = D [OK]

Hint: Filter first, then aggregate sums [OK]

Common Mistakes:

• Ignoring the WHERE filter on amount
• Confusing sum with count or average
• Assuming all sales are included

4. You wrote this dbt model but it fails with a compilation error. What is the likely cause?

WITH base AS (
  SELECT *
  FROM ref('orders')
)
SELECT * FROM base

medium

A. Missing curly braces around ref function

B. Incorrect CTE name 'base'

C. SELECT * is not allowed in dbt models

D. Missing GROUP BY clause

Solution

1. Step 1: Check the usage of ref() in the CTE

   The ref function must be wrapped in double curly braces like {{ ref('orders') }} to be recognized by dbt.

2. Step 2: Identify the error cause

   Without curly braces, dbt treats ref('orders') as plain text, causing a compilation error.

3. Final Answer:

   Missing curly braces around ref function -> Option A

4. Quick Check:

   Always use {{ ref() }} with curly braces = A [OK]

Hint: Always wrap ref() in {{ }} in dbt SQL [OK]

Common Mistakes:

• Forgetting curly braces around ref()
• Thinking CTE names cause errors
• Assuming SELECT * is invalid in dbt

5. You want to create a dbt model that calculates the average order amount per customer but only for customers with more than 5 orders. Which advanced pattern best helps you write this cleanly and efficiently?

hard

A. Calculate average without filtering orders count

B. Write a single SELECT with multiple JOINs without CTEs

C. Use multiple separate models without referencing each other

D. Use nested CTEs: one to count orders per customer, another to filter, then calculate average

Solution

1. Step 1: Understand the problem requirements

   You need to filter customers by order count > 5, then calculate average order amount.

2. Step 2: Identify the best advanced pattern

   Using nested CTEs breaks the problem into steps: count orders, filter customers, then compute average, making code clear and maintainable.

3. Final Answer:

   Use nested CTEs: one to count orders per customer, another to filter, then calculate average -> Option D

4. Quick Check:

   Break complex tasks into steps with CTEs = A [OK]

Hint: Use nested CTEs to split filtering and aggregation [OK]

Common Mistakes:

• Trying to do all in one complex query
• Not filtering customers before averaging
• Avoiding model references and CTEs