What dbt Is For

The query that lived in a dashboard

A typical analytics team has a warehouse that a loading tool fills every night with raw tables copied from the company's applications. Every number the business looks at begins as a SELECT someone wrote over those tables: daily revenue, active customers, refund rate. The first version of each query lives where it was written, inside a dashboard tool or a saved worksheet, and for a while that works.

The trouble arrives with the second analyst. She needs daily revenue too, so she copies the query and changes one filter. 6 months later the finance team counts a refunded order as revenue and the product team does not, and nobody can say which of the 11 copies is the right one. The queries that create tables are in worse shape: someone runs CREATE TABLE statements by hand each morning, in an order kept in their head, and a missed step leaves a dashboard reading yesterday's numbers under today's date.

Transformation as code

dbt exists to turn that pile of saved queries into a project. Each transformation becomes a file holding one SELECT statement, kept in a folder under version control with every other transformation the company has. When you run dbt, it reads every file, works out which files depend on which, wraps each SELECT in the statement that creates a table or a view, and sends those statements to the warehouse in an order where every dependency is built before the file that reads it.

select
    cast(ordered_at as date) as order_date,
    sum(amount) as revenue
from raw.orders
where status = 'complete'
group by 1

Set that beside the version in the dashboard. The SELECT is the same text. What changed is where it lives and who runs it: the file sits next to every other transformation, its history is a git log, and a scheduler runs dbt run each morning in place of a person. A new analyst who needs daily revenue reads this file or builds on top of it, and the 11 copies never come into being.

Writing only the SELECT is the design decision the rest of dbt depends on. Because the CREATE TABLE and DROP statements are missing from a model, and so is any INSERT, dbt is free to choose those statements itself: the same file can be built as a view today and a table next month, and the same file can build into a private schema on your laptop and into the production schema on the scheduler. The author decides what the rows should be; dbt decides how they come to exist.

That division tells you what kind of tool dbt is. The joins and aggregations run inside the warehouse that already stores the tables. dbt's own work is generating the SQL and sending each statement in the right order, then reporting what came back. A dbt project is a description of the tables a company wants to exist, and dbt run is the act of making the warehouse match that description.

Saved queries, run by hand
  • Each copy drifts from the others
  • Build order lives in someone's head
  • A change is tested by running it in production
  • Nobody can list which tables depend on which
A dbt project
  • One file per transformation, under version control
  • dbt derives the build order from the files
  • The same files build a private copy for testing
  • The dependency graph is drawn from the files

What a run prints

Running dbt run in a project of 3 files prints one pair of lines per file: a START line when dbt sends the statement and an OK line when the warehouse returns, each naming the model and whether it was created as a view or a table. The last line totals the results: how many models passed, and how many failed or were skipped. The ref() section walks through that log line by line; for now the point is that a person typed one command and 3 tables came to exist in the right order.

The word model is dbt's name for one of these files, and this lesson takes models at their plainest: a SELECT over raw tables, built as a view or a table, reading other models by name. Everything else dbt does hangs off that file.

About this lesson

This section is part of the Models: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.