BigQuery vs Snowflake vs Redshift: A Comparison

BigQuery, Snowflake and Amazon Redshift are more alike than their marketing suggests. All three store data in compressed columns, separate storage from compute, run SQL in parallel across many machines and can query files in object storage. A team that has learned one can be productive on another within weeks. The differences that matter are practical: what you have to manage, what unit you are billed in, and which settings you tune. This comparison is drawn from each vendor's own documentation.

At a glance

Three architectures

BigQuery has no cluster. It is serverless: storage and compute are separate layers, and you do not provision either (Google Cloud: BigQuery overview). A query is given compute, measured in slots, for as long as it runs. There is nothing to start, size or stop. See BigQuery explained.

Snowflake has warehouses you size. Its architecture has three layers: storage, compute and a cloud services layer that coordinates them. Compute comes as virtual warehouses, each an independent cluster that shares nothing with the others (Snowflake: key concepts). You choose a size, from X-Small upwards, and can run several warehouses over the same data, so the finance team's reports do not slow the data engineers' loads. See Snowflake explained.

Redshift has a cluster, or a serverless option. The classic form is a cluster with a leader node that plans queries and compute nodes that run them. It is based on PostgreSQL, which existing tools appreciate. Redshift Managed Storage lets storage grow independently of the nodes (AWS: Redshift architecture). Redshift Serverless removes the cluster and provisions capacity automatically (AWS: Redshift Serverless). See Amazon Redshift explained.

What you are billed for

This is where the three differ most, and where surprises come from.

• BigQuery on demand bills the bytes a query reads. Time and idleness do not enter into it. Its alternative, capacity pricing, bills slots per second with a one-minute minimum (Google Cloud: BigQuery pricing).
• Snowflake bills credits for each second a warehouse runs, with a 60-second minimum every time it starts. An X-Small warehouse uses 1 credit an hour and each size up doubles that. Warehouses suspend when idle and resume on the next query (Snowflake: warehouses).
• Redshift provisioned bills an hourly rate for each node while the cluster runs, and pausing the cluster suspends that charge (AWS: Redshift pricing). Redshift Serverless bills capacity, in Redshift Processing Units, per second with a 60-second minimum (AWS: Redshift Serverless billing).

So BigQuery on demand charges for data, and the others charge for time. With time-based billing, the bill depends less on how long queries run than on how long compute stays up around them.

Idle time is the bill

The script models one working day: 54 queries of 20 seconds each, busy for an hour and sparse afterwards. It applies two documented rules, per-second billing with a 60-second minimum, and a wait before idle compute is suspended. It is a model of those rules, not any of the three products, and it uses no prices.

# A model of how the same day of queries is billed under time-based compute. It applies two
# documented rules: per-second billing with a 60-second minimum each time compute starts, and an
# idle period before compute is suspended. It is not Snowflake, Redshift or BigQuery, and it uses no
# prices: the result is seconds billed, to compare with seconds actually spent running queries.
QUERY_SECONDS = 20
MINIMUM = 60

# One working day, as seconds after 09:00: a busy hour, then a query every 20 minutes until 18:00.
arrivals = [n * 120 for n in range(30)] + [3600 + n * 1200 for n in range(1, 25)]


def billed(idle_before_suspend):
    """Seconds billed when compute stops after the given idle period, with a 60-second minimum."""
    total, starts, running_since, busy_until = 0, 0, None, None
    for start in arrivals:
        if running_since is not None and start > busy_until + idle_before_suspend:
            total += max(MINIMUM, busy_until + idle_before_suspend - running_since)   # suspend, then bill
            running_since = None
        if running_since is None:
            running_since, starts = start, starts + 1
            busy_until = start + QUERY_SECONDS
        else:
            busy_until = max(busy_until, start + QUERY_SECONDS)
    total += max(MINIMUM, busy_until + idle_before_suspend - running_since)
    return total, starts


busy = len(arrivals) * QUERY_SECONDS
print(f"{len(arrivals)} queries of {QUERY_SECONDS} seconds: {busy:,} seconds of work\n")
print(f"{'compute stops after':24} {'starts':>6} {'seconds billed':>15} {'billed per second of work':>26}")
for label, idle in [("the query ends", 0), ("1 minute idle", 60), ("5 minutes idle", 300), ("10 minutes idle", 600)]:
    seconds, starts = billed(idle)
    print(f"{label:24} {starts:6} {seconds:15,} {seconds / busy:26.1f}")
print(f"{'never (always on, 9 h)':24} {1:6} {9 * 3600:15,} {9 * 3600 / busy:26.1f}")

54 queries of 20 seconds: 1,080 seconds of work

compute stops after      starts  seconds billed  billed per second of work
the query ends               54           3,240                        3.0
1 minute idle                54           4,320                        4.0
5 minutes idle               25          11,480                       10.6
10 minutes idle              25          18,980                       17.6
never (always on, 9 h)        1          32,400                       30.0

The queries need 1,080 seconds of work. What is billed ranges from three times that to thirty times:

• The 60-second minimum alone triples it. A 20-second query is billed as 60.
• Each minute of idle wait adds up. With compute suspended after 10 idle minutes, 18,980 seconds are billed, nearly eighteen times the work done.
• Always-on compute bills the whole day.

Two lessons follow. First, the idle timeout is the most important cost setting on time-based compute. Snowflake recommends a low value, for example 5 or 10 minutes or less, matched to the gaps in your workload (Snowflake: warehouse considerations). Second, many short, scattered queries favour byte-based billing, while a steady stream of heavy queries over large tables favours time-based billing, where the data scanned is not charged at all.

What you tune

BigQuery: you declare a partition column and up to four clustering columns, and you write queries that filter on them. That is nearly all of it. See partitioning and clustering.

Snowflake: tables are divided automatically into micro-partitions of 50 to 500 MB of uncompressed data, each recording the range of values it holds, so pruning happens without any declaration (Snowflake: micro-partitions). The main lever is the warehouse: its size, how many there are, and when they suspend.

Redshift: tables have a sort key and a distribution style, which decides how rows are spread across nodes. Both now default to automatic, and AWS recommends leaving them so (AWS: sort keys; AWS: distribution styles). On a provisioned cluster you also choose node type and count. On serverless you set a base capacity, 128 RPUs by default and adjustable from 4 to 512 (AWS: serverless capacity).

Each has moved towards automation. The practical difference is where the remaining decisions sit: in table design for BigQuery, in warehouse configuration for Snowflake, in capacity and keys for Redshift.

Which cloud

BigQuery runs on Google Cloud and Redshift on AWS. Snowflake runs on AWS, Azure and Google Cloud (Snowflake: supported cloud platforms).

This often decides the matter. A warehouse sits beside the data and the pipelines that feed it. Moving data between clouds costs money and adds delay, so a company on AWS will look first at Redshift and Snowflake, and one on Google Cloud at BigQuery and Snowflake. GCP vs AWS vs Azure compares the platforms around them.

Choosing

1. Which cloud holds your data and your team's skills? Start there.
2. Do you want to manage compute at all? If not, BigQuery or a serverless option fits. If you want explicit control and isolation per team, Snowflake's warehouses provide it most directly.
3. What does your workload look like? Occasional, unpredictable queries suit pay-per-query. Constant heavy use suits reserved or always-on capacity, on any of the three.
4. Do you need more than one cloud? Snowflake is the one that runs natively on all three.
5. What do your tools expect? PostgreSQL compatibility points to Redshift.

Then test with your own data and queries. Published benchmarks rarely resemble a real workload, and a week of trial on each shortlisted option tells you more than any comparison article, this one included.

Cloud data warehouses are covered in the Data Engineering course.

Sources

• BigQuery overview (Google Cloud Documentation) — Google Cloud
• BigQuery pricing (Google Cloud) — Google Cloud
• Snowflake key concepts and architecture (Snowflake Documentation) — Snowflake
• Overview of warehouses (Snowflake Documentation) — Snowflake
• Warehouse considerations (Snowflake Documentation) — Snowflake
• Micro-partitions and data clustering (Snowflake Documentation) — Snowflake
• Supported cloud platforms (Snowflake Documentation) — Snowflake
• Data warehouse system architecture (Amazon Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift Serverless (Amazon Redshift Management Guide) — AWS Documentation
• Compute capacity for Amazon Redshift Serverless (Amazon Redshift Management Guide) — AWS Documentation
• Billing for Amazon Redshift Serverless (Amazon Redshift Management Guide) — AWS Documentation
• Distribution styles (Amazon Redshift Database Developer Guide) — AWS Documentation
• Sort keys (Amazon Redshift Database Developer Guide) — AWS Documentation
• Amazon Redshift Pricing (AWS) — AWS