Firebolt vs Snowflake

Query

Duration

Duration

Perf.boost

$1.54 / hour

Engine size:

1 x c5d.4xlarge

$16 / hour

Warehouse size:

Large

#5

#1

#2

#3

#4

0.09 sec

72 sec

62 sec

90 sec

60 sec

1.88 sec

0.01 sec

0.16 sec

X388

X800

X48

X6000

83 sec

1.6 sec

X52

Disclaimer - This is not a global benchmark. The results are based on real world queries and run-times as reported by our users over Snowflake, and their equivalent run-times over the same data in Firebolt after tuning and optimization

Here's one of the Snowflake queries (fields/values are masked of course):

It's a simple query. No joins, only one big table. The other queries weren't very different conceptually, so let's stick to this example. The query essentially aggregates over a few dimensions, and returns sums with a combination of case statements. The query is filtered by a few of the dimensions.

Why even simple queries can be slow in cloud data warehouses?

Unlike in the on-premise world, modern cloud data engines have a much more complicated relationship with storage. In the cloud we enjoy infinite storage, which is great. But the infinite storage layer of S3 (I will talk in AWS lingo throughout the post, but the same concepts/challenges are true for all cloud providers) is far from optimal for performance.

When queries aren't waiting in the cache, and the query engine has to scan data in S3, that's when you typically go and get coffee. Queries are too slow for interactive analytics if large data scans over S3 are involved. This is something that most Athena/Presto users know very well. Since these query engines scan data directly in S3, they run into slowdowns very often when data volumes are significant.

All cloud data warehouses have various techniques for storing data in a smarter way and serving it to the query engine in a faster way. This is why cloud data warehouses are typically faster than Athena/Presto for non-cached queries.

But at the end of the day, at scale, even the modern cloud data warehouses have to move too much data between S3 and the SSD of the compute nodes to stay performant.