Data Lake Transformation for a Membership-Based Organization

Background

Historic and leading professional membership organization for ob-gyns. Our client champions OB-GYNs in providing equitable, evidence-based care for patients today and into the future. Prior to Keyrus’ involvement, there was no centralized platform for data ingestion/analytics. Various departments would reference various sources from Excel documents to Looker dashboards and had different levels of data maturity. The organization aimed to modernize their data analytics platform and gain critical insights to their marketing campaigns and product sales previously unavailable.

Challenge

The membership organization needed to consolidate disparate data sources and varying degrees of analytics capabilities into one modern, meaningful platform. The current system with varying departments facing varying challenges proved difficult for IT to manage soundly. Solving this required the development of a robust end-to-end solution, creating in-roads for the organizations varying on-prem and cloud data platforms such as SQL Server and GCP. The data was landed into AWS Redshift to efficiently process and transform data with AWS Glue, and Power BI for interactive reporting and visualization.

Approach

Keyrus implemented a tailored data ingestion strategy leveraging a mix of SFTP connectors, AWS DMS, proxies, and API calls to ensure data was refreshed regularly and reliably. An ELT process built on AWS Glue extracted and processed records from six distinct source system applications, creating a unified data foundation. The team then designed and deployed a comprehensive data warehouse in Amazon Redshift, using stored procedures to efficiently transform and structure data for analytics. Finally, automated operational dashboards were developed to track product sales and inventory, monitor marketing campaign performance, and measure event attendance—providing real-time visibility into key organizational metrics.

Key results

Benefits

The solution delivered several key benefits, including improved data accuracy and freshness through automated processing that eliminated manual errors and ensured real-time insights. Actionable dashboards provided stakeholders with immediate access to key business metrics, empowering data-driven decision-making across departments. Additionally, the use of AWS Glue and Redshift ensured scalability and high performance, enabling the environment to handle large datasets efficiently and adapt seamlessly as organizational needs grow.

Share this key play