F-TABLE & F-INCREMENTAL

FEDERATED MEANS FEDERATED — ALWAYS

The materialization is a contract, not a hint. An f_tablemodel runs through DVT's federation pipeline every time — even if all its sources happen to sit on one engine today, and no matter what your default target is.

That sounds pedantic. It's actually the point: because the model never leans on any particular engine, it keeps working unchanged when you add a second engine tomorrow — or when you switch the default target entirely. f_table means: this model is engine-portable.

THE CONTRACT IS FIXED. THE ROUTE IS OPTIMIZED.

"Federated always" doesn't mean "staged always." DVT picks the cheapest execution that honors the contract, automatically, on every run:

You never choose between these. Add a second engine to a homogeneous model and it silently takes the DuckDB path on the next run — the model itself never changes.

One subtlety worth knowing: "same connection" means the same profiles.yml output, not merely the same engine type. Two different Oracle servers are heterogeneous.

F_TABLE — THE FULL REBUILD

You've already met f_table— it's the model at the top of this page. It's the cross-engine equivalent of materialized='table': every run is a full rebuild.

Extract (with pushdown) → compute in DuckDB → load to the target as a table. Simple and predictable.

F_INCREMENTAL — ONLY MOVE WHAT CHANGED

A full rebuild is honest but expensive. When your source table grows by thousands of rows a day, not millions, f_incremental moves only the delta. The simplest flavor needs no Jinja at all — just name the column that marks new rows:

-- models/marts/events_rollup.sql
{{ config(
    materialized='f_incremental',
    unique_key='event_id',
    incremental_strategy='merge',     -- or append / delete+insert
    watermark_column='updated_at'
) }}

select event_id, user_id, payload, updated_at
from {{ source('pg_events', 'raw_events') }}

When you want full control, that works too. Since 0.2.7, is_incremental() and {{ this }}work exactly like dbt's — your SQL owns the delta:

{{ config(materialized='f_incremental', unique_key='id') }}

{% if is_incremental() %}
SELECT id, payload, updated_at
FROM {{ source('pg_events', 'raw_events') }}
WHERE id NOT IN (SELECT id FROM {{ this }})        -- anything goes:
  AND updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% else %}
SELECT id, payload, updated_at
FROM {{ source('pg_events', 'raw_events') }}
{% endif %}

How DVT keeps this fast — and honest:

• ▸No source replicas. DVT keeps a pruned INDEX of {{ this }} — a NOT IN subquery stores one column, not the whole table — persisted and self-maintained after every load.
• ▸Scalar watermarks (MAX(...) FROM {{ this }}) are inlined as literals into each source's extraction query, so only true deltas ever leave the source. Set logic anti-joins locally; lists never serialize into SQL.
• ▸Set a unique_key — it's strongly recommended. Deltas then apply as a Sling upsert: merge, append, or delete+insert by name (insert_overwrite and microbatch are refused). Without one, Jinja models are accepted only for the scalar MAX-watermark pattern (a provably-safe keyless append), and watermark_column models fall back to a full rebuild with a warning.
• ▸watermark_column stays available as no-Jinja sugar, with its own proven path.
• ▸--full-refresh resets the index and rebuilds, selectively with --select. dvt clean never touches it — clean is assets, --full-refresh is data.

The Sling direct path applies here too: single-connection models go direct whenever Sling can compute the delta target-side — Jinja {{ this }} models, and watermark_column models with a target watermark. Otherwise Jinja models use the index-backed DuckDB path and watermark_column models the replica-cache path.

WHERE RESULTS LAND

By default: on your default target. No config needed. A federated model with no target= materializes its result on the default target — or whatever --target you passed on the CLI.

This model from our live verification project joins ClickHouse with SQLite and lands the result on pg_dev, the project's default target — no destination config anywhere:

-- models/new_adapters/cross_clickhouse_sqlite.sql  (trial 21, runs green)
{{ config(materialized='f_table') }}

-- Cross-engine join — ClickHouse + SQLite → default target (pg_dev)
SELECT
    ch.id,
    ch.name as ch_name,
    sq.name as product_name,
    sq.price
FROM {{ source('clickhouse_source', 'test_data') }} ch
JOIN {{ source('sqlite_source', 'products') }} sq
    ON ch.id = sq.id

Resolution order, exactly as the engine applies it: model config(target=...) → CLI --target → profile default. So target= is purely an override, for when the result must land somewhere else:

{{ config(materialized='f_table', target='s3_lake', format='parquet') }}
-- result lands in S3 as Parquet objects

{{ config(materialized='f_incremental', target='snowflake_prod',
          unique_key='id', watermark_column='updated_at') }}
-- delta-merged into Snowflake, regardless of your default target

Model 'my_view' is materialized as 'view' but reads federated
sources — running it as 'f_table'. Set materialized='f_table'
explicitly to silence this.

THREE NAMES, ONE MEANING

All of these are accepted and mean the same thing (hyphens and underscores both work):

And your project stays a valid dbt project: dvt sync writes compatibility macros so that plain dbt run(an IDE extension, a CI job calling dbt directly) fails on a federated model with a clear "run this with dvt run" message instead of a cryptic "materialization not found".

WHEN TO USE WHICH