markus41/plugins/lobbi-data-migration/skills/transform-builder

Transform Builder

Produce a complete ETL transformation specification for a data migration. Every source-to-destination field transformation is explicitly defined with input format, transformation logic, output format, and test cases. This document is used by the migration developer to implement the transformation pipeline.

Transformation Catalog Overview

List all transformations in the migration by type:

| Transformation Type | Count | Complexity | |--------------------|-------|------------| | Direct copy (with type conversion) | N | Low | | Lookup / code translation | N | Low-Medium | | String operations | N | Low | | Date format conversion | N | Low | | Numeric conversion | N | Low | | Conditional / derived | N | Medium | | Multi-field derivation | N | Medium-High | | Null handling | N | Low |

Transformation Type Library

DIRECT — Copy with Type Conversion

No transformation logic beyond data type conversion.

Pattern:

Source: [FieldName] ([SourceType])
Destination: [FieldName] ([DestType])
Transform: Cast [SourceType] to [DestType]

Examples:

Source: ClientID (int) → Destination: ExternalId (string)
Transform: ExternalId = ClientID.ToString()
Test: 10042 → "10042"

Source: PremiumAmount (decimal 18,2) → Destination: WrittenPremium (decimal 18,4)
Transform: Cast decimal to higher precision — no rounding needed
Test: 1250.00 → 1250.0000

LOOKUP — Code Translation

Translate a source code to a destination value using a lookup table.

Lookup table format:

Transformation: StatusCode → PolicyStatus

| Source Value | Destination Value | Notes |
|-------------|------------------|-------|
| A | Active | |
| C | Cancelled | |
| X | Expired | |
| P | Pending | |
| E | Active | Endorsed = Active + separate endorsement record |
| R | Cancelled | Rescinded treated as cancelled |
| null | Pending | Null status defaults to Pending; log occurrence |
| (other) | [REJECT] | Unknown code — reject record, log source value |

Implementation:

const STATUS_LOOKUP: Record<string, string | null> = {
  'A': 'Active',
  'C': 'Cancelled',
  'X': 'Expired',
  'P': 'Pending',
  'E': 'Active',
  'R': 'Cancelled',
};

function translateStatus(sourceCode: string | null): string {
  if (sourceCode === null) {
    logger.warn('Null status code — defaulting to Pending');
    return 'Pending';
  }
  const result = STATUS_LOOKUP[sourceCode];
  if (result === undefined) {
    throw new TransformationError(
      `Unknown status code: "${sourceCode}"`,
      'UNKNOWN_CODE',
      { field: 'StatusCode', value: sourceCode }
    );
  }
  return result;
}

Test cases:

| Input | Expected Output | Notes | |-------|----------------|-------| | "A" | "Active" | Happy path | | "C" | "Cancelled" | Happy path | | "E" | "Active" | Endorsed = Active | | "R" | "Cancelled" | Rescinded = Cancelled | | null | "Pending" | Null default | | "" | [REJECT — UNKNOWN_CODE] | Empty string is not a known code | | "Z" | [REJECT — UNKNOWN_CODE] | Unknown code | | "a" (lowercase) | [REJECT] | Case-sensitive — or add .toUpperCase() if source has mixed case |

STRING — String Operations

Text manipulation transformations.

Trim whitespace (apply to ALL text fields):

function trim(value: string | null): string | null {
  return value?.trim() ?? null;
}
// Apply at the start of every string field transformation

Name normalization (Title Case):

function toTitleCase(name: string | null): string | null {
  if (!name) return null;
  return name.trim()
    .toLowerCase()
    .split(' ')
    .map(word => word.charAt(0).toUpperCase() + word.slice(1))
    .join(' ');
}
// Test: "MARTINEZ ELENA" → "Martinez Elena"
// Test: "  smith  " → "Smith"
// Test: null → null

Concatenation (full name from parts):

function buildFullName(lastName: string | null, firstName: string | null): string | null {
  const last = lastName?.trim();
  const first = firstName?.trim();
  if (!last && !first) return null;
  if (!last) return first ?? null;
  if (!first) return last;
  return `${last}, ${first}`;
}
// Test: ("Martinez", "Elena") → "Martinez, Elena"
// Test: ("Smith", null) → "Smith"
// Test: (null, null) → null

String truncation:

function truncate(value: string | null, maxLength: number, fieldName: string): string | null {
  if (!value) return null;
  if (value.length > maxLength) {
    migrationLog.warn(`Field truncated: ${fieldName}`, {
      originalLength: value.length,
      truncatedTo: maxLength
    });
    return value.substring(0, maxLength);
  }
  return value;
}

DATE — Date Format Conversion

Normalize all dates to ISO 8601 format (YYYY-MM-DD for dates, YYYY-MM-DDTHH:mm:ssZ for datetimes).

Multi-format date parser (handles mixed format source data):

const DATE_FORMATS = [
  'MM/DD/YYYY',    // US format: 01/15/2026
  'M/D/YYYY',      // US short: 1/15/2026
  'YYYY-MM-DD',    // ISO 8601: 2026-01-15
  'DD-MMM-YYYY',   // 15-JAN-2026
  'YYYYMMDD',      // Compact: 20260115
];

function parseDate(value: string | null): Date | null {
  if (!value || value.trim() === '') return null;
  
  for (const format of DATE_FORMATS) {
    const parsed = dayjs(value.trim(), format, true); // strict mode
    if (parsed.isValid()) return parsed.toDate();
  }
  
  // Log unparseable date for manual review
  migrationLog.error('Unparseable date value', { value });
  return null; // or throw depending on whether the field is required
}

// Output format: always ISO 8601
function formatDateISO(date: Date | null): string | null {
  return date ? dayjs(date).format('YYYY-MM-DD') : null;
}

Test cases:

| Input | Expected Output | Format Detected | |-------|----------------|-----------------| | "01/15/2026" | "2026-01-15" | MM/DD/YYYY | | "2026-01-15" | "2026-01-15" | YYYY-MM-DD | | "15-JAN-2026" | "2026-01-15" | DD-MMM-YYYY | | "20260115" | "2026-01-15" | YYYYMMDD | | "" | null | Empty | | "13/45/2026" | null (error logged) | Invalid |

Timezone handling: All source dates stored without timezone are assumed to be in the firm's local timezone. Convert to UTC for storage in destination if destination uses UTC. Document the assumed timezone: US/Eastern (adjust for client).

NUMERIC — Numeric Conversion

// Currency: ensure 2 decimal places, non-negative
function parseCurrency(value: unknown): number {
  if (value === null || value === undefined) return 0; // or throw if required
  const n = typeof value === 'string' ? parseFloat(value.replace(/[$,]/g, '')) : Number(value);
  if (isNaN(n)) throw new TransformationError(`Invalid currency value: ${value}`);
  if (n < 0) throw new TransformationError(`Negative currency not allowed: ${n}`);
  return Math.round(n * 100) / 100; // round to 2 decimal places
}

// Test cases:
// "$1,250.00" → 1250.00
// "1250" → 1250.00
// "-50.00" → THROW (negative)
// null → 0.00
// "abc" → THROW (invalid)

CONDITIONAL — Conditional Logic

// Derive PolicyType from source fields
function derivePolicyType(
  isNewBusiness: boolean,
  isRenewal: boolean,
  policyNumber: string
): string {
  if (isNewBusiness) return 'New Business';
  if (isRenewal) return 'Renewal';
  // Fallback: derive from policy number format
  if (policyNumber.startsWith('NB-')) return 'New Business';
  if (policyNumber.startsWith('RN-')) return 'Renewal';
  return 'Unknown'; // Log this case
}

CONST — Hardcoded Constant

// Fields that require a fixed value regardless of source data
const CONSTANTS = {
  MigrationSource: 'Legacy-AMS-Migration',   // Track which records were migrated
  MigratedAt: new Date().toISOString(),       // Set at migration run time
  DataClassification: 'Confidential',          // All migrated records are confidential
};

Null and Default Value Handling Policy

Define the null handling decision for every destination required field that may have a null source:

| Destination Field | Required | Source Null Handling | Default Value | Rationale | |-----------------|----------|---------------------|---------------|-----------| | PolicyStatus | Yes | Source never null — throw if null | — | Required field; null indicates data error | | ProducerId | No | Set to null | null | Unassigned policies are valid | | ExpirationDate | Yes | Derive from EffectiveDate + standard term | EffectiveDate + 365 days | Can be calculated | | PremiumAmount | Yes | Default to 0 | 0.00 | Zero-premium records may be valid; log occurrence | | LastName | Yes | Reject record | — | Cannot have client without name | | BirthDate | No | Null accepted | null | Optional field |

Data Cleansing Rules

Cleansing operations applied before transformation to fix known source data quality issues (identified in data profiling):

| Issue | Field | Cleanse Rule | Records Affected | |-------|-------|-------------|-----------------| | Leading/trailing spaces | All text fields | .trim() applied universally | All records | | Mixed case in names | LastName, FirstName | Apply toTitleCase() | ~15% of records | | Multiple spaces in name | LastName | Replace / {2,}/g with single space | ~300 records | | SSN in Notes field | Notes | Replace SSN pattern \d{3}-\d{2}-\d{4} with [REDACTED] | 14 records | | Phone: non-digit characters | Phone | Strip all non-digits; keep 10 digits | All records | | Negative premium (error) | WrittenPremium | Reject record — log for business review | 3 records |

Transformation Sequence

When multiple transformations apply to one field, the order of operations matters:

For each source record:
  1. CLEANSE: Apply universal cleansing (trim all strings, normalize whitespace)
  2. VALIDATE: Check for critical null / invalid source values — reject record if critical
  3. LOOKUP: Apply code translations (status codes, LOB codes)
  4. DERIVE: Calculate derived fields (ExpirationDate from EffectiveDate, FullName from parts)
  5. FORMAT: Apply format conversions (dates, currency precision)
  6. CONST: Inject constant values (MigrationSource, MigratedAt)
  7. LOAD: Write to destination system
  8. LOG: Record success or failure in migration event log

Transformation Test Cases Summary

For every transformation in the catalog, include at minimum:

• 1 happy path (typical valid input)
• 1 null/empty input test
• 1 edge case (boundary value, special character, maximum length)
• 1 invalid input test (expect rejection or error)

Collect all test cases into a test data file that the migration developer runs against the transformation functions before executing against real source data.

Output Format

Deliver as:

1. Transformation catalog overview table (type counts)
2. Full transformation specifications (one section per transformation using the templates above)
3. All lookup tables (formatted tables, one per code field)
4. Null and default value handling policy table
5. Data cleansing rules table
6. Transformation sequence diagram (ordered steps)
7. Test case table (all fields: transformation, input, expected output, test category)
8. Implementation notes for developer (language/library recommendations, performance considerations for large datasets)