Unlocking Investment Insights Without Migrating Your Data: Lakehouse Federation

Unlocking Investment Insights Without Migrating Your Data: Lakehouse Federation | Databricks Blog Skip to main content

Summary

Federate, don't copy: Databricks Lakehouse Federation queries on-premise databases directly from Databricks with standard SQL. The data never moves.

Subscribe, don't build: FactSet financial data from the Databricks Marketplace appears as a native catalog. No ingestion pipeline required.

One query, full picture: A single SQL join across both catalogs powers dashboards and Genie natural-language queries, with zero persisted intermediate objects providing rich portfolio and investment information.

Lakehouse Federation: Query Anywhere, Move Nothing In today's enterprise landscape, data rarely sits in one place. Financial institutions, asset managers, and individual investors face a common challenge: their most sensitive data including portfolio holdings, trading positions, and client information often resides in on-premises databases behind corporate firewalls, in the cloud, or across both environments. That challenge is compounded by the fact that the third-party data they need for analysis, including market data, analyst estimates, and financial fundamentals increasingly lives in the cloud. Traditional approaches force an uncomfortable choice: copy sensitive data to the cloud, stand up brittle ETL pipelines, or give up on integrating the two altogether. What if you could just federate the on-premises source, subscribe to the third-party data on the cloud, and go straight from those two catalogs to a dashboard and a natural-language chat, with no pipeline, no materialized views, and no persisted objects in between? That's Databricks Lakehouse Federation at its simplest. The Solution: Query Anywhere, Move Nothing, Build Nothing Databricks Lakehouse Federation lets you create federated connections to external databases like SQL Server, PostgreSQL, MySQL, Snowflake, and more, and query them directly from Databricks using standard SQL. The data stays exactly where it is. No copying. No syncing. No compliance headaches. Add a marketplace data share on top of it, and you have two catalogs sitting side by side in Unity Catalog - one federated, one shared, that a plain SQL query can join like they were any two ordinary tables.

A Real-World Example: Client Portfolio Analysis Let's walk through a concrete example. Imagine you're a wealth management firm with: First-party data : Your portfolio holdings - which stocks you own and how many shares, stored in an on-premises SQL Server database. Third-party data : FactSet financial data from the Databricks Marketplace, including earnings fundamentals and analyst estimates.

Your goal: Build an investment dashboard that shows your projected earnings, identifies high-risk positions, and recommends actions, all without exposing your holdings data to the cloud. Step 1: Set Up Access to Portfolio Data and FactSet Data Step 1a: Set Up the Federated Connection Setting up federation is straightforward through the Databricks UI. Navigate to Catalog → External Data → Connections and create a new connection to your on-premises database.

The connection securely stores your credentials. Step 1b: Create a Foreign Catalog Once the connection is established, create a foreign catalog that maps to your on-premises database. This makes your external tables appear as native Unity Catalog objects.

After this step, your on-premises data appears as a catalog, but the data never leaves your SQL Server. Every query executes against the source in real time.

Step 1c: Subscribe to FactSet Data from the Marketplace The Databricks Marketplace provides instant access to FactSet's comprehensive financial data. Search for FactSet , review the available datasets, and request access. Once the provider approves your request, you can subscribe to the relevant datasets.

After subscribing, the data is available as a shared catalog in your workspace, organized into schemas: fe_v4 - FactSet Estimates: analyst consensus EPS forecasts, earnings projections, and estimate counts ff_v3 - FactSet Fundamentals: annual financial statements including revenue, net income, profit margins, and debt levels sym_v1 - Symbology: ticker-to-entity mappings that connect your portfolio symbols to FactSet's internal identifiers

These tables appear alongside your federated portfolio catalog in Unity Catalog. You can browse them, preview data, and check schemas, all without any ingestion or data copying. Step 2: Explore the Combined Data Step 2a: Explore the combined data with SQL With both data sources accessible through Unity Catalog, an asset manager can open the SQL editor and write one query that spans both the federated holdings on one side, the FactSet marketplace tables on the other. To the SQL author they look like two ordinary tables in the same warehouse.

There is no pipeline to author, no materialized view to schedule, and no gold layer to maintain. Every time the query runs it re-federates the on-premises source live and re-joins it against the marketplace share. Step 3: Consume the Data Step 3a: Create the Investment Dashboard The dashboard is powered by the same combined query inlined directly into each widget's dataset. No views are created and no tables are persisted. Every widget re-federates the source on each refresh. It instantly answers questions like: Portfolio health : total holdings, current annual earnings, projected annual earnings, high-risk count Risk distribution : what share of the book is high / medium / low risk? Investment recommendations : how many stocks should I buy more of vs. sell? Top performers : which holdings are generating the most earnings and how do current earnings compare to projected earnings? Full holdings table : per-stock earnings, growth %, margin %, D/E, risk, and action recommendation.

Step 3b: Enable Natural Language Queries with Genie The final piece: a Genie agent wired directly to the same federated holdings and FactSet marketplace tables. Genie receives the join chain and data-quality filters in its instructions. It translates natural language into the same queries the dashboard runs. The result: democratized data access. Portfolio managers, financial advisors, and individual investors can all get answers without writing SQL, and without anyone materializing anything.

The Complete Picture Databricks Lakehouse Federation fundamentally changes the economics of hybrid...