Snowflake Generation 2 Warehouses

When using Snowflake, compute and storage are fully separated. This means choosing different sizes (and types) of compute to use against the same data for different purposes. Even at the same time.

It used to be that size was the main difference between warehouses, and one of the main knobs for performance and cost. With changes introduced in the last year or two, the options around warehouses are exploding. First it was adding snowpark-optimized warehouses – larger individual nodes with more memory that benefit a more spark-based way of working with data. Within the last year, Generation 2 Warehouses reached GA, Snowflake announced the upcoming Adaptive Warehouses, and Interactive Warehouses also became generally available. With all the choices, it is important to understand each warehouse and the scenarios in which it makes sense.

Generation 2 Warehouses

Generation 2 Standard Warehouses (Gen2) make use of newer chip architectures and faster hardware. It makes sense that this is needed. Over time better hardware becomes available, and it is often more expensive, at least at the outset.

Gen2 warehouses are more than just faster hardware, though. They include core updates to the Snowflake product that improve performance in several key ways. Perhaps the most impactful is the improvements in DML – in this case, MERGE, UPDATE, and DELETE statements. The official blog post on DML improvements goes through this in detail. I recommend reading it.

Before these improvements, Snowflake would (re)write any micro-partition with even one changed row. Writing an entire micro-partition is a lot of work to do for a single row when a micro-partition can have thousands or even millions of rows. The newer DML methodology uses delete files in addition to micro-partitions when only a small portion of the data on a micro-partition changes. I have seen multiple customers see huge increases in performance due to simply using a Gen2 warehouse in this kind of scenario. This is the no-brainer use-case for Gen2 warehouses in my opinion. If the workload fits in to this improvement, Gen2 warehouses are so much faster that they are also cheaper.

To cover the faster hardware and work put into the software improvements, Gen2 warehouses are 25% to 35% more expensive than Gen1 warehouses. See the Snowflake credit consumption table to understand the details.

There are additional improvements in Gen2 warehouses – if you hit one of the edge cases that these improvements are targeted at, there is a significant performance gain.

Remember how I started this article talking about the separation of compute and storage? You can absolutely run Gen1 and Gen2 warehouses against the same data, at the same time or different times. This means that you can test out Gen2 warehouses with little effort and risk, and just as easily flip back to Gen1. This is the best way to prove their worth for yourself – using your own real-world workloads.

Generation 2 Warehouses Example

I prefer actually demonstrating what I’m talking about, so here’s a bit of an exploration of Gen2 warehouses. This should be easy to run on a free trial account if you want to explore for yourself. First, I see that the default warehouses created in my free trial account are all generation 1:

SHOW WAREHOUSES 
    ->> SELECT "name"
            , "type"
            , "resource_constraint" 
          FROM $1;

I can create a Gen2 warehouse using:

CREATE WAREHOUSE compute_wh_gen2
        WAREHOUSE_SIZE='XSMALL' 
        AUTO_SUSPEND=60 
        GENERATION = '2';

After that, I can use the same query as before to see the additional warehouse:

I do not generally recommend putting the warehouse type (like gen2) or size in the warehouse name, because in the real world those are things that both can change, and are easily query-able. It is excellent for comparisons and experimentation like this, though.

I am going to be working with a table that’s around 2,500 micro-partitions here, and my guess is that a large warehouse will get me reasonable enough response times that I won’t get distracted in the middle of this article waiting for things to run, while still fitting into my free trial. Here are the warehouses I will use:

CREATE WAREHOUSE compute_wh_large
        WAREHOUSE_SIZE='LARGE' 
        AUTO_SUSPEND=60 ;
CREATE WAREHOUSE compute_wh_large_gen2
        WAREHOUSE_SIZE='LARGE' 
        AUTO_SUSPEND=60 
        GENERATION = '2';

Next, I will create a database and schema and then pull data over from the SNOWFLAKE_SAMPLE_DATA dataset, into a couple of table clones, to test the time an update takes on Gen1 vs Gen2:

CREATE DATABASE IF NOT EXISTS datageek_exp;
CREATE SCHEMA IF NOT EXISTS gen2_exp;
USE datageek_exp.gen2_exp;

USE WAREHOUSE compute_wh_large;
CREATE OR REPLACE TABLE orders_source AS
    SELECT * 
      FROM snowflake_sample_data.tpch_sf1000.orders
    ORDER BY RANDOM();

CREATE OR REPLACE TABLE orders_upd_gen1
    CLONE orders_source;
CREATE OR REPLACE TABLE orders_upd_gen2
    CLONE orders_source;

Notice that I have added an order by random when copying the data – this is to ensure I am not getting any partition pruning. It is not likely to be an issue for this scenario, but could impact the performance if we did see it.

Random, Targeted Update Scenario

Ok, now I am going to use an anti-pattern. I am going to use a modulus function to make sure I am hitting the percentage of rows that I want to. I do not recommend this method for sampling data when performance is important, but it’s an easy hack when I want to make sure I hit a reasonable number of rows and therefore a reasonable number of micro-partitions, without updating that many rows on each micro-partition.

This data set has 1.5 billion rows on just under 2,500 micro-partitions. The update statements I have chosen here will update 60,000 rows. This ensures that every micro-partition will have a row updated, but the total percentage of rows being updated is well under 5%.

USE WAREHOUSE compute_wh_large;
UPDATE orders_upd_gen1
    SET o_totalprice = 42
    WHERE MOD(O_ORDERKEY, 100000) = 0
;

USE WAREHOUSE compute_wh_large_gen2;
UPDATE orders_upd_gen2
    SET o_totalprice = 42
    WHERE MOD(O_ORDERKEY, 100000) = 0
;

Running this multiple times and resetting the tables in between by doing the clone statements again, I get a timing on the Generation 1 warehouse of about 1 minute and 22 seconds. For the generation two warehouse, I get about 34 seconds. This represents almost a 60% improvement on the same size of warehouse! This represents faster performance at a lower cost.

Update Every Row

For curiosity’s sake, I wanted to see what the timing would look like if I instead updated every row in the table. I’m curious about this because this requires the same amount of physical work on each warehouse type – the DML changes specifically shouldn’t impact the performance of this comparison. To test this, I used this syntax:

CREATE OR REPLACE TABLE orders_upd_gen1
    CLONE orders_source;
CREATE OR REPLACE TABLE orders_upd_gen2
    CLONE orders_source;

USE WAREHOUSE compute_wh_large;
UPDATE orders_upd_gen1
    SET o_totalprice = 42
;

USE WAREHOUSE compute_wh_large_gen2;
UPDATE orders_upd_gen2
    SET o_totalprice = 42
;

The results here showed about 1 minute and 2 seconds on the Gen2 warehouse and about 1 minute and 30 seconds on the Gen1 warehouse. The difference in this case is still just over 30%.

For the cloud provider I’m using, the cost difference for Gen2 is about 35% more. This means that the targeted update can be thought of as significantly cheaper on Gen2, while the second is slightly more expensive (though faster) on Gen2. This is true if we are calculating on pure warehouse time, not counting any idle time.

Update Only One Row

I was curious about one final scenario – what about the update of a single row in an unclustered table? This likely means that the entire table will have to be scanned, but we’ll only be writing a single micro-partition. Here’s the code:

USE WAREHOUSE compute_wh;
UPDATE orders_upd_gen1
    SET o_totalprice = 42
    WHERE O_ORDERKEY = 1780080164
;

USE WAREHOUSE compute_wh_gen2;
UPDATE orders_upd_gen2
    SET o_totalprice = 42
    WHERE O_ORDERKEY = 1780080164
;

Note that I used XS warehouses here, which seemed a bit more appropriate when writing a maximum of one micro-partition, even though I’m still scanning quite a few. Over several runs, the Gen2 single-row update was running in about 6 seconds, while the Gen1 one was running in about 13 seconds. I was a bit impressed with this, as I didn’t expect the difference to be so drastic. Over 50% better counts as better cost and performance.

The results here are not meant to be a benchmark or to extrapolate for all scenarios. Hopefully they show a methodology that can be used for testing with real data and real workloads.

Choosing Between Gen1 and Gen2 Warehouses

I would expect (but not promise) that adaptive warehouses, when they are released, will probably be using the same hardware and software as Gen2. If I were selecting a warehouse today, I would start with a Gen2 warehouse. I think the price-performance argument is there for it. I would certainly use Gen2 for any workload that does UPDATE, DELETE, or MERGE operations that are affecting a small percentage of rows.