Data Mapper

Produce a complete field mapping specification between a source system and a destination system. This document is the definitive reference for transforming data from source to destination format. Every field mapping is explicit — no ambiguity about what transformation applies.

Source System Data Model

Document the source system's data model for the entities being synchronized:

Source system: [System name] — [version/API version] Extraction method: REST API / SQL query / CSV export / event stream

Source entity: [Entity name] (e.g., Policy, Client, Loan Application)

Field Name	Data Type	Nullable	Format / Valid Values	Example	Notes
policy_id	string	No	UUID format	a1b2c3d4-...	System-assigned, immutable
policy_number	string	No	Alpha-numeric, max 20 chars	POL-2026-001042	Human-readable
client_id	string	No	Integer as string	"10042"	References client record
effective_date	string	No	YYYY-MM-DD	"2026-01-15"	ISO 8601 date only
premium_amount	number	No	Decimal, 2 places	1250.00	USD, never negative
status_code	string	No	Enum: A, C, X, P, E	"A"	A=Active, C=Cancelled, X=Expired, P=Pending, E=Endorsed
lob_code	string	No	Enum: AU, HO, LI, CO, UM	"AU"	Line of business code
producer_npi	string	Yes	NPI format or null	"1234567890"	Null for direct business
notes	string	Yes	Free text, max 2000 chars		May contain special characters
created_at	string	No	ISO 8601 datetime with TZ	"2026-01-15T09:30:00-05:00"
modified_at	string	No	ISO 8601 datetime with TZ		Used for delta sync

Repeat this table for each source entity involved in the integration.

Destination System Data Model

Document the destination system's expected data structure:

Destination system: [System name] Write method: REST API POST/PUT / SQL INSERT/UPDATE / Dataverse record / SharePoint list item

Destination entity: [Entity name]

Field Name	Data Type	Required	Validation Rules	Foreign Key	Notes
PolicyId	GUID	No (auto-generated)			System assigns on creation
PolicyNumber	string(50)	Yes	Must be unique
ClientId	GUID	Yes	Must exist in Client table	→ Client
EffectiveDate	datetime	Yes	Must be >= 1990-01-01
ExpirationDate	datetime	Yes	Must be > EffectiveDate
PremiumAmount	decimal(18,2)	Yes	Must be >= 0
PolicyStatus	string(20)	Yes	Enum: Active, Cancelled, Expired, Pending
LineOfBusiness	string(30)	Yes	Enum: Auto, Homeowners, Life, Commercial, Umbrella
ProducerId	GUID	No	Must exist in Producer table if provided	→ Producer
Notes	string(4000)	No
CreatedDate	datetime	No (system-assigned)
LastModifiedDate	datetime	No (system-assigned)
ExternalSystemId	string(100)	No	Used for sync tracking		Store source policy_id here

Field Mapping Table

The core of this specification. Every source field is mapped to a destination field with a transformation.

Transformation type legend:

• DIRECT: Copy value as-is (with data type conversion only)
• LOOKUP: Translate a code value using a lookup table
• FORMULA: Derive the value through a calculation
• SPLIT: One source field → multiple destination fields
• CONCAT: Multiple source fields → one destination field
• CONST: Hardcoded constant value regardless of source
• DERIVED: Calculated from one or more source fields using business logic
• OMIT: Source field is not mapped to any destination field (confirm this is intentional)

#	Source Field	Destination Field	Transform Type	Transformation Logic	Null Handling
1	policy_id	ExternalSystemId	DIRECT	Copy as string	Error — source never null
2	policy_number	PolicyNumber	DIRECT	Copy as string	Error — source never null
3	client_id	ClientId	LOOKUP	Look up client in destination by ExternalSystemId = source.client_id. Use destination GUID. If not found: reject record, log error.	Error — required
4	effective_date	EffectiveDate	FORMULA	Parse ISO date "YYYY-MM-DD", convert to destination datetime with time 00:00:00 UTC	Error — required
5	effective_date + 365 days	ExpirationDate	DERIVED	ExpirationDate = EffectiveDate + policy term days. Term days comes from lob_code lookup: AU=365, HO=365, LI=365, CO=365.	Error — required
6	premium_amount	PremiumAmount	DIRECT	Convert number to decimal(18,2)	Default: 0.00
7	status_code	PolicyStatus	LOOKUP	See Status Code lookup table below	Error — required
8	lob_code	LineOfBusiness	LOOKUP	See LOB Code lookup table below	Error — required
9	producer_npi	ProducerId	LOOKUP	Look up producer in destination by NPI. Use destination GUID. If not found: set to null (not reject).	Default: null
10	notes	Notes	DIRECT	Copy as string. Truncate to 4000 chars if longer. Log truncation.	Default: null
11	(none)	CreatedDate	CONST	Do not set — destination system assigns.	N/A
12	created_at	(log only)	OMIT	Not stored in destination. Preserved in integration event log for audit.	—

Lookup Tables

Status Code Mapping

Source Code	Source Meaning	Destination Value	Notes
A	Active	Active
C	Cancelled	Cancelled
X	Expired	Expired
P	Pending	Pending
E	Endorsed (active with endorsement)	Active	Endorsed policies are Active in destination; endorsement detail stored separately
R	Rescinded	Cancelled	Treat as cancelled in destination
(any other)	Unknown	—	Reject record, log unknown code

Line of Business Code Mapping

Source Code	Source Meaning	Destination Value
AU	Automobile	Auto
HO	Homeowners	Homeowners
LI	Life	Life
CO	Commercial Lines	Commercial
UM	Umbrella	Umbrella
BO	BOP (Business Owners Policy)	Commercial
WC	Workers Compensation	Commercial
(any other)	Unknown	Reject — log for review

Producer NPI Lookup

Maintain a mapping table: source NPI → destination ProducerId (GUID). Built at sync startup, refreshed hourly.

If a producer NPI arrives that is not in the mapping table:

• Do not reject the policy record
• Set ProducerId to null (unassigned)
• Add the unknown NPI to a "Unknown Producers" tracking list for manual resolution

Transformation Logic Detail

For complex transformations, provide implementation-level detail:

Transformation #5: ExpirationDate Derivation

function deriveExpirationDate(effectiveDate: Date, lobCode: string): Date {
  const termDaysByLob: Record<string, number> = {
    'AU': 365, 'HO': 365, 'LI': 365, 'CO': 365, 'UM': 365,
    'BO': 365, 'WC': 365
  };
  
  const termDays = termDaysByLob[lobCode];
  if (!termDays) {
    throw new MappingError(`Unknown LOB code for term calculation: ${lobCode}`);
  }
  
  const expiration = new Date(effectiveDate);
  expiration.setDate(expiration.getDate() + termDays);
  return expiration;
}

String Truncation Logging

When a string field is truncated to fit the destination column limit:

if (sourceValue.length > maxLength) {
  integrationLog.warn('Field truncated', {
    field: 'notes',
    originalLength: sourceValue.length,
    truncatedTo: maxLength,
    policyId: record.policy_id
  });
  return sourceValue.substring(0, maxLength);
}

Unmapped Source Fields

These source fields have no destination mapping. Confirm with the client that discarding them is intentional:

Source Field	Reason Not Mapped	Business Confirmation
created_at	Destination uses system-assigned CreatedDate	Confirmed [date]
modified_at	Used for delta sync logic only, not stored	Confirmed [date]
internal_ref	Legacy field, no business value	Pending confirmation

Do not proceed with go-live if any unmapped field marked "Pending confirmation" is not resolved.

Output Format

Deliver as:

1. Source system data model table (all fields for each entity)
2. Destination system data model table (all fields for each entity)
3. Field mapping table (all rows with transformation type and logic)
4. Lookup tables (one table per code translation)
5. Complex transformation detail (pseudocode for non-trivial transformations)
6. Unmapped fields table (with confirmation status)
7. Null handling summary (which fields cause record rejection vs. applying a default)
8. Test cases derived from the mapping (for each lookup table: at least one test per source code; for each DERIVED field: at least two test cases including edge cases)