Optimize Dbt Models | Skills PoolOptimize Dbt Models
优化和重构DBT模型以提升性能。提供查询性能分析、SQL优化、索引建议、物化策略调整和增量模型实现。
When to Use
Use this skill when:
- Optimizing dbt model performance
- Refactoring SQL for better performance
- Adjusting materialization strategies
- Implementing incremental models
Instructions
- Review Model Config -
execute("dbt show --select <model>")
- Check Compilation -
execute("dbt compile --select <model>")
- Parse Model -
execute("dbt parse")
- Optimize SQL - Refactor SQL for better performance
- Recommend Configuration - Suggest materialization improvements
- Final Diagnosis -
execute("dbt debug") to verify project health
Optimization Techniques
Query Optimization:
- Eliminate unnecessary columns
Configuration Optimization:
- Use incremental materialization for large tables
- Add partitioning strategies
- Enable sorting and clustering
- Configure unique keys for incremental models
Infrastructure Optimization:
- Add database indexes on join keys
- Use query result caching
- Optimize warehouse cluster size
- Schedule heavy jobs during off-peak hours
⚠️ CRITICAL: Avoid Deep Copy Operations
DO NOT use deepcopy on large data structures:
❌ WRONG (causes timeout):
import copy
all_tests = copy.deepcopy(tests) # Times out on 100+ tests
✅ CORRECT (process in batches):
# Process 5 columns at a time
for batch in chunks(columns, 5):
generate_tests(batch)
If project has 50+ models, warn user and suggest specific models.
IMPORTANT: After completing all tool calls, you MUST provide a natural language summary to the user.
Say something like:
"✅ DBT 模型优化完成！优化摘要：
- 优化的模型：[列出优化的模型]
- SQL 优化：[描述 SQL 改进]
- 配置优化：[物化策略、分区等]
- 性能提升预估：[预期改进]
- 验证结果：[编译是否成功]"
DO NOT end with just the tool call - your final message must be a text summary!
02
Instructions