Data Mapper

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)

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 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)

Lookup Tables

Status Code Mapping

Line of Business Code Mapping

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:

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)