3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

DP-750 Exam Focus: The blueprint bullet is "Implement a foreign catalog by configuring connections." Expect scenarios where an operational database must be queried from Azure Databricks without copying the data, and where the wrong answer is "ingest it nightly."

---

1. What Lakehouse Federation Is

Lakehouse Federation is the Azure Databricks query federation platform. It gives governed, read-only access to data that lives outside Unity Catalog by projecting the external system into the familiar catalog.schema.table namespace as a foreign catalog, with Unity Catalog access controls applied at the table level and automatic query pushdown to the remote engine.

Two things follow immediately, and both are examinable:

1. The data never moves. There is no copy, no schedule, and no storage cost in your lakehouse.
2. You cannot write. Foreign catalogs are read-only in Azure Databricks. INSERT, UPDATE, MERGE, and DELETE against a foreign table are not supported.

---

2. The Two-Step Implementation

Step 1: Create the Connection

A connection is itself a Unity Catalog securable object. It stores the endpoint and the credentials once, so individual users never handle the source password.

-- Azure SQL Database / SQL Server
CREATE CONNECTION sqlserver_orders_prod TYPE SQLSERVER
OPTIONS (
  host 'orders-prod.database.windows.net',
  port '1433',
  user secret('federation_scope', 'sqlserver_user'),
  password secret('federation_scope', 'sqlserver_password')
);

-- PostgreSQL
CREATE CONNECTION postgres_crm TYPE POSTGRESQL
OPTIONS (host 'crm.postgres.database.azure.com', port '5432',
         user secret('federation_scope', 'pg_user'),
         password secret('federation_scope', 'pg_password'));

Reading the credentials from a secret scope rather than inlining them is the pattern the exam expects - it keeps the plaintext out of the connection definition, notebooks, and audit output.

Step 2: Create the Foreign Catalog

CREATE FOREIGN CATALOG orders_federated
USING CONNECTION sqlserver_orders_prod
OPTIONS (database 'OrdersDB');

-- Now the external database is addressable in the three-level namespace
SELECT customer_id, order_total
FROM   orders_federated.dbo.orders
WHERE  order_date >= current_date() - INTERVAL 7 DAYS;

Step 3: Grant Access

Foreign catalogs use the same hierarchical privilege model as any other catalog (Section 4.1):

GRANT USE CATALOG ON CATALOG orders_federated TO analytics_readers;
GRANT USE SCHEMA  ON SCHEMA  orders_federated.dbo TO analytics_readers;
GRANT SELECT      ON TABLE   orders_federated.dbo.orders TO analytics_readers;

Privileges Required to Build It

---

3. Supported Sources

Query federation (JDBC pushdown) currently supports:

• Microsoft SQL Server and Azure Synapse (SQL Data Warehouse)
• MySQL, PostgreSQL, Oracle, Teradata
• Amazon Redshift, Google BigQuery, Snowflake
• Salesforce Data 360
• Other Databricks workspaces

Catalog federation - the second Lakehouse Federation mode - supports the legacy Databricks Hive metastore, external Hive metastores, Snowflake, Salesforce Data 360 file sharing, Palantir Foundry, and OneLake.

---

4. Query Federation Versus Catalog Federation

This distinction is the highest-value fact in the section.

Pushdown has a victim. Because query federation executes part of the plan on the source system, a poorly filtered federated query puts load on a live operational database. Always filter and project in the WHERE and SELECT clauses so the pushdown is selective, and never point a dashboard that refreshes every minute at a production OLTP system through federation.

---

5. Federation Versus Ingestion: the Decision Rule

Databricks states the rule directly: when a source supports both Lakehouse Federation and Lakeflow Connect, prefer Lakeflow Connect if performance on higher data volumes and lower latency are priorities.

When Federation Is Not Enough and JDBC Is

The Spark Data Source API remains the escape hatch. Use it when Lakehouse Federation does not support your source, when you need write access, or when you need explicit control over partitioned parallel reads. Databricks Runtime bundles connectors for PostgreSQL, SQL Server, MySQL, Snowflake, and Redshift, and a JDBC Unity Catalog connection lets you bring your own driver with centralized credential management.

---

6. Exam Traps

• "Create a foreign catalog and then MERGE into it" is always wrong. Federation is read-only.
• A connection is not a catalog. Creating the connection alone exposes nothing; the foreign catalog is the second, separate statement.
• Federation does not give you time travel. VERSION AS OF works on Delta tables, not on a live SQL Server table surfaced through federation.
• Dedicated access mode caveat: on dedicated access mode compute, reads and REFRESH FOREIGN TABLE on foreign Iceberg tables require ALL PRIVILEGES.