Why dbt transformed data transformation workflows - Visual Breakdown

Concept Flow - Why dbt transformed data transformation workflows

Raw Data in Warehouse

↓

Write SQL Models in dbt

↓

dbt Compiles & Runs SQL

↓

Transformed Tables/Views Created

↓

Data Analysts & BI Tools Use Clean Data

This flow shows how dbt takes raw data, applies SQL transformations, and produces clean data for analysis.

Execution Sample

dbt

select
  user_id,
  count(*) as orders_count
from raw.orders
group by user_id

This SQL model counts orders per user from raw data using dbt.

Execution Table

Step	Action	SQL Model State	Result
1	Start with raw.orders table	raw.orders (untransformed)	Raw data available
2	Write SQL model to count orders by user	Model SQL written	Ready to compile
3	dbt compiles SQL model	Compiled SQL query	Valid SQL ready to run
4	dbt runs SQL against warehouse	Executed query	orders_count per user calculated
5	dbt creates transformed table/view	orders_summary table created	Clean transformed data ready
6	Analysts query transformed data	orders_summary used	Faster, reliable analysis
7	Workflow complete	All steps done	Data transformation workflow finished

💡 All transformation steps completed successfully, clean data ready for use

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
raw.orders	Raw data table	Unchanged	Unchanged	Unchanged
SQL Model	Not written	SQL model with count query	Compiled and executed SQL	Executed and stored result
orders_summary	Not exist	Not exist	Created transformed table/view	Created transformed table/view

Key Moments - 2 Insights

Why do we write SQL models in dbt instead of running SQL directly in the warehouse?

dbt organizes SQL models with dependencies and version control, making transformations repeatable and maintainable, as shown in execution_table steps 2 and 3.

How does dbt ensure transformed data is reliable for analysts?

dbt runs tested SQL models and creates clean tables/views, so analysts query consistent data, as seen in steps 4 to 6 in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does dbt run the SQL query against the warehouse?

A Step 4

B Step 2

C Step 3

D Step 5

💡 Hint

Check the 'Action' column for when the SQL is executed against the warehouse.

According to variable_tracker, what is the state of 'orders_summary' after Step 4?

A Not exist

B Created transformed table/view

C Unchanged

D Compiled SQL query

💡 Hint

Look at the 'orders_summary' row and the 'After Step 4' column.

If we skip writing SQL models in dbt, which benefit from the workflow is lost?

A Raw data availability

B Data storage in warehouse

C Repeatable and maintainable transformations

D Analysts querying data

💡 Hint

Refer to key_moments about why SQL models are written in dbt.

Concept Snapshot

dbt transforms raw data by running SQL models in a controlled workflow.
Write SQL models that define transformations.
dbt compiles and runs these models in the warehouse.
Results are stored as tables or views.
Analysts use clean, reliable data for analysis.

Full Transcript

This visual execution shows how dbt changed data transformation workflows by organizing SQL transformations into models. Starting from raw data, you write SQL models in dbt that count or aggregate data. dbt compiles these models into executable SQL and runs them in the data warehouse. The results are saved as transformed tables or views. This process makes data transformations repeatable, maintainable, and reliable for analysts. The execution table traces each step from raw data to final transformed data ready for analysis. Variable tracking shows how data and models change state through the workflow. Key moments clarify why writing SQL models in dbt is important and how dbt ensures data reliability. The quiz tests understanding of when dbt runs SQL, the state of transformed tables, and the benefits of using dbt models.

Practice

1. What is one main reason dbt changed how data transformation workflows are done?

easy

A. It breaks complex data tasks into smaller, clear steps called models.

B. It replaces SQL with a new programming language.

C. It removes the need for testing data transformations.

D. It stores data in a new type of database automatically.

Solution

1. Step 1: Understand dbt's approach to data workflows

   dbt organizes data transformations into small, manageable pieces called models, making workflows clearer.

2. Step 2: Compare options to dbt's features

   Only It breaks complex data tasks into smaller, clear steps called models. correctly describes this key feature; others are incorrect or unrelated.

3. Final Answer:

   It breaks complex data tasks into smaller, clear steps called models. -> Option A

4. Quick Check:

   dbt uses models to simplify workflows = B [OK]

Hint: Remember: dbt splits work into models for clarity [OK]

Common Mistakes:

• Thinking dbt replaces SQL
• Believing dbt removes testing
• Assuming dbt changes database types

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

easy

A. dbt run my_model;

B. CREATE MODEL my_model AS SELECT * FROM source_table;

C. SELECT * FROM source_table;

D. DEFINE MODEL my_model SELECT * FROM source_table;

Solution

1. Step 1: Recall dbt model definition syntax

   In dbt, a model is defined simply by writing a SQL SELECT statement in a .sql file.

2. Step 2: Evaluate each option

   SELECT * FROM source_table; is just a SELECT statement, which is the correct way. The other options use incorrect syntax such as CREATE MODEL, dbt run command, or DEFINE MODEL.

3. Final Answer:

   SELECT * FROM source_table; -> Option C

4. Quick Check:

   dbt models are SQL SELECT queries = A [OK]

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

Common Mistakes:

• Trying to use CREATE MODEL syntax
• Using dbt commands inside SQL files
• Adding extra keywords like DEFINE

3. Given this dbt model SQL code:

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id

What will be the output of this model?

medium

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

B. A list of all orders without grouping.

C. An error because COUNT(*) cannot be used with GROUP BY.

D. A table with order_count but no customer_id.

Solution

1. Step 1: Analyze the SQL query

   The query groups orders by customer_id and counts orders per customer.

2. Step 2: Determine the output structure

   The output will have two columns: customer_id and order_count, showing total orders per customer.

3. Final Answer:

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

4. Quick Check:

   GROUP BY customer_id with COUNT(*) = grouped counts [OK]

Hint: GROUP BY + COUNT(*) gives counts per group [OK]

Common Mistakes:

• Thinking COUNT(*) can't be used with GROUP BY
• Expecting ungrouped list
• Missing customer_id in output

4. You wrote this dbt model SQL:

SELECT user_id, SUM(amount) AS total FROM sales

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

medium

A. dbt requires CREATE TABLE statements in models.

B. Missing GROUP BY clause for user_id in aggregation.

C. user_id is not a valid column name.

D. SUM(amount) cannot be used in dbt models.

Solution

1. Step 1: Identify the SQL error

   Using SUM(amount) with user_id requires GROUP BY user_id to aggregate correctly.

2. Step 2: Check options against SQL rules

   Missing GROUP BY clause for user_id in aggregation. correctly points out the missing GROUP BY clause causing the error.

3. Final Answer:

   Missing GROUP BY clause for user_id in aggregation. -> Option B

4. Quick Check:

   Aggregations need GROUP BY for non-aggregated columns [OK]

Hint: Always add GROUP BY for columns outside aggregation [OK]

Common Mistakes:

• Thinking SUM() is disallowed in dbt
• Assuming column names cause error without checking
• Expecting CREATE TABLE in dbt models

5. You want to build a dbt model that calculates the average order value per customer but only for customers with more than 5 orders. Which SQL snippet correctly implements this in dbt?

hard

A. SELECT customer_id, AVG(order_value) AS avg_value FROM orders HAVING COUNT(*) > 5 GROUP BY customer_id

B. SELECT customer_id, AVG(order_value) AS avg_value FROM orders WHERE COUNT(*) > 5 GROUP BY customer_id

C. SELECT customer_id, AVG(order_value) AS avg_value FROM orders GROUP BY customer_id WHERE COUNT(*) > 5

D. SELECT customer_id, AVG(order_value) AS avg_value FROM orders GROUP BY customer_id HAVING COUNT(*) > 5

Solution

1. Step 1: Understand filtering after grouping

   To filter groups by aggregate conditions, use HAVING after GROUP BY.

2. Step 2: Check SQL syntax correctness

   SELECT customer_id, AVG(order_value) AS avg_value FROM orders GROUP BY customer_id HAVING COUNT(*) > 5 correctly places HAVING COUNT(*) > 5 after GROUP BY customer_id.

3. Final Answer:

   SELECT customer_id, AVG(order_value) AS avg_value FROM orders GROUP BY customer_id HAVING COUNT(*) > 5 -> Option D

4. Quick Check:

   HAVING filters groups after GROUP BY = A [OK]

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

Common Mistakes:

• Using WHERE with aggregate functions
• Placing HAVING before GROUP BY
• Confusing order of clauses