1BRC in SQL with Snowflake (1-1.5 s, but the 1TRC below 20 seconds)

[waldekkot]

Small contribution to the fun 1BRC - here: using Snowflake and all in SQL (the data generation, the contest's aggregation query and output). The aggregation query and output inspired by the DuckDB (#39) contest entry 😄

Data generation does not use files (actually does not use tables or databases either 😉 ). This makes the whole experimentation much easier - all you need is a browser and a Snowflake account (there is no-strings-attached / no-credit-card-required Snowflake trial available at: https://signup.snowflake.com/ ). Takes 1-2 minutes to get up and running. The lowest (Standard) edition of Snowflake is sufficient for the contest. Pick up your favorite cloud provider and the cloud region where you want to have the Snowflake service available. After activation and logging in, create a new SQL worksheet (big blue Plus button top right corner in the Snowflake UI) and paste the SQL script below for the 1BRC fun. Run all (the same big blue Play button). No other tools or anything else needed. Timings, etc. are in the Query History (left menu, Activity -> Query History) - filter out the important queries by query tag ("BRC:").

Excerpts (whole script at the bottom):

-- Run calculations (yes, if it resembles to you what is in the DuckDB post, I "creatively borrowed" it, then tweaked it a bit):
WITH src AS 
    (SELECT station_name,
            MIN(measurement) AS min_measurement,
            AVG(measurement) AS mean_measurement,
            MAX(measurement) AS max_measurement
    FROM TABLE(RESULT_SCAN($gen_qid))               -- here is reading data from the Query Results 
    GROUP BY station_name
    )
SELECT '{' ||
        ARRAY_TO_STRING(ARRAY_SORT(ARRAY_AGG(station_name || '=' || CONCAT_WS('/',min_measurement::DECIMAL(8,1), mean_measurement::DECIMAL(8,1), max_measurement::DECIMAL(8,1)))),', ') ||
        '}' AS "1BRC"
FROM src;

My 1BRC results (Snowflake on AWS Ireland, zero tuning or configuration, i.e. a vanilla trial account):
1 Billion Rows:

• min/max/avg aggregation: 6-8.5 seconds (but wait, the result below is better !)
• data generation: < 25 seconds

Those are the best timing-wise (not so much common-sense wise), running on the largest (4-6XL) Snowflake engines (difference between the largest engines was less than 2 seconds - the task to do is too easy simply).

Note: since Snowflake is a distributed system running on the public cloud infrastructure, there is some variability (jitter), especially impacting the timing of small (like the =1 billion) queries, so your mileage might differ by a second or two (or rather 5-10% of execution time), in both directions, up and down. The queries in Snowflake have to be compiled, scheduled to run on (sometimes) hundreds of the cloud provider's nodes (VMs), etc. Hence the variability (although the cloud infrastructure is very homogeneous)... For larger, analytical queries, this does not matter much.
This is particularly visible, if you scale up to the humongous Snowflake engines (=virtual warehouses), like 5XL or 6XL. Time to provision them might sometimes take even 2-3 minutes, it is dynamic and depends on what cloud provider & cloud region you selected and how busy it currently is. For virtual warehouses smaller than 4XL, you should rarely experience more than 1 second of provisioning or queuing time (P90 <1 second). So, the jitter will be negligible.

So, if the data is materialized into a Snowflake tables, things become more interesting... For 1 bln rows, the smaller virtual warehouse (and cheaper than the humongous ones) - XLARGE actually gives better results (less scheduling overhead):
1 Billion Rows:

• min/max/avg aggregation: 1.2-2 seconds 😃

With Snowpark-optimized engine (having more memory), on X2LARGE, this 1 bln query goes below 1 second often... 😵‍💫

Actually - and probably this is the most interesting aspect of this contribution to the whole entertainment: the 1 Billion is NOT a very challenging task for a modern DBMS 😉 ... Try 10 and 100 Billion or why stop here: go the 1TRC (1 Trillion Rows Challenge ®) 😉

My 1 Trillion (1000 billion) Rows (Snowflake 6XL engine) timing:

• query: ca. 18-20 seconds
• data generation: ca. 6 minutes 30 seconds
• data materialization into a table: ca. 8 minutes 30 seconds

I can bet the 1QRC will be fine too (Q = quadrillion, a million of a billion, 10^15 rows). Just might take a few hours 😄 (the scaling here is significantly sub-linear...)

The code has no tricks and is rather simple (reading what the gurus do with the 1BRC Java code is fascinating - super cool!).

Important: the data generation is a one-time thing - you can refer to its results by the query ID (UUID). BTW: notice, in the SQL code there is no materialization of the data into any table (no database/schema/table creation statements - yet it all works). This is Snowflake's Persisted Query Results buffer in action (more in the docs for the curious ones: https://docs.snowflake.com/en/user-guide/querying-persisted-results). So, if you want to tweak the query code or run the query on a differently sized Snowflake engine, just modify the line 80 marked HERE (SET WH_SIZE = 'X3LARGE') and run the rest of the script below it (select lines from 80 'HERE' including till the end and press the big blue Play button) to re-run the calculation query.

There are actually 3 parameters to play with:

1. how many rows to generate (count the zeros properly 😄): number_of_measurements
2. what (T-shirt) size of Snowflake engine (the virtual warehouse) to use for the data generation (again - this should be a one time activity for specific number of rows, do not waste your time): XLARGE is fine (20-25s for 1 billion)
3. what (T-shirt) size of Snowflake engine to use for the xBRC query/calculation: SET WH_SIZE = 'X3LARGE';

In terms of further tuning - there are (and it is like that 'by design' in Snowflake) only a few tuning parameters/mechanisms to play with in Snowflake platform. I seriously doubt they would matter for such a simple query as the 1BRC one. I wish the challenge, besides the 1 Trillion+ rows, also tested more complex aggregations and - more importantly - running many queries concurrently, e.g. simulating many BI/ML. So, not just a single query at a time. For a modern, distributed DBMS, this is where it should shine.

A few "for educational purposes" notes on the data generation piece (this is a common pattern when working with synthetic data in Snowflake - also easy, as there is no need to bother with files, wasting laptop storage, etc.):

1. it starts with the dictionary of cities and their mean temperatures: a static query with inlined values (SELECT ... FROM VALUES ()): https://docs.snowflake.com/en/sql-reference/constructs/values
2. in Snowflake, you can refer back to the query results by its UUID - here, it is stored in 'stations_qid' session variable, used in the code later (https://docs.snowflake.com/en/sql-reference/session-variables)
3. 'number_of_measurements' - another session variable which defines how many measurements will be generated
4. adjust the size of Snowflake engine used for the measurements-generating query - XLARGE should be sufficient for 1 billion rows (~20s). For trillion rows, on 6XL it takes ~7 minutes. Remember: 4XL-6XL consume quite a lot of Snowflake credits, so running such simple query (1billion datapoints) on them is a pure waste. But if you need a simplistic evidence of up/down scalability of Snowflake, you might play with it (every T-shirt size is roughly 2x more powerful in terms of compute than the previous one, so S is 2x power than XS, M is 2x power than S, etc.) Values of the WAREHOUSE_SIZE parameter are explained in: https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse
5. Snowflake allows you to execute multiple SQL commands at once using EXECUTE IMMEDIATE $$ <block of procedural SQL> $$ (https://docs.snowflake.com/en/sql-reference/sql/execute-immediate)
6. DECLARE block - variables used by the measurement generation query (mostly the dictionary data, number of cities: 413). You might notice transforming the dictionary into SQL array. It will be embedded as one of the CTEs in the measurements generation query (equivalent of the 'stations' list of records in the CreateMeasurements.java in original 1BRC Java code)
7. the measurements generation is a single SQL SELECT statement (just dynamically build and frankly a bit hairy as it is multi-line and with a CTE and a 2 SELECT sub-queries - just dissect it piece by piece)
8. the most nested SELECT subquery uses the Snowflake generator table function - "for given number of measurements (e.g. 1B), pickup a random city (ID)". I wanted to re-create the same logic as in Java CreateMeasurements.java. Note: here is the uniform pseudo-random distribution used
   • CTEs: https://docs.snowflake.com/en/user-guide/queries-cte
   • SQL subqueries: https://docs.snowflake.com/en/user-guide/querying-subqueries
   • Generators: https://docs.snowflake.com/en/sql-reference/functions/generator
   • uniform: https://docs.snowflake.com/en/sql-reference/functions/uniform
9. the 'stations' dictionary has been converted earlier to array of objects (here: object is one of Snowflake's semi-structured data types - think JSON like): https://docs.snowflake.com/en/sql-reference/data-types-semistructured
   • to refer to the attributes of the object use colon, e.g. m:station_name
10. the 'stations' dictionary is 'embedded' in the 'stations_avgtemp' CTE - the CTE is constructed 'dynamically'
11. the 'measurement' attribute is used as the mean in Gaussian random-number generator (in Snowflake named: normal instead of Java's nextGaussian): https://docs.snowflake.com/en/sql-reference/functions/normal
12. the dynamically generated SQL is then executed using nested EXECUTE IMMEDIATE instruction (https://docs.snowflake.com/en/sql-reference/sql/execute-immediate)
13. another session variable 'gen_qid' is used to store the UUID of the measurements generation query, to allow referring back to its results (the 1B or whatever) in the calculation query, like here: set gen_qid = LAST_QUERY_ID(-2);
    • note, we need to get back to the second last query (WITH ... SELECT ... FROM ... generator)
14. to refer to results of any query in the past (at least as long as it exists: 24h clock, resets every time the buffer is used, up to 31 days) - just use it in the SQL's FROM clauses:
    • like: SELECT * FROM TABLE(RESULT_SCAN( UUID ))
    • RESULT_SCAN table function: https://docs.snowflake.com/en/sql-reference/functions/result_scan
    • this is also how the calculations query refers to the previously generated dataset

All in all, it might not be the best SQL code as it has been written by only averagely-intelligent person (=me) and in maybe 30 minutes or so (plus copying-and-tweaking the DuckDB original calculation query to work on Snowflake 😉 ). But in the end this is maybe 50 lines of code, not a few hundred. The beauty of declarative language like SQL. Explanations are longer than the SQL code itself 😄

Enjoy !

Cheers,
Waldek from ❄️ (email me if you spot a bug: waldemar.kot@snowflake.com)

The whole script - copy/paste it into a SQL worksheet in UI of your Snowflake, then run:

ALTER SESSION SET USE_CACHED_RESULT = FALSE;    -- turn off query results caching, so every query is re-executed (do not do it in prod)

-- all operations will be performed using just this engine (it will be resized later)
CREATE OR REPLACE WAREHOUSE CONTEST_WH WITH WAREHOUSE_SIZE = 'XSMALL' WAREHOUSE_TYPE = 'STANDARD' AUTO_SUSPEND = 60 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE MAX_CONCURRENCY_LEVEL=1;
USE WAREHOUSE CONTEST_WH;

-- dictionary data (cities and their average temperature)
SELECT $1 AS city, $2::DECIMAL(8,1) AS averagetemp 
FROM VALUES 
('Abha',18.0),('Abidjan',26.0),('Abéché',29.4),('Accra',26.4),('Addis Ababa',16.0),('Adelaide',17.3),('Aden',29.1),('Ahvaz',25.4),('Albuquerque',14.0),('Alexandra',11.0),('Alexandria',20.0),('Algiers',18.2),('Alice Springs',21.0),('Almaty',10.0),('Amsterdam',10.2),('Anadyr',-6.9),('Anchorage',2.8),('Andorra la Vella',9.8),('Ankara',12.0),('Antananarivo',17.9),('Antsiranana',25.2),('Arkhangelsk',1.3),('Ashgabat',17.1),('Asmara',15.6),('Assab',30.5),('Astana',3.5),('Athens',19.2),('Atlanta',17.0),('Auckland',15.2),('Austin',20.7),('Baghdad',22.77),('Baguio',19.5),('Baku',15.1),('Baltimore',13.1),('Bamako',27.8),('Bangkok',28.6),('Bangui',26.0),('Banjul',26.0),('Barcelona',18.2),('Bata',25.1),('Batumi',14.0),('Beijing',12.9),('Beirut',20.9),('Belgrade',12.5),('Belize City',26.7),('Benghazi',19.9),('Bergen',7.7),('Berlin',10.3),('Bilbao',14.7),('Birao',26.5),('Bishkek',11.3),('Bissau',27.0),('Blantyre',22.2),('Bloemfontein',15.6),('Boise',11.4),('Bordeaux',14.2),('Bosaso',30.0),('Boston',10.9),('Bouaké',26.0),('Bratislava',10.5),('Brazzaville',25.0),('Bridgetown',27.0),('Brisbane',21.4),('Brussels',10.5),('Bucharest',10.8),('Budapest',11.3),('Bujumbura',23.8),('Bulawayo',18.9),('Burnie',13.1),('Busan',15.0),('Cabo San Lucas',23.9),('Cairns',25.0),('Cairo',21.4),('Calgary',4.4),('Canberra',13.1),('Cape Town',16.2),('Changsha',17.4),('Charlotte',16.1),('Chiang Mai',25.8),('Chicago',9.8),('Chihuahua',18.6),('Chișinău',10.2),('Chittagong',25.9),('Chongqing',18.6),('Christchurch',12.2),('City of San Marino',11.8),('Colombo',27.4),('Columbus',11.7),('Conakry',26.4),('Copenhagen',9.1),('Cotonou',27.2),('Cracow',9.3),('Da Lat',17.9),('Da Nang',25.8),('Dakar',24.0),('Dallas',19.0),('Damascus',17.0),('Dampier',26.4),('Dar es Salaam',25.8),('Darwin',27.6),('Denpasar',23.7),('Denver',10.4),('Detroit',10.0),('Dhaka',25.9),('Dikson',-11.1),('Dili',26.6),('Djibouti',29.9),('Dodoma',22.7),('Dolisie',24.0),('Douala',26.7),('Dubai',26.9),('Dublin',9.8),('Dunedin',11.1),('Durban',20.6),('Dushanbe',14.7),('Edinburgh',9.3),('Edmonton',4.2),('El Paso',18.1),('Entebbe',21.0),('Erbil',19.5),('Erzurum',5.1),('Fairbanks',-2.3),('Fianarantsoa',17.9),('Flores, Petén',26.4),('Frankfurt',10.6),('Fresno',17.9),('Fukuoka',17.0),('Gabès',19.5),('Gaborone',21.0),('Gagnoa',26.0),('Gangtok',15.2),('Garissa',29.3),('Garoua',28.3),('George Town',27.9),('Ghanzi',21.4),('Gjoa Haven',-14.4),('Guadalajara',20.9),('Guangzhou',22.4),('Guatemala City',20.4),('Halifax',7.5),('Hamburg',9.7),('Hamilton',13.8),('Hanga Roa',20.5),('Hanoi',23.6),('Harare',18.4),('Harbin',5.0),('Hargeisa',21.7),('Hat Yai',27.0),('Havana',25.2),('Helsinki',5.9),('Heraklion',18.9),('Hiroshima',16.3),('Ho Chi Minh City',27.4),('Hobart',12.7),('Hong Kong',23.3),('Honiara',26.5),('Honolulu',25.4),('Houston',20.8),('Ifrane',11.4),('Indianapolis',11.8),('Iqaluit',-9.3),('Irkutsk',1.0),('Istanbul',13.9),('İzmir',17.9),('Jacksonville',20.3),('Jakarta',26.7),('Jayapura',27.0),('Jerusalem',18.3),('Johannesburg',15.5),('Jos',22.8),('Juba',27.8),('Kabul',12.1),('Kampala',20.0),('Kandi',27.7),('Kankan',26.5),('Kano',26.4),('Kansas City',12.5),('Karachi',26.0),('Karonga',24.4),('Kathmandu',18.3),('Khartoum',29.9),('Kingston',27.4),('Kinshasa',25.3),('Kolkata',26.7),('Kuala Lumpur',27.3),('Kumasi',26.0),('Kunming',15.7),('Kuopio',3.4),('Kuwait City',25.7),('Kyiv',8.4),('Kyoto',15.8),('La Ceiba',26.2),('La Paz',23.7),('Lagos',26.8),('Lahore',24.3),('Lake Havasu City',23.7),('Lake Tekapo',8.7),('Las Palmas de Gran Canaria',21.2),('Las Vegas',20.3),('Launceston',13.1),('Lhasa',7.6),('Libreville',25.9),('Lisbon',17.5),('Livingstone',21.8),('Ljubljana',10.9),('Lodwar',29.3),('Lomé',26.9),('London',11.3),('Los Angeles',18.6),('Louisville',13.9),('Luanda',25.8),('Lubumbashi',20.8),('Lusaka',19.9),('Luxembourg City',9.3),('Lviv',7.8),('Lyon',12.5),('Madrid',15.0),('Mahajanga',26.3),('Makassar',26.7),('Makurdi',26.0),('Malabo',26.3),('Malé',28.0),('Managua',27.3),('Manama',26.5),('Mandalay',28.0),('Mango',28.1),('Manila',28.4),('Maputo',22.8),('Marrakesh',19.6),('Marseille',15.8),('Maun',22.4),('Medan',26.5),('Mek\'ele',22.7),('Melbourne',15.1),('Memphis',17.2),('Mexicali',23.1),('Mexico City',17.5),('Miami',24.9),('Milan',13.0),('Milwaukee',8.9),('Minneapolis',7.8),('Minsk',6.7),('Mogadishu',27.1),('Mombasa',26.3),('Monaco',16.4),('Moncton',6.1),('Monterrey',22.3),('Montreal',6.8),('Moscow',5.8),('Mumbai',27.1),('Murmansk',0.6),('Muscat',28.0),('Mzuzu',17.7),('N\'Djamena',28.3),('Naha',23.1),('Nairobi',17.8),('Nakhon Ratchasima',27.3),('Napier',14.6),('Napoli',15.9),('Nashville',15.4),('Nassau',24.6),('Ndola',20.3),('New Delhi',25.0),('New Orleans',20.7),('New York City',12.9),('Ngaoundéré',22.0),('Niamey',29.3),('Nicosia',19.7),('Niigata',13.9),('Nouadhibou',21.3),('Nouakchott',25.7),('Novosibirsk',1.7),('Nuuk',-1.4),('Odesa',10.7),('Odienné',26.0),('Oklahoma City',15.9),('Omaha',10.6),('Oranjestad',28.1),('Oslo',5.7),('Ottawa',6.6),('Ouagadougou',28.3),('Ouahigouya',28.6),('Ouarzazate',18.9),('Oulu',2.7),('Palembang',27.3),('Palermo',18.5),('Palm Springs',24.5),('Palmerston North',13.2),('Panama City',28.0),('Parakou',26.8),('Paris',12.3),('Perth',18.7),('Petropavlovsk-Kamchatsky',1.9),('Philadelphia',13.2),('Phnom Penh',28.3),('Phoenix',23.9),('Pittsburgh',10.8),('Podgorica',15.3),('Pointe-Noire',26.1),('Pontianak',27.7),('Port Moresby',26.9),('Port Sudan',28.4),('Port Vila',24.3),('Port-Gentil',26.0),('Portland (OR)',12.4),('Porto',15.7),('Prague',8.4),('Praia',24.4),('Pretoria',18.2),('Pyongyang',10.8),('Rabat',17.2),('Rangpur',24.4),('Reggane',28.3),('Reykjavík',4.3),('Riga',6.2),('Riyadh',26.0),('Rome',15.2),('Roseau',26.2),('Rostov-on-Don',9.9),('Sacramento',16.3),('Saint Petersburg',5.8),('Saint-Pierre',5.7),('Salt Lake City',11.6),('San Antonio',20.8),('San Diego',17.8),('San Francisco',14.6),('San Jose',16.4),('San José',22.6),('San Juan',27.2),('San Salvador',23.1),('Sana\'a',20.0),('Santo Domingo',25.9),('Sapporo',8.9),('Sarajevo',10.1),('Saskatoon',3.3),('Seattle',11.3),('Ségou',28.0),('Seoul',12.5),('Seville',19.2),('Shanghai',16.7),('Singapore',27.0),('Skopje',12.4),('Sochi',14.2),('Sofia',10.6),('Sokoto',28.0),('Split',16.1),('St. John\'s',5.0),('St. Louis',13.9),('Stockholm',6.6),('Surabaya',27.1),('Suva',25.6),('Suwałki',7.2),('Sydney',17.7),('Tabora',23.0),('Tabriz',12.6),('Taipei',23.0),('Tallinn',6.4),('Tamale',27.9),('Tamanrasset',21.7),('Tampa',22.9),('Tashkent',14.8),('Tauranga',14.8),('Tbilisi',12.9),('Tegucigalpa',21.7),('Tehran',17.0),('Tel Aviv',20.0),('Thessaloniki',16.0),('Thiès',24.0),('Tijuana',17.8),('Timbuktu',28.0),('Tirana',15.2),('Toamasina',23.4),('Tokyo',15.4),('Toliara',24.1),('Toluca',12.4),('Toronto',9.4),('Tripoli',20.0),('Tromsø',2.9),('Tucson',20.9),('Tunis',18.4),('Ulaanbaatar',-0.4),('Upington',20.4),('Ürümqi',7.4),('Vaduz',10.1),('Valencia',18.3),('Valletta',18.8),('Vancouver',10.4),('Veracruz',25.4),('Vienna',10.4),('Vientiane',25.9),('Villahermosa',27.1),('Vilnius',6.0),('Virginia Beach',15.8),('Vladivostok',4.9),('Warsaw',8.5),('Washington,D.C.',14.6),('Wau',27.8),('Wellington',12.9),('Whitehorse',-0.1),('Wichita',13.9),('Willemstad',28.0),('Winnipeg',3.0),('Wrocław',9.6),('Xi\'an',14.1),('Yakutsk',-8.8),('Yangon',27.5),('Yaoundé',23.8),('Yellowknife',-4.3),('Yerevan',12.4),('Yinchuan',9.0),('Zagreb',10.7),('Zanzibar City',26.0),('Zürich',9.3);

SET stations_qid = LAST_QUERY_ID();     



-- GENERATE the data (measurements) - should be done just once for specific number of measurements (e.g. 1 Billion)

SET number_of_measurements = 1000000000;    -- 1 Billion

ALTER WAREHOUSE CONTEST_WH SET WAREHOUSE_SIZE = 'XLARGE' WAIT_FOR_COMPLETION = TRUE;
ALTER WAREHOUSE RESUME IF SUSPENDED;
SELECT * FROM table(result_scan($stations_qid)); -- this is to avoid impact of the virtual warehouse's provisioning time, esp. for the humongous ones (5XL, 6XL) - can be any query touching the engine...

EXECUTE IMMEDIATE $$
DECLARE 
    number_of_stations CURSOR FOR (select count(*) from table(result_scan($stations_qid)));
    stations_with_temps RESULTSET DEFAULT (
        select replace(replace(array_to_string(array_agg(object_construct('station_name', city, 'measurement', averagetemp)), ', '), '\'', '\\\''), '\"', '\'') as res
        from table(result_scan($stations_qid))
    );
    cur1 CURSOR FOR stations_with_temps;
BEGIN
    LET num_of_stations INTEGER := 0;
    OPEN number_of_stations;
    FETCH number_of_stations INTO num_of_stations;
    
    LET sql STRING := '';
    sql := sql || 'WITH 
    stations_avgtemp(sid) AS (
    SELECT [  ';

    FOR row_variable IN cur1 DO
      sql := sql || row_variable.res;
    END FOR;
    
    sql := sql || ']
    )
    select m:station_name as station_name, (m:measurement + normal(0, 100, random()))::DECIMAL(8,1) as measurement
    from (
        select 
            (select sid[id] as station_name from stations_avgtemp) as m
        from (
            SELECT uniform(0, ' || (num_of_stations-1)::varchar || ', random()) as id
            FROM TABLE(GENERATOR(ROWCOUNT => $number_of_measurements))
        )
    )';

    EXECUTE IMMEDIATE sql;
    LET qid STRING := (LAST_QUERY_ID());
    RETURN qid;
END;
$$;

-- "SAVE" the generated data - you can refer to this data later by the generation Query ID (UUID) 
-- make sure to note what UUID is for which dataset (or chck the Query history in Snowflake UI):
set gen_qid = LAST_QUERY_ID(-2);
select $gen_qid; 

-- for example, later you can change it like this: set gen_qid = '01b17d48-0303-45b3-0000-0001fbde602d';    -- UUID for 1 Billion rows
-- the 'gen_qid' session variable is used by the aggregation/calculation query below


-------------------------------------------------------------------------------------------------------------------------
-- let's materialize the data into regular Snowflake tables
CREATE OR REPLACE DATABASE benchmark_DB;
CREATE OR REPLACE TABLE measurements_table AS 
SELECT * FROM TABLE(RESULT_SCAN($gen_qid));
select count(*) from measurements_table;    -- 1 billion




----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------

-- run the contest calculation - this can be done many times on the already pre-generated data 

-- HERE
SET WH_SIZE = 'XLARGE';     -- THIS IS THE T-SHIRT SIZE OF THE SNOWFLAKE ENGINE USED FOR RUNNING THE CONTEST CALCULATION


-- run all the instructions below (one by one or better all of them at once, up to the last line)
ALTER WAREHOUSE CONTEST_WH SET WAREHOUSE_SIZE = $WH_SIZE WAIT_FOR_COMPLETION = TRUE;
ALTER WAREHOUSE CONTEST_WH RESUME IF SUSPENDED;
USE WAREHOUSE CONTEST_WH;
SELECT * FROM TABLE(RESULT_SCAN($gen_qid)) LIMIT 10; -- this is to avoid impact of the virtual warehouse's provisioning/queueing time (esp. the humongous engines: 5XL/6XL) - can be any query touching the engine...

SET QUERY_TAG = (SELECT 'BRC:' || $number_of_measurements/1000000000 || ':' || $WH_SIZE);   -- tagging, makes it easier to find in the query history
ALTER SESSION SET QUERY_TAG = $QUERY_TAG;

-- calculations with proper output (shamelessly stolen from the DuckDB's post - but Snowflake-adjusted :-))
WITH src AS 
    (SELECT station_name,
            MIN(measurement) AS min_measurement,
            AVG(measurement) AS mean_measurement,
            MAX(measurement) AS max_measurement
    FROM measurements_table                                  -- here it is querying the Snowflake table
    -- change to: FROM TABLE(RESULT_SCAN($gen_qid))    for reading data from the Snowflake Query Results 
    GROUP BY station_name
    )
SELECT '{' ||
        ARRAY_TO_STRING(ARRAY_SORT(ARRAY_AGG(station_name || '=' || CONCAT_WS('/',min_measurement::DECIMAL(8,1), mean_measurement::DECIMAL(8,1), max_measurement::DECIMAL(8,1)))),', ') ||
        '}' AS "1BRC"
FROM src;

-- cleanup
ALTER SESSION UNSET QUERY_TAG;
ALTER WAREHOUSE CONTEST_WH SUSPEND;
SELECT SYSTEM$WAIT(1);
ALTER WAREHOUSE CONTEST_WH SET WAREHOUSE_SIZE = 'XSMALL';

[AlexanderYastrebov]

Snowflake is a distributed system

How many shards this was running on?

[waldekkot]

Hi Alexander, Snowflake does not publish such details, because we try to simplify all of the platform's internals by exposing easy to comprehend "T-shirt" compute engines' sizes (incl. the underlying cloud infrastructure details, which differ among the cloud providers). The idea here is simple: each size roughly doubles in compute power (and this is achieved by a combination of multiple nodes/VMs, but also characteristics of those nodes). In the end query performance is what counts (and ease of use, low cognitive load), not the nitty-gritty details of the (cloud) hardware behind the scenes. One of the goals is also to ensure the service works and feels the same, (largely) independently of what cloud provider the end customer has chosen (not to mention that the underlying cloud infrastructure evolves over time and this too is something what the end user should not be bothered about either, like the transition to Graviton in case of AWS EC2).
Of course, Snowflake is highly distributed, so for the large sizes which have been used for the contest's fun, this is tens of nodes. If you want to know more about the high-level Snowflake architecture, take a look at some of the academic papers (there has been a number of engineering/commercial decisions taken over the years, but the papers are still valid):

• https://dl.acm.org/doi/pdf/10.1145/2882903.2903741
• https://www.usenix.org/system/files/nsdi20-paper-vuppalapati.pdf
• https://www.micahlerner.com/2023/01/19/elastic-cloud-services-scaling-snowflakes-control-plane.html

[joeharris76]

This is not a secret

https://stackoverflow.com/questions/58973007/what-are-the-specifications-of-a-snowflake-server

one part states the server EC2 instance type as "warehouseServerType" : "c5d.2xlarge",

https://select.dev/posts/snowflake-warehouse-sizing

it’s fairly well known that on AWS each node (except for 5XL and 6XL warehouses) is a c5d.2xlarge EC2 instance, with 16GB of RAM and a 200GB SSD.

https://medium.com/snowflake/how-many-nodes-are-in-a-snowflake-virtual-warehouse-4452eed1979d

Each increase in warehouse size will double the number of nodes available to the query, starting with 1 node for an X-Small warehouse and scaling up to 512 nodes for a 6X-Large warehouse.

However they now have Graviton (ARM) in the mix

https://aws.amazon.com/blogs/apn/how-snowflake-optimized-its-virtual-warehouses-for-sustainability-using-aws-graviton/

Snowflake reduced its carbon footprint per Snowflake virtual warehouse credit by an estimated 57% by transitioning workloads from x86 to Graviton-based instances.

You can compare the sizes used by Fivetran in their benchmark to see that the above is roughly correct. See How did we configure the warehouses?. https://www.fivetran.com/blog/warehouse-benchmark