Datawarehouse Data Modeling - Data Vault

About

The data vault (DV) is a relational data model technique used in the persistence layer of a datawarehouse

The Data Vault consists of three primary entity types:

  • Hubs: They are core business keys,
  • Links: They form all relationships,
  • and Satellites: They provide all descriptive information

The data vault is a detailed oriented, historical tracking and uniquely linked set of normalized tables that support one or more functional areas of business.

It's an hybrid approach encompassing the best of breed between 3rd normal form (3nf) and star schema. The design is flexible, scalable and adaptable of the needs of enterprise.

Naming Convention

Naming convention allow management, identification and monitoring of the entire system.

Table

Suffix Description
HUB or H HUB tables
LNK or L Standard Link Tables
HLNK or HL Hierarchical Links
LSA, SAL, SLNK, SL Same as Links
TLNK, TL Transactional Links
SAT. or S Generic Satellites
HSAT Hub Satellites
LSAT Link Satellites
PIT, or P Point-in-time tables
BR or B Bridge tables
REF or R Reference tables

Column

The naming convention for the standardized fields is:

Suffix Description
LDTS, LDT Load date time stamps
LEDTS, LEDT Load end date time stamps
SQN Sequence numbers
REC_SRC, RSRC Record sources
LSD, LSDT Last seen dates
SSQN Sub-sequencing identifiers

Performance Note

A full Data Vault loading cycle might take 40 or 50 minutes total time, (50 staging tables, 1 terabyte incomming data) – where a so-called “traditional” (type 2 dimension + facts) loading cycle for the same data sets would take about 3 to 4 hours…. It can all happen in PARALLEL…

Documentation / Reference


Powered by ComboStrap