Snowflake 自适应数仓：DML 最高提速 13.7 倍

Why Adaptive Warehouses Are up to 13.7x Faster for DML Operations

TL;DR: Changing a small fraction of a table shouldn’t mean rewriting the whole thing. Snowflake Adaptive Warehouses’ write path cuts the work data manipulation language (DML) operations do — writing only what changed, reusing metadata instead of recomputing it, and overlapping CPU and I/O — delivering the largest gains on DELETE, UPDATE and MERGE. In this blog, we’ll explain how Adaptive Warehouses lead to a 13.7x speedup on a representative workload.

Why small modifications can require so much work

Snowflake’s write path is the engine work that turns a modification into stored data and the metadata needed to query it. Understanding its cost starts with how data is stored.

Native Snowflake tables are divided into micro-partitions stored in compressed, columnar files. The storage design described in Snowflake’s original SIGMOD paper uses immutable files: An existing file is not edited in place. The engine must represent a modification through new data and metadata instead.

Under a copy-on-write approach, modifying a few rows can mean copying the unchanged rows into replacement files too. This is write amplification: The amount physically written can be much larger than the logical change. Small, scattered updates make the mismatch especially apparent.

Snowflake also maintains accurate metadata over the surviving data, allowing the engine to make more informed decisions in later queries, holistically improving performance for the workload as a whole. This, however, comes with a cost at DML time that can in some cases be disproportionately more significant than the local effect of the modification itself.

What is the Adaptive write path?

The Adaptive write path is the set of engine changes behind DML performance on Adaptive Warehouses: writing less data for a given change, reusing existing metadata instead of recomputing it, overlapping CPU work with I/O waits and arranging new files to match how a workload actually reads them. Each mechanism activates automatically when it applies to a given DML statement, with no configuration required.

The four bottlenecks we targeted

We first reduced the amount of data written for sparse modifications. Metadata computation then became a more prominent cost. Reusing metadata reduced CPU work, which exposed time spent waiting for I/O. Alongside those write-path changes, we explored how the layout of newly written files could better serve the queries that read them.

1. Write amplification: Record the change instead of copying unchanged data

For sparse modifications, delta writes can represent changes with compact delta files and a bitmap rather than rewriting whole partitions. The benefit comes from avoiding copies of data that did not change.

Consider an update that changes only a small fraction of the rows in a file. A full rewrite copies the unchanged rows as well as writing the modified data. A delta representation retains the existing data and records the modification separately. The engine still has work to do, but the physical write is better aligned with the size of the change.

A real MERGE query from the benchmark comparison used later in this post shows this mechanism in practice — see “Write Amplification: A Real Benchmark MERGE Query” below.

Reducing writes does not make the rest of the operation free. The engine must still maintain the metadata that describes the resulting data. This method was originally described in this blog.

2. Metadata updates: Reuse statistics where recomputing them is unnecessary

Column statistics are part of the work behind a modification. Even when a delta write avoids copying unchanged rows, computing statistics can still consume CPU time.

The next improvement reuses existing column statistics on eligible DML paths instead of computing them again. The distinction is important: This avoids redundant computation where reuse is applicable, rather than promising that every statistic can be updated incrementally for every operation.

Reuse also changes where the work happens. Existing statistics must be retrieved from metadata files and parsed. A metadata file can contain statistics for multiple partitions, so several modified partitions may need information from the same file. After reducing computation, retrieving that information efficiently became the next problem to solve.

There’s a trade-off here: the more accurate the statistics, the more they pay off. So we keep certain key statistics exact matches for the surviving data. Doing that sometimes meant storing a little extra metadata, which lets us cheaply decide when the statistics need a full recompute and when a quick adjustment will do.

3. CPU and I/O efficiency: Overlap useful work and cache parsed metadata

A sequential write path alternates between fetching the data (I/O) and applying the DML operation and metadata computation (CPU). When metadata reuse reduces the CPU work, the time spent waiting for downloads becomes more noticeable. Faster computation alone cannot hide that wait. The goal here is to saturate both the CPU and the I/O bandwidth for as long as possible.

We addressed this with two complementary mechanisms:

• Asynchronous data fetching: Background threads download and parse data while, in the foreground, partitions that are ready are processed. The engine can prepare later work without making the current work wait for each download in sequence.
• In-memory metadata caching: After a column-statistics file has been downloaded and parsed, its contents can be reused by other partitions referencing that file. Entries are subject to eviction; this is reuse of available cached metadata, not a guarantee that every access avoids I/O.

Previously, the statistics-reuse path batched partitions before fetching their metadata. Batch flushes produced bursts of CPU activity and could leave final partitions waiting at the end of a query. Caching parsed metadata in-memory avoids that batching requirement in this path. These changes address scheduling and repeated work. They do not eliminate all I/O or every possible source of query delay.

4. Workload-based optimization: Arrange new files for the queries that read them

The fourth opportunity concerns the output of a write, not just its duration. Columns read together can be placed next to one another within a file, with the aim of reducing remote I/O for those access patterns.

The approach uses table query history to identify columns frequently accessed together and derive a column ordering. That ordering is applied as new files are written. Existing files do not become newly organized merely because a query runs, so the effect depends on how much data is subsequently written and whether future queries resemble the observed access patterns.

For example, a wide table may have a recurring query that reads a small subset of columns. Grouping those columns together is a way to improve the layout for that query pattern. It does not imply that every query benefits, or that each workload receives its own independent copy of the table.

This is a downstream read-performance opportunity. The DML benchmark below does not isolate or quantify its contribution. By optimizing the column layout based on target workloads, we expect more efficient I/O patterns for data reads. This benefits both downstream analytics and DML operations, as most of the data modifications require an initial read step.

Performance results

To illustrate the performance gains in Adaptive Warehouses vs. standard Gen1 warehouses, take a look at the synthetic example below:

The query is a MERGE into a store-returns-style fact table, joined against customer, item, date and demographics dimensions on a nine-column key, updating matched rows and inserting new ones. The query ran single-user with a warm cache (steady-state, not a cold first run) on both warehouses, each limited to a 2XL size, so there is no cache-warming artifact.

merge into cdml_final tgt
using (select distinct sr_returned_date_sk, ..., hd_vehicle_count from tmp_combined1) as src
on tgt.sr_returned_date_sk = src.sr_returned_date_sk and tgt.sr_return_time_sk = src.sr_return_time_sk and ... -- nine join keys in total
when matched then update set tgt.hd_vehicle_count = src.hd_vehicle_count, tgt.sr_return_amt = 1.1 * src.sr_return_amt, tgt.sr_fee = 1.1 * src.sr_fee
when not matched then insert (...) values (...)

Query Profile: same shape

Note: the plan/operator-tree diagram in this figure is illustrative, recreated for this blog post to match the real profile data — it is not a literal screenshot of the Snowsight UI. Node percentages, timings, and category breakdowns are the real measured values; the boxes, arrows, and layout are a redrawn approximation of Query Profile’s plan-graph view for readability.

While both runs share the same query plan and perform the exact same logical work — updating 1,825,641 rows and inserting 419,754 rows across ~439 GB of scanned data — the Adaptive Warehouse achieves a 13.7x speedup. A 545x reduction in data written back to storage drives this gain.

This drastic reduction in written bytes changes where execution time is spent:

• Gen1 Warehouse (6m 28s total): Writes dominate execution, taking 67% of the total runtime to write back 431.8 GB of updated micro-partitions.
• Adaptive Warehouse (28s total): Writing drops to just 0.79 GB. With the write bottleneck removed, the query’s primary cost shifts to scanning the target table, accounting for 48% of runtime.

Although remote disk I/O accounts for 90% of execution time on Adaptive compared to 35% on Gen1, absolute I/O wait time drops significantly from 1m 17s down to 14s due to asynchronous data prefetching.

What the query profile doesn’t show:

Faster infrastructure: Adaptive Warehouses use faster compute than Gen1 and Gen2 warehouses. DML performance improves because of a combination of faster hardware and software.

Metadata computation that never ran at all: Statistics reuse avoids recomputing column statistics that would otherwise run on every write. Key statistics remain exact with minimal adjustment. This does not show up in the query profile view at all.

Downstream effects on future reads: Workload-informed column placement changes how the newly written data is organized, which would appear in a later query’s profile reading this table — not in this write’s own profile. This example measures the write; it does not measure whether the write also changed the cost of the next read.

Real production workloads

We see similar gains for customers in our production environments. Below is a relatively simple query — an UPDATE statement from a large communications company that marks newly ingested files as processed:

UPDATE <schema>.file_tracking_table SET is_parsed = 'Y' WHERE split_part(split_part(file_path, '/', -1), '-', 1) = '<batch_id>' AND is_parsed = 'N'

With the mechanism off, this query scanned 1.59 TB, wrote 5.35 GB back to storage, and took 7.33 seconds of execution time. With it on, it scanned the identical 1.59 TB, wrote only 12.98 MB (412x less), and took 1.72 seconds — a 4.25x improvement.

When do these improvements help most?

The mechanisms are most relevant when a pipeline spends substantial time on the work they target:

• Sparse modifications: Change data capture (CDC) MERGE statements, targeted DELETEs and row-level updates can amplify writes when unchanged rows must be copied.
• Frequent transformation jobs: Recurring extract, load, transform (ELT) pipelines and slowly changing dimension (SCD) maintenance repeat that work across many executions.
• Wide tables with recurring column subsets: Workload-informed column placement targets the reads following a write, where repeated access patterns can inform file layout.

Who should care?

Data engineers maintaining frequently updated tables are the primary audience. Analytics engineers should also consider how newly written data serves downstream queries. Platform and FinOps teams should compare both elapsed time and measured consumption when evaluating a change; a runtime ratio alone is not a cost estimate.

Getting started

The fastest way to get started with Adaptive Warehouses is to create a new warehouse, or convert one of your production warehouses with safe defaults and tune only if your results require it. Converting between warehouse types is a zero-downtime operation, so switching back is simple too.

Here’s how to do that:

-- Create an Adaptive Warehouse with the default settings
CREATE ADAPTIVE WAREHOUSE analytics_adaptive_wh;
-- Convert an existing standard warehouse without downtime,
-- and let Snowflake figure out the safe defaults
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_TYPE = 'ADAPTIVE';
-- Or specify the performance ceiling and throughput target explicitly
CREATE ADAPTIVE WAREHOUSE analytics_adaptive_wh
  WITH MAX_QUERY_PERFORMANCE_LEVEL = XLARGE
       QUERY_THROUGHPUT_MULTIPLIER = 4;

-- Review or adjust the Adaptive Warehouse settings
SHOW WAREHOUSES;
ALTER WAREHOUSE analytics_adaptive_wh
  SET MAX_QUERY_PERFORMANCE_LEVEL = XLARGE -- performance cap
      QUERY_THROUGHPUT_MULTIPLIER = 4; -- throughput cap

If you prefer to use CoCo, open it in Snowsight or the CoCo CLI and describe the desired change in natural language — for example, “Create an Adaptive Warehouse called analytics_adaptive_wh with MAX_QUERY_PERFORMANCE_LEVEL set to XLARGE and QUERY_THROUGHPUT_MULTIPLIER set to 4,” or “Convert analytics_wh to an Adaptive Warehouse.”

CoCo generates and runs the corresponding SQL on your behalf, so review the proposed statement and confirm the target account and privileges before approving it. Start with the defaults, then increase QUERY_THROUGHPUT_MULTIPLIER if you observe queuing; use budgets and resource monitors for aggregate cost controls.

Choose a representative pipeline and establish its current elapsed time and consumption.

See the Snowflake Adaptive Compute documentation for full syntax and monitoring examples. Then, observe the same workload on Adaptive under comparable conditions.

Key takeaways

• Adaptive Warehouses deliver substantial performance gains for workloads heavy on MERGE, UPDATE and DELETE operations by reducing write amplification.
• Assess your existing pipelines for write-heavy data transformations and high remote I/O wait times to identify prime candidates for migrating from Gen1 to Adaptive Warehouses.
• Delta writes reduce copying of unchanged data; statistics reuse avoids redundant metadata computation on eligible DML paths.
• Asynchronous data fetching and in-memory metadata caching address I/O waits and repeated metadata processing that become more visible as CPU work shrinks.
• Workload-based column placement improves subsequent reads; it is not an independently measured source of the headline gain.
• Snowflake is continuously evolving DML operations to improve extract, transform, load (ETL) and ELT pipelines, as well as the price/performance of data pipelines, with more improvements on the way that improve both bulk and sparse updates.

For more information on specific Snowflake performance optimizations, follow along here.

This post may contain forward-looking statements about future product capabilities. Actual results may differ. See our latest 10-Q.

Your Data Pipelines Just Got Faster and Smarter: Meet Adaptive Warehouses was originally published in Snowflake Builders Blog: Data Engineers, App Developers, AI, & Data Science on Medium, where people are continuing the conversation by highlighting and responding to this story.