DDD, MDM & Multi-Region Modeling | ShaDey Inc. Data Platform

1Why DDD for Data Modeling?

Domain-Driven Design is a methodology for handling complexity in large software systems by decomposing them into bounded contexts. When applied to enterprise data architecture, DDD helps isolate data semantics, define explicit ownership, and prevent semantic collisions across organizational domains.

ShaDey Inc. operates eight distinct business domains, each with their own vocabulary, rules, and understanding of shared entities. The term "Customer" has profoundly different meanings:

Domain	Customer Meaning	Key Attributes	System of Record
Sales	Account or prospect for opportunity pipeline	Account name, industry, revenue tier, sales rep	SAP SD (KNA1)
Marketing	Segment member, subject of campaigns	Segment ID, engagement score, email opt-in, lifecycle stage	Salesforce CRM
Supply Chain	Ship-to address for delivery	Address, ZIP, delivery days, warehouse zone	SAP MM/LE (KNAD, J_1IADDRS)
Finance	Bill-to legal entity, credit exposure	Legal name, tax ID, payment terms, credit limit, dunning	SAP FI (KNB1, KNKK)
Loyalty	Points-earning entity with tier and redemptions	Member ID, tier status, points balance, lifetime value	Custom Loyalty DB
eCommerce	Shopper with cart, order history, preferences	Email, password hash, shipping preference, wishlists	Magento / custom CMS
Store Ops	Physical visit frequency, merchandise affinity	Footfall count, basket size, preferred location	Analytics (aggregated from POS)
Customer Service	Support ticket contact with interaction history	Phone, support tier, resolution history, NPS	Zendesk / ServiceNow

Key Insight

Without explicit bounded contexts, teams build conflicting data models for the same entity. DDD forces you to name the conflict: "Sales defines a Customer as an account with an active opportunity; Finance defines it as a legal entity with credit exposure." These are different concepts and deserve different tables.

The payoff: By decomposing into bounded contexts first, the data model emerges naturally. Each domain gets a clear definition of which entities it owns, which it consumes, and which it shares. Data governance becomes mechanical: ownership is explicit, not assumed.

2Bounded Contexts: Drawing the Lines

A bounded context is an explicit boundary within which a domain model is valid. Inside the boundary, terms are unambiguous. Outside the boundary, a formal translation happens (Anti-Corruption Layer). ShaDey Inc.'s eight bounded contexts map cleanly onto the organization chart.

Bounded Context Map: Eight distinct business domains (Sales, Marketing, Supply Chain, Finance, Loyalty, eCommerce, Store Operations, Customer Service) with shared entities (Customer, Product, Order) appearing across multiple contexts. Shared Kernel and Anti-Corruption Layer patterns show context interaction points.

Context Mapping Patterns

Shared Kernel: Sales and Marketing share Customer (account name, industry) but with different extensions (Sales adds sales_rep_id, Marketing adds segment_id).
Customer-Supplier: Sales provides SalesOrder → Finance consumes Billing. Finance is downstream; Sales owns the contract.
Anti-Corruption Layer: eCommerce provides Shopper; Sales consumes via ACL that maps Shopper.email → Customer.contact_email.

Entity Name	Sales Context	Marketing Context	Finance Context
Customer	Account with sales org, dist. channel, division	Campaign member with engagement metrics	Bill-to legal entity with credit terms
Product	Sellable item with discount matrix	Promotable bundle with shelf planogram	GL-linked item with cost allocation
Order	Sales order (VBAK) with line items	Campaign response event	Invoice (VBRK) with payment terms

3Shared vs Domain-Local Entities

Some entities are truly shared (Customer, Product, Date); most are domain-local (Campaign, Store Layout, SalesRep). Ownership rules determine which domain is the system of record and which are consumers.

Entity	Sales	Marketing	Supply Chain	Finance	Owner
Customer	Owner	Consumer	Consumer	Consumer	Sales
Product	Consumer	Consumer	Owner	Consumer	Supply Chain
Date	Shared	Shared	Shared	Shared	Platform
SalesOrder	Owner	Not Used	Consumer	Consumer	Sales
Campaign	Not Used	Owner	Not Used	Not Used	Marketing
Promotion	Consumer	Owner	Not Used	Consumer	Marketing
Inventory	Consumer	Not Used	Owner	Not Used	Supply Chain
LoyaltyMember	Consumer	Consumer	Not Used	Not Used	Loyalty

Ownership Rules

Owner: System of record, authoritative for all attributes, owns the SCD (slowly-changing dimension) history.
Consumer: Reads the entity, may have local extensions (prefixed with domain initials: mkt_segment_id), never modifies owner's attributes.
Not Used: Domain has no dependency on this entity.

Example: Product is owned by Supply Chain (MARA in SAP). Sales consumes it and adds product_discount_matrix_id. Finance consumes it and adds cost_center_allocation. Marketing consumes it and adds promotion_id and shelf_planogram_location. None of these local extensions are visible to other domains; they live in domain-specific extension tables.

4Anti-Corruption Layer Pattern

When one bounded context depends on another but cannot fully control it, an Anti-Corruption Layer (ACL) sits between them. The ACL translates and isolates domain models, protecting downstream from upstream changes.

Concrete example: ShaDey Inc. is upgrading SAP from ECC to S/4HANA. The Finance domain (downstream) needs LedgerPosting. The SAP system (upstream) is changing: ECC uses BKPF (header) + BSEG (detail); S/4HANA splits accounting into ACDOCA (universal journal). Without an ACL, Finance's entire model breaks.

With an ACL: Finance defines dim_ledger_posting in their language. The ACL exposes two implementations: one for ECC (joins BKPF + BSEG), one for S/4HANA (reads ACDOCA); and unifies the output. Finance's model never changes.

          -- Anti-Corruption Layer: ECC Implementation
-- Translates SAP ECC tables → Finance domain model
CREATE TABLE stg_ledger_posting_ecc AS
SELECT
  bkpf.bukrs AS company_code,
  bkpf.bstat AS posting_status,
  bkpf.budat AS posting_date,
  bkpf.gjahr AS fiscal_year,
  bkpf.xblnr AS reference,
  SUM(bseg.wrbtr) AS total_amount,
  bkpf.usnam AS posted_by_user
FROM sap_ecc.bkpf
JOIN sap_ecc.bseg ON bkpf.mandt = bseg.mandt
  && bkpf.bukrs = bseg.bukrs
  && bkpf.gjahr = bseg.gjahr
  && bkpf.belnr = bseg.belnr
WHERE bkpf.bstat != 'X'  -- exclude canceled
GROUP BY bkpf.bukrs, bkpf.bstat, bkpf.budat,
           bkpf.gjahr, bkpf.xblnr, bkpf.usnam;
        

          -- Anti-Corruption Layer: S/4HANA Implementation
-- Translates ACDOCA → same Finance domain model
CREATE TABLE stg_ledger_posting_s4 AS
SELECT
  acdoca.bukrs AS company_code,
  acdoca.posting_status,
  acdoca.posting_date,
  acdoca.fiscal_year,
  acdoca.reference,
  acdoca.amount_in_company_currency AS total_amount,
  acdoca.created_by AS posted_by_user
FROM sap_s4hana.acdoca
WHERE acdoca.posting_status != 'CANCELED';
        

Finance then consumes a unified view. A dim_ledger_posting table uses a UNION or parameterized logic to pick the correct ACL implementation based on the posting period. The Finance domain model never changes; the translation layer absorbs upstream complexity.

ACL Benefits

1. Downstream contexts (Finance) remain stable during upstream migrations (ECC → S/4HANA).
2. Translation logic isolated in one place, not scattered in 50 ETL pipelines.
3. Easy to support dual-run scenarios: ECC + S/4 running in parallel, one logical view.

5Master Data Management: Golden Record Design

Master data is the single source of truth for business entities. A golden record is not just the "survivor" from multiple sources: it's a deliberate design with survivorship intent: each attribute comes from the source system most authoritative for that attribute.

Customer Golden Record: Survivorship Intent (ShaDey Inc.)

Attribute	Owner (System of Record)	Confidence Score	Last Verified Logic
legal_name	SAP FI (KNB1)	95%	Audited for SOX compliance; last verified by Finance
email	eCommerce (self-service)	88%	Customer can update; validated by opt-in confirmation
phone	CRM (Salesforce)	82%	Sales rep updates; last verified during account review
shipping_address	eCommerce	91%	Customer-entered; validated by successful delivery
billing_address	SAP (KNA1)	97%	Verified by invoice delivery; verified by Finance
segment_code	Marketing (CRM segmentation)	75%	Recalculated monthly; last verified by Marketing Manager
credit_limit	Finance (SAP FI)	94%	Approved by credit committee; last updated 6 months ago
industry_code	CRM (manual classification)	68%	Sales rep enters; not validated; needs review

Survivorship Intent vs Conflict Resolution

Intent: "The marketing segmentation system is the authoritative source for segment_code because the Marketing team owns the segmentation logic."
Never: "Pick the most recent value." That's conflict resolution, not intent. Intent is declarative: this system is the source of truth for this attribute.

Each attribute gets a confidence score: how often the source is actually right. legal_name from SAP is 95% confident (audited for SOX); industry_code from CRM is 68% (sales reps guess). Low confidence signals: data quality issue, need for cleansing, or wrong owner.

          -- Golden Record: Customer Master with Survivorship Intent
CREATE TABLE dim_customer_golden (
  customer_id                BIGINT PRIMARY KEY,
  legal_name                VARCHAR(255),
  legal_name_source_system  VARCHAR(20),      -- 'SAP_FI'
  legal_name_confidence     DECIMAL(3,2),    -- 0.95
  legal_name_last_verified  TIMESTAMP,

  email                      VARCHAR(255),
  email_source_system       VARCHAR(20),     -- 'ECOMMERCE'
  email_confidence          DECIMAL(3,2),
  email_last_verified       TIMESTAMP,

  shipping_address          VARCHAR(500),
  shipping_address_source    VARCHAR(20),
  shipping_confidence       DECIMAL(3,2),
  shipping_last_verified    TIMESTAMP,

  billing_address           VARCHAR(500),
  billing_address_source     VARCHAR(20),
  billing_confidence        DECIMAL(3,2),
  billing_last_verified     TIMESTAMP,

  credit_limit              DECIMAL(12,2),
  credit_limit_source       VARCHAR(20),
  credit_limit_confidence   DECIMAL(3,2),
  credit_limit_last_verified TIMESTAMP,

  segment_code              VARCHAR(50),
  segment_source            VARCHAR(20),     -- 'MARKETING'
  segment_confidence        DECIMAL(3,2),
  segment_last_verified     TIMESTAMP,

  created_ts                 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  modified_ts                TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
        

The golden record is not a table of survivors. It's a specification. Each consumer context (Sales, Marketing, Finance) queries it with a SELECT that picks the surviving attribute. The confidence score tells them: "This email is 88% likely correct, and hasn't been verified in 45 days."

6Conflict Detection Modeling

Conflict detection is not conflict resolution. You detect misalignment, surface it to humans, and let them decide. Modeling conflicts means building explicit queries and workflow tables.

Example: SAP says customer address is "123 Main St, Chicago, IL 60601". CRM says "123 Main Street, Chicago, IL 60601" (spelling variation). eCommerce says "123 Main St, Chicago, IL 60614" (different ZIP). These are conflicts. The golden record flow must:

1. Detect that three sources disagree on shipping_address for customer_id = 98765

2. Log the conflict to mdm_conflict_log with all three versions and source systems

3. Flag the golden record as conflict_status = 'UNRESOLVED' and requires_manual_review = TRUE

4. Notify the data steward via workflow (email or queue)

5. Steward decides which source is authoritative (eCommerce is customer self-serve, trust it) and marks resolution

6. Golden record updates: pick the winning source, reset conflict_status = 'RESOLVED'

          -- Conflict Detection: Compare Source Values
CREATE TABLE mdm_conflict_log (
  conflict_id           BIGINT PRIMARY KEY AUTO_INCREMENT,
  entity_type          VARCHAR(50),  -- 'CUSTOMER', 'PRODUCT'
  entity_id            BIGINT,
  attribute_name       VARCHAR(100), -- 'shipping_address'
  source_system_1      VARCHAR(20),
  source_system_1_value VARCHAR(500),
  source_system_2      VARCHAR(20),
  source_system_2_value VARCHAR(500),
  conflict_type        VARCHAR(50),  -- 'SPELLING_VARIANCE', 'GENUINE_MISMATCH'
  detection_ts         TIMESTAMP,
  resolution_status    VARCHAR(50),  -- 'UNRESOLVED', 'AUTO_RESOLVED', 'MANUAL_RESOLVED'
  steward_decision     VARCHAR(20),  -- 'PICK_SYSTEM_1', 'PICK_SYSTEM_2', 'CUSTOM_VALUE'
  steward_user_id      VARCHAR(50),
  resolution_ts        TIMESTAMP,
  notes                 TEXT
);

-- Detect conflicts: shipping_address differs across sources
INSERT INTO mdm_conflict_log (
  entity_type, entity_id, attribute_name,
  source_system_1, source_system_1_value,
  source_system_2, source_system_2_value,
  conflict_type, detection_ts,
  resolution_status
)
SELECT
  'CUSTOMER' AS entity_type,
  sap.customer_id,
  'shipping_address' AS attribute_name,
  'SAP' AS source_system_1,
  sap.address AS source_system_1_value,
  'ECOMMERCE' AS source_system_2,
  ecom.shipping_address AS source_system_2_value,
  'GENUINE_MISMATCH' AS conflict_type,
  CURRENT_TIMESTAMP(),
  'UNRESOLVED' AS resolution_status
FROM sap_extract sap
JOIN ecommerce_extract ecom ON sap.customer_id = ecom.customer_id
WHERE 1.0 - (LEVENSHTEIN(sap.address, ecom.shipping_address)
       ::FLOAT / GREATEST(LENGTH(sap.address), LENGTH(ecom.shipping_address), 1)) < 0.85
  AND sap.address IS NOT NULL
  AND ecom.shipping_address IS NOT NULL;
        

Conflict Types

Spelling variance: SAP "Main St" vs CRM "Main Street": auto-resolvable via fuzzy matching.
Genuine mismatch: Different ZIPs: requires human decision (which address is current?).
Timeliness lag: Old value in System A vs new value in System B: resolve based on timestamp.

7Multi-Region Master Design: Global Core vs Regional Extensions

ShaDey Inc. is expanding globally: North America (NA), Europe/Middle East/Africa (EMEA), Asia-Pacific (APAC). The master data architecture must support global attributes (legal_name, customer_id) while allowing regional attributes (tax_ids, regulatory flags) and local overrides.

The pattern: Global core → regional extension → local override. Example: Product pricing.

Multi-Region Master Data Architecture: Global Core contains universal attributes (customer_id, legal_name, country_code). Regional Extensions (NA, EMEA, APAC) add region-specific attributes (tax IDs, regulatory flags, currency). Local Overrides (dashed) allow store/division-level customizations.

Global Core

dim_product_global
• product_id (surrogate)
• sku
• name
• msrp (manufacturer suggested retail)
• category_id
Source: Supply Chain

Regional Extension

dim_product_ext_na
• product_id
• na_base_price
• na_currency (USD)
• na_regulatory_class
Source: NA Finance
FK → Global

Local Override

dim_product_local_na_west
• product_id
• west_promotional_price
• west_shelf_location
• effective_from
Source: NA West Store Ops
FK → NA Extension

Attribute	Level	System of Record	Scope	Example Value
legal_name	Global	Supply Chain (MARA)	All regions	"Organic Cotton T-Shirt"
msrp	Global	Supply Chain (MARA)	All regions	USD 49.99 (reference only)
na_base_price	NA Regional	NA Finance	North America	USD 44.99
emea_base_price	EMEA Regional	EMEA Finance	Europe/Middle East/Africa	EUR 39.99
apac_base_price	APAC Regional	APAC Finance	Asia-Pacific	AUD 65.99
west_promotional_price	Local (NA-West)	NA West Store Ops	West US stores only	USD 34.99 (promotion)
gdpr_consent_date	EMEA Regional	EMEA Marketing	EMEA customers only	2024-03-15

          -- Multi-Region Master Data: Global → Regional → Local
CREATE TABLE dim_customer_global (
  customer_id         BIGINT PRIMARY KEY,
  global_customer_code VARCHAR(50),
  legal_name          VARCHAR(255),
  primary_region_code VARCHAR(10),  -- 'NA', 'EMEA', 'APAC'
  country_code        VARCHAR(2),   -- ISO 3166
  global_status       VARCHAR(50),
  modified_ts         TIMESTAMP
);

-- Regional Extension: NA (North America)
CREATE TABLE dim_customer_ext_na (
  customer_id         BIGINT PRIMARY KEY,
  na_customer_code    VARCHAR(50),
  ssn                 VARCHAR(11),   -- US Social Security (PII)
  state_tax_id        VARCHAR(20),
  na_credit_limit     DECIMAL(12,2),
  na_payment_terms    VARCHAR(50),
  FOREIGN KEY (customer_id) REFERENCES dim_customer_global
);

-- Regional Extension: EMEA (Europe/Middle East/Africa)
CREATE TABLE dim_customer_ext_emea (
  customer_id         BIGINT PRIMARY KEY,
  vat_number          VARCHAR(50),   -- Tax ID
  gdpr_consent_date   DATE,
  gdpr_consent_version VARCHAR(20),
  data_retention_days  INT,          -- GDPR right to erasure
  emea_credit_limit   DECIMAL(12,2),
  FOREIGN KEY (customer_id) REFERENCES dim_customer_global
);

-- Regional Extension: APAC (Asia-Pacific)
CREATE TABLE dim_customer_ext_apac (
  customer_id         BIGINT PRIMARY KEY,
  abn                 VARCHAR(50),   -- Australian Business Number
  gst_registration    VARCHAR(50),   -- Goods & Services Tax
  apac_credit_limit   DECIMAL(12,2),
  apac_payment_terms  VARCHAR(50),
  FOREIGN KEY (customer_id) REFERENCES dim_customer_global
);
        

Multi-Region Design Principles

Global first: Start with customer_id, legal_name, country. No regional data in global table.
Regional extension: Tax IDs, regulatory flags, regional credit rules live here.
FK integrity: Regional tables always have FK to global; never the reverse.
Data sovereignty: EMEA customer data stays in EMEA databases (partition by region_code).

8Attribute Override Patterns

Regional extensions set defaults; local overrides temporarily change them. The key is precedence chains: when you query an attribute, you check Local → Regional → Global in order.

Example: Product pricing in ShaDey Inc. Global MSRP is USD 49.99. NA regional base is USD 44.99. West Coast promotional override is USD 34.99 for 90 days. The override chain resolves to: West override → NA base → Global MSRP.

Product ID	Global MSRP	NA Base Price	West Promo Price	Effective Price	Source
SKU-001	USD 49.99	USD 44.99	USD 34.99 (active until 2024-05-31)	USD 34.99	Local override
SKU-002	USD 29.99	USD 26.99	None (expired 2024-03-01)	USD 26.99	Regional base
SKU-003	EUR 39.99	N/A (EMEA)	N/A	EUR 39.99	Global MSRP

          -- Override Resolution: Local → Regional → Global
CREATE TABLE dim_product_price_final AS
SELECT
  g.product_id,
  g.sku,
  g.name,
  -- Override chain: Local > Regional > Global
  COALESCE(
    local.promotional_price,
    regional.base_price,
    g.msrp
  ) AS effective_price,
  -- Track which level provided the price
  CASE
    WHEN local.promotional_price IS NOT NULL
         && CURRENT_DATE() <= local.effective_until
      THEN 'LOCAL_OVERRIDE'
    WHEN regional.base_price IS NOT NULL
      THEN 'REGIONAL_BASE'
    ELSE 'GLOBAL_MSRP'
  END AS price_source,
  local.effective_from,
  local.effective_until
FROM dim_product_global g
LEFT JOIN dim_product_ext_na regional
  ON g.product_id = regional.product_id
LEFT JOIN dim_product_local_na_west local
  ON regional.product_id = local.product_id
  && CURRENT_DATE() BETWEEN local.effective_from && local.effective_until;
        

Override Best Practices

1. Always include effective_from and effective_until dates. Overrides expire.
2. Never overwrite parent data; always use COALESCE or CASE to resolve chains.
3. Log every override: who created it, why, when it expires. Audit trail required.
4. Validate at each level: promotional price must be ≤ regional base; regional ≤ global MSRP.

9Regional Partition Strategies

Multi-region data must be partitioned for performance, compliance (GDPR: EMEA data stays in EMEA), and operational isolation. Partition key selection is critical.

Partition Strategy	Partition Key	Benefits	Challenges
By Region	`region_code` (NA, EMEA, APAC)	Data sovereignty; easy compliance; clean geographic isolation	Cross-region queries require multi-partition scan; global reporting slower
By Country	`country_code` (US, UK, FR, AU, etc.)	Fine-grained control; country-level regulations; multi-level hierarchy	Extreme partition count (100+ countries); unbalanced sizes (US dominant)
By Tenant	`tenant_id` (each region/subsidiary is a tenant)	Multi-tenancy support; each tenant isolated; SaaS-friendly	Adds application-level complexity; tenant isolation rules required

Recommended: Partition by Region with Range (Date)

-- Global customer table, partitioned by region + month CREATE TABLE fact_customer_activity ( activity_id BIGINT, customer_id BIGINT, region_code VARCHAR(10), activity_date DATE, amount DECIMAL(12,2), activity_type VARCHAR(50) ) PARTITION BY LIST (region_code) ( PARTITION p_na VALUES IN ('NA') SUBPARTITION BY RANGE (YEAR_MONTH(activity_date)) ( SUBPARTITION p_na_202401 VALUES LESS THAN ('202402'), SUBPARTITION p_na_202402 VALUES LESS THAN ('202403') ), PARTITION p_emea VALUES IN ('EMEA') SUBPARTITION BY RANGE (YEAR_MONTH(activity_date)) (...), PARTITION p_apac VALUES IN ('APAC') SUBPARTITION BY RANGE (YEAR_MONTH(activity_date)) (...) );

Query Pattern	Execution Plan	Partition Pruning
`WHERE region_code = 'NA' AND activity_date BETWEEN...`	Scans p_na_202401, p_na_202402 only	Excellent (predicate filters both dimensions)
`WHERE activity_date BETWEEN ... (no region)`	Scans all regions' date partitions	Good (prunes by date, but must scan all regions)
`SELECT COUNT(*) FROM fact_customer_activity`	Full table scan	None

Partition Key Selection for ShaDey Inc.

Primary key: region_code (NA, EMEA, APAC): aligns with GDPR/data sovereignty.
Subpartition: YEAR_MONTH(created_date): enables monthly archive/purge cycles.
Index: Composite index on (region_code, created_date, customer_id) to support typical queries.

10Sync-Friendly Modeling

In a multi-region environment, regions must sync master data without creating conflicts. The model must support eventual consistency, detect conflicts, and avoid cross-region foreign keys.

Key principle: No cross-region foreign keys. Customer ID is global (created in NA HQ), but regional systems reference it via relaxed FK checks or eventual consistency guarantees. Never issue a query: "JOIN fact_sales_na TO fact_sales_emea on customer_id": regions are independent.

          -- Sync-Friendly: Global Master with Regional Event Stream

-- Master: created in NA, authoritative copy
CREATE TABLE dim_customer_master_na (
  customer_id    BIGINT,
  global_version INT,              -- Version counter for conflict detection
  legal_name     VARCHAR(255),
  modified_ts    TIMESTAMP,
  modified_by_user VARCHAR(50),
  PRIMARY KEY (customer_id)
);

-- Regional Copy: sync from NA, but supports local edits with conflict tracking
CREATE TABLE dim_customer_replica_emea (
  customer_id    BIGINT,
  global_version INT,              -- Tracks version from master
  local_version   INT,              -- Version of local edits
  legal_name     VARCHAR(255),
  vat_number     VARCHAR(50),
  modified_ts    TIMESTAMP,
  sync_status    VARCHAR(50),      -- 'SYNCED', 'NEEDS_SYNC', 'CONFLICT'
  last_sync_ts   TIMESTAMP,
  PRIMARY KEY (customer_id)
);

-- Event log: all changes published to message queue (Kafka, Pub/Sub)
CREATE TABLE mdm_sync_event_log (
  event_id       BIGINT PRIMARY KEY AUTO_INCREMENT,
  entity_type    VARCHAR(50),      -- 'CUSTOMER', 'PRODUCT'
  entity_id      BIGINT,
  event_type     VARCHAR(50),      -- 'CREATED', 'UPDATED', 'DELETED'
  change_data    JSON,
  source_region  VARCHAR(10),     -- 'NA' (origin)
  timestamp      TIMESTAMP,
  published_to_queue BOOLEAN
);

-- Sync logic: idempotent upsert with conflict detection
INSERT INTO dim_customer_replica_emea (
  customer_id, global_version, legal_name, modified_ts, sync_status
)
SELECT
  na.customer_id,
  na.global_version,
  na.legal_name,
  na.modified_ts,
  'SYNCED'
FROM dim_customer_master_na na
ON CONFLICT (customer_id) DO UPDATE SET
  global_version = CASE
    WHEN na.global_version = EXCLUDED.global_version
         THEN na.global_version  -- clean sync
    ELSE na.global_version      -- conflict: master wins
  END,
  sync_status = CASE
    WHEN na.global_version > EXCLUDED.global_version
         && EXCLUDED.local_version > 0
      THEN 'CONFLICT'      -- master changed AND local edits exist
    ELSE 'SYNCED'
  END,
  last_sync_ts = CURRENT_TIMESTAMP();
        

Sync-Friendly Design Checklist

✓ No cross-region foreign keys (use global ID only)
✓ Version counters on master and replica for conflict detection
✓ Event log publishes all changes to message queue (Kafka)
✓ Idempotent upserts (same change applied twice = no conflict)
✓ Last-write-wins with vector clocks if concurrent edits possible
✓ Eventual consistency guaranteed; conflicts surfaced for human review

11Neo4j Graph Modeling for Domain Context Mapping

Graph databases excel at modeling domain context maps: domains (nodes), entities (nodes), ownership relationships (edges). This enables queries like "Which systems consume the Customer entity?" instantly.

          -- Neo4j: Domain-Driven Design Graph Model

-- 1. Create domain nodes for all 8 bounded contexts
CREATE (
  sales : Domain { name: 'Sales', owner: 'Sales VP' }
);
CREATE (
  marketing : Domain { name: 'Marketing', owner: 'CMO' }
);
CREATE (
  supply_chain : Domain { name: 'Supply Chain', owner: 'COO' }
);
CREATE (
  finance : Domain { name: 'Finance', owner: 'CFO' }
);

-- 2. Create entity nodes (conformed dimensions)
CREATE (
  customer_entity : Entity {
    name: 'Customer',
    table_name: 'dim_customer',
    grain: 'One row per unique customer',
    scd_type: 2,
    is_shared: true
  }
);
CREATE (
  product_entity : Entity {
    name: 'Product',
    table_name: 'dim_product',
    grain: 'One row per SKU',
    scd_type: 2,
    is_shared: true
  }
);
CREATE (
  campaign_entity : Entity {
    name: 'Campaign',
    table_name: 'dim_campaign',
    is_shared: false
  }
);

-- 3. Create OWNS relationships (domain owns entity)
CREATE (sales)-[:OWNS { confidence: '100%' }]-(customer_entity);
CREATE (supply_chain)-[:OWNS { confidence: '100%' }]-(product_entity);
CREATE (marketing)-[:OWNS { confidence: '100%' }]-(campaign_entity);

-- 4. Create CONSUMES relationships (domain uses entity)
CREATE (marketing)-[:CONSUMES { update_lag: '1 day' }]-(customer_entity);
CREATE (finance)-[:CONSUMES { update_lag: 'real-time' }]-(customer_entity);
CREATE (sales)-[:CONSUMES { update_lag: '1 hour' }]-(product_entity);

-- 5. Create DATA_STEWARD relationships
CREATE (customer_entity)-[:HAS_STEWARD { title: 'Data Steward' }]-(steward_john : Person { name: 'John Smith' });
        

          -- Query: All consumers of Customer entity
MATCH (domain : Domain)-[:CONSUMES]-(entity : Entity { name: 'Customer' })
RETURN domain.name, entity.table_name;
        

Output: Sales, Marketing, Finance, Customer Service all depend on Customer. Sales owns it.

          -- Query: What's the update lag for EMEA Marketing to get Finance's data?
MATCH (finance : Domain { name: 'Finance' })-[:CONSUMES { update_lag: lag }]-(entity : Entity)
MATCH (marketing : Domain { name: 'Marketing' })-[:CONSUMES]-(entity)
RETURN entity.name, lag;
        

Graph models make context mapping visible and queryable. You can detect conflicts ("Two domains own the same entity"), trace dependencies ("If Sales.Order changes, who's affected?"), and audit ownership transitions.

Graph Modeling Benefits

• Instant visibility: "Who owns Customer?" → one query, not a governance document.
• Dependency analysis: "If we modify Customer, what breaks?" → traverse CONSUMES edges.
• Conflict detection: "Two domains claim to own the same entity" → graph constraint violation.
• Audit trails: SCD Type 2 on OWNS relationships shows when ownership transferred.