Materializations — view, table, incremental, ephemeral

This lesson on Materializations — view, table, incremental, ephemeral is hands-on and example-driven. You will configure and execute table, view, and incremental materializations in dbt against a Snowflake warehouse. You will choose the right refresh strategy—full refresh, append-only, or merge/delete+insert—to balance transformation compute costs and data latency.

What You'll Be Able To Do

• Configure dbt models using table, view, and incremental materialization strategies.
• Distinguish between full-refresh CTAS operations and incremental merge/append executions.
• Implement unique_key configurations to enable upsert logic via SQL MERGE statements.
• Apply the delete+insert incremental strategy to optimize warehouse performance on large updates.
• Execute incremental models using the dbt CLI and verify output records in Snowflake.

Detailed Concept Walkthrough

1. Table and View Materializations

Table and view materializations represent the two foundational approaches to persisting or virtualizing transformation logic in the warehouse.

• Execution Flow: Table materializations build a physical table on every run using CREATE TABLE AS SELECT (CTAS), completely overwriting previous data.
• Under the Hood: View materializations execute CREATE OR REPLACE VIEW, saving only the SQL query logic in the warehouse catalog without storing physical data on disk.
• Trade-off Analysis: Views consume zero storage and compute upon build but re-execute SQL on every downstream read, whereas tables require upfront compute and storage but deliver faster query performance for end users.

-- models/staging/stg_customers.sql
{{ config(materialized='view') }}

select
    customer_id,
    first_name,
    last_name,
    email
from {{ source('raw_data', 'customers') }}

Key Takeaway: Use views for lightweight staging transformations and tables for computationally heavy models queried frequently by downstream consumers.

2. Incremental Materialization and Append Strategy

Incremental materialization transforms only newly added or modified data since the previous dbt run to dramatically reduce compute time.

• Initial Run Lifecycle: On the first execution, when the target relation does not yet exist in the warehouse, dbt falls back to a CTAS statement identical to a table materialization.
• Append-Only Execution: On subsequent runs without a unique key, dbt isolates new records into a temporary staging table and executes an INSERT INTO statement to append them directly onto the existing physical table.
• Syntax Rule: Incremental models check the warehouse state at runtime to determine whether to perform a full table creation or an incremental load.

-- models/marts/fct_orders.sql
{{ config(
    materialized='incremental'
) }}

select
    order_id,
    customer_id,
    order_date,
    order_total
from {{ ref('stg_orders') }}

Key Takeaway: An incremental model without a unique_key defaults to an append-only pattern after its initial full-table creation.

3. Merge Strategy with Unique Keys

The merge strategy updates existing records while inserting new records by matching source rows to target rows against a specified primary key.

• Mechanism: Specifying unique_key tells dbt to generate an atomic MERGE INTO SQL statement comparing incoming rows with existing records in the target table.
• Under the Hood: Matched rows with identical unique keys are updated with new column values, while non-matching rows are inserted as new records.
• Configuration Nuance: If unique_key is omitted when records are updated at the source, subsequent runs will create duplicate rows in the target table rather than updating them.

-- models/marts/fct_customer_balances.sql
{{ config(
    materialized='incremental',
    unique_key='customer_id'
) }}

select
    customer_id,
    current_balance,
    last_updated_at
from {{ ref('stg_customer_balances') }}

Key Takeaway: A unique_key configuration is required to prevent duplicate records and enable update-and-insert behavior during incremental runs.

4. Advanced Incremental Strategies and Delete+Insert

Advanced incremental configurations optimize write operations when standard MERGE operations degrade warehouse performance or when granular column updates are needed.

• Mechanism: The delete+insert strategy splits upserts into a two-step operation that removes matching rows based on the unique_key before bulk-inserting incoming records from the temporary table.
• Under the Hood: Some modern warehouses optimize delete-and-insert cycles faster than evaluating row-by-row comparisons across wide tables in a MERGE statement.
• Configuration Nuance: You can refine merge performance further using merge_update_columns to restrict updates to only the columns that actually change.

-- models/marts/fct_events_delete_insert.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='delete+insert'
) }}

select
    event_id,
    user_id,
    event_type,
    event_timestamp
from {{ ref('stg_events') }}

Key Takeaway: Choose delete+insert over merge when dealing with data warehouses or workloads where merge overhead degrades build performance.

Topics Covered in Materializations — view, table, incremental, ephemeral

1. Refresh Strategies (0:00 - 1:08) — Differentiates full refresh rebuilds from incremental append and merge strategies.
2. Materialization Overview (1:08 - 1:31) — Lists dbt materialization types and frames table, view, and incremental targets.
3. Table Materialization (1:31 - 3:12) — Explains physical table creation using CTAS statements and verifies Snowflake persistence.
4. View Materialization (3:12 - 4:24) — Demonstrates defining database views that execute SQL queries at read time.
5. Incremental Initial Run (4:24 - 5:28) — Details how the first incremental execution builds the base target table via CTAS.
6. Append Strategy (5:28 - 6:33) — Demonstrates how subsequent incremental runs append new source records without updating old rows.
7. Merge Strategy (6:33 - 8:41) — Explains configuring unique_key to perform atomic updates and inserts on modified data.
8. Advanced Strategies (8:41 - 10:14) — Explores merge update column restrictions and the delete+insert alternative pattern.

dbt Cheat Sheet

• config(materialized='table') — Builds persistent physical table using CTAS on every run

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

• config(materialized='view') — Creates database view without persisting underlying data

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

• config(materialized='incremental') — Appends new records or merges changes into target

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

• unique_key='<column>' — Identifies matching records to update rather than duplicate

  {{ config(materialized='incremental', unique_key='order_id') }}
  

• incremental_strategy='delete+insert' — Deletes matching keys then inserts new rows from temp

  {{ config(incremental_strategy='delete+insert') }}
  

• dbt run --select <model> — Executes compilation and warehouse transformation for a model

  dbt run --select fct_orders
  

Comparison Table

Common Pitfalls

• Mistake: Expecting a table materialization to process only modified records. Avoid: Switch to incremental materialization because table materializations always execute full CTAS rebuilds.
• Mistake: Omitting the unique key config on an incremental model with updated source rows. Avoid: Explicitly define unique_key to prevent duplicate records from appending on every run.
• Mistake: Assuming an incremental model executes append or merge logic on its first run. Avoid: Note that initial incremental runs execute full CTAS statements because target tables do not exist.

FAQs

• What happens if an incremental model is run for the first time? dbt performs a full refresh using a CTAS statement because the target table does not yet exist.
• Can table materialization perform an incremental update? No, table materializations always perform a complete rebuild via full refresh and do not support incremental logic.
• How does dbt handle incremental updates if no unique_key is configured? dbt defaults to an append-only strategy, inserting all incoming records from the temporary table and causing duplicates if IDs overlap.
• Why would I use the delete+insert strategy instead of merge? Some warehouses process deletes followed by bulk inserts more efficiently than complex row-by-row MERGE evaluations on large datasets.