Snowflake vs BigQuery for the DE interview

Why interviewers love this question

Walk into any Data Engineering loop at Snowflake, Stripe, Airbnb, or DoorDash and you will hear "walk me through Snowflake's architecture and tell me when you'd pick BigQuery instead". The question tests three things at once: cloud DWH internals, pricing literacy, and the judgment to map a workload onto a platform without sounding like a marketing brochure.

The trap is that both products look interchangeable from a SQL prompt. They are not. Snowflake separates storage and compute with explicit virtual warehouses, while BigQuery hides compute behind serverless slots and bills per byte or per slot-hour. If you cannot explain the difference in 60 seconds with a cost example, the interviewer will assume you have only used one.

This post gives you the answer a senior DE would give: the layered architecture for each, a comparison table you can sketch on a whiteboard, the pricing model with real numbers, and the worked SQL that shows where dialect differences actually bite.

Snowflake architecture in one diagram

Snowflake has three logical layers, and naming them in order is half the answer.

The storage layer sits on top of object storage — S3, Azure Blob, or GCS — depending on the region you deploy into. Files are written in Snowflake's proprietary columnar format (FDN micro-partitions, typically 50-500 MB compressed), with metadata that tracks min/max per column for pruning. You do not see these files directly; you query tables.

The compute layer is where Snowflake makes its money. Each query runs on a virtual warehouse — an isolated MPP cluster sized from X-Small (1 node) up to 6X-Large (512 nodes). Warehouses do not share state, so you can spin up a dedicated WH for ELT, another for BI, and a third for ad-hoc analysts without any of them blocking each other. Billing is per-second with a 60-second minimum after the first start, and auto-suspend parks idle warehouses after a configurable idle window.

The cloud services layer is the brain — query parsing, optimization, metadata, security, and the Time Travel ledger live here. It is operated by Snowflake and you do not pay for it directly unless it exceeds 10% of your compute spend.

Two features show up in interviews almost every loop:

• Time Travel lets you query a table AT (TIMESTAMP => '...') or BEFORE (STATEMENT => '...') within a retention window of 1-90 days. Useful for "oh no I deleted the fact table" and for slowly-changing dimension reconstruction.
• Zero-copy clones create a new table or schema pointer that shares the underlying micro-partitions until either side mutates. Cloning a 50 TB warehouse for a staging environment takes seconds and costs nothing extra until writes diverge.

Snowflake's superpower is multi-cloud portability — the same account model runs on AWS, Azure, and GCP.

BigQuery architecture in one diagram

BigQuery is serverless by default. There is no cluster to size, no node count to argue about in a planning meeting, no warehouse to auto-suspend. You write SQL, Google's infrastructure runs it.

Under the hood there are still layers, just hidden ones. Storage uses Capacitor, Google's columnar format, on Colossus (Google's distributed file system, the successor to GFS). Compute runs on Dremel, the same execution engine that powers internal Google analytics, with slots as the unit of work. One slot is roughly one vCPU executing one shard of one stage of one query.

You pick one of two pricing modes:

• On-demand bills per byte scanned — currently $6.25 per TB in most US regions. Cheap for occasional ad-hoc, expensive when an analyst forgets a partition filter on a 10 TB table.
• Capacity (slots reservation) bills per slot-hour with monthly or annual commitments. A 100-slot reservation at $0.04/slot-hour is roughly $2,920/month and gives you predictable cost regardless of bytes scanned.

BigQuery's interview-worthy extras include BigQuery ML (train regression, classification, and ARIMA models with CREATE MODEL SQL), native streaming insert via the Storage Write API (real-time append at scale, billed at roughly $0.025 per 200 MB), federated queries that read directly from Cloud Storage, Bigtable, or Cloud SQL without ingest, and BigQuery Omni for cross-cloud reads against S3 and Azure Blob.

Side-by-side comparison

This is the table to draw on the whiteboard. Memorize the rows; the cells are where seniors differentiate from mids.

Two adjacent products always come up. Redshift is AWS-only and historically coupled storage and compute — RA3 fixed this but the legacy reputation sticks. Databricks SQL competes on the lakehouse angle with Delta Lake and Photon. ClickHouse is the open-source columnar workhorse for sub-100ms dashboard QPS, where Snowflake and BigQuery are an order of magnitude too slow.

When to pick which

The interviewer wants a decision tree, not a feature dump.

Pick Snowflake when your org is multi-cloud or wants to avoid GCP lock-in, when you need strict workload isolation (separate WHs for ELT, BI, and data science), when Data Sharing with external partners is a real business requirement, or when your data team prefers an explicit cost knob (warehouse size) over a serverless mystery box. Typical buyers: Stripe, Capital One, large retailers running on AWS who want a DWH that is not Redshift.

Pick BigQuery when you are already on GCP, when ad-hoc query volume is unpredictable and capacity planning would be a nightmare, when streaming inserts at scale are core to the product (clickstream, IoT, ad tech), or when BigQuery ML or GIS map to your team's skill set. Typical buyers: Spotify, Twitter (when it was on GCP), ad-tech, any team where SREs do not want to manage warehouses.

Pick ClickHouse when you need sub-100ms p95 latency on customer-facing dashboards with high QPS, when you want self-hosted control, or when your bill on Snowflake/BigQuery is dominated by one always-on dashboarding workload. Uber's M3 and Cloudflare's analytics layer are the canonical examples.

Pick Databricks SQL when your data lake is already Delta Lake on S3/ADLS, when you want to keep one platform for both Spark ETL and BI SQL, or when ML lifecycle (MLflow, Unity Catalog) is the deciding factor.

Pick Redshift when you are deeply AWS-native, your workload is predictable, and you want RA3 + Redshift Spectrum without onboarding a second vendor. The price-performance gap has closed against Snowflake on equivalent workloads but the operational complexity is higher.

Worked SQL: same query, two dialects

Most candidates can write a GROUP BY. Seniors know where dialects diverge. Here is the same cohort retention query in both flavors — the differences are small but they are exactly the things an interviewer points to when they ask "would this run on BigQuery?".

-- Snowflake
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', signup_at) AS cohort_month
  FROM analytics.users
  WHERE signup_at >= DATEADD(month, -12, CURRENT_DATE())
),
activity AS (
  SELECT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM analytics.events
  WHERE event_at >= DATEADD(month, -12, CURRENT_DATE())
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY user_id, DATE_TRUNC('month', event_at)
    ORDER BY event_at
  ) = 1
)
SELECT
  c.cohort_month,
  DATEDIFF(month, c.cohort_month, a.active_month) AS month_offset,
  COUNT(DISTINCT a.user_id) AS retained_users
FROM cohorts c
JOIN activity a USING (user_id)
GROUP BY 1, 2
ORDER BY 1, 2;

-- BigQuery (standard SQL)
WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC(signup_at, MONTH) AS cohort_month
  FROM `project.analytics.users`
  WHERE signup_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
),
activity AS (
  SELECT
    user_id,
    DATE_TRUNC(event_at, MONTH) AS active_month
  FROM `project.analytics.events`
  WHERE event_at >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY user_id, DATE_TRUNC(event_at, MONTH)
    ORDER BY event_at
  ) = 1
)
SELECT
  c.cohort_month,
  DATE_DIFF(a.active_month, c.cohort_month, MONTH) AS month_offset,
  COUNT(DISTINCT a.user_id) AS retained_users
FROM cohorts c
JOIN activity a USING (user_id)
GROUP BY 1, 2
ORDER BY 1, 2;

Three things to flag to the interviewer: DATE_TRUNC argument order is flipped (unit-first in Snowflake, value-first in BigQuery), DATEDIFF vs DATE_DIFF with different argument order, and table references use backtick-qualified project.dataset.table in BigQuery. The QUALIFY clause works in both — that's the one nice thing they agreed on.

Common pitfalls

The most common interview miss is conflating Snowflake with Redshift. They look similar on a slide but Snowflake's separation of storage and compute is architecturally cleaner — multiple warehouses read the same storage without contention, whereas legacy Redshift DC/DS nodes coupled them. Modern RA3 closed the gap but candidates who say "they're basically the same" lose credibility immediately. The fix: practice the three-layer story for Snowflake until you can draw it without thinking.

A second trap is ignoring pricing-model trade-offs. BigQuery on-demand looks cheap until an analyst writes SELECT * against a 50 TB unpartitioned table and burns $312 in a single query at $6.25/TB. Snowflake hides similar mistakes behind a runaway 4X-Large warehouse that no one suspended. The senior answer is to mention slot reservations for predictable BigQuery workloads and resource monitors plus auto-suspend for Snowflake — both prevent the bill spike that ends careers.

The third pitfall is leaving Snowflake warehouses running 24/7 because someone wired a BI tool with no idle timeout. A Medium warehouse at $4/hour × 24 × 30 is roughly $2,880/month for zero overnight queries. Auto-suspend at 60 seconds and auto-resume on query arrival is the default for a reason — turn it on and configure resource monitors to alert on credit thresholds.

Another classic is using ClickHouse where Snowflake fits, or vice versa. ClickHouse is excellent for wide flat tables with high QPS read patterns and weak join requirements; the moment you have a star schema with 10+ dimension joins and need ACID semantics, you are fighting the tool. Snowflake handles the joins gracefully but charges accordingly and cannot match ClickHouse on tail latency for live dashboards.

Finally, streaming inserts in BigQuery without cost awareness. The Storage Write API is dramatically cheaper than the legacy insertAll, but candidates still quote the old numbers. At roughly $0.025 per 200 MB via Storage Write, real-time pipelines are affordable; at the legacy $0.05/GB streamed, large-volume telemetry will bankrupt you.

Related reading

• BigQuery for Data Engineering interviews
• ClickHouse as DWH
• ClickHouse MergeTree deep dive
• Databricks for Data Engineering interviews
• Inmon vs Kimball for DE
• DWH cost optimization
• Redshift for DE interviews

If you want to drill DE platform-knowledge questions like this one every day, NAILDD ships with 500+ interview problems across Snowflake, BigQuery, Spark, and ClickHouse — the exact pattern hiring managers reach for.

FAQ

Is Snowflake or BigQuery cheaper?

Neither, in the abstract. Snowflake wins on steady, predictable workloads where you can right-size a warehouse and lean on auto-suspend. BigQuery wins on spiky ad-hoc patterns where on-demand pricing means you only pay for what runs. The honest answer is "show me QPS, average scanned bytes per query, and off-hours utilization, and I can tell you within 10%."

Can BigQuery run on AWS or Azure?

Not natively. BigQuery Omni reads tables in S3 and Azure Blob via Anthos clusters, but the control plane runs on GCP. If multi-cloud is a hard requirement, Snowflake is the cleaner answer — the same account model runs across all three hyperscalers.

Why is Snowflake's storage cheaper than the compute?

Storage on object stores (S3/Blob/GCS) bills at roughly $23/TB-month compressed. Compute requires an active virtual warehouse, so a Medium WH at roughly $4/hour dominates total spend the moment you run queries for more than an hour a day. The architectural separation is also the cost-shape separation — storage can grow 10x without touching compute spend.

When would I pick Databricks SQL over both?

When your data lake is already Delta Lake on S3 or ADLS and your team writes Spark notebooks for ETL. Databricks SQL with Photon competes credibly on lakehouse workloads, and Unity Catalog gives you one governance layer across SQL, Python, and ML. The trade-off is operational complexity.

Does Snowflake support true streaming?

Snowpipe is micro-batch with sub-minute latency; Snowpipe Streaming (GA) reaches second-level latency. For sub-second streaming at millions of events per second, BigQuery's Storage Write API or a dedicated stream processor (Flink, Kafka Streams) is still the better fit.