Background
Frederick Wildman & Sons, a leader in fine wine and spirits sourcing and distributing, was looking to optimize its database, reduce administration costs, and minimize maintenance efforts. The company was also looking to sunset their on-premise SQL 2008 database server, whose Microsoft support was running out.
As Keyrus assessed Frederick Wildman’s current IT and server environments, we determined that multiple on-premise servers meant a higher cost of maintenance and administration for the company. Their database administrators were spending a lot of time and effort on tasks that could be automated with the right platform.
In selecting the right solution, Frederick Wildman’s goals were to build a strong foundation for an analytics platform, improve data availability and scalability, reduce administrative costs, dismantle the SQL servers, and move their platform to the cloud.
Recommendation
Keyrus worked with Frederick Wildman to assess potential solutions. Comparing a Snowflake to Azure databases and to a hosted SQL Server, we determined that Frederick Wildman’s data usage meant they could save significantly by moving to Snowflake. For context, a sample pricing matrix for a small environment is below.
Snowflake would also allow our client to scale up usage in the future as enterprise requirements grow. Frederick Wildman can plan for greater concurrency by increasing Snowflake’s cluster count limit, plan for greater power by increasing Snowflake’s cluster size and track usage via built-in administration dashboards.
Solution
Frederick Wildman went forward with Snowflake’s fully-managed, cloud-based data warehouse solution, and Keyrus assisted in the migration. The Keyrus team implemented a production-ready platform on Snowflake, configured with full integration to an Azure active directory. First, we moved data from those SQL Servers into Snowflake via Alteryx’s Snowflake bulk connection which is built to load large data volumes quickly (see next section). We set up virtual warehouses for different departments to enable data storage and set up usage alerts to monitor costs. The Snowflake solution also significantly reduced administrative costs from manual effort previously required to maintain SQL databases (indexing, caching, encryption, etc) while simultaneously enabling elastic compute scaling to speed up query times.
Once the data was set up properly in Snowflake, we reconfigured existing Alteryx workflows to read from Snowflake and push curated data seamlessly to Tableau. Sales and marketing teams use Tableau to make decisions, and we cut over to the new data sources with no downtime. We also helped re-work some of the dashboards to be most effective to the business.
Spotlight - Snowflake Bulk Loader
Alteryx’s Snowflake Bulk Loader is used for writing large volumes of data at a high speed.
How does bulk loading work?
If you convert from one database management system to another, you would not want to waste time and resources reentering all the information (tables, columns, etc from all different departments) from the old database into the new one. Instead, this solution allows developers to dump the data from the old database into special files in AWS S3 buckets. The files are formatted to be read in parallel at high speeds to import data into a database on the Snowflake instance where tables are then created automatically.
Results
With Snowflake’s cloud platform and “pay as you go” pricing, Frederick Wildman was able to significantly cut administrative work and data warehousing costs. Frederick Wildman’s data is now governed and supported on a fully managed database, which includes Snowflake protecting their data through encryption, continuously backing it up, and providing a built-in disaster recovery solution.
Frederick Wildman’s new Alteryx workflows, adjusted by Keyrus to be compatible with Snowflake, can now run in parallel and faster than before. Snowflake’s smallest computing unit has more computing power than Frederick Wildman’s old servers, meaning Snowflake can handle more data extraction and data transformation than their previous environment could - and with fewer manual hours of maintenance. Finally, Frederick Wildman is paying less for a faster cloud-based data warehouse with more elastically scalable computing power.
Keyrus continues to assist Frederick Wildman & Sons in applying Snowflake’s best practices, ensuring that they get the most value out of their new data platform.