Case Study: Creating a Medical Data Warehouse

TekPartners Case Study - Medical Data Warehouse

Download Now

Business Situation

The client was looking to modernize their company’s central data analytics platform from an on-premise SQL Server environment over multiple servers and databases and move to an enterprise data warehouse. The client’s preference was to build that data warehouse in Azure SQL Data Warehouse, which would later become known as Azure Synapse Analytics. They were interested in this solution because they wanted to stay within the Microsoft ecosystem.

When the client reached out to Microsoft for consultants to help them launch this project, they were referred to TekPartners as a trusted Gold Data Analytics & AI Partner in the industry that could help with their unique needs. TekPartners then submitted proposals with a variety of options that met the client’s requirements and budget as part of our customized solutions that can be adapted to each client’s specifications.

The Solution

The client chose a co-managed project solution with a team of eight experts that consisted of a blend of TekPartners consultants working alongside the client’s internal staff. The team included a Scrum Master, Solutions Architect, a Data Architect, four Data Engineers, and a Quality Assurance Engineer. This hybrid team would then work on two-week sprints to build a foundation based on high-priority use cases. The end goal was to create an enterprise data warehouse built in Azure Synapse with three layers: source, staging and data warehouse. This solution included Azure Data Factory ELT data ingestion from on-premise source systems to the Azure Data Lake, then to the Data Warehouse with multiple daily refreshes.

The TekPartners consultants worked with a senior member of the client team that was well-versed in their data to set up a process to land on-premises data into the data lake and into the data warehouse. As part of our work with this client, we documented all processes in Microsoft Azure DevOps to make it easy to onboard new team members who joined later in the project. Azure DevOps managed the workflow with ease, and our Scrum Master educated the team in the Agile methodology and best practices. These systems made working together more efficient and would set the client’s team up for success once the project was complete.


The initial project was estimated to take about six months and our team delivered the Financial, Master Data, and Clinical subjects successfully. The Financial area was especially important to complete early on because it held many of the objects that later subject areas would depend on. This project was also being used internally as a model for Agile methodology, DevOps and GitHub source control.

When fully launched, the project will offer the client several solutions to help improve their business through new efficiencies in clinical and financial metrics that the business was not able to access without large manual efforts from multiple different systems. For example, our team integrated previously siloed subject area data, significantly reducing the amount of time required for creating insights in one concise area for business audience use. In addition, the data is modeled in a user-friendly way, so only minor training would enable a team member to be able to work with the data instead of depending on specialists with deep and unmatched industry or company-specific knowledge. Additionally, the data vault now allows for easier incremental field additions with minimal to no major changes to the system, allowing a vast increase in time to production, rather than a virtual standstill. Lastly, the single source(s) of truths are realized certified data sources that allow for ad-hoc querying by others in the company without fear of multiple versions of business rules, etc.

Technologies Supported

Microsoft Synapse Analytics, Azure DevOps, Azure Data Factory, Azure Databricks, GitHub Source Control, PowerBI.

Client Profile

The client works within the healthcare industry by partnering with hospitals, systems, and facilities to supply clinical services, revenue cycle management, and other solutions for better patient care. The client’s wide-ranging services help to enable these entities to better serve their communities by optimizing performance and quality while improving the patient experience.


Want to See More?

View more of our case studies by clicking here, or use the tags at the bottom of the page.


Contact Us

More Case Studies

Use the tags below to see more of our case studies and other content by topic: