ExamFinch.com My Study

Study guide

5.2 Lakehouse Federation

Practice this exam

Databricks Certified Data Engineer Professional Exam

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Developing Code for Data Processing using Python and SQL - Core Spark SQL & PySpark

1.1 Complex PySpark Transformations & DataFrame API 1.2 Advanced Spark SQL Patterns 1.3 Streaming Workloads with Structured Streaming 1.4 Lakeflow & Delta Live Tables (DLT) Declarative Pipelines

2 Chapter 2: Developing Code for Data Processing using Python and SQL - Streaming & Advanced Patterns

2.1 Advanced Delta Lake Features 2.2 Custom & Stateful Streaming Operations 2.3 Python & PySpark Code Modularization 2.4 User-Defined Functions (UDFs) & Performance Impact

3 Chapter 3: Data Ingestion & Acquisition

3.1 Auto Loader (cloudFiles) Production Ingestion 3.2 Change Data Capture (CDC) Ingestion Patterns 3.3 Batch & Streaming Connectors

4 Chapter 4: Data Transformation, Cleansing, and Quality

4.1 Data Validation & Quality Frameworks 4.2 Data Cleansing, Deduplication & Standardization 4.3 Silver and Gold Medallion Layer Refinement 4.4 Handling Schema Drift and Evolution

5 Chapter 5: Data Sharing and Federation

5.1 Delta Sharing Architecture & Governance 5.2 Lakehouse Federation

6 Chapter 6: Monitoring and Alerting & Observability

6.1 System Tables & Observability 6.2 Databricks Workflows & Job Monitoring 6.3 Spark UI & Query Execution Diagnostics 6.4 Data Quality & Pipeline Alerting

7 Chapter 7: Cost & Performance Optimisation - Data Layout & Compute

7.1 Data Storage & Layout Optimization 7.2 Compute Choice & Cluster Sizing 7.3 Photon Engine & Query Acceleration

8 Chapter 8: Cost & Performance Optimisation - Skew & Cost Governance

8.1 Partitioning & Data Skew Remediation 8.2 Cost Governance & DBU Optimization

9 Chapter 9: Ensuring Data Security and Compliance

9.1 Unity Catalog Access Control & Least Privilege 9.2 Dynamic Data Masking & Row/Column-Level Security 9.3 Secrets Management & Network Security 9.4 Compliance, Audit & Data Privacy

10 Chapter 10: Data Governance in Unity Catalog

10.1 Unity Catalog Governance Architecture 10.2 Data Lineage & Tagging in Unity Catalog 10.3 Managed vs External Tables & Storage Governance

11 Chapter 11: Debugging and Deploying - Asset Bundles & CI/CD

11.1 Databricks Asset Bundles (DABs) & Infrastructure as Code 11.2 CI/CD Automation & Databricks CLI / REST API 11.3 Troubleshooting Pipeline & Job Failures 11.4 Code Versioning & Databricks Git Integration

12 Chapter 12: Data Modelling & Lakehouse Analytics

12.1 Medallion Architecture Design Patterns 12.2 Dimensional Modeling in the Lakehouse 12.3 Analytics & BI Optimization

5.2 Lakehouse Federation

Key Takeaways Explain This Topic AI

Lakehouse Federation allows you to query external databases directly from Databricks without requiring data replication or complex ETL pipelines.

Setting up federation requires creating a Foreign Connection to manage credentials and a Foreign Catalog to expose the external database within Unity Catalog.

Query optimization features like predicate pushdown improve performance by sending filters and aggregations to the external system, minimizing data transfer.

Federation is ideal for ad-hoc queries and prototyping, but high-volume repetitive analytics usually perform better if the data is ingested into Delta Lake.

Supported foreign data sources include PostgreSQL, MySQL, Snowflake, Redshift, and BigQuery, with security managed centrally via Unity Catalog.

Last updated: July 2026

5.2 Lakehouse Federation

In the complex landscape of modern enterprise architectures, organizational data is almost never confined to a single system. Instead, it is typically scattered across a vast array of multiple operational databases, enterprise data warehouses, SaaS applications, and diverse cloud environments. Traditionally, unlocking the value of this siloed data for comprehensive analysis required data engineering teams to build, maintain, and monitor complex Extract, Transform, Load (ETL) or Extract, Load, Transform (ELT) pipelines. These pipelines were necessary to physically copy the data from its source systems into a central data lake or data warehouse before it could be queried by analysts. This traditional approach is fraught with challenges: it introduces significant data latency, increases infrastructure and storage costs, creates complex dependencies, and expands the attack surface for potential security vulnerabilities.

Lakehouse Federation in Databricks fundamentally solves this pervasive problem by providing a robust framework that allows you to query external databases directly from your Databricks workspace without ever moving, copying, or replicating the underlying data. This modern approach is frequently referred to as a "zero-copy" integration or data virtualization strategy. By federating data, organizations can drastically reduce time-to-insight and simplify their data architectures while maintaining centralized governance.

Lakehouse Federation Architecture

At its core, Lakehouse Federation integrates external operational systems and data warehouses deeply into the Unity Catalog governance framework. By mapping external databases as virtual catalogs within Databricks, users can query external tables using standard Spark SQL, DataFrame APIs, or Databricks SQL interfaces. This creates a seamless analytical experience where users can join live, external operational data with historical internal Delta tables as if they all resided in the exact same physical storage location.

Databricks currently supports a wide and growing variety of external systems for federation. This expansive list includes popular relational databases such as PostgreSQL, MySQL, and Microsoft SQL Server, as well as enterprise data warehouses like Snowflake, Amazon Redshift, and Google BigQuery. This breadth of support ensures that Lakehouse Federation can act as the central analytical hub for almost any enterprise data ecosystem.

Setting Up Lakehouse Federation

To configure Lakehouse Federation, workspace administrators must establish a secure, authenticated link between the Databricks environment and the target external system. This setup process involves configuring two primary components within Unity Catalog: Foreign Connections and Foreign Catalogs .

1. Creating a Secure Foreign Connection

A Connection is a highly secure Unity Catalog securable object that stores the authentication credentials and necessary network routing details required to communicate with the external database. Unity Catalog encrypts and manages these sensitive credentials internally, ensuring that end-users never see or interact with raw passwords or API keys. -- Create a secure connection to an external PostgreSQL database

  • CREATE CONNECTION pg_prod_db
  • TYPE postgresql
  • OPTIONS (
  • host 'pg-prod.internal.example.com',
  • port '5432',
  • user 'db_reader',
  • password 'secure_password_123'
  • );

Once the connection is established, administrators can utilize standard Unity Catalog GRANT statements to implement strict Role-Based Access Control (RBAC). This allows administrators to precisely dictate which Databricks principals (individual users, groups, or service principals) are permitted to leverage this connection for data access.

2. Creating a Foreign Catalog

After a secure connection is successfully established and validated, the next step is to create a Foreign Catalog . This operation logically exposes the external database's internal schemas and tables, mapping them directly into Unity Catalog's standard three-level namespace architecture ( catalog.schema.table ). -- Create a foreign catalog utilizing the previously defined connection

  • CREATE FOREIGN CATALOG erp_system
  • USING CONNECTION pg_prod_db
  • OPTIONS (
  • database 'erp_production'
  • );

Immediately after executing this command, authorized users can browse the erp_system catalog visually within the Databricks Data Explorer. More importantly, they can execute complex queries against the remote PostgreSQL tables utilizing standard SQL syntax, treating them exactly as if they were native Delta Lake tables stored in cloud object storage: -- Querying federated data and joining it with native Delta tables

  • SELECT
  • ext.user_id,
  • ext.order_total,
  • int.customer_segment
  • FROM erp_system.public.orders AS ext
  • JOIN main.customer_data.profiles AS int
  • ON ext.user_id = int.user_id
  • WHERE ext.order_date >= '2026-01-01';
  • Query Optimization and Predicate Pushdown

A primary and very valid concern when querying massive external databases over a network is the potential cost and latency associated with transferring large volumes of data. Databricks mitigates this risk intelligently through an advanced optimization technique known as Query Pushdown , and more specifically, Predicate Pushdown .

When a user executes a query against a foreign catalog, the Databricks Catalyst optimizer steps in before execution begins. The optimizer deeply analyzes the logical SQL statement and determines how much of the computational workload can be pushed down to the external source system.

For instance, if your query includes a WHERE clause for filtering, a GROUP BY clause for aggregation, or a LIMIT statement to constrain results, Databricks will dynamically rewrite that portion of the query into the native SQL dialect of the target external system (such as Snowflake SQL or PostgreSQL dialect). The external database engine then executes the filtering and aggregation locally using its own compute resources. Consequently, only the heavily reduced, final result set is transferred back across the network to the Databricks cluster for final processing or display.

This intelligent optimization drastically minimizes data egress costs, reduces network congestion, and significantly improves overall query latency and performance.

Performance Tradeoffs: Federation vs. Materialized Ingestion

While Lakehouse Federation is incredibly powerful and convenient, it is not a silver bullet for every analytical workload. Experienced data engineers must carefully evaluate the tradeoffs between virtually federating data and physically materializing (ingesting) it into optimized Delta tables.

Optimal Scenarios for Data Federation:

Ad-hoc Analysis and Discovery: When analysts need to occasionally query a table for exploratory data analysis without waiting days or weeks for data engineering teams to build formal ETL pipelines.

Rapid Prototyping: When constructing a proof-of-concept pipeline, federation allows for extremely rapid iteration and validation of business logic.

Strict Freshness Requirements: When downstream applications or queries require sub-second, real-time access to operational data to reflect the absolute current state of an OLTP database.

Compliance and Data Residency Constraints: When regulatory requirements dictate that sensitive data must physically remain localized within a specific system, geographic region, or sovereign cloud environment.

Optimal Scenarios for Data Ingestion (Delta Lake):

Heavy Analytical and Reporting Workloads: If a specific dataset is queried repeatedly by highly concurrent BI dashboards or large-scale scheduled jobs, relying on federation will cause redundant network overhead and potentially overwhelm the external operational database with analytical queries.

Highly Complex Transformations: For operations involving massive distributed joins, complex window functions, or intensive machine learning model training, physically ingesting data into the Parquet-backed Delta format allows organizations to take full advantage of Databricks' vectorized Photon engine and highly optimized cloud object storage.

Historical Auditing and Versioning: Delta Lake provides native capabilities for time travel and historical data versioning, critical features which most traditional operational databases do not support natively for massive datasets.

In most mature production architectures, organizations implement a hybrid approach. Lakehouse Federation provides agile, immediate access to operational tables for ad-hoc queries, while Materialized Views in Databricks are utilized to periodically ingest, optimize, and cache the most heavily queried subsets of that federated data, striking the perfect balance between agility and performance.