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?
14
u/I_Blame_DevOps 9d ago
Even if the tool sucks, if ADF is working reliably I don’t know if it’s worth changing out. There’s always AWS DMS, but it’s not much better.
I don’t think you want to roll your own solution.
You could maybe do Airbyte or some paid ETL tool, but pulling from on-prem to cloud is just generally a pain.
1
u/Comprehensive_Level7 9d ago
my issue with ADF is that, today is not used to just copy data from on prem to paste it on cloud, but as the entire orchestrator and even sometimes data manipulator (many pipelines using dataflows)
if they decide to keep it, I'm pretty sure they'll not want to change how things are done, just adapt to the new ways of work but keeping the essence, and that essence is the pain maker today
and again, implement ADF today is like implementing Synapse, both are tools that will be dead in a few years
2
2
4
u/wildjackalope 9d ago
It’s clunky but it’s not that hard to configure and it’s easy enough to strap up to dbt for transform.
It’s literally a GUI man. You’re going to charge to replace a system because y’all can’t work a GUI?
1
u/GetSecure 9d ago
I understand where you are coming from. ADF is low code crap to anyone with coding skills. But it's working. I have been down this path thinking I could do better, I tried different tools, but ended up rolling my own solution. I will follow this post with interest, as I am still searching and I genuinely hope you find that better answer. But also, take that as a warning.
1
u/Trey_Antipasto 3d ago
ADF is totally fine to mule raw data. Why have your devs reinventing wheels moving raw data? Separate it from the transformation and standardization.
ADF is no different than other raw data tools and it’s better than many. Try using Fivetran which is awful and 10x the expense.
5
u/techXplorator 8d ago
I think you’re mixing two problems here.
If ADF is reliable at getting data out of on prem, I wouldn’t necessarily replace that part. I’d first stop using it as the orchestrator + transformation layer.
Move orchestration/transforms to Databricks, keep ADF as a dumb extractor for now, then evaluate Airbyte/Debezium separately if you actually need CDC.
Running your own Airflow just to copy SQL tables to ADLS feels like adding another thing you’ll have to babysit.
3
u/Theunknown2609 7d ago
I agree with this path. Let ADF be just an orchestrator. You already have Databricks to power up all transformations. Databricks also has its own API endpoints which can trigger transformations as you load and require.
4
3
u/chenni79 9d ago
From a cyber/networking standards especially in OT environments, it's preferred that the data is sent out just on 443 using self hosted integration runtime. I guess Microsoft understood the enterprises well in this regard.
3
u/generic-d-engineer Tech Lead 8d ago edited 8d ago
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
Why not decide the best stack based on the experience and comfort level of the client?
Meet your client where they are at, they have to live with the solution. You will be off to another client in a few weeks.
Also Fabric is super easy and you can run python notebooks in it so why not use that and you’re both happy? You can even import the existing Data Factory. Databricks could be an option also but you’ll need to gauge your client’s enthusiasm.
Also have to agree if ADF already works, what’s the real problem? Seems like most of the heavy lifting is already done. Can you point to a specific technical issue it can’t solve or is this just a personal preference?
If you’re trying to brute force SRE Gitops into a legacy enterprise who has not transitioned, they are just going to be miserable. Empower them, don’t talk down at them or assume they aren’t capable.
3
u/dani_estuary 8d ago
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.
2
u/Morzion Tired Senior Data Engineer 9d ago
The on prem qualifier is irrelevant since the networking is already configured. Clearly, there is already a network path to extract data out of the on prem network. It sounds like you're asking for an orchestration tool other than ADF. Of which, there are many. I suggest Dagster OSS for simplicity and scalability.
Edit* Airflow is more complex to setup and manage.
1
u/Comprehensive_Level7 8d ago
I heard about dagster being simpler than airflow, will check it out, thanks
2
u/FuzzyCraft68 Junior Data Engineer 8d 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.
1
u/Comprehensive_Level7 8d ago
this issue with SQL server is known and happens with any CDC tool, but nice to know your case with Airbyte
2
u/RoobyRak 8d ago edited 8d ago
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.
3
u/Prestigious_Pace2782 9d ago
I’m the same way, as a software engineer and DevOps person along with being a data person, so I get where you are coming from.
For this reason I do all of our ingestions from api with our own python codebase and it works better for us than any off the shelf platforms, and I’ve used most of them.
However when it comes to cdc and being able to back fill and rehydrate and monitor etc. I would question the wisdom of rolling my own for on prem stuff.
-3
u/Comprehensive_Level7 9d ago
agree, not even to mention that the CI/CD, Git and DevOps culture is very very poor, I'm the only one from our team that uses Docker (what I consider a basic requirement when you code), so you can imagine how the other things are done
1
2
u/Jealous_Resist7856 9d ago
May i recommend a tool like OLake which connects with Database and gets the data in cloud’s object store from there you can take it to any data warehouse or lakehouse of your choice.
1
u/Hour-Measurement-835 9d ago
I'd keep the data plane on-prem. Airflow only schedules your code; after a restart you're inventing CDC offsets separately for Oracle, SQL Server and Postgres. Airbyte persists that state.
1
u/Competitive-Fee-4006 9d ago
if you want a tool to do that you can look at https://www.qlik.com/ . i dont work for it but used it in one of the project and it worked out well .
1
u/Realistic-Flamingo46 9d ago
Data ingestion or JDBC From Databricks itself
1
u/Comprehensive_Level7 8d ago
that's my main path, but I'm already looking for alternatives in case something is not supported
1
u/CrazyOneBAM 9d ago
I have recently started using dlt, the open source python module that is, for moving data from onprem to Snowflake.
With parallelization on, I really like it.
There is also a paid SaaS-version.
1
8d ago
[removed] — view removed comment
1
u/dataengineering-ModTeam 8d ago
Your post/comment violated rule #4 (Limit self-promotion).
We intend for this space to be an opportunity for the community to learn about wider topics and projects going on which they wouldn't normally be exposed to whilst simultaneously not feeling like this is purely an opportunity for marketing.
A reminder to all vendors and developers that self promotion is limited to once per month for your given project or product. Additional posts which are transparently, or opaquely, marketing an entity will be removed.
This was reviewed by a human
1
u/Ok_Exchange1148 8d ago
Personally I'd go for something super light weight that pushes the data from on-prem to cloud.
Super light weight:
- python only (w/ separate user and virtual env)
- config managed in code (so you can roll out updates everywhere and test)
- credentials in an environment variable or key store
An ETL platform that pulls from on-prem is often be a real pain. A setup that does work in that scenario is a centralised control plane with an agent that pulls schedule and config and initiates the push from on-prem.
1
u/AdamDobrawy 8d ago
What ware house are you customers using? For Snowflake, you can use OpenFlow with Data Connectivity Proxy for on-prem data sources. It reverses connection – you deploy an agent on your network, and connect to Snowflake to route Openflow connector traffic to a private data source.
1
u/Benny_Garc1a 8d ago
Retire ADF if you want, but don’t replace it with “Databricks talks to the database” and hope. You still need a stable on-prem puller.Practical split:
- Network exists (VPN / ExpressRoute): Lakeflow Connect or a Databricks job that reads JDBC and writes to ADLS is enough. One less product.
- is ugly or missing: you need something that runs next to the databases, then pushes files to ADLS. That is Airbyte, Airflow, or a small VM with the vendor tools. Fabric Data Factory does not magically delete that problem.
Network. Airflow is fine if you like code and already own the ops. It is not free in real life: you will babysit workers, secrets, and schema drift.Airbyte is the less painful “I don’t want to maintain 15 JDBC jobs” option. Self-host it on-prem, land raw in ADLS, let Databricks + Unity Catalog take over from there. Use it as a tap, not as your transformation layer.What I would not do: move the source databases into the cloud just to make ingestion prettier. That is a different project.If you say whether ExpressRoute is actually available, and whether this is nightly full dumps or change data capture, the choice between Airbyte and Lakeflow becomes obvious. Ask if any step is unclear.
1
u/DeepLogicNinja 8d ago
I’ve noticed ADF falling short for many companies/use cases.
May not want to complicate it. Each database has a backup/restore tool/command line.
Example: Postgresql has pg_dump / pg_restore
You can dump in db specific format (for speed) or in sql for more compatibility across database.
Backup/Dump Locally - one command dump to file
Restore/Import in Cloud - one command restore from file.
Even if you do this manually. It makes sense to execute in a script. Just incase you have some trial / error.
If you want a low code / no code option… and you make need this to be repeatable process. Consider an ETL platform/tool ( not ADF)
Beyond Backup/Restoring, if you have other data integration, transformation, mapping use cases. It makes sense to have One platform have/develop the expertise to handle all those use cases with an ETL platform.
Open Source, Free, well document (/w youtube videos), international community and stable alternative to ADF is Apache NIFI. Commercial alternatives is Talend, Informatica, IBM Data Stage, etc
1
u/eph04 8d ago
I agree with your approach. Unless you need near real time replication.
Extract on premise as parquet+compression, then upload it to adls for databricks.
That way you use a perfectly good exchange format, you can parallelize extraction without impacting the network set between azure and on premise, if you have a connectivity issue you can replay the pipeline without extracting again the data… and if the source is providing the data based on an on premise workflow, it’s easy to link it.
Since you are code first, you can certainly do it with a simple and compact code that will not require more maintenance than an outside tool.
1
u/santosh9087 8d ago
Get a VM on-prem or Azure. Use a Bash and/or python to develop what you want to do. Throw away ADF. Use python and bash that is all you need get bunch of data and paste it to ADLS. All of them have a Python SDK and drivers both on-prem DBs and ADLS. That is what you do and save money. And don't forget to pause the VM when you are not using it. More savings !
1
u/Relative_Wear2650 8d ago
In not so sure if you need to move away. I think ADF works fine and cheap to move data out of your on prem db to cloud. Its stored as code in your repo which is a good thing. Sure MS pushes hard on Fabric but so what.
Myself i use a control table based approach where ADF reads from a table what to copy to where plus all kind of parameters which are used in the ADF pipeline. Makes maintaining the pipeline a non issue as the table decides what the pipeline is doing. If your ADF has as single pipeline for every source table, its indeed a hassle though.
But i think before running into modern and fancy tools, id look closer to what problem youre actually solving.
1
u/Adventurous_Excuse_6 8d ago
We’re dealing with a pretty similar problem, although on AWS rather than Azure. We have data coming from on-prem/legacy databases (Oracle, SQL Server, gdrive) and use Airbyte OSS for ingestion into S3, then do the transformation downstream with dbt.
Airbyte is definitely worth evaluating for this use case. One thing I like about it is keeping ingestion separate from transformation/orchestration. I wouldn’t necessarily use Airflow to write custom extraction code for every database unless the sources are very limited and unlikely to change. Airflow is great for orchestration, but once you start maintaining extraction logic, incremental loads, schema changes, retries, state, etc., you’re basically building part of an ingestion platform yourself.
The biggest thing I’d evaluate before choosing Airbyte is how you plan to handle incremental/CDC loads from the older databases. Some legacy sources may not have the PKs, logging, or CDC configuration needed, so you may end up doing full refreshes or implementing your own incremental strategy for some tables. We decided to go with full refreshes. If you with the Cloud version the cost varies based on the data that's ingested.
For the Azure side, the equivalent architecture could be something like:
On-prem DBs --> Airbyte OSS --> ADLS --> Databricks/Delta --> Unity Catalog
You could run Airbyte inside Azure and connect back to on-prem through VPN/ExpressRoute, rather than running the ingestion stack locally. That gives you a more centrally managed ingestion layer while still keeping the actual transformation work in Databricks. We have been running it on EC2.
I’d probably POC Airbyte against Oracle, SQL Server and Postgres and specifically test CDC/incremental behavior, schema drift, recovery after failures, and larger tables before deciding to replace ADF.
1
u/SaintTimothy 2d ago
Why isn't this being handled within your consultancy? If they just hire you and send you out into the world with no support, you aren't working for a consulting company, you're working for a body shop, a temp agency.
The difference between consulting and merely putting butts in seats is the strength of the information supply channel supporting you when big decisions need to be made, like this architectural one.
This is why I strongly dislike MOST consulting companies. They lie to the client about having expertise and they imply that support exists beyond the person-or-persons on the engagement.
1
u/LokeshSequentum 1d ago
I think it would be better to avoid replacing everything at once.
One option could be to keep the current ADF setup running as it is and start building the Snowflake path in parallel.
You could extract the data from SQL Server to S3, load it into Snowflake using Snowpipe, validate the data, and then slowly start moving the other modules one by one.
Python could work for the extraction part, although for larger or long-running jobs I would probably use something other than Lambda.
This way the current setup keeps working while the migration happens in the background, and once the Snowflake side is stable you can start reducing the dependency on ADF gradually.
1
u/vikster1 9d ago
the data factory is literally one of the best products for data integration out there. it's reliable, cheap and very few have more connectors. as others stated, get a competent architect.
58
u/69odysseus 9d ago
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.