Warehouse Optimization

Warehouse Optimization

I'll help you reduce costs and improve query performance through appropriate clustering, partitioning, materialization strategies, and query rewrites.

Check Context First

Read .claude/data-stack-context.md. Key inputs: warehouse type, data volume, cost constraints, slowest queries.

Before optimizing, run node tools/clis/model-stats.js --manifest target/manifest.json to see row counts and materialization types, and node tools/clis/cost-estimate.js --help to estimate the cost impact of changes.

Before You Start

• Run node tools/clis/cost-estimate.js --help to identify the most expensive queries before making any changes.
• Run node tools/clis/model-stats.js --manifest target/manifest.json to find the largest tables by row count and bytes.
• Read the warehouse-specific integration guide in tools/integrations/ for your warehouse type.
• Record baseline query execution times and costs before optimizing so you can measure improvement.

Diagnostic First: Find the Expensive Queries

Before optimizing, measure what's actually slow or expensive.

Snowflake Query History

-- Top 10 most expensive queries (last 7 days)
select
    query_text,
    user_name,
    warehouse_name,
    total_elapsed_time / 1000 as elapsed_seconds,
    bytes_scanned / 1e9 as gb_scanned,
    partitions_scanned,
    partitions_total,
    100 * partitions_scanned / nullif(partitions_total, 0) as pct_partitions_scanned
from snowflake.account_usage.query_history
where start_time >= current_timestamp - interval '7 days'
  and query_type = 'SELECT'
  and total_elapsed_time > 10000  -- > 10 seconds
order by bytes_scanned desc
limit 10;

BigQuery Cost Analysis

-- BigQuery: top tables by bytes billed (last 30 days)
SELECT
    destination_table.table_id,
    SUM(total_bytes_billed) / 1e12 AS total_tb_billed,
    SUM(total_bytes_billed) / 1e12 * 5 AS estimated_cost_usd,  -- $5/TB on-demand
    COUNT(*) AS query_count
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND statement_type = 'SELECT'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20

---

Snowflake Optimization

Clustering Keys

-- When to cluster:
-- Table > 100GB AND common filter/join column with high cardinality

-- Check current clustering effectiveness
select system$clustering_information('analytics.marts.fct_events', '(event_date)');

-- Add clustering
alter table fct_events cluster by (event_date::date);

-- Enable automatic clustering (for > 500GB tables with frequent scans)
alter table fct_events enable automatic clustering;

-- In dbt:
{{ config(
    materialized='table',
    cluster_by=['event_date', 'user_id']  -- up to 4 columns
) }}

Clustering key selection:

• Use the most common filter column (usually a date)
• Add a high-cardinality join column as second key
• Don't cluster on boolean or low-cardinality columns
• Verify: partitions_scanned / partitions_total < 0.2 for good clustering

Warehouse Sizing

-- Right-size warehouses by query type
-- Use INFORMATION_SCHEMA to find per-warehouse cost

select
    warehouse_name,
    sum(credits_used) as total_credits,
    sum(credits_used) * 2 as estimated_cost_usd,  -- $2/credit enterprise
    count(*) as query_count,
    avg(total_elapsed_time) / 1000 as avg_seconds
from snowflake.account_usage.query_history
where start_time >= current_date - 30
group by 1
order by 2 desc;

Warehouse strategy:

• LOADING warehouse: X-Small or Small — bulk COPY INTO ops
• TRANSFORMING warehouse: Medium or Large — dbt builds
• REPORTING warehouse: X-Small or Small — BI tool queries (auto-suspend in 60s)
• ANALYST warehouse: Small — ad-hoc queries

-- Configure auto-suspend and auto-resume
alter warehouse REPORTING set
    auto_suspend = 60     -- suspend after 1 minute of inactivity
    auto_resume = true    -- resume automatically on query
    min_cluster_count = 1
    max_cluster_count = 3  -- Multi-cluster for concurrent BI users
    scaling_policy = 'ECONOMY';

Query Rewrites

-- ❌ Expensive: non-SARGable filter (function on column)
where year(created_at) = 2024
-- ✅ Cheaper: range filter allows partition pruning
where created_at >= '2024-01-01' and created_at < '2025-01-01'

-- ❌ Expensive: SELECT * on large table
select * from fct_events
-- ✅ Select only needed columns
select event_id, user_id, event_type, event_date from fct_events

-- ❌ Expensive: DISTINCT on large table without filter
select distinct user_id from fct_events
-- ✅ Use COUNT(DISTINCT) only at aggregation time, or pre-aggregate
select date_trunc('day', event_date), approx_count_distinct(user_id)
from fct_events group by 1

-- ❌ Expensive: JOIN on expression
join dim_customers on lower(o.email) = lower(c.email)
-- ✅ Normalize in staging, join on indexed column
join dim_customers on o.normalized_email = c.normalized_email

---

BigQuery Optimization

Partitioning

-- Always partition large tables
{{ config(
    partition_by={
        "field": "event_date",      -- use a date column
        "data_type": "date",
        "granularity": "day"        -- day | month | year
    },
    require_partition_filter=True   -- Force callers to filter on partition
) }}

-- Partition by ingestion time (when no good date column exists)
{{ config(
    partition_by={
        "field": "_PARTITIONTIME",
        "data_type": "timestamp",
        "granularity": "day"
    }
) }}

Clustering

-- Cluster in addition to partitioning for better scan efficiency
{{ config(
    partition_by={"field": "event_date", "data_type": "date"},
    cluster_by=["user_id", "event_type"]  -- up to 4 columns
) }}

BigQuery Cost Controls

-- Set per-query byte limit (prevents runaway queries)
-- In connection settings or via API:
-- maximumBytesBilled = 10_000_000_000  -- 10GB limit

-- Use approximate aggregates for analytics
select approx_count_distinct(user_id) from fct_events  -- Much cheaper than COUNT(DISTINCT)
select approx_quantiles(revenue_usd, 100)[offset(50)] as p50_revenue from fct_orders

-- Enable query cache (free for repeated identical queries)
-- Default: enabled. Only disabled with CURRENT_TIMESTAMP() in query

---

Databricks (Delta Lake) Optimization

-- OPTIMIZE: compact small files (run after bulk writes)
OPTIMIZE analytics.marts.fct_events
ZORDER BY (user_id, event_type)  -- Collocate related data

-- VACUUM: remove old Delta versions (default: 7-day retention)
VACUUM analytics.marts.fct_events RETAIN 168 HOURS

-- Auto-optimize (Delta Lake feature)
ALTER TABLE fct_events SET TBLPROPERTIES (
    'delta.autoOptimize.optimizeWrite' = 'true',
    'delta.autoOptimize.autoCompact' = 'true'
)

-- Bloom filter index for non-date filters
CREATE BLOOMFILTER INDEX ON TABLE fct_events FOR COLUMNS (user_id OPTIONS (fpp=0.1))

---

Redshift Optimization

-- Choose distribution style
{{ config(
    dist='customer_id',    -- DISTKEY: use join/group-by column most used in queries
    -- dist='all'           -- DISTSTYLE ALL: for small dimension tables (< 5M rows)
    -- dist='even'          -- DISTSTYLE EVEN: for staging or unknown access patterns
    sort=['created_at'],   -- SORTKEY: most common filter column
) }}

-- Vacuum and analyze after bulk loads
VACUUM DELETE ONLY analytics.fct_orders;
ANALYZE analytics.fct_orders;

-- Check table health
SELECT table_name, pct_used, unsorted, stats_off
FROM svv_table_info
WHERE schema = 'marts'
ORDER BY pct_used DESC;

---

DuckDB Optimization

DuckDB is columnar and vectorized by default — it's fast out of the box, but a few patterns matter at scale.

-- Check database size and table stats
PRAGMA database_size;
PRAGMA storage_info('fct_events');

-- EXPLAIN ANALYZE to see actual row counts and execution plan
EXPLAIN ANALYZE
SELECT user_id, count(*) FROM fct_events WHERE event_date >= '2024-01-01' GROUP BY 1;

-- Memory limit (important for large dbt runs)
SET memory_limit = '8GB';
SET threads = 4;  -- match to available CPU cores

-- Parallel reads from Parquet (extremely fast)
SELECT * FROM read_parquet('s3://my-bucket/events/*.parquet', hive_partitioning=true)
WHERE event_date >= '2024-01-01';

DuckDB-specific tips:

• No cluster/partition concept — focus on column projection and filter pushdown
• Use COPY for bulk loads, not INSERT
• Native SQL is faster than Python UDFs for heavy transforms

---

Snowflake Dynamic Tables

Dynamic Tables replace scheduled tasks + incremental models for near-real-time use cases. They refresh automatically with a configurable lag target.

-- Create a Dynamic Table with a 5-minute lag target
CREATE OR REPLACE DYNAMIC TABLE analytics.marts.fct_orders_live
    TARGET_LAG = '5 minutes'
    WAREHOUSE = TRANSFORMING
AS
SELECT
    o.order_id,
    o.customer_id,
    c.customer_segment,
    o.order_amount,
    o.created_at
FROM raw.orders o
JOIN analytics.staging.stg_customers c ON o.customer_id = c.customer_id;

In dbt (dbt-snowflake 1.6+):

{{ config(
    materialized='dynamic_table',
    target_lag='5 minutes',
    snowflake_warehouse='TRANSFORMING'
) }}

	Dynamic Tables	Incremental
Latency	Minutes	Hours (scheduled)
Cost control	Less predictable	Predictable
Complex transforms	Limited	Full dbt support
Late-arriving data	Automatic	Manual lookback

---

BigQuery Materialized Views

BigQuery Materialized Views pre-compute and cache aggregation results, refreshing automatically when base tables change.

CREATE MATERIALIZED VIEW analytics.marts.mv_daily_revenue
OPTIONS (enable_refresh = true, refresh_interval_minutes = 60)
AS
SELECT
    DATE(ordered_at) as order_date,
    customer_segment,
    SUM(net_revenue) as total_revenue,
    COUNT(*) as order_count
FROM analytics.marts.fct_orders
GROUP BY 1, 2;

In dbt (BigQuery adapter):

{{ config(
    materialized='materialized_view',
    enable_refresh=true,
    refresh_interval_minutes=60
) }}

Use MVs for stable rollup-heavy BI queries. Use incremental models when you need dbt tests, docs, and full DAG lineage.

---

dbt Materialization Strategy

# dbt_project.yml — set materializations by layer

models:
  my_project:
    staging:
      +materialized: view          # Views are free to query
    intermediate:
      +materialized: ephemeral     # No table created; inlined into final query
    marts:
      +materialized: table         # Materialize marts for performance
      core:
        fct_events:                # Override: large event table — incremental
          +materialized: incremental
          +incremental_strategy: merge

Materialization cost comparison:

Materialization	Storage cost	Query cost	Rebuild cost
View	None	High (re-runs query)	None
Ephemeral	None	High (inlined)	None
Table	Medium	Low (pre-computed)	High (full rebuild)
Incremental	Medium	Low	Low (processes new data only)

---

Cost Alerting

# Snowflake: alert if daily spend exceeds threshold
# (via Snowflake Resource Monitor)

CREATE RESOURCE MONITOR daily_budget
WITH CREDIT_QUOTA = 100  -- 100 credits/day limit
TRIGGERS
    ON 75 PERCENT DO NOTIFY  -- Send email at 75%
    ON 100 PERCENT DO SUSPEND  -- Suspend at 100%
    ON 110 PERCENT DO SUSPEND_IMMEDIATE;

ALTER WAREHOUSE TRANSFORMING SET RESOURCE_MONITOR = daily_budget;

Verify Your Work

Do not present output from this skill as complete until every command below passes without error. If a command fails, consult "If Something Goes Wrong" before asking the user.

• Re-run node tools/clis/cost-estimate.js --help after applying optimizations to measure improvement against the baseline.
• Re-run node tools/clis/model-stats.js --manifest target/manifest.json to confirm table sizes and scan efficiency improved.
• For Snowflake: check partitions_scanned / partitions_total in query history — good clustering should bring this below 0.2.

If Something Goes Wrong

• Clustering not reducing scan cost: Verify the WHERE clause in expensive queries actually filters on the clustered column — clustering only helps when the filter column matches the cluster key.
• Partition pruning not working: Check that the filter is on the raw partition column, not a computed value (e.g., WHERE event_date >= '2024-01-01' works; WHERE DATE(event_timestamp) >= '2024-01-01' may not prune).
• Cost increased after change: A view may have been converted to a table — check the model's materialized config; tables incur storage costs and rebuild costs that views avoid.