Hand-coding SQL for Data Integration? Not Cool!
Case study underscores the long-term advantages of open-source ETL tools.
I attend a lot of conferences pertaining to data integration, so I’ve seen a certain scenario unfold several times. The keynote speaker asks the audience for a show of hands: “How many of you are using ETL tools?” A smattering of hands go up. “How many of you are still hand-coding SQL scripts?” A sea of hands.
Let me start by saying this: I get it. The allure of hand-coding SQL scripts for data integration can be overpowering. It’s fast. It’s cheap. It’s easy. It solves a problem immediately. And there’s something very satisfying about having the skill to do it. That’s why despite the advances in ETL tools, and despite the fact that a broad spectrum of ETL tools are now available from multiple sources at multiple price points—both commercial and open source—many IT professionals and developers still prefer hand-coding SQL scripts for data integration.
It’s similar to the way people assess their options when they’re experiencing knee pain. Would you prefer to go to the physical therapist, get a detailed assessment of why your knee is hurting, learn new ways of walking so that it won’t hurt in the future, and foot the bill for all of that? Or would you prefer to pop a pill that makes the pain go away right now?
Like the pill, however, hand-coding may become habit-forming and create unforeseen long-term consequences. All too often, what started out as a simple hand-coded SQL script turns into dozens of pages of undocumented, non-compliant scripts that are difficult to repeat, audit, verify, and validate. The result is unexpected issues, delays in running jobs, and extra expense.
Case in Point: Government Agency Moves to Open Source ETL
The recent experience of one of our customers, a non-profit organization serving the court system of a major U.S. state, highlights the advantages both of transitioning to an ETL tool and of paying for the commercial edition during the transition.
In 2016, the organization launched a pilot program to replace their existing SQL scripts and manual data entry processes. The development team at this organization included just a handful of professionals with varying time assignments to the project, which I’m sure our readers never have to deal with :) .
These challenges meant optimizing their efforts was key to meeting the deadline. With very little ETL tools experience, the team selected Talend because the learning process was “very fast” which was critical as the 100+ source tables were going to impact the delivery window. The Net result: it now takes less than three hours to build the same data migration jobs that used to consume more than three days for hand-coded SQL scripts. An added benefit for the team was the ability to the Talend jobs remotely, saving them additional time since they didn’t need to set up the infrastructure.
Did everything about the transition to the Talend ETL solution run flawlessly? No. But surprisingly, the issues made the customer even happier they’d gone with Talend for data integration.
“As a non-profit we always try to save costs, and while we found that Talend’s responses to technical issues were just as timely before we decided to purchase the commercial version, we’re really, really happy we decided to pay in this case,” said the Senior Application Development Analyst on the project. “There was no attempt to hide bugs. Quite the contrary—the community was very collaborative about both identifying bugs, fixing the bugs they found, and documenting the workaround. That meant the issues that impacted the speed and quality of our jobs was minimized—in the short term and over time.”
This came in handy as the project scope expanded from the planned 20 reports to 80, still within the original delivery window of course.
Having chopped the timeframes for data migration jobs from days to just hours, the organization is now focused on improving its infrastructure, adding job diagnostics, logging, and internal standards and guidelines.
Is open-source ETL the perfect fit for everybody? It’s not up to us to make that call, but for those of you that are interested in moving away from manual data entry or custom scripts, take a look at Talend Open Studio. With that I’ll leave you with my opinion: Code is cool. Hand-coding… not so much.