Cloud-Native Analytics Compared, in depth: BigQuery, Snowflake, Redshift, Databricks SQL, Athena and Fabric by architecture, scaling, caching and billing unit

The design they all share

Cloud-native analytics engines are built on three ideas. Storage and compute are separated: data lives in durable object storage, and compute nodes are stateless workers that can be added, removed or shared. Data is columnar and compressed, with per-file statistics such as min and max values so the engine can skip files that cannot match a filter; the columnar database deep dive explains the encodings and vectorised execution. And a services layer, sometimes called the cloud services or control plane, parses and plans queries, holds metadata, enforces access control and caches results.

Given that shared shape, the differences come down to four questions. Who sizes compute: you (a warehouse or cluster size), the service (serverless slots or capacity), or a mix? What is the billing unit: bytes scanned, compute time, or reserved capacity? How is concurrency handled: queueing, automatic scale-out, or workload isolation into separate pools? And is the storage format open, so other engines can read the same data without copying it?

BigQuery: serverless slots over Capacitor

BigQuery has no clusters to size. Queries are executed by slots, units of compute that the service schedules dynamically, over data stored in the Capacitor columnar format on Google's distributed storage, with shuffle held in a separate in-memory tier. You choose between two billing models. On-demand bills bytes processed, at 6.25 US dollars per TiB with the first TiB each month free, and the service decides how many slots your query gets. Capacity pricing, through BigQuery editions, bills slot-hours, with autoscaling between a baseline and a maximum you set.

The consequence is that on-demand cost is a property of the query, not the runtime: selecting fewer columns and filtering on partition and cluster columns directly cuts the bill, while a fast query that scans everything is expensive. Always dry-run large queries and set a maximum bytes billed guard. The architecture in detail, including Dremel and BI Engine, is in the BigQuery architecture deep dive.

# estimate before you pay: dry run reports bytes, bills nothing
bq query --use_legacy_sql=false --dry_run \
  'SELECT user_id, SUM(amount) FROM shop.orders
   WHERE order_date BETWEEN "2026-09-01" AND "2026-09-29" GROUP BY user_id'

# hard stop: fail the query rather than scan more than 50 GB
bq query --use_legacy_sql=false --maximum_bytes_billed=50000000000 '...'

Snowflake: virtual warehouses you size

Snowflake separates a cloud services layer from virtual warehouses, which are compute clusters you create and size in T-shirt sizes. Each size step doubles both compute and credits per hour: extra small is 1 credit per hour, small 2, medium 4, large 8. Warehouses bill per second while running, with a 60-second minimum every time a warehouse resumes, and auto-suspend after an idle period you configure. Tables are stored as immutable micro-partitions with per-column metadata used for pruning, and a result cache returns identical repeated queries without running a warehouse.

Isolation is explicit: give ELT, dashboards and data science separate warehouses so one cannot starve another. Concurrency is handled by multi-cluster warehouses, which add clusters of the same size when queries queue and remove them when load falls. The trade-off is that sizing is your job. An oversized warehouse wastes credits every second it runs; an undersized one spills to disk and runs long. The 60-second minimum also means that very short auto-suspend settings with bursty traffic can cost more than a slightly longer idle window.

Redshift: provisioned nodes or serverless RPUs

Amazon Redshift started as a shared-nothing cluster database and moved toward separation with RA3 nodes, whose managed storage keeps data durable in S3 and caches hot blocks on local SSD. You can run provisioned clusters, billed per node-hour, or Redshift Serverless, billed in Redshift Processing Unit hours with a configurable base capacity. Concurrency scaling adds transient capacity for queued read queries, and Redshift Spectrum queries data in S3 directly.

Redshift rewards tables designed for it: sort keys drive block skipping and distribution styles decide how much data moves between nodes during joins, which ties it more closely to classic warehouse modelling than BigQuery or Snowflake. It fits teams heavily invested in AWS who want warehouse semantics with tight IAM and S3 integration.

Databricks SQL, Athena and Fabric: engines over open tables

Three engines put open file formats at the centre. Databricks SQL runs SQL warehouses, in serverless, pro or classic types, over Delta Lake tables in your own object storage, using the vectorised Photon engine. Billing is in Databricks Units, plus the cloud provider's virtual machine charges for non-serverless types. Because the tables are Delta files in your account, Spark jobs, streaming pipelines and other engines read the same data; the Spark SQL deep dive covers the engine family underneath.

Amazon Athena is serverless SQL over S3, built on Trino in engine version 3, reading Parquet, ORC, Iceberg and other formats through the Glue Data Catalog. On-demand it bills 5 US dollars per TB scanned, rounded up to the megabyte with a 10 MB minimum per query, so it is cheap for occasional exploration and expensive for scan-heavy dashboards; provisioned capacity is available for steady workloads.

Microsoft Fabric combines warehouse and lakehouse experiences over OneLake, storing tables as Delta Parquet, and bills a shared capacity measured in capacity units across all its workloads. That makes it predictable for Microsoft-centred organisations, and makes noisy-neighbour management between workloads part of capacity planning.

The comparison that matters

Read the table as a set of fits. Scan-billed engines (BigQuery on-demand, Athena) suit spiky, ad hoc work and punish repeated full scans. Time-billed engines (Snowflake, Databricks SQL, Redshift Serverless) suit steady, heavy workloads and punish idle compute. Capacity models (BigQuery editions, Fabric, provisioned Redshift) trade flexibility for predictable bills. Open storage matters when more than one engine must read the data, and matters less when one warehouse is the whole platform.

Worked example: pricing one workload two ways

Consider a retailer with a 20 TB orders fact table, partitioned by day. Fifty analysts use dashboards that run 4,000 queries a day, each scanning about 2 GB after partition pruning. Nightly ELT rebuilds aggregates, scanning about 3 TB.

On a scan-billed engine, dashboards scan 4,000 times 2 GB, 8 TB a day, and ELT another 3 TB, for 11 TB a day. At BigQuery's on-demand 6.25 dollars per TiB, 11 TB is about 10 TiB, so roughly 62 dollars a day before caching. Result caching and BI Engine lower the dashboard part; one analyst's accidental full scan of the table adds about 114 dollars in a single query, which is why the bytes-billed guard matters.

On a time-billed engine, cost follows how long compute runs. Suppose a medium Snowflake warehouse at 4 credits per hour serves dashboards for 10 business hours, and a large warehouse at 8 credits per hour runs ELT for 1.5 hours. That is 40 plus 12, 52 credits a day, multiplied by your contracted credit price. The dashboard warehouse's cost is almost independent of how many queries run, until queueing forces multi-cluster scale-out.

The lesson is not that one is cheaper. It is that the cost drivers differ: bytes per query on one side, hours of provisioned compute on the other. Measure both drivers for your workload before choosing, and design for the one you pick: partitioning and clustering for scan billing, auto-suspend and warehouse right-sizing for time billing.

Benchmark fairly, then attribute cost

Vendor benchmarks rarely match your queries. A fair internal benchmark uses your own top queries by frequency and cost, the same data loaded with each engine's recommended layout, cold and warm runs reported separately, concurrency at your real peak, and cost measured from the billing system rather than estimated.

import statistics, time

def run_suite(conn, queries, runs=5, disable_result_cache=None):
    """queries: {name: sql}. Returns per-query cold time and warm median."""
    if disable_result_cache:
        disable_result_cache(conn)          # e.g. session setting, engine-specific
    results = {}
    for name, sql in queries.items():
        times = []
        for i in range(runs):
            t0 = time.perf_counter()
            cur = conn.cursor()
            cur.execute(sql)
            cur.fetchall()
            times.append(time.perf_counter() - t0)
        results[name] = {"cold_s": times[0], "warm_median_s": statistics.median(times[1:])}
    return results

Then attribute spend to teams and queries using each engine's own system views, so the benchmark's conclusions can be checked in production:

-- BigQuery: bytes billed per user, last 7 days
SELECT user_email, SUM(total_bytes_billed) / POW(1024, 4) AS tib_billed
FROM `region-us`.INFORMATION_SCHEMA.JOBS
WHERE creation_time > TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY user_email ORDER BY tib_billed DESC;

-- Snowflake: credits per warehouse, last 7 days
SELECT warehouse_name, SUM(credits_used) AS credits
FROM snowflake.account_usage.warehouse_metering_history
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY warehouse_name ORDER BY credits DESC;

-- Databricks: DBUs per SKU, last 7 days
SELECT sku_name, SUM(usage_quantity) AS dbus
FROM system.billing.usage
WHERE usage_date > current_date() - INTERVAL 7 DAYS
GROUP BY sku_name ORDER BY dbus DESC;

Failure modes and trade-offs

The deeper trade-off is control versus convenience. Serverless engines remove sizing mistakes and add bill surprises; sized warehouses make bills predictable and make idle time your problem. Managed formats give the engine freedom to optimise; open formats give you freedom to change engines. Workloads that mix transactional and analytical access need a different design altogether, covered in the OLTP versus OLAP deep dive.

What to do next

1. Export your top 50 queries by frequency and by cost from your current system.
2. Classify your workload as spiky or steady, scan-heavy or compute-heavy, and single-engine or multi-engine.
3. Shortlist two engines whose billing unit matches that shape, one scan-billed or capacity-billed and one time-billed if unsure.
4. Load the same data with each engine's recommended partitioning and clustering or sort keys.
5. Run the benchmark harness with cold and warm runs at your real peak concurrency.
6. Read actual cost from each engine's billing views, not from calculators.
7. Put guardrails in before go-live: bytes-billed caps, auto-suspend, isolated compute for ELT and dashboards.
8. Schedule a monthly cost-attribution review per team using the system-view queries above.