Case Study - Customized Data Synchronization

We implemented a customized data synchronization process for a Canadian Pharmaceutical Retailer, employing three sync strategies - wholesale, sliced, and time-sliced - to integrate their on-premise retail and commerce data with a cloud data platform.

Client
Canadian Pharmaceutical Retailer
Year
Service
Data Integration and Systems Automation

Overview

Founded over a century ago, this Canadian-based pharmacy retailer has grown into a major player in the country’s health and wellness sector. With hundreds of locations across the nation, the company serves millions of customers annually through its network of pharmacies, walk-in clinics, and wellness services.

The company operates under a trusted banner and provides a wide range of products and services, including prescription fulfillment, over-the-counter medications, health consultations, and a private-label product line. It has consistently evolved with consumer needs, integrating digital tools such as online prescription management and mobile services to improve accessibility and convenience.

In recent years, the organization has positioned itself not only as a retail pharmacy but also as a healthcare destination, offering immunizations, chronic condition support, and partnerships with health practitioners. Through these efforts, it aims to promote better health outcomes while supporting the communities it serves.

The Problem

This customer faced significant challenges in extracting insights from their vast amounts of retail and commerce data. The majority of this data was stored in a large number of front-line systems, which were integrated into their on-premise warehouse/datamart system using an MPP architecture built on top of the Greenplum database solution. This setup resulted in high maintenance overhead, with a significant staff and HR budget dedicated to keeping the existing data systems functioning. Moreover, the lack of good data models and poor data visibility/trustworthiness hindered the customer's ability to compete effectively in the market.

The Engagement

We started with a thorough analysis of the customer's current data infrastructure and workflows. We worked closely with their technical teams to identify pain points and areas for improvement. Our team evaluated several data movement tools, including Matillion, Stitch, and Fivetran, but found them to be unsuitable due to budgetary constraints and the sheer volume of data involved. After careful consideration, we determined that a bespoke data synchronization process would be the most effective solution.

The Solution

We implemented a customized data synchronization process that employed three sync strategies: wholesale, sliced, and time-sliced. Wholesale tables were recreated in the destination cloud data platform every sync, in their entirety, making it suitable for small tables under 10 million rows. Sliced tables were divided into virtual "slices" of table data, each slice containing rows that shared a common value. For example, a table with geographic location columns might be sliced on portal code, ensuring that all rows for the 10001 zip code were moved together. These slices were compared for cardinality between the cloud destination and source, and when there was a mismatch, the entire slice was recreated. Time-sliced tables imposed a temporal ordering on the slice values, giving precedence to more recent slices when determining what to sync.

The Outcomes

The implementation of our bespoke data synchronization process had a significant impact on the customer's business operations. With minimal budgetary impact and without requiring retooling their technology stack or migrating several thousand ETL flows, the customer was able to:

  1. Unlock Cloud Elasticity and Scale: By integrating their on-premise data systems with the cloud data platform, the customer was able to leverage the scalability and cost-effectiveness of the cloud, reducing the need for expensive hardware upgrades.
  2. Improve Data Efficiency: The logging and observability dashboards we implemented allowed them to gauge the efficiency of the sync process, identify areas for improvement, and optimize their workflows for better data management.
  3. Enhance Realtime Analysis: With the integration complete, they were able to unlock advanced analytics capabilities, enabling them to make more informed business decisions in real-time.

By addressing the root causes of poor data systems and implementing a customized data synchronization process, we helped the client overcome their challenges and achieve lasting change, ultimately driving business growth and competitiveness.

More case studies

Modernizing Actuarial Data Preparation

We modernized a manual actuarial data preparation process, migrating it to a more capable, efficient and reliable implementation on top of the Snowflake Data Cloud.

Read more

Unlocking the Power of an Orgnization’s Documents

What if your organization had the ability to reliably read every single document it owned, no matter where it was stored?

Read more

Tell us about your project

Our offices

  • Washington, DC
    1900 Reston Metro Plaza
    Reston, VA 20190
  • New York
    132 West 31st St, 9th Floor
    New York, NY 10001
  • London
    United Kingdom
    1 St Katharines Way, E1W 1UN
  • Bengaluru
    4/1 Bannerghatta Rd
    IBC Knowledge Park, Tower C, Level 2
  • Sydney
    Level 7, 117 York Street
    Sydney, NSW 2000
  • Melbourne
    Level 20, Tower 5 Collins Square, 727 Collins St
    Melbourne, VIC 3008
worldmap