Case Study: Snowflake Compute Costs Optimized by 25%

About Client

The Client is a national weight-loss brand that specializes in delivering healthy foods to its subscribers. They have helped millions of people in North America obtain their weight loss goals and have become a household name brand.

Challenge

The extensive data gathering and analytics requirements produced by their marketing campaigns were growing the Client's data footprint and its associated compute in a linear path with extreme jumps. This linear growth exploded with the deployment of a marketing application that simply did not have the appropriate optimization and spiked their Snowflake compute and storage out of control. The cost of their compute expansion caught the attention of their executive stakeholders as it was trending to millions of dollars in cost overruns. The attention from the executive stakeholders drove the organization to seek expertise in Snowflake optimization to cut off the linear growth. This was the status the client was in when Intricity started its optimization work.

Navigating Constraints

• The average critical query was consuming between 12,000 and 40,000 micro-partitions. Additionally, these queries were running all day long with updated data.
• The churn of data was very high due to its real-time nature with some tables churning several thousand times per day.
• The Client's time travel data retention was very high and added complexity to the price reduction of the Snowflake footprint.
• The marketing tool validates all of the query structure every time it is queried which is a cost that cannot be circumvented.

Win 1 : 

Intricity immediately began a cleanup of known performance problems with some tactical efforts around the existing warehouses. The team needed to provide a ceiling and floor range that could guide the finance teams in determining the compute commit that should be made for the next contract. The tactical changes gave some confidence that the range was not jumping on a linear path while still satisfying the needs of the user community.

Win 2:

Upon stabling the compute footprint, Intricity began a strategic table optimization track that could apply automation to efficiently pack the Client's tables for the fastest possible acquisition speeds and lowest amount of micro-partition scanning. This packing was based on the most optimal clustering sequence for the highest possible performance. Intricity leveraged its FlowObjects tooling to cyclically analyze and repack tables ensuring both the range and the trend stabilized below the negotiated amount. 

Win 3:

Intricity generated a data onboarding strategy coupled with automated scripts to deal with blind updates which were sharding the micro-partitions in Snowflake. This leveraged the best practices of the Landing Zone and graduated data to ensure effective packing of the micro-partitions after the updates had been reconciled. In combination with the clustering, the queries themselves were optimized to take better advantage of the structural changes. In many cases, the query speeds went from minutes to seconds.

Win 4:

Intricity implemented the FlowObjects tracking framework which indicates where tables are beginning to get out of control and suggests changes. The cost control provided by this framework has enabled the client to stay well below the contracted amount (refer back to the yellow line).

This additional capacity has given the organization the ability to trial new content with a process that accounts for future growth.