THE FEDERATION ENGINE
You've written an f_tablemodel that joins tables living on different engines, and you'd like to know what actually happens when you run it. Fair question — here's the answer: six steps, no magic, each one inspectable.
DVT is a wrapper around stock dbt-core: dbt compiles your project and runs everything it can; DVT picks up the federation models dbt can't express and runs them through this pipeline.
We'll follow one model through the pipeline — two engines, one join:
{{ config(materialized='f_table') }}
select o.order_id, o.amount, c.region
from {{ source('oracle_crm', 'orders') }} o -- Oracle
join {{ source('sf_finance', 'customers') }} c -- Snowflake
on o.customer_id = c.customer_idCOMPILE — DBT DOES WHAT DBT DOES
Your project compiles with dbt itself: Jinja renders, ref() and source() resolve, the manifest is built.
DVT reads the compiled SQL from the manifest. It never reimplements dbt's compilation — which is exactly why every dbt feature keeps working.
DECOMPOSE — FIND WHAT LIVES WHERE
The compiled query is parsed with SQLGlot. Every table reference is matched to a connection from your profiles.yml (via each source's config.meta.connection in sources.yml).
Now DVT knows: orders is on Oracle, customers is on Snowflake, and that ref() over there is a table my last run materialized on PostgreSQL.
TRANSPILE & PUSH DOWN — PER SOURCE, IN ITS OWN DIALECT
For each source engine, DVT builds the smallest possible extraction query in that engine's SQL dialect: only the columns the model actually uses, with every filter that can legally move to the source pushed into it. The Snowflake source gets Snowflake SQL, the Oracle source gets PL/SQL-flavored syntax — all generated from your one DuckDB-dialect model.
This is the single biggest performance lever — see the predicate pushdown deep dive.
EXTRACT — SLING MOVES DATA AS PARQUET
Each extraction query runs through Sling, landing results as Parquet files — columnar, compressed, fast. Extractions for different sources run in parallel.
For f_incremental models, a watermark column limits extraction to new rows only, and previous extracts persist in the cache between runs.
COMPUTE — DUCKDB JOINS IT ALL LOCALLY
The Parquet files are ingested into a local DuckDB database (.dvt/cache.duckdb), and your model's SQL — the actual JOIN / GROUP BY logic — executes there, on one of the fastest analytical engines that exists, using your machine's cores and memory.
Two details worth knowing: files are ingested one at a time through the single cache connection — deliberately staying out of DuckDB's single-writer lock, while each individual ingest is still multi-threaded by DuckDB — and every table is namespaced per model, so parallel models never collide.
LOAD — RESULTS LAND ON THE TARGET
The computed result goes back out through Sling to wherever the model materializes: the default target, a per-model config(target=...) override, or a cloud bucket as Parquet/CSV. Incremental strategies (append, merge, delete+insert) apply on the target.
The output prints dbt-style — START / OK lines, rows, timings — because your tooling already understands that format.
THE SHORTCUT: THE DIRECT PATH (HOMOGENEOUS MODELS)
When every source in an f_table model lives on oneconnection and the target is a SQL engine, steps 4–6 collapse into a single hop. The whole query — joins included — is transpiled to that engine's dialect and handed to Sling as a custom-SQL stream, loaded straight to the target. No Parquet. No DuckDB.
The check is automatic, and any failure falls back to the standard pipeline. Bucket targets and file-based sources always take the standard pipeline; heterogeneous models do too. The same model switches between paths purely based on where its sources live.
NAMES ACROSS ENGINES — THREE LAWS, ONE PLACE
Every engine treats identifier case differently — Snowflake and Oracle upfold unquoted names, Postgres downfolds, Trino stores lowercase, ClickHouse stores exactly what it is given — and five different actors (dbt, the SQL parser, the SQL renderer, the bulk loader, the engine) each have an opinion. DVT settles it with three laws, decided in one place and measured against every live engine, not reasoned from a table:
What DVT lands follows the engine's convention. A model's relation is spelled the way an unquoted creation would be stored on that engine — MIXEDCASE on Snowflake and Oracle, mixedcaseon Trino, quoted as written on the Postgres family — and every lane that later refers to it (hooks' {{ this }}, extraction, retract, the catalog) derives the spelling from that same rule.
What you created yourself is read exactly as spelled. A table you quoted with lowercase or mixed case is, to DVT, a foreign object — it is referenced verbatim and quoted, never folded.
A rendered name is always safe. Reserved words and names with special characters are quoted on every engine, in every lane — a model called select just works. One exception is refused rather than handled: a relation name containing a space or special character crashes the bulk loader outright, so DVT stops it before loading (DVT066) and tells you to rename the model or set alias=.
TYPES ACROSS ENGINES — ONE HOME, PINNED EVERYWHERE
A column crosses type systems twice on the federation path — source to parquet to DuckDB, then DuckDB to parquet to the target — and three times for a Python model. Every door used to re-infer the type from whatever sat in front of it, and the drift was measurable: a source numeric(18,4) arriving as DECIMAL(24,6), a timestamp with time zone landing as a plain datetime, a boolean landing on Oracle as the strings 'true'/'false', an incremental watermark rendered to the second and re-reading the truncated second every run. Since 0.2.59 the rules live in one type home and the store carries the facts.
Declared types travel.Arrow is the carrying vocabulary — the one parquet, DuckDB, pandas, polars and pyarrow already share — and each engine has one map in and one map out. On extraction the source's declared decimal precision and string widths are pinned on the parquet DVT reads (the metadata store knows them); on load every column DVT can type verifiably is pinned on everyengine — the strict rule that only Trino had is now the rule — and what cannot be pinned is named in the log, never left to inference in silence.
What lands is the trusted type.Downstream of the default target nothing re-decides a type: native models read what landed, federated models extract it back with its declared shape, watermark and hook literals are rendered from the column's type with its precision, and a same-engine round trip reproduces the engine's own type rather than a translation of it. Where an engine genuinely has no counterpart the crossing is documented, not fought: Oracle's NUMBER is its one numeric type and its DATEcarries a time; ClickHouse'sDateTime64 is one temporal type; on the MySQL family and SQL Server a tz-aware instant lands as its UTC wall clock in DATETIME(6) /datetime2(6)— DVT writes the UTC instant itself, because the loader's own tz-aware write was measured to shift it by the machine's offset on SQL Server, MariaDB and MySQL 5.7 (Postgres, Oracle, ClickHouse, Trino and DuckDB hold the instant exactly). Oracle booleans land NUMBER0/1 — the engine's own idiom. On Postgres the loader creates every decimal as bare numeric (values exact, the declared precision dropped), so DVT restores the declared numeric(p,s) right after the load. On Snowflake the loader lands every integer as NUMBER(38,0) (values exact, the declared width not kept) and a uuid as VARCHAR(36), the declared DDL itself — both documented crossings, measured on a client estate. Every one of these is measured live — the type each engine reports and the value each engine renders back — before a release.
And DVT checks its own work.After every load the metadata store records what DVT declared for each landed column and reads the engine's catalog back to verify it — dvt metadata statusprints DVT's own world and names any column that landed with another type. Every pin was admitted by a live measurement: a type gauntlet runs a corpus of types across every engine and three lanes (load, round trip, literal) and stands as the regression net.
WHY THIS DESIGN WINS
- ▸Source engines only ever run simple filtered SELECTs — your OLTP databases are barely touched.
- ▸The heavy lifting happens in DuckDB on your hardware — no warehouse compute billed for the join.
- ▸Parallel where it pays: extractions and model waves run concurrently; ingest is serialized through DuckDB's single writer on purpose.
- ▸Models stay in one dialect (DuckDB) no matter how many engines they read from.