Why models are the core of dbt - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple dbt model that selects all columns from the source table.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Aref('my_model')

Bdbt_model

Csource_table

Dstaging_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using ref() inside the select statement incorrectly.

Trying to select from a model name instead of a source table.

The source table is the raw data we select from in a dbt model. Using select * from source_table creates a basic model.

2fill in blank

medium

Complete the code to reference another dbt model named 'customers' inside a model.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Asource('raw', 'customers')

Bref('customers')

Cdbt.customers

Dcustomers_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using source() instead of ref() to reference a model.

Trying to use the model name directly without ref().

In dbt, ref('model_name') is used to reference another model. This ensures dependencies are tracked.

3fill in blank

hard

Fix the error in the dbt model code to correctly create a model that filters customers with more than 100 orders.

dbt

select * from [1] where order_count > 100

Drag options to blanks, or click blank then click option'

Aref(customers)

Bsource('raw', 'customers')

Ccustomers

Dref('customers')

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting quotes inside ref().

Using the model name directly without ref().

The correct way to reference a model in dbt is using ref('model_name') with quotes around the model name.

4fill in blank

hard

Fill both blanks to create a dbt model that calculates total sales per customer and orders results by total sales descending.

dbt

select customer_id, sum(sales) as total_sales from [1] group by customer_id order by [2] desc

Drag options to blanks, or click blank then click option'

Aref('orders')

Btotal_sales

Ccustomer_id

Dsource('raw', 'orders')

Attempts:

3 left

💡 Hint

Common Mistakes

Ordering by a column not selected or aliased.

Using source() instead of ref() for models.

We reference the 'orders' model using ref('orders'). Then we order by the alias total_sales descending.

5fill in blank

hard

Fill all three blanks to create a dbt model that joins customers and orders, selecting customer name and total order amount.

dbt

select c.customer_name, sum(o.amount) as total_amount from [1] c join [2] o on c.customer_id = o.customer_id group by [3]

Drag options to blanks, or click blank then click option'

Aref('customers')

Bref('orders')

Cc.customer_name

Do.customer_id

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by a column not in select.

Using table names directly without ref().

We reference the customers and orders models with ref(). Grouping is done by the customer name alias c.customer_name.

Practice

1. What is the main role of models in dbt?

easy

A. To transform raw data into useful tables or views

B. To store raw data without changes

C. To create visual dashboards

D. To manage user permissions

Solution

1. Step 1: Understand the purpose of models in dbt

   Models are SQL files that define how raw data is transformed into clean, organized tables or views.

2. Step 2: Identify the correct role from options

   Only To transform raw data into useful tables or views describes transforming raw data into useful tables or views, which is the core function of models.

3. Final Answer:

   To transform raw data into useful tables or views -> Option A

4. Quick Check:

   Models transform data [OK]

Hint: Models transform raw data into tables/views [OK]

Common Mistakes:

• Confusing models with dashboards
• Thinking models store raw data unchanged
• Assuming models manage permissions

2. Which of the following is the correct way to define a model in dbt?

easy

A. models/my_model.yaml containing configuration only

B. models/my_model.py containing Python code

C. models/my_model.txt containing raw data

D. models/my_model.sql containing a SELECT statement

Solution

1. Step 1: Recall dbt model file requirements

   dbt models are SQL files that contain SELECT statements to transform data.

2. Step 2: Match file type and content

   Only models/my_model.sql containing a SELECT statement uses a .sql file with a SELECT statement, which is correct for a dbt model.

3. Final Answer:

   models/my_model.sql containing a SELECT statement -> Option D

4. Quick Check:

   Model = SQL file with SELECT [OK]

Hint: Models are SQL files with SELECT statements [OK]

Common Mistakes:

• Using Python or text files for models
• Confusing config files with models
• Not including a SELECT statement in model files

3. Given this dbt model SQL code:

SELECT user_id, COUNT(*) AS orders_count FROM raw.orders GROUP BY user_id

What will this model produce when run?

medium

A. A table or view with user_id and their total order counts

B. A list of all orders without grouping

C. An error because COUNT(*) is invalid

D. A table with only user_id and no counts

Solution

1. Step 1: Analyze the SQL query in the model

   The query selects user_id and counts orders grouped by user_id, aggregating orders per user.

2. Step 2: Determine the output of the model

   The model will create a table or view showing each user_id with their total number of orders.

3. Final Answer:

   A table or view with user_id and their total order counts -> Option A

4. Quick Check:

   GROUP BY user_id with COUNT(*) = aggregated counts [OK]

Hint: GROUP BY with COUNT(*) gives totals per group [OK]

Common Mistakes:

• Ignoring GROUP BY and expecting raw data
• Thinking COUNT(*) causes errors
• Assuming counts are missing

4. You wrote this dbt model SQL:

SELECT customer_id, date, SUM(amount) AS total FROM sales GROUP BY customer_id

But dbt throws an error. What is the likely problem?

medium

A. SUM(amount) cannot be used with GROUP BY

B. The SELECT includes date but GROUP BY does not, causing mismatch

C. customer_id should be aggregated with SUM()

D. Missing WHERE clause causes error

Solution

1. Step 1: Check SELECT and GROUP BY columns

   SELECT has customer_id, date, and SUM(amount), but GROUP BY includes only customer_id.

2. Step 2: Identify mismatch causing error

   All non-aggregated columns in SELECT must be in GROUP BY. date is missing in GROUP BY, causing error.

3. Final Answer:

   The SELECT includes date but GROUP BY does not, causing mismatch -> Option B

4. Quick Check:

   GROUP BY columns must match SELECT non-aggregates [OK]

Hint: SELECT non-aggregates must match GROUP BY columns [OK]

Common Mistakes:

• Ignoring GROUP BY and SELECT column mismatch
• Thinking SUM() can't be used with GROUP BY
• Assuming WHERE clause is mandatory

5. You want to create a dbt model that builds a monthly sales summary table. Which approach best uses models as the core of dbt?

hard

A. Create a YAML file listing monthly sales without SQL

B. Manually export raw sales data and summarize in Excel

C. Write a SQL model that selects sales data, groups by month, and calculates totals

D. Use a Python script outside dbt to summarize sales

Solution

1. Step 1: Identify how models transform data in dbt

   Models are SQL files that transform raw data into organized tables, like monthly summaries.

2. Step 2: Choose the option that uses dbt models correctly

   Write a SQL model that selects sales data, groups by month, and calculates totals uses a SQL model to group and summarize sales by month, fitting dbt's core purpose.

3. Final Answer:

   Write a SQL model that selects sales data, groups by month, and calculates totals -> Option C

4. Quick Check:

   Models transform data with SQL for summaries [OK]

Hint: Use SQL models to transform and summarize data [OK]

Common Mistakes:

• Using external tools instead of dbt models
• Confusing YAML config with data transformation
• Ignoring dbt's SQL model workflow