Building a Data Vault: Hands-On with DuckDB

Most data warehouses are built for the business as it was on the day they were designed. Then a company gets acquired, a second system arrives with different IDs, or a one-to-many relationship quietly becomes many-to-many, and the warehouse needs rework. Data Vault is a modeling method built for exactly that situation: it integrates many sources, keeps every change, and absorbs new requirements without rebuilding what already works.

In this course you build a Data Vault yourself, in plain SQL, on your own laptop. You'll learn the patterns behind hubs, links and satellites, when each variant is the right tool, and how to turn the vault into star schemas that analysts can use. The course is current for Data Vault 2.1 and DuckDB 1.5.

What you'll build

A data warehouse for Northpaw Veterinary Group, a fictional group of clinics that grew through acquisitions. Its data comes from its main practice system (ClinicOS), a legacy system in the acquired clinics (VetBase), an online booking app, and a pet-insurance partner. You'll integrate them into one vault that remembers every change: pets that exist in two systems, animals rehomed to new owners, vets moving between clinics, corrected invoices and deleted records. On top of it, you'll deliver revenue and patient reports.

What you'll learn

  • Decide whether Data Vault fits a data-integration problem, and explain what it costs compared with 3NF and dimensional models.
  • Identify business keys in real source systems and model hubs, links and satellites from them.
  • Load a raw vault with insert-only, idempotent SQL patterns based on hash keys and hashdiffs.
  • Capture history, ended relationships and deletions correctly, with and without change data capture.
  • Model transactions, duplicate identities, hierarchies and reference data with the right link and satellite variants.
  • Deliver current and historical star schemas from the vault using views, PIT tables, bridges and ghost records.
  • Evolve a vault as sources and requirements change, without rework, and test it automatically.
  • Place Data Vault 2.1 in a 2026 platform: lakehouse storage, automation with dbt, CDC and semi-structured data.

Before you start

Download the course code (.zip) — unzip it and work inside the code/ folder.

You should be comfortable writing SQL joins, GROUP BY and basic window functions, and have some experience with data warehouses or analytics tables. No prior Data Vault knowledge is needed.

Set up the code repo:

  1. Install the DuckDB 1.5.5 command-line client. code/README.md has download commands for macOS, Linux and Windows.

  2. Check the installation:

    duckdb --version

  3. Open a terminal in the course's code/ folder. Every hands-on step in the lessons runs from there, for example:

    duckdb northpaw.duckdb -f module-01/01-clinicos-oltp.sql

All data is fictional, and everything stays in one local file, northpaw.duckdb. Delete it at any time to start fresh.

Modules

  1. The models you already know — a hands-on refresher of 3NF and star schemas on Northpaw's data, ending where they break.
  2. Hubs, links and satellites — business keys, hash keys and the three core table types.
  3. Loading the raw vault — staging, idempotent insert-only loads, hashdiffs and automated tests.
  4. Relationships over time and deletions — effectivity, record tracking, status tracking and multi-active satellites.
  5. Transactions, identities and hierarchies — non-historized links, same-as links, hierarchical links and reference data.
  6. Getting data out — information marts, virtual end dates, PIT tables, ghost records and bridges.
  7. Change, fit and the 2026 platform — absorbing change, deciding when Data Vault fits, and today's platforms.