What is dbt - Practice Questions & Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding dbt's primary function

What is the main purpose of dbt in data workflows?

A It visualizes data through dashboards and reports directly.

B It stores large volumes of raw data in a data lake without transformation.

C It transforms raw data into clean, tested datasets using SQL and version control.

D It collects data from external APIs and loads it into databases.

💡 Hint

Think about what dbt does after data is loaded into a warehouse.

dbt focuses on transforming and testing data inside the warehouse using SQL and version control, not on storage, visualization, or data collection.

🧠 Conceptual

intermediate

dbt's role in the ELT process

In the ELT (Extract, Load, Transform) process, where does dbt fit?

A Extracting data from source systems.

B Loading data into the data warehouse.

C Visualizing data for business users.

D Transforming data inside the data warehouse.

💡 Hint

dbt works after data is already loaded into the warehouse.

dbt is designed to transform data inside the warehouse after extraction and loading are complete.

❓ data_output

advanced

dbt model output structure

Given a dbt model that selects customer_id and total_sales from a raw sales table, what will be the structure of the resulting table after running dbt?

dbt

select customer_id, sum(sales_amount) as total_sales from raw_sales group by customer_id

A A table with raw sales data including all columns from raw_sales.

B A table with columns: customer_id, total_sales aggregated by customer.

C A table with only sales_amount column without aggregation.

D An error because aggregation functions are not allowed in dbt models.

💡 Hint

dbt models create tables or views based on the SQL query you write.

The model runs the SQL query and creates a table or view with the selected columns and aggregation as specified.

🔧 Debug

advanced

Identifying dbt model error

What error will occur when running this dbt model SQL?

select customer_id, total_sales from raw_sales group by customer_id

A Error: 'total_sales' is not aggregated or grouped.

B No error, query runs successfully.

C Error: 'customer_id' must be aggregated.

D Error: 'raw_sales' table does not exist.

💡 Hint

Check if all selected columns are either grouped or aggregated.

Since total_sales is selected but not aggregated or grouped, SQL will raise an error.

🚀 Application

expert

dbt testing output interpretation

After running dbt tests on a model, you see this output:

PASS test_not_null_customer_id (1 passed)

FAIL test_unique_order_id (2 failed)

What does this mean?

A All customer_id values are present, but order_id has duplicates in 2 rows.

B customer_id has nulls, and order_id is unique.

C Both customer_id and order_id columns have no issues.

D Tests did not run correctly due to syntax errors.

💡 Hint

Look at what PASS and FAIL indicate for each test.

PASS means the test succeeded (no nulls in customer_id). FAIL means duplicates found in order_id for 2 rows.

Practice

1. What is the main purpose of dbt in data projects?

easy

A. To transform raw data into clean, organized tables using SQL

B. To store large amounts of raw data without changes

C. To create visual dashboards directly from raw data

D. To replace databases with a new storage system

Solution

1. Step 1: Understand dbt's role in data transformation

   dbt is designed to help transform raw data into clean tables using SQL.

2. Step 2: Compare options with dbt's function

   Options A, B, and D describe storage or visualization, which are not dbt's main tasks.

3. Final Answer:

   To transform raw data into clean, organized tables using SQL -> Option A

4. Quick Check:

   dbt = data transformation tool [OK]

Hint: Remember dbt transforms data with SQL, not stores or visualizes [OK]

Common Mistakes:

• Confusing dbt with a database system
• Thinking dbt creates dashboards
• Assuming dbt only stores raw data

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

easy

A. CREATE MODEL my_model AS SELECT * FROM raw_data;

B. SELECT * FROM raw_data WHERE date > '2023-01-01';

C. dbt run SELECT * FROM raw_data;

D. INSERT INTO my_model SELECT * FROM raw_data;

Solution

1. Step 1: Identify how dbt models are written

   dbt models are SQL SELECT statements saved as files; no CREATE MODEL or INSERT commands are used.

2. Step 2: Check each option's syntax

   SELECT * FROM raw_data WHERE date > '2023-01-01'; is a valid SELECT query, suitable for a dbt model. Options A, C, and D use incorrect or unsupported syntax in dbt.

3. Final Answer:

   SELECT * FROM raw_data WHERE date > '2023-01-01'; -> Option B

4. Quick Check:

   dbt model = SQL SELECT query [OK]

Hint: dbt models are just SELECT queries saved as files [OK]

Common Mistakes:

• Using CREATE or INSERT statements in dbt models
• Trying to run dbt commands inside SQL files
• Confusing dbt syntax with database commands

3. Given this dbt model SQL code:

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

What will be the output of this model?

medium

A. A table with each user_id and their total number of orders

B. A list of all orders without grouping

C. An error because GROUP BY is missing

D. A table with user_id and order details for each order

Solution

1. Step 1: Analyze the SQL query

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

2. Step 2: Determine the output structure

   The output will be a table listing each user_id with their total orders count, not detailed orders or errors.

3. Final Answer:

   A table with each user_id and their total number of orders -> Option A

4. Quick Check:

   GROUP BY user_id = orders count per user [OK]

Hint: GROUP BY aggregates data by user_id for counts [OK]

Common Mistakes:

• Thinking the query returns all order details
• Assuming missing GROUP BY causes error here
• Confusing COUNT(*) with listing rows

4. You wrote this dbt model SQL:

SELECT user_id, SUM(order_amount) FROM orders

When you run dbt, you get an error. What is the likely cause?

medium

A. SELECT statement must include WHERE clause

B. SUM() function is not allowed in dbt

C. Table orders does not exist

D. Missing GROUP BY clause for user_id

Solution

1. Step 1: Check SQL aggregation rules

   When using SUM(order_amount) with user_id, SQL requires GROUP BY user_id to group data properly.

2. Step 2: Identify error cause

   Missing GROUP BY causes SQL error; SUM() is valid, table existence or WHERE clause are unrelated here.

3. Final Answer:

   Missing GROUP BY clause for user_id -> Option D

4. Quick Check:

   Aggregation needs GROUP BY user_id [OK]

Hint: Use GROUP BY with aggregation functions like SUM() [OK]

Common Mistakes:

• Thinking SUM() is invalid in dbt
• Assuming WHERE clause is mandatory
• Ignoring SQL aggregation rules

5. You want to create a dbt model that shows total sales per product category but only for categories with sales over 1000. Which SQL code correctly achieves this?

hard

A. SELECT category, SUM(sales) AS total_sales FROM sales_data WHERE sales > 1000 GROUP BY category

B. SELECT category, SUM(sales) AS total_sales FROM sales_data WHERE SUM(sales) > 1000 GROUP BY category

C. SELECT category, SUM(sales) AS total_sales FROM sales_data GROUP BY category HAVING SUM(sales) > 1000

D. SELECT category, SUM(sales) AS total_sales FROM sales_data GROUP BY category WHERE total_sales > 1000

Solution

1. Step 1: Understand filtering on aggregated data

   Filtering on SUM(sales) requires HAVING clause after GROUP BY, not WHERE.

2. Step 2: Evaluate each option's correctness

   SELECT category, SUM(sales) AS total_sales FROM sales_data GROUP BY category HAVING SUM(sales) > 1000 uses HAVING with SUM(sales) > 1000 correctly. Options A, B, and C misuse WHERE or HAVING clauses.

3. Final Answer:

   SELECT category, SUM(sales) AS total_sales FROM sales_data GROUP BY category HAVING SUM(sales) > 1000 -> Option C

4. Quick Check:

   Use HAVING to filter aggregated results [OK]

Hint: Use HAVING, not WHERE, to filter after aggregation [OK]

Common Mistakes:

• Using WHERE to filter aggregated sums
• Placing WHERE after GROUP BY
• Confusing HAVING and WHERE clauses