Federated Queries - User Guide

Federated queries let you execute one SQL statement across multiple sources — databases, local files, and S3-compatible storage — from a single SQL Console session.

What federated queries are for

joining data from multiple databases
combining database tables with local files or S3-backed files
comparing data across providers (e.g., AWS S3 vs DigitalOcean Spaces)
validating multi-source logic before turning the result into a stream workflow

Limitations

Federated queries run through DuckDB, which may differ from native database behavior
Not all providers and engines behave identically — use explicit casts when types differ across sources
Federated queries are read-only
Query execution is time-limited; narrow long-running checks with filters or smaller ranges
The SQL Console pages federated results from the server, but each cross-source page is still a fresh query execution. Use exports or stream workflows when you need to move large result sets.

Setting up a multi-source session

Open the SQL Console from Data Explorer.
Click Manage sources in the top-right corner. This opens the Query Session panel.
The panel lists available connections in two sections:
- DATABASES — PostgreSQL, MySQL, and other database connections
- FILES — local file connections and S3-compatible storage (DigitalOcean Spaces, MinIO, etc.)
Check two or more connections to select them.
For database connections, pick a database from the dropdown that appears below the selected connection. Use + DB to add another database from the same connection.
Use + Add at the top of the section to create a new connection if needed.

When more than one source is selected, the console switches to multi-source (DuckDB) mode automatically. The header updates to show "Multi-source • N sources" with alias badges for each selected source.

Aliases

Each selected source gets an auto-generated alias based on its connection type — for example, my1 for MySQL, pg1 for PostgreSQL, aws for S3. Aliases can be edited inline in the Query Session panel.

Alias rules:

Must start with a letter
Letters, numbers, and underscores only
Keep them short and readable: pg1, my1, aws, do

Naming conventions

In multi-source mode, qualify all table references with aliases:

Source type	Pattern	Example
PostgreSQL	`alias.schema.table`	`pg1.public.actor`
MySQL	`alias.database.table`	`my1.sakila.film`
S3 / files	`read_*` functions with alias scheme	`read_parquet('aws://bucket/path/*.parquet')`

Examples

Cross-database join (MySQL + PostgreSQL)

SELECT p.first_name, p.last_name, m.title
FROM my1.sakila.film AS m
JOIN pg1.public.film_actor AS fa ON m.film_id = fa.film_id
JOIN pg1.public.actor AS p ON p.actor_id = fa.actor_id
WHERE m.title = 'ACE GOLDFINGER';

Database + S3 join

SELECT c.id, c.email, o.total
FROM pg1.public.customers c
JOIN read_parquet('aws://analytics/orders/*.parquet') o
  ON c.id = o.customer_id
LIMIT 100;

Query two S3 providers

Assign each S3 connection an alias (e.g., aws, do), then use the alias as the URL scheme:

SELECT a.actor_id, a.first_name AS aws_name, b.first_name AS do_name
FROM read_parquet('aws://my-bucket/sakila/actor/*.parquet') a
JOIN read_parquet('do://my-bucket/sakila/actor/*.parquet') b
  ON a.actor_id = b.actor_id
LIMIT 50;

UNION across S3 providers

SELECT 'AWS' AS source, actor_id, first_name, last_name
FROM read_parquet('aws://bucket/actor/*.parquet')
UNION ALL
SELECT 'DO' AS source, actor_id, first_name, last_name
FROM read_parquet('do://bucket/actor/*.parquet')
ORDER BY actor_id
LIMIT 100;

Database + local file

SELECT db.film_id, db.title, ratings.rating
FROM pg1.public.film db
JOIN read_parquet('/data/ratings.parquet') ratings
  ON db.film_id = ratings.film_id
LIMIT 100;

Three-way join

SELECT
  m.title AS mysql_title,
  p.title AS postgres_title,
  csv.title AS file_title
FROM my1.sakila.film m
JOIN pg1.public.film p ON m.film_id = p.film_id
JOIN read_csv_auto('/data/film.csv') csv ON m.film_id = csv.film_id
WHERE m.title LIKE 'A%'
LIMIT 10;

File-only query

You can query files in a multi-source session even when the query does not use a database table:

SELECT *
FROM read_parquet('/data/analytics.parquet')
LIMIT 100;

Supported file readers

Any DuckDB file reader that accepts a URL string works with S3 aliases:

read_parquet('alias://bucket/path/*.parquet')
read_csv_auto('alias://bucket/path/*.csv')
read_json_auto('alias://bucket/path/*.json')

Glob patterns work for local files and S3-style paths:

SELECT COUNT(*) AS rows
FROM read_parquet('/data/exports/*.parquet');

SELECT COUNT(*) AS rows
FROM read_parquet('aws://bucket/data/**/*.parquet');

SQL templates

The Templates panel (Ctrl+J) provides context-aware snippets for federated mode. When multiple sources are selected, templates are organized into sections:

Joins — cross-database JOIN, UNION, database+S3 JOIN, database+file JOIN, S3+S3 JOIN
Databases — grouped by alias (e.g., my1 (MySQL), pg1 (PostgreSQL)), each with:
- Starter query for that alias
- List namespaces (using duckdb_tables())
- List tables (using duckdb_tables())
Session — SHOW DATABASES; to list all attached aliases

Templates are generated dynamically based on the selected connections and their aliases. A search field at the top of the panel filters across all sections.

See SQL Console — Templates for full details.

Preview and inspect safely

For exploration, start with a small result and increase the range only after the shape is correct:

SELECT *
FROM pg1.public.orders
WHERE order_date >= DATE '2026-01-01'
LIMIT 100;

For cross-source joins, filter each side and project only columns used by the result:

SELECT c.id, c.email, o.total
FROM pg1.public.customers c
JOIN my1.shop.orders o ON CAST(o.customer_id AS INTEGER) = c.id
WHERE c.created_at >= DATE '2026-01-01'
  AND o.created_at >= '2026-01-01'
LIMIT 100;

Use branch parentheses when each side of a UNION ALL has its own ORDER BY or LIMIT:

(SELECT 'Postgres' AS source, film_id, title
 FROM pg1.public.film
 WHERE title LIKE 'A%'
 LIMIT 5)
UNION ALL
(SELECT 'MySQL' AS source, film_id, title
 FROM my1.sakila.film
 WHERE title LIKE 'A%'
 LIMIT 5);

Paging large result sets

The multi-source SQL Console uses the same results footer as direct database tables. Changing the page size or using Next/Previous re-runs the query with LIMIT / OFFSET applied to the SQL itself.

For simple single-source queries, this is usually cheap because DuckDB can push the page boundary into the source. For cross-source joins, each page re-executes the full federated query, so keep filters selective and select only the columns you need.

The footer can show "of more" instead of an exact total. That is intentional: exact totals require a separate count over the unwrapped query and can be as expensive as another full federated execution.

Reduce data movement with source-side queries

Regular federated SQL is the default path for exploration, previews, row-by-row comparisons, and joins:

SELECT m.id, m.total, p.total
FROM my1.shop.orders m
JOIN pg1.public.orders p ON m.id = p.id
WHERE m.created_at >= '2026-01-01'
LIMIT 100;

DuckDB attaches the selected sources, pushes simple filters and column selection down where it can, and performs the cross-source work locally. In this path, only the rows and columns needed by the query should be read from each source.

For large validation queries, go one step further when each source database can calculate the final check itself: return the aggregate result instead of returning the matching rows. DuckDB's MySQL and PostgreSQL scanner extensions expose passthrough table functions:

mysql_query('alias', 'source SQL')
postgres_query('alias', 'source SQL')

Those functions run the inner SQL inside the selected source database and return only the result rows to DuckDB. Use this for counts, sums, min/max checks, grouped summaries, and checksum-style validation.

-- Replace: your_table, public.your_table, text_column, amount_column, id.
SELECT
  mysql_stats.row_count AS mysql_rows,
  pg_stats.row_count AS pg_rows,
  mysql_stats.text_chars AS mysql_text_chars,
  pg_stats.text_chars AS pg_text_chars,
  mysql_stats.amount_sum AS mysql_amount_sum,
  pg_stats.amount_sum AS pg_amount_sum
FROM mysql_query(
  'my1',
  'SELECT COUNT(*) AS row_count,
          SUM(CHAR_LENGTH(text_column)) AS text_chars,
          SUM(amount_column) AS amount_sum
   FROM your_table
   WHERE id <= 1000000'
) mysql_stats
CROSS JOIN postgres_query(
  'pg1',
  'SELECT COUNT(*) AS row_count,
          SUM(LENGTH(text_column)) AS text_chars,
          SUM(amount_column) AS amount_sum
   FROM public.your_table
   WHERE id <= 1000000'
) pg_stats;

The SQL Console includes this pattern in Templates as Source-side aggregate check when both MySQL and PostgreSQL aliases are selected.

Use regular federated SQL when...	Use `mysql_query()` / `postgres_query()` when...
You need joined rows in the result	You need a count, sum, min/max, checksum, or grouped summary
You are exploring data with `LIMIT`	The source database can return a small aggregate result
The join decides which rows count	The aggregate is source-local and safe before any join
You are joining files/S3 with database rows	You can return one aggregate result instead of matching source rows

Only aggregate before a join when you already know the join will not change the row set or duplicate rows, such as a unique key comparison where the ID ranges are known to match.

Mode transitions

Single-source → multi-source: adding a second source switches to DuckDB mode automatically. Existing SQL is not rewritten.
Multi-source → single-source: removing sources back to one switches back to direct mode automatically.
Explicit rewrite: when a starter query needs alias-qualified naming, the console offers a "Rewrite starter SQL to federated naming" action.

Practical rules

Always qualify table references with aliases in multi-source mode
Unqualified references (e.g., SELECT * FROM actor) will fail in multi-source mode
Add LIMIT while exploring, then remove it only when the output size is intentional
Filter each source early and select only the columns needed by the result
Use explicit casts when types differ across sources
S3 alias routing covers S3-compatible providers; GCS and Azure are not currently supported with alias routing

Troubleshooting

"Table not found"

Check that the table uses the correct alias-qualified name:

PostgreSQL: alias.schema.table, for example pg1.public.film
MySQL: alias.database.table, for example my1.sakila.film

Also verify that the source is selected in Manage sources.

add filters to each source
select fewer columns
use LIMIT for previews
aggregate before returning rows when the final answer is a count, sum, min/max, or checksum
use mysql_query() / postgres_query() when the source can safely reduce rows before DuckDB joins the results

SQL Console — Query editor, autocomplete, diagnostics
Federated Query API Workflows