Skip to main content

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​

Never rely on SELECT * in Production

While 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 customers as c in one model, don't alias it as cust in 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.