Materializations: `view` versus `table`

Description

A materialization is the answer to a question every dbt model has to answer: when you run dbt run, exactly what SQL object does dbt build from your SELECT? You already used one materialization without thinking twice about it — view, the one you configured in dbt_project.yml since module 1, and that module 2's four staging views inherited — but until now you never had a real reason to consider the alternative. That reason arrives in this module: dim_store, dim_date, and fact_orders aren't staging views that only rename columns — they combine data, and that work has a cost worth paying once, not on every query.

This lesson explains exactly what tells view apart from table — not in the abstract, but with a real experiment: you're going to change a piece of Kiosko raw data and watch, with your own eyes, that a view reflects it immediately and a table doesn't, until you decide to rebuild it. And it's going to show you the exact SQL dbt generates for each one, so the difference stops being a memorized rule and becomes something you watched run.

Connection to the module. Lesson 2 gave you ref(), the mechanism that declares what depends on what. This lesson gives you the other half of the decision: how each model's result gets stored. Lessons 4 and 5 are going to apply both pieces together to build dim_store, dim_date, and fact_orders for real.

An analogy: a live window, versus a photo already developed

Imagine two ways of showing someone what's happening on a busy street. The first is a glass window: every time someone looks through it, they see the street at that exact instant — if a car just drove by, they see it drive by; if the street changed a second ago, the window already reflects that change, with no one having to do anything. The second is a photograph, developed and printed at a specific moment: it shows the street exactly as it was at the instant the photo was taken — and it's going to keep showing that same scene, without a single pixel changing, until someone decides to take (and develop) a new photo.

A view is the window: DuckDB doesn't store any data — it stores your SELECT, and every time someone queries that view, DuckDB runs the complete query again, at that same instant, against the data as it stands right now. A table is the photograph: dbt runs your SELECT once, at the moment you run dbt run, and stores the physical result — real rows and columns, taking up space on disk. Querying that table afterward doesn't run the original SELECT again; it simply reads what's already stored, not caring whether the source data has changed since then. Neither is "better" in the abstract — the right question, which this lesson answers with a real experiment, is which one you need in each case.

Worked example: the same change, two different results

You're going to change a piece of Kiosko raw data — add a fourth store to stores.csv — and watch what happens to stg_stores (a view) versus what happens to dim_store (a table, which you're going to build formally in lesson 4; for now, a minimal model that just reorders stg_stores), without running dbt run in between.

First, confirm the starting state:

import duckdb
con = duckdb.connect("kiosko.duckdb")
print("stg_stores:", con.sql("select count(*) as n from stg_stores").fetchone()[0])
print("dim_store:", con.sql("select count(*) as n from dim_store").fetchone()[0])

What to expect.

stg_stores: 3
dim_store: 3

Both match: three stores, the same number you already know from stores.csv. Now, without running any dbt command, edit the raw file directly:

echo "S04,Kiosko Este,Medellin" >> raw_data/kiosko/stores.csv

-- raw_data/kiosko/stores.csv, after the change
store_id,store_name,city
S01,Kiosko Centro,Bogota
S02,Kiosko Norte,Lima
S03,Kiosko Sur,Santiago
S04,Kiosko Este,Medellin

And query again, without having run dbt run:

con = duckdb.connect("kiosko.duckdb")
print("stg_stores (view, re-runs live):", con.sql("select count(*) as n from stg_stores").fetchone()[0])
print("dim_store (table, frozen photo):", con.sql("select count(*) as n from dim_store").fetchone()[0])

What to expect.

stg_stores (view, re-runs live): 4
dim_store (table, frozen photo): 3

There's the difference, with a concrete number on each side. stg_stores is a view: its original SELECT points at raw_data/kiosko/stores.csv with a glob (external_location), and DuckDB ran that whole read again the instant you queried it — like looking through the window, it saw the street as it is now, new store included, with no one telling it about the change. dim_store is a table: its content got fixed the last time you ran dbt run, before stores.csv changed — like a photo already developed, it keeps showing three stores, exactly what was there the instant it was taken, not caring that reality has already changed.

Now, rebuild the table explicitly:

dbt run --select dim_store

What to expect.

1 of 1 START sql table model main.dim_store .................................... [RUN]
1 of 1 OK created sql table model main.dim_store ............................... [OK in 0.06s]

Finished running 1 table model in 0 hours 0 minutes and 0.16 seconds (0.16s).

Completed successfully

Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1

con = duckdb.connect("kiosko.duckdb")
print("dim_store (table, after dbt run):", con.sql("select count(*) as n from dim_store").fetchone()[0])

What to expect.

dim_store (table, after dbt run): 4

Only now, after an explicit dbt run, does dim_store "take the new photo" and reflect the four stores. None of this is a table defect — it's exactly its designed behavior: a table is a stable result, that doesn't change under your feet while you work with it, at the cost of you (or your orchestrator, in airflow-and-declarative-orchestration-guide) having to explicitly decide when to refresh it.

Undo the change before continuing, so the rest of the module works over the original three stores:

sed -i '' '/S04,Kiosko Este,Medellin/d' raw_data/kiosko/stores.csv   # macOS
dbt run --select dim_store

Going deeper: exactly what DDL dbt builds

You can confirm the difference with no data experiment at all, by looking directly at what dbt runs against DuckDB. After a dbt run, inspect the files dbt stores in target/run/:

cat target/run/kiosko_analytics/models/staging/kiosko/stg_stores.sql

What to expect (simplified).

create view "kiosko"."main"."stg_stores__dbt_tmp" as (
    select
    store_id,
    store_name,
    city
from 'raw_data/kiosko/stores.csv'
);

cat target/run/kiosko_analytics/models/marts/dim_store.sql

What to expect (simplified).

create table "kiosko"."main"."dim_store__dbt_tmp" as (
      select
    store_id,
    store_name,
    city
from "kiosko"."main"."stg_stores"
    );

The central difference is two words: create view versus create table ... as. A CREATE VIEW tells DuckDB "store this query, not its result" — cheap to create, because it copies no data. A CREATE TABLE ... AS tells DuckDB "run this query now, and store the result" — more expensive to create (the complete SELECT has to run and every row gets copied), but afterward every query against that table is as fast as reading already-computed data, with no repeated work.

(The __dbt_tmp suffix and the final name with no suffix aren't a detail you need to memorize in this lesson: dbt builds the new object under a temporary name, and only if that build finishes with no errors does it rename it to the final name — stg_stores, dim_store — safely replacing the previous version. If something fails partway through, the previous version stays intact and queryable — you never end up with a half-built model.)

When view, when table: the complete argument

Three factors explain why module 2 chose view for staging, and why this module is going to choose table for the marts (lesson 6 applies this argument model by model):

• Cost of recomputing. A staging model does no JOIN or GROUP BY — it's, at most, a column selection with a few cast()s. Running that query again every time someone uses it costs almost nothing. fact_orders, on the other hand, is going to join three different models (lesson 5) — recomputing that join on every query from every person who opens a dashboard would mean paying the same cost, over and over, for a result that doesn't change between one dbt run and the next.
• Freshness versus stability. A view always reflects the current state of its source data — you saw this with stg_stores in the worked example; a table reflects the state at the moment of its last run. For staging, that immediate freshness is an advantage: any change in raw_data/ shows up with no extra step. For a mart several people are going to query throughout the day, stability is the advantage: everyone sees the same number, at the same moment, until the next scheduled run — with no row changing mid-analysis.
• Disk space. With data as small as Kiosko's this is irrelevant in practice, but in a real project, materializing as table a model that does no heavy computation wastes storage with no speed gain to justify it.

The rule isn't "table is always better" or "view is always better" — it's that the correct materialization depends on how much work the model does and how many people are going to query it. A staging model does little work and, almost always, only another dbt model consumes it (never a person directly) — view is nearly free there. A mart does real work (JOINs, in fact_orders's case) and gets consumed by people and BI tools, repeatedly — paying that cost once per run, with table, is almost always the right choice.

Common mistakes

Assuming a table "updates itself" when the source data changes. What happens: someone builds dim_store as table, changes stores.csv, and expects to see the change reflected on the next query, without running dbt run. Why it happens: in tools where "everything recalculates automatically" (a spreadsheet with formulas, for example), that's the expected behavior, and it's easy to apply the same intuition to a warehouse table. How to spot it: if you query a table after changing its source and the number didn't change, it isn't a bug — it's exactly this lesson's "developed photo" behavior; confirm with dbt run --select table_name whether the change has already been applied. How to fix it: a table never updates itself — it needs an explicit dbt run (or, in production, an orchestrator that triggers it for you, airflow-and-declarative-orchestration-guide's topic).

Materializing a model with an expensive JOIN as view, "because view is the default." What happens: someone writes fact_orders.sql with its three ref()s and its two JOINs, and leaves it materializing as view (the project's default up to this module), without thinking about it. Why it happens: view was the only value you've used until now, and changing it feels like an unnecessary extra step. How to spot it: if a model has more than one FROM/JOIN, every query against it — including any other view or table that depends on it — pays the full cost of the JOIN again, every time. With data as small as Kiosko's the cost is invisible; with real data, stacking that cost on every query from every dashboard shows. How to fix it: this module's lesson 6 formalizes the rule, but get ahead of it now: any model with a real JOIN that's going to be queried more than once deserves table, not view.

Confusing the temporary name (__dbt_tmp) with an object that needs manual cleanup. What happens: someone sees stg_stores__dbt_tmp mentioned in a log or in target/run/ and wonders whether they have to delete it by hand. Why it happens: the __dbt_tmp suffix doesn't look like part of the model's "real" name, and gives the impression of being a forgotten temporary file. How to spot it: if you query kiosko.duckdb with a DuckDB client after a successful run, you're never going to find any object with the __dbt_tmp suffix persisting — dbt creates it, uses it to build the result safely, and renames it (or drops it, depending on the internal mechanism) within the same run. How to fix it: no manual cleanup is needed — it's part of the internal mechanism of how dbt materializes a model safely, not an artifact dbt forgot to pick up.

Exercises

Exercise 1 — Predict the result before running anything. Without running any command, answer: if you added a fifth row to products_v1.csv and queried stg_products (a view) without running dbt run, would you see the new row? And if you queried dim_store (a table) after that same change, also without running dbt run?

Exercise 2 — Reproduce the experiment with products_v1.csv. Repeat the worked example's experiment, but this time adding a fifth product to products_v1.csv and comparing stg_products (view) against any table that depends on it. Confirm the same pattern: the view reflects the change immediately, the table needs an explicit dbt run.

echo "P005,Chocolate Bar,snacks,0.50,2026-08-01" >> raw_data/kiosko/products_v1.csv

import duckdb
con = duckdb.connect("kiosko.duckdb")
print("stg_products:", con.sql("select count(*) as n from stg_products").fetchone()[0])  # 5, immediately

Exercise 3 — Explain the analogy in your own words. Using the comparison of the live window versus the developed photo, explain in 2-3 sentences why a real data team, with a dashboard several people query throughout the day, would prefer the mart behind that dashboard to be a table and not a view — even knowing the table might be "stale" until the next run.

Summary and next step

In this lesson you saw, with a real experiment and not just a definition, the complete difference between view and table: a view re-runs its query on every use and always reflects its source data's current state; a table runs its query once, at dbt run, and stores a stable result until the next explicit run. You inspected the exact DDL dbt generates for each one (create view versus create table ... as), and saw the complete argument of cost, freshness, and space that explains why staging uses view and why this module's marts are going to use table.

Before moving on you should be able to: explain, without looking at this lesson, what happens to a view and what happens to a table when the source data changes, without running dbt run; and name the three factors that determine which materialization suits a given model.

With ref() (lesson 2) and materializations (this lesson) settled, lesson 4 builds the project's first two real marts: dim_store and dim_date, both materialized as table, both using what you just learned.

Resources

• dbt Developer Hub — "About materializations," the complete official reference for the five materialization types dbt supports (view, table, incremental, ephemeral, materialized view) — this lesson only covers the first two; incremental arrives in module 6. docs.getdbt.com/docs/build/materializations. In English.
• dbt-duckdb — the official GitHub repository, with the specific implementation of each materialization for the DuckDB adapter, including the safe-rename mechanism (__dbt_tmp) you saw in this lesson's deeper dive. github.com/duckdb/dbt-duckdb. In English.
• DuckDB — official CREATE VIEW and CREATE TABLE documentation, the SQL foundation both materializations are built on. duckdb.org/docs/stable/sql/statements/create_view · duckdb.org/docs/stable/sql/statements/create_table. In English.