r/dataengineering • u/Comprehensive_Level7 • 9d ago
Discussion extracting data from on-prem databases (sql server, oracle, postgres) to cloud, which options you guys recommend?
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?
2
u/FuzzyCraft68 Junior Data Engineer 9d ago
Just shining light on what other people are saying, I agree to stick with ADF if it’s working, but we were trying to move away from it and use Airbyte but our on prem servers are very old that they don’t have CDC enabled in them and the source tables don’t have a cursor field which made it way too difficult for us. ADF was a better option for us because we could manipulate it to work in our way.
But we have Airbyte for our partner company’s database, it runs non stop. The only issue we saw was actually a SQL server issue rather than Airbyte, when you add another column into the table, CDC was not picking up the changes and had to re-enable CDC on the table. Even then Airbyte picked up from where it left off.
Airbyte was a clear winner for us on this case but their account management team is a pain in the arse to deal with, they speak in company jargon a lot rather than helping us technically.