Skip to main content

Warehouse Cost and Query Performance

Warehouse cost depends on platform pricing and workload, but useful principles are portable: scan fewer relevant bytes, reduce rows before expensive joins, avoid repeated work, bound concurrency, and measure plans on representative data. This lesson intentionally avoids invented cloud prices and tiny-fixture benchmark claims.

Prerequisites​

Complete SQL Debugging and Query Plans, Columnar Storage and Parquet, and Star Schemas.

Learning goals​

  • Connect bytes scanned, CPU, memory, shuffle, spill, and concurrency to workload cost.
  • Inspect estimated and actual query plans.
  • Apply partition pruning, projection, filtering, and aggregation deliberately.
  • Evaluate materialization and incremental-processing tradeoffs.

Worked example​

EXPLAIN inspects the plan without executing the aggregation. The query projects only amount_cents and filters one time range; on the in-memory fixture DuckDB still uses a sequential scan because the table is tiny.

EXPLAIN
SELECT SUM(amount_cents)
FROM fct_event
WHERE occurred_at >= TIMESTAMP '2026-10-02';

On real storage, compare estimated cardinality with EXPLAIN ANALYZE results and platform scan metrics. Partitioning can skip files only when predicates align with partition columns. Parquet row-group statistics can support pruning, but layout, data distribution, and engine capabilities matter. Selecting fewer columns reduces columnar I/O even when row counts do not change.

Model choices move work: a view recomputes on read, a table pays on build and storage, and an incremental table processes a subset while adding state and correction complexity. Measure total workload, including builds, tests, retries, BI concurrency, and downstream extracts. Cache effects and warm runs must be reported separately.

Limitations and common mistakes​

  • Runtime from a laptop-sized fixture does not predict warehouse performance.
  • More partitions can create metadata and small-file overhead.
  • Filtering after a many-to-many join may be too late to control fanout.
  • Materializing every intermediate model increases storage and maintenance.
  • A lower bill can hide missed freshness or quality objectives; optimize within service constraints.

Practice​

For a daily dashboard scanning a year of events, propose one layout change, one SQL change, and one materialization change. Name the metric that would confirm each improvement.

Next steps​

Finish the area with Project: Raw Events to an Analytics Mart, which integrates files, incremental publication, modeling, tests, and lineage.

Primary sources​

Checked 2026-10-15: DuckDB profiling, EXPLAIN, Parquet performance, and dbt incremental models.