07. Model Best Practices
Writing Enterprise-Grade Models
Writing SQL is easy. Writing SQL that a team of 50 people can maintain for 5 years is incredibly difficult. DBT enforces structure, but it is still up to you to follow best practices.
1. The SELECT * Anti-Pattern
SELECT * in ProductionWhile it is tempting to use SELECT * in staging models, it breaks data lineage logic and makes it impossible to know exactly which columns are being passed downstream.
Instead, be explicit in your Staging Models:
-- Best Practice: Explicit Columns
SELECT
id AS user_id,
first_name,
last_name,
created_at
FROM {{ source('raw', 'users') }}
2. Always Use CTEs at the Top
Common Table Expressions (CTEs) make your SQL read chronologically from top to bottom. It acts like an "Import" block in Python.
-- Best Practice: Import CTEs First
WITH customers AS (
SELECT * FROM {{ ref('stg_customers') }}
),
orders AS (
SELECT * FROM {{ ref('stg_orders') }}
)
SELECT
c.customer_id,
COUNT(o.order_id) AS total_orders
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY 1
️ 3. Never Hardcode Environments
If you hardcode database or schema names, your project cannot be deployed across different environments via CI/CD.
- ** Bad:**
SELECT * FROM prod_db.sales.raw_orders - ** Good:**
SELECT * FROM {{ source('sales', 'raw_orders') }}
4. Naming formatting
Readability is vital. Establish an alignment style and stick to it.
- Lower Case: SQL keywords can be capitalized (
SELECT), but table and column names should always be lower-cased (user_id). - Trailing Commas: Prefer putting commas at the end of the line, not the beginning.
- Consistent Aliases: If you alias
customersascin one model, don't alias it ascustin another.
Summary
Writing good DBT models means writing code for the next developer, not just for the computer. Use CTEs, avoid SELECT * in your final layers, and heavily utilize references to keep your code clean and debuggable.