hyperlakeDiscuss a deployment ↗
Blog · 9 min read

Incremental SQL for Warehouse Cost Optimization

Incremental models can reduce repeated full-refresh work when updates are handled correctly; check data correctness and freshness alongside compute.

Review warehouse cost drivers, idle compute, repeated transformations and retention policies before choosing an optimisation or migration path.

A tall cloud bill bar split into bursty and steady workloads, with the steady part moving to an on-premises server.

Try the cheap fixes before you migrate anything; most warehouse bills shrink without a move. To reduce Snowflake and cloud warehouse costs, measure usage, review idle compute, fix repeated transformations and check retention against what you actually need. Compare architectural changes only after that, with migration, infrastructure and operating effort counted in. No fixed number of active hours guarantees another platform will be cheaper.

*Disclosure: We make Hyperlake. This guide is our engineering assessment and links directly to each vendor's and project's official documentation.*

A tall cloud bill bar split into bursty and steady workloads, with the steady part moving to an on-premises server.

Which cost-reduction approach fits the workload?

Cost areaFirst actionConstraint to preserve
Idle computeReview activity and suspension settingsInteractive latency and cache behaviour
TransformationsAvoid unnecessary reprocessingCorrect handling of updates and late data
StorageReview copies and approved retentionRecovery and retention obligations
ArchitectureCompare complete operating costsService quality and migration effort

Step 1: How do you control idle compute and expensive queries?

Before you change a single setting, find out who owns each warehouse and what it's doing. Snowflake's cost-control documentation lays out the controls available. Pick settings against workload and service requirements, not by habit.

Auto-suspend can cut idle compute. Set it too aggressively and cache behaviour and interactive performance suffer. So measure the trade-off for each warehouse; one timeout everywhere is the wrong answer. Snowflake's warehouse-cache guidance explains how the two interact.

Who can resize warehouses, set timeouts and change resource controls? Alerts and limits need an owner who can tell a runaway job from a genuine rise in demand. Also check what each control actually covers, because one monitor rarely caps every charge.

Step 2: How do you cut transformation scan volume?

Picture a scheduled pipeline that scans and rewrites terabytes of unchanged data every hour. No idle-compute guardrail will catch it. In transformation-heavy environments that's usually where the real Snowflake warehouse cost sits, so the big savings come from changing how pipelines process data.

1. Replace full refreshes with incremental models

Running SQL incrementally instead of doing full refreshes is the main way to cut warehouse compute costs. Take a large fact table rebuilt every hour just to append new events: the warehouse scans, sorts and writes the whole historical table 24 times a day.

The dbt Developer Hub on Incremental Models documents the alternative. Configure models with materialized='incremental', and each run transforms only records that are new or updated since the last run. Entire tables aren't rebuilt through a full refresh.

Testing pull requests in CI? Pair incremental models with dbt clone in Slim CI jobs (state:modified+). That avoids full-refresh rebuilds of large incremental tables in pull-request test schemas.

2. Eliminate SELECT * and filter before joining

Snowflake and Google Cloud BigQuery are columnar: they store and scan data by column, and your queries should take advantage of that. Three habits do most of the work.

Skip SELECT * (or use SELECT * EXCEPT in BigQuery) so the engine stops reading every column when you need a few, and project only required columns in the earliest staging layer. Use table partitioning and clustering, and filter on partition or clustering keys before big joins so the query planner can prune blocks you don't need. In BigQuery, prefer batch loading to streaming inserts when you don't need real-time latency, and set table or partition expiration.

3. Align schedule cadence and warehouse size to business SLAs

Each step up in Snowflake warehouse size doubles the hourly credit rate (for example, X-Small = 1 credit/hour on Gen1, Small = 2 credits/hour, Medium = 4 credits/hour). Bigger only saves money if runtime falls in proportion.

Once a query hits diminishing returns, the smaller warehouse wins: 15 minutes on a Small warehouse at 2 credits/hour = 0.50 credits, against 10 minutes on a Medium at 4 credits/hour = 0.67 credits, so the smaller one uses 25% fewer credits. Schedule matters too. Moving a non-operational reporting pipeline from hourly (24 runs/day) to every 6 hours (4 runs/day) cuts run frequency by 83%.

How should retention and cold data be reviewed?

Recovery capability and regulatory retention can matter far more than one storage line item. So before you change a policy, list the active data, the historical copies and the retention requirements, and find the owner of each policy.

Never move production tables to a different durability class, or switch off retention, just to save money. Review the impact with the data owner and rehearse recovery first. Archive only data whose access pattern and retention obligations suit the destination.

When does it make sense to move workloads off a cloud data warehouse?

We wouldn't argue with the cloud warehouse on elastic, bursty analytics: compute spins up for a few minutes, handles a big scan and suspends. Four workload patterns strain metered warehouse economics, though, and can justify moving to open lakehouse storage (Apache Iceberg) or owned or reserved infrastructure.

1. Continuous, high-duty-cycle compute (sustained utilisation)

Steady-state work on an elastic consumption rate is the mismatch. Streaming ingestion, frequent micro-batch transformations or operational dashboards can keep virtual warehouses running all day. Once something needs round-the-clock CPU and memory, open query engines (Trino, ClickHouse or DuckDB) on reserved cloud instances, private cloud or on-premises hardware avoid per-credit compute markups.

2. High-frequency AI agent grounding and tool calls

AI agents, copilots and MCP-connected tools query structured tables, vector stores and semantic layers constantly. On a traditional cloud warehouse, two costs compound:

  • Wake-lock billing. Frequent sub-second agent lookups stop virtual warehouses ever reaching their 60-second AUTO_SUSPEND window.
  • Per-use AI multipliers. As agents multiply model calls, searches and jobs, per-use pricing can climb with every action. Run model serving (via KServe) beside fast analytical and vector engines (ClickHouse, PostgreSQL, Qdrant/Milvus, and Trino over Iceberg) on well-used owned capacity, and each extra action can cost relatively little, until you need more hardware.

3. Heavy local dev/test iterations and CI pipelines

Does a developer running exploratory SQL and dbt model tests all day need an enterprise cloud cluster for every iteration? No. Move local development and CI validation to embedded DuckDB reading Parquet files or object storage directly, and the idle cluster billing goes away.

4. Strict data residency, sovereignty, and cross-cloud egress

Raw telemetry, financial records or robotics/Physical AI sensor feeds often start life on-premises or in a specific jurisdiction. Moving terabytes of that into a public cloud warehouse adds transfer costs and compliance scope. Keep the data in open Apache Iceberg tables inside infrastructure you control, and both analytical SQL and private AI models can run where the data already is. Our guide on Data Residency vs. Data Sovereignty for Enterprise AI goes deeper.

Proposed Reference Architecture: Hybrid Warehouse Cost Offload to an Open Lakehouse

Architectural Label: The diagram below is a Proposed Reference Architecture. Bursty executive BI stays in an existing cloud warehouse, while continuous transformations, cold historical storage and 24/7 AI agent grounding move to an open Apache Iceberg lakehouse on Kubernetes.
Architecture connecting Ingestion & Streaming (Kafka / Batch Parquet Ingest), Open Object Storage (Cloud or On-Prem) Format: Apache Iceberg Tables, Existing Cloud Warehouse (Snowflake) - Auto-suspend = 60s & Resource Monitors - Used for bursty BI, Customer-Controlled Kubernetes Environment (Hyperlake) - Trino: Lakehouse analytics over Iceberg - ClickHouse: Fast analytics & agent queries - Qdrant/Milvus + KServe: Vectors & open models - Access: OAuth/OIDC -> JWT -> OPA.

The data sits in Apache Iceberg format on object storage in your environment. Snowflake reads the Iceberg tables for occasional executive dashboards (AUTO_SUSPEND = 60). Everything sustained runs on Kubernetes: lakehouse analytics (Trino), fast analytical queries (ClickHouse), and vector and model services (Qdrant/Milvus and KServe). No per-second warehouse credit markups apply there.

How should a cost comparison be calculated?

Same workload, same accounting period. Separate compute, storage, managed services, transfer and operating effort. Cutting warehouse runtime by some percentage doesn't cut the total bill by the same amount.

A hypothetical illustration: taking a workload from 200 to 150 compute-hours is a 25% reduction in hours. What that's worth in money depends on the rate you pay and on anything else that changed. These are arithmetic assumptions, not a measured customer result or a vendor price.

Keep a before-and-after record that covers output correctness and service performance as well as cost. A cheaper job that misses its delivery window hasn't met the original requirement.

Where Hyperlake fits (and when to stay in your cloud warehouse)

Hyperlake is the sovereign AI operating platform. Enterprise and platform teams use it to assemble, deploy and govern the data, models, applications and tools behind Physical AI, enterprise agents and AI-native products, in their own environment or their clients', with no compute markup. Today it runs on Kubernetes in AWS and Google Cloud accounts; private cloud, on-premises and other clouds are coming soon.

When Hyperlake is a strong fit:

Are AI agents and applications multiplying your data and model calls? Then you need a governed data and intelligence foundation: Apache Iceberg and Trino for lakehouse analytics, ClickHouse for fast analytics, PostgreSQL for applications, Redis for state, Kafka for streams, and Qdrant or Milvus for vectors, paired with open model serving (KServe). Owned capacity prevents per-action pricing spikes.

Reuse is the second case. Wiring identity, data services, models, policies and monitoring from scratch for every project or client is plumbing you'd rather not rebuild, so Hyperlake packages those deployment patterns with shared governance (OAuth/OIDC proxy sign-in, validated JWT identity and OPA policy decisions), managed through an application UI, CLI and MCP-compatible tools. (Note: Dedicated public documentation for Hyperlake's MCP tools is not yet published.)

Third, you need workload-aware capacity and infrastructure control. Eligible inference endpoints can scale down when idle, and customers keep their data, models, applications and keys in infrastructure they control (on-premises environments are on the way). Savings come from reuse, less integration work and workload-aware capacity, not a fixed percentage promise. We make the ROI case one workload at a time.

When you should stay in your cloud warehouse instead:

  • Purely bursty BI and ad-hoc analyst queries. If your virtual warehouses run only a few hours a day and the bill is manageable after Steps 1-3 above, a managed cloud warehouse like Snowflake, BigQuery or MotherDuck is simpler than running dedicated cluster capacity.
  • No need for private AI, sustained agent workloads or environment ownership. If all your team wants is a managed SQL endpoint for standard business reporting, native warehouse cost controls and dbt incremental models deal with the main sources of compute and storage waste.

To see how your data and AI workloads map to open lakehouse and self-hosted economics, read our guide to the Open-Source Data Lakehouse Stack, look at our FinOps for AI governance framework, or test your assumptions in the LLM API vs. Self-Hosted Cost Calculator.

Frequently Asked Questions (FAQ)

1. Where should cost optimisation start?

With usage and ownership. Look at idle compute and expensive jobs first, then weigh each change against performance and recovery requirements. Agree controls with the accountable operator, since one aggressive setting applied to every workload tends to backfire.

2. Why did my Snowflake bill stay high after I enabled Resource Monitors?

Because, as Snowflake documents, Resource Monitors only track and suspend user-managed Virtual Warehouses. They don't control credits used by serverless features (such as Snowpipe or Automatic Clustering) or AI services (such as Cortex). To monitor and alert on serverless and AI credit usage, you need to configure Snowflake Budgets.

3. Does downsizing a Snowflake warehouse always save money?

Not always. Snowflake bills per second (after a 60-second minimum on start or resize), and each size tier doubles the credit burn rate. If dropping from Medium (4 credits/hour) to Small (2 credits/hour) makes a query run twice as long, the credit cost is the same. Downsizing does save credits when the larger warehouse was underused, or kept being woken for short tasks that hit the 60-second minimum.

4. How do dbt incremental models reduce cloud data warehouse costs?

They stop you reprocessing the whole table on every run. A dbt incremental model (materialized='incremental') transforms only records that are new or updated since the last run, while a full-refresh build redoes everything. Pairing incremental models with dbt clone in Slim CI (state:modified+) also avoids full-refresh rebuilds during pull-request testing.

5. How should storage costs be reduced safely?

Start by reviewing retention and recovery requirements with the data owner. Remove unnecessary copies only under an approved policy, and understand the durability implications before changing table types.

6. When is moving a workload cheaper?

Only a workload-specific cost comparison can tell you. Put migration, parallel running, infrastructure, licences, support and people next to the service charges, then decide.

*All trademarks belong to their respective owners. Contact the Hyperlake team.*

Start with a workload. Build the environment around it.

Explore example deployments, or see how the platform assembles, deploys, governs and operates the stack.