Getting Started with dbt: A Practical Guide for Analytics Engineering Teams

In this article let us look at dbt (data build tool) and how analytics engineering teams can use it to bring software engineering practices to their data transformations. If your team writes a lot of SQL that lives in your data warehouse with no version control, no testing, and a tangled mess of dependencies, dbt is probably the tool you need.

I have seen teams go from hundreds of undocumented stored procedures to a clean dbt project with lineage graphs, tests, and CI/CD in a matter of weeks. This article walks through the basics — what dbt actually does, how to set it up, and how to write your first models.

What Problem Does dbt Solve?

Before dbt, the typical analytics workflow looked something like this: write SQL queries in your warehouse UI, save them as views or scheduled queries, and hope nobody breaks anything when they edit a query at 5 PM on a Friday. Documentation lived in someone’s head. Business logic was copied and pasted across twenty different dashboards.

dbt brings the software engineering workflow to analytics. You write SQL SELECT statements (called models), dbt compiles them and runs them against your warehouse in the right order. You get version control through Git, automated testing for data quality, and documentation that actually stays up to date.

It is worth clarifying something upfront: dbt is the T in ELT. It does not extract data from anywhere. It assumes your data is already loaded into your warehouse (BigQuery, Snowflake, Redshift, etc.) and focuses entirely on transforming it into something useful for analytics.

Setting Up dbt

I will use dbt Core (the open-source CLI) for this walkthrough since it is what most teams start with. dbt Cloud is the managed version if you prefer a browser-based IDE and scheduled runs out of the box.

Installation

The easiest way is using pip. Make sure you have Python 3.8 or newer.

pip install dbt-core dbt-bigquery

Swap dbt-bigquery for dbt-snowflake, dbt-redshift, or dbt-postgres depending on your warehouse. The adapter is what lets dbt talk to your specific database.

Initialising a Project

dbt init my_analytics_project
cd my_analytics_project

dbt will ask for your warehouse connection details — project ID and dataset for BigQuery, account and role for Snowflake, etc. These go into ~/.dbt/profiles.yml. Here is what a BigQuery profile looks like:

my_analytics_project:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: my-gcp-project
      dataset: dbt_dev
      threads: 4

Run dbt debug to make sure the connection works. If you see green checks across the board, you are good to go.

Project Structure

dbt scaffolds a folder structure that is worth understanding before you start writing models:

my_analytics_project/
├── models/          # Your SQL transformation files live here
├── tests/           # Custom data tests
├── macros/          # Reusable Jinja snippets
├── seeds/           # CSV files you want to load as static tables
├── analyses/        # Ad-hoc queries not part of your DAG
├── dbt_project.yml  # Project-level configuration
└── profiles.yml     # Actually lives in ~/.dbt/

Writing Your First Models

In dbt, a model is just a .sql file with a SELECT statement. dbt wraps it in a CREATE TABLE AS or CREATE VIEW AS depending on how you configure it.

Let us say we have raw order data and raw customer data already loaded into our warehouse as raw.orders and raw.customers. We want to clean these up and join them into a useful table for the analytics team.

Staging Models

First, create staging models that clean and standardise the raw data:

models/staging/stg_orders.sql:

WITH source AS (
    SELECT * FROM 
),

renamed AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        order_status,
        total_amount,
        CASE
            WHEN order_status = 'C' THEN 'COMPLETED'
            WHEN order_status = 'P' THEN 'PENDING'
            WHEN order_status = 'X' THEN 'CANCELLED'
            ELSE order_status
        END AS order_status_clean
    FROM source
)

SELECT * FROM renamed

models/staging/stg_customers.sql:

WITH source AS (
    SELECT * FROM 
),

renamed AS (
    SELECT
        customer_id,
        customer_name,
        signup_date,
        tier_level AS customer_tier,
        region_code AS region
    FROM source
)

SELECT * FROM renamed

Mart Model

Now we create a mart model that joins them into something the business can query:

models/marts/customer_orders.sql:

SELECT
    o.order_id,
    o.order_date,
    o.order_status_clean AS order_status,
    o.total_amount,
    c.customer_name,
    c.customer_tier,
    c.region,
    c.signup_date,
    DATE_DIFF(o.order_date, c.signup_date, DAY) AS days_since_signup
FROM  o
LEFT JOIN  c
    ON o.customer_id = c.customer_id

Two things to notice here. references raw source tables defined in a `sources.yml` file, and creates a dependency between models. dbt uses ref() to build a DAG and run models in the right order — staging models first, then marts.

Run everything with:

dbt run

dbt will spin up the number of threads you configured and build the models in dependency order. If something fails, you get a clear error message pointing to the model and the SQL that broke.

Adding Tests

Untested SQL is one of the main reasons data pipelines break silently. dbt has built-in tests you can add to your models without writing a single line of test logic.

Create a models/staging/schema.yml:

version: 2
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: order_status_clean
        tests:
          - accepted_values:
              values: ['COMPLETED', 'PENDING', 'CANCELLED']

  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

Run dbt test and dbt will check every order_id is unique and not null. If a test fails, it tells you exactly how many rows violated the condition. You can also write custom SQL tests for more complex checks — things like “revenue should never be negative” or “every order must have a valid customer”.

Documenting Your Models

dbt can generate a static documentation site from your model descriptions:

version: 2
models:
  - name: customer_orders
    description: >
      One row per order, enriched with customer information.
      Used by the sales dashboard and weekly revenue reporting.
    columns:
      - name: days_since_signup
        description: Days between customer signup and order date

Run dbt docs generate and then dbt docs serve, and you get a local site with a DAG visualisation showing exactly how all your models connect. This is one of those features that sounds minor but becomes invaluable once your project grows past 30 or 40 models.

Comparison: dbt vs Traditional Approaches

Approach	Code Management	Testing	Documentation	Dependency Management
Raw SQL in warehouse UI	None, SQL lives in UI	Manual, ad-hoc	None or external wiki	Manual — you remember
Stored Procedures	Versioned if you try hard	Custom scripts needed	Separate docs that go stale	You track it in your head
Python (Pandas/Spark)	Git-friendly	Unit tests possible	Docstrings, but not query-level	Code-based, but heavy
dbt	Git-native	Built-in, SQL-level	Auto-generated, stays in sync	Automatic DAG via ref()

Practical Limitations and What to Watch For

After using dbt across a few projects, here are things I have learned the slightly painful way:

dbt is not an orchestrator. It runs transformations and tests, but it does not extract or load data. You still need something to get data into your warehouse — Fivetran, Airbyte, or custom scripts. In production, you will likely orchestrate dbt runs through Airflow, Prefect, or Dagster.

Incremental models need thought. The default table materialisation drops and recreates the table every run. For large tables, you want incremental materialisation, but that means you need to design for upserts and decide how far back to look for changes. Get the incremental logic wrong, and you end up with missing or duplicate rows.

Jinja can get out of hand. It is tempting to write clever Jinja macros for everything, but every layer of abstraction makes your SQL harder for new team members to read. I have found it is usually better to repeat a bit of SQL than to write a macro that saves ten lines but nobody understands six months later.

Orphaned models. Since dbt builds a DAG from ref() calls, models that nobody references anymore still run. Check the lineage graph regularly and drop unused models. Your warehouse bill will thank you.

Source freshness. dbt can check if your source data is stale using dbt source freshness, but you need to configure it. A pipeline that runs successfully on empty source tables is still a failure.

Production Considerations

What changes in production versus a proof of concept:

• Use a dedicated CI/CD pipeline. Run dbt build on pull requests against a dev schema so you catch model changes before they hit production.
• Slim CI. For large projects, only run changed models and their downstream dependents instead of the full DAG.
• Separate environments. Have dev, staging, and production schemas. Use dbt’s target variable to switch between them.
• Store your artifacts. Keep manifest.json from dbt docs generate somewhere accessible — tools like dbt Power User for VS Code and various data catalogs use it.
• Monitor test failures. A failing test in dbt is silent unless someone looks at the run logs. In production, set up alerts (Slack, PagerDuty) for test failures.

Wrapping Up

dbt fills a real gap in the modern data stack. It takes something that nearly every data team does — write SQL to transform data — and wraps it with the engineering practices that software teams have been using for years. Version control, testing, documentation, and clear dependency management are not luxuries for a data team; they are the difference between a pipeline people trust and one they work around.

If your team is still managing transformations through stored procedures and wiki pages, start with a small dbt project on a single dataset. Even three or four models with tests and docs will give you a feel for why the approach works.