View or Table

Concepts covered: Materializations

Storing the SELECT or the rows

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.

Telling dbt which one

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.

{{ config(materialized='table') }}

select
    customer_id,
    sum(amount) as lifetime_amount,
    count(*) as order_count
from {{ source('shop', 'orders') }} as src
where src.status = 'complete'
group by customer_id

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.

models:
  shop_analytics:
    +materialized: view
    reports:
      +materialized: table

Choosing

The choice follows from who reads the model and how often. A view costs nothing to build and nothing to store, and it suits a model that reshapes rows without heavy work, that few queries read, or that has to show the latest landed rows between runs. A table suits a SELECT that is expensive, a result that many dashboards read, or figures that every reader must see identically until the next run. An aggregation over millions of orders read by 50 dashboards every morning is the strongest case for a table: as a view, the warehouse would run that aggregation 50 times before lunch.

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.

{{ config(materialized='table') }}

select
    customer_id,
    sum(amount) as lifetime_amount,
    count(*) as order_count
from {{ source('shop', 'orders') }} as src
where src.status = 'complete'
group by customer_id

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.