Cloud data platforms: BigQuery, Snowflake and Databricks

Most data engineering jobs today run on a cloud data platform, not on a server you manage yourself. Three names come up again and again: Google BigQuery, Snowflake and Databricks. They solve a similar problem in different ways, and job descriptions often list one or two of them.

This lesson explains what they have in common, how each one works, how you pay for them, and how to choose. You do not need an account to follow it, but the final exercise suggests free ways to try them. It builds on *OLTP vs OLAP: databases, warehouses, lakes and lakehouses* and *File and table formats*.

Older warehouses ran on a fixed set of servers, where storage and computing power were tied together. If you needed more space you also bought more compute. Cloud platforms separate the two:

- Storage is cheap object storage that holds your data for as long as you want. - Compute is a pool of machines that you start when you need to run queries and stop when you do not.

Think of a library. The books (storage) stay on the shelves all the time. The reading rooms (compute) can be opened for an exam rush and closed afterwards, and ten groups can read the same books in ten rooms without disturbing one another. Separation also means you pay for compute only while it works.

BigQuery is Google Cloud's serverless data warehouse. Serverless means you do not create or size a cluster. You load data, run SQL, and Google allocates the computing power behind the scenes.

Key ideas:

- You organise data in projects, datasets and tables. - You can pay on demand, based on how much data your queries read, or buy reserved capacity (slots) for steady workloads. Check Google's pricing page for current rates. - Because on-demand pricing follows the data scanned, good table design matters: partitioning (split a table by, for example, date) and clustering (sort data within partitions by columns you filter on) let a query read less. - It has built-in support for loading from Google Cloud Storage, streaming inserts, scheduled queries and machine learning models using SQL. - There is a free sandbox and a free monthly allowance at the time of writing (2026); exact limits change, so read the official page.

CREATE TABLE shop.orders (
    order_id    STRING,
    customer_id STRING,
    status      STRING,
    total       INT64,
    created_at  TIMESTAMP
)
PARTITION BY DATE(created_at)
CLUSTER BY status;

-- Reads only one day's partition instead of the whole table
SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
FROM shop.orders
WHERE DATE(created_at) = '2026-10-01'
GROUP BY status;

The WHERE clause on the partition column lets BigQuery skip every other day. In the console, the query validator shows how many bytes a query will scan before you run it.

Snowflake is a cloud data platform that runs on AWS, Microsoft Azure or Google Cloud. It is the platform most closely associated with the idea of a separate "compute" layer you control.

Key ideas:

- Data lives in Snowflake-managed storage. You create databases and schemas with tables inside. - Queries run on virtual warehouses, which are clusters of compute with T-shirt sizes (XS, S, M, and so on). Larger sizes run a query faster but cost more per minute. - You pay in credits for compute while a warehouse runs, plus storage. Warehouses can auto-suspend when idle and auto-resume when a query arrives. - Different teams can have their own warehouses on the same data, so a heavy data science job does not slow the dashboards. - Handy features include semi-structured data (JSON in a VARIANT column), Time Travel (query a table as it was earlier, within a retention period), zero-copy cloning and secure data sharing between accounts. - A free trial is available; check the official page for the current offer.

-- A small warehouse that stops itself when idle
CREATE WAREHOUSE IF NOT EXISTS bi_wh
    WAREHOUSE_SIZE = 'XSMALL'
    AUTO_SUSPEND = 60
    AUTO_RESUME = TRUE
    INITIALLY_SUSPENDED = TRUE;

USE WAREHOUSE bi_wh;

-- Query JSON stored in a VARIANT column
SELECT payload:id::STRING AS order_id,
       payload:customer_name::STRING AS customer
FROM raw_orders
LIMIT 10;

AUTO_SUSPEND = 60 stops the warehouse after 60 seconds of inactivity, so you stop paying for compute. A warehouse left running by mistake is one of the most common surprise bills.

Databricks started from the creators of Apache Spark and is the best-known lakehouse platform. A lakehouse keeps data in open file formats on cheap object storage (the lake) but adds table features such as transactions and SQL performance (the warehouse). Its core table format is Delta Lake, which you met in *File and table formats*.

Key ideas:

- You work in a workspace with notebooks, jobs and SQL warehouses. Spark runs behind the notebooks and jobs, so Python and PySpark are first-class. SQL is fully supported too. - Unity Catalog manages access, lineage and discovery across data and AI assets. - Compute comes as clusters for notebooks and jobs, or SQL warehouses for BI queries, and you pay for compute time (in DBUs) plus the cloud provider's infrastructure cost. - It is strong for engineering, streaming and machine learning in the same place as analytics. - A free edition for learning exists at the time of writing; check the official page for its current limits.

from pyspark.sql import functions as F

# In a Databricks notebook, `spark` already exists.
orders = spark.read.table("main.shop.orders")

daily = (
    orders
    .where(F.col("status") == "picked_up")
    .groupBy(F.to_date("created_at").alias("day"))
    .agg(F.sum("total").alias("revenue"))
)

daily.write.mode("overwrite").saveAsTable("main.shop.daily_revenue")

main.shop.orders is a three-level name: catalog.schema.table, as used by Unity Catalog. The result is saved as a Delta table by default.

The lines between them keep blurring. Snowflake and BigQuery both support open table formats such as Iceberg, and both have features for Python and machine learning. Databricks has strong SQL warehouses. Treat the table as a starting point, and check current documentation before a real decision.

Others to know: Amazon Redshift (AWS's warehouse), Azure Synapse Analytics and Microsoft Fabric (Microsoft), and ClickHouse or Apache Druid for very fast real-time analytics. If you learn one platform well, moving to another takes weeks, not years, because the concepts are the same.

Cloud data platforms make it easy to spend money quickly. Whichever you use, the habits are the same:

In real life the choice is rarely made from scratch. It depends on:

- Where your company already is. A company on Google Cloud tends to choose BigQuery, and an Azure company tends to look at Fabric or Databricks on Azure. - Who will use it. Mostly SQL analysts favour BigQuery or Snowflake. Engineers and data scientists who live in Python and notebooks often favour Databricks. - Workload. Heavy streaming, ML and unstructured data lean towards the lakehouse. Pure BI leans towards a warehouse. - Openness. Open table formats (Delta, Iceberg) reduce lock-in because your data stays in files you can read with other engines. - Cost model and skills. Pick the one whose bill you can predict and whose tools your team can learn.

You are a data platform architect. Compare BigQuery, Snowflake and Databricks for this situation, and recommend one with reasons and risks.

Company: [type, size, country]
Cloud already used: [AWS / Azure / Google Cloud / none]
Data: [sources, rough size per day, structured or not]
Team: [number of people, SQL vs Python skills]
Workloads: [BI dashboards / ML / streaming / data sharing]
Budget sensitivity: [low / medium / high]

Format: a table with the criteria, then a short recommendation. State which facts you are unsure about and which I should verify on the vendors' official pricing pages.

What to expect: A good answer ties the recommendation to your cloud, team skills and workloads, and flags prices and limits as things to verify. Distrust exact prices from a chatbot.

Interviewers rarely ask you to recite features. They ask how you think. Typical questions: "Why separate storage and compute?", "A query on a 2 TB table is slow and expensive. What do you do?" (partition, cluster, select fewer columns, pre-aggregate), "How do you stop a warehouse costing too much?" (auto-suspend, right-sizing, budgets) and "Warehouse or lakehouse?" (depends on workloads, skills and openness). If a job asks for Snowflake and you only know BigQuery, say so and show you understand the shared concepts.