OverviewQuickstartdbt CompatibilityThe Style Guideprofiles.ymlsources.ymlConfigurationSupported Databases

PREDICATE PUSHDOWN

The fastest row to move is the one you never extract. Before any data leaves a source engine, DVT rewrites each extraction query to do as much work as possible on that engine— fewer rows over the wire, less to ingest, faster runs. Here's precisely what gets pushed and how.

WHAT YOU WRITE VS. WHAT EACH ENGINE RECEIVES

One model, two engines. You write DuckDB-dialect SQL:

{{ config(materialized='f_table') }}

select
    o.order_id,
    o.amount,
    c.region
from {{ source('mysql_crm', 'orders') }} o          -- MySQL, 40M rows
join {{ source('sf_finance', 'customers') }} c       -- Snowflake, 2M rows
  on o.customer_id = c.customer_id
where o.order_date >= '2025-01-01'                   -- ~3% of orders
  and c.region in ('EMEA', 'APAC')

MySQL receives MySQL syntax, with its filter and only its columns:

SELECT amount, customer_id, order_date, order_id
FROM crm.orders
WHERE order_date >= '2025-01-01'

Snowflake receives Snowflake syntax, same treatment:

SELECT customer_id, region
FROM finance.customers
WHERE region IN ('EMEA', 'APAC')

Notice the quoting: identifiers you didn't quote in the model stay unquoted, so each engine resolves them with its own case rules — lowercase resolves everywhere, including Snowflake's uppercase world. Quotes appear only where you quoted a name in the model, or where a name needs them (spaces, reserved words) — then in the engine's own style: backticks on MySQL, double quotes on Snowflake.

Instead of 42M rows × all columns, the wire carries ~1.2M orders × 4 columns and a filtered slice of customers × 2 columns. The join runs in DuckDB on what's left.

THE THREE PUSHDOWN MECHANISMS

COLUMN PRUNING

The decomposer walks the parsed query and collects exactly which columns each source table contributes — SELECT list, join keys, filters, group-bys. Everything else never leaves the source. Wide tables (100+ columns, 6 used) are where this pays brutally well.

FILTER PUSHDOWN

WHERE clauses are split into AND-conjuncts. Every subquery-free, window-free conjunct that references only one source's columns — and that the source's dialect can express — travels to that source, in that source's dialect. Conjuncts spanning sources (like join conditions) stay in DuckDB where they belong, and anything skipped still filters in DuckDB after extraction — correctness never depends on pushdown.

WHOLE-QUERY PUSHDOWN — TWO FLAVORS

On the default target, standard dbt materializations are whole-query pushdown: the adapter runs the entire query natively. And since 0.2.5, homogeneous f_table models get it too — when every source lives on ONE connection and the target is a SQL engine, the whole query (joins and aggregations included) is transpiled to that engine's dialect and streamed by Sling directly to the target. No Parquet staging, no DuckDB. Bucket targets and file-based sources always use the standard pipeline. The model stays federated by contract: add a source on another engine tomorrow and it silently switches to the DuckDB path.

DIALECT TRANSPILATION — 19 ENGINE TYPES, 14 SQL DIALECTS, ONE MODEL

Pushdown only works if the source understands the query. DVT uses SQLGlot to transpile each extraction query into the source engine's dialect — quoting (backticks vs. double quotes), date literals, function names, type casts. You write DuckDB; Oracle gets Oracle, T-SQL gets T-SQL, BigQuery gets Standard SQL.

The same machinery powers the lakehouse switch — federation models never depend on any one engine's syntax.

WHAT DOES NOT GET PUSHED (ON PURPOSE)

  • ▸Cross-source predicates — a condition comparing MySQL and Snowflake columns can only be evaluated after both sides are local.
  • ▸Aggregations over joined data — pushing a partial GROUP BY below a join changes results; DVT keeps correctness over cleverness.
  • ▸Subqueries and window functions — a conjunct containing either never pushes; it evaluates in DuckDB after extraction.
  • ▸Anything the source dialect can't express — if SQLGlot can't render the conjunct in that engine's dialect, it's skipped, not sent as-is.
  • ▸Filters on a relation the model reads more than once — the two reads share one extraction, so pushing one read's filter would corrupt the other; both filter in DuckDB.

Transparency rule: pushdown must never change query semantics. If a predicate can't provably move, it stays in DuckDB and the run is still correct — just with more rows on the wire.

SEE IT YOURSELF

Run any federation model with verbose logging and read the per-source extraction queries DVT generated:

dvt run --select my_federation_model --log-level debug

Every extraction query is logged before it runs. What we claim is what you can read.

← The full federation pipelineThe execution paths →