What is Extract, Transform, and Load (ETL)?

ETL– stands for extraction, transformation, and loading. The process of extracting data from source systems and bringing them into the data warehouse is commonly called ETL.

Why selecting a right ETL tool is important for Data Migration ?

Technology is evolving, and business needs are always changing. Given this state of consistent change, a majority of organizations will need to take on a data migration project at some point. Data migrations can be tricky. These projects hold many challenges, and according to Gartner, 83 percent of data migrations fail or exceed their budgets and schedules. Selecting the right Extract Transform and Load (ETL) tool can reduce the data quality issues and improve the standardization of data across different systems. The standardized data can be trusted and used to develop various insights on data by developing business analytical capabilities like AI and Machine learning techniques.

What were the problems faced when using a legacy ETL tool ?

Our client is a leading independent healthcare technology company growing at an annual rate of approx. 12% YoY. They were consolidating different siloed tools to a unified platform as part of their technology strategy roadmap. They were also acquiring new business through multiple mergers and acquisitions. It warranted the client to review the existing legacy and limited feature set ETL data migration tool to an effective and efficient ETL tool at the enterprise level to reduce technical debt and save annual cost. They had a hard deadline to accomplish this milestone and realize the value benefits out of it. There were multiple enterprise applications involved and the data from those applications have to be migrated to Enterprise Data Warehouse (EDW), which has to be done in a smooth and seamless manner with minimal disruption.

How an alternate ETL tool was identified, implemented and its challenges ?

The team evaluated various ETL tools both, open and closed source, and identified Talend Open Studio as an alternate tool, which provided much flexibility for developers in developing the ETL packages quickly which, used to take weeks/months with the earlier legacy tool. The challenge was to design and create a data pipeline from various Enterprise application data feeds (i.e., Financial, HR/Payroll, and Supply Chain Suites) and its extraction, transformation, and load. High-level logical design and low-level design was created with various stakeholders’ technical and functional teams involved. The final solution was to have Talend ETL jobs to be developed and automated to push data to Enterprise Data Warehouse (EDW) powered by Amazon Redshift. In addition to the Talend Migration, there was another challenge mid-way of the project to migrate from a Talend v6.5 to Talend v7.3 is more secure and less prone to vulnerable attacks.