Lakehouse Federation

Stop migrating data to Databricks: Are you using Lakehouse Federation yet?

submitted by /u/SalamanderOpen859 [link] [comments]

Stop migrating data to Databricks: Are you using Lakehouse Federation yet?

I’ve been diving deep into Unity Catalog lately, and the "Lakehouse Federation" feature is genuinely a game-changer for reducing ETL bloat. For a long time, the default answer to "how do I analyze this external data?" was "copy it into the lake." We’re finally moving away from that. Here is the breakdown for anyone trying to cut down on unnecessary data movement: The two ways to federate: Catalog Federation (The "Data-Centric" approach): How it works: Connects Databricks to external metadata (AWS Glue, Hive, Snowflake Iceberg). The Magic: Databricks compute does the heavy lifting (reading raw files in S3/ADLS). It’s fast, low-cost, and creates a "live" bridge to your storage. Best for: Incremental migrations and long-term hybrid storage. Query Federation (The "Database-Centric" approach): How it works: Pushes SQL via JDBC to remote databases (Postgres, MySQL, SQL Server, Synapse, etc.). The Magic: The remote DB does the compute. You get the result set back without touching the raw underlying tables. Best for: Ad-hoc reporting against operational/production databases where you don't want to break their performance with heavy ETL loads. Why it’s actually useful: Centralized Security: Unity Catalog acts as the single security guard. You don't need to manage separate credentials for your users on the remote DB. Automatic Lineage: This is the biggest win for me—Databricks automatically maps the lineage from the remote system to your report. No more "mystery data." Unified Syntax: You query everything using catalog.schema.table regardless of whether it’s sitting in Databricks, Snowflake, or an on-prem SQL Server. submitted by /u/SalamanderOpen859 [link] [comments]

Lakehouse Federation (Snowflake) — large query results fail to download from internal stage

How to Deploy Lakehouse Federation Using DABs from the Dev Environment to the Stage Environment

When Lakehouse Federation "loses" your tables: the silent space-in-name trap

Tutorial: joining Lakebase OLTP and Unity Catalog Delta in one federated SQL query, with a text-to-SQL agent

I wanted to answer one natural-language question that crosses two stores: "Show me orders from high-value customers that shipped last week." The order status is live in Postgres (OLTP). The customer-segment definition lives in a Delta gold table (OLAP). For years this was a two-system query — pull from one, dump to CSV, join in pandas, regret your life. With Lakebase (managed Postgres) sitting in the same Databricks workspace as Unity Catalog, Lakehouse Federation makes that a single SQL statement. And once it's a single statement, you can put a text-to-SQL agent on top of it and stop writing the query by hand. This post walks the whole thing end-to-end: data setup, federation wiring, the agent pipeline, the safety guardrail, and three swappable LLM backends (Gemini via Google AI Studio, GPT-4.1 / o4-mini via GitHub Models, and self-hosted vLLM on a Shadeform H100). The data setup I used the Olist Brazilian e-commerce dataset (Kaggle, \~100MB). Loaded twice on purpose: data/raw/\*.csv (customers, orders, items, products, payments) │ ├─► load\_oltp.py ─────► Lakebase Postgres │ (5 normalized tables — the live source-of-truth) │ └─► data/processed/\*.csv (cleaned, dedup'd) │ └─► UC Volume → build\_olap.py ─► Unity Catalog Delta (gold aggregates: customer\_segments, category\_performance, revenue\_aggregates) The OLTP side is what your app would write to in production. The OLAP side is what your analytics jobs would build nightly. We want to query both from the same agent without it knowing or caring which is which. Wiring Lakehouse Federation Three steps from a clean workspace. * Create the connection in Unity Catalog. `CREATE CONNECTION lakebase_olist` `TYPE postgresql` `OPTIONS (` `host 'YOUR-LAKEBASE-INSTANCE.database.cloud.databricks.com',` `port '5432',` `user 'chandank@becloudready.com',` `password secret('lakebase', 'pat-token'),` `trustServerCertificate 'true'` `);` * Create the foreign catalog. This is where the database option lives — not on the connection. (This trips up most people first time, including me.)`CREATE FOREIGN CATALOG lakebase_olistUSING CONNECTION lakebase_olistOPTIONS (database 'olist');` I wanted to answer one natural-language question that crosses two stores: The order status is live in Postgres (OLTP). The customer-segment definition lives in a Delta gold table (OLAP). For years this was a two-system query — pull from one, dump to CSV, join in pandas, regret your life. With Lakebase (managed Postgres) sitting in the same Databricks workspace as Unity Catalog, Lakehouse Federation makes that a single SQL statement. And once it’s a single statement, you can put a text-to-SQL agent on top of it and stop writing the query by hand. This post walks the whole thing end-to-end: data setup, federation wiring, the agent pipeline, the safety guardrail, and three swappable LLM backends (Gemini via Google AI Studio, GPT-4.1 / o4-mini via GitHub Models, and self-hosted vLLM on a Shadeform H100). # The data setup I used the Olist Brazilian e-commerce dataset (Kaggle, \~100MB). Loaded twice on purpose: data/raw/*.csv (customers, orders, items, products, payments) │ ├─► load_oltp.py ─────► Lakebase Postgres │ (5 normalized tables — the live source-of-truth) │ └─► data/processed/*.csv (cleaned, dedup'd) │ └─► UC Volume → build_olap.py ─► Unity Catalog Delta (gold aggregates: customer_segments, category_performance, revenue_aggregates) The OLTP side is what your app would write to in production. The OLAP side is what your analytics jobs would build nightly. We want to query both from the same agent without it knowing or caring which is which. # Wiring […truncated]

How would you onboard legacy data stores that don't use OAuth into Databricks Unity Catalog?

Lakehouse federation typically uses OAuth, this the above qn.