# Dbt Performance

> Transform AI agents into experts on dbt and Snowflake performance optimization, providing guidance Use when this capability is needed.

- Skill: `tomevault-io/dbt-performance` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add tomevault-io/dbt-performance`
- Raw SKILL.md: https://api.skillmd.com/api/skills/tomevault-io/dbt-performance/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: tomevault-io (https://skillmd.com/u/tomevault-io)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/tomevault-io/dbt-performance

---


# dbt Performance Optimization

## Purpose

Transform AI agents into experts on dbt and Snowflake performance optimization, providing guidance
on choosing optimal materializations, leveraging Snowflake-specific features, and implementing query
optimization patterns for production-grade performance.

## When to Use This Skill

Activate this skill when users ask about:

- Optimizing slow dbt model builds
- Choosing appropriate materializations for performance
- Implementing Snowflake clustering keys
- Sizing warehouses appropriately
- Converting models to incremental for performance
- Optimizing query patterns and SQL
- Troubleshooting performance bottlenecks
- Using Snowflake performance features (Gen2, query acceleration, search optimization)

**Official Snowflake Documentation**:
[Query Performance](https://docs.snowflake.com/en/user-guide/performance-query)

---

## Materialization Performance

### Choose the Right Materialization

| Materialization | Build Time | Query Time | Best For                       |
| --------------- | ---------- | ---------- | ------------------------------ |
| **ephemeral**   | Fast       | Varies     | Staging, reusable logic        |
| **view**        | Instant    | Slow       | Always-fresh simple transforms |
| **table**       | Slow       | Fast       | Dimensions, complex logic      |
| **incremental** | Fast       | Fast       | Large facts (millions+ rows)   |

**Guidelines**:

- Use `ephemeral` for staging (fast, no storage)
- Use `table` for dimensions
- Use `incremental` for large facts

---

### When to Change Materializations

#### Change Ephemeral/View to Table When

**1. Memory Constraints**

Queries failing or running slowly due to memory limitations:

```sql
-- Change from ephemeral to table
{{ config(materialized='table') }}
```

**2. CTE Reuse**

Same intermediate model referenced multiple times downstream:

```sql
-- If int_customers__metrics is used by 3+ downstream models
{{ config(materialized='table') }}  -- Materialize to avoid re-computation
```

**3. Functions on Join Columns**

Transformations in JOIN conditions prevent optimization:

```sql
-- ❌ BAD: Functions on join columns (forces full table scans)
select *
from {{ ref('stg_customers') }} c
join {{ ref('stg_orders') }} o
  on upper(trim(c.customer_email)) = upper(trim(o.customer_email))

-- ✅ GOOD: Materialize cleaned columns as a table first
{{ config(materialized='table') }}

select
  customer_id,
  upper(trim(customer_email)) as customer_email_clean  -- Pre-compute once
from {{ ref('stg_customers') }}
```

**Performance Impact**: Ephemeral → Table trades storage for compute efficiency

---

#### Change Table to Incremental When

**1. Large Data Volumes**

Table has millions+ rows and full refreshes take too long:

```sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    cluster_by=['order_date']
) }}

select * from {{ ref('stg_orders') }}

{% if is_incremental() %}
    where order_date > (select max(order_date) from {{ this }})
{% endif %}
```

**2. Append-Only Data**

Event logs, clickstreams, transaction history

**3. Time-Based Updates**

Daily/hourly data loads with time-based filtering

**Performance Impact**: Table → Incremental reduces build time at cost of added complexity

---

## Snowflake-Specific Optimizations

### Clustering Keys

**Improve query performance on large tables:**

```sql
{{ config(
    materialized='table',
    cluster_by=['order_date', 'customer_id']
) }}
```

**Best Practices**:

- Use 1-4 columns maximum
- Order columns by cardinality (low to high)
- Include common WHERE clause columns
- Include JOIN key columns
- Monitor cluster usage: `SYSTEM$CLUSTERING_INFORMATION()`

**Example with Multiple Keys:**

```sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    cluster_by=['event_date', 'event_type', 'user_id']
) }}
```

**Official Snowflake Docs**:
[Clustering Keys](https://docs.snowflake.com/en/user-guide/tables-clustering-keys)

---

### Warehouse Sizing

```sql
{{ config(
    snowflake_warehouse='LARGE_WH'  -- For complex transformations
) }}
```

**Optimal Sizing Goal: ~500 Micropartitions (MPs) per Node**

Snowflake stores data in micropartitions (~16MB compressed). The warehouse sizing goal is to
maintain approximately 500 MPs scanned per node for optimal performance. Too few MPs per node
underutilizes the warehouse; too many causes compute skew and spilling.

**Sizing Formula:**

```sql
Warehouse Size Needed = Total MPs Scanned / 500
```

**Quick Reference Table** (MPs scanned → Recommended warehouse):

| MPs Scanned | Warehouse Size | Nodes | MPs per Node |
| ----------- | -------------- | ----- | ------------ |
| 500         | XS             | 1     | 500          |
| 1,000       | S              | 2     | 500          |
| 2,000       | M              | 4     | 500          |
| 4,000       | L              | 8     | 500          |
| 8,000       | XL             | 16    | 500          |
| 16,000      | 2XL            | 32    | 500          |
| 32,000      | 3XL            | 64    | 500          |
| 64,000      | 4XL            | 128   | 500          |

**How to Find MPs Scanned:**

```sql
-- Check query profile after running model
SELECT
    query_id,
    total_elapsed_time,
    partitions_scanned
FROM snowflake.account_usage.query_history
WHERE start_time >= dateadd(day, -1, current_timestamp())
  and query_text ILIKE '%your_model_name%'
ORDER BY start_time DESC
LIMIT 1;
```

**Practical Guidelines**:

- **Under-sized**: If MPs per node > 1000, consider larger warehouse
- **Over-sized**: If MPs per node < 250, consider smaller warehouse
- **Development**: Start with XS-S, profile, then adjust
- **Production**: Size based on actual MP scan metrics from query history

**Official Snowflake Docs**:
[Warehouse Considerations](https://docs.snowflake.com/en/user-guide/warehouses-considerations)

---

### Generation 2 Standard Warehouses

Gen2 standard warehouses offer improved performance for most dbt workloads.

**Why Gen2 is Better for dbt Projects**:

- **Faster transformations**: Enhanced DELETE, UPDATE, MERGE, and table scan operations (critical
  for incremental models and snapshots)
- **Delta Micropartitions**: Snowflake does not rewrite entire micropartitions for changed data
- **Faster Underlying Hardware**: Majority of queries finish faster, can do more work simultaneously
- **Analytics optimization**: Purpose-built for data engineering and analytics workloads

**Converting to Gen2:**

```sql
-- Run directly in Snowflake
ALTER WAREHOUSE TRANSFORMING_WH
SET RESOURCE_CONSTRAINT = STANDARD_GEN_2;
```

**Official Snowflake Docs**:
[Gen2 Standard Warehouses](https://docs.snowflake.com/en/user-guide/warehouses-gen2)

---

### Search Optimization Service

**For point lookups and selective filters on large tables:**

```sql
{{ config(
    post_hook=[
        "alter table {{ this }} add search optimization on equality(customer_id, email)"
    ]
) }}
```

**When to Use**:

- Point lookups (WHERE customer_id = ?)
- Selective filters on large tables
- High-cardinality columns

**Official Snowflake Docs**:
[Search Optimization](https://docs.snowflake.com/en/user-guide/search-optimization-service)

---

### Query Acceleration Service

Query Acceleration is configured at the warehouse level, not in dbt models:

```sql
-- Run directly in Snowflake
ALTER WAREHOUSE TRANSFORMING_WH
SET ENABLE_QUERY_ACCELERATION = TRUE;

-- Set scale factor (optional)
ALTER WAREHOUSE TRANSFORMING_WH
SET QUERY_ACCELERATION_MAX_SCALE_FACTOR = 8;
```

**When to Use**:

- Queries with unpredictable data volume
- Ad-hoc analytics workloads
- Queries that scan large portions of tables
- Variable query complexity

**Official Snowflake Docs**:
[Query Acceleration](https://docs.snowflake.com/en/user-guide/query-acceleration-service)

---

### Result Caching

Snowflake automatically caches query results for 24 hours.

**Best Practices**:

- Use consistent query patterns to leverage cache
- Avoid unnecessary `current_timestamp()` in WHERE clauses (breaks cache)
- Identical queries return cached results instantly

**Example to Preserve Cache:**

```sql
-- ❌ Breaks cache every run
where created_at > current_timestamp() - interval '7 days'

-- ✅ Preserves cache (use dbt variables)
where created_at > '{{ var("lookback_date") }}'
```

---

## Incremental Model Performance

### Efficient WHERE Clauses

```sql
{% if is_incremental() %}
    -- ✅ Good: Partition pruning
    where order_date >= (select max(order_date) from {{ this }})

    -- ✅ Good: With lookback for late data
    where order_date >= dateadd(day, -3, (select max(order_date) from {{ this }}))

    -- ✅ Good: Limit source scan
    where order_date >= dateadd(day, -30, current_date())
      and order_date > (select max(order_date) from {{ this }})
{% endif %}
```

### Incremental Strategy Performance

| Strategy          | Speed   | Use Case             |
| ----------------- | ------- | -------------------- |
| **append**        | Fastest | Immutable event data |
| **merge**         | Medium  | Updateable records   |
| **delete+insert** | Fast    | Partitioned data     |

**Choose based on data characteristics:**

- Append: Event logs, clickstreams (never update)
- Merge: Orders, customers (updates possible)
- Delete+Insert: Date-partitioned aggregations

---

## Query Optimization Tips

### Filter Before Joining

```sql
-- ✅ Good: Filter before joining
with filtered_orders as (
    select * from {{ ref('stg_orders') }}
    where order_date >= '2024-01-01'
)

select
    c.customer_id,
    count(o.order_id) as order_count
from {{ ref('dim_customers') }} c
join filtered_orders o on c.customer_id = o.customer_id
group by c.customer_id
```

**Why It Works**: Reduces join size, improves memory efficiency

---

### Use QUALIFY for Window Functions

```sql
-- ✅ Good: Snowflake QUALIFY clause (cleaner & faster)
select
    customer_id,
    order_date,
    order_amount,
    row_number() over (partition by customer_id order by order_date desc) as rn
from {{ ref('stg_orders') }}
qualify rn <= 5  -- Top 5 orders per customer

-- ❌ Slower: Subquery approach
select * from (
    select
        customer_id,
        order_date,
        row_number() over (partition by customer_id order by order_date desc) as rn
    from {{ ref('stg_orders') }}
)
where rn <= 5
```

**Why QUALIFY is Better**: Single scan, no subquery overhead

---

### Pre-Aggregate Before Joining

```sql
-- ✅ Good: Aggregate first, then join
with order_metrics as (
    select
        customer_id,
        count(*) as order_count,
        sum(order_total) as lifetime_value
    from {{ ref('stg_orders') }}
    group by customer_id
)

select
    c.*,
    coalesce(m.order_count, 0) as order_count,
    coalesce(m.lifetime_value, 0) as lifetime_value
from {{ ref('dim_customers') }} c
left join order_metrics m on c.customer_id = m.customer_id
```

**Why It Works**: Reduces join size dramatically, avoids repeated aggregation

---

### Avoid SELECT \*

```sql
-- ❌ Bad: Reads all columns
select *
from {{ ref('fct_orders') }}
where order_date = current_date()

-- ✅ Good: Select only needed columns
select
    order_id,
    customer_id,
    order_date,
    order_amount
from {{ ref('fct_orders') }}
where order_date = current_date()
```

**Why It Matters**: Column pruning reduces data scanned and network transfer

---

## Development vs Production

### Limit Data in Development

**Create macro for dev data limiting:**

```sql
-- macros/limit_data_in_dev.sql
{% macro limit_data_in_dev(column_name, dev_days_of_data=3) %}
    {% if target.name == 'dev' %}
        where {{ column_name }} >= dateadd(day, -{{ dev_days_of_data }}, current_date())
    {% endif %}
{% endmacro %}
```

**Usage:**

```sql
select * from {{ ref('stg_orders') }}
{{ limit_data_in_dev('order_date', 7) }}
```

---

### Target-Specific Configuration

```yaml
# dbt_project.yml
models:
  your_project:
    gold:
      # Use views in dev, tables in prod
      +materialized: "{{ 'view' if target.name == 'dev' else 'table' }}"
```

**Benefits**:

- Faster dev builds
- Lower dev costs
- Prod stays optimized

---

## Query Profiling

### Snowflake Query Profile

**View in Snowflake UI:**

1. Go to Query History
2. Select your query
3. Click "Query Profile" tab
4. Analyze execution plan

**Query history with performance metrics:**

```sql
select
    query_id,
    query_text,
    execution_time,
    bytes_scanned,
    warehouse_name,
    partitions_scanned
from snowflake.account_usage.query_history
where user_name = current_user()
  and start_time >= dateadd(day, -7, current_date())
order by execution_time desc
limit 100;
```

---

### dbt Timing Information

```bash
# Run with timing details
dbt run --select model_name --log-level debug

# View run timing
cat target/run_results.json | jq '.results[].execution_time'
```

**Analyze slow models:**

```bash
# Find slowest models
cat target/run_results.json | jq -r '.results[] | [.execution_time, .unique_id] | @tsv' | sort -rn | head -10
```

---

## Performance Checklist

### Model-Level Optimization

- [ ] Appropriate materialization chosen (ephemeral/table/incremental)
- [ ] Clustering keys applied for large tables (1-4 columns)
- [ ] Incremental strategy optimized (append/merge/delete+insert)
- [ ] WHERE clauses filter early (before joins)
- [ ] JOINs are necessary and optimized (filter before join)
- [ ] SELECT only needed columns (no SELECT \*)
- [ ] Window functions use QUALIFY when possible

### Project-Level Optimization

- [ ] Staging models are ephemeral (no storage overhead)
- [ ] Large facts are incremental (faster builds)
- [ ] Dev environment uses limited data (faster iteration)
- [ ] Warehouse sizing appropriate per model complexity
- [ ] Gen2 warehouses enabled for transformations
- [ ] Regular performance reviews scheduled
- [ ] Clustering monitored and maintained

---

## Helping Users with Performance

### Strategy for Assisting Users

When users report performance issues:

1. **Identify the bottleneck**: Build time? Query time? Both?
2. **Check materialization**: Is it appropriate for model size/purpose?
3. **Review query patterns**: Are there obvious inefficiencies?
4. **Assess warehouse sizing**: Using appropriate compute for workload?
5. **Recommend optimizations**: Specific, actionable improvements
6. **Provide examples**: Working code with performance comparisons

### Common User Questions

**"My model is slow to build"**

- Check materialization: Should it be incremental?
- Review warehouse size: Appropriate for data volume?
- Analyze query: Are there inefficient patterns?
- Check clustering: Would it help query performance?

**"How do I make this faster?"**

- Change ephemeral to table if reused multiple times
- Convert table to incremental for large datasets
- Add clustering keys for frequently filtered columns
- Pre-aggregate before joining
- Use QUALIFY instead of subqueries

**"What warehouse size should I use?"**

- Profile the query to see MPs scanned
- Aim for ~500 MPs per warehouse node
- Start small, scale up based on actual metrics
- Use `snowflake_warehouse` config for model-specific sizing

---

## Related Official Documentation

- [Snowflake Docs: Query Performance](https://docs.snowflake.com/en/user-guide/performance-query)
- [Snowflake Docs: Clustering](https://docs.snowflake.com/en/user-guide/tables-clustering-keys)
- [Snowflake Docs: Gen2 Warehouses](https://docs.snowflake.com/en/user-guide/warehouses-gen2)
- [dbt Docs: Best Practices](https://docs.getdbt.com/guides/best-practices)

---

**Goal**: Transform AI agents into expert dbt performance optimizers who identify bottlenecks,
recommend appropriate optimizations, and implement Snowflake-specific features for production-grade
performance.

---
> Converted and distributed by [TomeVault](https://tomevault.io/claim/sfc-gh-dflippo) — claim your Tome and manage your conversions.
<!-- tomevault:4.0:skill_md:2026-04-11 -->