Salesforce Customer 360 data model
Data Vault 2.0 raw vault for a multi-cloud Salesforce Customer 360. Eight business-key hubs, ten links (including OpportunityContactRole, CampaignMember, lead conversion, a transactional opportunity-line link, and a Campaign Influence link), and ten satellites with PII masking and effectivity tracking for opportunity stage and case status. Hash keys and HASH_DIFF are generated by the DDL layer.
A Salesforce Customer 360 raw vault spanning Sales, Service, and Marketing clouds: hubs for Account, Contact, Lead, Opportunity, Case, Campaign, User, and Product; links for account-contact, opportunity, case, campaign membership, lead conversion, opportunity lines, and Campaign Influence; and satellites (including effectivity satellites for opportunity stage and case status) for fully auditable, multi-source loading of Salesforce into the warehouse. 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.
- 29
- tables
- 195
- columns
- 34
- relationships
- 13
- 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 the Salesforce 18-char business keys
Separating Account, Contact, Lead, Opportunity, Case, and Campaign Ids into hubs decouples them from any single object's schema, so adding Service or Marketing cloud sources later means new sats/links, not reshaping existing structures.
Effectivity satellites for opportunity stage and case status
Stage and status change frequently and must be queryable as-of; dedicated effectivity satellites with effective_from/to and is_current capture the full transition history Salesforce overwrites in place.
Lead conversion modelled as a unit-of-work link
Lead conversion creates an Account, Contact, and Opportunity in one action; a single same-as/unit-of-work link records all four keys together, preserving the conversion event for funnel analysis.
Transactional links for opportunity lines and Campaign Influence
OpportunityLineItem and Campaign Influence carry measures and are immutable events, so they are non-historized transactional links rather than hub+sat pairs, keeping line- and touch-grain facts close to the raw load.
PII masked on contact, lead, and user satellites
GDPR/CCPA require masking personal data; email is hashed and mailing address is restricted with full masking so the governance report has signal directly from the raw vault.
Tables in the Salesforce Customer 360 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 (8)
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_lead
Grain: one row per unique hub_lead business key
| Column | Type | Keys |
|---|---|---|
| lead_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_case
Grain: one row per unique hub_case business key
| Column | Type | Keys |
|---|---|---|
| case_id | 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 |
hub_user
Grain: one row per unique hub_user business key
| Column | Type | Keys |
|---|---|---|
| user_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 |
Links (10)
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
The account an opportunity belongs to.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_opportunity_contact
Grain: one row per OpportunityContactRole
Buying-group roles linking opportunities to contacts.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| contact_hk | binary | |
| contact_role | varchar | |
| is_primary | boolean | |
| load_date | timestamp | |
| record_source | varchar |
lnk_opportunity_owner
Grain: one row per lnk_opportunity_owner relationship
Opportunity ownership (OwnerId).
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| user_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_case_account
Grain: one row per lnk_case_account relationship
The account a case is logged against.
| Column | Type | Keys |
|---|---|---|
| case_hk | binary | |
| account_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_case_contact
Grain: one row per lnk_case_contact relationship
The contact who raised a case.
| Column | Type | Keys |
|---|---|---|
| case_hk | binary | |
| contact_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_campaign_member
Grain: one row per CampaignMember (contact or lead)
Campaign membership — a contact or lead associated with a campaign.
| Column | Type | Keys |
|---|---|---|
| campaign_hk | binary | |
| contact_hk | binary | |
| lead_hk | binary | |
| campaign_member_id | varchar | BK |
| member_status | varchar | |
| has_responded | boolean | |
| load_date | timestamp | |
| record_source | varchar |
lnk_lead_conversion
Grain: one row per converted lead
Lead conversion — ties a lead to the account, contact, and opportunity it became.
| Column | Type | Keys |
|---|---|---|
| lead_hk | binary | |
| account_hk | binary | |
| contact_hk | binary | |
| opportunity_hk | binary | |
| converted_date | date | |
| load_date | timestamp | |
| record_source | varchar |
lnk_opportunity_line
Grain: one row per OpportunityLineItem (non-historized transactional link)
Transactional link — opportunity line items with measures.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| product_hk | binary | |
| opportunity_line_id | varchar | BK |
| quantity | decimal | |
| unit_price | decimal | |
| total_price | decimal | |
| currency_code | varchar | |
| load_date | timestamp | |
| record_source | varchar |
lnk_campaign_influence
Grain: one row per (opportunity, campaign, contact, model) influence allocation
Transactional link — Campaign Influence revenue attribution across touches.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | |
| campaign_hk | binary | |
| contact_hk | binary | |
| attribution_model | varchar | |
| influence_pct | decimal | |
| attributed_amount | decimal | |
| touch_position | varchar | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (10)
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 | |
| account_type | varchar | |
| industry | varchar | |
| annual_revenue | decimal | |
| employee_count | integer | |
| billing_country | varchar | |
| owner_name | varchar | PII |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_contact_details
Grain: one row per contact_hk per change
Contact descriptive history (PII, GDPR/CCPA-masked).
| Column | Type | Keys |
|---|---|---|
| contact_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| job_title | varchar | |
| lead_source | varchar | |
| mailing_address | varchar | PII |
| has_opted_out_of_email | boolean | |
| do_not_call | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_lead_details
Grain: one row per lead_hk per change
Lead descriptive history (PII, GDPR/CCPA-masked).
| Column | Type | Keys |
|---|---|---|
| lead_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| company | varchar | |
| varchar | PII | |
| lead_status | varchar | |
| rating | varchar | |
| lead_source | varchar | |
| industry | varchar | |
| is_converted | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_opportunity_details
Grain: one row per opportunity_hk per change
Opportunity descriptive history.
| Column | Type | Keys |
|---|---|---|
| opportunity_hk | binary | PK |
| load_date | timestamp | PK |
| opportunity_name | varchar | |
| opportunity_type | varchar | |
| amount | decimal | |
| annual_recurring_revenue | decimal | |
| lead_source | varchar | |
| close_date | date | |
| 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_current | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_case_details
Grain: one row per case_hk per change
Case descriptive history.
| Column | Type | Keys |
|---|---|---|
| case_hk | binary | PK |
| load_date | timestamp | PK |
| subject | varchar | PII |
| case_type | varchar | |
| case_reason | varchar | |
| priority | varchar | |
| origin | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_case_status
Grain: one row per case per status change
Effectivity satellite tracking case status and ownership over time.
| Column | Type | Keys |
|---|---|---|
| case_hk | binary | PK |
| load_date | timestamp | PK |
| status | varchar | |
| is_escalated | boolean | |
| owner_name | varchar | PII |
| effective_from | date | |
| effective_to | date | |
| is_current | 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 | |
| status | varchar | |
| start_date | date | |
| end_date | date | |
| budgeted_cost | decimal | |
| actual_cost | decimal | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_user_details
Grain: one row per user_hk per change
User descriptive history (PII-masked).
| Column | Type | Keys |
|---|---|---|
| user_hk | binary | PK |
| load_date | timestamp | PK |
| user_name | varchar | PII |
| varchar | PII | |
| role_name | varchar | |
| team | varchar | |
| region | varchar | |
| profile_name | varchar | |
| 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 | |
| charge_type | varchar | |
| list_price | decimal | |
| 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 | |
| conversion_rate_to_corporate | decimal |
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_contact.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_contact.contact_hk → hub_contact (many-to-one)
- lnk_opportunity_owner.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_owner.user_hk → hub_user (many-to-one)
- lnk_case_account.case_hk → hub_case (many-to-one)
- lnk_case_account.account_hk → hub_account (many-to-one)
- lnk_case_contact.case_hk → hub_case (many-to-one)
- lnk_case_contact.contact_hk → hub_contact (many-to-one)
- lnk_campaign_member.campaign_hk → hub_campaign (many-to-one)
- lnk_campaign_member.contact_hk → hub_contact (many-to-one)
- lnk_campaign_member.lead_hk → hub_lead (many-to-one)
- lnk_lead_conversion.lead_hk → hub_lead (many-to-one)
- lnk_lead_conversion.account_hk → hub_account (many-to-one)
- lnk_lead_conversion.contact_hk → hub_contact (many-to-one)
- lnk_lead_conversion.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_line.opportunity_hk → hub_opportunity (many-to-one)
- lnk_opportunity_line.product_hk → hub_product (many-to-one)
- lnk_campaign_influence.opportunity_hk → hub_opportunity (many-to-one)
- lnk_campaign_influence.campaign_hk → hub_campaign (many-to-one)
- lnk_campaign_influence.contact_hk → hub_contact (many-to-one)
- sat_account_details.account_hk → hub_account (many-to-one)
- sat_contact_details.contact_hk → hub_contact (many-to-one)
- sat_lead_details.lead_hk → hub_lead (many-to-one)
- sat_opportunity_details.opportunity_hk → hub_opportunity (many-to-one)
- sat_opportunity_status.opportunity_hk → hub_opportunity (many-to-one)
- sat_case_details.case_hk → hub_case (many-to-one)
- sat_case_status.case_hk → hub_case (many-to-one)
- sat_campaign_details.campaign_hk → hub_campaign (many-to-one)
- sat_user_details.user_hk → hub_user (many-to-one)
- sat_product_details.product_hk → hub_product (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.