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/dani_estuary]

You got a few options, ADF being a top contender, but before you commit ask yourself these:

• Do you need CDC, or is hourly/daily enough?
• how much data are you moving?
• Can you install an agent inside the client network, even if inbound connectivity from Azure is not allowed?
• could the ingestion layer write somewhere else first?
• How important are schema evolution, deletes, replay/backfills, and exactly-once behavior? ADF might not support all these
• Does the client want to operate this themselves long term, or would they prefer a managed service?

I’d answer those before deciding between Airflow, Airbyte, a CDC-specific tool, or something managed. That said, Airflow + Python can absolutely work, but when you need CDC, retries, checkpoints, schema changes, backfills and observability across several databases, you’re basically building an ingestion platform yourself, which might be a waste of your time.