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.
• ▸chunk_column + chunk_size split a FIRST load (f_table, or an f_incremental's first run or --full-refresh) into value ranges of the column, extracted and landed one at a time — for tables a single SELECT cannot move.

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.

CHUNKED FIRST LOADS — CHUNK_COLUMN AND CHUNK_SIZE

A big table cannot always be moved with one SELECT *. Give the model a column to range over and a row-count target per chunk, and DVT probes the column's count, minimum and maximum over exactly the rows it would pull, splits the span into equal-width value ranges, and moves them one after another:

{{ config(materialized='f_table', chunk_column='id', chunk_size=500000) }}
select * from {{ source('eska__igeneral', 'GRN_PLAN_DETAILS') }}

On the compute lane each chunk is its own parquet part with the same pinned types, ingested into the cache in order — so peak memory is one chunk, not the table. On the direct lane the first chunk creates the landing and the rest append through the engine, in ascending, non-overlapping ranges. It applies to first loads only: an f_table, or an f_incremental on its first run or with --full-refresh; later deltas are bounded by the watermark already. The column must be numeric or a date/time. Ranges are equal in value, not in rows — a skewed column gives uneven chunks, so treat chunk_size as a target. The run prints the plan it chose — grn_plan_details: 1500000 rows → 3 chunks (500,000 rows each, by value range) on id — and --debugstates each chunk's rows.

Chunking bounds extraction and ingest. The compute itself — the whole model query in DuckDB — is bounded by federation_memory_limitin dvt_project.yml, which caps DuckDB's memory and lets a big join spill to the project's own .dvt/tmp instead of choking the machine.

F_SNAPSHOT — THE FEDERATED TYPE-2 SNAPSHOT

A third federated materialization, since 0.2.59. Where a dbt snapshot is a {% snapshot %} file that runs on the default target, an f_snapshot is a model: its SELECT is DuckDB SQL over sources on any engines, and DVT keeps the type-2 history of what it returns.

-- models/history/customers_scd.sql
{{ config(
    materialized='f_snapshot',
    unique_key='customer_id',
    strategy='timestamp',        -- or strategy='check', check_cols=['status', 'tier'] / 'all'
    updated_at='updated_at',
    invalidate_hard_deletes=false
) }}

select customer_id, name, status, tier, updated_at
from {{ source('crm_mysql', 'customers') }}

Each run computes the current state, reads the standing snapshot's open versions back from the target, diffs by unique_key under the strategy, and lands one result: new and changed versions opened, the versions they replace closed at the new version's valid-from — applied as a merge on dbt_scd_id. The first run creates the table; every later run is an incremental load. Nothing changed means nothing applied.

The meta columns carry dbt's names — dbt_scd_id, dbt_updated_at, dbt_valid_from, dbt_valid_to — so downstream models read where dbt_valid_to is null for the current state exactly as they would from a native snapshot. Their types are declared and pinned on every engine (a varchar and three timestamps), never inferred: a dbt_valid_to that is all NULL on the first run is still a timestamp when the first close arrives.

It runs under dvt run and dvt build like any model (dvt snapshotremains dbt's own {% snapshot %} files). Engines whose loader cannot merge are refused before anything is read. An f_snapshot has no native pair, so it stays federated through every default-target switch; --full-refresh rebuilds the history from the current state.

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