Driving Down Costs for a B2B Marketplace Analytics Team
B2B Software • Warehouse Optimization • Cost Reduction
dbt
Snowflake
Prefect
Overview
Over the course of 6 weeks we helped an analytics team at a B2B Software company cut their Snowflake costs by $40,000 / year. We worked closely to audit their warehouse and designed a plan to implement optimizations without causing any downtime. We also dramatically improved their data processing - what used to take hours now takes minutes. We were able to get rid of hundreds of unnecessary tables, which made the daily work much easier for the Analysts who are building models and reporting downstream of our work.
What we implemented
- Warehouse sizing optimization: Analyzed query patterns and workload characteristics to eliminate unnecessary tables and processes resulting in a more simplified, lighter-weight DAG.
- dbt pipeline refactoring: Refactored or redesigned dbt models to minimize redundant computations, implemented incremental models where appropriate to reduce compute expense and runtime.
- Query performance tuning: Identified and optimized expensive queries through better join strategies and clustering optimization to reduce data scanning and processing overhead as well as faster wall-clock times for analysts running ad hoc queries.
- Orchestration improvements: Implemented new Prefect triggers to support both incremental and legacy dbt models.
- Comprehensive documentation: Authored new documentation to help the team support and implement microbatch processing, how to use Snowflake notebooks for validation, and how to use Jinja to perform validation of new dbt models more efficiently.