CSE Roadmap

Common tier · about 3 hours · 7 parts

Part 3 of 7 · 3 min read

The data warehouse and ETL

A separate database for analysts, and the pipeline that fills it.

Think of a restaurant kitchen during the dinner rush. The head chef does not want an inspector counting every pan on the line. A separate office keeps copies of the day's receipts, and the inspector works there.

The book explains why companies do this. The databases that run the business, the OLTP systems, must be highly available and quick. Their administrators guard them closely. An analyst's query may scan large parts of the data and slow down the transactions that run at the same time.

Data warehouse

A data warehouse is a separate database that holds a read-only copy of data from many operational systems, shaped for analysis.

Example: a retail company copies sales from its shops, orders from its website and stock from its warehouses into one place, where analysts can query freely.

The book describes the route into the warehouse. Data is extracted from the OLTP databases, either by a periodic dump or by a continuous stream of changes. It is transformed into a shape fit for analysis and cleaned. Then it is loaded into the warehouse.

ETL

ETL (extract, transform, load) is the process of getting data into a warehouse: take it from the source, reshape and clean it, then store it.

Example: every night, a job reads yesterday's orders, converts prices into one currency, drops test orders and loads the result.

[ Shop DB ]   \
[ Web DB  ]    >--- extract --> transform --> load --> [ Data warehouse ] --> analysts, reports
[ Stock DB]   /

A modern variation, not from the book, loads the raw data first and transforms it inside the warehouse. It is often called ELT. The order changes, the idea stays.

A worked example. A company has three source systems that produce 40 GB, 25 GB and 15 GB of new data a day.

  • Daily load: 40 + 25 + 15 = 80 GB.
  • If the load window is 2 hours (7,200 seconds), the pipeline must handle 80,000 MB ÷ 7,200 s ≈ 11 MB per second. That is easy.
  • A year of history: 80 GB × 365 = 29,200 GB, about 29 TB. This is why warehouses are described in terabytes and petabytes.

Other points from the book:

  • Warehouses are common in large firms and rare in small ones. A small company with one database and a small amount of data can query it directly, or even analyse it in a spreadsheet.
  • The data model of a warehouse is most often relational, since SQL suits analytic queries, and many tools generate SQL, draw charts and allow drill-down.
  • The inside of a warehouse is quite different from an OLTP database, though both speak SQL. Many vendors now focus on one or the other. Products named by the book include Teradata, Vertica, Amazon Redshift and SQL-on-Hadoop projects such as Hive and Spark SQL. Newer cloud warehouses follow the same ideas.

Common mistake

Running heavy reports on the live database "just this once". A scan of a hundred million rows can slow every customer request that runs at the same time. Use a copy for analysis.

Check yourself:

  • Name the three steps of ETL.
  • Why do administrators avoid analytic queries on the live database?