topic Re: Anyone using Databricks Query Federation for ETL purpose ? in Data Engineering

Anyone using Databricks Query Federation for ETL purpose ?

dipanjannet — Wed, 21 May 2025 09:19:49 GMT

Hello All,

We have a use case to fetch data from a SQL Server wherein we have some tables to consume. This is typically a OLTP setup wherein the comes in a regular interval.

Now, as we have Unity Catalog enabled, we are interested in exploring Databricks Query federation to ingest the data into our Storage Account. Please note, we do not jut want to query the SQL Server tables.

We want to do one time full load and then incremental load.

Seeking for a suggestion for this use case. In the databricks documentation it primarily suggesting for below -

Ad hoc reporting.
Proof-of-concept work.
The exploratory phase of new ETL pipelines or reports.
Supporting workloads during incremental migration.

However, in this case - this is a fully flagged ETL workflow. Any suggestion on this setup ?

#Query Federation

Re: Anyone using Databricks Query Federation for ETL purpose ?

nikhilj0421 — Fri, 23 May 2025 16:56:53 GMT

Hi @dipanjannet, you can leverage DLT feature to do so.

Please check: https://docs.databricks.com/aws/en/dlt/transform

https://docs.databricks.com/aws/en/dlt/stateful-processing

Here is the step-by-step tutorial: https://docs.databricks.com/aws/en/dlt/tutorials

In case you do not want to use dlt (and there are reasons not to), you can also check the docs for autoloader and merge notebooks

These 2 do basically the same as dlt but without the extra cost and more control. You have to write more code though.
For ingesting the SQL server data I would use Data Factory, which lands the data onto your bronze layer (adls gen2).
Or use the Azure SQL connector of Databricks, but that will use DLT and is more expensive than ADF but has the ease of use (but less control/visibility).
So you see, many choices.

Re: Anyone using Databricks Query Federation for ETL purpose ?

dipanjannet — Mon, 26 May 2025 03:08:55 GMT

Hello @nikhilj0421 - Thank you for help responding. The question is not about DLT. The Question is what is the use case of Databricks Query Federation? If we plug Query Federation - what are the implications ? What databricks is suggesting for that?

Re: Anyone using Databricks Query Federation for ETL purpose ?

Krishna_S — Sat, 01 Nov 2025 04:16:14 GMT

Hi @dipanjannet

Please don’t use Query Federation for production ETL. Databricks’ own guidance says to ingest with managed Lakeflow connectors for scalable, low-latency pipelines, and use Lakehouse Federation/query federation mainly for ad-hoc analysis, discovery, and PoC/bake-off work—not as your primary ETL engine.

For more information, refer to this documentation here:

https://docs.databricks.com/aws/en/query-federation/database-federation?utm_source=chatgpt.com#query-federation-vs-lakeflow-connect

Here is information on the Lakeflow connector for SQL Server:
https://docs.databricks.com/aws/en/ingestion/lakeflow-connect/sql-server-pipeline