What is dbt (data build tool)?

A starter guide to dbt: its core concepts, benefits, and whether you should use it

dbt has been around for years now. It’s rare to find somebody who hasn’t heard of it or used it. For many of us, dbt became the default for data transformation tool of choice.

That said, not everyone has extensive experience using it or fully understands the benefits that dbt brings to their data workflows. Especially if you’re new to the data industry, it’s likely that you haven’t had a chance to use dbt just yet. Or perhaps you’ve been working in the legacy data tools for a long time like Oracle and SQL Server via stored procedures or legacy GUI based tools like Oracle Data Integrator, SSIS, and Talend.

If that’s you then this article is for you. It’ll help you see the benefits of dbt and understand why it became so popular.

We’ll cover:

• What is dbt?

• Core Concepts

• Benefits of dbt

• dbt Products

• When Should you Use dbt?

• dbt Alternatives

• The Future of dbt

What is dbt?

dbt (data build tool) is a data transformation framework that enables data analysts and engineers to transform data in their warehouse using software engineering practices such as version control, testing, documentation, and CI/CD.

dbt lets you skip writing SQL commands like CREATE, UPDATE, DELETE, etc while allowing you to only write SELECT statements to build tables and views with your custom business logic.

You can version control your logic with ease, and it helps you make your logic and models “DRY“ (do not repeat yourself).

With dbt, you can focus on what’s important, your data transformation logic.

In dbt, we use the term “model”, that defines a table or view in your target data warehouse/lakehouse.

Core Concepts

dbt uses these concepts at its core:

• Models - SQL transformations. Or you can use Python models if your warehouse provides the Python runtime (e.g. Snowflake, Databricks)

  

  unrealistic, too simple model example

• DAG - Dependencies between models.

  

  unrealistic, too simple DAG example

• Tests - Data quality validation.

  -- Example test
  -- Fails if my_first_dbt_model contains unexpected id values
  -- Run with "dbt test" or "dbt build" commends
  select id
  from {{ ref('my_first_dbt_model') }}
  where id not in (1)
    and id is not null
  

• Documentation - Automatically generated lineage and docs.

  

  dbt doc example

• Macros - Reusable SQL logic.

  -- jinja macro examples
  {% macro filter_non_null(column_name='id') %}
      where {{ column_name }} is not null
  {% endmacro %}
  
  {% macro filter_equals(column_name, value) %}
      where {{ column_name }} = {{ value }}
  {% endmacro %}
  

• Packages - Reusable community components.

  

  source

Benefits of dbt

There are many benefits dbt brings to the table. If you’re a power user or someone who’s been using dbt for a long time, you might want to make this list longer, but here’s my list:

• Better maintainability and collaboration

  • dbt is a code-first tool that allows multiple developers work on different models and projects at the same time. This also lets you take advantage of the benefits of Git, including the full history of model changes with which you can rollback or troubleshoot easily when issues arise.

• Improved data quality

  • Data tests and unit tests are built-in in dbt. Data tests are as simple as ensuring the uniqueness of a column in a model. And unit tests help you validate your logic works as expected.

• Clear lineage

  • dbt automatically generates a DAG, which is basically a representation of your data flowing through the models. You can also generate a documentation including a lineage view.

• Faster development

  • You won’t ever write DDL and DML in dbt. You just write select statements. It abstracts unnecessary details of how to build things behind the scenes and helps you focus on what matters.

• Easier onboarding

  • dbt is primarily built around SQL models. If you know SQL, you can get up to speed with dbt in no time.

dbt Products

dbt has mainly two products:

• dbt Core

• dbt Cloud

Let’s go through each.

dbt Core

The open-source version of dbt. It’s available as a Python library you can install locally and run it anywhere. This gives you the flexibility to choose other tools for different components while still benefiting from dbt. dbt Core is also called “self-hosted dbt”.

source

dbt Cloud

The paid version of dbt. It adds many more features on top of dbt Core and these features make your life easier in exchange of the licensing cost. dbt Cloud is also called “dbt platform”.

dbt Cloud gives you an IDE, built-in job scheduler, hosted documentation, job monitoring, etc. They also recently came out with dbt State that makes model runs more efficient.

source

dbt recently announced the dbt Fusion engine. It’s the new Rust-based execution and compilation engine that’s more high-performant for dbt and it powers both the new dbt Core 2.0 and dbt Cloud (a.k.a dbt platform). The acquisition of SDF Labs played a big role in the development of dbt Fusion.

As for the pricing, if you’re a small team, you can likely justify the cost and if you’re the only one using it, the cost is free (as of this writin). Though it’d add up if you have a large number of people needing to use dbt Cloud.

source

When Should You Use dbt?

Now you understand a little bit about dbt, the question is, should you use dbt?

It’s not that every team needs or should use dbt, because there are things dbt is good at and not good at. And there are some points you need to keep in mind when considering using dbt in your team:

• Good fit

  • You mainly use SQL to apply data transformations

  • You want to simplify your workflows but still use a code-first tool

  • You want to apply software engineering practices like version control, automated testing, etc in your process

• Not ideal

  • You have and/or want to keep transformation logic in Python or other languages that your warehouse doesn’t support

  • You use streaming-first architecture for data transformations and analytical data modeling than batch processing

  • Your workload is so small that adding an additional tool in your stack would hurt you more than benefitting

If your architecture is built around batch processing and SQL transformations, using dbt is a great choice.

dbt Alternatives

There are quite a few alternatives on the market. But these are the ones I’ve seen used as a direct alternative to dbt:

• SQLMesh

  • An open-source, Python-native data transformation framework. It gives you more flexible, powerful Python models and efficient data processing.

• Dataform

  • A similar tool to dbt, but it’s fully managed and integrated in Google Cloud. It’s free, but since you process models in GCP, all the compute and storage are tracked there.

• Coalesce

  • dbt is a code-first tool, whereas Coalesce is GUI-based and great for people who are not as technical. Not an open-source tool though.

• Matillion

  • Matillion provides a low/no-code platform while still allowing you to use code. Also, it’s not a one-to-one comparison to dbt, since Matillion handles data ingestion as well, and it’s more like a data platform than a pure data transformation tool.

• Your own framework/tool

  • This option of building your own tool is more and more becoming common as AI took off. At most big tech companies, there are enough resources and people to build their own tools in-house. Now with AI, it’s easier than ever to build in-house tools at any company.

The Future of dbt

dbt’s future is bright. I say that because of recent acquisitions, where Fivetran acquired both dbt Labs and Tobiko (SQLMesh). They already came out a new feature, dbt State, which is a feature ported over from SQLMesh.

The fact that Fivetran donated the open-source (OSS) SQLMesh to the Linux Foundation means they will no longer have the sole responsibility to keep the OSS SQLMesh going, and they can focus on improving dbt capabilities.

I talked about how SQLMesh is the best dbt alternative in a previous article, but what if all SQLMesh features get implemented in dbt? At that point, dbt would be THE data transformation framework everyone should be using. If those features are going behind the paywall or they’ll be part of dbt-core is a separate discussion.

Overall, I’d say these areas would be what help users continue using dbt or new users pick dbt:

• Efficient data processing

• Better developer experience

• AI-assisted development

• Continued development on open-source version of dbt (dbt-core)

If you can get started in dbt quickly, it allows you to build models much more efficiently, and it lets you use AI natively, who would not want to use such a tool?

Now, some teams would prefer OSS tools, so if Fivetran can keep the new features in dbt-core, that’s even better, and would be a huge thing for the whole data community (the new dbt State is a paid feature as of this writing)

Conclusion

dbt brings many benefits into your data modeling workflows. It remains one of the most influential tools in the modern data stack and it only gets better year over year.

---

Data Advisory & Consulting

Do you need help with your data & analytics projects? I provide data advisory and consulting services, including dbt consultations and implementations.

Reach out to me at yuki@oremdata.com or on LinkedIn to discuss the data challenges you’re facing.