Data warehouse for premier fertility network

We joined the project to assist an organization that supports fertility clinics in providing reproductive services. They help other teams to grow and scale while preserving their autonomy.

• Their focus: to support fertility centers with strategy, technology, tools, and operations
• Their model: a unique healthcare network that brings together exceptional fertility clinics 

Our company was contracted to design and build a reliable data warehouse for centralized data management. Their records were stored all locally and required a significant manual effort – both inefficient and sluggish.

In this detailed healthcare data warehouse case study, we reveal the details.

One, complex data structures

We had to dive into complex data structures to understand the processes.

Two, restricted source access

We had to work despite restricted source access to preserve patient privacy.

Stage 1. Business discovery

Stage 2. ETL pipeline

Our team:

1. First, extracted .bak and .trn files from the external storage to the AWS S3
2. Second, moved and restored the extracted .bak and .trn files in the MSSQL Server
3. Then, exported data stored in the MSSQL Server
4. Finally, loaded data directly into the AWS Redshift stage layer

Next thing:

• For fast data integration, we utilized Glue Jobs using Python
• For regular data extraction, we utilized Step Functions as a workflow orchestrator

Stage 3. DWH development 

We build a cloud-based data warehouse by utilizing Redshift Procedures covered by Glue Jobs.

Stage 4. Business automation

In brief:

• To provide easy reporting, we prepared data marts and aggregates
• To simplify data access, we integrated Azure AD by leveraging AWS SSO