Where dbt Fits
From checkout page to dashboard
Follow one order from the shop's checkout page to the revenue dashboard. The order is written to the application's transactional database. A loading tool copies that row, with the rest of the table, into the warehouse's analytics database each night, into a schema the team calls raw, with the columns exactly as the application stored them. dbt reads raw.orders, joins and aggregates the filtered rows into tables the business can use, and writes those tables to a schema called prod in the same database. The dashboard tool queries analytics.prod.daily_revenue and draws a chart.
- Checkout app (source, transactional database)
- Payments provider (source, payments API)
- Loader (transform, extract and load tool); freshness: nightly
- raw schema (storage, warehouse)
- dbt (transform, dbt run)
- prod schema (storage, warehouse)
- Revenue dashboard (consumer, BI tool)
- Checkout app -> Loader
- Payments provider -> Loader
- Loader -> raw schema
- raw schema -> dbt
- dbt -> prod schema
- prod schema -> Revenue dashboard
- Sources: Checkout app, Payments provider
- Load: Loader
- Warehouse: raw schema, dbt, prod schema
- Serving: Revenue dashboard
Extract, load, transform
That path has 3 jobs. Extraction pulls rows out of the systems that produce them. Loading lands those rows in the warehouse unchanged. Transformation turns the landed rows into the shapes the business asks for. dbt does the third job, and only the third: it holds no connection to the checkout database or the payments API, and it copies no row across the network. Every action it takes is a SQL statement sent to the warehouse, which reads one of its own tables and writes another.
| Job | Who does it | Where the rows are afterwards | dbt's part |
|---|---|---|---|
| Extract | loading tool | in flight from the source system | none |
| Load | loading tool | raw schema, unchanged | none; dbt reads what landed |
| Transform | dbt | prod schema, reshaped | the whole job |
| Serve | BI tool | on screens | none; dbt built what it reads |
Why transformation comes after loading
Doing the transformation after loading, inside the warehouse, is what lets a dbt model be a plain SELECT. Older pipelines transformed rows in flight, in a separate processing engine, before they reached the warehouse: a change to a metric meant a change to a program, and the raw rows were gone by the time anyone wanted to recompute. With the raw tables kept, every model can be rebuilt from scratch at any time, and a wrong definition of revenue costs one corrected file and one run.
The split also sets the edges of what you can fix in dbt. A missing column in raw.orders is a loading problem, because the loader decides what lands. A dashboard that times out is a serving problem, or a sign that the model it reads should be a table and not a view; the View or Table section takes that up. A revenue figure that disagrees with finance is a transformation problem, and that one is yours.
A second source
The second source on the diagram shows why the raw schema is worth keeping. The payments provider delivers a charges table with amounts in cents and a created column holding a Unix timestamp; the checkout app stores amount in dollars and ordered_at as a timestamp. Neither system knows about the other. The model that matches a charge to its order lives in dbt, where both tables already sit in one warehouse and one SELECT can read both. Had the match been attempted in the loader, every new source system would need a new home for that logic.
Inside the warehouse band, the 2 schemas have different owners. Only the loader writes to raw, so a model can trust that what it read this morning is what landed. Only dbt writes to prod, so a table there can be dropped and rebuilt without asking whose work it was. Keeping those 2 rules is most of what it takes for the diagram to stay true a year from now.
Timing follows the same split. The loader starts at 6:00 and finishes by 6:20; the scheduler starts dbt run at 6:30; the dashboards refresh at 7:00. A table built at 6:30 holds the rows landed by 6:20. An order placed at 6:45 reaches raw only with the next night's load, so no model shows it before then, whether it is built as a table or a view, because a model reads only what the loader has landed. A view gets ahead of a table only when rows land after the last dbt run, as with a loader that runs every 15 minutes, a difference the View or Table section explains.
- What belongs to dbt
- Every SELECT that turns raw tables into tables the business reads
- The order those SELECTs run in
- Whether each result is stored as a view or a table
- The names of the raw tables it reads, declared in one place
- What belongs to other tools
- Copying rows out of source systems
- The clock that starts a load and then a run
- Drawing charts over the finished tables
- Storing and scanning the rows
- Do
- Point dbt only at tables a loader has already landed
- Keep raw tables unchanged so every model can be rebuilt
- Let the scheduler finish the load before dbt run starts
- Avoid
- Expect dbt to copy rows from an API
- Edit raw tables by hand to fix a model
- Transform rows before they reach the warehouse
The loader and dbt usually share one scheduler: load first, then dbt run, so the models see this morning's rows. Running dbt on a schedule has a lesson of its own later in the track.
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.