datapipelines.co
Menu

Per-run scratch database · nothing landed · no warehouse

Data virtualization, minus the platform

Federated query across databases, without a warehouse

Join tables from different databases — Postgres to MySQL to SQLite — in one pipeline. Each source is queried where it lives, the join happens in a staging database created for that one execution, and the staging database is destroyed when the execution ends. Nothing is landed, nothing is synced, and there is no warehouse to keep.

See it on real data Read the staging spec

The mechanism

Cross-database joins in an in-memory staging area

Staging is an in-memory H2 database that holds the intermediate result sets of one execution. It is created when the execution starts and destroyed when it ends. No state survives between executions — that is the design, not a limitation being worked around.

Each execution gets its own isolated instance at jdbc:h2:mem:exec_{execution_id}, with no DB_CLOSE_DELAY: the database exists exactly while a connection to it is open, so a leaked staging database is not a shape this can take. The connection the author's SQL runs on is a de-privileged user, not H2's sa.

  1. Read each source in place

    One node per source, each a plain DQL query against its own datasource, in that engine's own dialect. The source database does the filtering and the aggregation it is good at.

  2. Stage the result sets

    Each node's output becomes a table in the execution's staging database, under a stable name the downstream SQL can reference.

  3. Join in staging

    A node whose source is tempdb sees all of them as ordinary tables and joins them with ordinary SQL. Neither original engine ever learns about the other.

  4. Return rows, drop the staging database

    The caller node's result is paged back through the result cursor; the staging database goes away with the execution.

Worked example

Join Postgres and SQLite in one query: nyc/mobility/revenue_by_borough

Seeded by the NYC demo. The trip rollup lives in a Postgres datasource; the 265-row taxi-zone lookup that turns a pickup zone into a borough lives in a SQLite one. Neither engine can see the other's table, so both are staged first — and then it is just a join.

Node 1 — the trips, from Postgres

A DQL node against the sample-trips datasource, staged as stg_trips_monthly.

Node 2 — the zones, from SQLite

A DQL node against the sample-reference datasource, staged as stg_zones.

Node 3 — the join, in staging

Source tempdb. This is the pipeline's SQL, verbatim:

SELECT
    z.borough                                                AS borough,
    SUM(t.trip_count)                                        AS trip_count,
    ROUND(SUM(t.total_revenue), 2)                           AS total_revenue,
    ROUND(SUM(t.total_tips), 2)                              AS total_tips,
    ROUND(SUM(t.total_revenue) / SUM(t.trip_count), 2)       AS revenue_per_trip,
    ROUND(100.0 * SUM(t.total_tips) / SUM(t.total_revenue), 2) AS tip_pct_of_revenue
FROM stg_trips_monthly t
JOIN stg_zones z ON z.location_id = t.pu_location_id
GROUP BY z.borough
ORDER BY SUM(t.total_revenue) DESC

Unquoted references to staged tables resolve because the staging database runs in PostgreSQL compatibility mode with lower-folded identifiers — the author style the specs themselves use. That parameter is a correctness invariant of the identifier scheme, not an operator preference.

The demo family goes further: nyc/mobility/rainy_vs_dry_ridership joins the same Postgres trips to daily precipitation from a MySQL datasource and a calendar from SQLite — one query, nothing landed.

Where this fits

And where it does not

  • Staging is in memory, per execution. That is the right shape for joining a rollup to a lookup, and the wrong one for joining two billion-row fact tables. Push the aggregation down into each source node — that is what the source engines are for.
  • DuckDB as a staging engine is planned, not shipped. H2 is the staging engine today; the abstraction exists so a larger analytical engine can take its place.
  • This is not data virtualization as a platform. There is no unified catalog, no semantic layer, no query planner spanning engines. There is a pipeline that reads several databases and joins their results — which is the part most teams actually needed.
  • It is not an ETL tool. Nothing is copied anywhere on a schedule. If you want a result written back to a database, that is a node you add on purpose.

The SQL MCP server overview → · Staging specification → · Pipeline contract →

Asked before

Federated query, in questions

Where does the cross-engine join actually run?

In a per-execution staging database: an isolated in-memory H2 instance created when the run starts, at jdbc:h2:mem:exec_{execution_id}, where each source's result is a table the join SQL reads. The lifecycle is docs/staging.md §3.1.

Is anything copied between the engines?

Nothing permanent: results are staged into the scratch database for the duration of the one execution, and the staging database is destroyed when the execution ends — by design, not as a limitation. The design is docs/staging.md §1.

Can I try it without my own data?

Yes — the NYC demo seeds the Postgres trips, the SQLite zone lookup and the pipeline that joins them (nyc/mobility/revenue_by_borough), and its SQL is printed verbatim on the page. The demo quickstart is docs/deployment.md Appendix B.

Will staging always be in-memory H2?

No — DuckDB as a staging engine is a planned roadmap item, and the staging abstraction exists so a larger analytical engine can take H2's place. The honest limit today: join a rollup to a lookup, not two billion-row fact tables. The roadmap is docs/ROADMAP.md §2.

Run the join on the demo, then on your databases

./app.sh --start --demo nyc seeds every source the worked example uses. The step from there to your own sources is registering a datasource and asking the agent the same question.

Demo quickstart Connect your agent