dbt-performance_skill

Overview

This skill provides actionable guidance to optimize dbt models and Snowflake workloads for faster builds and queries. It focuses on materialization choices, clustering keys, warehouse sizing, incremental strategies, and query patterns to reduce scan, memory, and compute costs. Use it to diagnose slow builds, pick appropriate configs, and apply Snowflake-specific features for production-grade performance.

How this skill works

The skill inspects model patterns and recommends materialization changes (ephemeral, view, table, incremental) based on data volume, reuse, and join complexity. It analyzes Snowflake tuning levers: clustering keys, warehouse sizing using micropartition metrics, Gen2 conversions, search optimization, and query acceleration. It also suggests SQL-level optimizations—filter-before-join, QUALIFY for windowing, pre-aggregation, and avoiding SELECT *—and provides development vs production practices and profiling queries for root-cause analysis.

When to use it

• Model builds are slow or failing due to memory/compute limits
• Deciding between ephemeral/view/table/incremental materializations
• Sizing or right-sizing Snowflake warehouses for dbt workloads
• Implementing clustering keys or search-optimization on large tables
• Optimizing incremental models and merge strategies
• Troubleshooting high scan, skew, or long-running queries

Best practices

• Choose ephemeral for short-lived staging logic; materialize to table when reused or when joins use functions
• Convert large full-refresh tables to incremental with proper unique_key and partition predicates
• Use 1–4 clustering columns ordered by cardinality and common WHERE/JOIN predicates; monitor with SYSTEM$CLUSTERING_INFORMATION()
• Size warehouses aiming for ~500 micropartitions scanned per node; use query history to compute MPs scanned and adjust
• Filter before joins, pre-aggregate heavy inputs, use QUALIFY for top-N windowing, and select only needed columns
• Use dev targets with limited data and view materializations for fast iteration; keep prod as tables/incremental and schedule regular performance reviews

Example use cases

• Change an ephemeral staging model to table when the same logic is referenced by multiple downstream models
• Convert a multi-million-row fact table to an incremental model with merge strategy and a lookback window to handle late-arriving data
• Add cluster_by on order_date and customer_id for a large orders table to improve range filters and join performance
• Estimate warehouse size by retrieving partitions_scanned for a model and applying the MPs-per-node formula to pick an appropriate warehouse
• Apply search optimization for high-cardinality point lookups on customer_id and email, and use query acceleration for unpredictable heavy scans