> ## Documentation Index
> Fetch the complete documentation index at: https://docs.wherobots.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Materializations and Incremental Strategies
> How the Wherobots dbt adapter builds views, tables, ephemeral models, incremental models, snapshots, and seeds, and which configurations each one accepts.
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](#incremental-models). |
| `snapshot` | A slowly changing dimension type 2 (SCD2) history table | See [Snapshots](#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.
```sql title="models/staging/stg_zipcode_zones.sql" theme={"system"}
{{ 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.
```sql title="models/marts/fct_trips_hourly.sql" theme={"system"}
{{ 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](#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.
```sql title="models/staging/stg_taxi_trips.sql" theme={"system"}
{{ 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`:
```yaml title="dbt_project.yml" theme={"system"}
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.
```sql title="models/marts/fct_trip_events.sql" theme={"system"}
{{ 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.
```sql title="models/marts/fct_trips_by_zone_daily.sql" theme={"system"}
{{ 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.
```sql theme={"system"}
{{ 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.
```sql title="models/marts/fct_trips_hourly_incremental.sql" theme={"system"}
{{ 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 `.
* **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()`. The SRID comes from a `data_type: geometry()` 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](/develop/dbt/spatial-data#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:
```sql theme={"system"}
alter table .. 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.
```sql title="snapshots/snap_trips_by_zone_daily.sql" theme={"system"}
{% 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.
```sql theme={"system"}
alter table .. add columns (geom geometry(4326))
alter table .. add columns (geog geography(4326))
```
If the snapshot table is still at format-version 2 (check with `show tblproperties ('format-version')`), first upgrade it in place with `alter 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 ..`, 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:
```sql theme={"system"}
alter table .. alter 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.
```yaml title="dbt_project.yml" theme={"system"}
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](/develop/dbt/spatial-data#seed-geometry-columns).
## 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`.
```bash theme={"system"}
dbt docs generate
dbt docs serve
```
## Next steps
Seed geometry from four formats, manage SRIDs, and test spatial invariants.
Install, configure a profile, and verify the connection.
How Wherobots stores tables, and the table properties you can set.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.