Models Over Sources

Concepts covered: Sources and seeds

A model is one SELECT

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.

Sources are declared once

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.

version: 2

sources:
  - name: shop
    database: analytics
    schema: raw
    tables:
      - name: orders
      - name: customers

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.

select
    order_id,
    customer_id,
    cast(ordered_at as date) as order_date,
    amount
from {{ source('shop', 'orders') }}
where status = 'complete'

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.

The SQL dbt writes around the SELECT

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.

select
    order_id,
    customer_id,
    cast(ordered_at as date) as order_date,
    amount
from analytics.raw.orders
where status = 'complete'

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.

create or replace view analytics.dbt_maria.stg_orders as
select
    order_id,
    customer_id,
    cast(ordered_at as date) as order_date,
    amount
from analytics.raw.orders
where status = 'complete'

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.

Source names and model names

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.

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.