Part 2 of 6 · 2 min read
Why a separate warehouse
Think of a company's data like several filing cabinets kept by different departments. Orders sit in one cabinet, support tickets in another, marketing lists in a third. Each cabinet was organized by the department that uses it daily, not for someone who wants to compare all three at once.
In plain words: A large organization usually has several operational systems, each with its own schema, because each was built for a different job. An analyst who wants to compare sales against support tickets cannot easily query across them directly, and running big analytical queries against a live operational system risks slowing down the real-time work that system is supposed to do. A data warehouse solves both problems by gathering data from the separate sources into one place, under one unified schema, built specifically for analysis.
A worked example. The bookstore runs an orders database for checkout and a separate support database for customer tickets. A manager wants to know whether customers who had a shipping complaint buy less in the following three months. That question needs both systems at once. Querying each live system repeatedly for this analysis would compete with real checkouts for the same disk and CPU. A warehouse that already holds both orders and tickets, refreshed on a schedule, answers the question without that risk.
The key lines. No new SQL; the orders and tickets tables below are what gets copied into the warehouse, each under its own warehouse table.
-- Operational system 1: orders
SELECT order_id, customer_id, order_date, total_cents FROM orders;
-- Operational system 2: support tickets
SELECT ticket_id, customer_id, opened_at, category FROM tickets;
Data warehouse
A data warehouse is a database that holds data copied from multiple operational source systems, converted into one unified schema, kept at a single site for querying and historical analysis. It is built and tuned for large read-heavy analytical queries, not for the small fast transactions of the systems it copies from.
Example: the bookstore's warehouse would hold copies of both the orders table and the tickets table, joined on customer_id, something that is awkward to do live across two separate production systems.