Study guide

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

Microsoft Certified: Azure Databricks Data Engineer Associate DP-750

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Azure Databricks Architecture & Compute Selection

1.1 DP-750 Exam Overview & Azure Databricks Lakehouse Architecture 1.2 Compute Types & Workload Matching 1.3 Cluster Access Modes, Standard/Dedicated, & Serverless Compute

2 Chapter 2: Compute Configuration, Performance & Cost Management

2.1 Autoscaling, Databricks Pools, & Node Type Selection 2.2 Photon Vectorized Engine & Cost Governance with Cluster Policies 2.3 Library Management: Workspace, Compute-Scoped, Notebook, & Init Scripts 2.4 Auto-Termination, Budget Alerts, & Tagging for Cost Allocation 2.5 Databricks Runtime & ML Runtime Selection, and Compute Access Permissions

3 Chapter 3: Unity Catalog Storage Infrastructure & Workspace Setup

3.1 Unity Catalog 3-Level Namespace & Metastore Architecture 3.2 Azure Managed Identity, Storage Credentials, & External Locations 3.3 Managed Tables, External Tables, & Unity Catalog Volumes 3.4 Unity Catalog Naming Conventions, Environment Isolation, & Data Granularity 3.5 Lakehouse Federation: Connections, Foreign Catalogs, & Query Pushdown

4 Chapter 4: Unity Catalog Privilege Model & Access Control

4.1 Hierarchical Privilege Model & Securable Objects 4.2 Account-Level Identities, SCIM Sync, & Group Management 4.3 Object Ownership, Ownership Transfer, & Administrative Delegation

5 Chapter 5: Advanced Data Security, Masking & ABAC

5.1 Dynamic Row Filters & Column Masking Implementation 5.2 Attribute-Based Access Control (ABAC) with Unity Catalog Tags 5.3 Data Isolation across Workspaces & Multiple Metastore Patterns 5.4 Secure Data Sharing within Azure Databricks

6 Chapter 6: Governance, Auditing, Lineage & Delta Sharing

6.1 Automated Data Lineage Tracking in Catalog Explorer 6.2 Unity Catalog System Tables: Audit, Billing, and Access Logs 6.3 Data Retention Policies, Time Travel Security, & Tag Governance 6.4 Delta Sharing Protocols: Open Sharing vs. Databricks-to-Databricks 6.5 Data Discovery: Table and Column Descriptions, and AI/BI Genie Instructions

7 Chapter 7: Ingestion with Auto Loader, Batch & Structured Streaming

7.1 Auto Loader (cloudFiles) Architecture, File Notification vs. Directory Listing 7.2 Auto Loader Schema Inference, Evolution, & Rescued Data Column 7.3 Batch Ingestion with COPY INTO, CTAS, & Multipart Files 7.4 Structured Streaming Fundamentals & Azure Event Hubs / Kafka Ingestion 7.5 Streaming Watermarking, Triggers, & Output Modes 7.6 Choosing an Ingestion Tool: Lakeflow Connect, Notebooks, & Azure Data Factory

8 Chapter 8: Delta Lake Core Mechanics, ACID & Table Maintenance

8.1 Delta Lake Architecture, Parquet Files, & the _delta_log Transaction Log 8.2 ACID Transactions, Concurrency Control, & Conflict Resolution 8.3 Compaction (OPTIMIZE), Z-Ordering, & Liquid Clustering (CLUSTER BY) 8.4 Data Retention, Snapshot Isolation, & VACUUM Operations 8.5 Delta Change Data Feed (CDF) & MERGE INTO Upsert Patterns 8.6 Choosing a Table Format: Delta, Parquet, CSV, JSON, & Apache Iceberg

9 Chapter 9: Data Cleansing, Transformation & Medallion Architecture

9.1 The Medallion Lakehouse Pattern: Bronze, Silver, and Gold Layer Design 9.2 Data Cleansing: Deduplication, Null Handling, & Type Casting in PySpark/SQL 9.3 Complex Transformations: Higher-Order Functions, Arrays, Structs, & JSON Parsing 9.4 Relational Joins, Window Functions, Set Operators, & Pivoting 9.5 Enforcing Data Quality: Check Constraints & Table Invariants 9.6 Data Profiling: Summary Statistics & Distribution Assessment

10 Chapter 10: Declarative Pipelines, Lakeflow Spark & Delta Live Tables

10.1 Declarative Pipeline Fundamentals: Streaming Tables vs. Materialized Views 10.2 Data Quality Expectations: expect, expect or drop, & expect or fail 10.3 Change Data Capture Ingestion with AUTO CDC / APPLY CHANGES INTO 10.4 Pipeline Execution Modes, Serverless DLT, & Event Logs

11 Chapter 11: Workflow Orchestration with Lakeflow Jobs

11.1 Multi-Task DAG Design in Lakeflow Jobs & Task Type Selection 11.2 Task Dependencies, Parameters, Task Values, & Dynamic Variables 11.3 Triggers (Scheduled, File Arrival, Continuous), Retries, & Timeouts 11.4 Notifications, Alerting (Email, Slack, Webhooks), & Failure Handling

12 Chapter 12: CI/CD, Git Integration & Databricks Asset Bundles

12.1 Databricks Git Folders & Source Control Integration 12.2 Databricks Asset Bundles (DAB) Architecture & databricks.yml Structure 12.3 Multi-Environment Deployment (Dev / Staging / Prod) & Target Overrides 12.4 Automated Testing, Unit Testing PySpark, & CI/CD Pipelines

13 Chapter 13: Workload Monitoring, Troubleshooting, & Performance Tuning

13.1 Spark UI Mastery: Query Execution DAG, Stages, Tasks, & Skew Analysis 13.2 Troubleshooting Common Failures: OOM, Disk Spill, Data Skew, & Network Timeouts 13.3 Performance Tuning: Adaptive Query Execution (AQE), Caching, & Broadcast Joins 13.4 Workspace Monitoring with Azure Monitor, Diagnostic Settings, & Log Analytics

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

Key Takeaways Explain This Topic AI

A foreign catalog is created in two steps: CREATE CONNECTION stores the credentials and JDBC endpoint as a Unity Catalog securable, then CREATE FOREIGN CATALOG mirrors the external database into the three-level namespace.

Lakehouse Federation is read-only in Azure Databricks - foreign catalogs support SELECT with query pushdown but never INSERT, UPDATE, MERGE, or DELETE against the source.

Query federation supports Microsoft SQL Server, Azure Synapse, MySQL, PostgreSQL, Oracle, Teradata, Amazon Redshift, Google BigQuery, Snowflake, Salesforce Data 360, and other Databricks workspaces.

Catalog federation is the second Lakehouse Federation mode: it reads foreign catalogs such as Hive metastore, AWS Glue, or Snowflake directly from object storage on Databricks compute only, which is cheaper and faster than JDBC pushdown.

When a source supports both Lakehouse Federation and Lakeflow Connect, Databricks recommends Lakeflow Connect whenever throughput on large volumes or low latency matters; federation is for ad hoc reporting and proof-of-concept access.

Last updated: August 2026

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:

The data never moves. There is no copy, no schedule, and no storage cost in your lakehouse.

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
  • Action Privilege needed
  • Create the connection CREATE CONNECTION on the metastore
  • Create the foreign catalog from it USE CONNECTION on the connection plus CREATE CATALOG on the metastore
  • Query a foreign table USE CATALOG + USE SCHEMA + SELECT , exactly as for a managed table
  • 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.

  • Query federation Catalog federation
  • Query path Pushed down to the foreign database over JDBC Reads the foreign table directly from object storage
  • Where it runs Partly on Azure Databricks, partly on the remote engine On Azure Databricks compute only
  • Cost/performance Bounded by the source database's capacity More cost-effective and performance-optimized
  • Write support None (read-only) None (read-only)

Governance Unity Catalog foreign catalog, table-level access control Unity Catalog foreign catalog, table-level access control

Best for Ad hoc reporting, BI, proof-of-concept access to operational databases Incremental Unity Catalog migration, or a long-term hybrid where some data stays in an external catalog

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

  • Requirement Correct approach
  • Ad hoc lookup against an operational database, no copy allowed Lakehouse Federation foreign catalog
  • Explore a source before committing to a pipeline Lakehouse Federation
  • Incrementally migrate off a Hive metastore Catalog federation
  • High-volume, low-latency, incremental replication into the lakehouse Lakeflow Connect (Section 7.6)

Historical retention, time travel, and Delta optimization over the source data Ingest it - federation gives you neither

Any write-back to the source Neither - use the Spark Data Source API with JDBC

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 .

Loading diagram... Query Federation Versus Catalog Federation Execution Paths