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.

-- PostgreSQLのデータベースをFOREIGN CATALOGとしてマッピング
CREATE FOREIGN CATALOG pg_catalog
USING CONNECTION pg_production
OPTIONS (database 'production_db');

-- Snowflakeのデータベースをマッピング
CREATE FOREIGN CATALOG sf_catalog
USING CONNECTION sf_analytics
OPTIONS (database 'ANALYTICS_DB');

-- BigQueryのプロジェクトをマッピング
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.

-- PostgreSQLのテーブルをクエリ
SELECT * FROM pg_catalog.public.customers WHERE region = 'APAC';

-- SnowflakeのテーブルとローカルのテーブルをクロスデータベースJOIN
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';

-- BigQueryのテーブルを参照
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.

-- 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;

-- 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.

-- プッシュダウンの確認（EXPLAIN文）
EXPLAIN FORMATTED
SELECT customer_id, customer_name
FROM pg_catalog.public.customers
WHERE region = 'APAC' AND created_at >= '2026-01-01';

-- 結果の PushedFilters セクションに
-- [region = 'APAC', created_at >= '2026-01-01'] が表示されれば
-- フィルタがプッシュダウンされている

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

[外部DB: PostgreSQL / MySQL / Snowflake / BigQuery]
       │
       │  CONNECTION (認証情報はSecretで管理)
       v
[FOREIGN CATALOG] → UC名前空間にマッピング
       │
       ├── アドホック分析: SELECT ... WHERE で直接クエリ
       │     → Pushdownで転送量を最小化
       │
       └── ETLパイプライン: 定期的にDelta Lakeに取り込み
             → CTAS/MERGEでDeltaテーブルにマテリアライズ
             → 大量データの結合・集約は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