Challenges with the Legacy ETL Platforms
How did we get here? Over time, organizations have a build-up of legacy platforms, often through mergers and acquisitions, or bringing in different tools for specific use cases. Integration of other transformation assets, like SQL packages, adds to the mix.
Why is it a problem? With multiple tools come multiple licenses and exploding costs. The combination of legacy platforms and SQL packages results in complexity and presents challenges for governance and lineage. Also, legacy tools were not built for big data or cloud, which are now part of the ecosystem.
According to Dzone, the traditional ETL process is slow, time-consuming, and resource-intensive.
As traditional, on-premise enterprise data warehouses are moving to cloud data lake environments, organizations see a need for modern ETL platforms like Azure Data Factory and AWS Glue to meet their requirements.
Even if companies do not have multiple ETL tools, there is often a need to upgrade their old platform in order to take advantage of newer capabilities like self-service BI and real-time analytics, which can require migration effort.
The most common reason we see why organizations end up with multiple ETL tools is largely due to mergers and acquisitions, which creates a variety of IT problems for enterprises that can be intensified when legacy platforms like Ab Initio, DataStage, SSIS, etc. are in place.
Cost is one problem area caused by M&A activity that results in having two or more ETL tools because now you are maintaining multiple licenses, providing support for multiple ETL environments, and you are maintaining separate skill sets across the organization.
Data lineage is another challenge because now you have your ETLs spread across two or more separate products. Overall complexity of the environment increases because there are certain features in one product that might be superior to the other, leading to cross-pollination issues.
The ability to efficiently consolidate systems and integrate data can help control costs and reduce complexity while enabling BI and analytics across business units, helping to solve your technology-related M&A challenges.
Converting PL/SQL and T-SQL
It’s not just traditional data integration tools that need to be migrated to more modern ETL tools, we need to look at converting PL/SQL and T-SQL packages, which come with their own set of challenges and limitations.
The most common reasons we see for converting PL/SQL and T-SQL packages to an ETL tool is that for stored procedures it is costly and difficult to maintain the code, and it is nearly impossible to get any lineage from the code or to look at how the code is interacting with other applications.
Also, built-in features like auditing, logging, and metadata that are available in modern ETL tools, like Talend, are completely missing from PL/SQL and T-SQL, which can impact an organization’s ability to adhere to regulatory compliance requirements.
Being able to effectively analyze your PL/SQL or T-SQL and map the code for conversion to a modern ETL tool helps overcome challenges of stored procedures and gain data governance and analytical advantages of newer platforms.
Risk of Migration
While there are many benefits of converting to newer technology, the immense cost of re-writing years of legacy data integrations seems to overshadow any opportunities. Challenges that we commonly see holding back a migration initiative include:
- Time to market and cost of conversion can be significant
- Not clear if the conversion can provide the necessary ROI
- Difficulty not just in re-writing the code, but testing the entire process
- If you have two separate tools, the consolidation effort requires two skill sets
These risks are typical in the traditional approach to migrating ETL and PL/SQL, which includes a manual re-write process using some variation of the following steps:
- Bring in legacy ETL developers for analysis and documentation of legacy ETL mapping
- Document the ETL jobs using Excel templates
- Identify opportunities for improvement and optimizations
- Build a phased conversion plan
- Bring in new ETL developers to begin ‘rewriting’ the ETL jobs
By nature, this approach is tedious, error-prone, and very resource-intensive, leading companies to avoid migration and letting the legacy jobs phase out over time rather than proactive modernization.
Mitigating ETL Conversion Risk
Today, one of the most compelling reasons to migrate from one ETL to another is the fact that there are automated solutions proven to greatly minimize the time, complexity, and cost of a conversion initiative, making it possible to understand the feasibility and level of effort required before even starting.
For example, Bitwise uses an automated process to assess the ETL environment, including a detailed analysis of complexities and risks, and a complete inventory listing for jobs and their components, which is used to provide a precise roadmap with conversion timeline estimate and business case for reducing total cost of ownership (TCO) of data integration platforms.
To determine if an automated conversion approach might be a good fit to help you in migrating one ETL to another, visit the Bitwise ETL Converter page which includes webinars and case studies on a solution that automates up to 70-80% of the conversion process and realizes ROI of conversion cost within 1-2 years from saving on legacy licensing.