Skip to main content

07.13 Lakehouse Federation & Foreign Catalogs

Lakehouse Federation lets Databricks query external data without first copying it into the lakehouse.

The key architectural idea is simple:

External Database | Connection | Foreign Catalog | Unity Catalog | Databricks SQL / Spark | Consumer

A foreign catalog mirrors an external database in Unity Catalog. The data remains in the external system, while Databricks provides governed, read-only access through Unity Catalog. This is the foundation for many hybrid data architectures. Lakehouse Federation | Foreign catalogs

1. Federation vs ingestion​

These are different architectural choices.

Ingestion​

Source ↓ Extract ↓ Transform ↓ Delta ↓ Databricks

You move data.

Federation​

Source ↓ Foreign Catalog ↓ Databricks query

You query data where it already lives.

The architect must therefore decide whether the requirement is access or ownership.

2. What is a foreign connection?​

A Unity Catalog connection is a securable object representing the remote system.

It contains information needed to communicate with the external source, including:

  • source type;
  • endpoint;
  • port or URL;
  • authentication information;
  • connection properties.

Credential-bearing options should use Databricks secret handling rather than hard-coding credentials in SQL. CREATE CONNECTION

Conceptually:

Unity Catalog | Connection | Remote DB

3. What is a foreign catalog?​

A foreign catalog is a Unity Catalog securable object that mirrors a database in an external system.

It provides:

  • discoverability;
  • Unity Catalog permissions;
  • read-only querying;
  • lineage integration;
  • centralized governance metadata.

The source remains external.

UC | Foreign Catalog | External DB

Manage foreign catalogs

4. Current supported systems​

Lakehouse Federation supports many external platforms, including:

  • MySQL;
  • PostgreSQL;
  • Teradata;
  • Oracle;
  • Amazon Redshift;
  • Salesforce Data 360;
  • Snowflake;
  • Microsoft SQL Server;
  • Azure Synapse;
  • Google BigQuery;
  • Databricks.

The exact authentication and feature support varies by connector, so architects should always validate the connector-specific documentation before committing to a design. Query federation overview

5. Federation architecture​

A production architecture looks like:

┌──────────────────────────────────────────┐ │ Unity Catalog │ │ │ │ Connection → Foreign Catalog → Grants │ └───────────────────┬──────────────────────┘ | federated query | v ┌───────────────────┐ │ External Database │ │ Oracle / SAP / │ │ Snowflake / etc. │ └───────────────────┘

The query path is important.

For federated queries, traffic goes between Databricks compute and the external database. Unity Catalog and the Databricks control plane are not on that network path. OAuth authentication can additionally require control-plane access to the OAuth endpoint. Networking recommendations

6. Control plane vs data path​

This is a common architect interview question.

Governance path​

User ↓ Unity Catalog ↓ Privileges

Query path​

Databricks Compute ↓ External Database

Do not design the network assuming every federated query passes through the Databricks control plane.

7. Query pushdown​

Federated query execution attempts to push work to the external source.

Conceptually:

Databricks query | optimizer | pushdown | External DB | result | Databricks

Pushdown can reduce data movement dramatically.

Example:

SELECT supplier_id, SUM(spend) FROM oracle_procurement.orders WHERE fiscal_year = 2026 GROUP BY supplier_id;

The objective is to let the source database perform as much selective work as practical.

Never assume every expression, join, function, or transformation will push down identically across every connector.

8. Federation is not a magic performance layer​

Consider:

5 TB external table | SELECT * | Databricks

This can become an expensive architecture.

Better:

filter ↓ project required columns ↓ aggregate where appropriate ↓ return small result

Federation reduces data-copy requirements. It does not remove source-system compute, network, concurrency, or workload limitations.

9. Foreign catalog permissions​

Foreign catalogs use Unity Catalog's authorization model.

You can govern access at:

  • catalog;
  • schema;
  • table.

This allows a central platform team to provide governed access without granting every consumer credentials directly to the external database.

The source database still has its own security model.

Therefore:

Cloud / DB security + Unity Catalog security

Both matter.

10. Connection permissions​

Connections are themselves securable objects.

Important privileges include:

  • CREATE FOREIGN CATALOG;
  • USE CONNECTION.

Connection ownership and permissions should be tightly controlled because the connection represents access to an external system. Manage connections

A good enterprise pattern is:

Platform team | Owns connections | Domain teams | Receive catalog/table access

Do not distribute source credentials to every analyst.

11. Read-only semantics​

Foreign catalogs provide read-only access through Lakehouse Federation.

This makes them particularly useful for:

  • operational reporting;
  • migration assessment;
  • data discovery;
  • proof-of-concept analytics;
  • hybrid architectures;
  • gradual modernization.

If the requirement is sustained high-volume transformation, federation may not be the right long-term execution model.

12. Federation vs Lakeflow Connect​

This distinction is critical.

Lakehouse Federation​

External system ↓ Query remotely ↓ Analyze

Lakeflow Connect​

External system ↓ Managed ingestion ↓ Delta / lakehouse ↓ Transform / serve

Databricks currently recommends Lakeflow Connect when higher data volumes and lower latency make ingestion the better performance choice. Connect external databases and catalogs | Query-based ingestion

Decision:

Need live access with minimal movement? → Federation

Need local ownership, repeated processing, historical snapshots or high-volume analytics? → Ingestion

13. Federation vs catalog federation​

Databricks distinguishes query federation from catalog federation.

Query federation​

Queries are pushed to an external database, typically through JDBC.

Catalog federation​

For supported catalog/storage systems, Databricks can access foreign tables directly through the external catalog/storage architecture.

Catalog federation can be more performance-optimized because the query executes on Databricks compute rather than repeatedly relying on remote database compute.

Use the correct pattern for the source architecture rather than treating all "federation" as one technology. Federation overview

14. Hybrid enterprise architecture​

A mature organization often uses both.

┌───────────────┐ │ Operational │ │ databases │ └───────┬───────┘ | Federation | v ┌─────────────┐ │ Databricks │ │ UC │ └──────┬──────┘ | high-value data | Ingest | v Delta Lakehouse

Federation becomes the access bridge.

Delta becomes the analytical ownership layer.

15. Migration architecture​

Federation is powerful during modernization.

Example:

Legacy Oracle | Foreign Catalog | Users validate data | New Delta pipeline | Reconciliations | Production cutover

This avoids forcing every consumer to wait for the complete migration.

16. SAP/BW perspective​

For your SAP-oriented architecture, think:

S/4HANA / SAP DB | Integration | Datasphere / BW | External analytical source | Lakehouse Federation | Unity Catalog | Databricks analytics

Federation is particularly useful when the source must remain authoritative.

For large analytical workloads, however, establish a governed Delta copy rather than repeatedly hammering the operational source.

17. Federation + SAP authorization​

Do not assume Unity Catalog replaces source-system authorization.

You have two control planes:

SAP / source authorization + Unity Catalog authorization

A user may be authorized in Databricks but still require appropriate source-side permissions.

Enterprise designs should document:

  • source identity;
  • connection identity;
  • Unity Catalog identity;
  • row/column controls;
  • audit ownership.

18. Networking architecture​

Before implementing federation, validate:

  • DNS;
  • routing;
  • firewall;
  • security groups;
  • private endpoints;
  • allowlists;
  • ports;
  • TLS;
  • database listener;
  • serverless connectivity;
  • OAuth endpoint connectivity where applicable.

The fundamental requirement is:

Databricks Compute | network | External Database

Networking recommendations

19. Cost model​

Federation has at least four cost dimensions:

  1. Databricks compute.
  2. External database compute.
  3. Network transfer.
  4. Operational complexity.

A query that looks cheap in Databricks may create significant load on the source system.

Therefore establish:

  • workload limits;
  • query governance;
  • source capacity;
  • concurrency limits;
  • monitoring;
  • caching/materialization strategy where appropriate.

20. Performance engineering​

Use this sequence:

Filter ↓ Project ↓ Join carefully ↓ Aggregate ↓ Return minimum data

Avoid:

  • SELECT *;
  • unrestricted scans;
  • repeated large joins;
  • unnecessary cross-system joins;
  • high-frequency BI queries against operational databases;
  • unbounded analyst access.

21. Cross-system joins​

A common anti-pattern:

Oracle ─────┐ ├── huge join ── Databricks Snowflake ───┘

Ask first:

Where should the data physically meet?

For a large recurring workload, ingesting both datasets into Delta is often more predictable.

Federation is strongest when the remote access remains selective.

22. Observability​

Monitor:

  • query latency;
  • remote execution time;
  • returned bytes;
  • source CPU;
  • source concurrency;
  • failed connections;
  • timeout rates;
  • authentication failures;
  • query volume by user;
  • expensive federated queries.

Governance should identify both:

Who accessed the data?

and

What impact did that access have on the source system?

23. Data lineage​

Foreign catalog tables can participate in Unity Catalog lineage.

This is important because the external source does not become invisible merely because the data wasn't copied.

A useful enterprise lineage view is:

External Source ↓ Foreign Catalog ↓ Transformation ↓ Delta Table ↓ BI / ML / AI

Foreign catalog lineage

24. Hands-on Lab 1 — PostgreSQL federation​

Architecture:

PostgreSQL ↓ Connection ↓ Foreign Catalog ↓ Unity Catalog permissions ↓ Databricks SQL

Tasks:

  1. Create a test PostgreSQL connection.
  2. Register the foreign catalog.
  3. Inspect schemas.
  4. Grant a group read access.
  5. Run a filtered query.
  6. Run EXPLAIN.
  7. Record execution time.
  8. Compare source-side and Databricks-side behavior.

Use a non-production source for the first exercise.

25. Hands-on Lab 2 — Federation vs ingestion​

Take one dataset and design both:

Option A​

Foreign Catalog → query

Option B​

Source → Lakeflow Connect → Delta

Measure:

  • latency;
  • source load;
  • Databricks compute;
  • network;
  • operational complexity;
  • repeatability.

Then document the architectural decision.

26. Hands-on Lab 3 — Migration bridge​

Design:

Legacy DB ↓ Foreign Catalog ↓ Existing reports

in parallel with:

Legacy DB ↓ Ingestion ↓ Delta ↓ New reports

Define reconciliation checks and a cutover strategy.

27. Hands-on Lab 4 — Security model​

Create a matrix:

LayerResponsibility
Source DBSource identity and native authorization
ConnectionSecure connectivity
Unity CatalogDatabricks governance
Foreign catalogCatalog/schema/table visibility
NetworkReachability and isolation
AuditAccess evidence

Then identify which team owns each layer.

28. Troubleshooting​

Connection fails​

Check:

  • DNS;
  • route;
  • firewall;
  • port;
  • TLS;
  • credentials;
  • OAuth;
  • source availability.

Catalog appears but query fails​

Check:

  • catalog permissions;
  • connection privileges;
  • source authorization;
  • compute access mode;
  • connector-specific limitations.

Query is slow​

Check:

  • pushdown;
  • filter selectivity;
  • returned data volume;
  • source indexes;
  • source CPU;
  • network latency;
  • cross-system joins.

Source database is overloaded​

Stop treating federation as a free ingestion replacement.

Move recurring heavy workloads to a governed Delta ingestion pattern.

29. Common anti-patterns​

Anti-pattern 1 — Federation as permanent ETL​

If you repeatedly scan the same terabytes, ingest them.

Anti-pattern 2 — Analyst access to operational databases​

Use curated access and workload controls.

Anti-pattern 3 — Ignoring source performance​

The source remains part of your production architecture.

Anti-pattern 4 — Sharing source credentials​

Govern the connection centrally.

Anti-pattern 5 — Assuming all SQL pushes down​

Validate with execution plans and connector behavior.

Anti-pattern 6 — Cross-system mega joins​

Move data into a common analytical layer when scale requires it.

30. Architect decision matrix​

RequirementRecommended pattern
Live selective accessFederation
Migration discoveryFederation
POC against external DBFederation
Repeated high-volume analyticsLakeflow Connect / ingestion
Historical snapshotsIngestion
Complex transformationsIngestion
Source must remain authoritativeFederation
Source cannot tolerate analytical loadIngest
Hybrid modernizationFederation + Delta
Govern external accessForeign catalog + UC

31. Enterprise reference architecture​

┌─────────────────────────────────────────────┐ │ Enterprise Sources │ │ SAP | Oracle | SQL Server | Snowflake | DBs │ └───────────────┬─────────────────────────────┘ | ┌──────┴───────┐ | | Federation Ingestion | | v v Foreign Catalog Delta Lake | | └──────┬───────┘ v Unity Catalog | ┌────────────┼────────────┐ v v v BI ML/AI Data Products | Governance Lineage / Audit / ABAC

The strongest enterprise architecture is rarely "federate everything" or "ingest everything".

It is a selective hybrid.

32. Interview questions​

What is Lakehouse Federation?

A Databricks capability for governed, read-only access to supported external data sources through Unity Catalog without first ingesting the data.

What is a foreign catalog?

A Unity Catalog securable object that mirrors an external database and provides governed read-only access.

Where does the federated query execute?

Work is executed across Databricks compute and the external system, with supported operations pushed to the remote source.

Is Unity Catalog on the query network path?

No. The federated data path is between Databricks compute and the external system. OAuth can additionally require control-plane connectivity to the OAuth endpoint.

When would you choose Lakeflow Connect instead?

When high data volume, lower latency, repeated transformation, historical ownership, or source-load isolation makes ingestion preferable.

Does federation replace source authorization?

No. Source-side authorization and Unity Catalog authorization both remain relevant.

33. Production checklist​

  • Source system approved for federated access.
  • Connection owned by the platform team.
  • Credentials securely managed.
  • Network path tested.
  • Firewall and DNS documented.
  • Foreign catalog permissions defined.
  • Source authorization documented.
  • Query pushdown tested.
  • Source capacity reviewed.
  • Query workload limits established.
  • BI concurrency assessed.
  • Cross-system joins evaluated.
  • Lineage verified.
  • Audit monitoring enabled.
  • Failure and timeout handling documented.
  • Migration/ingestion exit strategy defined.

34. Final mental model​

Remember:

EXTERNAL DATA ↓ CONNECTION ↓ FOREIGN CATALOG ↓ UNITY CATALOG ↓ GOVERNED READ ↓ QUERY / BI ↓ ┌───────────────┐ │ Need repeated │ │ heavy analytics? └───────┬───────┘ ↓ INGEST TO DELTA

The architect's rule:

Federation is an access pattern. Delta ingestion is an ownership pattern. Choose based on workload, latency, source impact, data movement, and long-term analytical responsibility.