80 ) Data vault modeling steps

 

Data Vault Modeling in Banking: Architecture, Comparison, and Implementation

Step 1: The Client's Existing System and Tables (OLTP)

In my banking project, the client operated multiple legacy core banking systems and siloed transaction platforms (Savings, Credit Cards, and Loans) running on separate transactional OLTP databases (Oracle and DB2):

• Core Banking System (Oracle OLTP): Handled everyday customer deposits and account activities.

  • CUST_MASTER: Stored customer ID, personal info, national ID, and address.

  • ACCT_MASTER: Stored account numbers, account types (Savings, Current), and live balances.

  • TRANS_LOG: Stored real-time deposit and withdrawal logs.

• Loan Management System (DB2 OLTP): Handled lending operations and mortgage applications.

  • LOAN_APPLICANT: Stored applicant details and credit scores.

  • LOAN_CONTRACT: Stored loan IDs, approved amounts, interest rates, and active statuses.

Step 2: The Problems They Faced

• Rigid Source System Integration: Whenever the bank acquired a smaller regional bank or launched a new product line with a different database schema, integrating those tables into a traditional dimensional Star Schema broke existing ETL pipelines and forced massive redesigns of surrogate keys.

• Lack of Complete Auditability: Regulatory authorities required a strict, unalterable audit trail showing the exact source system, exact load timestamp, and exact record state for every single banking transaction over a 10-year period. Dimensional models (like SCD Type 2) often overwrote or compressed history and struggled to trace multi-source lineage cleanly.

• Collaborative Loading Bottlenecks: Multiple data engineering teams could not load data into a traditional dimensional warehouse simultaneously because dimension and fact tables had strict foreign key dependency locks. Loading fact tables required dimensions to be fully populated first, causing severe delays in nightly batch processing windows.

Step 3: The Client's Expectation and Why

• The Goal: They wanted an enterprise-grade data warehouse built on Data Vault 2.0 architecture that could ingest data from multiple disparate banking sources without breaking when source structures changed.

• The Reason: They needed a centralized, fully auditable historical repository that separated business keys, descriptive attributes, and relationships so that audit compliance teams and downstream data marts could trace financial data lineage instantly and load data in parallel without dependency locks.

Step 4: The Solution Design I Proposed (Data Vault 2.0 Architecture)

To meet these requirements, I designed a Data Vault 2.0 model consisting of three core table types:

1. Hubs (Business Keys): Stored unique business keys with no descriptive data or foreign keys.

   • HUB_CUSTOMER: Stored unique Customer_ID, Load_Timestamp, and Record_Source.

   • HUB_ACCOUNT: Stored unique Account_Number, Load_Timestamp, and Record_Source.

2. Links (Relationships / Associations): Stored associations between business keys to represent transactions or structural links.

   • LINK_CUSTOMER_ACCOUNT: Connected HUB_CUSTOMER and HUB_ACCOUNT.

   • LINK_ACCOUNT_TRANSACTION: Connected HUB_ACCOUNT to transaction events.

3. Satellites (Descriptive Context & History): Stored descriptive attributes and historical changes tied directly to Hubs or Links.

   • SAT_CUSTOMER_DETAILS: Attached to HUB_CUSTOMER, storing names, addresses, and credit scores with full audit tracking.

   • SAT_ACCOUNT_BALANCE: Attached to HUB_ACCOUNT, tracking balance updates and status flags over time.

Interview Q&A: Data Vault vs. Dimensional Modeling

Q1: Did you work on Data Vault modeling for banking? Give all steps.

Answer:

Yes, I implemented Data Vault 2.0 for a multi-system banking client. The step-by-step implementation process included:

1. Source Analysis & Business Key Identification: Mapped out core business concepts across Oracle and DB2 systems to identify unique business keys (like Customer ID, Account Number) for Hubs.

2. Relationship Mapping: Identified how business entities interacted (such as customers holding accounts or accounts executing transactions) to design Links.

3. Attribute & History Separation: Extracted descriptive details (like names, balances, and risk ratings) into Satellites to maintain a pure audit trail.

4. ETL Pipeline Configuration: Built automated hash-key generation pipelines (using MD5/SHA hashes) in the staging layer to load Hubs, Links, and Satellites in parallel without foreign key dependency bottlenecks.

5. Information Mart (Star Schema) Generation: Built downstream dimensional data marts on top of the Data Vault presentation layer to serve fast business reports and dashboards.

Q2: What is the difference and advantage over dimensional modeling?

Answer:

• Structural Difference: A Dimensional model (Star Schema) is built for fast business reporting, combining business keys and descriptive text into centralized Fact and Dimension tables. A Data Vault model is built for enterprise integration and auditing, breaking data down into Hubs (keys), Links (relationships), and Satellites (history).

• Key Advantages of Data Vault over Dimensional:

  • Extensibility & Agility: If a source system adds new columns or changes tables, you only add a new Satellite table. It never breaks existing structures or downstream tables.

  • 100% Traceability & Auditability: Every single record in a Data Vault includes exact load timestamps and record source metadata, making it ideal for strict banking regulations.

  • Parallel Loading: Because Hubs, Links, and Satellites use hash keys instead of database-generated sequential surrogate keys, multiple data pipelines can load data simultaneously without locking errors.

Q3: How did you handle changes in Data Vault? Give all steps.

Answer:

Handling changes in a Data Vault is streamlined because the architecture is designed to accept schema evolution without rewriting historical data. The steps are:

1. Handling New Attributes (Column Changes): If a bank adds a new field to an existing source table (e.g., adding Customer_Email), we do not alter existing tables. We simply create a new Satellite table or add the column to a new version of the Satellite linked to that Hub, preserving past history cleanly.

2. Handling New Source Systems (Integration): When integrating a new bank acquisition, we ingest its unique business keys into the existing Hubs, use the same hash-key generation logic, and route its descriptive data into new or existing Satellites, mapping the Record_Source metadata to track origin.

3. Handling Changed Data (History Tracking): Satellites automatically capture history by inserting a new row whenever an attribute changes, driven by hash-diff comparisons between the incoming payload and the existing active satellite record.

Q4: When and how do you choose Data Vault over a dimensional model?

Answer:

• When to Choose Data Vault: We choose Data Vault when building an Enterprise Data Warehouse (EDW) for large organizations (like banks or insurance firms) that integrate multiple disjointed source systems, face frequent structural source changes, and require strict, long-term regulatory audit tracking.

• When to Choose Dimensional (Star Schema): We choose a dimensional model when building a department-specific data mart where the primary goal is fast, user-friendly business reporting and dashboarding for end-users.

• How We Combine Them: In enterprise architecture, we don't choose just one; we use Data Vault as the Enterprise Core Layer (single source of truth and audit storage) and generate Dimensional Star Schemas downstream from the Data Vault to feed end-user business reports efficiently.

Additional Interview Questions on Data Vault Modeling

Q5: What is a "Hash Key" in Data Vault 2.0, and why do we use surrogate sequence keys?

Answer:

In Data Vault 2.0, we use cryptographic hash keys (like MD5 or SHA-256) generated from natural business keys instead of database-generated sequential surrogate keys (like Auto-Increment integers). We use hash keys because they allow completely independent, parallel loading of data streams from multiple systems without needing database sequence lookups or causing locking bottlenecks.

Q6: How do you handle "Same-As Links" in Data Vault modeling? Give a banking example.

Answer:

A Same-As Link is used to handle situations where a single business entity has multiple business keys across different systems that need to be resolved. For example, if a banking customer has a retail customer ID in one system (CUST-101) and a corporate banking ID in another system (CORP-998), a Same-As Link connects the two Hub records together to indicate they belong to the same physical person.

Q7: What is a "Multi-Active Satellite," and when would you use one in a banking project?

Answer:

A Multi-Active Satellite is used when a single business key can have multiple active values at the same time for a single point in time, breaking the standard one-to-one relationship between a Hub key and its Satellite attributes. In banking, we use this for a customer who has multiple phone numbers, active email addresses, or multiple credit cards linked to a single account where history needs to be maintained for each individual attribute independently.

Q8: How do you handle Deletes or "Soft Deletes" from source systems in Data Vault?

Answer:

When a record is deleted or deactivated in the source system, Data Vault preserves complete historical integrity. We do not delete rows from Hubs or Links. Instead, we insert a new record in the Satellite table with a status flag (e.g., Is_Deleted = True or Record_Status = 'Inactive') driven by a hash-diff check, ensuring compliance and historical accuracy are maintained.

Q9: How do you query data from a Data Vault model for reporting, since data is split across Hubs, Links, and Satellites?

Answer:

Because Data Vault is normalized and spread across multiple tables, querying it directly for business reports requires complex inner joins across Hubs, Links, and Satellites. To make reporting practical, we build Information Marts (Star Schemas) downstream from the Data Vault presentation layer. Business users and BI tools query the Star Schema, while the Data Vault remains the enterprise audit core.

SQL DDL: Creating Data Vault Tables with Primary & Foreign Keys

Below is the SQL script to create a core Data Vault structure (Hub, Link, and Satellite) for banking customer accounts with primary keys (PK) and foreign keys (FK).

SQL

-- 1. Create Hub Table (Stores unique business keys)
CREATE TABLE HUB_CUSTOMER (
    Customer_HK CHAR(32) NOT NULL,          -- Hash key (PK)
    Customer_ID VARCHAR(50) NOT NULL,       -- Natural business key
    Load_Timestamp DATETIME NOT NULL,       -- Audit metadata
    Record_Source VARCHAR(100) NOT NULL,    -- Audit metadata
    CONSTRAINT PK_HUB_CUSTOMER PRIMARY KEY (Customer_HK)
);

-- 2. Create another Hub Table for Accounts
CREATE TABLE HUB_ACCOUNT (
    Account_HK CHAR(32) NOT NULL,           -- Hash key (PK)
    Account_Number VARCHAR(50) NOT NULL,    -- Natural business key
    Load_Timestamp DATETIME NOT NULL,       -- Audit metadata
    Record_Source VARCHAR(100) NOT NULL,    -- Audit metadata
    CONSTRAINT PK_HUB_ACCOUNT PRIMARY KEY (Account_HK)
);

-- 3. Create Link Table (Stores relationships between Hubs)
CREATE TABLE LINK_CUSTOMER_ACCOUNT (
    Customer_Account_HK CHAR(32) NOT NULL,  -- Hash key of the link itself (PK)
    Customer_HK CHAR(32) NOT NULL,          -- FK pointing to HUB_CUSTOMER
    Account_HK CHAR(32) NOT NULL,           -- FK pointing to HUB_ACCOUNT
    Load_Timestamp DATETIME NOT NULL,       -- Audit metadata
    Record_Source VARCHAR(100) NOT NULL,    -- Audit metadata
    CONSTRAINT PK_LINK_CUSTOMER_ACCOUNT PRIMARY KEY (Customer_Account_HK),
    CONSTRAINT FK_LINK_CUST_ACCT_CUSTOMER FOREIGN KEY (Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK),
    CONSTRAINT FK_LINK_CUST_ACCT_ACCOUNT FOREIGN KEY (Account_HK) 
        REFERENCES HUB_ACCOUNT (Account_HK)
);

-- 4. Create Satellite Table (Stores descriptive attributes and history for a Hub)
CREATE TABLE SAT_CUSTOMER_DETAILS (
    Customer_HK CHAR(32) NOT NULL,          -- FK pointing to HUB_CUSTOMER
    Load_Timestamp DATETIME NOT NULL,       -- Part of Composite PK (tracks version start time)
    Hash_Diff CHAR(32) NOT NULL,            -- Detects changes in descriptive columns
    First_Name VARCHAR(100),
    Last_Name VARCHAR(100),
    Credit_Score INT,
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_SAT_CUSTOMER_DETAILS PRIMARY KEY (Customer_HK, Load_Timestamp),
    CONSTRAINT FK_SAT_CUST_HUB_CUSTOMER FOREIGN KEY (Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK)
);

Handling Schema Changes in Data Vault Modeling (Adding / Deleting Columns & Tables)

Step-by-Step Process for Schema Changes in Data Vault

Unlike traditional relational or dimensional models (where altering tables can break fact/dimension joins or require expensive historical rewrites), Data Vault is designed specifically for additive and non-destructive change management.

1. Adding a New Column (Attribute)

• How it works: You never modify an existing Satellite table's structure if you can avoid it, but standard Data Vault allows adding columns to existing Satellites, or better yet, creating a new Satellite table attached to the same Hub.

• The Steps:

  1. Identify which business entity (Hub) the new attribute belongs to (e.g., adding Customer_Email to HUB_CUSTOMER).

  2. Create a brand-new Satellite table specifically for this attribute (e.g., SAT_CUSTOMER_CONTACT) linked to the existing Hub key.

  3. Update the ETL pipeline to populate the new Satellite using hash-diff tracking.

  • Advantage: Existing tables and historical loads remain completely untouched and unbroken.

2. Adding a New Table (Entity or Source)

• How it works: Adding a completely new business entity or integrating a new source system requires zero changes to existing structures.

• The Steps:

  1. Create a new Hub for the new business key.

  2. Create a new Link if it connects to existing Hubs.

  3. Create a new Satellite for its descriptive attributes.

  4. Route the new source stream into these new tables. Because Data Vault uses hash keys, it integrates seamlessly without foreign key dependency conflicts on existing tables.

3. Deleting or Soft-Deleting a Column / Record

• How it works: Data Vault enforces a strict non-deletion policy for audit compliance and historical accuracy. You never drop rows or columns physically.

• The Steps:

  1. For a deleted source column: Mark the old column as deprecated in documentation. In the ETL pipeline, map it to NULL for future loads, while preserving all historical values in past satellite rows.

  2. For a deleted/inactive source record: Do not delete rows from Hubs, Links, or Satellites. Instead, generate a new record in the Satellite table with a status flag set to inactive (e.g., Is_Active = 0 or Record_Status = 'DELETED'), driven by the hash-diff change capture.

SQL DDL: Implementing Schema Changes

Below is the SQL script demonstrating how we handle these changes in a Data Vault environment—specifically showing how to add a new Satellite table for a new column and how to handle soft-deletes via a status flag.

SQL

-- 1. SCENARIO: ADDING A NEW COLUMN
-- Instead of altering an old table, we create a new Satellite table linked to the existing Hub 
-- to store new attributes (e.g., Customer Email and Phone Number).

CREATE TABLE SAT_CUSTOMER_CONTACT (
    Customer_HK CHAR(32) NOT NULL,          -- FK pointing to HUB_CUSTOMER
    Load_Timestamp DATETIME NOT NULL,       -- Part of Composite PK (version start time)
    Hash_Diff CHAR(32) NOT NULL,            -- Detects changes in contact info
    Email_Address VARCHAR(150),
    Phone_Number VARCHAR(50),
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_SAT_CUSTOMER_CONTACT PRIMARY KEY (Customer_HK, Load_Timestamp),
    CONSTRAINT FK_SAT_CONTACT_HUB FOREIGN KEY (Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK)
);


-- 2. SCENARIO: HANDLING SOURCE DELETES (SOFT DELETE / STATUS FLAG)
-- When a record is deleted or deactivated in the source system, we insert a new row 
-- in the existing Satellite with an explicit deletion/inactive flag. No rows are ever physically deleted.

CREATE TABLE SAT_CUSTOMER_STATUS (
    Customer_HK CHAR(32) NOT NULL,          -- FK pointing to HUB_CUSTOMER
    Load_Timestamp DATETIME NOT NULL,       -- Part of Composite PK
    Hash_Diff CHAR(32) NOT NULL,            -- Detects status changes
    Is_Active INT NOT NULL,                 -- 1 = Active, 0 = Soft Deleted / Inactive
    Deletion_Reason VARCHAR(255),           -- Optional context from source
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_SAT_CUSTOMER_STATUS PRIMARY KEY (Customer_HK, Load_Timestamp),
    CONSTRAINT FK_SAT_STATUS_HUB FOREIGN KEY (Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK)
);

Deep Dive: Resolving "Same-As" Customer Entities in Data Vault

Step 1: The Problem Scenario (How it Was Previously)

The Issue

When our client bank merged with a regional financial institution, customers interacted with both retail and corporate banking systems. Because these legacy systems operated on separate databases without a shared identifier, a single person (e.g., John Doe) was assigned two completely different business keys:

• Retail System: Assigned CUST-RETAIL-1001

• Corporate System: Assigned HUB-CORP-9988

Previous Data Architecture (Siloed Approach)

Our raw transactional tables and initial Data Vault models treated these as two distinct human beings.

• Previous SQL (Creating Separate Hubs):

SQL

-- Previously, separate hubs were created without knowing they belonged to the same person
CREATE TABLE HUB_CUSTOMER_RETAIL (
    Customer_Retail_HK CHAR(32) NOT NULL,
    Customer_ID VARCHAR(50) NOT NULL,
    Load_Timestamp DATETIME NOT NULL,
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_HUB_CUST_RETAIL PRIMARY KEY (Customer_Retail_HK)
);

CREATE TABLE HUB_CUSTOMER_CORPORATE (
    Customer_Corp_HK CHAR(32) NOT NULL,
    Corporate_ID VARCHAR(50) NOT NULL,
    Load_Timestamp DATETIME NOT NULL,
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_HUB_CUST_CORP PRIMARY KEY (Customer_Corp_HK)
);

• Previous Design Structure:

Plaintext

[Source System A: Retail] ---> [HUB_CUSTOMER_RETAIL] ---> [SAT_RETAIL_DETAILS]
[Source System B: Corp]   ---> [HUB_CUSTOMER_CORP]   ---> [SAT_CORP_DETAILS]
*(Completely disconnected silos leading to fragmented reporting and double-counting)*

Step 2: The Resolution (How it Is Now)

The Solution Design

To connect these isolated entities without altering the core immutable Hub records, we introduced a Same-As Link (LINK_CUSTOMER_SAME_AS).

1. Deterministic Matching Process: In our staging layer, a matching engine evaluated shared attributes (National ID, Tax ID, and Mobile Phone Number).

2. Same-As Link Creation: Once a match was confirmed, we captured the relationship in a specialized Link table that points two Hub keys back to each other.

3. Unified Downstream View: Downstream business data marts consumed this link to merge the profiles into a single master customer dimension.

Updated SQL Scripts (Current Architecture)

SQL

-- 1. Unified Hub Definition (or keeping separate Hubs bridged via a Same-As Link)
-- Here we maintain the Hubs, but link them explicitly.
CREATE TABLE HUB_CUSTOMER (
    Customer_HK CHAR(32) NOT NULL,
    Master_Customer_ID VARCHAR(50) NOT NULL,
    Load_Timestamp DATETIME NOT NULL,
    Record_Source VARCHAR(100) NOT NULL,
    CONSTRAINT PK_HUB_CUSTOMER PRIMARY KEY (Customer_HK)
);

-- 2. Updated Link Table: Same-As Link to bridge duplicate/merged identities
CREATE TABLE LINK_CUSTOMER_SAME_AS (
    Same_As_Link_HK CHAR(32) NOT NULL,      -- Hash key of the relationship (PK)
    Source_Customer_HK CHAR(32) NOT NULL,   -- FK pointing to HUB_CUSTOMER (e.g., Retail profile)
    Target_Customer_HK CHAR(32) NOT NULL,   -- FK pointing to HUB_CUSTOMER (e.g., Corporate profile)
    Load_Timestamp DATETIME NOT NULL,       -- Audit metadata
    Record_Source VARCHAR(100) NOT NULL,    -- Audit metadata
    CONSTRAINT PK_LINK_CUSTOMER_SAME_AS PRIMARY KEY (Same_As_Link_HK),
    CONSTRAINT FK_CUST_SAME_AS_SOURCE FOREIGN KEY (Source_Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK),
    CONSTRAINT FK_CUST_SAME_AS_TARGET FOREIGN KEY (Target_Customer_HK) 
        REFERENCES HUB_CUSTOMER (Customer_HK)
);

Updated Design Structure

Plaintext

[HUB_CUSTOMER (Retail Key)]  <\
                                >--- [LINK_CUSTOMER_SAME_AS] ---> [Unified Business Data Vault / Mart View]
[HUB_CUSTOMER (Corp Key)]    </