Optimizing model performance and reducing data platform costs: A dbt Labs case study from Coalesce 2023

"You can deepen platform knowledge by engaging the folks on your team and helping them understand the tangible effects of implementing some of Snowflake best practices."

Elize Papineau, Sr. Data Engineer at dbt Labs, explains how to optimize the performance of a dbt project running on Snowflake. Elize describes how to leverage Snowflake infrastructure for performance analysis, the performance improvements achieved, and how she deepened knowledge of Snowflake on their data team.

Large-scale projects can be optimized for better performance without refactoring the models

Elize discusses the process of optimizing the performance of dbt projects running on the Snowflake. The team at dbt Labs noticed some performance degradation in a project that had grown over the years, and they decided to take action rather than just refactor the models.

"About six months ago, we started to see some performance degradation that we had to do something about," Elize explains. "We wanted to fix it. I'm going to talk about what we did in May, and then in June, to bring this average runtime down again without refactoring a single model."

Elize highlights the use of Snowflake query tags and metadata views which allowed her team to analyze the performance of their dbt project and identify trends. This deep analysis enabled her to make informed decisions about where to optimize, leading to significant performance improvements.

Prescriptive recommendations can help optimize resources and improve team efficiency

Prescriptive recommendations were an important part of dbt Labs' strategy for performance optimization. They provided a clear guide on when to increase compute resources and when to change the dbt models' materialization strategy. This took the guesswork out of the process and enabled team members to easily apply recommendations.

"Prescriptive model recommendations give an explicit ‘yes’ or ‘no’ [on] if we should increase the warehouse size," Elize explains. "We also have an explicit ‘yes’ or ‘no’ that we check after a week of running on that increased warehouse to ensure that we actually saw the results that we want."

This approach not only improved the performance of her dbt project but also had a positive human impact, empowering team members to take ownership and deepen their understanding of the platform.

Performance analysis can highlight areas for refactoring and improve resource usage

The performance analysis also helped Elize’s team identify which models were prime candidates for refactoring. By understanding which models weren’t benefiting from increased compute resources, they could direct their efforts more effectively and allocate resources where they were most needed.

"Models with failed optimization experiments become our prime candidates for refactoring. We're putting our human time to the best use that we can by going through this exercise," explains Elize.

As a result of their optimization efforts, Elize’s team reduced their average dbt Cloud runtime by 45%, resulting in significant savings. This underscores the benefits of a data-driven approach to performance optimization and resource allocation.

Insights surfaced

• dbt Lab’s team optimized 26 models in their project and saw a 45% drop in their dbt Cloud job time
• The team was able to save approximately $22,000 annually, despite using a larger warehouse
• They found that having a prescriptive recommendation for optimization was important for standardization across the team
• The team started to move deeper in the stack, turning Snowflake query profiles into tangible dbt modeling choices
• They were able to identify which models were best candidates for refactoring, making efficient use of their limited resources