Introduction to dbt: The "T" in ELT

dbt, which stands for Data Build Tool, is an open-source command-line tool that helps data analysts and engineers transform data inside their data warehouse more effectively. Think of the common data pipeline pattern: ELT (Extract, Load, Transform). Traditional ETL tools would extract data from a source (like a production database or a SaaS app), transform it in a separate engine, and then load it into a data warehouse.

The modern approach, ELT, flips this. We first load the raw data directly into a powerful cloud warehouse like Snowflake, BigQuery, or Redshift. The transformation then happens inside the warehouse. dbt is the premier tool for this "T" step. It doesn't extract or load data; it specializes in running SQL-based transformation jobs against data that's already there.

Why is this a better approach? It leverages the immense power of modern cloud warehouses to do the heavy lifting. More importantly, dbt brings software engineering best practices to analytics code. It allows you to use version control (like Git), write tests for your data, and create reusable, modular data models. In short, it helps you turn your SQL scripts from a tangled mess into a reliable, documented, and testable data asset.

dbt championed the role of the "Analytics Engineer." This is a person who applies software engineering principles to the world of analytics. Before dbt, data transformation was often a collection of isolated SQL scripts, scheduled with a tool like Cron. It was difficult to see how different queries related to each other, hard to test, and nearly impossible to reuse logic.

dbt's core philosophy is that data transformations are code and should be treated as such. It allows you to write standard SQL, but enhances it with features that promote better engineering practices:

• Modularity: Every .sql file in your dbt project is a single model (a single transformation). You can then build larger, more complex models by referencing other models, creating a clear dependency graph.
• Version Control: Since a dbt project is just a directory of .sql and .yml files, it integrates perfectly with Git. You can track changes, collaborate with teammates, and use pull requests to review new data logic.
• Testing: You can write tests to assert things about your data. For example, you can test that a primary key column is always unique and not null. This ensures data quality and builds trust in your pipelines.
• Documentation: dbt can automatically generate documentation for your project, including a visual representation of your entire data pipeline (the DAG).

By combining SQL with these principles, dbt empowers analysts to build robust, production-grade data pipelines without needing to be expert software engineers.

A dbt project is a specific directory structure containing your SQL models, tests, and configuration files. When you run dbt init, it scaffolds this structure for you. Here are the most important components.

In dbt, a "model" is just a single .sql file that contains one SELECT statement. Each model defines a new table or view in your data warehouse. You organize these files in the models/ directory.

The magic of dbt comes from the {{ ref(...) }} function. Instead of hardcoding table names like analytics.schema.raw_orders, you reference other dbt models. dbt uses this function to automatically infer dependencies between your models.

For example, you might have a model named stg_customers.sql that cleans up a raw customers table. It might look like this:

-- models/staging/stg_customers.sql

SELECT
    id as customer_id,
    first_name,
    last_name
FROM raw.jaffle_shop.customers

Now, let's say you want to build another model that counts orders per customer. Instead of selecting from the raw table again, you can reference your new staging model:

-- models/marts/fct_customer_orders.sql

SELECT
    c.customer_id,
    count(o.id) as order_count
FROM {{ ref('stg_customers') }} as c
LEFT JOIN raw.jaffle_shop.orders as o ON c.customer_id = o.user_id
GROUP BY 1

When you run dbt run, dbt sees that fct_customer_orders depends on stg_customers. It automatically builds them in the correct order, creating a Directed Acyclic Graph (DAG) of your transformations. This ensures that models are always built with the most up-to-date data from their parents.

The ref function is the glue that connects your models. dbt uses these references to build a Directed Acyclic Graph (DAG) that represents your entire data pipeline. This graph makes dependencies explicit and ensures a correct build order every time.

A materialization is a strategy for how dbt builds your model in the data warehouse. You can configure the materialization for a model in your dbt_project.yml file or directly in the model's SQL file using a config block. The four main types are:

Here's how you would configure a model to be materialized as a table:

-- models/marts/fct_customer_orders.sql

{{ config(materialized='table') }}

SELECT
    c.customer_id,
    count(o.id) as order_count
FROM {{ ref('stg_customers') }} as c
-- ... rest of query

Choosing the right materialization is a trade-off between build time, query performance, and storage cost. Most projects start with views and switch to tables for models that are slow to query.

So far, we've used ref() to refer to other dbt models. But what about the raw data that we loaded into the warehouse? It's a bad practice to hard-code these raw table names directly in your models. Instead, dbt provides a way to declare them as sources.

You define sources in a .yml file, typically within your models/ directory. For example, models/staging/schema.yml:

version: 2

sources:
  - name: jaffle_shop # A logical grouping for your sources
    schema: raw # The actual schema in your warehouse
    tables:
      - name: customers
      - name: orders

This YAML file tells dbt that there's a source named jaffle_shop which contains tables named customers and orders located in the raw schema of our database. Now, you can use the {{ source(...) }} function in your models, which works just like ref():

-- models/staging/stg_customers.sql

SELECT
    id as customer_id,
    first_name,
    last_name
FROM {{ source('jaffle_shop', 'customers') }}

Using source() provides two main benefits. First, it makes your project more readable and abstracts away the exact location of your raw data. Second, it allows you to test and document your source data just like you would with your dbt models, helping you identify upstream data quality issues.

One of dbt's most powerful features is built-in data testing. Tests are assertions you make about your data to ensure its quality and integrity. If a test fails, dbt test will alert you, preventing bad data from propagating downstream.

There are two main types of tests:

1. Generic Tests: These are common tests that you can apply to any model or column. dbt comes with four built-in generic tests: unique, not_null, accepted_values, and relationships. You define these tests in the same .yml file where you declare your models.

   # models/staging/schema.yml
   version: 2
   
   models:
     - name: stg_customers
       columns:
         - name: customer_id
           tests:
             - unique
             - not_null
   

   This configuration will test that every customer_id in the stg_customers model is unique and not null.

2. Singular Tests: These are custom tests written as a SQL query in a .sql file, typically in the tests/ directory. A singular test is successful if the query returns zero rows. For example, you could write a test to ensure no order has a total amount less than zero.

   -- tests/assert_positive_order_total.sql
   select
       order_id,
       total_amount
   from {{ ref('fct_orders') }}
   where total_amount < 0
   

Testing is fundamental to building trust in your data. It moves your team from an attitude of "I hope the data is right" to "I know the data is right because my tests passed."

Once you have models and tests defined, you interact with your project using the dbt command-line interface (CLI). There are three fundamental commands you'll use constantly:

1. dbt run This command executes the SELECT statements in your model files to create the corresponding tables and views in your data warehouse. dbt uses the dependency graph (DAG) to run the models in the correct order. You can use model selectors to run only specific parts of your project, for example dbt run --select stg_customers.

2. dbt test This command runs all the tests you've defined in your .yml and .sql files. For each test, dbt runs the underlying SQL query. If the query returns any rows, the test fails, and dbt will report an error. This is your data quality safety net.

3. dbt build This is a convenient command that runs models and tests in a single, intelligent sequence. It will run a model, then immediately run any tests defined on that model before moving on to its children. If a test fails, the build process for that part of the DAG stops. It's an efficient way to run and test your project simultaneously.

A typical development workflow:

1. Make changes to a model file.
2. Run dbt run --select your_model_name to build just that model.
3. Run dbt test --select your_model_name to test it.
4. Once satisfied, commit your changes using Git.
5. In a production environment, a scheduler would typically run dbt build on a recurring basis.

dbt uses the Jinja templating language to add programming logic to your SQL files. This allows you to write dynamic queries that can behave differently based on variables, configurations, or the environment they're running in. You've already seen Jinja in action with {{ ref(...) }} and {{ config(...) }} — these are Jinja macros.

You can use standard programming constructs like if statements and for loops. A common use case for an if statement is in incremental models, where you want to filter for new data only when the model is being run incrementally.

{{ config(materialized='incremental') }}

SELECT * FROM raw_events

{% if is_incremental() %}

  -- this filter gets applied on an incremental run
  WHERE event_time > (select max(event_time) from {{ this }})

{% endif %}

In this example, the is_incremental() macro returns true only when dbt is performing an incremental run. The {{ this }} variable refers to the model itself, allowing you to query the existing table to find the most recent record.

You can also create your own macros in the macros/ directory. These are reusable functions that can encapsulate complex SQL logic. Jinja gives dbt its flexibility, turning your SQL from static text into dynamic, reusable code.