Data Warehouse Cost Optimisation: How to Cut Cloud Warehouse Spend Without Cutting Capability

Cloud data warehouse spend follows a predictable pattern in most organisations: low initial cost during implementation, steady growth as adoption increases, and then a plateau or spike that prompts the question "why is this so expensive?" By that point, the cost has often become entangled with workloads that could have been optimised much earlier with less effort.

Cost optimisation in cloud data warehouses is technical work — specific, diagnosable, and fixable. The spend drivers are well-understood, and the interventions for each are defined.

Snowflake Cost Optimisation

Snowflake cost has two components: storage and compute. Storage is rarely the dominant cost driver (Snowflake's columnar compression is efficient); compute is where the spend accumulates.

**Auto-suspend and auto-resume configuration**: Virtual warehouses that run continuously when idle consume credits proportional to their size for no benefit. Set auto-suspend to 60-120 seconds for warehouses used for interactive queries (short idle periods between queries); 5-10 minutes for data engineering warehouses (longer jobs with occasional gaps). A warehouse running idle costs the same as one running queries.

**Right-sizing virtual warehouses**: Snowflake's billing model charges by warehouse size per second. A query that takes 60 seconds on an XS warehouse takes approximately 30 seconds on a Small warehouse but costs the same (Small is 2x the credit consumption rate). For long-running transformation queries that would benefit from larger warehouse compute, the cost is the same; for short queries on small data, smaller warehouses run them adequately at lower cost. Profile which queries run on which warehouses and match warehouse size to actual query requirements.

**Query result caching**: Snowflake caches query results for 24 hours. An identical query run within the cache period returns results instantly at zero compute cost. Dashboard workloads that run the same queries repeatedly (business users loading the same dashboard on the same day) benefit from caching automatically. Do not disable the query result cache; confirm it is enabled and understand when it is and is not being used.

**Clustering keys for large tables**: Snowflake micro-partitions are stored in the order data was inserted. For tables where queries consistently filter on a specific dimension (date range, customer ID, region), the lack of clustering means Snowflake scans more micro-partitions than necessary. Automatic clustering (defined clustering key) maintains optimal micro-partition organisation at the cost of background clustering credits. For tables queried heavily on a specific dimension, clustering reduces compute per query; the break-even depends on query volume and table size.

**Materialized views for frequent expensive queries**: If specific expensive queries are run many times per day against the same base table, materialising the result as a Snowflake Materialized View reduces compute for each run. The trade-off is the background refresh cost of the materialised view, which must be less than the accumulated savings from query execution.

BigQuery Cost Optimisation

BigQuery on-demand pricing charges per terabyte scanned. The most direct cost lever is reducing bytes scanned per query.

**Partitioning**: Partition tables by a date or timestamp column. Queries that include a filter on the partition column scan only the relevant partitions, not the full table. A daily partition on a 5-year table of events reduces scanned data by 365x for a single-day query. This is the single highest-leverage optimisation for BigQuery cost reduction.

**Clustering**: Within a partition, BigQuery clusters data by specified columns. Queries filtering on clustered columns scan fewer blocks within the partition. Clustering by the most common filter columns (customer_id, product_id, region) reduces scanned bytes further.

**Column selection**: BigQuery charges for columns scanned, not rows. SELECT * scans all columns; SELECT only the columns needed scans fewer bytes. In BigQuery, 'SELECT * FROM large_table' is expensive; 'SELECT col1, col2 FROM large_table WHERE date = current_date' is cheap. Train analysts and dashboard developers on this billing implication.

**Materialized views for aggregation**: BigQuery Materialized Views maintain pre-aggregated results that are transparently substituted when a query would benefit from them. A dashboard that displays daily summary metrics benefits from a materialized view that pre-aggregates the underlying event data, reducing the scan from raw events to pre-computed summaries.

**Table expiration for transient data**: Staging tables and intermediate analysis tables that are only needed for a defined period should have expiration dates set. Tables that persist indefinitely accumulate storage cost; automatic expiration removes the manual hygiene requirement.

**Commitment discounts**: BigQuery's flat-rate pricing (capacity commitment) converts variable per-query cost to a fixed slot reservation. For predictable, high-query-volume workloads, flat-rate can produce significant savings over on-demand. The break-even analysis depends on your actual query volume; run it with 3 months of billing history to validate the projection.

Redshift Cost Optimisation

Redshift ra3 nodes run continuously regardless of query activity. The primary cost levers are node right-sizing, workload management, and Spectrum.

**Right-sizing node count**: Redshift charges per node-hour. Reduce the node count to the minimum that meets performance requirements, and use Concurrency Scaling for burst capacity rather than maintaining peak-capacity node count continuously. A cluster that handles normal load with 4 nodes and uses Concurrency Scaling for the 20% of time when demand spikes costs less than a 6-node cluster maintained at all times.

**Reserved instance purchasing**: Redshift reserved instances (1-year or 3-year) reduce on-demand node-hour costs by 30-75% depending on term and payment method. For stable, long-running clusters, reserved instances are the most straightforward cost reduction available. The trade-off is the commitment term.

**Redshift Spectrum for cold data**: Cold data (historical data queried rarely) stored in S3 and queried via Redshift Spectrum is charged per TB scanned at Spectrum rates, not at node-hour rates. Moving cold historical data to S3 and out of Redshift storage reduces the storage component and may allow reducing node count. The trade-off is Spectrum query performance, which is generally slower than in-cluster queries for complex joins.

**WLM queue management**: Runaway queries that consume cluster resources for hours block other workloads and run up compute costs for no benefit. WLM time limits kill queries that exceed a defined execution window. Set time limits appropriate to workload type: dashboard queries should complete in seconds; long-running ETL queries have different limits.

Our cloud engineering practice conducts warehouse cost optimisation assessments using billing data and query history — contact us to discuss reducing your cloud data warehouse spend.

A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.