case study

Teradata to snowflake

1.

the customer:

A Pharmacy Benefits Manager serving 27+ million members was experiencing serious performance and reliability issues with their Teradata data warehouse supporting mission-critical app. 

2.

THE CHALLENGE THEY FACED:

As their Teradata contract came up for renewal, they were looking to move to Snowflake; but it was a daunting complex and risky undertaking, which they lacked the skills for.

3.

HOW WE HELPED:

phData brought best practices, runbook, automation not only to migrate their data warehouse,
but to architect it to maximize the benefits.

4.

WHAT WE GOT DONE:

Massive improvements in performance, scalability and efficiency querying data across tables with 1.7 billion detailed rows:
  • Large queries ran 91% faster for Medium Warehouse, 97% faster for XL Warehouse
  • Zero failures across 518 queries with 50 concurrent users
  • Ability to support new Data Streaming use cases, unlocking new revenue streams
  • Improved ease-of-use with autotuning and automated SQL conversion tools to save DBA time

Full story: data warehouse modernization

Providing access to centralized pharmacy claims information is what Pharmacy Benefits Managers (PBMs) do; it’s what empowers them to help both pharmacies and insurers identify fraud, waste, and other potential savings.

In other words, their data is their value. So, when a certain PBM (serving 27+ million members throughout the U.S.) found themselves facing increasingly destabilizing performance issues with their on-prem Teradata data warehouse, finding a solution was a top priority.

The pain of poor data warehouse performance

It was clear to the PBM that performance and reliability issues with their data warehouse were hurting their bottom line. As they ramped up more concurrent users on the system to support the growth of their business, the issues only got worse:
  • Queries took longer and longer
  • Far more queries began to fail
  • Their DBAs had to dedicate more time to fighting fires and tuning systems

One of the company’s most important business-critical applications was especially responsible for hampering the performance of other systems, but there were few options to remediate the problem without limiting access.

Ultimately, it was clear that the PBM’s existing systems couldn’t scale to meet their needs. With their Teradata contract coming up for renewal, they were eager to invest in a next-generation data warehouse platform, with a modern, cloud-based architecture. They became especially interested in Snowflake — not only because it could resolve their multi-tenancy issues and do what their existing system did more efficiently, but also because it provided the best support for new use cases, such as Data Sharing, that could potentially unlock new revenue streams.

However, they also understood that enterprise-scale data warehouse migrations bring complexity and risk. Knowing they needed deep expertise around Snowflake, they turned to phData’s team of specialists to map a path to a modern cloud-based platform with the ability to support both current and future needs.

A cloud-based treatment

phData provided a complete framework for the PBM to migrate Teradata workloads to Snowflake, complete with best practices, runbooks, and automation. To validate performance in Snowflake — querying across tables with 1.7 billion detailed rows — here’s what we did:
  • Developed the strategy for ingesting data sets onto Snowflake
  • Trained the PBM’s team on standards and best practices for future data ingestion and streaming-based architecture to ensure future query performance and reliability
  • Implemented automation along the way, including a tool to translate Teradata SQL statements to Snowflake SQL statements at the push of a button
  • Replicated the PBM’s existing security and RBAC models for Snowflake at a row and column level
  • Demonstrated Snowflake’s ability to support new Data Sharing use cases key to the PBM’s future revenue generation strategy

Proving the value of data warehouse modernization

The results after moving from Teradata to Snowflake were night and day. The PBM realized massive improvements in performance, scalability and efficiency. Queries joining data across eight tables — three of which had 1.7 billion detailed rows — saw the following results:
  • Improved performance — Large queries ran 91% faster for Medium Warehouse, 97% faster for XL Warehouse.
  • Drastically better scalability — We saw zero failures across 518 queries by 50 concurrent users on Snowflake, compared to the majority of those queries failing when we ran them for 20 concurrent users on Teradata. And with Snowflake’s cloud-based deployment model, the PBM can much more easily scale data warehouse operations up and down to suit demand.
  • Ability to support new use cases and revenue streams — Snowflake’s unique capability to support Data Sharing use cases (uniquely valuable to the PBM’s core business) opens the door to future innovation.
  • Ease-of-use and developer time — Much simpler ease of use, especially to optimize tuning. Automation from phData, such as SQL conversion tool.

Results

Ultimately, the PBM is now positioned not only to scale their data warehouse with far better performance, but to support
innovative new ways of monetizing the value of their data.

As one of our solutions architects put it: anyone migrating to Snowflake from an on-prem data warehouse knows there is
“no easy button” to make it happen. But when you make the jump — and you do it with Snowflake-specific data ingestion,
architecture, and operational best practices in mind — the results will speak for themselves.

Ready to learn more about phdata? Let's chat.