5 Tips for a Successful dbt Project

dbt is open source and purposely flexible, which is why it's so quick to get started and so easy to let a project get out of hand. Five habits keep it on track. Build a staging layer so every raw table is cleaned once and reused everywhere. Write models as CTEs, with import CTEs at the top and a final CTE at the bottom, so logic is grouped and easy to debug. Use directories, because configurations and selectors work at the directory level. Write down a style guide before the project grows. And don't optimize for fewer lines, because new lines are cheap and brain power is expensive.

Tip 1: Create a staging layer

A fun part of dbt is how fast you can get going. But with that much freedom of choice comes responsibility, and moving fast tends to produce subtle bad habits, or worse, mistakes that compound.

The first habit that prevents it is a staging layer. At a high level, staging contains cleaned-up versions of your raw tables. Each staging model matches one-to-one with a raw table, and the rest of the project uses the staging model instead of the raw table.

That means there will be a lot of them. That's perfectly okay, and tip 3 explains why.

What belongs in staging

Transforms at this level are light:

• Renaming columns
• Casting data types
• Simple conditional statements

Why bother

First, it makes the project modular. Rather than the same source column being transformed in different places, possibly in different ways, you set it once and it's consistent everywhere. Future maintenance gets easier too, because a simple change happens in one model instead of five.

Second, it acts as a gatekeeper. If you always need to filter certain rows out of a source, add that condition to the staging model. Everyone can see what's excluded, and it's excluded from the start.

Tip 2: Use CTEs

A CTE, or common table expression, is a named subquery declared with with at the top of a query. I talk about them a lot for SQL in general, and the same holds in dbt.

With CTEs you group your custom business logic into smaller pieces, each with its own alias. That's far cleaner than one extremely long and overly complex select statement.

Import CTEs

You can also use CTEs as imports, similar to how you'd import a module at the top of a Python file. dbt Labs published an article showing that on modern data platforms a select * in a CTE is essentially a pass-through. The query engine is smart enough to only use what's needed, so there's little to no performance hit.

That matters because it means you can bring in any model you want as a CTE with a select *, get access to every underlying column without listing them out, and not crush performance doing it.

The structure I use

Structure every dbt model along these lines:

• Import CTEs for the models you need
• Logic CTEs that group your custom transformations
• A final CTE that selects the exact columns the model should produce
• select * from final to finish

with customers as (

    select * from {{ ref('stg_customers') }}

),

orders as (

    select * from {{ ref('stg_orders') }}

),

customer_orders as (

    select
        customer_id,
        count(order_id) as order_count,
        min(order_date) as first_order_date
    from orders
    group by 1

),

final as (

    select
        customers.customer_id,
        customers.first_name,
        customers.last_name,
        customer_orders.order_count,
        customer_orders.first_order_date
    from customers
    left join customer_orders
        on customers.customer_id = customer_orders.customer_id

)

select * from final

Troubleshooting for free

Because CTEs operate as a pass-through unless something selects from them, the last line is your debugger. Change select * from final to select * from customer_orders and you're looking at that step alone, no rewriting needed.

Tip 3: Make use of directories

Beyond the obvious reason of staying organized, directories give you a major benefit in the dbt workflow itself. You can set directory-specific configurations in dbt_project.yml.

This is by design, to avoid repeating the same config across every model in a directory. Say every model in staging uses the same custom schema. Set it once at the directory level and it flows through to every model inside.

models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
    marts:
      +materialized: table
      +schema: marts

Subdirectories inherit

Subdirectories can be given additional configurations on top of what the parent sets, so on and so forth down the tree. That's the DRY principle, don't repeat yourself, applied to project config.

Run by directory

You can also select resources by directory path, which makes it easy to run or test one layer or one source at a time.

dbt run --select models/staging
dbt test --select models/marts

Don't be afraid of a lot of directories. People sometimes get tripped up here, but directories open up a lot of options and cost nothing to add.

Tip 4: Create a style guide

As with any team project, naming and consistency matter, and that's especially true with dbt. As developers we'd love unlimited creative freedom. The reality is that keeping everyone aligned creates efficiencies in how you develop, in code reviews, and in onboarding new teammates.

dbt projects grow fast. An agreed-upon style should be one of the first things a team decides.

What goes in it

Some of the conventions worth settling:

• Column naming: camel case or snake case
• Plural versus singular model names
• Model prefixes, such as stg_, int_, dim_ and fct_
• How CTEs are structured and indented

There's more, but deciding these up front saves a lot of time later.

Where to keep it

Put the guide in the README of the project itself so it's front and center for everyone working in the repo. dbt Labs publishes an example style guide that's a good starting point to adapt.

Nobody likes extra documentation. In this case I'd still recommend it.

Tip 5: Don't optimize for fewer lines

In a training I attended a couple of years ago, someone shared a line that stuck with me: new lines are cheap, brain power is expensive.

We all like the challenge of writing the most optimized code possible, and simplifying syntax can be rewarding. But when the next person comes along, retracing those steps takes a lot of time, even with good comments. That's brain power pulled away from more productive work, and it has a very real cost.

Don't be lazy, don't over-engineer

You don't want sloppy code. You also don't want a solution so engineered that quite literally nobody else understands it. Aim for the middle.

Readability is the same idea

Removing white space and line breaks just to keep a query short doesn't provide any real benefit. There's essentially no cost to breaking logic across multiple lines, or adding a blank line before a join so you can see it more clearly.

Next time you're building a dbt model, remember: extra lines are cheap, brain power is expensive.

Related reading

• SQL vs dbt Models (& the value of CTEs)
• dbt Project Naming Conventions - Recommendations
• A simple 4-step process for creating dbt models
• Why Bother With Naming Conventions?

Final takeaway

None of these five tips are hard, and yet most of the dbt projects I've been asked to clean up were missing at least a couple of them. dbt rewards diligence and planning more than cleverness, so decide on these habits early and the project stays one you're glad to work in.