dbt (Data Build Tool) Tutorial

This dbt tutorial covers the basics and core concepts of the Data Build Tool. You will learn how to install dbt and build your first project. You will also understand the differences between dbt Core and dbt Cloud. By the end, you will clearly understand dbt, including deployment and testing.

Table of Contents

• What is dbt?
• ETL vs ELT - Where dbt Fits
• dbt Core Concepts
• Installation and Setup
• Building Your First dbt Project
• dbt Core vs dbt Cloud
• Advanced Features
• dbt Deployment
• Dbt Testing and Data Quality
• Conclusion

What is dbt?

dbt (Data Build Tool) is an open-source data transformation tool. It enables data teams to transform raw data into trusted, analytics-ready datasets using SQL. It enables modularity, testing, and version control for data transformations.

By using dbt, you can define transformations as SQL models, manage dependencies between models, test data quality, document datasets, integrate changes through CI/CD, and more.

ETL vs ELT - Where dbt Fits

Let’s uncover where dbt fits in the ETL vs. ELT process. Before that, take a close look at ETL and ELT:

ETL - An Overview

ETL (Extract, Transform, Load) is a data integration approach in which data is extracted from sources and transformed into the required format before loading it into a target data warehouse or data platform.

A typical data flow in the ETL process is shown below:

Data Sources → Extract → Transform → Load → Analytics

ELT - An Overview

The modern ELT (Extract, Load, Transform) paradigm separates data ingestion from data transformation. Instead of transforming data before loading it into a data warehouse, data teams extract data from sources and load it into a cloud data platform first. Then they transform the data using the relevant tools.

A typical data flow in the ELT process is shown below:

Data Sources → Extract → Load → Transform → Analytics

dbt sits in the transform stage of the ELT pipeline. It doesn't extract data from sources or load data into the warehouse. Instead, dbt sits between the warehouse and BI tools.

An example ELT flow with dbt is shown below:

Salesforce (Data Source) → Extract → Snowflake Raw Layer → dbt Transformations → Data Mart → Power BI

dbt Core Concepts

Let’s go through the key dbt Core concepts below:

1. dbt Models

dbt models are SQL files that define how data should be transformed into analytics-ready datasets. dbt executes these SQL files in the target data platform and manages dependencies between models.

A dbt model contains the SELECT statement as shown below:

select
	order_id, --your first column you want selected
	customer_id, --your second column you want selected
	order_date --your last column you want selected (and so on)
from {{ ref('orders') }} --the table/view/model you want to select from
limit 3

2. Materialisations

They determine how dbt builds and stores a model in the target data platform. The key materialisations are:

• View: A view creates a database view that contains the model's SQL. The underlying query runs when you query the view. Use a view for lightweight transformations when you want the data to stay relatively current and don't need to store the results physically.
• Table: A table executes the model and physically stores the resulting dataset. It rebuilds the table when dbt runs the model. Use a table for frequently queried or computationally expensive models where query performance is crucial.
• Incremental: Incremental builds the model, then processes only new or changed records during subsequent runs. It works based on the model's incremental logic. Use incremental for large datasets where rebuilding the entire table is expensive or slow.
• Ephemeral: It doesn't create a physical database object. dbt injects the model's SQL into downstream queries. Use ephemeral models for reusable intermediate transformations that don't need to exist as standalone tables or views.
• Materialised View: A database-managed object that stores the results of a query. It can refresh the results based on the warehouse's capabilities. Use a materialised view when you need faster query performance.

3. Sources and the ref() Function

Sources represent raw data loaded into the data warehouse by an external system. With the source() function, you can run freshness checks, tests, and lineage tracking.

source() connects dbt to externally loaded raw data, whereas ref() creates dependencies between dbt-managed resources. Both source() and ref() contribute to dbt's DAG (Directed Acyclic Graph) and lineage.

4. Generic and Singular Tests

Generic tests are reusable tests that you can perform on multiple columns or models. 

dbt provides built-in generic tests such as:

• unique – It checks that values are unique.
• Not_null – It checks that values are not NULL.
• accepted_values – This test checks that values belong to a specified list.
• relationships – It checks referential integrity between two tables.

A singular test is a custom SQL query that you can write to address a specific data-quality requirement.

5. Documentation

You can use dbt documentation to describe models, columns, sources, tests, and other project resources. dbt documentation is typically defined in YAML files and SQL model files.

dbt documentation helps you:

• Understand how data is transformed and used
• Identify where data originates
• Understand transformation dependencies
• Improve data discoverability
• Maintain a shared data dictionary
• Support data governance and onboarding.

6. Jinja and Macros

Jinja is the templating language that you can use to add programming logic to SQL. It makes SQL dynamic, reusable, and configurable.

Macros are reusable Jinja or SQL code that can accept parameters and return generated SQL. They are similar to functions used in programming languages.

Jinja allows you to use variables, conditions, loops, and expressions inside SQL.

7. Seeds

Seeds are CSV files stored in a dbt project. dbt can load seeds into the data warehouse as tables. They help manage small, relatively static datasets alongside the dbt project.

You can use seeds for:

• Small lookup tables
• Country or currency codes
• Mapping tables
• Static business classifications
• Small reference datasets.

You can't use seeds for large or frequently changing datasets.

8. Snapshots (SCDs)

Snapshots are a dbt mechanism that you can use to capture changes to records over time. It preserves previous row versions rather than showing only the current state.

Use snapshots when you need historical tracking, such as changes to customer status, account attributes, or product prices. They implement Slowly Changing Dimensions (SCDs), especially SCD Type 2, which preserves historical versions of a record.

9. Snapshot Strategies

dbt uses two strategies for detecting changes:

Timestamp strategy: This strategy uses an updated_at column to determine whether a record has changed.

Example

snapshots:
- name: snapshot_name:
  relation: source('my_source', 'my_table')
  config:
    strategy: timestamp
    updated_at: column_name

Check strategy: This strategy compares specified columns to detect changes.

snapshots:
- name: snapshot_name:
  relation: source('my_source', 'my_table')
  config:
    strategy: check
    check_cols: [column_name] | "all"

dbt Installation and Setup

We’ll go through the dbt Core installation process below.

Prerequisites

A data warehouse such as Snowflake, BigQuery, or Redshift.

• Python 3.10 or later
• A code editor
• An adapter package
• Basic understanding of SQL.

Installing dbt

Let’s walk through the dbt v2 installation procedure for pip environments.

Use the command below to install it.

python -m pip install dbt

To upgrade the software, use the command below.

python -m pip install --upgrade dbt

Use the command below for verification.

dbt --version

Installing the Right Adapter

You can use the pip install command to install the required adapters. Below are commands to install adapters for different warehouses.

Warehouse	Command
Snowflake	pip install dbt-snowflake
BigQuery	pip install dbt-bigquery
Redshift	pip install dbt-redshift
Databricks	pip install dbt-databricks

Creating a New Project

1. First, name the project
2. Select the database adapter
3. Answer the prompts for the user, account, and password associated with the database.
4. A new folder will be created with the project name.
5. Create a connection profile on your local machine. The default location is ~/.dbt/profiles.yml
6. Generate a .gitignore file that includes .env, target/, dbt_packages/, and logs/
7. Use dbt init to initialise your project.
8. Include the --profile flag to specify an existing profiles.yml as the profile:

Example:

dbt init --profile profile_name

Building Your First dbt Project

Let’s build a dbt project in your own environment to understand the platform more profoundly.

1. Prerequisites

• Dbt with Snowflake adapter support
• Snowflake account and role with permissions
• Access to the configured HED source table
• dbt packages from packages.yml

2. Project Configuration

Source: Source table configured through dbt vars

Default source: RAW.INDUSTRIES_HIGHER_EDUCATION.HED_RECORDS

dbt source: source('hed', 'hed_records')

Target schema config: industries_higher_education

Primary materialisation: views

3. Creating Model Layers

• Staging Layer

stg_hed__students

This is the base student-record staging model. It centralises the HED source reference. It selects and standardises the source columns; it adds reusable calculations such as:

Days_since_last_login
Days_active_since_enrollment
Credit_success_rate_pct

• Intermediate

Int_hed__risk_levels

This is the student-level risk classification model. It derives GPA, completion, engagement, login recency, financial aid, intervention, and overall retention-risk categories.

Int_hed__engagement_categories

This is the student-level engagement classification model. It derives categories for login recency, course views, assignments, discussion participation, engagement level, and recommended engagement actions.

4. Step-by-step Procedure

• Validate the local dbt configuration using the command below:

dbt debug

• Start by installing package dependencies using the command below:

dbt deps

• Compile the project using the command below:

dbt compile

• Build and run the HED models using the commands below:

dbt build --select hed.*
dbt run --select hed.*

• Perform HED-only tests using the command below:

dbt test --select hed.*

• If you want to test the HED source, use the command below:

dbt test --select source:hed

• Run by tags using the command below.:

dbt run --select tag:hed
dbt run --select tag:education

• Run a specific mart by using the command below:

dbt run --select vw_hed_student_success_kpi

• Generate documents using the command below:

dbt docs generate

• If you deploy the project based on a folder, model or tag, you can use the commands below:

dbt build --select hed.marts.*
dbt build --select tag:data_quality
dbt build --select vw_hed_retention_risk_analysis

dbt Core vs dbt Cloud

dbt Core is a free, open-source CLI tool. You can install it locally, write SQL models in your code editor, and run them using the command line. It requires Git proficiency, YAML configuration, Jinja templating knowledge, and a separate scheduler (like Airflow or Dagster) to orchestrate execution.

dbt Cloud is a fully managed SaaS platform. It supports a browser-based IDE, built-in job scheduling, CI/CD integration, the dbt Semantic Layer, dbt Copilot and enterprise governance features like RBAC and SSO.

Let’s go through the comparison between dbt Core and dbt Cloud in this section.

Features	dbt Core	dbt Cloud
Type	It is an open-source command-line framework	It is a managed cloud platform
Installation	It is installed and managed locally	No local dbt installation
Development	Local IDE/editor and CLI commands	It supports a web-based development environment and local workflows
Scheduling	It requires an external scheduler or orchestrator	It uses built-in job scheduling and orchestration capabilities
CI/CD	You can configure it using GitHub or GitLab and CI tools	It supports CI and job workflows

Advanced dbt Features

This section covers dbt’s advanced features in detail.

1. dbt Packages and the Hub

dbt packages are reusable collections of dbt models, macros, tests, and other resources that you can add to a dbt project. The dbt Hub serves as a public registry for discovering dbt packages.

dbt_utils, dbt_expectations, dbt_date, audit_helper, and dbt_project_evaluator are some commonly used dbt packages.

2. dbt Copilot

dbt Copilot is an AI-powered assistant integrated into the dbt platform. It uses context from a dbt project such as model structure, metadata, relationships, and lineage to automate routine tasks like documentation, data testing, semantic modelling, and SQL formatting.

With dbt Copilot, you can:

• Generate or modify SQL using natural-language prompts.
• Generate model and column descriptions.
• Suggest and generate data tests based on model context.
• Create semantic models and metric definitions.
• Provide suggestions for improving SQL.
• Use project metadata and relationships to provide context-aware assistance.

3. Hooks and Operations

A hook is SQL or a macro that dbt executes automatically at a particular point in the execution lifecycle.

There are two main types: pre-hooks and post-hooks. A pre-hook runs before a model, snapshot, or seed executes, while a post-hook runs after the resource executes.

A dbt operation is a reusable macro that you can execute explicitly using the dbt run-operation command.

You can use operations when you need to:

• Grant permissions
• Create database objects
• Run maintenance SQL
• Perform administrative tasks
• Execute reusable database procedures.

4. The dbt Semantic Layer and MetricFlow

The dbt Semantic Layer provides a centralised and governed layer. Teams can define, manage, and serve business metrics on top of business models. This layer ensures consistency across BI and analytics tools and prevents metric drift with a single source of truth.

MetricFlow is the query-generation engine associated with the dbt Semantic Layer. It uses semantic definitions such as entities, dimensions, measures, and metrics to determine how to join data and generate the required SQL.

5. dbt Mesh

dbt Mesh is an effective way to organise a large dbt project into multiple domain-oriented projects. It helps develop data products independently while sharing trusted models and dependencies.

Example:

A finance team maintains a "Core Financial Metrics" project, while the marketing team builds a separate project that references the financial models without duplicating code or logic.

6. dbt Fusion Engine

The dbt Fusion engine is a Rust-based high-performance engine. You can use this engine to improve the development, compilation, parsing, and execution experience for dbt projects. It is designed to work with modern dbt workflows.

This engine can compile projects 10–100× faster than dbt Core's Python compiler for teams with 1,000+ models.

dbt Deployment

We’ll discuss about dbt deployment here.

1. Git-based Workflows with dbt

Two major strategies cover all dbt branching strategies: Direct Promotion and Indirect Promotion.

Direct promotion keeps one long-lived branch in our repository, like main.

Indirect promotion adds other long-lived branches that are derived from main. The simplest version of indirect promotion is a two-trunk hierarchical structure, which you commonly see in indirect workflows.

A typical development workflow in dbt includes:

• Development: Create a feature branch from qa to make, test, and review changes.
• Quality Assurance: Open a pull request comparing the feature branch to qa.
• Promotion: After all required approvals and checks, merge my changes to qa.
• Quality Assurance: SMEs or other stakeholders can review the changes in qa when the feature branch is merged.
• Promotion: Soon after a QA specialist gives their approval of qa’s version of the data, a release manager triggers a pull request using qa’s branch targeting main.
• Deployment: Others can see and use the changes (and others’ changes) in main after qa merges to main and main is deployed.

2. Slim CI and State Deferral

Slim CI is a dbt CI/CD technique that avoids rebuilding the entire dbt project for every pull request. dbt uses the state of a previous production or comparison run to identify changes and run only the affected resources.

With deferral, dbt can resolve references for unchanged resources against the previous state, typically production, rather than rebuilding those resources in CI.

3. Orchestration with Airflow / Prefect / Dagster

Orchestration in dbt refers to scheduling and coordinating dbt jobs with other data engineering tasks, such as data ingestion, warehouse operations, validation, and downstream processing.

dbt primarily handles the transformation layer, while orchestrators such as Apache Airflow, Prefect, and Dagster manage the broader data pipeline.

4. DbtCloudRunJobOperator

An Airflow operator from the apache-airflow-providers-dbt-cloud package/provider. It triggers a preconfigured dbt Cloud job through the dbt Cloud API.

Dbt Testing and Data Quality

Understanding dbt testing and post-transformation data quality is crucial. Let’s see them below.

• dbt's native tests vs. dbt-expectations

Both native tests and dbt-expectations validate data quality in dbt, but they differ in many ways. The table below shows the differences.

Aspects	dbt native tests	dbt-expectations
Type	Built into dbt	Community package
Installation	No additional package required	It requires the package to be installed
Complexity	Easy	Complex
Typical Checks	Unique, not_null, accepted values and relationships	Domain-specific expectations
Best For	Used for core integrity checks	Used for advanced data quality rules

• Source Freshness Checks

Source freshness checks verify whether the raw data loaded into your warehouse is up to date. They help identify issues such as delayed ingestion, failed pipelines, or stale source tables before they affect downstream dbt models.

• Contracts and Model Versioning

Contracts and model versioning help teams manage changes to dbt models securely. They are highly useful when multiple teams or downstream applications depend on the same models.

A model contract defines the expected structure and data types of a model's output. dbt can validate the model against this declared contract during execution.

Model versioning allows a dbt project to maintain different versions of a model when making a breaking change.

Conclusion

In summary, dbt transforms raw warehouse data into trusted, analytics-ready datasets. This dbt tutorial should have helped you learn key dbt concepts, models, testing, deployment, and other capabilities.

The tutorial clearly guided you in building maintainable, reliable data transformation workflows. You have gained a deep understanding of dbt Core, dbt Cloud, orchestration, CI/CD, and data quality practices.