What is dbt - Visual Explanation

Concept Flow - What is dbt

Write SQL models

↓

dbt compiles models

↓

dbt runs models on warehouse

↓

Transforms raw data

↓

Produces clean tables/views

↓

Enables analysis & reporting

dbt takes your SQL code, prepares it, runs it on your data warehouse, and creates clean tables for analysis.

Execution Sample

dbt

model.sql:
SELECT * FROM raw_data
WHERE status = 'active';

This SQL model selects only active records from raw_data to create a clean table.

Execution Table

Step	Action	Input	Output	Notes
1	Write SQL model	SQL code selecting active rows	SQL model file created	User writes transformation logic
2	Compile model	SQL model file	Compiled SQL query	dbt prepares SQL for warehouse
3	Run model	Compiled SQL query	New table/view in warehouse	dbt runs SQL to transform data
4	Transform data	Raw data in warehouse	Filtered active data table	Data is cleaned and ready
5	Use output	Clean table	Reports and dashboards	Analysts use clean data
6	End	All steps done	Clean data available	Process complete

💡 All models run and data transformed successfully

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
SQL Model	None	SQL code written	Compiled SQL query	Executed query	Clean table created	Clean table ready for use
Data Warehouse	Raw data only	Raw data only	Raw data only	New clean table added	Clean table updated	Clean table available

Key Moments - 2 Insights

Why does dbt compile SQL models before running them?

dbt compiles models to convert user-friendly SQL with variables and references into plain SQL that the data warehouse can understand, as shown in step 2 of the execution_table.

What happens to raw data during dbt run?

Raw data stays in the warehouse but dbt creates new clean tables or views based on transformations, shown in steps 3 and 4 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the output after step 3?

A New table/view in warehouse

B SQL model file created

C Compiled SQL query

D Clean table ready for use

💡 Hint

Check the 'Output' column for step 3 in the execution_table.

At which step does dbt transform raw data into clean tables?

A Step 1

B Step 2

C Step 4

D Step 5

💡 Hint

Look for the step where 'Filtered active data table' appears in the Output column.

If the SQL model is not written correctly, which step will fail?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Compilation of SQL happens at step 2; errors in SQL show up there.

Concept Snapshot

dbt (data build tool) lets you write SQL models
It compiles your SQL into queries
Runs them on your data warehouse
Transforms raw data into clean tables
These tables are ready for analysis and reporting

Full Transcript

dbt is a tool that helps you transform raw data into clean, usable tables by writing SQL models. First, you write SQL code that describes how to transform your data. Then, dbt compiles this code into plain SQL that your data warehouse understands. Next, dbt runs these queries on your warehouse, creating new tables or views with the transformed data. Finally, these clean tables can be used for analysis and reporting. This process makes data easier to work with and more reliable.

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