Datavault or data vault modeling is a database modeling method that is designed to provide long-term historical storage of data coming in from multiple operational systems. It is also a method of looking at historical data that deals with issues such as auditing, tracing of data, loading speed, and resilience to change, as well as emphasizing the need to trace where all the data in the database came from. This means that every row in a data vault must be accompanied by record source and load date attributes, enabling an auditor to trace values back to the source. The concept was published in 2000 by Dan Linstedt.
Data vault modeling makes no distinction between good and bad data ("bad" meaning not conforming to business rules).Super Charge your data warehouse, page 74 This is summarized in the statement that a data vault stores "a single version of the facts" (also expressed by Dan Linstedt as "all the data, all of the time") as opposed to the practice in other data warehouse methods of storing "a single version of the truth"The next generation EDW where data that does not conform to the definitions is removed or "cleansed". A data vault enterprise data warehouse provides both a single version of facts and a single source of truth.Building a scalable datawarehouse with data vault 2.0, p. 6
The modeling method is designed to be resilient to change in the business environment where the data being stored is coming from, by explicitly separating structural information from descriptive attributes.Super Charge your data warehouse, page 21 Data vault is designed to enable parallel loading as much as possible,Super Charge your data warehouse, page 76 so that very large implementations can scale out without the need for major redesign.
Unlike the star schema (dimensional modelling) and the classical relational model (3NF), data vault and anchor modeling are well-suited for capturing changes that occur when a source system is changed or added, but are considered advanced techniques which require experienced data architects. Both data vaults and anchor models are entity-based models but anchor models have a more normalized approach.
History and philosophy
In its early days, Dan Linstedt referred to the modeling technique, which was to become data vault, as common foundational warehouse architectureBuilding a scalable datawarehouse with data vault 2.0, p. 11 or common foundational modeling architecture.Building a scalable datawarehouse with data vault 2.0, p. xv In data warehouse modeling, there are two well-known competing options for modeling the layer where the data are stored. Either you model according to Ralph Kimball, with conformed dimensions and an enterprise data bus, or you model according to Bill Inmon with the database normalized. Both techniques have issues when dealing with changes in the systems feeding the data warehouse. For conformed dimensions, you also have to cleanse data (to conform it), and this is undesirable in several cases since this inevitably will lose information. Data vault is designed to avoid or minimize the impact of those issues by moving them to areas of the data warehouse that are outside the historical storage area (cleansing is done in the data marts) and by separating the structural items (business keys and the associations between the business keys) from the descriptive attributes.
Dan Linstedt, the creator of the method, describes the resulting database as follows:
The detail-oriented Data Vault's philosophy is that all data is relevant data, even if it is not in line with established definitions and business rules. If data are not conforming to these definitions and rules, then that is a problem for the business, not the data warehouse. The determination of data being "wrong" is an interpretation of the data that stems from a particular point of view that may not be valid for everyone, or at every point in time. Therefore, the data vault must capture all data, and only when reporting or extracting data from the data vault is the data being interpreted.
Another issue to which the data vault is a response is that, more and more, there is a need for complete auditability and traceability of all the data in the data warehouse. Due to Sarbanes-Oxley requirements in the USA and similar measures in Europe, this is a relevant topic for many business intelligence implementations. The focus of any data vault implementation is complete traceability and auditability of all information.
Data Vault 2.0 is the new specification. It is an open standard.A short intro to#datavault 2.0 The new specification consists of three pillars: methodology (SEI/CMMI, Six Sigma, SDLC, etc..), the architecture (amongst others an input layer (data stage, called persistent staging area in Data Vault 2.0) and a presentation layer (data mart), and handling of data quality services and master data services), and the model. Within the methodology, the implementation of best practices is defined. Data Vault 2.0 has a focus on including new components such as big data and NoSQL , and also focuses on the performance of the existing model. The old specification (documented here for the most part) is highly focused on data vault modeling. It is documented in the book: Building a Scalable Data Warehouse with Data Vault 2.0.
History Data vault modeling was originally conceived by Dan Linstedt in the 1990s and was released in 2000 as a public domain modeling method. In a series of five articles in The Data Administration Newsletter, the basic rules of the Data Vault method are expanded and explained. These contain a general overview, an overview of the components, a discussion about end dates and joins,Data Vault Series 3 – End Dates and Basic Joins link tables,Data Vault Series 4 – Link tables, paragraph 2.3 and an article on loading practices.Data Vault Series 5 – Loading Practices
An alternative (and seldom used) name for the method is "Common Foundational Integration Modelling Architecture."Data Warehousing for Dummies, page 83
Data Vault 2.0 is a newer conception (2013) and incorporates NoSQL, unstructured, semi-structured integration, and various best practices.A short intro to #datavault 2.0Data Vault 2.0 Being Announced
Alternative interpretations According to Dan Linstedt, the Data Model is inspired by (or patterned off) a simplistic view of neurons, dendrites, and synapses – where neurons are associated with Hubs and Hub Satellites, Links are dendrites (vectors of information), and other Links are synapses (vectors in the opposite direction). By using a data mining set of algorithms, links can be scored with confidence and strength ratings. They can be created and dropped on the fly in accordance with learning about relationships that currently don't exist. The model can be automatically morphed, adapted, and adjusted as it is used and fed new structures.Super Charge your Data Warehouse, paragraph 5.20, page 110
Another view is that a data vault model provides an ontology of the Enterprise in the sense that it describes the terms in the domain of the enterprise (Hubs) and the relationships among them (Links), adding descriptive attributes (Satellites) where necessary.
Another way to think of a data vault model is as a graphical model. The data vault model actually provides a "graph-based" model with hubs and relationships in a relational database world. In this manner, the developer can use SQL to get at graph-based relationships with sub-second responses.
Basic notions Data Vault 2.0 organizes data into three core components that separate stable identifiers from changing descriptive attributes:
Hub – stores a unique business key for a core business concept together with minimal metadata for lineage/audit; it acts as an integration point across sources. Link – captures the relationship (often many-to-many) between hubs; the participating hub keys define the grain of the relationship. Satellite – contains descriptive attributes and their history associated with a hub or link; satellites are append-only, so every change is preserved (similar in effect to Type-II history in dimensional models).
Specialized satellites support temporal semantics. For example, an effectiveness satellite on a link records begin/end dates representing when the relationship is considered effective by the business.
Layers Raw Vault – a source-driven integration layer that retains granular, auditable history with minimal transformations. Business Vault – a derived layer that applies business rules and query-assistance structures (e.g., PIT and bridge tables) to facilitate downstream consumption.
Use with dimensional models. In practice, Data Vault commonly serves as the historical integration layer, while star-schema information marts are projected from the Raw/Business Vault for performant analytics and simpler user access.
Hubs Hubs contain a list of unique business keys with low propensity to change. Hubs also contain a surrogate key for each Hub item and metadata describing the origin of the business key. The descriptive attributes for the information on the Hub (such as the description for the key, possibly in multiple languages) are stored in structures called Satellite tables, which will be discussed below.
The Hub contains at least the following fields:Data Vault Forum, Standards section, section 3.0 Hub Rules a surrogate key, used to connect the other structures to this table. a business key, the driver for this hub. The business key can consist of multiple fields. The record source, which can be used to see which system loaded each business key first. Optionally, you can also have metadata fields with information about manual updates (user/time) and the extraction date.
A hub is not allowed to contain multiple business keys, except when two systems deliver the same business key but with collisions that have different meanings.
Hubs should normally have at least one satellite.
Hub example This is an example of a hub-table containing cars, called "Car" (H_CAR). The driving key is the vehicle identification number.
Fieldname Description Mandatory? Comment H_CAR_ID Sequence ID and surrogate key for the hub No Recommended but optionalData Vault Modeling Specification v1.0.9 VEHICLE_ID_NR The business key that drives this hub. Can be more than one field for a composite business key Yes H_RSRC The record source of this key when first loaded Yes LOAD_AUDIT_ID An ID into a table with audit information, such as load time, duration of load, number of lines, etc. No
Links Associations or transactions between business keys (relating for instance the hubs for customer and product with each other through the purchase transaction) are modeled using link tables. These tables are basically many-to-many join tables, with some metadata.
Links can link to other links, to deal with changes in granularity (for instance, adding a new key to a database table would change the grain of the database table). For instance, if you have an association between customer and address, you could add a reference to a link between the hubs for product and transport company. This could be a link called "Delivery". Referencing a link in another link is considered a bad practice, because it introduces dependencies between links that make parallel loading more difficult. Since a link to another link is the same as a new link with the hubs from the other link, in these cases creating the links without referencing other links is the preferred solution (see the section on loading practices for more information).