Creating your first model in dbt - Practice Exercises

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 resulting table's row count if the source table has 100 rows with 20 rows having NULL in the column status?

dbt

select * from {{ source('raw_data', 'orders') }} where status is not null

A 0 rows

B 100 rows

C 20 rows

D 80 rows

💡 Hint

Think about how the WHERE clause filters rows with NULL status.

The WHERE clause excludes rows where status is NULL, so only 80 rows remain.

❓ data_output

intermediate

What columns will the model output have?

Consider this dbt model SQL that selects specific columns from a source table. What columns will the resulting model table contain?

dbt

select order_id, customer_id, order_date from {{ source('raw_data', 'orders') }}

A All columns from orders table

B order_id, customer_id, order_date

C Only order_id

D order_id, customer_id, order_date, status

💡 Hint

Look at the SELECT statement columns.

The model explicitly selects only order_id, customer_id, and order_date columns.

🔧 Debug

advanced

Why does this dbt model fail to compile?

This dbt model SQL throws a compilation error. What is the cause?

dbt

select * from {{ source('raw_data', orders) }}

A Missing quotes around the table name 'orders' in the source function

B Missing schema name in the source function

C Using * is not allowed in dbt models

D The source function requires three arguments

💡 Hint

Check the syntax of the source function arguments.

The table name must be a string literal, so it needs quotes.

🚀 Application

advanced

How to create a model that aggregates total sales per customer?

You want to create a dbt model that shows total sales amount per customer from the orders source table with columns customer_id and amount. Which SQL snippet correctly achieves this?

A select customer_id, amount from {{ source('raw_data', 'orders') }} group by customer_id

B select customer_id, sum(amount) from {{ source('raw_data', 'orders') }}

C select customer_id, sum(amount) as total_sales from {{ source('raw_data', 'orders') }} group by customer_id

D select customer_id, total(amount) as total_sales from {{ source('raw_data', 'orders') }} group by customer_id

💡 Hint

Remember to use GROUP BY when aggregating and alias the sum.

Option C correctly sums amounts per customer and groups by customer_id.

🧠 Conceptual

expert

What is the main benefit of using dbt models for data transformation?

Choose the best explanation for why dbt models are useful in data projects.

A They allow version-controlled, modular SQL transformations that build a clear data pipeline

B They automatically clean data without writing any SQL code

C They replace the need for any database or data warehouse

D They provide a graphical interface to drag and drop data transformations

💡 Hint

Think about how dbt helps organize and manage SQL code.

dbt models let you write SQL in files that are version controlled and build a dependency graph for transformations.