Stop 13 · Version Control, Git & CollaborationTopics · 5/7

Intro to Cloud Data Warehouses (BigQuery, Snowflake, Redshift)

In short

Your laptop chokes on a few million rows. A cloud data warehouse can scan billions in seconds, and you still just write SQL. Most analyst jobs now involve one.

Modern cloud warehouses that store and query massive datasets far beyond what a single spreadsheet or local database could handle — the destination in most companies' ETL pipelines.

The jaadu holds a clipboard and looks up in awe between two tall shelves stacked with boxes.

Think of it like this

Renting space in a giant godown

A small trader can't build a huge warehouse. So he rents space in a big godown and pays for the shelves he uses and the workers he calls in. Cloud warehouses work like that: someone else runs the building, you pay for storage and work.

Watch it happen

From app data to your query result

Sources

Load

Storage

Compute

BI tool

1/5Data starts in app databases, payment systems, ad platforms and spreadsheets.

The big ideas

What you need to know

Columnar storage

Data is stored column by column. Selecting only the columns you need reads far less data.

Storage and compute are separate

You pay to store data and, separately, for the processing your queries use. Big queries cost more.

Three big names, same idea

BigQuery (Google), Snowflake (runs on several clouds) and Redshift (Amazon). The SQL dialects differ slightly.

Cost is your responsibility

Avoid SELECT * on huge tables. In BigQuery, LIMIT doesn't reduce the data scanned.

See it in code

Walk through the code

sql

1SELECT name, SUM(number) AS babies2FROM `bigquery-public-data.usa_names.usa_1910_current`3WHERE year >= 20004GROUP BY name5ORDER BY babies DESC6LIMIT 10;

1/3Only name and number are read, plus year for the filter. Other columns are skipped entirely.

A query on one of BigQuery's free public datasets. Picking only the columns you need keeps the scan small.

Save yourself the pain

Common mistakes

Oops

Running SELECT * on a billion-row table to "have a look".

Do this instead

Use the table preview tab, or select only a few columns. Check the estimated bytes before running.

Oops

Assuming SQL written for Postgres runs unchanged on BigQuery.

Do this instead

Check dialect differences, like date functions and quoting, in the warehouse's docs.

Your mission

Query a public dataset in the cloud

~30 min

Quick check

Why does selecting fewer columns make a cloud warehouse query cheaper and faster?

Go deeper

1Try a free tier of BigQuery or Snowflake with a public sample dataset.
2Learn the basic pricing model differences (compute vs storage) between warehouses.
3Understand that the SQL you already know transfers directly, with minor dialect differences.