Read how Quantiphi optimized 900+ ETL mappings, 400+ tables, and over 8 billion rows of data, accelerating processing times and reducing costs to help the insurer enhance their data infrastructure and maintain a competitive edge leveraging Snowflake.

About the Client

The client is one of the Fortune 500 insurers in the United States, serving millions of policyholders nationwide. They required a holistic data management transformation to reduce dependencies on legacy systems like Teradata and Hadoop, improve operational efficiency, and secure sensitive data.

Problem Statement

The customer relied on Teradata’s on-premise infrastructure to host the key components of their enterprise data platform, primarily the End-of-Day Systems (EODS) and Analytical Data Warehouse (ADW). Teradata served as the central data service layer, supporting downstream sales operations and analytical reporting applications. As part of their modernization strategy, the customer plans to transition to a cloud-based infrastructure, with Snowflake being chosen as the target cloud data warehouse layer.

Complex Data Ingestion: The scale of data ingestion required higher turnaround times, complicating the migration process.
Standardization Needs: The necessity to standardize ETL pipelines without disrupting business outcomes.
Security Concerns: Addressing security aspects related to Personally Identifiable Information (PII) was critical to maintain compliance.

Challenges

Tight Deadlines: The migration had to be completed within 6-8 months, requiring precise coordination and execution.
Data Complexity: Large-scale data ingestion and processing presented challenges in maintaining performance and accuracy.
ETL Pipeline Standardization: Ensuring that the standardization of ETL processes did not negatively impact ongoing business operations.
PII Security: Implementing robust security measures to protect sensitive data during and after the migration.
Validation Strategy: Building a scalable and comprehensive data validation strategy to maintain data integrity.
On-Premise Database Maintenance: The IVR, GPR, and RDM databases needed to be maintained on-premises, requiring a dual-environment management strategy.
Dual Data Maintenance: EODS (End-of-Day Systems) needed to be maintained both on-premises and in Snowflake, ensuring consistency across platforms.
Reduce Legacy Dependencies: The client aimed to minimize dependency on Hadoop and eliminate dependency on Teradata as a source, streamlining their data infrastructure.
Utilize Existing Mechanisms: The existing ingestion and tokenization mechanisms had to be leveraged for moving data to Snowflake, necessitating seamless integration with new systems.

The Solution

Our Implemented Solution:

Quantiphi developed a comprehensive migration strategy tailored to the client’s needs. The solution included:

Complete Data Migration: Over 6-8 months, all data assets were migrated from Teradata to Snowflake and on-premises systems.
Downstream Data Availability: Configured data availability through Snowflake’s cloud warehouse and on-premises systems, ensuring seamless access.
Custom Snowflake Accelerators: Designed and implemented custom Snowflake accelerators to optimize the design, implementation, and validation phases of the migration.

The migration process was strategically executed in two key phases to ensure a smooth transition from Teradata to Snowflake and on-premises systems, while maintaining the integrity and availability of critical workloads.

Phase 1: Full Load and Incremental Load Migration

Objective: Enable full load and incremental load processes for migrating both existing and future workloads from Teradata to Snowflake and on-premises systems.
Execution:
- Implemented full load migration by transferring all existing data from Teradata to Snowflake.
- Established incremental load processes to ensure ongoing synchronization of data, capturing any new or updated information post-migration.
- Prioritized the migration of critical workloads, specifically EODS (End-of-Day Systems) and ADW (Analytical Data Warehouse), to ensure minimal disruption and immediate availability in the new environment.

Phase 2: ETL Pipeline Refactoring

Objective: Refactored existing ETL pipelines to eliminate dependency on Teradata, transitioning to Snowflake and on-premises SQL Server as the primary data processing platforms.
Execution:
- Analyzed and redesigned ETL pipelines to be compatible with Snowflake and on-premises SQL Server, ensuring that all data processing logic is preserved and optimized.
- Prioritized the refactoring of ETL processes related to EODS and ADW workloads, as these are critical for the client’s daily operations.
- Validated and tested refactored pipelines to ensure data integrity and performance before decommissioning Teradata as a source.

This dual-phase approach allowed for controlled migration, minimizing risks and ensuring that critical business functions remained uninterrupted throughout the transition.

Results and Impact Created

Quantiphi’s solution delivered significant improvements in the client’s data management capabilities:

Standardized ETL Pipelines: Achieved consistent and reliable data processing across the organization.
Reduced Execution Time: Optimized processes, resulting in faster data processing and quicker access to insights.
Reusable Modules: Reduced resource consumption and costs through the development of reusable modules.
Enhanced Security: Strengthened data protection with robust security measures, ensuring compliance with PII regulations.
Standardized Reporting: Enabled the creation of standardized reports, improving data accuracy and consistency.
Multiple Data Formats: Facilitated the seamless integration of various data formats and source systems.
Ready to use accelerators – Qinetic, mitigates the need for developers to create code from scratch when migrating their data warehouse through single-click, secure, automated, and scalable scripts that handle data better, faster, and cheaper.

Quantiphi’s data migration strategy enabled the client to streamline operations, enhance data security, and achieve significant cost savings, positioning them for future scalability and success.

Technology Used

Apache Hive

Python

Amazon EMR

Informatica

Snowflake