Stop 06 · Database ServicesTopics · 8/10

Data Warehousing (Redshift, BigQuery)

In short

'What was revenue by city each month this year?' is a terrible question to ask your live app database. Data warehouses are built for exactly these big questions, across years of data.

Databases optimized for large-scale analytical queries across huge datasets, rather than the fast, small transactional queries relational databases are built for — the destination for business intelligence and reporting workloads.

The blob character works a big calculator surrounded by tall stacks of ledgers.

Think of it like this

Bill book vs the CA's ledger

The kirana uncle's bill book records each sale as it happens, fast and one at a time. At year end, his CA pulls all of it together to answer 'Which month made most profit?' The bill book is your app's database. The CA's ledger is the warehouse.

Watch it happen

Two kinds of database work

OLTP (app database)

Many small reads and writes
One order, one user at a time
Row storage, millisecond answers

OLAP (warehouse)

Few huge read queries
Scans millions of rows at once
Column storage, built for totals

Keep the app on a transactional database. Copy data into a warehouse for reports, dashboards and analysis, so heavy queries never slow down checkout.

The big ideas

What you need to know

Columnar storage

Warehouses store each column together, so summing one column across a billion rows reads only that column.

Fed by pipelines

ETL or ELT jobs copy data from app databases, logs and other sources into the warehouse on a schedule or stream.

Managed options

Amazon Redshift, Google BigQuery, Azure Synapse and Snowflake all run SQL over very large datasets.

Watch the bill

BigQuery on-demand pricing charges by data scanned. Selecting only the columns you need saves real money.

See it in code

Walk through the code

sql

1-- BigQuery: revenue by city per month this year2SELECT3  city,4  DATE_TRUNC(order_date, MONTH) AS month,5  SUM(total) AS revenue,6  COUNT(*) AS orders7FROM `shop.orders`8WHERE order_date >= '2026-01-01'9GROUP BY city, month10ORDER BY revenue DESC11LIMIT 10;

1/3Pick just the columns needed and roll dates up to months.

A typical analytics query. Naming only four columns, instead of SELECT *, means far less data is scanned.

Save yourself the pain

Common mistakes

Oops

Running heavy monthly reports directly on the production database.

Do this instead

Big scans compete with real users for CPU and locks. Run them on a warehouse or at least a replica.

Oops

Using SELECT * in BigQuery to explore a big table.

Do this instead

It scans every column. Use the table preview, or name only the columns you need.

Your mission

Query a public dataset

~25 min

Quick check

Why is a columnar warehouse fast at 'total sales per month over five years'?

Go deeper

1Run a sample analytical query against a public dataset in a managed data warehouse.
2Learn the conceptual difference between OLTP (transactional) and OLAP (analytical) database design.
3Understand why a data warehouse is usually fed by an ETL/ELT pipeline, not written to directly by an app.