DBT (Data Build Tool) Use cases and Example

DBT (Data Build Tool) is an open-source command-line tool that enables data analysts and engineers to transform data in their warehouse more effectively. DBT allows you to write modular SQL queries to transform raw data into analytics-ready datasets, run these queries in your database, and manage dependencies between queries. It's designed to work within the ELT (Extract, Load, Transform) paradigm, where raw data is first loaded into a data warehouse and then transformed.

Key Concepts

1. Models: SQL files that contain your transformation logic.
2. Seeds: CSV files that you can upload to your database.
3. Snapshots: Tables that capture the state of a table at a given point in time.
4. Tests: Assertions you can make about your data.
5. Documentation: Automatically generated documentation for your models.
6. Data Lineage: Understanding and visualizing how data flows through your transformations.

Example DBT Project

Let's walk through an example of a simple DBT project.

Step 1: Project Setup

First, initialize a new DBT project:

dbt init my_dbt_project

Navigate into your project directory:

cd my_dbt_project

Step 2: Configure Your Profile

DBT uses a profiles.yml file to manage database connections. Create or edit the ~/.dbt/profiles.yml file to include your database connection details:

my_dbt_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: your_user
      password: your_password
      port: 5432
      dbname: your_db
      schema: public

Step 3: Create Models

Create a new SQL file in the models directory. This SQL file represents a transformation.

Example: models/my_first_model.sql

-- models/my_first_model.sql

with source_data as (
    select * from raw_data.users
)

select
    id,
    first_name,
    last_name,
    email,
    created_at,
    updated_at
from source_data
where is_active = true

This model transforms data from the raw_data.users table, filtering out inactive users.

Step 4: Run the Model

Run the DBT model to execute the SQL and create the transformed table in your database:

dbt run

This command will execute the SQL in my_first_model.sql and create a new table (or view) in your database with the transformed data.

Step 5: Testing Your Models

DBT allows you to write tests to ensure data quality. Create a new test in the tests directory.

Example: tests/test_my_first_model.sql

version: 2

models:
  - name: my_first_model
    columns:
      - name: id
        tests:
          - not_null
          - unique

Run the tests:

dbt test

This command will run the tests defined in test_my_first_model.sql to ensure the id column is not null and unique.

Step 6: Documenting Your Models

DBT can generate documentation for your models. Create a schema.yml file in the models directory.

Example: models/schema.yml

version: 2

models:
  - name: my_first_model
    description: "This model selects active users from the raw data."
    columns:
      - name: id
        description: "The unique identifier for a user."
      - name: first_name
        description: "The user's first name."
      - name: last_name
        description: "The user's last name."
      - name: email
        description: "The user's email address."
      - name: created_at
        description: "The timestamp when the user was created."
      - name: updated_at
        description: "The timestamp when the user was last updated."

Generate the documentation:

dbt docs generate

Serve the documentation locally:

dbt docs serve

Visit the local URL provided to view your project's documentation.

Step 7: Scheduling and Running DBT Jobs

DBT models can be scheduled to run at specific intervals using a scheduler like Airflow, Prefect, or DBT Cloud.

Example with Airflow:

1. Create a DAG in Airflow:

from airflow import DAG
from airflow.operators.bash_operator import BashOperator
from datetime import datetime, timedelta

default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2023, 1, 1),
    'retries': 1,
    'retry_delay': timedelta(minutes=5),
}

dag = DAG(
    'dbt_run',
    default_args=default_args,
    description='A simple DBT DAG',
    schedule_interval=timedelta(days=1),
)

t1 = BashOperator(
    task_id='dbt_run',
    bash_command='dbt run --profiles-dir /path/to/.dbt',
    dag=dag,
)

t1

2. Add the DAG file to your Airflow dags directory and start Airflow to schedule the DBT runs.