Applies toUnity Catalog Lakehouse Federation (connections and foreign catalogs to SQL Server, Azure SQL, PostgreSQL, MySQL, Snowflake, Synapse, BigQuery and others), Lakeflow Connect managed connectors, Auto Loader and custom pipelines
The two approaches
| Federation (query in place) | Ingestion (copy into Delta) | |
|---|---|---|
| Data freshness | Live from source | As fresh as the pipeline |
| Performance | Limited by source and network; pushdown helps | Fast, Delta-optimised, cacheable |
| Source load | Every query hits the source | Load only during extraction (CDC/incremental) |
| History | Only what source keeps | Keep history, snapshots, SCD |
| Governance | Unity Catalog permissions on foreign catalog | Full Unity Catalog on Delta tables |
| Setup | Connection + foreign catalog | Pipeline, schedules, monitoring |
When to federate
- Exploring a new source before deciding to ingest.
- Low-volume lookups or occasional reports.
- Joining small reference data from an operational system.
- Migration periods: query the old warehouse while moving to Delta.
CREATE CONNECTION sqlsrv_erp TYPE sqlserver
OPTIONS (host 'erp-sql.database.windows.net', port '1433',
user secret('kv-scope', 'erp-user'), password secret('kv-scope', 'erp-pass'));
CREATE FOREIGN CATALOG erp USING CONNECTION sqlsrv_erp OPTIONS (database 'ERP');
GRANT USE CATALOG, USE SCHEMA, SELECT ON CATALOG erp TO `dbx-finance-readers`;
Check the current list of supported sources and authentication methods; some sources support managed identity or OAuth instead of passwords.
When to ingest
- Dashboards or jobs hitting the data many times a day.
- Large scans or joins with lake data.
- Need for history, time travel or reproducible snapshots.
- Source owners can't accept analytical query load.
Lakeflow Connect managed connectors (for supported databases and SaaS apps) handle CDC and incremental ingestion with less custom code. Otherwise use CDC tools or pipelines (see the ingestion pattern guide).
Decide per source
| Factor | Points to federation | Points to ingestion |
|---|---|---|
| Queries per day | Few | Many |
| Data volume per query | Small | Large |
| Source sensitivity to load | Low | High |
| Need for history | No | Yes |
| Latency need | Real-time read | Minutes to hours fine |
Protect the source
- Use a read replica or secondary for federated queries.
- Limit who can query foreign catalogs.
- Monitor query load on the source; move to ingestion if it grows.
Tip
Start with federation to learn which tables people actually use. Ingest those, and leave the rest federated or unexposed.
Checklist
- Each source assessed with the factor table
- Connections use secrets or managed identity
- Foreign catalog access granted to groups only
- Source load monitored
- Heavily used data ingested via connectors or pipelines