Snowflake details Adaptive Warehouses for write-heavy pipelines

Snowflake detailed four engineering changes behind Adaptive Warehouses on October 1, aimed at write-heavy data pipelines that use operations such as MERGE, UPDATE and DELETE. The company says the changes reduce how much unchanged data the system rewrites, reuse existing metadata, overlap CPU work with storage reads and shape newly written files around past query patterns.

For change-data-capture merges, targeted deletions, row-level updates, recurring ETL and ELT jobs, and slowly changing-dimension maintenance, that means a different execution path. Snowflake’s current documentation lists Adaptive Compute as generally available to Enterprise Edition or higher accounts in select AWS, Azure and Google Cloud regions. This was not an initial launch: Snowflake’s release notes date AWS general availability to June 16. That work sits downstream of Snowflake’s Elastic Channels ingestion path, which covers how producers move records into Snowflake tables rather than how subsequent DML work runs.

The first change targets write amplification. Snowflake’s published warehouse architecture stores table data in immutable columnar files, so changing a small number of rows can require rewriting much larger partitions. Snowflake says Adaptive Warehouses can instead record sparse changes in compact delta files with a bitmap, reducing copies of unchanged data.

Snowflake also says eligible DML paths can reuse existing column statistics instead of calculating them again. Meanwhile, background threads can fetch and parse data as ready partitions are processed, allowing CPU and I/O work to overlap. Parsed column-statistics metadata may be cached in memory for reuse, although entries can be evicted and the mechanism does not eliminate all I/O.

For later reads, Snowflake says the system examines table query history to identify columns that are frequently read together, then applies that column ordering to newly written files. Running a query does not reorganize existing files on its own. Any read benefit therefore depends on subsequent writes and on future access patterns resembling the recorded history. Snowflake did not isolate or quantify that effect in its headline DML test.

In a Snowflake-run, single-user warm-cache benchmark using 2XL warehouses, a MERGE scanned about 439 GB, updated 1,825,641 rows and inserted 419,754 rows. It finished in 28 seconds on Adaptive, compared with 6 minutes 28 seconds on Gen1. Snowflake reported a 13.7-fold speedup and 545 times less data written: 0.79 GB versus 431.8 GB. The results were not independently reproduced. Snowflake also says Adaptive Warehouses use faster compute than Gen1 and Gen2 instances, so the comparison does not separate the software mechanisms from the hardware change.

Snowflake also described an UPDATE at an unnamed communications company that scanned 1.59 TB in both configurations. The operation wrote 12.98 MB and ran in 1.72 seconds with the mechanism enabled, the company reported, versus 5.35 GB and 7.33 seconds with it disabled. Snowflake did not identify the customer or provide raw telemetry, so the example cannot be independently checked.

According to Snowflake’s documentation, customers can create an Adaptive Warehouse or convert an existing standard warehouse without downtime. Adaptive uses MAX_QUERY_PERFORMANCE_LEVEL and QUERY_THROUGHPUT_MULTIPLIER controls instead of standard warehouse size and multi-cluster settings, and bills queries in virtual-warehouse compute credits. Snowflake recommends testing a representative pipeline under comparable conditions and comparing both elapsed time and credit consumption. The disclosed runtime gains alone do not establish a cost reduction.