# Getting Started with dbt: A Practical Introduction to Data Transformation
## Introduction
Data engineering has a persistent challenge: as analytics projects grow, so does the complexity of managing dozens or even hundreds of SQL scripts. What starts as a handful of files quickly becomes a tangled web of dependencies, where a single column rename can break downstream reports and bring nightly data pipelines to a halt. The tool **dbt** (short for data build tool) was designed to solve exactly this problem.
dbt was developed in the mid-2010s by a group now known as dbt Labs. It emerged from an internal analytics workflow and evolved into a powerful open-source CLI tool called **dbt Core**, alongside a fully managed commercial offering called **dbt Platform**. At its heart, dbt treats SQL transformations as a proper software project — complete with dependency management, automated testing, documentation, and environment control.
dbt is free to use under the Apache 2.0 open-source licence, and it works with a variety of databases including Snowflake, BigQuery, Redshift, and Databricks. For the purposes of this guide, I’ll be demonstrating everything with a local DuckDB instance, which is also free under the MIT licence.
If you’ve ever inherited a folder full of undocumented SQL scripts and wished for a better way to manage them, read on.
—
## Why dbt Matters
Consider a typical analytics workflow. Raw order data arrives in a data warehouse every hour. You write one SQL script to clean it, another to compute customer totals, another to aggregate daily sales, and yet another to feed executive dashboards. At the start, tracking four or five files is straightforward. Six months later, you have fifty files, and no one quite remembers the correct execution order.
The questions that plague growing analytics projects are familiar to anyone who has worked in the space:
– Which script runs first, and what depends on what?
– What happens when someone renames a column in a source table?
– How do you verify that the data is still valid after a transformation?
– Can a new developer understand the project without reading every single file?
Traditionally, teams dealt with these issues through naming conventions, shared knowledge, and handwritten notes. But as organisations became more data-driven, analytics projects started looking more like software projects — and they needed software-like tooling. dbt fills that gap by treating every transformation as a defined component with explicit dependencies, rather than a collection of isolated scripts.
dbt handles a broad range of capabilities beyond simple transformations:
– **Data quality testing** — automatically validate your data at each stage
– **Documentation and lineage** — generate visual maps of how data flows through your system
– **SQL reuse through macros** — write reusable logic that works across multiple models
– **Environment management** — maintain separate development, testing, and production configurations
– **Scheduled and CI/CD execution** — run transformations as part of automated pipelines
—
## Prerequisites
Before you begin, make sure you have the following:
– **Python 3.13** (or a compatible version) installed on your system
– **A database** that dbt can connect to. This guide uses DuckDB, but other databases require their own specific adapter setup
– **A Unix-like terminal** (or PowerShell on Windows) for running commands
The examples in this guide assume a Windows environment, but the same principles apply across Linux and macOS with minor path adjustments.
—
## Installing dbt
The first step is to create an isolated Python environment for your project. This keeps your dbt installation separate from other projects and prevents dependency conflicts.
“`powershell
cd projects
mkdir dbt-demo
cd dbt-demo
python3 -m venv .venv
# Activate the virtual environment
…venvScriptsActivate.ps1
“`
With your virtual environment active, install dbt along with the DuckDB adapter using pip:
“`powershell
python3 -m pip install dbt-duckdb
“`
The `dbt-duckdb` package is a convenient one-file install that bundles both the dbt Core engine and the DuckDB adapter together. For other databases such as BigQuery, Snowflake, or Redshift, you would install `dbt-core` separately and then add the corresponding adapter package.
The installation pulls in a substantial set of dependencies including Jinja2 for templating, SQLFluff for SQL parsing, Pydantic for data validation, and many others. Once the installation completes successfully, you’re ready to create your first project.
—
## Setting Up a dbt Project
Initialising a new dbt project is as simple as running a single command:
“`powershell
dbt init
“`
You’ll be prompted to enter a project name (using letters, digits, and underscores only) and then asked to select your database type. For DuckDB, you simply enter `1`. The command generates a complete project structure and writes a `profiles.yml` file containing your database connection configuration.
After initialisation, your project directory will look something like this:
“`
MY_DBT_DEMO/
analyses/
data/
macros/
models/
example/
my_first_dbt_model.sql
my_second_dbt_model.sql
schema.yml
seeds/
snapshots/
tests/
.gitignore
dbt_project.yml
README.md
“`
The `example` folder inside `models/` contains starter files you can safely delete once you understand the structure.
### Understanding profiles.yml
One of the most important configuration files is `profiles.yml`, which stores your database connection details. This file lives outside your project directory — in your home folder under `.dbt/profiles.yml` on most systems.
The default configuration creates both a `dev` and a `prod` target, each pointing to a separate DuckDB file. You can customise the paths to suit your setup. For example, you might want your DuckDB data files stored in a specific project subdirectory rather than your home folder. The path is relative to your home directory, and you can use environment variables within the configuration for flexibility.
—
## Creating Your Database and Sample Data
Before building transformations, you need a database with some data to work with. First, install the DuckDB CLI from the official DuckDB website, then launch it with a filename to persist your data:
“`powershell
.duckdb path/to/your/database.duckdb
“`
Inside the DuckDB shell, create a schema and a sample orders table:
“`sql
CREATE SCHEMA IF NOT EXISTS raw;
CREATE OR REPLACE TABLE raw.orders (
order_id INTEGER,
customer_name VARCHAR,
product_name VARCHAR,
order_date DATE,
quantity INTEGER,
unit_price DECIMAL(10, 2),
order_status VARCHAR
);
“`
Then insert some sample records covering a range of customers, products, dates, and order statuses including `completed`, `processing`, `returned`, and `cancelled`. This dataset will serve as the foundation for all the examples that follow.
—
## Models and Sources: The Core Concepts
Two concepts are fundamental to working with dbt: **models** and **sources**.
### Sources
A source represents an existing table or view in your database that dbt did not create — typically raw data loaded by an application or an ingestion pipeline. Sources are defined in YAML configuration files, and they allow your model SQL files to reference these underlying tables by name rather than hard-coding table identifiers.
For example, a source YAML file for our orders table would look like this:
“`yaml
version: 2
sources:
– name: raw
schema: raw
tables:
– name: orders
“`
The benefit of this approach becomes clear as projects scale. If the source table is ever renamed or moved, you update the source definition in one place, and every model that references it continues to work without modification.
### Models
A model is a SQL file that dbt uses to create a new table or view in your target database. Models reference sources (and other models) using a special syntax, and dbt automatically tracks the dependency graph between them.
Here is a model that builds a customer order summary from our raw orders source:
“`sql
{{ config(materialized=’table’) }}
with completed_orders as (
select
order_id,
customer_name,
order_date,
quantity,
quantity * unit_price as order_value
from {{ source(‘raw’, ‘orders’) }}
where lower(order_status) = ‘completed’
)
select
customer_name,
count(*) as completed_order_count,
sum(quantity) as total_units_purchased,
round(sum(order_value), 2) as total_revenue,
round(avg(order_value), 2) as average_order_value,
min(order_date) as first_order_date,
max(order_date) as most_recent_order_date
from completed_orders
group by customer_name
“`
Place both the YAML source file and the SQL model file inside the `models/` directory of your project. To execute the transformation, run:
“`powershell
dbt run
“`
dbt will find your model, resolve its dependency on the source, execute the SQL, and create the resulting table in your database. You can verify the output by querying the newly created table directly in DuckDB.
While this example involves just one table, the same pattern scales seamlessly to dozens or hundreds of interconnected transformations.
—
## Testing Your Data
One of dbt’s most valuable features is automated data testing. Tests are defined in YAML alongside your models and sources, and they can be executed independently or as part of a full build.
dbt provides four built-in test types out of the box:
– **unique** — ensures all values in a column are distinct
– **not_null** — verifies that a column contains no null values
– **relationships** — checks that values in one column correspond to valid values in another column (similar to foreign key constraints)
– **accepted_values** — confirms that a column contains only a predefined set of values
### Not Null Test
To test that the `customer_name` column in your summary model contains no nulls, add the test definition to your YAML file:
“`yaml
models:
– name: customer_order_summary
columns:
– name: customer_name
data_tests:
– not_null
“`
If there are null values in the underlying data, dbt will flag the test as a failure. To simulate this, you can update a record in the source table to set `customer_name` to null, then run:
“`powershell
dbt build
“`
The `dbt build` command executes models and tests in the correct dependency order, reporting exactly which tests pass and which fail. When a test fails, dbt reports the error but does not delete or roll back the model — downstream models that depend on the failed model are typically skipped.
If you only want to run the tests without re-executing any models, use `dbt test` instead.
### Accepted Values Test
The accepted values test ensures that a column contains only specified entries. For an `order_status` column, the valid values might be `completed`, `processing`, `returned`, and `cancelled`. Define the test like this:
“`yaml
sources:
– name: raw
schema: raw
tables:
– name: orders
columns:
– name: order_status
data_tests:
– accepted_values:
arguments:
values:
– completed
– processing
– returned
– cancelled
“`
Run `dbt test` to validate the source table. If any row contains a value outside the allowed set, the test fails and reports the offending record. The same four built-in test types apply equally to models and sources, giving you consistent validation across your entire data pipeline.
—
## Documenting Your System
Good documentation is often the first casualty of a busy analytics team. dbt takes a different approach: because your models, tests, and metadata live alongside your SQL files, dbt can automatically generate project documentation — and it goes further by producing a visual lineage graph that shows exactly how models depend on one another.
Generate the documentation with:
“`powershell
dbt docs generate
“`
This creates a `catalog.json` file containing metadata about all your models, sources, and tests. To view it in a browser, serve it locally:
“`powershell
dbt docs serve
“`
This launches a web server (typically on port 8080) and opens a browser window showing your project’s documentation.
The automatically generated documentation is useful, but it becomes truly powerful when you add your own descriptions using YAML. You can document sources, models, and individual columns with plain-language explanations:
“`yaml
models:
– name: customer_order_summary
description: >
A dbt-created table containing one row per customer. It includes only completed
orders and summarises order counts, units purchased, revenue, and order dates.
columns:
– name: customer_name
description: “Customer represented by the summary row.”
data_tests:
– not_null
– name: total_revenue
description: “Total value of the customer’s completed orders.”
“`
After updating the YAML and regenerating the docs, the browser view becomes a rich, searchable reference that any team member can use to understand the data pipeline at a glance. The visual lineage graph is especially valuable for onboarding new team members — instead of reverse-engineering hundreds of SQL files, they can see the entire transformation pipeline immediately.
—
## Where to Go Next
dbt is a large and extensible ecosystem, and the topics covered here represent just the foundation. As you become more comfortable, consider exploring these advanced features:
– **Incremental models** — process only new or changed records instead of rebuilding an entire table on every run, dramatically reducing compute time for large datasets
– **Jinja templating** — a templating language that lets you add variables, conditions, loops, and reusable functions directly inside your SQL
– **Macros** — reusable blocks of Jinja and SQL logic that accept parameters and generate SQL dynamically, reducing duplication across your project
– **Snapshots** — capture how source records change over time, preserving historical values and enabling slow-change-dimension workflows
– **Reusable packages** — share and reuse models, macros, and tests across projects by leveraging packages created by other dbt teams
The official dbt Labs website at [getdbt.com](https://www.getdbt.com) provides comprehensive documentation, tutorials, and community resources for continuing your learning journey.
—
## Frequently Asked Questions
**What is dbt Core, and is it really free?**
Yes, dbt Core is a fully functional, free open-source tool released under the Apache 2.0 licence. You can install it locally and use it with any supported database without creating an account or providing payment information. dbt Platform is the fully managed, paid enterprise version that offers additional collaboration and governance features.
**Can dbt work with databases other than DuckDB?**
Absolutely. dbt supports a wide range of databases and cloud data warehouses including Snowflake, BigQuery, Amazon Redshift, Databricks, PostgreSQL, MySQL, and many more. Each requires its own adapter package, which is typically installed separately from dbt Core.
**Do I need to know Python to use dbt?**
No. dbt is primarily a SQL-based tool. While Python is needed to run dbt itself and to set up the virtual environment, the day-to-day work of writing models, tests, and documentation is done entirely in SQL and YAML. Jinja templating is also YAML/HTML-like rather than Python.
**What happens when a data test fails?**
dbt reports the failure and marks the test as errored. It does not roll back or delete any models that were already created. Downstream models that depend on a failed model are normally skipped during subsequent runs so that errors don’t propagate silently through the pipeline.
**How is dbt documentation different from regular documentation?**
Because dbt stores model definitions, test configurations, and metadata in YAML files alongside SQL, it can automatically generate up-to-date documentation. The visual lineage graph shows dependencies between models, which is something that traditional documentation approaches struggle to maintain as projects grow.
**Is dbt suitable for small projects?**
dbt is powerful, but it does add some overhead. For a project with only a handful of SQL scripts, the full dbt workflow might feel like overkill. However, if you anticipate growth or need testing and documentation from the start, even small projects can benefit from dbt’s structure.
**What is the difference between dbt run and dbt build?**
`dbt run` executes only the models (transformations). `dbt build` executes models and all their associated tests in dependency order, giving you a complete validation of your pipeline in a single command.
—
## Conclusion
dbt addresses one of the most common pain points in data engineering: managing growing collections of SQL transformations as proper, maintainable software projects. By introducing concepts like models, sources, automated testing, and auto-generated documentation with visual lineage, dbt brings the rigour of software engineering to analytics workflows.
The learning curve is gentle — if you can write SQL and understand YAML configuration, you can start using dbt productively within a single afternoon. The real payoff comes as your projects scale and the investment in structure, testing, and documentation pays dividends in reliability, maintainability, and team collaboration.
Whether you’re a data engineer looking to add a high-demand skill to your toolkit or a team lead searching for better ways to manage analytics pipelines, dbt offers a compelling solution. Start with the basics covered in this guide, experiment with your own data, and progressively explore the more advanced features as your needs grow.
Thank you for reading
