Multi-Source CRM & Sales EDW data model
Data Vault 2.0 raw vault for integrating multiple CRM/MarTech/CPQ sources into one customer data platform: hubs (account/contact/opportunity/product/campaign), links (account-contact, opportunity-account, a transactional opportunity product-line link with quantity/price/amount, contact-campaign), and satellites (account, contact PII, an opportunity stage effectivity satellite, product, campaign details, and account status). Hash keys and load metadata are generated by the DDL layer. For a Salesforce-native, all-cloud (Sales/Service/Marketing) vault, use the Salesforce Customer 360 (Data Vault 2.0) template.
A multi-source CRM & sales enterprise data warehouse modeled as a Data Vault 2.0 raw vault for integrating heterogeneous systems (CRM, marketing automation, CPQ, ERP) into one conformed, historized vault: account, contact, opportunity, product, and campaign hubs; relationship links plus a transactional opportunity-line link; and descriptive plus effectivity satellites — with the contact-data lineage (GDPR consent, access, erasure) that makes Data Vault attractive for customer data platforms. 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.
- 16
- tables
- 100
- 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 CRM business keys
Separating account_id, contact_id, and opportunity_id into hubs lets marketing automation, CPQ, and billing source systems attach without reworking existing structures — the core agility of Data Vault.
Transactional opportunity-line link
Opportunity line items are immutable quote events, so the line link is insert-only and carries its measures (quantity, unit_price, amount) directly — no satellite needed.
Effectivity satellite for opportunity stages
Pipeline stages move forward and backward over time; an effectivity satellite records the valid period of each stage for accurate as-of pipeline and conversion reporting.
GDPR-driven masking on the contact satellite
Contact email is hashed and mailing address is restricted so the governance report flags sensitive identifiers; marketing_consent plus load_date + record_source give the lineage needed for consent and erasure handling.
Tables in the Multi-Source CRM & Sales 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 (5)
hub_account
Grain: one row per unique hub_account business key
| Column | Type | Keys |
|---|---|---|
| account_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_contact
Grain: one row per unique hub_contact business key
| Column | Type | Keys |
|---|---|---|
| contact_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_opportunity
Grain: one row per unique hub_opportunity business key
| Column | Type | Keys |
|---|---|---|
| opportunity_id | 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_campaign
Grain: one row per unique hub_campaign business key
| Column | Type | Keys |
|---|---|---|
| campaign_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_account_contact
Grain: one row per lnk_account_contact relationship
Which contacts belong to which account.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | |
| contact_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_opportunity_account
Grain: one row per lnk_opportunity_account relationship
Which account an opportunity is associated with.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_opportunity_product_line
Grain: one row per opportunity product line (non-historized link)
Transactional (non-historized) link — opportunity line items with measures.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| product_hk | binary | |
| line_id | varchar | BK |
| quantity | integer | |
| unit_price | decimal | |
| amount | decimal | |
| currency | varchar | |
| load_date | timestamp | |
| record_source | varchar |
lnk_contact_campaign
Grain: one row per lnk_contact_campaign relationship
Which contacts were targeted by / responded to which campaign.
| Column | Type | Keys |
|---|---|---|
| contact_hk | binary | |
| campaign_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
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_name | varchar | |
| industry | varchar | |
| account_type | varchar | |
| annual_revenue | decimal | |
| employee_count | integer | |
| billing_country | varchar | |
| owner_name | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_contact_details
Grain: one row per contact_hk per change
Contact descriptive history (PII).
| Column | Type | Keys |
|---|---|---|
| contact_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| mobile_phone | varchar | PII |
| job_title | varchar | |
| mailing_address | varchar | PII |
| marketing_consent | boolean | |
| lead_source | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_opportunity_status
Grain: one row per opportunity per stage change
Effectivity satellite tracking opportunity stage transitions over time.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | PK |
| load_date | timestamp | PK |
| stage | varchar | |
| forecast_category | varchar | |
| probability_pct | decimal | |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| 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_family | varchar | |
| list_price | decimal | |
| currency | varchar | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_campaign_details
Grain: one row per campaign_hk per change
Campaign descriptive history.
| Column | Type | Keys |
|---|---|---|
| campaign_hk | binary | PK |
| load_date | timestamp | PK |
| campaign_name | varchar | |
| campaign_type | varchar | |
| channel | varchar | |
| start_date | date | |
| end_date | date | |
| budgeted_cost | decimal | |
| actual_cost | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_account_status
Grain: one row per account per status change
Account status satellite split out for its rate of change.
| Column | Type | Keys |
|---|---|---|
| account_hk | binary | PK |
| load_date | timestamp | PK |
| lifecycle_stage | varchar | |
| health_score | integer | |
| 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_account_contact.account_hk → hub_account (many-to-one)
- lnk_account_contact.contact_hk → hub_contact (many-to-one)
- lnk_opportunity_account.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_account.account_hk → hub_account (many-to-one)
- lnk_opportunity_product_line.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_product_line.product_hk → hub_product (many-to-one)
- lnk_contact_campaign.contact_hk → hub_contact (many-to-one)
- lnk_contact_campaign.campaign_hk → hub_campaign (many-to-one)
- sat_account_details.account_hk → hub_account (many-to-one)
- sat_contact_details.contact_hk → hub_contact (many-to-one)
- sat_opportunity_status.opportunity_hk → hub_opportunity (many-to-one)
- sat_product_details.product_hk → hub_product (many-to-one)
- sat_campaign_details.campaign_hk → hub_campaign (many-to-one)
- sat_account_status.account_hk → hub_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.