Snowflake, BigQuery, Databricks and Synapse

Four platforms that job descriptions list together as if they were interchangeable. They are not, and the differences that matter in an interview are the compute model and how you are billed for it.

The one idea they share

All four separate storage from compute. Data sits in columnar files on object storage; query engines are spun up against it and can be resized or killed without touching the data.

That is what makes them different from a classic warehouse, and it has three consequences worth stating: you can run two workloads at different sizes over one dataset, idle costs approach storage-only, and scaling up is a configuration change rather than a migration.

Everything below is a variation on how each vendor exposes that compute.

Snowflake: explicit clusters

Compute is a virtual warehouse - a named, sized cluster you start, resize and suspend. Two teams get two warehouses over the same tables, and neither can slow the other down.

ALTER WAREHOUSE etl_wh SET
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 60;

Storage is immutable micro-partitions (roughly 50-500MB compressed), each carrying min/max metadata. There are no indexes; pruning comes from that metadata, and a clustering key controls how well it works on large tables.

The two features that come up most:

• Zero-copy clone. A clone copies metadata, not data, so a full-size test environment is instant and nearly free until it diverges.
• Time travel. Query a table as of a past timestamp, within a retention window. Recovering from a bad UPDATE is a query, not a restore.

Billing is per second of warehouse uptime, so the failure mode is idle warehouses left running, and AUTO_SUSPEND is the fix.

BigQuery: no cluster at all

BigQuery is serverless. There is nothing to size or suspend; the engine allocates slots - units of parallelism - from a shared pool.

Two pricing models, and confusing them is the classic mistake:

Under on-demand, SELECT * on a wide table is expensive and the same query with three columns is cheap, because columnar storage only reads what you ask for. This is why SELECT * is a costly habit specifically here.

Tuning is partitioning and clustering, not indexes:

CREATE TABLE events
PARTITION BY DATE(created_at)
CLUSTER BY tenant_id, event_type
AS SELECT * FROM staging;

Partition pruning is what stops a query scanning years of history, and a query that filters on an unpartitioned column scans everything.

Databricks: the lakehouse position

Databricks starts from Spark and adds a SQL warehouse over Delta tables, so the same data serves notebooks, ML training and BI without a copy. The pitch is one copy of data with an open table format rather than a proprietary store.

Unity Catalog is the governance layer, and its structure is what to know: a three-level namespace over a metastore that spans workspaces.

GRANT SELECT ON TABLE
  prod.sales.invoices TO `analysts`;

catalog.schema.table - the extra level above schema is what lets one metastore hold prod and dev, or several business domains, with permissions, lineage and audit applying across all of them. Before Unity Catalog, access control was per workspace, which is why environments proliferated.

It also handles external locations on cloud storage, so a table’s files stay in your bucket while the catalogue governs access to them.

Synapse and Fabric: the Microsoft story

Azure Synapse Analytics bundles two different things under one name, and the distinction is the interview question:

• Dedicated SQL pool - a provisioned MPP warehouse, sized in DWUs, billed while it exists. The descendant of Azure SQL Data Warehouse.
• Serverless SQL pool - query files in the data lake, billed per terabyte processed, with no cluster to provision.

Since 2023 Microsoft has positioned Fabric as the successor umbrella, with OneLake as a single storage layer and Delta as the table format underneath. As of 2026-08 both names are in the field, so the safe answer is to say which one you used and note that Fabric is where Microsoft is pointing new work.

Choosing, and what interviewers listen for

The stronger answer is that the engine is rarely the constraint - the modelling, the cost model and the governance are. All four run good SQL over columnar storage. What differs is whether you pay for uptime (Snowflake), bytes (BigQuery on-demand) or provisioned capacity, and each of those rewards a different habit.

The cost trap is worth naming without prompting: on Snowflake it is an idle warehouse, on BigQuery it is an unpartitioned scan, and on a dedicated SQL pool it is a cluster nobody paused.

Related

• Data catalogues, governance, and lineage - catalogues and lineage across platforms
• Delta Lake — ACID Tables on Object Storage - Delta and Iceberg table formats
• Lambda and Kappa architectures - why the lakehouse merges batch and streaming