Banking EDW data model
Data Vault 2.0 raw vault for core banking: hubs (customer/account/product/branch), links (customer-account, account-branch, account-product, transactions), and satellites (customer, account, balance, branch, product details + a customer-account effectivity satellite). Hash keys and load metadata are generated by the DDL layer.
Banking enterprise data warehouse modeled as a Data Vault 2.0 raw vault: customer, account, product, and branch hubs; ownership, management, and product links; a non-historized transaction link; and descriptive plus effectivity satellites — built for integrating many source systems with full historization and the audit lineage (BCBS-239, Basel, GLBA) that makes Data Vault the standard for regulated banking EDWs. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 15
- tables
- 92
- columns
- 14
- relationships
- 7
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Hubs hold only business keys
Separating the immutable business keys (customer_id, account_number) into hubs lets new source systems and relationships attach without reworking existing structures — the core agility of Data Vault.
Non-historized transaction link
Transactions are immutable events, so the transaction link is insert-only and carries its measures directly — no satellite needed.
Balance split into its own satellite
Account balances change far more frequently than account attributes; a separate high-rate-of-change satellite keeps history compact and loads efficient (satellite-per-rate-of-change).
Effectivity satellite on the customer-account link
Joint/primary ownership changes over time; an effectivity satellite records the valid period of each relationship for as-of reporting.
Auditability drives Data Vault in banking
Load_date + record_source on every row provide a queryable audit trail for BCBS-239 lineage and Basel/GLBA reporting without scraping logs.
Tables in the Banking EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (4)
hub_customer
Grain: one row per unique hub_customer business key
| Column | Type | Keys |
|---|---|---|
| customer_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_account
Grain: one row per unique hub_account business key
| Column | Type | Keys |
|---|---|---|
| account_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_product
Grain: one row per unique hub_product business key
| Column | Type | Keys |
|---|---|---|
| product_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_branch
Grain: one row per unique hub_branch business key
| Column | Type | Keys |
|---|---|---|
| branch_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_customer_account
Grain: one row per lnk_customer_account relationship
Which customer owns which account.
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_branch
Grain: one row per lnk_account_branch relationship
Which branch manages which account.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| branch_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_account_product
Grain: one row per lnk_account_product relationship
Which product an account is an instance of.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| product_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_transaction
Grain: one row per posted transaction (non-historized link)
Transactional (non-historized) link — insert-only ledger events.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| customer_hk | binary | |
| transaction_id | varchar | BK |
| transaction_ts | timestamp | |
| transaction_type | varchar | |
| direction | varchar | |
| amount | decimal | |
| currency | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_customer_details
Grain: one row per customer_hk per change
Customer descriptive history.
| Column | Type | Keys |
|---|---|---|
| customer_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| tax_id | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| customer_segment | varchar | |
| kyc_status | varchar | |
| risk_rating | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_details
Grain: one row per account_hk per change
Account descriptive history.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | PK |
| load_date | timestamp | PK |
| account_type | varchar | |
| currency | varchar | |
| open_date | date | |
| account_status | varchar | |
| interest_rate | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_balance
Grain: one row per account per balance change (high-rate-of-change satellite)
Balance satellite split out for its high rate of change.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | PK |
| load_date | timestamp | PK |
| closing_balance | decimal | |
| available_balance | decimal | |
| interest_accrued | decimal | |
| is_overdrawn | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_branch_details
Grain: one row per branch_hk per change
Branch descriptive history.
| Column | Type | Keys |
|---|---|---|
| branch_hk | binary | PK |
| load_date | timestamp | PK |
| branch_name | varchar | |
| city | varchar | |
| state | varchar | |
| region | varchar | |
| manager_name | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_product_details
Grain: one row per product_hk per change
Product descriptive history.
| Column | Type | Keys |
|---|---|---|
| product_hk | binary | PK |
| load_date | timestamp | PK |
| product_name | varchar | |
| product_category | varchar | |
| base_interest_rate | decimal | |
| monthly_fee | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_customer_account_role
Grain: one row per ownership relationship per effectivity change
Effectivity satellite on the customer-account link.
| Column | Type | Keys |
|---|---|---|
| customer_account_hk | binary | PK |
| load_date | timestamp | PK |
| relationship_type | varchar | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_currency
Grain: one row per ISO currency
| Column | Type | Keys |
|---|---|---|
| currency_code | varchar | BK |
| currency_name | varchar | |
| minor_units | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_customer_account.customer_hk → hub_customer (many-to-one)
- lnk_customer_account.account_hk → hub_account (many-to-one)
- lnk_account_branch.account_hk → hub_account (many-to-one)
- lnk_account_branch.branch_hk → hub_branch (many-to-one)
- lnk_account_product.account_hk → hub_account (many-to-one)
- lnk_account_product.product_hk → hub_product (many-to-one)
- lnk_transaction.account_hk → hub_account (many-to-one)
- lnk_transaction.customer_hk → hub_customer (many-to-one)
- sat_customer_details.customer_hk → hub_customer (many-to-one)
- sat_account_details.account_hk → hub_account (many-to-one)
- sat_account_balance.account_hk → hub_account (many-to-one)
- sat_branch_details.branch_hk → hub_branch (many-to-one)
- sat_product_details.product_hk → hub_product (many-to-one)
- sat_customer_account_role.customer_account_hk → lnk_customer_account (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.