Course Preview

Data Warehousing

A data warehouse is a central repository that stores structured, historical data optimized for analytical queries rather than transaction processing. It consolidates data from multiple source systems into a consistent schema so business analysts can run reports and measure trends reliably.

itData engineering and analytics | OpenSkills.info

Course pathWalk it in order

Look it upDip in anytime

Go furtherLeaves this page

Don't Panic — Data Warehousing

A data warehouse is where several operational systems send selected history so people can ask questions that no single application was designed to answer. It is not a grander order system with bigger shelving. It is a historical measurement system, which sounds stern because numbers become remarkably creative when left unattended.

An order system knows that an order happened. A support system knows that a case happened. A marketing platform knows that somebody responded to a campaign. Each is busy doing its own job, and asking one of them for three years of cross-company analysis is rather like asking a cashier to inventory the building during lunch. The warehouse gives those records a separate place to be cleaned, connected, and examined without disturbing the transaction work.

The useful mental furniture is grain, facts, and dimensions. Grain is the precise promise made by one fact row: perhaps one product line on one completed order. Facts are the measurements at that promise. Dimensions are the descriptive context that lets a report group those measurements by date, customer, product, or store. Get grain wrong and a query can multiply revenue with impeccable manners. Get it right and the model tells every measure where it is allowed to stand.

History is the part that looks polite until it is not. A customer can change segment and a product can change category. A slowly changing dimension decides whether an old sale keeps the old description or is viewed through the new one. Type 2 history keeps versioned rows so old facts can retain their original context. That is a design choice, not an automatic courtesy supplied by SQL.

The pipeline also needs to behave like a record keeper. Ingestion collects changes. Staging gives them somewhere to wait and restart. Transformation conforms identifiers and applies rules. Then dimensions usually load before facts, because a fact needs a resolved surrogate key. Reconciliation compares source totals with published totals. A job that finished successfully but lost rows has merely failed with a cheerful status message.

Start with the Course tab for the full architecture and the reason each boundary is a contract. Use Slides when you need the relationships in one view. The Cheatsheet is the working list for grain, history, incremental-load, and quality decisions. Field Notes concentrates on the expensive mistakes that tidy queries can hide. Then use Quiz to test the mental model, and Reference when the next question needs authoritative detail rather than fresh optimism.

Where this skill leads

Relevant careers

See how this topic contributes to broader role-level skill maps.

Sources