Model naming conventions in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding dbt Model Naming Patterns

In dbt, model names often follow specific patterns to indicate their purpose. Which of the following model names best represents a staging model for raw customer data?

A int_customers

B fct_customers

C dim_customers

D stg_customers_raw

💡 Hint

Staging models usually start with stg_ and represent raw or lightly transformed data.

In dbt, stg_ prefix is used for staging models that prepare raw data for further transformations. int_ is for intermediate models, dim_ for dimension tables, and fct_ for fact tables.

❓ Predict Output

intermediate

Output of dbt Model Naming Convention Check

Given the following list of dbt model names, how many are correctly following the common naming conventions for dimension models?

models = ['dim_users', 'fct_sales', 'dim_products', 'stg_orders', 'dim_customers']

dbt

models = ['dim_users', 'fct_sales', 'dim_products', 'stg_orders', 'dim_customers']
dim_models = [m for m in models if m.startswith('dim_')]
print(len(dim_models))

A 1

B 2

C 3

D 4

💡 Hint

Count how many model names start with dim_.

Models starting with dim_ are dimension models. Here, 'dim_users', 'dim_products', and 'dim_customers' match, totaling 3.

❓ data_output

advanced

Resulting DataFrame from Model Naming Filter

Consider a pandas DataFrame listing dbt models and their types. Which rows remain after filtering for models with names starting with fct_?

dbt

import pandas as pd
models_df = pd.DataFrame({
  'model_name': ['fct_sales', 'dim_customers', 'stg_orders', 'fct_revenue'],
  'description': ['sales facts', 'customer dimensions', 'order staging', 'revenue facts']
})
fact_models = models_df[models_df['model_name'].str.startswith('fct_')]
fact_models

A [{'model_name': 'fct_sales', 'description': 'sales facts'}, {'model_name': 'fct_revenue', 'description': 'revenue facts'}]

B [{'model_name': 'dim_customers', 'description': 'customer dimensions'}]

C Empty DataFrame

D [{'model_name': 'stg_orders', 'description': 'order staging'}]

💡 Hint

Filter rows where model_name starts with fct_.

Only 'fct_sales' and 'fct_revenue' start with 'fct_', so only those rows remain after filtering.

🔧 Debug

advanced

Identify the Error in Model Naming Convention Code

What error does the following Python code produce when checking if model names start with stg_?

models = ['stg_orders', 'dim_customers', 'stg_payments']
filtered = [m for m in models if m.startswith('stg_') == True]
print(filtered)

A No error; outputs ['stg_orders', 'stg_payments']

B TypeError: 'bool' object is not callable

C SyntaxError: invalid syntax

D Outputs an empty list []

💡 Hint

Check if startswith returns a boolean and how it is used.

The code correctly filters model names starting with 'stg_'. The comparison to True is redundant but valid. The output is the list of matching models.

🚀 Application

expert

Choosing the Correct Model Name for a New Intermediate Model

You are creating a new intermediate model in dbt that combines staging data for orders and payments. Which model name best follows dbt naming conventions?

A stg_orders_payments

B int_orders_payments

C fct_orders_payments

D dim_orders_payments

💡 Hint

Intermediate models often use the prefix int_.

The int_ prefix is used for intermediate models that combine or transform staging data before final models. stg_ is for raw staging, fct_ for fact tables, and dim_ for dimension tables.

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