Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 76 ยท Getting Data

Data Warehouses, ETL and Metadata

Understand how analytics data is stored: warehouse components, ETL versus ELT, star schemas, OLAP queries, and the types of metadata and data lineage that keep numbers trustworthy.

Most companies keep their operational data in many systems: an online shop database, a billing system, a customer-support tool, spreadsheets. Each is built to run one process quickly, not to answer questions that cut across all of them. A data warehouse is a separate database designed for exactly those questions: it collects data from the source systems, cleans and reshapes it, keeps the history, and serves analysts with fast aggregate queries.

As a data scientist you will rarely build a warehouse, but you will query one every week. Knowing how it is organized explains why some tables look strange (a "fact" table of numbers surrounded by "dimension" tables of descriptions), why numbers sometimes differ between two dashboards, and why metadata, the data about the data, matters. This lesson follows Data Sources and prepares you for SQL for Data Science.

What makes a warehouse different

A warehouse is usually described with four properties:

  • Subject-oriented: organized around business subjects (sales, customers, inventory), not around the applications that produced the data.
  • Integrated: data from several sources is brought to one consistent form (the same country codes, currencies, units and naming).
  • Time-variant: it keeps history, so you can compare this quarter with last year. Operational systems often overwrite old values.
  • Non-volatile: loaded data is not updated or deleted in place; new data is appended.
Operational database (OLTP) Data warehouse (OLAP)
Purpose Run the business: place an order, update an address Analyse the business: sales by region over three years
Queries Many tiny reads and writes touching a few rows Fewer, large reads aggregating millions of rows
Design Normalized, to avoid duplication Denormalized, to read fast
Data Current state Historical, integrated

The components

flowchart LR
    S1[("Shop DB")] --> E["Extract"]
    S2[("Billing")] --> E
    S3["CSV files, APIs"] --> E
    E --> T["Transform<br/>clean, conform, join"]
    T --> L[("Warehouse<br/>fact and dimension tables")]
    L --> M["Data marts<br/>(sales, finance)"]
    L --> B["BI tools, notebooks,<br/>ML models"]
    MD[("Metadata<br/>catalog")] -.-> E
    MD -.-> T
    MD -.-> L

Data flows left to right; the metadata catalog describes every step.

  1. Sources: operational databases, files, APIs, logs.
  2. Staging area: a landing zone where raw extracts are stored unchanged, so you can rerun the transformation without hitting the source again.
  3. ETL or ELT process: the pipeline that moves the data (next section).
  4. The warehouse storage: the integrated, historical tables.
  5. Data marts: smaller subsets for one team or subject, such as a finance mart, to keep queries simple and permissions separate.
  6. Access tools: SQL clients, BI dashboards, notebooks and reporting tools for end users.
  7. Metadata repository: the catalog describing what everything is (see below).

ETL and ELT

ETL means extract, transform, load: pull data from the sources, transform it on a separate processing server, and load only the finished result into the warehouse. ELT swaps the last two steps: load the raw data first, then transform it inside the warehouse with SQL. Modern cloud warehouses are fast and cheap enough that ELT is common, and it keeps the raw data available if a transformation turns out to be wrong.

Typical transformations: remove duplicates, fix types, convert currencies and time zones, map codes to names, join tables, and compute derived columns. A good pipeline is idempotent (running it twice gives the same result), logs how many rows it read and wrote, and fails loudly on bad data (see Testing Data Code).

The star schema

The classic warehouse layout is the star schema. One central fact table records measurable events (one row per order line, with quantity and amount), and several dimension tables describe the context (customers, products, dates). Facts hold numbers and keys; dimensions hold descriptive text that you group and filter by. The picture of a fact table with dimensions around it looks like a star.

The next example builds a tiny star schema in SQLite, then asks typical warehouse questions: totals by month and category, and a drill-down from year to month.

Python A star schema and OLAP-style queries

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The same operations have names in OLAP (online analytical processing): roll-up moves to a coarser level (month to year), drill-down to a finer one, slice fixes one dimension (only Computers), dice filters on several, and pivot rotates the view, as in the cube table above. Because dimensions are small and facts are indexed by keys, these joins stay fast even with billions of fact rows.

Slowly changing dimensions

Dimensions change: a customer moves city, a product changes category. If you overwrite the old value, last year's report changes retroactively. A common remedy is a type 2 slowly changing dimension: keep the old row, add a new row for the new value, and store the dates for which each row is valid.

Python A type 2 dimension

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The March order is credited to Berlin and the August order to Munich, as it was true at the time. Overwriting the city would have moved all of C1's revenue to Munich.

Metadata: data about the data

Metadata describes the data: what a column means, where it came from, who owns it, and how fresh it is. Without it a warehouse turns into a swamp of tables nobody trusts. Three kinds are usually distinguished.

  • Technical metadata: table and column names, data types, keys, sizes, partitions, indexes, and the ETL jobs with their schedules. This is what information_schema and PRAGMA table_info show.
  • Business metadata: plain-language definitions ("active customer: ordered within 90 days"), the data owner, approved calculations for each metric, and any quality rating. A shared business glossary prevents three teams from computing "revenue" three ways.
  • Operational metadata: facts about runs: when a table was last loaded, how many rows arrived, how long the job took, who queried it, and errors.

You already know metadata from everyday life: the date and camera model stored inside a photo, or the artist, album and year stored in a music file, are metadata describing the content rather than the content itself.

Data lineage

Data lineage records the path of a value from its sources through every transformation to the report that shows it. It answers "where does this number come from?" and "what will break if we change this column?". Lineage supports auditing and compliance, speeds up debugging (find the step where revenue doubled), and lets you estimate the effect of a schema change before making it.

The following example reads technical metadata from the SQLite catalog and writes a small, manually maintained lineage and glossary table, a simplified version of what a data catalog tool automates.

Python Reading technical metadata and recording lineage

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real catalogs (open-source and commercial) collect this automatically from query logs and job definitions, but the ideas are the same: describe the data, track where it came from, and name who is responsible.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which description fits a star schema?

    Facts hold numeric measures and foreign keys; dimensions hold the descriptive attributes you group and filter by.
  2. 2. What is the main difference between ETL and ELT?

    In ELT the transformation happens after loading, using the warehouse's own power; ETL transforms before loading.
  3. 3. Why use a type 2 slowly changing dimension?

    Type 2 adds a new row with validity dates instead of overwriting, so past reports do not change.
  4. 4. A column description such as 'active customer: ordered in the last 90 days' is which kind of metadata?

    Definitions, owners and approved calculations are business metadata; data types and keys are technical; load times and row counts are operational.
  5. 5. What question does data lineage answer?

    Lineage traces values from sources through transformations to reports, which supports debugging, audits and impact analysis.

Summary

  • A data warehouse is a subject-oriented, integrated, time-variant, non-volatile store built for analysis, separate from operational systems.
  • Components: sources, staging, ETL/ELT, warehouse storage, data marts, access tools and a metadata repository.
  • Star schemas separate numeric facts from descriptive dimensions; OLAP operations are roll-up, drill-down, slice, dice and pivot.
  • Type 2 slowly changing dimensions keep history so past reports stay stable.
  • Metadata (technical, business, operational) and lineage make data findable, understandable and trustworthy.

Next: Loading Data.