Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 36 ยท Modeling and Warehousing

Normalization, Denormalization and Data Vault

Compare 3NF, star schemas, one big table and Data Vault (hubs, links, satellites), and learn how warehouse layers (raw, staging, marts) combine them.

There is no single "correct" shape for data. An application database wants normalized tables, where each fact is stored once. An analytics team wants denormalized tables where the joins are already done. A large enterprise warehouse may want something more auditable still, such as Data Vault. Good data engineers know the trade-offs and move data between these shapes on purpose.

Normalization

Normalization removes redundancy so updates happen in one place. In third normal form (3NF) every non-key column depends on "the key, the whole key, and nothing but the key".

An orders system might look like this:

TEXT
customers(customer_id, name, city_id)
cities(city_id, city, country)
orders(order_id, customer_id, order_date)
order_lines(order_id, line_no, product_id, quantity)
products(product_id, name, category_id)

Changing a city name is one UPDATE. But answering "revenue by country and category" needs five joins.

Denormalization

For analytics we flatten dimensions into the fact, accepting duplicated text in exchange for fast scans and simple queries. A star schema is moderately denormalized; one big table (OBT) or "wide table" goes all the way, pre-joining everything.

Python From normalized tables to one wide table

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.

Wide tables are great for BI tools and ad hoc analysis in columnar engines. Their risks are duplicated attributes that change (you need a rebuild rather than an update), tables with hundreds of columns, and unclear grain when several fact tables are mashed together.

Shape Strength Weakness
Normalized (3NF) Consistent, small writes Many joins, slow analytics
Star schema Simple, fast, reusable dimensions Needs modeling effort
One big table Zero joins, great for dashboards Hard to maintain, duplicated data
Data Vault Auditable, parallel loading, easy to extend Not query-friendly, needs marts on top

Data Vault in one page

Data Vault, designed by Dan Linstedt, separates the stable skeleton of the business from its changing descriptions. It has three table types:

  • Hub: one row per unique business key (customer number, product code), with a hash key, load date and record source. Nothing else.
  • Link: a relationship between hubs (customer placed order), also keyed by a hash of the involved business keys.
  • Satellite: descriptive attributes of a hub or link with a load date. A new row is added every time attributes change, giving full history automatically.
flowchart LR
  HC["Hub_Customer"] --- L["Link_Order"]
  HP["Hub_Product"] --- L
  SC["Sat_Customer_Details"] --> HC
  SO["Sat_Order_Details"] --> L

Why teams adopt it: sources can be added without redesigning the model (a new satellite or hub), loads from many sources run in parallel because hash keys need no lookups, and every row records where and when it came from, which suits audits and regulated industries.

Python Build hubs, links and satellites with hash keys

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.

Hash keys are deterministic: two sources that hold the same customer number produce the same key without asking a central table. Always normalize the input (trim, case, a fixed delimiter) before hashing, or the same customer gets two keys.

Putting layers together

Most platforms combine these ideas in layers, sometimes called the medallion or "raw/staging/marts" architecture:

  1. Raw / bronze: copies of source data, append-only, no business rules.
  2. Cleaned / silver: typed, deduplicated, conformed. This is where a Data Vault or normalized "core" model may live.
  3. Marts / gold: star schemas or wide tables per business area, built for queries.

The important habit is to model for the consumer at the end and keep the earlier layers simple and reproducible.

Python Stand-in: pick a shape for each workload

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.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main purpose of normalization?

    Normalized models keep one copy of each fact, which suits transactional writes.
  2. 2. In Data Vault, where do descriptive attributes live?

    Hubs hold business keys, links hold relationships, satellites hold changing attributes with history.
  3. 3. Why normalize text (trim, uppercase) before computing a hash key?

    Different spelling of the same key would otherwise produce different hash keys and duplicates.
  4. 4. A downside of one big table is

    Pre-joining duplicates descriptive values and can blur the grain when many facts are combined.

Summary

  • Normalized models suit writes, star schemas and wide tables suit analytics.
  • Data Vault uses hubs, links and satellites for auditability and flexible integration, and still needs marts for querying.
  • Combine shapes by layer: raw, cleaned core, and consumer-facing marts.

Next: Warehouse SQL Patterns