Background
Our client is an American website where current and former employees anonymously review companies. Headquartered in San Francisco, California, the client wanted to convert their legacy ETL system created in Microsoft SSIS to a new modernized platform using Airflow.
The Challenge:
Not Supported Pipelines / ETL: The SSIS jobs were developed almost 7 years ago and were running on an unsupported version, posing a risk to the stability and reliability of the data pipelines.
Lack of Skilled Resources: As SSIS is a phased-out technology, it was challenging to find skilled resources with expertise in maintaining and updating SSIS pipelines.
Scalability: Due to the lack of skilled resources and the use of non-supported technology, the IT team faced difficulties in making modifications and meeting changing and dynamic business requirements.
The Solution
Our approach to modernize the pipeline included the following steps:
Defined Modernized Architecture: We designed an architecture using Airflow and Hive that would effectively replace the legacy SSIS system.
Documented Existing Data Flow: We thoroughly documented the current data flow within the SSIS system to identify dependencies and optimize the migration process.
Designed New Data Flow: We designed new data flows using Airflow, ensuring that all the required transformations and integrations were accounted for.
Developed HQL & Airflow DAG: We developed Hive Query Language (HQL) scripts and Airflow Directed Acyclic Graphs (DAGs) to implement the new data flows.
Connected Upstream & Downstream Systems: We established seamless connections between the new platform and the upstream and downstream systems to ensure smooth data flow.
Paused/Stopped SSIS Packages: We successfully halted the execution of SSIS packages, transitioning all data processing to the modernized Airflow platform.
The Results
Enable Retirement of Legacy Platform: The modernization effort allowed for the retirement of the unsupported and legacy SSIS platform, eliminating the risks associated with maintaining an obsolete system. This also resulted in cost savings for the client.
Cloud-Based Scalable Solution: With the implementation of the new tech stack on the cloud, the Data Engineering team gained the ability to respond faster to new requests and changing business requirements. The scalability of the new platform enabled efficient handling of larger volumes of data and adaptability to future growth.
Through the modernization of the pipeline using Airflow, we enabled our client to retire their unsupported SSIS system, improve scalability, and respond more effectively to changing business needs.
