Case Study: Snowflake Cloud Migration with Databricks
As part of a company-wide initiative to migrate to Microsoft Azure as the company cloud provider, the client needed to address a series of longstanding issues with their internal data estate. Over time, the company had developed multiple data warehouse and analytic solutions built primarily on an on-premises Microsoft SQL Server.
Because these systems were now outdated, they had difficulty processing the increasingly heavier analytical load that the business community now requires. Business users were pulling their own data through Tableau and direct queries. They were finding it difficult to navigate the multiple data solutions and get the performance they needed from the existing solutions. The mandate to move to Azure provided an opportunity to address multiple issues as they modernized their data warehouse data estate.
After considering several strong cloud data warehouse options, they made the decision to move to the Snowflake Data Cloud on the Microsoft Azure platform. Performance was a primary driver across the solution, as the platform needed to deliver data quickly and accurately for the end users and be able to process vast amounts of data on the back end efficiently during the data load processes. Given the complexity and size of the data transformations needed, the Databricks service within Azure was a clear choice for the data movement (ETL/ELT) tool with Azure Data Factory.
With the technology choices made, the client needed to begin delivering this solution as soon as possible. INSPYR Solutions had long been a partner for the company’s critical initiatives, and they turned to the Professional Services Data Analytics Practice for guidance on best practices and the expertise to deliver.
INSPYR Solutions assigned a team of six data engineers and report developers to work directly with the client team members to provide immediate value in accelerating the end-to-end development of a new consolidated data warehouse on Snowflake Data Cloud. The client also wanted our help in understanding best practices for implementing efficient data preparation and processing pipelines using the Databricks ETL service within Azure and in data presentation through Tableau.
The Data Engineering Team started by working with client architects to provide an understanding of the infrastructure needed to create the Databricks pipelines and the expertise to move data into the data warehouse solution in a cost effective and efficient manner. The data engineers worked side-by-side with client developers to create Snowflake data objects and Databricks and Azure Data Factory pipelines to handle the complex transformations required by the needs of the data load process. INSPYR Solutions consultants also provided ongoing guidance on a streamlined process for efficient data loads.
The Report Developers worked directly with business users to collect requirements and develop new high-performing Tableau dashboards leveraging the data from the new Snowflake data solution. The team also developed proofs of concept on Power BI and Azure Analysis Services to assist the client in their technology decisions and provide good projections on the response time when moving to the cloud from their on-premises analytic solutions.
When the client called on INSPYR Solutions to accelerate this initiative, the team was able to deliver on the objectives of the cloud data migration project with solid results and positive client feedback. In leveraging the expertise of the Data Analytics Practice and our vendor partnerships (Snowflake, Databricks, Microsoft, Tableau), we provided the right expertise to deliver a solution in less time and significantly lower cost to the client than the competition, helping them to achieve the goals of a fast-performing data solution with meaningful reports and dashboards for their business users.
Our teams were successful in creating the necessary dimensions and fact tables required for the Tableau data model consumption. Incremental refreshes and improved performance in the Snowflake data warehouse decreased the wait time dependency of data warehouse systems to be loaded before refreshing Tableau models. This allowed the business community to access their data sooner than ever before. The team implemented best practices in Tableau to allow for quick data display on dashboards relating to critical metrics within the organization.
In terms of savings, the client reported that the migration from the on-premises Microsoft SQL Server environment to the Snowflake Data Cloud saved over 50% in support costs. Query response times from Snowflake were up to 70% faster than their prior solution and the proofs of concept brought insights to the client on time and money saved by moving from SSAS to Azure Analysis Services or Power BI tabular model.
As a leader in workspace as a service (WaaS), the client pushes the limits of productivity with its innovative products. Specializing in cloud computing software that allows for visualization, mobility management, and secure networking applications and software, the client enables businesses and individuals to have easy access to applications, desktops, data, and communications on any device or network. With continual efforts to become the preferred solution for enterprise, the client is consistently expanding and creating integrated technology services that anticipate and meet the needs of their enterprise customers.
Snowflake, Databricks, Azure Data Factory, Tableau, Microsoft SQL Server, Microsoft SQL Server Management Studio.