Stop 04 · SQL & DatabasesTopics · 6/6

Data Warehouses (BigQuery, Snowflake)

In short

The app's database is busy serving users. Analysis happens somewhere else: a data warehouse built to scan huge tables fast. Most data science jobs expect you to query one.

Modern cloud warehouses built to query massive datasets far beyond what a local database could handle — the common destination for the large-scale data a data scientist often pulls from.

The mascot with a clipboard stands in a huge godown with tall, neatly stacked shelves of boxes.

Think of it like this

The supermarket chain's central godown

Every branch sells all day at its own counter. At night, each sends its stock and sales numbers to one huge central godown, organised so managers can find any total quickly. The branch counters are app databases. The godown is the warehouse.

Watch it happen

From app database to dashboard

App DB

Pipeline

Warehouse

Analyst

nightly export

1/5The app's database keeps serving users. A pipeline copies new rows out each night.

The big ideas

What you need to know

Columnar storage

Data is stored by column, so summing one column of a wide table reads far less data.

Separate storage and compute

BigQuery and Snowflake scale processing power up or down independently of how much data you keep.

Queries can cost money

Pricing depends on data scanned or compute time used. Select fewer columns and filter on partitions.

Raw, clean, reporting layers

Teams keep raw copies, cleaned models and small reporting tables so analysis stays consistent.

See it in code

Walk through the code

sql

1-- BigQuery: monthly revenue by city, reading only 3 columns2SELECT3  DATE_TRUNC(order_date, MONTH) AS month,4  city,5  SUM(amount) AS revenue6FROM `shop.analytics.orders`7WHERE order_date >= '2026-01-01'8GROUP BY month, city9ORDER BY month, revenue DESC;

1/4Truncate dates to the month and sum revenue. Only three columns are read.

Standard SQL with a few dialect differences. In BigQuery, table names use project.dataset.table inside backticks.

Save yourself the pain

Common mistakes

Oops

Running SELECT * on a huge warehouse table to 'take a look'.

Do this instead

Use the table preview, LIMIT on a filtered partition, or select a few columns. Full scans can be costly.

Oops

Running heavy analysis directly on the live app database.

Do this instead

Query the warehouse. Heavy queries on the production database can slow the app for real users.

Your mission

Query a public dataset

~30 min

Quick check

Why does selecting 3 columns instead of SELECT * often make a 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.