ETL Migration
Automated Informatica ETL Migration to Azure Data Factory and Snowflake EDW
A national used vehicle retailer needed to migrate its on-premise legacy Enterprise Data Warehouse built on Informatica and Teradata to the cloud in Azure Data Factory and Snowflake to leverage the advanced capabilities of the Azure ecosystem to enhance data integration, workflow orchestration and overall data management.
Client Challenges and Requirements
- Migrate Informatica to ADF as part of program to modernize the Enterprise Data Warehouse (EDW)
- Inventory to be migrated included 578 workflows, 3461 mappings, 801 Python, Java and VB Scripts (of which 611 needed to be re-engineered and 174 migrated as-is)
- No Legacy System Knowledge / Documentation
- Re-engineering of frameworks which were tightly coupled with Informatica to make them ADF compatible
- Limited Azure awareness with stakeholders, delays and changes to design decisions and unavailability of adequate test data
Bitwise Solution
Bitwise provided automated ETL Migration services using its proprietary ETL Converter solution to accelerate project execution within defined cost constraints.
- Conducted Assessment for detailed analysis of legacy system with Assessment Report to estimate efforts.
- Utilized the Source ETL Analyzer tool throughout the project to reduce turnaround time and increase accuracy with detailed analysis of source/target, patterns, writeback scenarios, linked services, lineage and scheduling.
- Used the Assessment reports to finalize ingestion patterns leveraging best practices for handling sensitive PII data and plan the Execution phase.
- Developed reusable frameworks for replication, email notification, error and logging.
- Integrated ADF with GitHub and Azure DevOps to build CI/CD pipeline to automate deployments from Dev to Prod.
- Achieved high availability and scalability to meet RPO/RTO requirements.
- Standardized documentation via wiki, created knowledge base and improved ecosystem support and observability.
- Streamlined governance with POD delivery structure during execution.
Tools & Technologies We Used
Bitwise Source ETL Analyzer
Bitwise ETL Converter
Informatica PowerCenter
Teradata
Azure Data Factory (ADF)
Bitwise Data Validation Utility
Python scripting
Snowflake
Key Results
Up to 87% automation in converting Informatica mappings to ADF Dataflows for a 21% reduction in overall migration effort
Code assessment time slashed by 50% using Bitwise Source ETL Analyzer
Performance and cost optimization through re-usable components, data flow parallelism, and infrastructure optimization
Delivered superior user experience, stability and performance with advanced data ingestion and replication
