Databricks DE · Topic 9.2

Lakehouse Federation

Query external databases (Postgres, MySQL, Snowflake, BigQuery…) in place — no ingestion, no copies — via connections + foreign catalogs, governed by Unity Catalog, with query pushdown. This deep dive walks setup, queries, and governance.

query in placeforeign catalogpushdowncross-source join

The one-liner

Lakehouse Federation = a connection + foreign catalog that lets you query an external DB's tables with normal SQL, read-only and without copying, governed by UC, pushing filters down to the source.

📚 Analogy: a library inter-loan — instead of buying a copy of every book (ingestion), your card lets you read books that live in other libraries through one catalog. They stay on their own shelves.

Federate vs ingest — click each

Federate (no copy)

Ingest (copy)

1 · Connection + foreign catalog

-- 1) connection holds credentials/host (a UC securable; reuse it)
CREATE CONNECTION pg_conn TYPE postgresql
  OPTIONS (host 'db.corp.net', port '5432',
           user secret('kv','pg_user'), password secret('kv','pg_pwd'));

-- 2) foreign catalog maps the remote namespace into UC 3-level naming
CREATE FOREIGN CATALOG pg USING CONNECTION pg_conn OPTIONS (database 'appdb');

Supported sources (connector TYPE)

Source	TYPE	Source	TYPE
PostgreSQL	`postgresql`	Snowflake	`snowflake`
MySQL	`mysql`	Redshift	`redshift`
SQL Server	`sqlserver`	BigQuery	`bigquery`

2 · Query in place + pushdown + cross-source join

-- read-only; supported predicates/aggregations push down to Postgres
SELECT region, count(*) FROM pg.public.orders
WHERE order_date > current_date() - 7 GROUP BY region;

-- the real power: join a LIVE external table to a lakehouse table
SELECT o.order_id, o.amount, c.segment
FROM pg.public.orders o                       -- federated (Postgres, live)
JOIN main.sales.dim_customer c                -- native Delta
  ON o.customer_id = c.id;

3 · Governance — foreign tables are UC objects

GRANT USE CATALOG ON CATALOG pg TO `analysts`;
GRANT USE SCHEMA  ON SCHEMA  pg.public TO `analysts`;
GRANT SELECT      ON TABLE   pg.public.orders TO `analysts`;

Foreign tables get UC grants, lineage, and discovery like native tables — governance doesn't stop at the lakehouse boundary.

How it works — click each

1 · Connection▸

Holds the credentials + host for the external system (Postgres, Snowflake…). Created once, reused.

2 · Foreign catalog▸

Surfaces the remote databases/schemas/tables into UC's 3-level namespace as foreign tables — governed like native tables.

3 · Query with pushdown▸

Query foreign tables with normal SQL; supported filters/aggregations are pushed down to the source so it does the work and less data moves. Complex expressions may run in Databricks — check the query profile.

Federation vs ingestion vs Delta Sharing

	Federation	Ingestion	Delta Sharing
Data moves?	No — in place	Yes — copied	No — shared in place
Direction	Read others' DBs	Pull into lakehouse	Share out to recipients
Best for	Ad-hoc / live	Repeated heavy use	Cross-org sharing

💡 Decision rule: federate for exploration & one-off joins; ingest once a query is frequent/heavy (cheaper, faster, insulates the OLTP source); share when giving your data to someone else.

Why it matters

Not all data should be copied — federation gives live, governed access for ad-hoc queries without ETL.
"Query Postgres without a pipeline?" → federate; "land it for heavy repeated use?" → ingest. Knowing federate vs ingest is the interview point.

📊 Real-world use case: analysts join a live Postgres orders table with lakehouse customers for a one-off report — a foreign catalog exposes it as pg.public.orders, queried directly, no pipeline.

Uses, edge cases & limitations

Uses: ad-hoc reporting on operational DBs, incremental migration, hybrid architectures, exploring before ingesting, joining live external data to lakehouse tables.
Edge cases: queries hit the live source — heavy/repeated queries can load the operational DB; pushdown coverage varies by connector (check the query profile).
Limitation: read-only; performance depends on the remote system & network. For frequent heavy workloads, ingesting is usually faster/cheaper.

Common gotchas

❌ Confusing federation (query others' DBs) with Delta Sharing (share yours out) — opposite directions.
❌ Hammering a production OLTP DB with heavy federated analytics.
❌ Expecting writes — federation is read-only.
❌ Federating a table you query constantly when ingesting once is cheaper/faster.
❌ Assuming full pushdown — complex expressions may pull rows then compute in Databricks.