How to Create a Mart Model in dbt: Step-by-Step Guide

To create a mart model in dbt, define a SQL file in your models/marts/ folder that selects and transforms data for business use. Then, add the model to your dbt_project.yml and run dbt run to build the mart table.

Syntax

In dbt, a mart model is a SQL file placed inside the models/marts/ directory. The basic syntax includes a SELECT statement that transforms raw or intermediate data into a business-ready table.

• models/marts/your_mart_model.sql: SQL file defining the mart model.
• SELECT statement: Defines the data transformation logic.
• dbt_project.yml: Configuration file where you can specify model paths and materializations.

sql

-- models/marts/sales_mart.sql
SELECT
  customer_id,
  SUM(amount) AS total_sales,
  COUNT(order_id) AS order_count
FROM {{ ref('stg_orders') }}
GROUP BY customer_id

Example

This example creates a mart model named sales_mart that summarizes total sales and order counts per customer from a staging model stg_orders. It uses the ref() function to reference upstream models.

sql

-- models/marts/sales_mart.sql
SELECT
  customer_id,
  SUM(amount) AS total_sales,
  COUNT(order_id) AS order_count
FROM {{ ref('stg_orders') }}
GROUP BY customer_id

Output

customer_id | total_sales | order_count
------------|-------------|------------
1           | 1500.00     | 3
2           | 2300.50     | 5
3           | 980.00      | 2

Common Pitfalls

Common mistakes when creating mart models in dbt include:

• Not placing the model SQL file inside the models/marts/ folder, which can cause organization issues.
• Forgetting to use {{ ref('model_name') }} to reference upstream models, leading to broken dependencies.
• Not specifying materialization (like table or view) in dbt_project.yml or model config, which affects how the model is built.

sql

-- Wrong: Missing ref function
SELECT customer_id, SUM(amount) AS total_sales FROM stg_orders GROUP BY customer_id

-- Right: Using ref function
SELECT customer_id, SUM(amount) AS total_sales FROM {{ ref('stg_orders') }} GROUP BY customer_id

Quick Reference

Step	Description
Create SQL file	Place your mart model SQL in models/marts/ folder
Write SELECT query	Use {{ ref('upstream_model') }} to reference data
Configure materialization	Set materialization in dbt_project.yml or model config
Run dbt	Execute dbt run to build the mart model

Key Takeaways

Place mart model SQL files inside the models/marts/ directory for clarity.

Always use {{ ref('model_name') }} to reference upstream models safely.

Define your transformation logic with a clear SELECT statement.

Specify materialization to control how dbt builds your model.

Run dbt commands to compile and build your mart models.