Creating your first model in dbt - Visual Walkthrough

Concept Flow - Creating your first model

Write SQL SELECT query

↓

Save as .sql file in models folder

↓

Run dbt run command

↓

dbt compiles and runs SQL

↓

Model table/view created in target database

↓

Check model output with dbt docs or SQL query

This flow shows how you write a SQL query, save it as a model file, run dbt to build it, and then check the resulting table or view.

Execution Sample

dbt

select
  customer_id,
  count(*) as order_count
from raw.orders
group by customer_id

This SQL query counts orders per customer from the raw.orders table, which will be saved as a dbt model.

Execution Table

Step	Action	Input/Code	Result/Output
1	Write SQL query	select customer_id, count(*) as order_count from raw.orders group by customer_id	SQL query ready in model file
2	Save file	models/customer_order_counts.sql	Model file saved
3	Run dbt	dbt run	dbt compiles SQL and runs it
4	dbt compiles	SQL with Jinja macros (if any)	Final SQL sent to database
5	Database executes	Final SQL	Table or view created with customer_id and order_count
6	Check output	select * from customer_order_counts limit 5	Sample rows of aggregated order counts
7	Exit	No more steps	Model created successfully

💡 Model created and data aggregated by customer_id, ready for analysis

Variable Tracker

Variable	Start	After Step 1	After Step 3	Final
SQL Query	empty	select customer_id, count(*) as order_count from raw.orders group by customer_id	compiled and run by dbt	table/view created in database
Model File	none	models/customer_order_counts.sql created	used by dbt run	exists in models folder
Database Table/View	none	none	created by dbt run	customer_order_counts with aggregated data

Key Moments - 3 Insights

Why do we save the SQL query as a .sql file in the models folder?

dbt looks for SQL files in the models folder to know which queries to run and build as models, as shown in execution_table step 2.

What happens when we run 'dbt run'?

dbt compiles the SQL files, applies any macros, and runs the final SQL on the database to create tables or views, as seen in execution_table steps 3 to 5.

How do we check if the model was created correctly?

We can query the new table or view in the database or use dbt docs to inspect it, as shown in execution_table step 6.

Visual Quiz - 3 Questions

Look at the execution table, what is the result after step 5?

A The SQL query is saved as a model file

B The database creates a table or view with aggregated data

C dbt run command is executed

D The model file is deleted

💡 Hint

Check the 'Result/Output' column in row for step 5 in execution_table

At which step does dbt compile the SQL query?

A Step 1

B Step 2

C Step 4

D Step 6

💡 Hint

Look at the 'Action' and 'Result/Output' columns for step 4 in execution_table

If you change the SQL query, which step must you repeat to update the model?

A Steps 1 and 3

B Step 3 only

C Step 1 only

D Step 6

💡 Hint

Refer to execution_table steps 1 and 3 where query is written and dbt run is executed

Concept Snapshot

Creating your first dbt model:
1. Write a SQL SELECT query to define your data transformation.
2. Save it as a .sql file in the models folder.
3. Run 'dbt run' to compile and execute the SQL.
4. dbt creates a table or view in your database.
5. Query or document the model to verify output.

Full Transcript

To create your first model in dbt, you start by writing a SQL query that selects and transforms data. You save this query as a .sql file inside the models folder of your dbt project. Then, you run the command 'dbt run' which compiles your SQL, applies any macros, and runs it on your database. This process creates a new table or view representing your model. Finally, you can check the model's output by querying the new table or using dbt's documentation tools. This step-by-step process helps you build reusable, tested data transformations easily.