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.