Case Study: Building a Data Warehouse Powered by Snowflake

Challenge

The client is a company in the healthcare sector that is in the midst of a rapid period of growth over a short time due to their continued success. The original data infrastructure they had in place was not going to be able to scale with the ongoing development, so they were rapidly approaching a point where they would need a solution that could serve their needs better than the current system. Although the client had adopted a cloud data warehouse technology in Snowflake before our team got involved, the original processes and usage would soon prove unsustainable with the incoming changes required to scale the solution for their rapid growth. They needed a better way to support the changes the company was undergoing and clean up older data in order to succeed.

Solution

The INSPYR Solutions Professional Services division was originally engaged for Tableau consulting services. We noticed systemic issues during the course of our work and spent some time to understand the state of the data solution environment at the company. We saw an opportunity to create a solution that would serve the client’s needs much better than their current infrastructure and streamline their Tableau dashboard development efforts, so we presented these ideas. The client had not previously considered implementing an enterprise data warehouse built in the Snowflake cloud, but it would prove to be the exact solution they needed to sustain the company’s rapid growth and data metrics needs.

INSPYR Solutions quickly deployed a team of consultants to work on different aspects of the project including four Data Engineers specializing in ELT and a Solutions Architect. The original project timeframe was approximately three months, but was later extended as the scope of the project changed to address additional needs that were discovered throughout the process.

The solution was built with Matillion ELT tools and incorporated a data vault data warehouse, chosen because it would be particularly beneficial for a client in the healthcare sector, which is known for constant change. This type of data warehouse would be well equipped to absorb changes with relative ease, creating a flexible solution that would work for the client in the long term. In addition, this solution would capture historical data changes, which was a first for the company.

Our team worked together with the client’s internal team to complete this project and the client’s staff took on roles as administrators for Snowflake and Matillion. The client was also responsible for landing the external data into Snowflake and the INSPYR Solutions team would work on it from that point forward in the solution. Working together with the client’s internal team required a great deal of synchronization between these processes, which was handled fluidly between the client’s internal team and the INSPYR Solutions team to create an efficient workflow.

Outcome

The first phase of the project consisted of three data marts to be architected, developed, and rolled out over the course of three months. The first data mart was constructed for the sales department since it contained many of the data assets the other data marts would depend on. In the course of their work, the team uncovered some issues with data quality that needed to be addressed. This additional work, in combination with changes to the scope of the original project charter to go with a data vault architecture, required that the project be extended in order to accommodate the additional tasks.

The INSPYR Solutions team did a thorough job handling these needs and exceeded the client’s expectations by the end of the first phase by leading the client’s internal team through several important milestones. This encompassed the first full development path to production through the various development, testing, and production environments including the first to use a deployment model; the first project to have a very thorough and full documentation done in conjunction with the milestones; the first project fully transitioned and functional in JIRA; the first to contain a process for capturing and logging data quality issues for the client’s new data governance effort; and the first project to include building out a data dictionary. These many firsts for the client represented a huge step forward in its development processes and will provide the groundwork for future projects.

Beyond these innovative improvements, INSPYR Solutions helped with other processes that would assist the client in future endeavors. For example, our team recorded videos of peer review sessions that were then utilized as helpful training materials when new members joined the team. This documentation will be invaluable as the team expands and when our team eventually hands off the system to the client. Another aspect of our work with this client also included scaling up the project management structure from a task tracking system to utilize Atlassian JIRA and include documentation and training materials that were stored in Confluence.

Once fully launched, our solution will solve several problems the client was experiencing. First, our team created a more efficient means for Tableau reporting to help with the client’s data. Another solution INSPYR Solutions created for the client was making the data sources in Tableau live-read (pass-through) from Snowflake. Additionally, the data vault now allows for easier incremental field additions with minimal to no data pipeline or data warehouse schema changes. Lastly, the single source(s) of truths could be realized as single star schemas. These certified data sources that allow for ad-hoc querying by others in the company without fear of multiple versions of business rules, etc.

After the initial project phase, the client signed INSPYR Solutions on for two more data marts to further extend the enterprise data warehouse and has been looking to utilize our experts for additional projects. Our team had a huge impact on the client’s business by helping their internal team improve their efficiencies, as well as by creating a system that would allow for faster creation of dashboards, alleviating pressures on the internal team for specialized ad-hoc querying. We continue to work with this client to this day by supporting further development of the company’s enterprise data warehouse powered by Snowflake.

Client Profile

The client is a part of the healthcare sector focused on advancing the health of certain populations and raising awareness of their specific healthcare issues. The company’s goals include developing solutions to advance healthcare through innovation and to help both patients and practitioners move toward a better tomorrow.