Models: Beginner

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.

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.

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.

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.

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.

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.

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.

A model is a file in the models/ folder with a .sql extension. The file holds one SELECT statement. The file's name is the model's name, so models/stg_orders.sql is the model stg_orders. dbt names the table or view it builds from that file stg_orders too. That is the whole definition. Adding a file adds a model: the file needs no header, and no list of models has to be registered or updated.

The model needs to read raw.orders. Writing raw.orders in the FROM clause works, and the model in the first section did. The cost shows up around the 40th model: when the loader is reconfigured to land tables in a schema called raw_shop, 40 files have to change. The team finds out which ones were missed when the morning run fails. dbt's answer is to declare each raw table once, in a YAML file in the models folder, and have every model name it through a function.

The source shop names the loader's landing area. Inside it are the tables orders and customers. A model reads a source table with {{ source('shop', 'orders') }} in its FROM clause. The double braces mark a region dbt fills in before any SQL leaves your machine. dbt looks up shop to find analytics.raw, then appends orders and writes analytics.raw.orders into the statement. When the landing schema moves, one line in the YAML changes and every model follows.

stg_orders does what most models over a raw table do. It keeps the 4 columns later models need and drops status once the filter has used it, it renames ordered_at to order_date, and it casts that timestamp to a date. Every model that reads it inherits those clean columns without repeating the work.

The braces are the syntax of a templating language called Jinja, and dbt renders every braced region before sending anything. This lesson uses 2 of its functions: source() here, and ref() in the last section. The Jinja lessons later in the track open the rest.

Running dbt run on this project produces 2 pieces of SQL for the model, and dbt saves both to disk so you can read them. The first is the compiled SQL: the file with every braced region replaced by the text it rendered to. It lands at target/compiled/shop_analytics/models/stg_orders.sql, under the project's name, and reads as plain SQL.

The second is the SQL dbt sends. dbt takes the compiled SELECT and wraps it in the statement that makes a view or a table, with the model's own name as the target, and saves that at target/run/shop_analytics/models/stg_orders.sql. This is the statement the warehouse receives, and it is the one to read when a run fails with an error you do not recognise.

Read the 2 files together and the division of labour is visible. Everything from select to the final line is yours, copied through unchanged. The first line is dbt's, and it is the only part that changes when the model becomes a table instead of a view. The schema dbt_maria is also dbt's: it comes from the connection profile the run used, so the same file builds into a different schema for each person who runs it.

Keep the 2 kinds of name apart. A source name is a name for something the loader created, and dbt never builds it: source('shop', 'orders') renders to a table that must already exist when the run starts. A model name is a name for something dbt will build, and the build creates it. If raw.orders is missing, the error comes from the warehouse, saying the table doesn't exist, because dbt sent a SELECT over a name it took on trust.

The first line of the run SQL carries a decision: is stg_orders a view or a table? A view stores the SELECT. Querying it runs that SELECT against raw.orders at that moment, so the view reflects whatever the loader has landed so far, and each read costs the full query. A table stores the rows. Building it runs the SELECT once and keeps the result, so a read is a scan of stored rows, and the rows are as old as the last dbt run.

dbt calls this choice the model's materialization, and the default is view. To make a model a table, set materialized in a config block at the top of the file. The block is another braced region, rendered before the SQL is sent, and it produces no text of its own: it tells dbt how to wrap the SELECT that follows.

The run SQL now begins create table analytics.dbt_maria.customer_totals as, and the warehouse does the aggregation during the run rather than during each read. On most warehouses dbt builds the new table under a temporary name and swaps it into place at the end, so a dashboard reading customer_totals sees either yesterday's complete rows or today's, never a half-built table.

The run log names the materialization on each model's line, a view for stg_orders and a table for customer_totals, and the table's line carries the longer time, because the warehouse did the aggregation during the run.

The setting can also be made for a whole folder in dbt_project.yml, under the models: key. A file's own config block wins over the folder's setting, so a project can default to views and mark a folder of heavy models as tables, or the reverse.

The choice also changes what a stale number means. If customer_totals is a table and a customer's count is wrong, the first question is when the last run finished, because the table cannot know about rows landed since. If it is a view and the count is wrong, the run time is irrelevant: the view computed its answer from raw.orders as it stood a second ago, so the fault is in the SELECT or in what the loader landed.

Storage is the quieter difference. A view occupies a row in the warehouse's catalog and nothing else. A table occupies the size of its result for as long as it exists, and a project of 300 tables where 250 could have been views pays for 250 copies of rows that already sit in raw. The habit that keeps that in check is asking of each table model what a reader gains from the rows being stored.

The first section's daily_revenue cast ordered_at to a date and kept only completed orders itself, and stg_orders now does both. Reading stg_orders in place of the raw table removes that duplicated work. dbt provides a function for it: {{ ref('stg_orders') }}. It takes a model's name and does 2 things: it renders to the table or view this run built for that model, and it links daily_revenue to a model dbt must build first. source() adds a node to the graph too, but a source is a node dbt never builds; it only has to exist when the run starts.

When the run uses a profile whose schema is dbt_maria, stg_orders is built as analytics.dbt_maria.stg_orders, and ref('stg_orders') in the next model renders to that same name. On the scheduler, with a profile whose schema is prod, the first model is built as analytics.prod.stg_orders and the ref renders to that. The file never changes. Had the author written analytics.prod.stg_orders by hand, every developer's private build would read production's copy of the model in place of their own, and a broken change would look fine until it shipped.

The second thing ref() does happens before any SQL is sent. While reading the project, dbt collects every ref() and every source() in every file and draws a graph: each model and each source table is a node, and each ref() is an edge from the model named to the model that named it. dbt takes the build order from that graph. A model is built only after every model it refs has finished, so stg_orders goes first and daily_revenue after it, with no list of steps written anywhere in the project.

The log of a run reads as that order. Each node gets a START line when it begins and a finish line when it ends, and the lines are numbered 1 of 2 and 2 of 2. The final summary counts the passes, with errors and skips counted on their own.

With more than 1 thread, dbt builds nodes that have no path between them at the same time: 2 models that both ref stg_orders and nothing else start together once it finishes. The order the graph imposes still holds; only the independent nodes overlap.

dbt run builds models and nothing else. dbt build walks the same graph and builds every kind of node the project has, models among them, in the same dependency order. In a project that holds only models, the 2 commands do the same work; later lessons add the node kinds that make build the command most teams schedule.