--- title: 'Exploring Efficiency in the Cloud: The One Billion Rows Challenge with Snowflake and Databend' date: 2024-01-05 cover_url: '20240107-213938.jpeg' tags: [databend, snowflake, duckdb] category: Product slug: category-product/2024-01-05-1brows authors: - BohuTANG --- Gunnar Morling's challenge, initially posed to Java users, involved processing a dataset of 1 billion rows, focusing on temperature measurement values. This challenge, highlighted in his blog post, [The One Billion Row Challenge](https://www.morling.dev/blog/one-billion-row-challenge/), has intrigued many in the database community. While the original challenge was Java-centric, it sparked interest across various databases, examining their speed and efficiency. Efforts like the [1BRC in SQL with DuckDB](https://rmoff.net/2024/01/03/1%EF%B8%8F%E2%83%A3%EF%B8%8F-1brc-in-sql-with-duckdb/) and [1 billion rows challenge in PostgreSQL and ClickHouse](https://ftisiot.net/posts/1brows/) demonstrate this widespread fascination. {/* truncate */} In this post, let's shift the focus to cloud data warehouses, particularly Snowflake and Databend Cloud, exploring how they handle such extensive datasets. The tests are aimed at understanding how these platforms efficiently sort and group large-scale data, especially when stored in cloud-based object storage. The test involves two main tasks: - Directly sort and group 1 billion rows of data stored in object storage. - Load these rows into a cloud data warehouse, then perform sorting and grouping. The dataset used for this test is in the Parquet format, known for its columnar storage efficiency. Hosted on AWS S3 and publicly accessible [here](https://wizardbend.s3.us-east-2.amazonaws.com/databend-doc/1brc/measurements.parquet) (5.1 GB), it allows anyone to replicate these tests on Snowflake and Databend Cloud. :::info The tests use a MEDIUM(32vCPU) warehouse size in Snowflake and Databend Cloud to process a dataset stored in the AWS US East (Ohio) region. The cost is $8 per hour for Snowflake and $4 per hour for Databend Cloud. ::: ## Snowflake vs. Databend: A Brief Overview **[Snowflake](https://www.snowflake.com)** is a well-established cloud-based data warehouse that provides a SQL interface for querying data. It employs a columnar database that stores data in cloud-based object storage like AWS S3. Snowflake has gained popularity for its user-friendliness and scalability in data warehousing. **[Databend Cloud](https://www.databend.com)** represents the new wave of cloud data warehouses, designed to handle massive-scale analytics with reduced cost and complexity. It's built on [Databend](https://github.com/databendlabs/databend), the open-source alternative to Snowflake, built from scratch in Rust, offering similar capabilities. ## Preparations: Generate Parquet File The tests follow the steps similar to Robin Moffatt for [generating the raw data](https://rmoff.net/2024/01/03/1%EF%B8%8F%E2%83%A3%EF%B8%8F-1brc-in-sql-with-duckdb/#setup) and [exporting it to a Parquet file](https://rmoff.net/2024/01/03/1%EF%B8%8F%E2%83%A3%EF%B8%8F-1brc-in-sql-with-duckdb/#extra-bonus-bit---using-parquet). ## Snowflake vs. Databend: Sort & Group Data in Object Storage ### Sort & Group Data in Object Storage with Snowflake Run the following SQL commands in Snowflake: ```sql CREATE OR REPLACE STAGE wizardbend URL='s3://wizardbend/'; CREATE OR REPLACE FILE FORMAT myformat TYPE = 'parquet'; SELECT $1:station_name, MIN($1:measurement) AS min_measurement, AVG($1:measurement) AS mean_measurement, MAX($1:measurement) AS max_measurement FROM @wizardbend/databend-doc/1brc/measurements.parquet (file_format => 'myformat') GROUP BY $1:station_name ORDER BY $1:station_name; ``` Result: | | | | ---- | ------- | | Time | 21m 35s | | Cost | $2.87 | One Billion Row Challenge ### Sort & Group Data in Object Storage with Databend Cloud In Databend Cloud, run the following commands: ```sql CREATE STAGE IF NOT EXISTS wizardbend URL = 's3://wizardbend/' CONNECTION = (ALLOW_ANONYMOUS = 'true'); SELECT station_name, MIN(measurement) AS min_measurement, AVG(measurement) AS mean_measurement, MAX(measurement) AS max_measurement FROM @wizardbend/databend-doc/1brc/measurements.parquet GROUP BY station_name ORDER BY station_name; ``` Result: | | | | ---- | ----- | | Time | 9.8s | | Cost | $0.01 | One Billion Row Challenge ## Snowflake vs. Databend: Load & Process Data in Cloud Data Warehouses Next, we'll examine the efficiency of Snowflake and Databend Cloud by loading, sorting, and grouping 1 billion rows of data. ### Load 1 Billion Rows into Snowflake ```sql CREATE TABLE onebrc ( station_name VARCHAR NULL, measurement DOUBLE NULL ); COPY INTO onebrc FROM ( SELECT $1:station_name::VARCHAR, $1:measurement::DOUBLE FROM @wizardbend/databend-doc/1brc/measurements.parquet (FILE_FORMAT => 'myformat') ); ``` Result: | | | | ---- | ------ | | Time | 26m 7s | | Cost | $3.48 | One Billion Row Challenge ### Load 1 Billion Rows into Databend Cloud ```sql CREATE TABLE onebrc ( station_name VARCHAR NULL, measurement DOUBLE NULL ); COPY INTO onebrc FROM ( SELECT $1:station_name::VARCHAR, $1:measurement::DOUBLE FROM @wizardbend/databend-doc/1brc/measurements.parquet (FILE_FORMAT => 'myformat') ); ``` Result: | | | | ---- | ----- | | Time | 19.1s | | Cost | $0.02 | One Billion Row Challenge ### Sort & Group 1 Billion Rows in Snowflake Excluding the utilization of cache (result cache and local disk cache), the SQL query is as follows: ```sql SELECT station_name, MIN(measurement) AS min_measurement, AVG(measurement) AS mean_measurement, MAX(measurement) AS max_measurement FROM onebrc GROUP BY station_name ORDER BY station_name; ``` Result: | | | | ---- | ------ | | Time | 3.1s | | Cost | $0.007 | One Billion Row Challenge ### Sort & Group 1 Billion Rows in Databend Cloud Again, without result caching: ```sql SELECT station_name, MIN(measurement) AS min_measurement, AVG(measurement) AS mean_measurement, MAX(measurement) AS max_measurement FROM onebrc GROUP BY station_name ORDER BY station_name; ``` Result: | | | | ---- | ------ | | Time | 3.6s | | Cost | $0.004 | One Billion Row Challenge ## Conclusion All the comparisons presented can be independently run and verified on both Snowflake and Databend Cloud platforms: - Snowflake ($400 voucher): [https://signup.snowflake.com/](https://signup.snowflake.com/) - Databend Cloud ($200 voucher): [https://app.databend.com/register](https://app.databend.com/register) | Task | Snowflake (Cost & Time) | Databend Cloud (Cost & Time) | | ------------------- | ----------------------- | ---------------------------- | | Read Parquet File | $2.87 (21m 35s) | $0.01 (9.8s) | | Load 1 Billion Rows | $3.48 (26m 7s) | $0.02 (19.1s) | | Read Native Format | $0.007 (3.1s) | $0.004 (3.6s) | | **Total** | **$6.357** | **$0.034** | In this benchmark comparison between Snowflake and Databend, it's evident that cloud data warehouses, particularly Databend Cloud, excel in efficiently processing large datasets stored in object storage like AWS S3. They offer not only superior speed but also remarkable cost-effectiveness, making them the go-to choice for handling complex data challenges.