Materializations and Incremental Strategies

The Wherobots dbt adapter supports five materializations. Every persisted relation is a Havasu (Apache Iceberg) table. There is no file format to configure.

Materialization	What it builds	Notes
`view`	A view over the model’s query	Replaced atomically on every run.
`table`	A Havasu table	Replaced atomically on every run. Accepts `partition_by`.
`ephemeral`	Nothing	Inlined into downstream models as a common table expression (CTE).
`incremental`	A Havasu table, updated in place	Four strategies. See Incremental models.
`snapshot`	A slowly changing dimension type 2 (SCD2) history table	See Snapshots.

Views and tables

When a model is rebuilt over an existing relation of the same type, both view and table build with a single atomic create or replace statement. There is no drop, rename, or swap step, so the relation is never briefly missing and a failed run leaves the previous version in place.

models/staging/stg_zipcode_zones.sql

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

select
    zcta5ce10 as zip,
    ST_SetSRID(geometry, 4326) as zone_geom
from {{ source('us_census', 'zipcode') }}

If a relation of the other type already occupies the name, the adapter drops it first and then creates the new one, so a model switched from table to view, or the reverse, rebuilds without manual intervention. A cross-type switch is two statements, so it is not atomic: if the create fails, nothing is left at that name.

Partitioning a table

table models accept partition_by, either as a single column name or a list.

models/marts/fct_trips_hourly.sql

{{ config(
    materialized='table',
    partition_by=['trip_date']
) }}

select trip_date, trip_hour, zip, count(*) as trip_count
from {{ ref('int_trips_zoned') }}
group by trip_date, trip_hour, zip

Values pass through to the partitioned by (...) clause unparsed, so Iceberg transform expressions such as days(pickup_ts) or bucket(16, zip) work as well as plain column names. Because a table model is replaced in full on every run, changing partition_by takes effect on the next dbt run with no extra flag. An incremental model is different — see insert_overwrite.

Ephemeral models

An ephemeral model is never persisted. dbt inlines it into each downstream model as a CTE, which keeps a shared filter or cast in one file instead of repeating it in every model that needs it. Inlining is not the same as computing the result once. Depending on the plan the engine picks, a CTE referenced more than once can be evaluated once per reference, so an ephemeral model does not guarantee an expensive source scan runs only once. Use a table or incremental model when downstream models need to share a materialized result.

models/staging/stg_taxi_trips.sql

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

select
    cast(pickup_datetime as timestamp) as pickup_ts,
    ST_SetSRID(pickup_location, 4326) as pickup_geom,
    fare_amount
from {{ source('nyc_taxi', 'yellow_2009_2010') }}
where cast(pickup_datetime as timestamp) < timestamp '{{ var("batch_end") }}'
  -- Keep pickups inside the study area. This drops GPS noise and trips outside New York City.
  and ST_Within(
      ST_SetSRID(pickup_location, 4326),
      ST_MakeEnvelope(-74.05, 40.68, -73.90, 40.85, 4326)
  )

batch_end is a project variable that bounds every read of the taxi source, which holds about 186 million trips. Give it a default in dbt_project.yml:

dbt_project.yml

vars:
  batch_end: '2009-01-01 02:00:00'

To load the next slice, raise it for a single run, for example dbt run --vars '{batch_end: "2009-01-01 04:00:00"}'.

Incremental models

Incremental models stage each run’s new rows, then apply one of four strategies to the target table. The staged rows live in a temporary view whose name is unique to the target, so concurrent runs that build a same-named model into other schemas do not read each other’s rows. delete+insert with a unique_key stages into a table beside the target instead, and drops it after the run.

Strategy	What it does	Requires
`append` (default)	Inserts the staged rows. Never updates or deletes.	—
`merge`	Upserts staged rows on `unique_key`.	`unique_key` for updates
`delete+insert`	Deletes target rows whose key appears in the staged data, then inserts.	`unique_key` for deletes
`insert_overwrite`	Replaces every partition present in the staged data.	`partition_by`

microbatch is not supported. Selecting it fails with dbt’s own “not valid for this adapter” error before the model’s materialization SQL runs. The model’s pre-hooks still run first, so a pre-hook that writes data takes effect even though the model fails.

append

The default strategy. With no unique_key and no strategy set, dbt appends the staged rows.

models/marts/fct_trip_events.sql

{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}

select pickup_ts, pickup_geom, fare_amount
from {{ ref('stg_taxi_trips') }}

{% if is_incremental() %}
where pickup_ts > (select coalesce(max(pickup_ts), timestamp '1900-01-01') from {{ this }})
{% endif %}

append inserts whatever the model selects, so the model has to avoid re-inserting rows it already loaded. The watermark filter above does that. The coalesce covers an empty table: if a run loads no rows, for example because batch_end is earlier than the first trip, max(pickup_ts) is NULL. A comparison with NULL matches no rows, so without the fallback no later run would load anything. The merge and insert_overwrite examples below use the same fallback.

The watermark filter is correct only when the source never receives a row older than the latest one already loaded. That holds here, because each run reads every trip before batch_end from a historical source. For a live source, a row that arrives late with a pickup_ts at or below the table’s maximum is skipped, including a row that shares the maximum timestamp, and no later run picks it up. If your source can receive late rows, re-read a lookback window and use merge with a unique_key so the overlap is upserted, not inserted twice.

merge

Upserts on unique_key, which accepts a single column name or a list for a composite key.

models/marts/fct_trips_by_zone_daily.sql

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=['zip', 'trip_date']
) }}

with zoned as (

    select zip, trip_date, fare_amount
    from {{ ref('int_trips_zoned') }}

    {% if is_incremental() %}
    where trip_date >= (select coalesce(max(trip_date), date '1900-01-01') from {{ this }})
    {% endif %}

)

select zip, trip_date, count(*) as trip_count, avg(fare_amount) as avg_fare
from zoned
group by zip, trip_date

The is_incremental() filter uses >=, so each run recomputes the latest day already in the table, plus any newer days. merge updates the existing rows for that day in place and inserts the rest. Without the filter the model still gives the right answer, but re-aggregates every day on every run. merge also honors merge_update_columns and merge_exclude_columns to narrow which columns an update touches, and incremental_predicates to add conditions to the match.

Key matching uses plain equality, so a NULL in a unique_key column never matches an existing row and is inserted again as a duplicate. dbt’s unique_key contract assumes non-null keys. Add a not_null test on every key column.

A unique_key value that appears more than once in the staged data does not always fail the run:

If the key already exists in the table, the run fails with MERGE_CARDINALITY_VIOLATION and leaves the table unchanged.
If the key is new, merge inserts every copy with no error, and the table now holds duplicates.
A first build stores whatever the model selects, duplicates included.

Deduplicate the model’s select on the key, as the group by in the example above does. Add a unique test on the key as well: the run catches only the first case, and the test catches the other two.

delete+insert

Deletes the target rows whose unique_key appears in the staged data, then inserts every staged row. For most models it reaches the same end state as merge, including models whose is_incremental() filter reads {{ this }}, and swapping between the two is a one-line change.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key=['zip', 'trip_date']
) }}

With no unique_key, delete+insert degrades to a pure append — there is nothing to delete.

insert_overwrite

Replaces whole partitions. Every partition present in the staged data is rewritten in full, and partitions the staged data does not touch are left alone. Use it when a run recomputes a whole day or region rather than individual rows.

models/marts/fct_trips_hourly_incremental.sql

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by=['trip_date']
) }}

with zoned as (

    select trip_date, trip_hour, zip
    from {{ ref('int_trips_zoned') }}

    {% if is_incremental() %}
    where trip_date >= (select coalesce(max(trip_date), date '1900-01-01') from {{ this }})
    {% endif %}

)

select trip_date, trip_hour, zip, count(*) as trip_count
from zoned
group by trip_date, trip_hour, zip

Three rules apply:

partition_by is required. Without it, the run fails at compile time, because an unpartitioned overwrite would replace the whole table. Use materialized='table' if that is what you want.
Every partition_by column must be selected by the model. On an incremental run, a partition column missing from the model’s output fails the run at compile time instead of filing every staged row into a NULL partition. On the first build, the engine rejects the create table with Couldn't find column <name>.
Adopting the strategy on an existing table requires --full-refresh, as does changing partition_by. The overwrite scopes by the table’s actual partitioning, not by your config, and the adapter does not detect a mismatch between the two. On an existing unpartitioned table, the first insert_overwrite run without --full-refresh replaces the whole table with the staged rows. A first build needs no --full-refresh, because it creates the partitioned table directly.

The dynamic partition overwrite is requested on the statement itself, so the strategy leaves the SQL session’s spark.sql.sources.partitionOverwriteMode unchanged for other statements on the runtime. unique_key and incremental_predicates are accepted but ignored. Partition replacement does not key on rows or scope by a predicate.

Handling schema changes

on_schema_change controls what happens when the model’s columns drift from the table’s.

Value	Behavior
`ignore` (default)	New model columns are not added. A column removed from the model fails the run.
`fail`	The run fails when the column sets differ.
`append_new_columns`	New model columns are added to the table in one `alter table ... add columns` statement.
`sync_all_columns`	Not supported. Fails at compile time.

sync_all_columns would need alter column ... type, which the Iceberg catalog rejects. Use append_new_columns, and promote a type yourself when you need it widened. When append_new_columns adds a geometry column to an Iceberg V3 table, the adapter creates it as geometry(<srid>). The SRID comes from a data_type: geometry(<srid>) declared for the column in the model’s YAML, or else from the model’s values, which must all share one SRID; reproject mixed values with ST_Transform in the model. A V3 column’s coordinate reference system is fixed once the column exists, so the adapter does not guess one: a new geometry column with no non-null values yet needs the declaration, and without it the run fails before the table is altered. See Declaring geometry column types. A table that is not Iceberg V3 (format-version below 3) cannot take a new geometry column in place. The run fails before altering the table and names the fix: rebuild the model with --full-refresh, or migrate the table to Iceberg V3 in place and run again:

alter table <catalog>.<schema>.<model> set tblproperties ('format-version'='3')

The upgrade keeps the table’s rows and its existing geometry columns. Adding non-geometry columns works on any table. A model that drops a column still present on the table is a column-set difference. Under fail, the run fails with dbt’s schema-change error. Under append_new_columns, every strategy writes NULL into the dropped column on the new rows; the engine does not fill missing columns on insert, so the adapter supplies the NULL. Under the default ignore, the run fails with an UNRESOLVED_COLUMN error, because the insert still lists every column the table has. Switch the model to append_new_columns, or drop the column from the table yourself.

Snapshots

dbt snapshot builds SCD2 history tables on Havasu. It uses dbt’s stock snapshot materialization with Wherobots dialect hooks.

snapshots/snap_trips_by_zone_daily.sql

{% snapshot snap_trips_by_zone_daily %}
{{ config(
    unique_key=['zip', 'trip_date'],
    strategy='check',
    check_cols='all'
) }}

select zip, trip_date, trip_count, avg_fare
from {{ ref('fct_trips_by_zone_daily') }}

{% endsnapshot %}

Each time a day’s trip_count or avg_fare changes, the snapshot closes the old version and records a new one. Supported configuration:

Strategies: timestamp (with updated_at) and check (with check_cols as a column list or 'all').
unique_key: a single column or a list for a composite key.
hard_deletes: all three modes. ignore (the default) leaves a vanished row’s history untouched, invalidate closes dbt_valid_to on its current version, and new_record also inserts a tombstone version with dbt_is_deleted = 'True'.
dbt_valid_to_current: set an explicit sentinel, such as "cast('9999-12-31' as timestamp)", instead of leaving dbt_valid_to NULL on open versions.
Schema evolution: a new source column is added to the snapshot table automatically on the next run, except a geometry or geography column (see below).

A new geometry or geography source column needs one manual step. Snapshot schema evolution adds the column with a bare geometry or geography type, which current runtimes reject because it declares no coordinate reference system, so the snapshot run fails. Add the column yourself with its CRS, then run dbt snapshot again. The existing history is kept.

alter table <catalog>.<schema>.<snapshot> add columns (geom geometry(4326))
alter table <catalog>.<schema>.<snapshot> add columns (geog geography(4326))

If the snapshot table is still at format-version 2 (check with show tblproperties <table> ('format-version')), first upgrade it in place with alter table <table> set tblproperties ('format-version'='3').

There is no dbt snapshot --full-refresh. dbt has no such flag for snapshots, and the materialization has no full-refresh branch. To rebuild a snapshot, drop the table yourself with drop table <catalog>.<schema>.<snapshot>, and the next run recreates it from the current source state. You cannot rebuild the history from the source, so drop the table only when you mean to.

Source column type changes are never applied to an existing snapshot column. If a source column widens from int to bigint, values that still fit the old type store correctly and the run proceeds. A value that overflows fails with the engine’s CAST_OVERFLOW_IN_TABLE_INSERT error (SQLSTATE 22003) instead of being stored wrong. Promote the column yourself:

alter table <catalog>.<schema>.<snapshot> alter column <column> type bigint

Then re-run the snapshot.

Seeds

dbt seed loads comma-separated values (CSV) files as tables. Values are written inline as SQL literals rather than bound parameters, so the loader handles quoting, typed date and timestamp literals, and geometry constructors itself.

dbt_project.yml

seeds:
  my_project:
    landmark_points:
      +column_types:
        geom: geometry
      +column_srids:
        geom: 4326

A non-string +column_types override, such as date or timestamp, fails at analysis time, because the engine rejects an implicit string-to-date assignment. Declare the column as string in the seed and cast it in a downstream model. geometry is the exception: it goes through the geometry type handler and works as shown above.

For geometry seeds and the SRID rules that apply to them, see Spatial data in dbt models.

Generating documentation

dbt docs generate works against Wherobots. For a selection of up to 100 relations, the adapter describes only those relations. Above 100, dbt falls back to describing every relation in the schemas involved, including tables that dbt does not manage. Column types in the catalog keep their Wherobots spellings, including geometry and raster.

dbt docs generate
dbt docs serve

Next steps

Spatial data in dbt models

Seed geometry from four formats, manage SRIDs, and test spatial invariants.

Adapter overview

Install, configure a profile, and verify the connection.

Havasu tables

How Wherobots stores tables, and the table properties you can set.

​Views and tables

​Partitioning a table

​Ephemeral models

​Incremental models

​append

​merge

​delete+insert

​insert_overwrite

​Handling schema changes

​Snapshots

​Seeds

​Generating documentation

​Next steps

Spatial data in dbt models

Adapter overview

Havasu tables

Views and tables

Partitioning a table

Ephemeral models

Incremental models

append

merge

delete+insert

insert_overwrite

Handling schema changes

Snapshots

Seeds

Generating documentation

Next steps