Model naming conventions in dbt - Step-by-Step Execution

Concept Flow - Model naming conventions

Start: Define model purpose

↓

Choose clear, descriptive name

↓

Use lowercase letters and underscores

↓

Avoid spaces and special characters

↓

Add prefixes/suffixes if needed (e.g., stg_, int_, fct_)

↓

Check for uniqueness in project

↓

Save model with chosen name

↓

Use name consistently in references

This flow shows how to pick clear, consistent names for dbt models to keep projects organized and easy to understand.

Execution Sample

dbt

stg_customers.sql
int_orders.sql
fct_sales.sql

Example model file names showing common prefixes for staging, intermediate, and fact models.

Execution Table

Step	Action	Input/Name	Validation	Result
1	Define model purpose	Customer raw data	Purpose clear	Proceed
2	Choose descriptive name	stg_customers	Lowercase, underscores	Valid
3	Check for spaces/special chars	stg_customers	No spaces or special chars	Valid
4	Add prefix for layer	stg_	Prefix matches staging layer	Valid
5	Check uniqueness	stg_customers	No duplicate model names	Unique
6	Save model file	stg_customers.sql	File saved with correct name	Success
7	Use name in references	ref('stg_customers')	Matches model file name	Reference works
8	End			Naming convention followed

💡 All steps passed, model named correctly following conventions.

Variable Tracker

Variable	Start	After Step 2	After Step 4	Final
model_name		stg_customers	stg_customers	stg_customers

Key Moments - 3 Insights

Why do we use prefixes like 'stg_' or 'fct_' in model names?

Prefixes help identify the model's role or layer in the project, making it easier to organize and understand the data flow. See execution_table steps 4 and 5.

Can model names have spaces or capital letters?

No, model names should be lowercase and use underscores instead of spaces to avoid errors and keep consistency. See execution_table step 3.

What happens if two models have the same name?

It causes conflicts and errors in dbt. Always check for uniqueness before saving. See execution_table step 5.

Visual Quiz - 3 Questions

Look at the execution table, what prefix is used for staging models?

A stg_

B int_

C fct_

D raw_

💡 Hint

Check Step 4 in the execution_table where prefix is added.

At which step is the model name checked for spaces or special characters?

A Step 2

B Step 5

C Step 3

D Step 6

💡 Hint

Look at the 'Validation' column in execution_table for Step 3.

If a model name had uppercase letters, which step would fail?

A Step 1

B Step 2

C Step 3

D Step 5

💡 Hint

Step 2 checks for lowercase letters and underscores.

Concept Snapshot

Model naming conventions in dbt:
- Use clear, descriptive names
- Use lowercase letters and underscores
- Avoid spaces and special characters
- Add prefixes like stg_, int_, fct_ for layers
- Ensure names are unique in the project
- Use consistent names in references

Full Transcript

This visual execution shows how to name dbt models properly. First, decide what the model does. Then pick a clear name using lowercase letters and underscores. Avoid spaces or special characters. Add a prefix like stg_ for staging models to show their role. Check that the name is unique in your project to avoid conflicts. Save the model file with this name and use it consistently when referencing the model in your dbt project. Following these steps keeps your project organized and easy to understand.

Practice

1. Which of the following is the best practice for naming a dbt model?

easy

A. Use clear, lowercase names with underscores to separate words

B. Use uppercase letters and spaces between words

C. Use special characters like # or $ in the name

D. Use very long names without any separators

Solution

1. Step 1: Understand dbt naming conventions

   dbt recommends using clear, lowercase names with underscores to separate words for readability and consistency.

2. Step 2: Evaluate each option

   Use clear, lowercase names with underscores to separate words follows the recommended style. Options A, B, and D do not follow best practices.

3. Final Answer:

   Use clear, lowercase names with underscores to separate words -> Option A

4. Quick Check:

   Clear lowercase with underscores = C [OK]

Hint: Choose lowercase with underscores for clarity [OK]

Common Mistakes:

• Using uppercase letters in model names
• Including spaces or special characters
• Using inconsistent naming styles

2. Which of these is a valid dbt model name?

easy

A. customer_orders

B. SalesReport2023

C. Inventory-List

D. Profit$Summary

Solution

1. Step 1: Check naming rules for dbt models

   Model names should be lowercase and use underscores to separate words, avoiding special characters and uppercase letters.

2. Step 2: Analyze each option

   customer_orders uses lowercase letters and underscores only, making it valid. Options B, C, and D contain uppercase letters or special characters.

3. Final Answer:

   customer_orders -> Option A

4. Quick Check:

   Lowercase with underscores = A [OK]

Hint: Pick lowercase with underscores, no special chars [OK]

Common Mistakes:

• Using uppercase letters in model names
• Including hyphens or special characters
• Starting names with numbers

3. Given the following dbt model names, which one best describes a model that summarizes monthly sales data?
monthly_sales_summary
salesMonthlySummary
monthly-sales-summary
MonthlySalesSummary

medium

A. MonthlySalesSummary

B. salesMonthlySummary

C. monthly-sales-summary

D. monthly_sales_summary

Solution

1. Step 1: Identify the best naming style for dbt models

   dbt prefers lowercase names with underscores to separate words for clarity and consistency.

2. Step 2: Compare each model name

   monthly_sales_summary uses lowercase with underscores and clearly describes the model. Options A and B use camel case or uppercase letters, and C uses hyphens, which are not recommended.

3. Final Answer:

   monthly_sales_summary -> Option D

4. Quick Check:

   Lowercase with underscores = D [OK]

Hint: Look for lowercase with underscores describing content [OK]

Common Mistakes:

• Using camelCase or PascalCase
• Using hyphens instead of underscores
• Ignoring clarity in names

4. You have a dbt model named CustomerOrders. What is the issue with this name and how can you fix it?

medium

A. It uses underscores incorrectly; rename to customerorders

B. It uses uppercase letters; rename to customer_orders

C. It is too short; rename to customer_orders_summary

D. It uses special characters; rename to customer-orders

Solution

1. Step 1: Identify naming convention violation

   The model name uses uppercase letters, which is against dbt's lowercase naming convention.

2. Step 2: Apply correct naming style

   Rename the model to lowercase letters with underscores: customer_orders.

3. Final Answer:

   It uses uppercase letters; rename to customer_orders -> Option B

4. Quick Check:

   Uppercase letters not allowed = A [OK]

Hint: Rename uppercase to lowercase with underscores [OK]

Common Mistakes:

• Ignoring uppercase letters in names
• Removing underscores instead of fixing case
• Adding unnecessary special characters

5. You want to create a dbt model that calculates the total revenue per product category for the last year. Which model name follows best practices and clearly describes the model's purpose?

hard

A. totalRevenuePerCategoryLastYear

B. Total_Revenue_Per_Category_Last_Year

C. total_revenue_per_category_last_year

D. revenue-category-last-year

Solution

1. Step 1: Understand naming best practices

   dbt model names should be lowercase, use underscores, and clearly describe the model's content.

2. Step 2: Evaluate each option

   total_revenue_per_category_last_year uses lowercase letters and underscores, and clearly describes the model. Options A and C use uppercase or camel case, and D uses hyphens.

3. Final Answer:

   total_revenue_per_category_last_year -> Option C

4. Quick Check:

   Lowercase with underscores and clear description = B [OK]

Hint: Use lowercase with underscores and descriptive words [OK]

Common Mistakes:

• Using camelCase or PascalCase
• Using hyphens instead of underscores
• Using uppercase letters