Salesforce

← All suppliers

Copy formulas into the KPI Definition Card, then adapt grain and filters.

Example

Won revenue

How much Opportunity.Amount was won in the period?

SUM(Opportunity.Amount) WHERE Opportunity.IsWon = true AND Opportunity.CloseDate IN period

Grain: Opportunity
Fields: Opportunity.Amount, Opportunity.IsWon, Opportunity.CloseDate, Opportunity.CurrencyIsoCode
Dimensions: owner, region, stage

CloseDate as period filter; watch multi-currency.

Adapt: Clarify refunds, partial wins, booking vs close date.

Example

Open pipeline amount

How much open opportunity value is in the funnel?

SUM(Opportunity.Amount) WHERE Opportunity.IsClosed = false

Grain: Opportunity
Fields: Opportunity.Amount, Opportunity.IsClosed, Opportunity.StageName
Dimensions: owner, stage, source

IsClosed = false; maintain stage allowlist.

Adapt: Optionally also filter ForecastCategory.

Weighted pipeline

What is the probability-weighted pipeline value?

SUM(Opportunity.Amount * Opportunity.Probability / 100) WHERE Opportunity.IsClosed = false

Grain: Opportunity
Fields: Opportunity.Amount, Opportunity.Probability, Opportunity.IsClosed
Dimensions: owner, stage

Probability comes from stage — check custom overrides.

Adapt: Prefer firm probability table if it exists.

Win rate

What share of closed opps is IsWon?

COUNT(Opportunity WHERE IsWon) / COUNT(Opportunity WHERE IsClosed)

Grain: Opportunity
Fields: Opportunity.IsWon, Opportunity.IsClosed, Opportunity.StageName
Dimensions: owner, source

Closed only in denominator; list withdrawn stages.

Adapt: Exclude duplicate/withdrawn from denominator.

Avg won amount

What is the typical amount of won opps?

SUM(Amount WHERE IsWon) / COUNT(IsWon)

Grain: Opportunity
Fields: Opportunity.Amount, Opportunity.IsWon
Dimensions: owner, region

Won only; normalize currency.

Adapt: Lock median vs mean.

Example

Customers with won opp

How many accounts have at least one won opp in the period?

COUNT(DISTINCT Opportunity.AccountId) WHERE Opportunity.IsWon = true AND Opportunity.CloseDate IN period

Grain: Account
Fields: Opportunity.AccountId, Opportunity.IsWon, Opportunity.CloseDate
Dimensions: region, owner

AccountId required; clarify person accounts.

Adapt: Slice new vs existing customers separately.

New accounts

How many accounts were created in the period?

COUNT(*) WHERE Account.CreatedDate IN period

Grain: Account
Fields: Account.Id, Account.CreatedDate, Account.OwnerId
Dimensions: region, owner

CreatedDate ≠ first won opp.

Adapt: Optionally separate lead-converted accounts.

Example

Won revenue by owner

How much won amount per salesperson (owner)?

SUM(Opportunity.Amount) WHERE IsWon GROUP BY Opportunity.OwnerId

Grain: Opportunity × owner
Fields: Opportunity.Amount, Opportunity.IsWon, Opportunity.OwnerId, User.Name
Dimensions: owner, region

OwnerId → User; model team roles separately.

Adapt: Quota/attainment needs custom/quota object.

Open opps per owner

How many open opportunities does each owner have?

COUNT(*) WHERE IsClosed = false GROUP BY OwnerId

Grain: Opportunity × owner
Fields: Opportunity.IsClosed, Opportunity.OwnerId, Opportunity.StageName
Dimensions: owner, stage

Workload view — not revenue.

Adapt: Optionally band by amount.

Lead conversion rate

What share of leads is IsConverted?

COUNT(Lead WHERE IsConverted) / COUNT(Lead)

Grain: Lead
Fields: Lead.IsConverted, Lead.ConvertedDate, Lead.LeadSource
Dimensions: source, owner

Use ConvertedDate for period filters.

Adapt: Exclude disqualified leads from denominator.

Sales cycle days (won)

How long is CreatedDate → CloseDate for won?

AVG(Opportunity.CloseDate - Opportunity.CreatedDate) WHERE Opportunity.IsWon = true

Grain: Opportunity
Fields: Opportunity.CreatedDate, Opportunity.CloseDate, Opportunity.IsWon
Dimensions: owner, stage

Date diff in warehouse days; clarify TZ.

Adapt: Median often more robust than AVG.

Won line total

What is the sum of OpportunityLineItem.TotalPrice for won opps?

SUM(OpportunityLineItem.TotalPrice) WHERE parent Opportunity.IsWon = true AND CloseDate IN period

Grain: OpportunityLineItem
Fields: OpportunityLineItem.TotalPrice, OpportunityLineItem.OpportunityId, Opportunity.IsWon, Opportunity.CloseDate
Dimensions: owner, region

Line vs header Amount can diverge.

Adapt: Product2 dimension for mix analysis.

ARR (custom / contract)

What is annualized recurring value?

SUM(annualized_recurring_value) WHERE contract_active_in_period

Grain: Contract / subscription line
Fields: Contract / custom ARR fields, OpportunityLineItem (if subscription)
Dimensions: owner, region

No standard ARR — check custom/CPQ/contract.

Adapt: Do not misuse Opportunity.Amount as ARR.

Formula overview

Same formulas as a table — copy into KPI cards or SQL drafts.

Label	Formula	Grain	Fields
Won revenue	`SUM(Opportunity.Amount) WHERE Opportunity.IsWon = true AND Opportunity.CloseDate IN period`	Opportunity	Opportunity.Amount, Opportunity.IsWon, Opportunity.CloseDate, Opportunity.CurrencyIsoCode
Open pipeline amount	`SUM(Opportunity.Amount) WHERE Opportunity.IsClosed = false`	Opportunity	Opportunity.Amount, Opportunity.IsClosed, Opportunity.StageName
Weighted pipeline	`SUM(Opportunity.Amount * Opportunity.Probability / 100) WHERE Opportunity.IsClosed = false`	Opportunity	Opportunity.Amount, Opportunity.Probability, Opportunity.IsClosed
Win rate	`COUNT(Opportunity WHERE IsWon) / COUNT(Opportunity WHERE IsClosed)`	Opportunity	Opportunity.IsWon, Opportunity.IsClosed, Opportunity.StageName
Avg won amount	`SUM(Amount WHERE IsWon) / COUNT(IsWon)`	Opportunity	Opportunity.Amount, Opportunity.IsWon
Customers with won opp	`COUNT(DISTINCT Opportunity.AccountId) WHERE Opportunity.IsWon = true AND Opportunity.CloseDate IN period`	Account	Opportunity.AccountId, Opportunity.IsWon, Opportunity.CloseDate
New accounts	`COUNT(*) WHERE Account.CreatedDate IN period`	Account	Account.Id, Account.CreatedDate, Account.OwnerId
Won revenue by owner	`SUM(Opportunity.Amount) WHERE IsWon GROUP BY Opportunity.OwnerId`	Opportunity × owner	Opportunity.Amount, Opportunity.IsWon, Opportunity.OwnerId, User.Name
Open opps per owner	`COUNT(*) WHERE IsClosed = false GROUP BY OwnerId`	Opportunity × owner	Opportunity.IsClosed, Opportunity.OwnerId, Opportunity.StageName
Lead conversion rate	`COUNT(Lead WHERE IsConverted) / COUNT(Lead)`	Lead	Lead.IsConverted, Lead.ConvertedDate, Lead.LeadSource
Sales cycle days (won)	`AVG(Opportunity.CloseDate - Opportunity.CreatedDate) WHERE Opportunity.IsWon = true`	Opportunity	Opportunity.CreatedDate, Opportunity.CloseDate, Opportunity.IsWon
Won line total	`SUM(OpportunityLineItem.TotalPrice) WHERE parent Opportunity.IsWon = true AND CloseDate IN period`	OpportunityLineItem	OpportunityLineItem.TotalPrice, OpportunityLineItem.OpportunityId, Opportunity.IsWon, Opportunity.CloseDate
ARR (custom / contract)	`SUM(annualized_recurring_value) WHERE contract_active_in_period`	Contract / subscription line	Contract / custom ARR fields, OpportunityLineItem (if subscription)

Id	Label	Grain	Notes
`owner`	Owner / team	User → role → team	Align owner mapping across CRMs when both systems run.
`stage`	Stage / pipeline	Stage name + pipeline	Stage labels are firm-specific — lock won/lost/open meanings.
`region`	Region / country	Country / region	Consciously choose billing vs shipping country.
`source`	Lead source / channel	Source value	Harmonize picklist values before the mart.

PII hotspots

Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.

Entity	Fields	Classification	Stage treatment	Treatment
Contact	`Email, Phone, MobilePhone, FirstName, LastName, MailingStreet, MailingCity, MailingPostalCode, Birthdate`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Direct identifiers — tag PII, keep match keys separate.
Lead	`Email, Phone, MobilePhone, FirstName, LastName, Street, City, PostalCode, Company`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Treat like Contact; inventory web-to-lead fields too.
Account	`BillingStreet, BillingCity, BillingPostalCode, ShippingStreet, ShippingCity, PersonEmail, PersonMobilePhone`	Quasi	RAW: tag; Curated: generalize (region vs postal); Mart: aggregated	B2B addresses often quasi-ID; person accounts = direct PII.
User	`Email, Name, Phone, MobilePhone, Username, FederationIdentifier`	Workforce	RAW: workforce policy; Curated: separate from customer PII; Mart: aggregates only	Workforce PII — separate retention and access vs customers.
Case	`SuppliedEmail, SuppliedName, SuppliedPhone, Description, Subject`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Free text and web-case fields — often unchecked PII.
Task	`Subject, Description, WhoId`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Activity bodies and polymorphic WhoId — PII path.
Event	`Subject, Description, WhoId, Location`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Meeting notes and location can be personal.
Opportunity	`Description, NextStep`	Sensitive	RAW: rarely load; Curated: drop/redact; Mart: never	Free-text risk — do not push unchecked into marts.
CampaignMember	`Email, FirstName, LastName, ContactId, LeadId`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Marketing join to Contact/Lead — second PII surface.
Custom / External Id	`Email custom fields, External Id custom fields, National ID customs`	Direct	RAW: restrict access; Curated: hash/tokenize; Mart: no cleartext	Inventory all custom fields with email/external id.

DSDR discovery

Where personal data can hide beyond obvious contact fields — warehouse copies included.

Focus	Notes
Match keys	Email, phone, Salesforce Id, external Id customs, MobilePhone.
Primary objects	Contact, Lead, person Account — plus any object with email/phone fields.
Warehouse copies	Landing/curated tables, activation/reverse-ETL, BI extracts, shared stages.
History objects	ContactHistory, LeadHistory, AccountHistory — old values remain PII.
Email & content bodies	EmailMessage, ContentNote, ContentVersion — bodies and headers.
Web-to-lead / forms	Lead-source forms and custom form fields without schema review.
Integration vs end user	Integration users see more fields — FLS ≠ warehouse policy.
Shared activities	Task/Event WhoId polymorphic — Contact and Lead in one column.
Sandbox & refresh	Do not mix sandbox/partial copies with prod marts.
Managed packages	Package objects with hidden PII fields — describe + sample.

Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.

Data quality guides 8

Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.

High Won without Amount

Problem: When opportunities close as Won with blank or zero Amount, revenue and forecast reports look too low or random. Leadership sees false performance — often noticed months later.
Prevent: Make Amount required before Closed Won (validation rule + required on layout/path). No sales bypass except a documented integration user. Regularly send a “Won without Amount” report to ops.
Staff must know: Sales: Won without an amount is not done. Enter the amount in deal currency before Closed Won. If unsure → manager; do not leave blank.
Checks: COUNT(*) WHERE IsWon AND (Amount IS NULL OR Amount = 0)
Fix in source: Validation rule: IsWon → Amount required; page layout required.
Warehouse safety net: Flag in Curated; exclude from won_revenue or document impute policy.

High CloseDate before CreatedDate

Problem: CloseDate before CreatedDate or missing CloseDate on closed stages breaks cycle time and period comparisons. Deals land in the wrong month bucket.
Prevent: On close, set CloseDate automatically (flow) and validate CloseDate ≥ CreatedDate. Historical corrections only via an admin process.
Staff must know: Sales: CloseDate = real close day. Never manually “fix” CreatedDate.
Checks: CloseDate < CreatedDate OR CloseDate IS NULL AND IsClosed
Fix in source: Validation rule + flow when stage=Closed.
Warehouse safety net: Quarantine row; set cycle time null.

Medium CurrencyIsoCode missing

Problem: Without CurrencyIsoCode, multi-currency amounts get summed as one currency — revenue and pipeline become wrong.
Prevent: Corporate currency default; require CurrencyIsoCode on Opportunity. Integrations always write the ISO code.
Staff must know: Sales: Choose deal currency deliberately. Finance needs the ISO code.
Checks: CurrencyIsoCode IS NULL (when multi-currency enabled)
Fix in source: Corporate currency default; required on Opportunity.
Warehouse safety net: Join FX table; mark missing as corporate currency.

Medium Owner inactive

Problem: Open opps on deactivated users disappear from team reports or stay orphaned. Routing and quota break.
Prevent: Deactivation checklist: transfer records, queues, reassign rules. Never disable without owner handoff.
Staff must know: Managers/ops: Before offboarding reassign open opps/leads. Sales: update Owner on team changes.
Checks: OwnerId → User.IsActive = false
Fix in source: Reassign rules / queue; owner change process.
Warehouse safety net: Map to team fallback dim; flag stale_owner.

High Stage vs IsClosed/IsWon

Problem: Stage “Closed Won” but IsWon=false (or reverse) → win rate and pipeline differ by tool.
Prevent: Maintain sales processes and stage picklists centrally; no ad-hoc stages. Audit data after process changes.
Staff must know: Sales: Use only approved stages. New stage values only via admin.
Checks: Stage in Closed Won but IsWon=false (and reverse)
Fix in source: Maintain stage picklist + sales process; no ad-hoc stages.
Warehouse safety net: Canonical stage map in Curated; derive IsWon from map.

Medium Opportunity without Account

Problem: Opps without Account cannot be sliced by customer/region. Customer counts have holes.
Prevent: Require Account on Opportunity; lead convert always Account+Contact.
Staff must know: Sales: Every deal needs an Account. New customer → create Account first or convert Lead cleanly.
Checks: AccountId IS NULL AND IsClosed = false (or always)
Fix in source: Require Account on Opp; always convert lead with Account.
Warehouse safety net: Unknown-account dim; exclude from customer metrics.

Low Probability outside 0–100

Problem: Probability outside 0–100 makes weighted pipeline unusable.
Prevent: Stage probability only via sales process; review custom overrides.
Staff must know: Sales: Probability comes from stage — do not freely type “99%” without process.
Checks: Probability < 0 OR Probability > 100
Fix in source: Check stage probability overrides.
Warehouse safety net: CLAMP(0,100) in Curated + alert.

Medium Line total vs header Amount

Problem: Product lines and Opportunity.Amount diverge → product-mix and header revenue contradict.
Prevent: Require products on Closed Won; sync Amount from lines (flow/CPQ). Diff report.
Staff must know: Sales: Maintain lines on product deals; do not guess header when lines exist.
Checks: ABS(SUM(lines)-Amount) > tolerance on Won
Fix in source: Products required on Closed Won; sync Amount from lines.
Warehouse safety net: Two measures (header vs line) and diff KPI.

MDM / duplicates 5

Account Duplicate accounts

Match keys: Website, BillingPostalCode + Name, External Id, DUNS (custom)
Prevent in source: Matching rules + duplicate rules (block/alert) on Account create/edit.
Resolve in warehouse: Fuzzy match in Curated; golden_record_id; merge map table.
Survivorship: Latest activity / highest ARR / master with External Id wins.

Contact Duplicate contacts

Match keys: Email, MobilePhone, AccountId + LastName, External Id
Prevent in source: Enable standard Contact matching rule; unique email where possible.
Resolve in warehouse: Person-key hash; link duplicates to master_contact_id.
Survivorship: Verified email > phone; most active contact on account.

Lead Duplicate leads

Match keys: Email, Company + LastName, MobilePhone
Prevent in source: Lead duplicate rules; convert check against Contact/Account.
Resolve in warehouse: Pre-convert dedupe job; reject orphan converted leads.
Survivorship: Converted lead points to surviving Contact.

Person Account Person Account vs Contact

Match keys: PersonEmail, PersonContactId
Prevent in source: Person Accounts only with a clear B2C model; else Contact+Account.
Resolve in warehouse: Separate grain in dims; never mix with B2B Account counts.
Survivorship: One golden customer per PersonEmail.

Account/Contact External Id as MDM anchor

Match keys: External Id custom (unique case-sensitive)
Prevent in source: External Id unique + case sensitive; integration always writes same key.
Resolve in warehouse: Warehouse natural key = External Id; SF Id only as source_id.
Survivorship: External Id beats SF Id on merges.

Product settings to improve 7

Setup → Duplicate Management Matching rules + duplicate rules for Account/Contact/Lead

Why: Prevents duplicates at source — cheaper than warehouse MDM.
How: Enable standard rules; action = Block on create for exact email matches.

Setup → Object Manager → Validation Rules Won → Amount/CloseDate/AccountId required

Why: Closes KPI-critical gaps before data lands.
How: Test rules per sales process; document bypass only for integration users.

Page Layouts / Dynamic Forms Required fields on Opportunity/Contact layouts

Why: UI pressure complements validation rules.
How: Closed stages: Amount, CloseDate, Forecast Category visible and required.

Sales Processes / Path Consistent stage values and path guidance

Why: Prevents stage sprawl and IsWon mismatch.
How: One sales process per record type; path with key fields.

Object Manager → Fields → External Id Unique External Id for Account and Contact

Why: Stable MDM and integration key.
How: Case sensitive unique; only integration may write.

Person Accounts Person Accounts only with clear B2C

Why: Wrong model creates duplicate customer grains.
How: Enable only with a data model guide; else Contact+Account.

Setup → Lead / Case / Opp Assignment Reassign on user deactivation

Why: Avoids stale owners in measures.
How: Deactivation checklist: transfer records + queues.

Metadata: what exists & how to get it 4

Source-native catalogs for schema, picklists/properties and access paths — not a full dump.

Object & field metadata

Where in product: Setup → Object Manager → Fields & Relationships / Record Types / Page Layouts
How to access: Browse UI; for automation: Tooling/Metadata API (EntityDefinition, FieldDefinition) or Workbench describe.
Use for: Load allowlist, PII inventory, validation rules, stage/picklist DQ.
Watchouts: FLS and layouts ≠ warehouse policy. Managed package fields are easy to miss.

Picklists / global value sets

Where in product: Object Manager → field → Picklist Values; Setup → Picklist Value Sets
How to access: UI or Metadata API (CustomField, GlobalValueSet). Sales Process controls available Opportunity stages.
Use for: Stage maps, won/lost definition, dimension harmonization.
Watchouts: Record types + sales processes hide values — describe alone is not enough.

Matching / duplicate rules

Where in product: Setup → Duplicate Management
How to access: UI: matching rules + duplicate rules. Metadata API: MatchingRule, DuplicateRule.
Use for: MDM prevention; which keys the org actually matches.
Watchouts: Alert vs block; integration users often exempt — document.

API describe (Partner/REST)

Where in product: REST `/sobjects/{object}/describe`, SOAP describeSObject
How to access: Run describe with integration user; store result as catalog in landing.
Use for: Automated field inventory for sync and DQ.
Watchouts: Describe sees what the user can access — not “the whole org”. Check permission sets.

Object overview with short descriptions and load guidance — not a full schema dump.

Table	Description	Grain	Load
`account` Account	Company or customer master data — dimension and hierarchy root.	One company / customer record	Load
`contact` Contact	People on an account — PII-heavy, central for DSDR.	One person	Load
`lead` Lead	Pre-qualified prospects before conversion — funnel top.	One lead	Optional
`opportunity` Opportunity	Deal header for pipeline, wins and revenue KPIs.	One deal	Load
`opportunitylineitem` OpportunityLineItem	Product lines on a deal — only when product mix matters.	One product line	Optional
`user` User	Salesforce users for owner/team dimension (without full profile dump).	One Salesforce user	Load
`campaign` Campaign	Marketing campaigns for attribution — load optionally.	One campaign	Optional
`case` Case	Support tickets — load only for service analytics.	One case	Optional
`task` Task	Activities — often noisy and PII-heavy; only with a clear consumer.	One activity	Optional

Most important fields to load early — not a full schema dump.

Entity	Field	Role	Why
Opportunity	`Opportunity.Id`	Key	Join / lineage
Opportunity	`Opportunity.Amount`	Measure	Revenue / pipeline
Opportunity	`Opportunity.IsWon`	Measure	Won filter
Opportunity	`Opportunity.IsClosed`	Measure	Open vs closed
Opportunity	`Opportunity.CloseDate`	Measure	Period grain
Opportunity	`Opportunity.Probability`	Measure	Weighted pipeline
Opportunity	`Opportunity.Type`	Dimension	Deal type
Opportunity	`Opportunity.CurrencyIsoCode`	Measure	Multi-currency
Opportunity	`Opportunity.StageName`	Dimension	Stage dimension
Opportunity	`Opportunity.OwnerId`	Dimension	Owner
Opportunity	`Opportunity.LeadSource`	Dimension	Channel
Opportunity	`Opportunity.AccountId`	Dimension	Account join
Opportunity	`Opportunity.CreatedDate`	Key	Cycle time / CDC
Opportunity	`Opportunity.LastModifiedDate`	Key	Incremental sync
Opportunity	`Opportunity.SystemModstamp`	Key	CDC / watermark
Opportunity	`Opportunity.IsDeleted`	Key	Soft delete / CDC
OpportunityLineItem	`OpportunityLineItem.OpportunityId`	Key	Parent join
OpportunityLineItem	`OpportunityLineItem.Quantity`	Measure	Quantity
OpportunityLineItem	`OpportunityLineItem.TotalPrice`	Measure	Line revenue
OpportunityLineItem	`OpportunityLineItem.Product2Id`	Dimension	Product
Account	`Account.Id`	Key	Join
Account	`Account.Name`	Dimension	Account label
Account	`Account.Type`	Dimension	Account type
Account	`Account.Industry`	Dimension	Industry
Account	`Account.BillingCountry`	Dimension	Region
Account	`Account.ParentId`	Dimension	Hierarchy
Account	`Account.OwnerId`	Dimension	Account owner
Contact	`Contact.Id`	Key	Join
Contact	`Contact.AccountId`	Dimension	Account join
Contact	`Contact.Email`	PII	Person key / PII
Contact	`Contact.Phone`	PII	PII
Contact	`Contact.MobilePhone`	PII	PII
Contact	`Contact.FirstName`	PII	PII
Contact	`Contact.LastName`	PII	PII
Contact	`Contact.Title`	Dimension	Job title
Lead	`Lead.Email`	PII	Person key / PII
Lead	`Lead.Status`	Dimension	Lead status
Lead	`Lead.IsConverted`	Measure	Conversion
User	`User.Id`	Key	Owner join
User	`User.Name`	Dimension	Owner label
User	`User.IsActive`	Dimension	Active owners
User	`User.UserRoleId`	Dimension	Team / role
Campaign	`Campaign.Id`	Key	Attribution join
Campaign	`Campaign.Name`	Dimension	Campaign label
Campaign	`Campaign.Type`	Dimension	Campaign type
Case	`Case.Id`	Key	Join
Case	`Case.Status`	Dimension	Case status
Case	`Case.Origin`	Dimension	Channel

System and noise objects/fields you typically should not sync into the warehouse by default.

Skip tables / objects

Table	Category	Why
`FeedItem / FeedComment (Chatter)`	System	Collaboration noise and unstructured PII.
`ContentVersion / Attachment (bodies)`	System	Binaries — expensive, little analytics value.
`ContentNote`	System	Free-text notes with PII — only with a use case.
`SetupAuditTrail`	System	Admin audit — not for business KPIs.
`LoginHistory / LoginGeo`	System	Login telemetry — security topic.
`EmailMessage (ohne Bedarf)`	System	Email bodies/headers — PII and volume.
`*History (vor SCD2-Bedarf)`	System	Field history only when Type-2 is needed.
`ApexLog / AsyncApexJob`	System	Platform runtime — not an analytics load.
`VoiceCall (bodies/recordings)`	System	Recordings and transcripts — highly sensitive.
`EventRelation (ohne Bedarf)`	System	Attendee lists — often unnecessary for marts.

Skip fields / content

Description / Body free-text (bulk) — PII and noise — selectively or not at all.
Unused custom fields (bulk sync all) — Expands DSDR surface and cost.
UI layout / page metadata — Not analytical.
Formula-only fields without consumer — Derived — recompute in Curated.
Chatter feed bodies — Unstructured PII.
Attachment binary columns — Cost without KPI value.
Debug / unused managed package fields — Schema ballast.

Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.

Landing (RAW)

RAW opportunity landing

Source-shaped landing — tag PII columns, restrict access. Not SOQL.

-- Warehouse-neutral RAW example (adapt dialect)
CREATE TABLE raw_opportunity (
  opportunity_id   VARCHAR,
  account_id       VARCHAR,
  owner_id         VARCHAR,
  amount           DECIMAL(18,2),
  currency_code    VARCHAR,
  stage_name       VARCHAR,
  is_won           BOOLEAN,
  is_closed        BOOLEAN,
  probability      DECIMAL(5,2),
  close_date       DATE,
  created_at       TIMESTAMP,
  modified_at      TIMESTAMP,
  is_deleted       BOOLEAN,
  _loaded_at       TIMESTAMP
);
-- Load 1:1 from source extract; do not drop PII policy tags here.

RAW account + user landing

Account addresses and user email are PII/workforce — separate grants.

CREATE TABLE raw_account (
  account_id VARCHAR,
  name VARCHAR,
  billing_country VARCHAR,
  billing_postal_code VARCHAR, -- quasi/direct PII
  owner_id VARCHAR,
  created_at TIMESTAMP,
  modified_at TIMESTAMP,
  is_deleted BOOLEAN,
  _loaded_at TIMESTAMP
);

CREATE TABLE raw_user (
  user_id VARCHAR,
  name VARCHAR,
  email VARCHAR, -- workforce PII
  is_active BOOLEAN,
  _loaded_at TIMESTAMP
);

Curated

Curated fact opportunity

Conformed fact — no cleartext contact PII; keys + measures only.

CREATE TABLE curated_fct_opportunity AS
SELECT
  o.opportunity_id,
  o.account_id,
  o.owner_id,
  o.amount,
  o.currency_code,
  o.stage_name,
  o.is_won,
  o.is_closed,
  o.probability,
  o.close_date,
  o.created_at,
  (o.amount * o.probability / 100.0) AS weighted_amount
FROM raw_opportunity o
WHERE COALESCE(o.is_deleted, FALSE) = FALSE;
-- Join dims in marts; keep RAW grants tighter than curated.

Curated dim account / owner

Dims without unnecessary address cleartext; owner without email in curated marts.

CREATE TABLE curated_dim_account AS
SELECT
  account_id,
  name,
  billing_country AS region,
  owner_id
  -- omit billing_postal_code from default curated marts
FROM raw_account
WHERE COALESCE(is_deleted, FALSE) = FALSE;

CREATE TABLE curated_dim_owner AS
SELECT
  user_id AS owner_id,
  name AS owner_name
  -- omit email from default analytics dims
FROM raw_user
WHERE is_active = TRUE;

Curated measure SELECTs

Example calculations — copy and adapt period filters.

-- Won revenue in period
SELECT SUM(amount) AS won_revenue
FROM curated_fct_opportunity
WHERE is_won = TRUE
  AND close_date BETWEEN :period_start AND :period_end;

-- Customers with won opp
SELECT COUNT(DISTINCT account_id) AS customers_with_won
FROM curated_fct_opportunity
WHERE is_won = TRUE
  AND close_date BETWEEN :period_start AND :period_end;

-- Won revenue by owner
SELECT owner_id, SUM(amount) AS won_revenue
FROM curated_fct_opportunity
WHERE is_won = TRUE
  AND close_date BETWEEN :period_start AND :period_end
GROUP BY owner_id;

Won revenue

Open pipeline amount

Weighted pipeline

Win rate

Avg won amount

Customers with won opp

New accounts

Won revenue by owner

Open opps per owner

Lead conversion rate

Sales cycle days (won)

Won line total

ARR (custom / contract)

Formula overview

PII hotspots

DSDR discovery

Skip tables / objects

Skip fields / content

Tools

Playbooks