dbt Query Performance Optimization AI Prompt

Optimize slow dbt models for this warehouse.

Slow model: {{model_name}}
Current runtime: {{runtime}} seconds
Warehouse: {{warehouse}}
Model type: {{model_type}} (incremental, full table, view)

1. Diagnose the bottleneck:
   - Run: dbt build --select {{model_name}} and check the query profile in the warehouse console
   - Identify: full table scans, missing clustering/partitioning, large cross-joins, excessive CTEs

2. Partitioning and clustering:

   BigQuery:
   config(
     partition_by={"field": "order_date", "data_type": "date"},
     cluster_by=["customer_id", "order_status"]
   )

   Snowflake:
   config(
     cluster_by=['TO_DATE(order_date)', 'order_status']
   )

   Redshift:
   config(
     sort=['order_date'],
     dist='customer_id'
   )

3. Incremental optimization:
   - Ensure the WHERE clause in the incremental filter uses the partition column
   - Wrong: WHERE id > (SELECT MAX(id) FROM {{this}}) — full table scan on the source
   - Right: WHERE updated_at >= (SELECT MAX(updated_at) FROM {{this}}) — if updated_at is the partition key

4. CTE vs temp table trade-off:
   - Many nested CTEs can confuse the optimizer on some warehouses
   - Snowflake: CTEs are generally fine
   - BigQuery: deeply nested CTEs with repeated references can be slow — consider intermediate tables
   - Redshift: complex CTEs may benefit from being broken into separate models

5. Reduce data early:
   - Push filters as early as possible in the CTE chain
   - Do not JOIN before filtering: filter first, then join
   - Avoid SELECT * in intermediate CTEs — project only needed columns

6. Warehouse-specific tuning:
   Snowflake: configure warehouse size per model:
   config(snowflake_warehouse='LARGE_WH')

   BigQuery: enable BI Engine for sub-second queries on frequently used tables

   Redshift: ANALYZE after large loads; VACUUM for reclaiming deleted rows space

Return: diagnosis approach, partitioning / clustering config for the warehouse, incremental filter optimization, and CTE vs table strategy.