How to Create a Model in dbt: Simple Steps and Example

To create a model in dbt, write a SQL SELECT statement in a file inside the models/ directory of your dbt project. The filename (without extension) becomes the model name, and dbt compiles and runs this SQL to build the model in your data warehouse.

Syntax

A dbt model is a SQL file that contains a SELECT statement. The basic syntax is:

• SELECT columns from tables or other models
• Save the file as model_name.sql inside the models/ folder
• The model name is the filename without .sql

dbt runs this SQL to create a table or view in your warehouse.

sql

SELECT column1, column2
FROM source_table
WHERE condition;

Example

This example creates a model named customer_orders that selects customer IDs and their total orders from a source table.

sql

-- File: models/customer_orders.sql
SELECT
  customer_id,
  COUNT(order_id) AS total_orders
FROM raw.orders
GROUP BY customer_id;

Output

customer_id | total_orders
------------|-------------
123         | 5
456         | 3
789         | 8

Common Pitfalls

Common mistakes when creating dbt models include:

• Not placing the SQL file inside the models/ directory, so dbt does not recognize it.
• Using CREATE TABLE or INSERT statements instead of just a SELECT statement.
• Filename and model name mismatch causing confusion.
• Referencing tables or models that do not exist or are misspelled.

sql

-- Wrong: Using CREATE TABLE
CREATE TABLE customer_orders AS
SELECT customer_id, COUNT(order_id) FROM raw.orders GROUP BY customer_id;

-- Right: Just SELECT statement
SELECT customer_id, COUNT(order_id) AS total_orders FROM raw.orders GROUP BY customer_id;

Quick Reference

Step	Description
1	Create a SQL file with a SELECT statement
2	Save it inside the models/ directory
3	Use the filename as the model name
4	Run dbt commands to build the model
5	Avoid CREATE or INSERT statements in model files

Key Takeaways

Create dbt models by writing SELECT queries in SQL files inside the models/ folder.

The model name is the SQL filename without the .sql extension.

Do not use CREATE or INSERT statements; dbt handles table creation.

Ensure your SQL references existing tables or models correctly.

Run dbt commands to compile and build your models in the warehouse.