Salesforce Core Objects Source Replica data model
Faithful 3NF replica of core Salesforce objects with the native 18-char Id as the business key and full platform/connector metadata (CreatedDate, SystemModstamp, IsDeleted, audit Ids, connector sync time) for incremental, soft-delete-aware loading. Mirrors how Fivetran/Airbyte land Salesforce; serves as the raw schema beneath the Salesforce analytics marts.
A normalized (3NF) landing replica of the core Salesforce Sales and Service Cloud objects exactly as an ELT connector (Fivetran/Airbyte/Stitch) replicates them into the warehouse: User, Account, Contact, Lead, Opportunity, OpportunityLineItem, Product2, Pricebook2, PricebookEntry, Campaign, CampaignMember, Case, Task, Event, RecordType, and OpportunityFieldHistory — the raw layer that staging models and marts build on. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 16
- tables
- 284
- columns
- 35
- relationships
- 15
- 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
Native 18-char Id retained as the business key on every table
ELT connectors replicate Salesforce keyed on the 18-char Id; keeping it as the business key (alongside a warehouse surrogate) preserves lineage to the source org and lets every relationship resolve on the same key the API exposes.
SystemModstamp and IsDeleted on every object
Incremental syncs filter on SystemModstamp and must honour the recycle bin via IsDeleted; carrying both on every replicated table is what makes the landing layer reloadable and soft-delete-aware.
Polymorphic WhoId/WhatId resolved to typed FKs
Task and Event use polymorphic WhoId/WhatId; the replica exposes the common targets as explicit nullable FKs (Contact, Account) so activity can be joined without parsing key prefixes, while rarer targets stay addressable by Id.
OpportunityFieldHistory kept as an append-only table
Salesforce overwrites Opportunity fields in place; replicating OpportunityFieldHistory preserves stage-entry timestamps and close-date changes that the marts need to reconstruct deal velocity.
Self-references modelled by Id, not FK
Account.ParentId, Campaign.ParentId, and User.ManagerId are kept as raw Id columns rather than enforced FKs to avoid load-order cycles, matching how connectors land hierarchical references.
Tables in the Salesforce Core Objects Source Replica 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.
Tables (15)
sf_user
Grain: one row per User
| Column | Type | Keys |
|---|---|---|
| user_key | bigint | PK |
| salesforce_id | varchar | BK |
| username | varchar | PII |
| name | varchar | PII |
| varchar | PII | |
| user_role_name | varchar | |
| manager_user_id | varchar | |
| profile_name | varchar | |
| department | varchar | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_account
Grain: one row per Account
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| salesforce_id | varchar | BK |
| account_name | varchar | |
| account_type | varchar | |
| industry | varchar | |
| annual_revenue | decimal | |
| number_of_employees | integer | |
| phone | varchar | PII |
| website | varchar | |
| billing_country | varchar | |
| billing_state | varchar | |
| parent_account_id | varchar | |
| owner_user_key | bigint | |
| record_type_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_contact
Grain: one row per Contact
| Column | Type | Keys |
|---|---|---|
| contact_key | bigint | PK |
| salesforce_id | varchar | BK |
| account_key | bigint | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| title | varchar | |
| department | varchar | |
| mailing_country | varchar | |
| lead_source | varchar | |
| has_opted_out_of_email | boolean | |
| owner_user_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_lead
Grain: one row per Lead
| Column | Type | Keys |
|---|---|---|
| lead_key | bigint | PK |
| salesforce_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| company | varchar | |
| varchar | PII | |
| phone | varchar | PII |
| title | varchar | |
| lead_source | varchar | |
| lead_status | varchar | |
| rating | varchar | |
| industry | varchar | |
| is_converted | boolean | |
| converted_date | date | |
| converted_account_key | bigint | |
| converted_contact_key | bigint | |
| converted_opportunity_key | bigint | |
| owner_user_key | bigint | |
| record_type_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_product
Grain: one row per Product2
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| salesforce_id | varchar | BK |
| product_code | varchar | |
| product_name | varchar | |
| product_family | varchar | |
| description | varchar | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_pricebook
Grain: one row per Pricebook2
| Column | Type | Keys |
|---|---|---|
| pricebook_key | bigint | PK |
| salesforce_id | varchar | BK |
| pricebook_name | varchar | |
| description | varchar | |
| is_standard | boolean | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_pricebook_entry
Grain: one row per PricebookEntry
| Column | Type | Keys |
|---|---|---|
| pricebook_entry_key | bigint | PK |
| salesforce_id | varchar | BK |
| pricebook_key | bigint | |
| product_key | bigint | |
| unit_price | decimal | |
| currency_iso_code | varchar | |
| use_standard_price | boolean | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_opportunity
Grain: one row per Opportunity
| Column | Type | Keys |
|---|---|---|
| opportunity_key | bigint | PK |
| salesforce_id | varchar | BK |
| opportunity_name | varchar | |
| account_key | bigint | |
| stage_name | varchar | |
| amount | decimal | |
| probability | decimal | |
| close_date | date | |
| opportunity_type | varchar | |
| lead_source | varchar | |
| forecast_category | varchar | |
| is_closed | boolean | |
| is_won | boolean | |
| campaign_key | bigint | |
| pricebook_key | bigint | |
| owner_user_key | bigint | |
| record_type_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_opportunity_line_item
Grain: one row per OpportunityLineItem
| Column | Type | Keys |
|---|---|---|
| opportunity_line_item_key | bigint | PK |
| salesforce_id | varchar | BK |
| opportunity_key | bigint | |
| pricebook_entry_key | bigint | |
| product_key | bigint | |
| quantity | decimal | |
| unit_price | decimal | |
| total_price | decimal | |
| list_price | decimal | |
| discount | decimal | |
| service_date | date | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_campaign
Grain: one row per Campaign
| Column | Type | Keys |
|---|---|---|
| campaign_key | bigint | PK |
| salesforce_id | varchar | BK |
| campaign_name | varchar | |
| campaign_type | varchar | |
| status | varchar | |
| start_date | date | |
| end_date | date | |
| budgeted_cost | decimal | |
| actual_cost | decimal | |
| expected_revenue | decimal | |
| number_of_leads | integer | |
| parent_campaign_id | varchar | |
| owner_user_key | bigint | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_campaign_member
Grain: one row per CampaignMember
| Column | Type | Keys |
|---|---|---|
| campaign_member_key | bigint | PK |
| salesforce_id | varchar | BK |
| campaign_key | bigint | |
| lead_key | bigint | |
| contact_key | bigint | |
| member_status | varchar | |
| has_responded | boolean | |
| first_responded_date | date | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_case
Grain: one row per Case
| Column | Type | Keys |
|---|---|---|
| case_key | bigint | PK |
| salesforce_id | varchar | BK |
| case_number | varchar | |
| account_key | bigint | |
| contact_key | bigint | |
| subject | varchar | PII |
| status | varchar | |
| priority | varchar | |
| origin | varchar | |
| case_type | varchar | |
| reason | varchar | |
| is_closed | boolean | |
| is_escalated | boolean | |
| closed_date | timestamp | |
| owner_user_key | bigint | |
| record_type_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_task
Grain: one row per Task (logged activity)
| Column | Type | Keys |
|---|---|---|
| task_key | bigint | PK |
| salesforce_id | varchar | BK |
| subject | varchar | PII |
| activity_type | varchar | |
| status | varchar | |
| priority | varchar | |
| activity_date | date | |
| is_closed | boolean | |
| owner_user_key | bigint | |
| who_contact_key | bigint | |
| what_account_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_event
Grain: one row per Event (calendar activity)
| Column | Type | Keys |
|---|---|---|
| event_key | bigint | PK |
| salesforce_id | varchar | BK |
| subject | varchar | PII |
| activity_date | date | |
| start_datetime | timestamp | |
| end_datetime | timestamp | |
| duration_minutes | integer | |
| location | varchar | |
| owner_user_key | bigint | |
| who_contact_key | bigint | |
| what_account_key | bigint | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
sf_opportunity_field_history
Grain: one row per field change on an Opportunity (OpportunityFieldHistory)
Append-only field-change log; reconstructs stage-entry timestamps and close-date slippage.
| Column | Type | Keys |
|---|---|---|
| opportunity_field_history_key | bigint | PK |
| salesforce_id | varchar | BK |
| opportunity_key | bigint | |
| field_name | varchar | |
| old_value | varchar | |
| new_value | varchar | |
| changed_date | timestamp | |
| created_by_user_key | bigint |
Lookup tables (1)
sf_record_type
Grain: one row per RecordType
| Column | Type | Keys |
|---|---|---|
| record_type_key | bigint | PK |
| salesforce_id | varchar | BK |
| sobject_type | varchar | |
| record_type_name | varchar | |
| developer_name | varchar | |
| is_active | boolean | |
| created_date | timestamp | |
| created_by_id | varchar | |
| last_modified_date | timestamp | |
| last_modified_by_id | varchar | |
| system_modstamp | timestamp | |
| is_deleted | boolean | |
| fivetran_synced | timestamp |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- sf_account.owner_user_key → sf_user (many-to-one)
- sf_account.record_type_key → sf_record_type (many-to-one)
- sf_contact.account_key → sf_account (many-to-one)
- sf_contact.owner_user_key → sf_user (many-to-one)
- sf_lead.owner_user_key → sf_user (many-to-one)
- sf_lead.converted_account_key → sf_account (many-to-one)
- sf_lead.converted_contact_key → sf_contact (many-to-one)
- sf_lead.converted_opportunity_key → sf_opportunity (many-to-one)
- sf_lead.record_type_key → sf_record_type (many-to-one)
- sf_pricebook_entry.pricebook_key → sf_pricebook (many-to-one)
- sf_pricebook_entry.product_key → sf_product (many-to-one)
- sf_opportunity.account_key → sf_account (many-to-one)
- sf_opportunity.campaign_key → sf_campaign (many-to-one)
- sf_opportunity.pricebook_key → sf_pricebook (many-to-one)
- sf_opportunity.owner_user_key → sf_user (many-to-one)
- sf_opportunity.record_type_key → sf_record_type (many-to-one)
- sf_opportunity_line_item.opportunity_key → sf_opportunity (many-to-one)
- sf_opportunity_line_item.pricebook_entry_key → sf_pricebook_entry (many-to-one)
- sf_opportunity_line_item.product_key → sf_product (many-to-one)
- sf_campaign.owner_user_key → sf_user (many-to-one)
- sf_campaign_member.campaign_key → sf_campaign (many-to-one)
- sf_campaign_member.lead_key → sf_lead (many-to-one)
- sf_campaign_member.contact_key → sf_contact (many-to-one)
- sf_case.account_key → sf_account (many-to-one)
- sf_case.contact_key → sf_contact (many-to-one)
- sf_case.owner_user_key → sf_user (many-to-one)
- sf_case.record_type_key → sf_record_type (many-to-one)
- sf_task.owner_user_key → sf_user (many-to-one)
- sf_task.who_contact_key → sf_contact (many-to-one)
- sf_task.what_account_key → sf_account (many-to-one)
- sf_event.owner_user_key → sf_user (many-to-one)
- sf_event.who_contact_key → sf_contact (many-to-one)
- sf_event.what_account_key → sf_account (many-to-one)
- sf_opportunity_field_history.opportunity_key → sf_opportunity (many-to-one)
- sf_opportunity_field_history.created_by_user_key → sf_user (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.