Carlos EscasainCommercial Systems & Process Architect
ENES

Data case / 02 · Grain, identity & freshness

Turning ERP transactions and targets into commercial intelligence

A fictional data architecture case: grain, conformed identity, late-arriving facts, corrections and period close—before any dashboard is built.

Fictional scenario9 min readUpdated 28 Sep 2026
Fictional scenario

Independently created. Contains no employer or client implementation detail, internal names or figures.

01The outcome

One conformed model at account × product line × month, with an explicit period status, from which every performance metric is computed once and published to the CRM.

The reality

Orders, invoices and targets arrive from different systems at different grains and times—so every report answers a slightly different question.

Architecture question

What is the grain of each fact, and where do they meet without double counting?

Key decision

Conform facts in the data platform at a shared grain and publish metrics—rather than copying transactions into the CRM or reporting from each source separately.

ContextA B2B manufacturer sells through several of its own companies, each with its own ERP company code. Commercial teams want one view of orders, invoices and targets per customer, subsidiary and period. Today each region exports ERP data into spreadsheets, joins it to the CRM by customer name and computes its own performance figures.

Disciplines involvedData & IntegrationsRevenue OperationsSystems & CRM Architecture
Systems and partiesERPPlanningCRMData platform

02The reality · current state

What the data does today.

  • Order lines and invoice lines are joined directly, so partial deliveries are counted twice
  • Customers are matched by name; groups buying through several legal entities split or collide
  • Invoices arrive a day after orders, so yesterday’s performance looks worse every morning
  • Targets set mid-year are compared with full-year actuals
  • Cancelled orders and credit notes are removed or ignored depending on who built the report
  • Each region converts currency with its own rate

03Target flow

Where the data comes from, where it goes—and how.

Every hand-over is named with its mode. The mode follows the business tolerance for waiting, inconsistency and loss.

  • 01 · ERPERP orders

    Order lines per company, every version kept.

    Feeds by Batch
  • 02 · ERPERP invoices

    Posted invoice lines, including credit notes.

    Feeds by Batch
  • 03 · PlanningTargets

    Published target versions by account, product line and month.

    Feeds by Batch
  • 04 · CRMCRM accounts

    Commercial accounts, hierarchy and legal-entity links.

    Feeds by Event
  1. 05 · Data platformData platform

    Conformed facts and the analytical customer key.

    Hands over by Batch
  2. 06 · Data platformMetric computation

    Performance to target, coverage and period status.

    Hands over by Batch
  3. 07 · CRMCRM activation

    Read-only signals on the account, with an as-of date.

04Contracts

What one row or message means.

Meaning, grain, key, freshness and owner—written down before anything is mapped.

Contract

Order line

A commercial commitment; changes until delivered or cancelled.

Grain
One row per order line per version
Key
Company + order + line + version
Freshness
Daily by 06:00
Owner
ERP data owner
Contract

Invoice line

A recognised sale. Corrected by credit notes, never edited.

Grain
One row per posted invoice line
Key
Company + invoice + line
Freshness
Daily by 06:00
Owner
Finance
Contract

Target

A published plan; partial-year targets start at their effective month.

Grain
Account × product line × month
Key
Target version + account + line + month
Freshness
On publication
Owner
Sales operations
Contract

Customer dimension

One commercial account as it was on each date, linked to its legal entities and ERP customers.

Grain
One row per account per validity period
Key
Analytical customer key
Freshness
Hourly from CRM events
Owner
CRM data owner

05Key dimension · Grain, identity & freshness

Four sources, four grains, one place to meet

Each source keeps its native grain. They are compared only at a conformed grain, with rules for what arrives late and what gets corrected.

  1. Order lines
    Native grain
    Order line × version
    Arrives
    Daily, same day
    Corrections
    New version; cancelled is a status
  2. Invoice lines
    Native grain
    Posted invoice line
    Arrives
    Daily, often a day after the order
    Corrections
    Credit-note line, never an edit
  3. Targets
    Native grain
    Account × product line × month
    Arrives
    On publication, sometimes mid-year
    Corrections
    New target version
  4. Accounts
    Native grain
    Account × validity period
    Arrives
    Hourly events
    Corrections
    Merge keeps the surviving key; history re-points
Conformed grain

Account × product line × month

  • Orders and invoices are aggregated separately and compared at this grain—never joined line to line.
  • A partial-year target counts from its effective month; months before it have no target, not a zero.
  • A month stays provisional until the finance close; late invoices recompute it.
  • Cancelled orders keep their history and drop out of open orders.
  • Amounts are converted once, at the reporting rate, in the data platform.

The grain decision is the architecture. Every later metric inherits it.

06Data authority

Who may create, change, read or derive each concept.

Highlight
Data authority by business concept: which system may create, update, read or derive each concept
Business conceptERPPlanningCRMData platform
Order lineThe ERP creates and changes orders. Every version is kept in the data platform.CreateUpdateNo authorityNo authorityRead
Invoice linePosted invoices are immutable; a correction is a new credit-note line.CreateNo authorityNo authorityRead
TargetPlanning publishes versions. A republished target is a new version, not an edit.No authorityCreateUpdateReadRead
Account ↔ ERP customer linkThe cross-reference comes from the CRM and master data; the data platform never infers it from names.ReadNo authorityCreateUpdateRead
Analytical customer keyIssued by the data platform; survives merges so history stays joined.No authorityNo authorityReadCreate
Performance to targetDerived once, from one definition. The CRM shows it; it never computes it.No authorityNo authorityReadDerive
Period statusProvisional until the finance close; final afterwards.No authorityNo authorityReadDerive

No system owns a whole record here. Authority sits with each concept—and sometimes changes hands when the lifecycle does.

07Failure & recovery

Design the failure path before the happy path.

  • Invoices arrive a day after ordersCompleteness per company and dayMark the month provisional; recompute when they arriveData platform team
  • An order is cancelled after being countedNew order version with a cancelled statusLatest version wins; history keeps bothERP data owner
  • An ERP customer maps to no CRM accountUnmapped-customer check on every loadPark under “unmapped” and route to master data—never drop the revenueMaster data
  • A target is republished mid-yearNew target versionRecompute affected months; keep the previous version for auditSales operations

08Candidate architectures

Credible options, judged against these premises.

Rejected

Copy transactions into the CRM

Few transactions, one company

Cost: CRM volume, no point-in-time history, every team recomputes
Rejected

Report straight from each source

One source per question

Cost: Different grains and timing give different answers to the same question
Selected

Conform in the data platform, publish metrics

Several sources and companies, shared definitions

Cost: Needs grain and identity contracts and a freshness promise

09The second layer

Questions that change the design.

Grain

  1. What is the grain of each dataset?
  2. Can order and invoice lines be joined—or only compared at a shared grain?
  3. How are partial-year targets represented?

Timing

  1. What happens when one source updates later than another?
  2. When is a period final?
  3. How are cancelled or corrected transactions represented?

Meaning

  1. How are customers identified consistently?
  2. Which currency is authoritative?
  3. Where are metrics computed—and how are they activated in the CRM?

10Decisions & outputs

What the work produces.

Architecture decisionADR 002 — Place commercial KPIs by action horizonArchitecture decisionADR 005 — Separate commercial identity from legal customer identity
  1. 01Canonical grain model
  2. 02Conformed customer identity
  3. 03Dataset contracts
  4. 04Metric contracts
  5. 05Freshness & period-close rules
  6. 06Activation model
Disciplines involvedData & IntegrationsRevenue OperationsSystems & CRM Architecture