F-TABLE & F-INCREMENTAL

THE SEMANTICS: FEDERATED MEANS FEDERATED — ALWAYS

An f_tablemodel runs through DVT's federation pipeline every time, regardless of where its sources live or what your default target is. Even if all its sources sit on one engine today, it stays federated — which means it keeps working unchanged when you add a second engine tomorrow, or when you switch the default target entirely. The materialization is a contract: this model is engine-portable.

AUTOMATIC OPTIMIZATION: THE SLING DIRECT PATH

"Federated always" doesn't mean "staged always." DVT picks the cheapest execution that honors the contract:

The switch is automatic and per-run: add a second engine to a homogeneous model and it silently takes the DuckDB path — the model never changes. Same connection means the same output, not merely the same engine type; two different MySQL servers are heterogeneous. f_incremental joins both paths 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.

F_TABLE

-- models/marts/cross_engine_sales.sql
{{ config(materialized='f_table') }}

select
    o.order_id,
    o.amount,
    c.region
from {{ source('mysql_crm', 'orders') }} o      -- MySQL
join {{ source('sf_finance', 'customers') }} c   -- Snowflake
  on o.customer_id = c.customer_id

Full rebuild on every run: extract (with pushdown), compute in DuckDB, load to the target as a table. The cross-engine equivalent of materialized='table'.

F_INCREMENTAL

-- 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') }}

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 %}

• ▸No replicas: DVT keeps a pruned INDEX of {{ this }} (a NOT IN subquery stores one column, not the table), persisted and self-maintained after every load.
• ▸Scalar watermarks (MAX(...) FROM {{ this }}) inline as literals into each source's extraction — only true deltas leave the source. Set logic anti-joins locally; lists never serialize into SQL.
• ▸unique_key is strongly recommended: deltas apply as a Sling upsert (merge / append / 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); watermark_column models fall back to a full rebuild with a warning.
• ▸watermark_column config remains as no-Jinja sugar with its proven path.
• ▸--full-refresh resets the index and rebuilds, selectively with --select. dvt clean never touches it — clean is assets, --full-refresh is data.

WHERE RESULTS LAND: DEFAULT TARGET, OR TARGET= OVERRIDE

No config needed to write to the default target — that's the baseline. 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. 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.

NAMING & DBT COMPATIBILITY

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

dvt sync writes compatibility macros into your project 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". Your project stays a valid dbt project.

WHEN TO USE WHICH