Lakehouse Federation: Virtualize External Databases with CONNECTION, FOREIGN CATALOG & Pushdown

Lakehouse Federation lets you query tables in external databases (PostgreSQL, MySQL, Snowflake, BigQuery, and more) from Databricks without moving any data. It uses Unity Catalog CONNECTION and FOREIGN CATALOG objects to virtually map external databases into the Databricks namespace, so you can access them with ordinary SQL. The biggest advantage is bringing external data into Databricks analytics without writing any ETL.

What is Lakehouse Federation?

Federation is a "don't move the data, forward the query to the external database" approach. External database tables appear to live in Databricks and are accessed through the standard three-level namespace (catalog.schema.table).

• No data is copied: requests hit the external database in real time when the query runs
• Read-only: only SELECT is supported. INSERT, UPDATE, and DELETE are not allowed
• Unity Catalog integration: UC access controls (GRANT/REVOKE) apply to external tables as well
• Pushdown optimization: WHERE-clause filters and column projection are pushed down to the external database to reduce transfer volume

Supported Databases

Creating a CONNECTION

A CONNECTION is a Unity Catalog object that holds the connection details for an external database (host, port, and credentials). Creating one requires the CREATE CONNECTION privilege.

PostgreSQL

CREATE CONNECTION pg_production
TYPE POSTGRESQL
OPTIONS (
  host     'prod-db.example.com',
  port     '5432',
  user     'databricks_reader',
  password SECRET('federation-scope', 'pg-password')
);

Snowflake

CREATE CONNECTION sf_analytics
TYPE SNOWFLAKE
OPTIONS (
  host     'myorg-myaccount.snowflakecomputing.com',
  user     'DATABRICKS_SVC',
  password SECRET('federation-scope', 'sf-password'),
  sfWarehouse 'COMPUTE_WH'
);

BigQuery

CREATE CONNECTION bq_data_warehouse
TYPE BIGQUERY
OPTIONS (
  GoogleServiceAccountKeyJson SECRET('federation-scope', 'bq-sa-key')
);

The cardinal rule is to use Databricks Secrets (the SECRET function) for credentials rather than hard-coding passwords in SQL. Create the Secret Scope ahead of time with commands like databricks secrets create-scope.

Creating a FOREIGN CATALOG

A FOREIGN CATALOG uses a CONNECTION to map the schemas and tables of an external database into the Unity Catalog namespace.

-- Map a PostgreSQL database as a FOREIGN CATALOG
CREATE FOREIGN CATALOG pg_catalog
USING CONNECTION pg_production
OPTIONS (database 'production_db');

-- Map a Snowflake database
CREATE FOREIGN CATALOG sf_catalog
USING CONNECTION sf_analytics
OPTIONS (database 'ANALYTICS_DB');

-- Map a BigQuery project
CREATE FOREIGN CATALOG bq_catalog
USING CONNECTION bq_data_warehouse
OPTIONS (project 'my-gcp-project');

Once created, you can access these tables using the same three-level namespace as any other Unity Catalog table.

-- Query a PostgreSQL table
SELECT * FROM pg_catalog.public.customers WHERE region = 'APAC';

-- Cross-database JOIN between a Snowflake table and a local table
SELECT
  c.customer_name,
  o.total_amount
FROM pg_catalog.public.customers c
JOIN main.gold.orders o ON c.customer_id = o.customer_id
WHERE o.order_date >= '2026-01-01';

-- Reference a BigQuery table
SELECT * FROM bq_catalog.my_dataset.events LIMIT 100;

Access Control

Unity Catalog GRANT/REVOKE applies to FOREIGN CATALOG tables as well. Access is gated by two layers — the external database's native privileges and Unity Catalog privileges — so only operations allowed by both can run.

-- Grant a group usage on the FOREIGN CATALOG
GRANT USE CATALOG ON CATALOG pg_catalog TO data_analysts;
GRANT USE SCHEMA ON SCHEMA pg_catalog.public TO data_analysts;
GRANT SELECT ON TABLE pg_catalog.public.customers TO data_analysts;

-- Manage privileges on the CONNECTION
GRANT CREATE FOREIGN CATALOG ON CONNECTION pg_production TO catalog_admins;

• Creating a CONNECTION: metastore admins or holders of the CREATE CONNECTION privilege
• Creating a FOREIGN CATALOG: holders of the CREATE FOREIGN CATALOG privilege on the CONNECTION
• Querying a table: requires both a UC GRANT SELECT and read access on the external database

Pushdown Optimization

When a federated query runs, some operations are pushed down to the external database. Pushdown reduces the volume of data transferred from the external database to Databricks, which improves performance.

-- Confirm pushdown (EXPLAIN)
EXPLAIN FORMATTED
SELECT customer_id, customer_name
FROM pg_catalog.public.customers
WHERE region = 'APAC' AND created_at >= '2026-01-01';

-- If the PushedFilters section of the output shows
-- [region = 'APAC', created_at >= '2026-01-01'],
-- the filter was pushed down

Constraints and Caveats

• Read-only: SELECT only. DML (INSERT/UPDATE/DELETE/MERGE) is not supported
• Performance: transferring large volumes of data can become a network bottleneck. Filter aggressively with WHERE or periodically ingest into Delta
• Type mapping: external database types are mapped to Spark types automatically, but some types (geometry, array, etc.) may not be supported
• Transaction isolation: depends on the isolation level of the external database. Dirty-read potential is determined by that system's configuration
• Network requirements: the Databricks compute plane must be able to reach the external database (VPC peering, Private Link, etc.)
• Joins with Delta tables: you can JOIN FOREIGN CATALOG tables with local Delta tables, but all data on the external side is shipped to Spark, so joining two large tables is not recommended

Federation vs. ETL: When to Use Which

Real-World Operating Pattern

[External DB: PostgreSQL / MySQL / Snowflake / BigQuery]
       │
       │  CONNECTION (credentials held in a Secret)
       v
[FOREIGN CATALOG] -> mapped into the UC namespace
       │
       ├── Ad-hoc analysis: query directly with SELECT ... WHERE
       │     -> pushdown keeps the transferred volume small
       │
       └── ETL pipeline: ingest into Delta Lake on a schedule
             -> materialize into a Delta table with CTAS/MERGE
             -> large joins and aggregations run fast on Delta

A practical hybrid pattern is to use Federation during the exploration phase, then ingest the data that proves valuable into Delta via ETL.

Check Your Understanding

Frequently Asked Questions