dbt Materializations

A materialization decides what DDL dbt 37,942 wraps around a model's SELECT. Set defaults per folder in dbt_project.yml and override them in a model's config():

dbt_project.yml: staging as views, marts as tablesYAML

name: booknest
version: "1.0.0"
profile: booknest              # the profile name in profiles.yml
models:
  booknest:
    staging:
      +materialized: view        # thin renaming layer, always fresh
    marts:
      +materialized: table       # what analysts and dashboards query

dbt's built-in materializations
Materialization	dbt creates	Use it for
view	CREATE VIEW	Staging: cheap, always current
table	CREATE TABLE AS, swapped in by rename	Marts queried often
incremental	Table, then insert, merge or delete+insert	Large, append-mostly facts
ephemeral	Nothing: inlined as a CTE	Reusable logic, no object

dbt build runs seeds, snapshots, models and tests in DAG order, testing each model before its dependents run:

Building the project on PostgreSQL, then on DuckDB

dbt build
dbt build --target duck

Output

17:22:38  12 of 21 OK created sql incremental model analytics.fct_sales  [SELECT 137944 in
  3.24s]
17:22:40  Done. PASS=21 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=21
...
17:24:45  12 of 21 OK created sql incremental model main.fct_sales  [OK in 2.42s]
17:24:46  Done. PASS=21 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=21

Each engine built 21 nodes: 8 models, the seed, the snapshot and 11 tests. On DuckDB 61,228 the fact table holds the same 137,944 lines and 3,509,959.19 gross as mart.fact_sales (BookNest's Star Schema), and SQL that only one engine accepts fails here first.

MENU