Customized Data Synchronization

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.