What Is a Data Warehouse?
PedalCity's rides live in the app database, payments with an outside provider, and weather in an API. To ask "do rainy days cost us revenue?" you need all three in one place, cleaned, joined and kept over years. That place is the data warehouse.
The rule
A warehouse is one cleaned, integrated, historical copy, built for questions.
Each word matters:
- Copy: the warehouse does not replace the app database. It receives copies through pipelines, so heavy analysis never touches production.
- Cleaned: cancelled rides are removed, times are in one time zone, and station names are spelled one way.
- Integrated: rides, payments and weather share keys and meanings, so they can be joined.
station_id17 means the same station everywhere. - Historical: data is added over time rather than overwritten, so last March is still there next March.
- Built for questions: data is modelled as facts and dimensions, stored in columns, and queried with SQL.
Most warehouses are organized in layers, each built from the one before it:
staging raw copies of each source, lightly typed stg_rides, stg_payments
core cleaned, integrated facts and dimensions fact_rides, dim_station
marts summaries shaped for one team or use finance_daily_revenueToday most teams rent a managed cloud warehouse rather than running their own servers. Google BigQuery, Snowflake and Amazon Redshift are common examples. The details differ, but all store data in columns, speak SQL and let you scale compute up and down. Many separate storage (where the data sits) from compute (the engines that run queries), so a heavy month-end report does not slow the morning dashboard.
The shortcut
To decide whether something belongs in the warehouse, check the four words: cleaned, integrated, historical, for questions. Raw app tables fail "cleaned". A single system's report fails "integrated". A live table that overwrites itself fails "historical".
Staging
Load raw rides, payments and hourly weather into
stg_tables exactly as they arrive.
Drills
Answer fast — trust the rule.
Why does PedalCity copy data into a warehouse rather than analysing the app database directly?
Rides and payments use different IDs for the same station, so they cannot be joined. Which property is missing?
In which layer would fact_rides and dim_station usually live?
What is the benefit of separating storage from compute in a cloud warehouse?
Remember
- A warehouse is one cleaned, integrated, historical copy built for questions.
- Layers: staging → core → marts.
- Managed cloud warehouses store columns, speak SQL and often separate storage from compute.