The Starter Guide for dbt^™

If there is one tool I always recommend learning (and implementing) in a modern data architecture, it's dbt.

Not only is it powerful as a data transformation tool, it's also supported by an amazing open-source community that has helped tens of thousands of data engineers (including me) better approach modern data platforms.

dbt Labs has also merged with Fivetran, so two of the most common tools for ingestion and transformation now sit under one company.

But dbt can be a bit confusing, especially when you're first getting started.

Let's dive in.

What is dbt?

dbt is a code-based data transformation tool designed for the modern age of data.

▶ Watch · 8:43 The Modern Data Viewpoint (3 Principles)

In traditional data pipelines, the data transformation step is usually handled with drag-and-drop tools and an "ETL" pipeline optimized for row-based databases.

However, advances in cloud computing and the growing expectations of data teams have forced us all to rethink the way we approach this entire process.

Version control and automation are a must, data quality is more important than ever, and data is now treated like a standalone product rather than an afterthought.

Enter dbt.

dbt allows you to create data transformation logic using SQL, but in a way that can be incredibly dynamic.

This is accomplished with features like Jinja templating and YAML-based config files.

At a high level, dbt uses these features to compile SQL queries, which are then sent to your cloud database to be executed. All of this happens within a single "run".

The result is new, transformed data models (tables, views) that are ready for analytics.

You can also add tests, create a documentation website, and automate the entire process with just a few simple commands.

This is just scratching the surface, and there are many subtleties outside the scope of this starter guide.

But what about all of this has made dbt skyrocket in popularity?

Why is it so popular?

Functionality plus an opinionated approach solve data workflow problems.

▶ Watch · 8:12 5 Tips to Improve Your dbt Project

We know that data now needs to be managed like a standalone product.

But even if that makes sense at a high level, it's hard to just start changing things without the right processes and tools in place. Not to mention this is a new thought process for most data folks, and there are many questions around best practices.

It also currently sits alone as the clear best option for this component of the modern stack.

With the right tools and the right approach, we can better solve this workflow problem so that your data team starts looking like a software engineering team, full of automation, documentation and consistency.

5 best practices to consider

A breakdown of common scenarios and approaches for optimizing dbt.

Tip #1Create a staging layer

One clean version of every raw table.

▶ Watch · 9:54 The Missing Piece in Many Data Pipelines

The staging layer holds cleaned-up versions of your raw data. Ideally, each staging model matches one-to-one with a raw table and is used in place of it everywhere else in your project.

Yes, that means you'll likely have a lot of them. That's perfectly okay (more on that in Tip #3).

Typical transforms at this level include:

• Renaming columns
• Casting data types
• Simple conditional statements

Why do this in the first place?

First, it embraces modularity. Rather than transforming the same source column in different places (and possibly in completely different ways), you set it once so it's consistent throughout the entire project. Future updates happen in one place too.

Second, it acts as a gatekeeper for the rest of your project. If you always need to filter out certain data from a source, add that condition to the staging model. Now you can clearly see what's being excluded, and you know it's excluded from the start.

Tip #2Use CTEs

CTEs (Common Table Expressions) are treated as pass-throughs.

▶ Watch · 4:07 SQL vs dbt Models (& the value of CTEs)

At the end of the day, all dbt "models" are ultimately just SQL statements. And traditionally, one would think that a select * from anything is a bad idea. But not so fast.

Modern cloud databases are really smart and treat CTEs as pass-throughs. They only care about the columns you select at the very end and will disregard all others completely.

For that reason, it's suggested to use CTEs as "imports" and to organize your code overall. For example, a typical dbt model might look something like this:

models/warehouse/fct_movie_voting.sqlwith fandango_scrape as (

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

),

fandango_scores as (

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

),

final as (

    select
        fandango_scores.movie_id,
        fandango_scrape.scrape_vote_count,
        fandango_scores.metacritic_user_vote_count,
        fandango_scores.imdb_user_vote_count,
        fandango_scores.fandango_votes,
        fandango_scores.fandango_difference

    from fandango_scores

    left join fandango_scrape
        on fandango_scores.movie_id = fandango_scrape.movie_id

)

select * from final

Tip #3Leverage directories

Remember DRY (don't repeat yourself).

▶ Watch · 9:01 Data Architecture w/ dbt: Putting It All Together

dbt is designed for modularity and reusability. One of the best ways you can take advantage of this is through directories.

First, it will help you organize your project. In my opinion, the best thing you can do for long-term success with dbt is to align your models directory with your database design. If your database has staging, warehouse and marts schemas, your project should have staging, warehouse and marts directories.

Second, and more importantly, it lets you set directory-level model configurations in your dbt_project.yml file rather than repeating the same configuration in each individual model. Just imagine if you had hundreds of them.

dbt_project.ymlmodels:
  dbt_training:
    +materialized: table
    staging:
      +materialized: view
      +schema: staging
    warehouse:
      +schema: warehouse
    marts:
      +schema: marts

The further down in directory layers you go, the more each config takes precedence, until you finally get to the model itself.

Set things that are reusable at the highest level, and only add new configs as you move further down. However, avoid creating model-specific configs in your dbt_project.yml if possible, so it doesn't get cluttered.

Tip #4Create a style guide

Consistency will make or break a project.

▶ Watch · 7:09 dbt Project Naming Conventions (recommended approach)

Any time you get into a group, you're bound to have different approaches. This isn't necessarily a bad thing, but it makes consistency hard to achieve.

The best way to combat this is to get everyone on the same page from the start. A great way to do this is to create a style guide for your project.

This should include things like how you plan to name your models (e.g. prefixes, plural vs singular), how you'll name columns (e.g. snake vs camel case, is_ for booleans) or how you'll write your queries (e.g. all lowercase, line length limits).

Add it directly to your README.md, or create a separate .md file and link to it from there. Either way, keep it front and center so everyone sees it while they work in the project.

Tip #5Don't optimize for fewer lines

New lines are cheap. Brain power is expensive.

▶ Watch · 4:26 A simple 4-step process for creating dbt models

We all love to challenge ourselves to write the most optimized code possible. And it can be rewarding to find ways to simplify your syntax.

But when the next person comes along, it's going to take them a lot of time to retrace your steps, even with great comments. That time takes brain power away from more productive work, and it has a very real cost.

You don't want to be lazy with your code. But you also don't want to over-engineer a solution to the point where nobody else can understand it.

The same goes for readability. Removing whitespace and line breaks just to get fewer lines doesn't provide any real benefit. There's essentially no cost to breaking logic into multiple lines, or adding an extra space to a join so it's easier to read.

BonusDon't hardcode tables

It works against core dbt functionality.

▶ Watch · 10:26 dbt vs Stored Procedures (3 key differences)

It might be second nature to write out your table names in a SQL query, but you'll want to avoid this at all costs with dbt.

Technically it will still work, but this approach will cause you to miss many of the best features of dbt. Instead, you should always refer to a table in your models using either the source or ref Jinja functions.

select * from {{ source('avengers', 'avengers_history') }}

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

Why? First, using these functions lets dbt create dependencies between your sources and models, which in turn allows it to build a proper DAG. This means you can easily run any upstream/downstream models using simple operators.

Second, it will automatically set the correct database/schema when running your project in different environments. If it's hardcoded, you'll be stuck with the same one and your code will be much less dynamic.

BonusAutomate testing

We're developers, not full-time testers.

▶ Watch · 5:23 Data Automation (CI/CD) with a Real Life Example

However, testing is a critical part of creating trust in the final data product. Fortunately, dbt is a command line tool and can be easily automated.

Once you've created your tests (singular/generic) in your project, you can use automations like GitHub Actions or GitLab Pipelines to automatically test your code before it gets too far.

For example, every time a new pull/merge request is opened, you can set a workflow to:

1. Deploy your dbt models (dbt run)
2. Test your data (dbt test)

Any failures would block the merge. While nothing is 100% bulletproof, this lets you catch obvious errors right away in an automated fashion. The more robust your test suite gets, the better this process will be.

BonusHost documentation

Give your stakeholders what they want.

▶ Watch · 3:40 View your dbt documentation as a website

Stakeholders love data dictionaries, and dbt lets you easily create a prebuilt website with two simple commands:

1. dbt docs generate
2. dbt docs serve

However, you're going to want a convenient way for people to access it. You could always keep it on a network server and give people access. But another option is to push it to a hosted provider.

For example, use your cloud provider or a static web hosting service like Netlify or GitHub Pages.

You can even add a step in your automated workflow (see above) to publish the latest version of your docs to one of these locations.

Once that's set up, you can set it and forget it, and direct 99% of stakeholder questions to the site.

Common questions

What is dbt?

dbt is a code-based data transformation tool. You write transformation logic in SQL, made dynamic with Jinja templating and YAML config files, and dbt compiles it and runs it in your cloud database.

Does dbt replace an ingestion tool?

No. Even with dbt and Fivetran now merged, dbt handles the transformation step. It runs inside your warehouse after an ingestion tool, like Fivetran, has loaded the raw data.

What's the difference between source() and ref() in dbt?

source() points to a raw table loaded into your database. ref() points to another dbt model. Both let dbt build the dependency graph (DAG) and set the right database and schema for each environment.

What should a staging model do?

Clean up one raw table: rename columns, cast data types and apply simple conditional logic. Each staging model matches one-to-one with a raw table and is used in its place everywhere else in the project.

How should I organize a dbt project?

Align your models directory with your database design. If your database has staging, warehouse and marts schemas, your project should have staging, warehouse and marts directories, with shared configs set once in dbt_project.yml.

Thanks for reading

I hope you found this guide helpful. There is so much more to learn about dbt, but hopefully you now have a better appreciation for its purpose and the type of things to expect as a developer.

Cheers,
Michael