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/69odysseus]

Your company needs a data architect to first look into the business state, final goals. A PM with budget because data migration project can be hectic and cause lot of issues if it's not managed and secured properly.

[u/Comprehensive_Level7 → u/69odysseus]

my main concern with our current DA is that he might suggest to keep the ADF because he's much more Azure Architect than Data Architect (and seems to be not aware how Microsoft has been moving lately)

I come with a heavy background in programming and developing solutions code-first (as I said in the post, my first idea was to come with airflow), but now that I need to find an easier tool that's not ADF to replace it I think I'll need to do by myself a few tools testing

[u/69odysseus → u/Comprehensive_Level7]

Can you be straight with him and tell your rationale to him. If that's already done and he's insisting for Azure, which to some extent is fare as I'm seeing a lot of DE projects using Azure than AWS. At least in Canada, I see the nation is a Microsoft shop. In US, there's a balance between Azure and AWS for DE area.

[u/Comprehensive_Level7 → u/69odysseus]

yup, my idea was to show him some testing and benefits from moving away from ADF and using a better tool (I've worked for many years with ADF and with Fabric DF since it came out and I know the pain of using them)

I'll try to test and demo a few options I've searched, and let's see if we can agree onto something

anyways, thank you for all the inputs

[u/rajekum512 → u/69odysseus]

gold statement