CRM & eCommerce Modeling Literacy + Cross-System Identity Resolution

1 The Multi-System Identity Problem

ShaDey Inc. operates SAP for order-to-cash, Salesforce for account management, Shopify for D2C eCommerce, and a legacy Magento platform for B2B. A single customer (Elena Rodriguez) exists across all four systems with different identifiers, attributes, and hierarchies. Each system has mastered only its own transactional truth. None sees the complete customer.

One person = 4 system identities. Without identity resolution, reporting shows "4 separate customers" with divergent lifetime value, purchase patterns, and credit risk. Analytics and CRM campaigns execute against fragmented data. Fulfillment and customer service lack a unified view.

Elena Rodriguez: The Divergent Identity

System	Customer ID	Name	Email	Phone	Status	Created
SAP KNA1	10045821	Rodriguez, Elena M.	e.rodriguez@corp.com	+1-512-555-0147	Active	2019-03-15
Salesforce	001DR00000xyz	Elena Rodriguez	elena.r@gmail.com	+1-512-555-0147	Active	2021-06-22
Shopify D2C	8847291	Elena M Rodriguez	elena.r@gmail.com	(not captured)	Active	2020-11-03
Magento B2B	comp-45782	E. Rodriguez	eroderiguez@company-x.com	+1-512-555-0149	Inactive	2018-09-10

SAP KNA1 Salesforce Account Salesforce Contact Shopify Customer

Impact without resolution: Reports show 4 distinct customers with $45K, $28K, $12K, and $3K lifetime values respectively, totaling $88K. Reality: Elena is a $88K single customer. Marketing sends 4 separate campaigns. Customer service sees fragmented interaction history. Credit scoring runs 4 times with different conclusions.

2 CRM Entity Ambiguity: Account vs Contact vs Household

Salesforce's data model is fundamentally hierarchical and ambiguous. An "Account" can represent a company (B2B), a household (B2C), or a distribution channel partner. A "Contact" is a person, but its parent Account can change. Understanding CRM identity requires grasping this structural ambiguity and its implications for temporal data quality.

The Rodriguez Family Hierarchy

In ShaDey's Salesforce org, the Rodriguez family is modeled as follows:

Entity Type	Salesforce ID	Name	Parent Account ID	Relationship	Last Modified
Account	001DR00000abc	Rodriguez Family Household	(null)	Parent	2024-01-10
Contact	003DR00000xyz	Elena Rodriguez	001DR00000abc	Household Member (Primary Decision Maker)	2024-02-14
Contact	003DR00000def	Marco Rodriguez	001DR00000abc	Household Member (Secondary)	2024-01-05
Contact	003DR00000ghi	Sofia Rodriguez	001DR00000abc	Household Member (Child)	2023-12-20

Salesforce allows one Contact to have one parent Account. But what happens when Elena moves to a different household or changes companies? Her Account ID in CRM changes (a re-parenting operation). This destroys historical continuity and creates temporal data quality issues.

Re-parenting Scenario: Elena Changes Employers

On 2024-03-01, Elena moves from "Rodriguez Family Household" to "Acme Corp B2B Account":

2024-02-28 09:00 AM

Before Change: Contact.AccountId = 001DR00000abc (Rodriguez Family). 47 related Orders, 23 Opportunities, 156 Activities linked to this account relationship.

2024-03-01 02:15 PM

Update Executed: CRM admin re-parents Elena. Contact.AccountId = 001AC00001234 (Acme Corp). All prior related records become "orphaned" in reporting unless explicit re-linking logic exists.

2024-03-01 02:16 PM

Analytical Impact: "Rodriguez Family Household" reports now show only Marco and Sofia. "Acme Corp" reports now include Elena's historical orders with a retroactive account attribution. Database audit trail shows Contact.AccountId changed; relationship reason may be lost.

2024-03-02 onwards

Truth Ambiguity: Was Elena a customer of "Rodriguez Family" or "Acme Corp"? Depends on query date. Time-slice tables (SCD Type 2) in the warehouse must track both effective_date and updated_at_timestamp to resolve temporal queries.

CRM hierarchies are not immutable dimensions. Account re-parenting, contact re-assignment, and household merges destroy the assumption that a Contact.AccountId is stable. Build SCD Type 2 durable keys and effective-date tracking into any Salesforce dimension extract.

Entity Relationship Diagram: CRM Hierarchy

Account-Contact Hierarchy: One-to-Many Relationship (but Unstable)

3 CRM Soft Deletes & Temporal Instability

Salesforce uses soft delete semantics: deleted records are marked with an IsDeleted flag and can be restored. This creates temporal data quality challenges: a contact deleted on Monday and restored Thursday has no permanent audit trail of the deletion reason. When building a warehouse, soft-deleted records present a paradox: include them in history or exclude them?

Soft Delete Pattern in Salesforce

Salesforce Contact Soft Delete Query

SELECT Id, FirstName, LastName, Email, IsDeleted, LastModifiedDate
FROM Contact
WHERE LastModifiedDate > '2024-01-01'
AND (IsDeleted = true OR IsDeleted = false)
-- includes deleted records; use queryAll() endpoint in Salesforce API
        

A contact with IsDeleted=true is purged from all standard CRM reports and list views, but remains in the database. If that contact is restored 90 days later, no system stores the deletion reason, duration, or who approved restoration. Audit trails are incomplete.

Temporal Instability: Account Merges

ShaDey Inc. discovered two Salesforce accounts for the same legal entity: "Acme Corp (Production)" and "Acme Corp (Test)". A merge was executed on 2024-02-15:

Timestamp	Event	Master ID	Detail ID	Impacted Records
2024-02-15 10:00 AM	Merge initiated	001AC00012345	001AC00067890	1 Account + 23 Contacts + 47 Opportunities
2024-02-15 10:15 AM	Detail account marked IsDeleted=true	001AC00012345 (active)	001AC00067890 (soft-deleted)	23 Contacts re-parented; 47 Opps re-linked
2024-02-15 10:30 AM	Contacts visible in Master account	001AC00012345	(none)	Account now shows all 23 contacts
2024-02-16 06:00 AM	Salesforce daily batch purge	001AC00012345	001AC00067890 (purged from searchable index)	Soft-deleted account no longer queryable via standard UI

Account merge is not a single atomic transaction from a data extraction perspective. It is a sequence of cascading updates: the detail account is soft-deleted, all child contacts are re-parented, all child opportunities are re-linked, and the system maintains no explicit "merge_detail_of" relationship. Historians must infer the merge from transaction timestamps.

Building an Immutable Audit Trail

SCD Type 2 Contact Dimension (Salesforce → Warehouse)

CREATE TABLE dim_salesforce_contact (
  contact_key INT PRIMARY KEY,
  contact_id VARCHAR(18) NOT NULL,  -- Salesforce 18-char ID
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  email VARCHAR(255),
  account_id VARCHAR(18),  -- parent account (mutable)
  is_deleted BOOLEAN,
  effective_date DATE NOT NULL,  -- when this version became true
  end_date DATE,  -- when superseded (null = current)
  extract_timestamp TIMESTAMP NOT NULL,
  UNIQUE(contact_id, effective_date)
) PARTITION BY RANGE (effective_date);
        

Soft Delete Flag Effective Date Account ID (mutable)

4 eCommerce Identity: Guest vs Registered

Shopify and Magento platforms distinguish between guest checkouts and registered customers. A guest checkout creates an order orphaned from any persistent customer record. Cross-device shopping (browse on mobile, buy on desktop) creates session fragmentation. Without explicit linking logic, one person becomes many identities in eCommerce systems.

Elena Rodriguez: Cross-Device Identity Fragmentation

Elena's shopping activity across Shopify D2C from January-March 2024:

Date	Device	IP Address	Session ID	Customer ID	Email (at checkout)	Order ID	Amount
2024-01-08	iPhone (iOS)	203.0.113.45	sess_8hf92k	8847291	elena.r@gmail.com	SHP-1001548	$45.99
2024-01-15	Desktop (Chrome)	203.0.113.125	sess_k2jd9x	(Guest)	elena.rodriguez@corp.com	SHP-1001847	$128.50
2024-02-03	iPad (Safari)	203.0.113.45	sess_oj3k2p	8847291	elena.r@gmail.com	SHP-1002134	$87.25
2024-02-18	Desktop (Firefox)	203.0.113.200	sess_pl8m4w	(Guest)	e.rodriguez@company-x.com	SHP-1002501	$203.00
2024-03-10	iPhone (iOS)	203.0.113.45	sess_x9q1r5	8847291	elena.r@gmail.com	SHP-1003002	$62.18

Order SHP-1001847 and SHP-1002501 have no registered Customer ID (guest checkout). They are orphaned from the Shopify customer record (8847291). Without email-based linking, these orders appear as separate low-value customers ($128.50 and $203.00 each) rather than part of Elena's $526.92 total spend.

Cart → Session → Order Identifier Chain

Shopify Orders: Guest vs Registered Customer

-- Registered customer order
{
  "id": 5678901234,
  "order_number": 1001548,
  "customer": {
    "id": 8847291,
    "email": "elena.r@gmail.com"
  },
  "created_at": "2024-01-08T14:23:45Z"
}

-- Guest checkout order (customer is null)
{
  "id": 5679012345,
  "order_number": 1001847,
  "customer": null,  -- NO CUSTOMER ID
  "email": "elena.rodriguez@corp.com",  -- email is only link
  "created_at": "2024-01-15T09:12:07Z"
}
        

Registered Order Guest Order Email Linkage

Shopify provides a customer_id for registered customers only. Guest orders have customer: null. To unify them, link on email address. But emails are mutable, and a single email can belong to multiple households. Implement probabilistic matching: if email + name + address (fuzzy) match, assume same customer. If only email matches but name diverges, flag for manual review.

5 Cross-System Identity Resolution Architecture

ShaDey Inc.'s identity resolution pipeline follows a four-stage architecture: Source System Extraction → Staging Layer → Identity Resolution Engine → Golden Record Hub. Each stage has distinct responsibilities for data quality, matching rules, and survivorship logic.

Stage 1

Source Systems

SAP, CRM, eCommerce

Stage 2

Staging Layer

Standardize, deduplicate

Stage 3

Identity Resolution

Deterministic + Probabilistic

Stage 4

Golden Record

Unified, survivorship-applied

Identity Resolution Four-Stage Pipeline: Extract, Match & Score, Survive & Merge

Stage 1: Source System Extraction

Pull full extracts from each source system with change-data-capture (CDC) awareness:

Source System	Primary Key	Extract Method	Frequency	Latency
SAP KNA1	KUNNR (12 char)	Table extraction via ABAP FM	Nightly (01:00 UTC)	~2 hours
Salesforce	Id (18 char)	Bulk API 2.0 + queryAll()	Every 6 hours	~1 hour
Shopify	id (numeric)	REST API pagination	Every 4 hours	~30 mins
Magento	entity_id (numeric)	SQL direct query	Nightly (02:30 UTC)	~1.5 hours

Stage 2: Staging Layer: Standardization & Deduplication

Normalize to a common schema before matching:

Staging Customer Record (all sources)

CREATE TABLE stage_customer (
  source_system VARCHAR(20),  -- SAP | SALESFORCE | SHOPIFY | MAGENTO
  source_customer_id VARCHAR(50),  -- native ID from source
  customer_name VARCHAR(500),
  email LOWER(VARCHAR(255)),  -- normalize to lowercase
  phone REGEXP_REPLACE(VARCHAR(20), '[^0-9+]', ''),  -- digits + plus only
  address_line1 VARCHAR(500),
  city VARCHAR(100),
  postal_code REGEXP_REPLACE(VARCHAR(20), '[^A-Z0-9]', ''),  -- uppercase, digits only
  country_code CHAR(2),  -- ISO 3166-1 alpha-2
  created_date DATE,
  updated_date DATE,
  extract_date DATE NOT NULL,
  source_hash VARCHAR(64)  -- MD5 of all attrs for change detection
);
        

Stage 3: Identity Resolution: Deterministic + Probabilistic

Apply matching rules in priority order:

Deterministic (High Confidence): Exact email match (after lowercase normalization). If two records share identical email, they are the same customer (confidence: 0.95).
Deterministic (High Confidence): Exact phone match (after digit-only normalization) AND (last name match OR company name match). Confidence: 0.92.
Probabilistic (Medium Confidence): Email domain match + first name + last name + postal code (fuzzy string matching with Levenshtein distance < 2). Confidence: 0.75.
Probabilistic (Low Confidence): Last name + postal code + country (company name optional). Confidence: 0.55. Flag for manual review if confidence < 0.70.

Identity Resolution SQL (Deterministic Phase)

WITH deduplicated_emails AS (
  SELECT email, ARRAY_AGG(
    STRUCT(source_system, source_customer_id, customer_name)
  ) AS identities
  FROM stage_customer
  WHERE email IS NOT NULL
  GROUP BY email
)
SELECT email, COUNT(*) AS identity_count, identities
FROM deduplicated_emails
WHERE ARRAY_LENGTH(identities) > 1
-- Email is shared across multiple source systems: likely same customer
        

Stage 4: Golden Record Creation

Assign a unique golden_customer_id and link all source identities:

Golden Record Hub

CREATE TABLE golden_customer (
  golden_customer_id VARCHAR(36),  -- UUID or semantic key
  primary_email VARCHAR(255),  -- selected from survivorship logic
  primary_phone VARCHAR(20),
  customer_name VARCHAR(500),
  address_line1 VARCHAR(500),
  city VARCHAR(100),
  postal_code VARCHAR(20),
  country_code CHAR(2),
  lifecycle_stage VARCHAR(50),  -- PROSPECT | CUSTOMER | INACTIVE | VIP
  first_order_date DATE,
  last_order_date DATE,
  created_at TIMESTAMP,
  updated_at TIMESTAMP,
  PRIMARY KEY (golden_customer_id)
);

CREATE TABLE bridge_customer_identity (
  golden_customer_id VARCHAR(36),
  source_system VARCHAR(20),
  source_customer_id VARCHAR(50),
  confidence_score DECIMAL(3,2),
  match_type VARCHAR(20),  -- DETERMINISTIC | PROBABILISTIC
  matched_date DATE,
  PRIMARY KEY (golden_customer_id, source_system, source_customer_id),
  FOREIGN KEY (golden_customer_id) REFERENCES golden_customer(golden_customer_id)
);
        

Golden Customer ID Bridge Identity Survivorship

6 Survivorship Logic: Why "Latest Wins" Fails

A naive golden record strategy is "latest update wins": take the most-recently-modified attribute from any source. This fails catastrophically when sources have different semantics for "modification." CRM's last modified timestamp includes permission changes; SAP's includes document reversals. Survivorship must be attribute-level and source-aware.

The Address Update Paradox

Elena's address appears in three systems with different meanings:

System	Address	Last Updated	Semantic Meaning	Validation Level
SAP KNA1	520 N Lamar Blvd, Austin, TX 78703	2023-12-10	Billing address (verified by finance)	USPS validation ✓
Salesforce	1301 Colorado St, Austin, TX 78701	2024-02-28	Self-reported in CRM update (contact form)	No validation
Shopify	1600 Pennsylvania Ave NW, Washington, DC 20500	2024-01-22	Shipping address (from order)	Carrier verified

Which address is correct? "Latest update" says Salesforce (2024-02-28). But SAP's address is billing-verified (higher confidence). Shopify's address is shipping-verified (transactional truth). They are all correct in their respective contexts. The golden record must choose based on business rules, not timestamp.

Survivorship Rules Matrix

Attribute	Winner (1st Choice)	Tiebreaker (2nd Choice)	Fallback (3rd Choice)	Rationale
Email Address	Salesforce	SAP	Shopify	CRM is primary contact system
Phone Number	Most recent non-null	Salesforce	SAP	Assume most recent is most accurate
Billing Address	SAP (USPS-verified)	Salesforce	Shopify	SAP has financial verification
Shipping Address	Most recent Shopify	Salesforce	SAP	eCommerce has most current info
Customer Name	Salesforce (primary contact)	SAP	Shopify	CRM canonicalizes names
Lifecycle Stage	SAP (revenue logic)	Salesforce	Shopify	SAP transaction history is authoritative

Survivorship Logic (SQL)

WITH source_ranks AS (
  SELECT
    bridge.golden_customer_id,
    bridge.source_system,
    stage.customer_name,
    stage.email,
    stage.phone,
    stage.address_line1,
    CASE bridge.source_system
      WHEN 'SALESFORCE' THEN 1
      WHEN 'SAP' THEN 2
      WHEN 'SHOPIFY' THEN 3
      ELSE 4
    END AS source_priority
  FROM bridge_customer_identity bridge
  JOIN stage_customer stage
    ON bridge.source_system = stage.source_system
    AND bridge.source_customer_id = stage.source_customer_id
)
SELECT golden_customer_id,
  FIRST_VALUE(customer_name) OVER (PARTITION BY golden_customer_id ORDER BY source_priority) AS survivor_name,
  FIRST_VALUE(email) OVER (PARTITION BY golden_customer_id ORDER BY source_priority) AS survivor_email
FROM source_ranks;
        

Do NOT apply survivorship at the record level. Apply it per attribute. Elena's name comes from Salesforce (best CRM data), her billing address from SAP (verified), her shipping address from recent Shopify orders, and her email from Salesforce. Mix sources intelligently.

7 Timing Conflicts Across Feeds

SAP runs nightly batch (01:00 UTC). Salesforce syncs every 6 hours. Shopify streams changes every 4 hours. When the same customer changes state in multiple systems within minutes, the order of message arrival may contradict the true business order of events. Identity resolution must be order-aware and timestamp-reconciled.

Timeline: Elena's Address Change Across Systems

2024-02-15 09:00 AM (Business Truth)

Real Event: Elena calls Salesforce to update her address to "1301 Colorado St" (moving to new apartment). CRM rep enters update in Salesforce.

2024-02-15 09:15 AM

Salesforce Updated: Contact record LastModifiedDate = 2024-02-15 09:15:00. Staging layer awaits next sync window (6 hours).

2024-02-15 12:00 PM

Shopify Sync Window: Elena places an order for delivery to her old address (520 N Lamar Blvd). Order SHP-1005432 created with shipping address = previous address (not yet aware of CRM change).

2024-02-15 03:00 PM

Salesforce Extract Arrives

System State Update: Stage_customer now shows Elena's address = "1301 Colorado St" (effective time: 09:15 AM). But Shopify order SHP-1005432 still shows old address from 12:00 PM extraction.

2024-02-16 01:00 AM

SAP Batch Runs

Billing Verification: SAP scans for address changes and validates against customer master. It does NOT see the Salesforce change (SAP integration happens only via ERP data entry). SAP's billing address remains "520 N Lamar Blvd".

The extraction order (Salesforce → Shopify → SAP) does not match the business order of events (Salesforce change → Shopify order → SAP batch). Identity resolution must tag each record with its business event timestamp, not extract arrival time. Build event-sourcing semantics into staging.

Event Ordering for Identity Resolution

Staging Layer with Event Timestamps

ALTER TABLE stage_customer ADD COLUMN (
  event_timestamp TIMESTAMP NOT NULL,  -- when change occurred in SOURCE
  extraction_timestamp TIMESTAMP NOT NULL,  -- when we extracted it
  event_sequence_id BIGINT  -- CDC log position (if available)
);

-- Sorting for identity resolution must use event_timestamp, not extraction_timestamp
SELECT golden_customer_id, source_system, customer_name
FROM bridge_customer_identity b
JOIN stage_customer s
  ON b.source_system = s.source_system
ORDER BY s.event_timestamp DESC  -- NOT extraction_timestamp
LIMIT 1;
        

8 Operational Truth vs Analytical Truth

SAP's truth is "as of batch run" (nightly at 01:00 UTC). CRM's truth is real-time. Shopify's truth is streaming. Each system is authoritative for different business questions. A return (refund) may be recorded in SAP but not yet reflected in CRM or Shopify. The data warehouse must distinguish operational-lag awareness from analytical lag.

Example: Return Processing Lag

Timestamp (UTC)	System	Event	Order Status	Warehouse Visibility
2024-02-15 14:30	Shopify	Customer initiates return	Return Initiated	Real-time
2024-02-16 10:00	Shopify	Return received & scanned	Return Accepted	Real-time (next 4-hour sync)
2024-02-16 16:45	SAP (Goods Receipt)	Return entered in MIGO transaction	Return Completed	Next batch (01:00 UTC next day)
2024-02-17 01:00	SAP (AR)	Credit memo VBRK generated	Refund Authorized	Available in warehouse from 03:00 UTC
2024-02-17 08:00	CRM Sync	Salesforce updated (manual or API)	Order Closed	6-hour sync window (next available)

A report run at 2024-02-17 02:00 UTC will show: Shopify knows the return is accepted; SAP does not yet know it was returned; CRM does not know either. Building a unified "order status" requires lag-aware aggregation: take Shopify as operational truth (real-time), but reconcile with SAP once batch arrives. Flag records with "stale" timestamps (older than last batch window).

Process Entities vs Analytical Entities

Entity Type	Definition	Source of Truth	Latency Tolerance	Use Case
Operational (Process)	Captures live business state: orders in-flight, returns pending, shipments tracking	System with fastest update (Shopify streaming)	Minutes (sometimes seconds)	Customer service dashboards, fulfillment ops, real-time fraud detection
Analytical	Captures reconciled business facts: completed orders, refunded amounts, customer lifetime value	System with highest confidence after reconciliation (SAP batch + reconciliation)	Hours (typically 24-48 hours)	Financial reporting, customer analytics, pricing models
Hybrid (Real-time Analytical)	Captures most-recent operational state annotated with analytical tags	Operational system until reconciliation; then analytical system	Depends on SLA (typically 4-6 hours)	Marketing dashboards, customer intelligence, inventory forecasting

Operational Order Analytical Customer Reconciled Identity

Dimension-Level Truth: The Elena Rodriguez Example

Customer Elena Rodriguez relocated from Dallas to Houston on 2024-03-15. SAP's KNA1 (master data) reflects the current state; the data warehouse must preserve historical state:

View	System	Elena's Address	Effective As Of	Use Case
Operational Truth	SAP KNA1	Houston, TX 77001	Now (current transaction perspective)	Shipping address for new orders, customer billing, fulfillment
Analytical Truth	DWH dim_customer (SCD Type 2)	Dallas, TX 75201 (2020-06-01 to 2024-03-15)	Historical snapshots	Q1 2024 sales analysis: which address were orders shipped to?
Analytical Truth	DWH dim_customer (SCD Type 2)	Houston, TX 77001 (2024-03-15 onwards)	Historical snapshots	Q2 2024 analysis: new address for forward-looking supply chain

Consequence: A report asking "Show Q1 2024 orders for Elena Rodriguez with shipping address" must query dim_customer with effective_date constraints, NOT current SAP KNA1. If the DWH only kept the latest address (overwriting Dallas with Houston), the Q1 report would incorrectly show Houston as the Q1 address, breaking historical accuracy.

Truth Comparison Across Dimension Types

Dimension	Operational Truth (SAP)	Analytical Truth (DWH SCD Type 2)	Why It Matters
Customer Address	Current address only (Houston)	Full history with effective dates (Dallas → Houston)	Historical order traceability; returns processing; regional analysis
Product Price	Current standard price from KONP	Price history with change dates (cost variance analysis)	Margin trends; promotional impact; pricing elasticity studies
Inventory Level	Current stock (MARD) as of last batch	Point-in-time snapshots (daily or hourly)	Demand forecasting; inventory aging; stockout analysis
Promotion Status	Currently active promotions in TVAPT	Promotion history: start_date, end_date, actual_duration	Campaign effectiveness; cannibalization analysis; ROI by promotion

The core principle: Operational systems are allowed to overwrite current state (SAP replaces Dallas with Houston). The data warehouse MUST NEVER overwrite. It appends and marks historical records as inactive via SCD Type 2 (or SCD Type 3 for limited history). Breaking this principle converts "analytical truth" into "operational approximation"; reports become unreliable for historical questions.

9 Modeling Unified Customer: Neo4j Graph Approach

A relational golden record table is one-dimensional. Neo4j's labeled property graph allows modeling the full identity resolution context: which source identities link to which golden records, with confidence scores, match types, and temporal history all as explicit relationships.

Neo4j Identity Resolution Graph Model

Golden Customer with Multi-Source Identity Relationships

Neo4j Cypher: Building the Identity Graph

Cypher: Create Golden Customer & Link Identities

-- Create Golden Customer node
CREATE (golden:Customer {
  golden_id: "CUST-golden-elena-rodriguez-uuid",
  name: "Elena Rodriguez",
  email: "elena.r@gmail.com",
  phone: "+1-512-555-0147",
  created_at: timestamp()
})

-- Create source identity nodes
CREATE (sap:SystemIdentity {
  system: "SAP",
  source_id: "10045821",
  name: "Rodriguez, Elena M.",
  created_at: timestamp('2019-03-15')
})

-- Create relationship with confidence metadata
CREATE (golden)-[:HAS_IDENTITY {
  confidence_score: 0.95,
  match_type: "DETERMINISTIC",
  matched_on: "email_exact_match",
  matched_at: timestamp()
}]->(sap)
        

Cypher: Resolve Identities by Email

Cypher: Find All Identities for a Customer

MATCH (golden:Customer {email: 'elena.r@gmail.com'})
-[rel:HAS_IDENTITY]->(identity:SystemIdentity)
RETURN
  golden.name AS golden_name,
  identity.system AS source_system,
  identity.source_id AS source_id,
  rel.confidence_score AS confidence,
  rel.match_type AS match_type
ORDER BY rel.confidence_score DESC;

-- Result: 3 rows (SAP 0.95, CRM 0.98, Shopify 0.92)
        

Cypher: Find Duplicate Identities (for deduplication)

MATCH (identity1:SystemIdentity)
-[r1:HAS_IDENTITY]-(golden1:Customer)
-[r2:HAS_IDENTITY]-(identity2:SystemIdentity)
WHERE identity1.system = 'SAP'
  AND identity2.system = 'SHOPIFY'
  AND r1.confidence_score >= 0.90
  AND r2.confidence_score >= 0.90
RETURN golden1.golden_id, identity1.source_id, identity2.source_id
LIMIT 100;
        

Customer (Golden) SystemIdentity HAS_IDENTITY (relationship)

Neo4j makes identity resolution logic explicit and queryable. You can instantly find all identities for a customer, filter by confidence, traverse to related orders/accounts, and maintain full audit history as graph properties. Relational models require complex pivot joins; Neo4j needs a single traversal.

10 Cross-System Identity in the ShaDey PDM (Golden Record Hub)

ShaDey Inc.'s physical data model integrates identity resolution via two new entities: DIM_CUSTOMER (unified golden record) and BRIDGE_CUSTOMER_IDENTITY (source-to-golden mapping). The PDM's 24-entity constellation gains customer-centric consistency.

DIM_CUSTOMER: The Golden Record Dimension

DDL: DIM_CUSTOMER (Golden Record Dimension)

CREATE TABLE dim_customer (
  -- Surrogate Keys & IDs
  customer_key INT PRIMARY KEY AUTO_INCREMENT,
  golden_customer_id VARCHAR(36) UNIQUE NOT NULL,  -- UUID

  -- Survivorship Attributes
  customer_name VARCHAR(500) NOT NULL,
  primary_email VARCHAR(255),
  primary_phone VARCHAR(20),
  billing_address_line1 VARCHAR(500),
  billing_city VARCHAR(100),
  billing_state_province VARCHAR(100),
  billing_postal_code VARCHAR(20),
  billing_country_code CHAR(2),
  shipping_address_line1 VARCHAR(500),
  shipping_city VARCHAR(100),
  shipping_postal_code VARCHAR(20),

  -- Lifecycle & Metrics
  lifecycle_stage VARCHAR(50),  -- PROSPECT | CUSTOMER | VIP | INACTIVE
  first_order_date DATE,
  last_order_date DATE,
  lifetime_value_usd DECIMAL(12,2),
  total_orders INT,
  source_system_count INT,  -- how many systems know about this customer

  -- SCD Type 2 Tracking
  effective_date DATE NOT NULL,
  end_date DATE,  -- null = current
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,

  INDEX (primary_email),
  INDEX (primary_phone),
  INDEX (effective_date),
  UNIQUE (golden_customer_id, effective_date)
);
        

BRIDGE_CUSTOMER_IDENTITY: Source-to-Golden Mapping

DDL: BRIDGE_CUSTOMER_IDENTITY (Identity Resolution Bridge)

CREATE TABLE bridge_customer_identity (
  bridge_key INT PRIMARY KEY AUTO_INCREMENT,

  -- Foreign Keys
  customer_key INT NOT NULL,  -- links to DIM_CUSTOMER
  FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key),

  -- Source System Identity
  source_system VARCHAR(20) NOT NULL,  -- SAP | SALESFORCE | SHOPIFY | MAGENTO
  source_customer_id VARCHAR(50) NOT NULL,

  -- Matching Metadata
  confidence_score DECIMAL(3,2) NOT NULL,  -- 0.00 - 1.00
  match_type VARCHAR(20) NOT NULL,  -- DETERMINISTIC | PROBABILISTIC
  match_criteria VARCHAR(255),  -- email_exact | phone_name | address_fuzzy
  matched_date DATE NOT NULL,

  -- Audit Trail
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  is_active BOOLEAN DEFAULT TRUE,  -- soft delete if identity was merged

  UNIQUE (customer_key, source_system, source_customer_id),
  INDEX (source_system, source_customer_id),
  INDEX (confidence_score),
  INDEX (matched_date)
);
        

Query Example: Unified Customer with All Source Identities

SQL: Golden Record with Source Context

SELECT
  dc.golden_customer_id,
  dc.customer_name,
  dc.primary_email,
  dc.lifecycle_stage,
  dc.lifetime_value_usd,
  STRING_AGG(bci.source_system || ':' || bci.source_customer_id, ',') AS source_ids,
  AVG(bci.confidence_score) AS avg_confidence,
  COUNT(DISTINCT bci.source_system) AS system_count
FROM dim_customer dc
LEFT JOIN bridge_customer_identity bci
  ON dc.customer_key = bci.customer_key
WHERE dc.primary_email = 'elena.r@gmail.com'
  AND dc.end_date IS NULL  -- current record
GROUP BY dc.customer_key;
        

DIM_CUSTOMER BRIDGE_CUSTOMER_IDENTITY Golden Record

11 Document Lifecycle Modeling: SAP Orders as Living Entities

SAP documents (orders, deliveries, invoices) are not static snapshots. They transition through lifecycle states: creation → modification → cancellation or completion. Each transition must be captured as a historical record. Naive designs that overwrite status destroy auditability. This section covers lifecycle state modeling and change history patterns.

SAP Order Lifecycle States

Documents are living entities with history, not static snapshots. An SAP order is created once but modified many times. Each lifecycle transition (created → line item added → quantity changed → cancelled → completed) is an event that must be recorded.

SAP order VBAK lifecycle stages:

Stage	SAP Table / Field	Meaning	Audit Trail
Creation	VBAK.ERDAT, VBAK.ERZEIT	Order created; initial status captured in VBAK.AUART (order type) and VBAK.VBTYP (doc type)	Single record at creation time
Change	CDHDR / CDPOS change docs	Order header or line items modified: quantity changes, line additions/deletions, ship-to address updates	One row per change event in CDHDR; details in CDPOS
Cancellation	VBAK.LIFEX, line-level VBAP.ABGST	Order marked cancelled or rejected; NOT physically deleted. Status flag set to 'C' or 'X'	Change record timestamps cancellation; original order row still exists
Completion	VBAK status fully delivered & billed	All line items delivered and invoiced; order lifecycle closes. No further modifications expected	Final change record marks completion; archive eligibility set

ShaDey Example: Order #4500001234 Lifecycle

Order 4500001234 for Elena Rodriguez: Created on Jan 15, modified on Jan 16 and Jan 18, finally completed on Feb 5.

Event	Timestamp	Change Type	Details
Create	2024-01-15 09:30 UTC	Order created	VBAK.ERDAT=20240115, ERZEIT=093000, line 10 qty=100, line 20 qty=50
Change 1	2024-01-16 14:22 UTC	Line 20 quantity change	CDHDR chgid created; CDPOS shows VBAP-KWMENG: 50 → 80 (reorder increase)
Change 2	2024-01-18 11:45 UTC	Line 30 cancellation	CDHDR new chgid; CDPOS shows new line 30 marked ABGST='C' (cancelled); total qty now 180
Completion	2024-02-05 16:30 UTC	Order delivered & invoiced	VBAK status closed; all lines marked delivered; final CDHDR chgid records closure

Modeling Implications: Never Delete Rows, Track Status

DWH dimension and fact tables must use status tracking (active/modified/cancelled/completed), NOT row deletion. A cancelled order line remains in the fact table with status_flag='CANCELLED' and effective_end_date set. This preserves historical accuracy: "How many lines were ordered vs cancelled?"

Fact: SAP Order Lines with Status Tracking

CREATE TABLE fact_sales_orders (
  -- Keys
  order_line_key INT PRIMARY KEY,
  order_id VARCHAR(10) NOT NULL,  -- VBAK.VBELN
  line_item INT NOT NULL,  -- VBAP.POSNR

  -- Denormalized dimensions
  customer_key INT,
  product_key INT,
  order_date DATE,

  -- Measures
  quantity_ordered DECIMAL(13,3),  -- VBAP.KWMENG
  quantity_delivered DECIMAL(13,3),  -- sum of LIPS.LFIMG
  quantity_cancelled DECIMAL(13,3),  -- qty_ordered - qty_delivered (if cancelled)
  list_price_usd DECIMAL(10,2),  -- VBAP.NETPR
  net_amount_usd DECIMAL(12,2),  -- VBAP.NETWR

  -- Status & Lifecycle
  status VARCHAR(20),  -- 'ACTIVE' | 'MODIFIED' | 'CANCELLED' | 'COMPLETED'
  status_change_reason VARCHAR(255),  -- Why was status set? e.g., "Customer cancellation"
  effective_from_date DATE NOT NULL,  -- When this status took effect
  effective_to_date DATE,  -- When status ended (null = current)

  -- Audit & Change History
  change_number INT,  -- CDHDR.CHGNR (SAP change doc number)
  changed_by_user VARCHAR(50),  -- CDHDR.UNAME
  changed_at_timestamp TIMESTAMP,  -- CDHDR.UDATE + CDHDR.UTIME
  dw_load_date TIMESTAMP
);
        

Change History: Separate Fact Table for Document Modifications

Instead of bloating the order fact with multiple versions, create a dedicated change fact table for audit trails:

Fact: Document Change History (SCD Type 2 alternative)

CREATE TABLE fact_order_changes (
  change_key INT PRIMARY KEY,

  -- Document Reference
  order_id VARCHAR(10),
  line_item INT,
  change_sequence INT,  -- 1st, 2nd, 3rd change for this order line

  -- What Changed
  field_name VARCHAR(50),  -- e.g., 'KWMENG', 'ABGST', 'SHIP_TO'
  old_value VARCHAR(255),
  new_value VARCHAR(255),

  -- Change Context
  change_type VARCHAR(20),  -- 'QUANTITY_CHANGE' | 'LINE_DELETE' | 'STATUS_CHANGE'
  changed_by_user VARCHAR(50),
  changed_at_timestamp TIMESTAMP,
  change_reason VARCHAR(255)  -- Business context
);
        

Example Query: Order Reconstruction from Change History

SQL: Reconstruct Order State at Point-in-Time

-- Show order 4500001234 as it existed on 2024-01-16 (after first change, before second)
SELECT
  order_id,
  line_item,
  quantity_ordered,
  status,
  effective_from_date,
  effective_to_date
FROM fact_sales_orders
WHERE
  order_id = '4500001234'
  AND effective_from_date <= '2024-01-16'::DATE
  AND (effective_to_date IS NULL OR effective_to_date >= '2024-01-16'::DATE)
ORDER BY line_item, effective_from_date;

-- Answer: Line 10 qty=100, Line 20 qty=50 (no change yet), Line 30 not yet cancelled
        

FACT_SALES_ORDERS FACT_ORDER_CHANGES Status Tracking

Documents are living entities with history, not static snapshots. Never delete order rows (use status tracking: active/modified/cancelled/completed) and maintain separate change fact tables. This design preserves the full audit trail: "Which order lines were ever ordered?" is distinct from "Which order lines were eventually delivered?" ShaDey's use of FACT_SALES_ORDERS + FACT_ORDER_CHANGES ensures 100% auditability and enables temporal queries like "Show the order as the customer saw it on order date vs. as it was finally delivered."

12 End-to-End Flow: CPQ → Order → Fulfillment → Invoice → Payment (Data Modeling Discipline)

The product lifecycle page covers these stages operationally. Here we teach them as a *data modeling* discipline: each stage generates distinct entity types with different grains and semantic meaning. The DWH models each as a separate fact table, not columns on the same row.

The Five Stages and Their Fact Tables

Stage	Entity Type	SAP Tables	Fact Grain	Purpose
CPQ (Configure-Price-Quote)	QUOTE	VBAK/VBAP (doc type "H" for quote)	Quote-level + Line-level	Tentative pricing, pre-order opportunities, proposal tracking
Order	ORDER_HEADER, ORDER_LINE	VBAK/VBAP (doc type "C" for order)	Order-line-level (fact grain)	Committed customer orders, revenue planning, backlog
Fulfillment	DELIVERY, DELIVERY_ITEM	LIKP/LIPS	Delivery-item-level (fact grain)	Physical goods movement, logistics tracking, cost allocation
Invoice	BILLING_DOC, BILLING_LINE	VBRK/VBRP	Billing-line-level (fact grain)	Revenue recognition, accounts receivable, tax compliance
Payment	PAYMENT	BSEG/BKPF (FI/CO), or ACDOCA (S/4HANA)	Payment-level or GL-account-level	Cash flow, customer settlement, dispute tracking

Key Insight: Grain Changes at Each Stage

Each fact table has a different grain. Quote and Order may share a header/line structure, but Fulfillment's grain is delivery-item, and Invoice's grain is billing-line. When a single order line generates 3 deliveries (partial shipments), the billing fact table may have 3 billing lines at different invoicing dates. The DWH must model these as separate fact tables, not unnormalized columns on the same row.

Referential Integrity Chain

Quote Header (VBAK)

VBELN

Proposal ID

Order Header (VBAK)

VBELN

Order ID

Delivery (LIKP)

VBELN (ref)

Order→Delivery FK

Invoice (VBRK)

VBELN (ref)

Order/Delivery→Invoice FK

Payment (BSEG)

BELNR (ref)

Invoice→Payment FK

Dimensional Modeling Example: ORDER_LINE Fact

DDL: ORDER_LINE Fact Table (Order stage)

CREATE TABLE fact_order_line (
  order_line_key INT PRIMARY KEY,
  order_date_key INT NOT NULL,  -- FK: DIM_DATE
  customer_key INT NOT NULL,  -- FK: DIM_CUSTOMER
  material_key INT NOT NULL,  -- FK: DIM_MATERIAL
  sales_org_key INT NOT NULL,  -- FK: DIM_ORG

  order_id VARCHAR(50) NOT NULL,  -- VBELN (SAP)
  order_line_num INT NOT NULL,  -- POSNR (SAP)

  -- Measures
  ordered_qty DECIMAL(15,3),
  order_unit_price DECIMAL(12,2),
  order_total_amount DECIMAL(12,2),
  tax_amount DECIMAL(12,2),

  -- Stage Identifier
  stage VARCHAR(20) DEFAULT 'ORDER',  -- distinguishes from quote

  -- Lineage
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL,

  UNIQUE (order_id, order_line_num),
  INDEX (order_date_key),
  INDEX (customer_key)
);
        

DDL: DELIVERY_ITEM Fact Table (Fulfillment stage)

CREATE TABLE fact_delivery_item (
  delivery_item_key INT PRIMARY KEY,
  delivery_date_key INT NOT NULL,  -- FK: DIM_DATE
  customer_key INT NOT NULL,  -- FK: DIM_CUSTOMER
  material_key INT NOT NULL,  -- FK: DIM_MATERIAL

  delivery_id VARCHAR(50) NOT NULL,  -- VBELN (LIKP)
  delivery_line_num INT NOT NULL,  -- POSNR (LIPS)
  order_id_ref VARCHAR(50),  -- FK back to ORDER_LINE

  -- Measures (fulfillment-specific)
  delivered_qty DECIMAL(15,3),  -- may differ from order qty
  delivery_cost DECIMAL(12,2),
  shipping_weight DECIMAL(10,3),

  -- Stage Identifier
  stage VARCHAR(20) DEFAULT 'FULFILLMENT',

  UNIQUE (delivery_id, delivery_line_num),
  INDEX (order_id_ref),
  INDEX (delivery_date_key)
);
        

QUOTE → ORDER → DELIVERY → INVOICE → PAYMENT Fact Tables (One per stage) Referential Integrity Chain

Each stage (CPQ, Order, Fulfillment, Invoice, Payment) is a separate fact table, not columns on the same row. The grain changes at each stage because the business process itself changes what we're tracking. This separation preserves data lineage, enables stage-specific analytics, and supports late-arriving dimensions when upstream stages lag.

13 Customer Identity Divergence Across Business Processes

The same business customer is represented with different identities depending on the process. This isn't a data quality problem; it's a design reality. The DWH must model role-based relationships, not flatten them into one identity record.

Identity Roles Across Systems

System	Identity Concept	Primary ID	Business Role	Example: Metro Office Supplies
SAP ERP (Ordering)	Bill-To Customer (KUNNR)	Customer Number (5-digit)	Entity that receives invoice and pays	KUNNR 12340 (Metro HQ billing)
SAP ERP (Fulfillment)	Ship-To Address (ADRNR)	Address Number (linked to KUNNR)	Location that receives physical goods	ADRNR 001, 002, 003 (Metro branch offices)
Salesforce CRM	Account (orgs) + Contact (people)	AccountId, ContactId (18-char IDs)	Relationship entity; manages deals & interactions	Account "Metro Office Supplies", Contact "Elena Rodriguez" (Procurement)
Salesforce CRM	Contact (multiple per account)	ContactId	Individual at customer; may have separate CRM relationships	Contact "Marco Santos" (Finance), separately tracked
Shopify eCommerce (B2B)	User Profile	customer_id (numeric)	Portal login identity; order history tracking	customer_id 6789 (Elena's B2B portal login)
Shopify eCommerce (B2B)	Guest Session (no login)	session_id, email hash	Ephemeral identity; one-time checkout	session_id "xyz123" (Marco's guest checkout, email-only)

ShaDey Inc. Example: Metro Office Supplies Identity Explosion

One business customer (Metro Office Supplies) = 9 distinct identity records across systems:

SAP KUNNR 12340 (bill-to HQ)
SAP ADRNR 001 (ship-to branch: Boston)
SAP ADRNR 002 (ship-to branch: Chicago)
SAP ADRNR 003 (ship-to branch: Dallas)
Salesforce Account "Metro Office Supplies"
Salesforce Contact "Elena Rodriguez" (Procurement Manager)
Salesforce Contact "Marco Santos" (Finance Manager)
Shopify customer_id 6789 (Elena's portal login)
Shopify guest session (Marco's email-based checkout)

Total: 9 identities. One customer. No data quality issue.

Role-Based Relationship Modeling

The DWH must model these identities as role-based relationships, not flatten them. A single DIM_CUSTOMER row for Metro doesn't capture the nuance. Instead, use bridge tables that preserve role context:

DDL: BRIDGE_CUSTOMER_ROLE (Role-based identity mapping)

CREATE TABLE bridge_customer_role (
  bridge_key INT PRIMARY KEY AUTO_INCREMENT,

  -- Golden record
  customer_key INT NOT NULL,  -- FK: DIM_CUSTOMER (golden Metro record)

  -- Role context
  role_type VARCHAR(50) NOT NULL,  -- BILL_TO | SHIP_TO | CRM_ACCOUNT | CRM_CONTACT | ECOMMERCE_USER | ECOMMERCE_GUEST
  role_sequence INT,  -- for ship-to addresses: 1, 2, 3...

  -- Source system identity
  source_system VARCHAR(20) NOT NULL,  -- SAP | SALESFORCE | SHOPIFY
  source_id VARCHAR(50) NOT NULL,  -- KUNNR, ADRNR, AccountId, ContactId, customer_id, etc.

  -- Role attributes
  role_name VARCHAR(500),  -- "Boston Branch", "Elena Rodriguez", "portal login"
  role_email VARCHAR(255),
  role_address VARCHAR(500),

  -- Metadata
  is_active BOOLEAN DEFAULT TRUE,
  effective_date DATE NOT NULL,
  end_date DATE,

  UNIQUE (customer_key, role_type, source_system, source_id),
  INDEX (source_system, source_id),
  INDEX (role_type),
  INDEX (effective_date)
);
        

SQL: Query all identities for Metro Office Supplies

SELECT
  dc.customer_name,
  bcr.role_type,
  bcr.source_system,
  bcr.source_id,
  bcr.role_name,
  bcr.role_email,
  bcr.role_address
FROM dim_customer dc
JOIN bridge_customer_role bcr ON dc.customer_key = bcr.customer_key
WHERE dc.customer_name = 'Metro Office Supplies'
  AND bcr.end_date IS NULL  -- current records only
ORDER BY bcr.role_type, bcr.role_sequence;
        

Result (metro-office-supplies): 9 rows, one per role. Bill-to role has SAP KUNNR, each ship-to has ADRNR, CRM has Account + 2 Contacts, eCommerce has user + guest session. All linked to one golden customer.

DIM_CUSTOMER (Golden Metro Record) BRIDGE_CUSTOMER_ROLE Role Types: BILL_TO | SHIP_TO | CRM_ACCOUNT | CRM_CONTACT | ECOMMERCE_USER

Customer identity divergence is not a problem to "fix"; it's a design reality that the DWH must preserve. Use BRIDGE_CUSTOMER_ROLE to map source identities to golden records with role context. This allows analytics to understand where a customer paid (bill-to), where they received goods (ship-to), who manages the relationship (CRM contacts), and how they browse and buy online (eCommerce users). A flattened customer table loses this richness.

14 ERP vs CRM vs eCommerce Data Intent Differences

Three systems, same customer. Three different "truths." ERP records financial compliance. CRM records relationships. eCommerce records behavior. When these conflict, the DWH must preserve all three as separate sources, not force a winner.

The Three Data Intents

System	Primary Intent	Time Granularity	Update Pattern	Truth Perspective
SAP ERP	Financial compliance & accounting control	Daily batch (GL posted nightly)	Append-mostly; rare corrections via journal entries	Legal Truth: What was ordered, shipped, billed, paid. Auditable and immutable.
Salesforce CRM	Relationship management & sales productivity	Real-time (API updates instantly)	Update-in-place (overwrite opportunity stage, contact job title, etc.)	Sales Truth: What we know about the customer now. Sales team's current view.
Shopify eCommerce	Conversion optimization & behavioral analytics	Event-stream (millisecond-level events)	Immutable events (every click, add-to-cart, checkout logged as append-only events)	Behavioral Truth: What the customer actually did. Event-driven and temporal.

Conflict Scenario: Three Truths Disagree

Customer: Elena Rodriguez, Metro Office Supplies

SAP ERP Truth: Elena's billing address = "100 Business Ave, Boston MA" (verified by AP, used for invoicing)
Salesforce CRM Truth: Elena's address = "45 Tech Plaza, Boston MA" (updated yesterday by her, self-reported in contact update)
Shopify eCommerce Truth: Elena's latest checkout address = "250 Supply Ln, Boston MA" (used for last order, 1 hour ago)

Which is correct? All three. They represent different moments, roles, and business contexts.

Preservation Strategy: Source Tags & Intent Markers

DDL: Customer Address (Preserving all three truths)

CREATE TABLE dim_customer_address (
  address_key INT PRIMARY KEY,
  customer_key INT NOT NULL,
  address_type VARCHAR(50),  -- BILLING | SHIPPING | CURRENT

  -- Address details
  address_line1 VARCHAR(500),
  city VARCHAR(100),
  state_province VARCHAR(100),
  postal_code VARCHAR(20),

  -- Source context (preserve all three truths)
  source_system VARCHAR(20) NOT NULL,  -- SAP | SALESFORCE | SHOPIFY
  source_intent VARCHAR(50) NOT NULL,  -- FINANCIAL_TRUTH | SALES_TRUTH | BEHAVIORAL_TRUTH
  confidence_level VARCHAR(20),  -- VERIFIED | SELF_REPORTED | DERIVED_FROM_EVENT

  -- Temporal tracking
  valid_from_date DATE NOT NULL,
  valid_to_date DATE,  -- null = current

  INDEX (customer_key, source_intent),
  INDEX (valid_from_date)
);
        

SQL: Query Elena's three address truths

SELECT
  source_system,
  source_intent,
  address_line1,
  city,
  valid_from_date,
  confidence_level
FROM dim_customer_address
WHERE customer_key = (
  SELECT customer_key FROM dim_customer
  WHERE customer_name = 'Elena Rodriguez' AND end_date IS NULL
)
  AND valid_to_date IS NULL  -- current addresses
ORDER BY source_intent;
        

Result (three truths):

FINANCIAL_TRUTH (SAP): 100 Business Ave, Boston MA | VERIFIED
SALES_TRUTH (Salesforce): 45 Tech Plaza, Boston MA | SELF_REPORTED
BEHAVIORAL_TRUTH (Shopify): 250 Supply Ln, Boston MA | DERIVED_FROM_EVENT

How Analytics Use Multi-Truth Data

Financial reporting: Use FINANCIAL_TRUTH (SAP) for invoice matching and AR reconciliation. Legally defensible.
Sales analytics: Use SALES_TRUTH (CRM) for territory mapping and forecast modeling. Reflects sales team's current view.
Fulfillment optimization: Use BEHAVIORAL_TRUTH (eCommerce) for recent shipping patterns. Indicates where the customer actually expects delivery.
Risk detection: Flag when BEHAVIORAL_TRUTH and FINANCIAL_TRUTH diverge significantly (address mismatch → fraud signal).

SAP: FINANCIAL_TRUTH Salesforce: SALES_TRUTH Shopify: BEHAVIORAL_TRUTH

Do not force a single "customer address" or "customer status" across your DWH. Three systems have three different data intents, and each truth is legitimate for different use cases. Preserve all three with source_intent tags. This enables financial compliance, sales productivity, and behavioral analytics simultaneously. When systems disagree, you have a source-aware decision point, not a data quality problem.

15 Soft vs Hard Dependencies Between Systems

Some system relationships are mandatory (hard): one cannot exist without the other. Others are optional (soft): a customer can order without going through CRM. DWH design must handle both with different patterns: hard dependencies need late-arriving dimension handling; soft dependencies need nullable FKs or ghost records.

Hard vs Soft Defined

Hard: System B cannot exist or function without System A. If the upstream feed is late, downstream facts are orphaned.

Examples: SAP order MUST exist before delivery can reference it. SAP material MUST exist before you can order it.

Soft: System B can exist and function independently of System A. The relationship is optional and may never occur.

Examples: CRM opportunity exists whether or not SAP order is created. Shopify cart exists whether or not order is completed.

Dependency Matrix: ShaDey Inc. Case Studies

System Pair	Dependency Type	Why	DWH Pattern
SAP Order → SAP Delivery	HARD	Delivery LIKP references order VBAK via VBELN. Cannot ship without an order.	FK constraint; late-arriving fact handling if delivery arrives before order feed
SAP Order → SAP Invoice	HARD	Invoice VBRK references order VBAK. Cannot bill without an order.	FK constraint; quarantine invoices with missing orders during load
SAP Material → SAP Order	HARD	Order line references MARA via MATNR. Cannot order a nonexistent product.	FK constraint; validate material master loaded before order lines
Shopify Cart → Shopify Order	SOFT	Cart is ephemeral; user may browse, add to cart, then abandon. No order ever created.	Nullable FK or separate fact table (fact_cart); no constraint
CRM Opportunity → SAP Order	SOFT	Opportunity may never convert to order. Or order may exist without an opportunity (spot purchase, self-service eCommerce).	No FK constraint; optional bridge table for matched pairs
Shopify Order → SAP Order (for B2B fulfillment)	HARD (once sync'd)	Shopify orders that are synced to SAP become hard dependencies. Missing sync → orphaned eCommerce order.	Integration status flag; quarantine unsynced orders; alert on divergence

Design Pattern 1: Hard Dependencies with Late-Arriving Dimensions

Problem: Delivery arrives before Order feed (common in high-volume operations)

-- 08:00 AM: Shopify order ships, delivery created in WMS, synced to DWH
-- DELIVERY fact loaded with order_id="SO-98765"
-- Problem: SAP order "SO-98765" hasn't been synced yet (batch runs at 10 AM)
-- Solution: Use a late-arriving dimension pattern (ghost record)
        

DDL: Order with Late-Arrival Handling

CREATE TABLE dim_order_header (
  order_key INT PRIMARY KEY,
  order_id VARCHAR(50) NOT NULL UNIQUE,
  order_date_key INT,  -- nullable; filled when order fact arrives
  customer_key INT,  -- nullable

  -- Late-arrival flag
  is_ghost_record BOOLEAN DEFAULT FALSE,  -- TRUE until order fact loaded
  ghost_created_at TIMESTAMP,  -- when ghost was created
  fact_loaded_at TIMESTAMP,  -- when actual order data arrived

  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);
        

ETL Logic: Ghost Record Creation → Completion

-- STEP 1: Delivery fact arrives, order doesn't exist yet
INSERT INTO dim_order_header (order_id, is_ghost_record, ghost_created_at)
VALUES ('SO-98765', TRUE, NOW())
ON CONFLICT (order_id) DO NOTHING;

-- STEP 2: Delivery fact references this ghost order_key
INSERT INTO fact_delivery (..., order_key, ...)
SELECT ... , ok.order_key, ...
FROM dim_order_header ok
WHERE ok.order_id = 'SO-98765';

-- STEP 3: Order fact arrives (10 AM batch)
UPDATE dim_order_header
SET order_date_key = 20260415,
    customer_key = 42,
    is_ghost_record = FALSE,
    fact_loaded_at = NOW()
WHERE order_id = 'SO-98765'
  AND is_ghost_record = TRUE;
        

Ghost records ensure no orphaned foreign keys. When delivery arrives before order, create a placeholder order dimension with is_ghost_record = TRUE. When the actual order fact arrives, update the ghost to include real attributes. Queries can filter on is_ghost_record = FALSE for production reporting, or include ghosts for completeness-checking.

Design Pattern 2: Soft Dependencies with Nullable FKs or Bridges

Example: Cart (soft dependency) vs Order (no FK required)

CREATE TABLE fact_cart (
  cart_key INT PRIMARY KEY,
  cart_date_key INT NOT NULL,
  customer_key INT NOT NULL,
  -- Note: NO FK to fact_order. Cart may never become an order.

  -- Optional reference to order (if cart was converted)
  order_key INT,  -- nullable; populated only if cart became an order

  cart_total_amount DECIMAL(12,2),
  cart_abandonment_date_key INT,  -- nullable; null = never abandoned (converted)

  INDEX (customer_key),
  INDEX (order_key)  -- not FK; filtered queries only
);

-- Soft-dependency question: "What was in the cart before this order was placed?"
SELECT fc.cart_total_amount, fc.cart_items
FROM fact_cart fc
LEFT JOIN fact_order fo ON fc.order_key = fo.order_key
WHERE fc.customer_key = 42
  AND fc.order_key IS NOT NULL;  -- only converted carts
        

For soft dependencies, use nullable FKs or optional bridges instead of hard constraints. CRM opportunities may never sync to SAP orders, but you still want to track them. Shopify carts may be abandoned. Use order_key INT (nullable) instead of order_key INT NOT NULL REFERENCES ... to allow orphans gracefully.

ETL Dependency Awareness Checklist

When designing ETL pipelines, always classify dependencies:

Identify hard dependencies → implement late-arriving dimension patterns (ghosts) or load-ordering constraints
Identify soft dependencies → use nullable FKs, separate fact tables, or optional bridges (no hard constraints)
Set up monitoring for orphaned FKs (hard dependencies with missing parent records)
Set up alerts for divergent feeds (when a soft dependency should be joined but isn't)
Document the dependency decision in your DWH wiki or data lineage system

HARD: SAP Order → Delivery SOFT: CRM Opportunity → SAP Order Pattern: Ghost Records + Nullable FKs

Dependency type (hard vs soft) drives architectural choices. Hard dependencies require late-arriving dimension patterns, load ordering, and foreign key constraints. Soft dependencies use nullable FKs and optional bridges, allowing orphans gracefully. Conflating the two leads to either orphaned facts (missing FK enforcement) or failed loads (hard FK constraints on optional relationships). Classify early; design accordingly.

16 Anti-Patterns & Common Mistakes

Identity resolution is error-prone. Common architectural mistakes destroy data quality silently. Here are the patterns that guarantee failure.

Anti-Pattern 1: "One Big Customer Table"

Naive Approach: Create a single CUSTOMER table and manually update it whenever any source system changes. No bridge table, no source tracking.

WRONG: Monolithic Customer Table (Do NOT do this)

CREATE TABLE customer (  -- ANTI-PATTERN
  customer_id VARCHAR(50) PRIMARY KEY,
  name VARCHAR(500),
  email VARCHAR(255),
  phone VARCHAR(20),
  address VARCHAR(500),
  -- lost: which source system is authoritative?
  -- lost: audit trail of matches
  -- lost: confidence metadata
);
        

When SAP updates a customer's billing address and CRM updates the same customer's shipping address, which version is in the table? Overwrite logic destroys history. No audit trail. No way to understand why an identity match was made. Rejects customer records without explanation.

Anti-Pattern 2: Premature Identity Resolution

Naive Approach: Resolve identities in the staging layer during extraction before understanding source system semantics. Assume all email addresses are unique and authoritative.

Problem: A shared corporate email (elena.rodriguez@bigcompany.com) exists for both Elena and Marco. Premature resolution merges them into a single golden customer. Impossible to undo without full re-processing.

Identity resolution is inherently probabilistic and context-dependent. Resolving too early (before you understand confidence levels) creates false positives. Always keep source identities separate until confidence is high. Use bridge tables to maintain source context.

Anti-Pattern 3: Ignoring Source System Trust Levels

Naive Approach: Apply survivorship logic uniformly to all attributes from all sources. Treat SAP (ERP with financial controls) the same as Shopify (unvalidated guest checkout).

Problem: A guest's self-reported address in Shopify overwrites SAP's verified billing address (because Shopify's timestamp is newer). Golden record now has an unvalidated address used for financial reporting.

Different systems have different validation levels. SAP address = financially verified. Shopify address = shipping address from order. CRM address = self-reported. Survivorship must weight source trust, not just recency. Build a source_trust_score into your bridge table.

Anti-Pattern 4: Treating CRM as Master Without Validation

Naive Approach: Assume Salesforce is the "master system" for customer data because it's the CRM. Use CRM as the sole source of truth for identity resolution.

Problem: A CRM admin deletes a contact (IsDeleted = true) due to a duplicate. Downstream analytics immediately removes that customer. Later, CRM is restored from backup; the golden record is now stale and inconsistent with CRM again.

CRM is optimized for sales workflows, not for mastering customer identity. Soft deletes, re-parenting, and manual interventions are common. SAP (order-to-cash) has stronger transactional semantics. Treat CRM as one input to identity resolution, not the master. Use SAP transaction history as the ultimate customer truth (if they have orders, they're a real customer).

Dimension	Best Practice	Anti-Pattern
Architecture	Bridge table + golden record + source tracking	Monolithic customer table with overwrite logic
Matching Timing	Late-stage, with confidence threshold enforcement	Premature matching during extraction
Survivorship	Attribute-level, with source-aware trust scores	Record-level or timestamp-based "latest wins"
Master System	Composite (SAP transactions + CRM context + eCommerce recency)	Single system (usually CRM) treated as sole truth
Audit Trail	Full bridge table with match_type, confidence, and criteria	No trail; only golden record persisted
Handling Failures	Quarantine low-confidence matches; flag for manual review	Force merge all matches regardless of confidence

Identity resolution requires architectural discipline. Separate source systems from the golden record. Maintain bridge tables with full metadata. Implement confidence-based quarantine logic. Treat survivorship as an attribute-level decision, not a record-level one. Use multiple source systems as checks-and-balances, not hierarchical masters.