Naming conventions at scale in dbt

Introduction

Good naming helps everyone understand and find data easily. It keeps projects clear and organized as they grow.

When working on a big data project with many tables and models.

When multiple people or teams collaborate on the same dbt project.

When you want to avoid confusion between similar data sources or tables.

When you need to automate or document your data pipeline clearly.

When maintaining data models over time and want to keep them consistent.

Syntax

dbt

-- Example naming pattern for dbt models
{{
  config(
    materialized='table',
    alias='stg_customers'
  )
}}

-- File name: stg_customers.sql

-- Table name: stg_customers

Use prefixes like stg_ for staging tables, int_ for intermediate, and fct_ for fact tables.

Keep names lowercase and use underscores to separate words for readability.

Examples

This creates a staging table named stg_customers to hold raw customer data.

dbt

-- Staging table for raw customer data
{{ config(materialized='table', alias='stg_customers') }}

select * from raw.customers

This creates a view named int_customers_clean with only active customers.

dbt

-- Intermediate table for cleaned customer data
{{ config(materialized='view', alias='int_customers_clean') }}

select * from {{ ref('stg_customers') }} where active = true

This creates a fact table fct_customer_orders summarizing orders per customer.

dbt

-- Fact table for customer orders
{{ config(materialized='table', alias='fct_customer_orders') }}

select customer_id, count(*) as order_count from {{ ref('int_customers_clean') }} join raw.orders using (customer_id) group by customer_id

Sample Program

This example shows a simple dbt project with three models using naming conventions: staging (stg_), intermediate (int_), and fact (fct_) tables. It helps keep the project organized and clear.

dbt

-- dbt model: stg_customers.sql
{{ config(materialized='table', alias='stg_customers') }}

select * from raw.customers

-- dbt model: int_customers_clean.sql
{{ config(materialized='view', alias='int_customers_clean') }}

select * from {{ ref('stg_customers') }} where active = true

-- dbt model: fct_customer_orders.sql
{{ config(materialized='table', alias='fct_customer_orders') }}

select customer_id, count(*) as order_count
from {{ ref('int_customers_clean') }}
join raw.orders using (customer_id)
group by customer_id

Important Notes

Consistent naming helps when searching or filtering models in dbt Cloud or IDEs.

Use clear prefixes to quickly know the purpose of each model.

Document your naming rules in your project README for team clarity.

Summary

Use simple, consistent prefixes like stg_, int_, and fct_ to organize models.

Keep names lowercase and separate words with underscores for readability.

Good naming makes large dbt projects easier to understand and maintain.