While data has become a valuable asset for every organization and plays a key role in the decision-making process, the value of analytical workloads has increased significantly as well.

However, many organizations still have legacy data that has not yet been migrated and continues to reside in relational databases such as SQL Server. Bringing this legacy data together with modern data sources and performing analytics across them can provide significant business value. The obvious approach would be to build ETL pipelines to extract, transform, and load the data into a centralized platform.

Databricks offers an alternative approach called Lakehouse Federation, which enables organizations to connect external systems directly to Unity Catalog without physically moving or copying the data.

As a result, organizations can unlock analytical value from legacy and external data sources.

Lakehouse house federation Key points

1. It is query federation platform
   • There are two types of federation provided by databricks catalog federation and query federation. Catalog federation is reading a dataset from external applications like AWS glue, Hive metastore using databricks compute.
   • Where as query federation, access data from relational databases.
2. Governed and read-only with unity catalog through foreign catalogs
3. Runs query against external system using jdbc connection internally
4. Performs push down (with some limitations)
5. Fine grained access at table level

Power of Lakehouse federation

If you’ve ever needed to pull together data scattered across a Delta table, a SQL Server instance, and a PostgreSQL database, you know the usual drill: export from each, land it somewhere central, then finally start your analysis. Databricks Lakehouse Federation is designed to skip that dance entirely — and it handles exactly this kind of multi-system scenario well.

Let’s demo!

Setup needed for the demo

1. Spin up databricks (unity catalog enabled)
2. Spin up SQL server
3. Spin up Postgres SQL server
4. Create Databricks connection and foreign catalog for SQL server
5. Create Databricks connection and foreign catalog for postgres server
6. Prepare Sample dataset in SQL and postgres Server along with databricks delta-table UC managed.

Databricks delta table

What else! Then start querying the table.

Able to analyse the data from different systems without moving the data.

Limitations of lakehouse federation

1. Every query against a foreign Catalog hit live SQL server which probably a live production OLTP bring downs the performance of production application.
2. Federation queries are limited by source engine speed and don’t use any databricks performance features like photon, z-ordering, liquid clustering
3. Federation queries don’t have time travel capability

References

https://www.databricks.com/blog/introducing-lakehouse-federation-capabilities-unity-catalog

https://learn.microsoft.com/en-us/azure/databricks/query-federation

Happy learning !!