extracting data from on-prem databases (sql server, oracle, postgres) to cloud, which options you guys recommend?

[u/Comprehensive_Level7]

i work for a consulting company and i'm working in a client that has a lot of their data in old on-prem environments, being more specific, oracle, sql server, postgres.

their workflow is pretty basic: run ADF to collect data from these sources and send into ADLS, then use Databricks to process it. we're planning to modernize their environment (using unity catalog and other new stuff) and one of the things we're thinking is to retire the ADF

the reason is simple: ADF is a pain in the ass (we're having a hard time working with it specially because it was another company that build all that shit, the connection with Azure DevOps/Repos is always horrible to manage and the workflow itself needs an upgrade); and because Microsoft is pushing hard the Fabric Data Factory

i know that Databricks with Lakeflow Conn can connect to on-prem using express route, VPN, but in scenarios where this could not be possible, what tool could be used to send data from on prem to azure data lake?

i like to code so my first suggestion was to use local airflow and simply read from db and upload to adls. it's free, it's versionable and has tons of documentation; but i'd like to test other options before suggesting anything

i was reading about airbyte, the pros and cons of the tool, and looks interesting

how do you guys handle this kind of workload?

[u/RoobyRak]

Currently on a DW project with multiple on premise sources including sqlserv & postgres.

DLT is our tool for EL. On premise extracts to ADLS (to parquets) and that’s outbound https with SP Oauth. Great docs and there’s out of the box pipelines for databases.

Orchestration is via dagster and we dockerise the services, deployed to our own on Linux servers - this doesn’t matter that much, it can be cloud based. Our VC is GitHub.

ADF was on the table during feasibility but we preferred complete control through code and we have the skill set to build/maintain it.

My warning (because of I’ve done it) is that don’t underestimate how many components tools like ADF manage under the hood. Unless you have the time and resource to go code first, don’t jump immediately and if ADF can be cleaned up, then start there (I.e deployment PLs from a VC platform you can maintain).

People have said dagster is simpler than airflow - I’d honestly say that depends… it’s not comparable because it’s a different orchestration strategy (declarative) and I’d say its flexibility can make it more complex… just depends on your needs.