Peoplecare is a national, not-for-profit health insurer that exists to benefit its members. Peoplecare plays an active part in several industry associations and joint ventures, aiming to get the best for its members. These associations combine the strength of smaller membership health funds to give our members a greater voice in a competitive market. Growing for more than 66 years, Peoplecare now covers around 70,000 people on 34,000 memberships.
Peoplecare were struggling to scale their existing on premise data solution and process data that was semi structured, unstructured and event driven. Peoplecare have a range of data types such as relational databases, click streams, flat files and APIs. They required a modern and flexible data solution to ingest, transform and process this wide range of sources, as well as more scalable services that could better suit the ever evolving demand in data analytics.
CMD proposed a solution that uses multiple AWS services to extract data from the wide range of sources needed by Peoplecare analytics and consolidate into a single source of truth data warehouse. Data is all landed into S3 where it is extracted and prepared for loading into Snowflake via Snowpipe. Data types and duplicates are removed in the curated schema in Snowflake before being merged into a single source of truth analytics schema focused around the member journey.
The solution uses Transfer Family or the AWS CLI to securely load flat files into S3 so that they can be transformed and loaded into Snowflake. DMS is used to connect to relational databases and continuously load CDC logs into S3 where they are loaded into Snowflake via S3. dbt is used to manage and schedule all the SQL transformation jobs in Snowflake, and Tableau connects to the unified analytics tables for consistent reporting.
- Transfer Family
CMD was able to help Peoplecare consolidate and structure all of their data into a single data warehouse that is more scalable and contains more functionality than the previous generation. Peoplecare are now able to process data with a much higher velocity and output member centric analysis with greater speed. This has improved how different types of data are processed and has enabled them to process critical sources such as external competitor analysis data and click stream data. By leveraging re-usable patterns and automation, Peoplecare can deploy additional data pipelines and make iterative changes to existing pipelines faster and get critical analysis in the hands of decision makers much faster.