Column Mappings¶

Column mappings enable CalcBridge to understand data from different sources with varying column headers. This guide covers what mappings are, how to create mapping profiles, apply mappings during upload, and manage your mapping configurations.

What Are Mappings?¶

Different data providers often use different column names for the same data:

Mappings tell CalcBridge how to interpret these different column names so your formulas and compliance tests work consistently regardless of data source.

How Mappings Work¶

flowchart LR
    subgraph Input["Source Data"]
        A["Current_Balance"]
        B["Rate"]
        C["Moody_Rating"]
    end

    subgraph Mapping["Mapping Profile"]
        D["Current_Balance -> Principal"]
        E["Rate -> Coupon"]
        F["Moody_Rating -> Rating"]
    end

    subgraph Output["Normalized Data"]
        G["Principal"]
        H["Coupon"]
        I["Rating"]
    end

    A --> D --> G
    B --> E --> H
    C --> F --> I

Mapping Profiles¶

A mapping profile is a reusable configuration that maps source columns to CalcBridge's standard column names.

Standard Column Names¶

CalcBridge uses a standard set of column names for CLO analytics:

Built-in Alias Profiles¶

CalcBridge includes built-in profiles for common data sources:

Creating Mapping Profiles¶

Via UI¶

1. Navigate to Mappings in the main menu
2. Click Create Profile
3. Enter a profile name and description
4. Add column mappings:
   • Source Column - The column name in your data
   • Target Column - The CalcBridge standard name
5. Click Save Profile

Via API¶

curl -X POST https://api.calcbridge.io/api/v1/mappings \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Provider X Format",
    "description": "Mapping for Provider X monthly data files",
    "mappings": {
      "Current_Balance": "principal",
      "Coupon_Rate": "coupon",
      "Moody_Rtg": "moodys_rating",
      "SP_Rtg": "sp_rating",
      "Final_Mat": "maturity_date",
      "GICS_Sector": "industry"
    },
    "transformations": {
      "coupon": {
        "type": "percentage",
        "multiply_by": 100
      },
      "maturity_date": {
        "type": "date",
        "format": "YYYY-MM-DD"
      }
    }
  }'

Mapping Options¶

Column Transformations¶

In addition to renaming columns, mappings can transform data:

Data Type Transformations¶

Value Mappings¶

Map specific values to standardized values:

{
  "transformations": {
    "moodys_rating": {
      "type": "value_map",
      "mappings": {
        "Aaa": "Aaa",
        "AAA": "Aaa",
        "Aa1": "Aa1",
        "AA+": "Aa1"
      }
    }
  }
}

Computed Columns¶

Create new columns from existing data:

{
  "computed_columns": {
    "market_value": {
      "formula": "principal * price / 100",
      "dependencies": ["principal", "price"]
    },
    "months_to_maturity": {
      "formula": "DATEDIF(TODAY(), maturity_date, \"M\")",
      "dependencies": ["maturity_date"]
    }
  }
}

Applying Mappings on Upload¶

During Upload¶

When uploading a workbook, you can specify a mapping profile:

curl -X POST https://api.calcbridge.io/api/v1/workbooks/upload \
  -H "Authorization: Bearer $TOKEN" \
  -F "file=@data.xlsx" \
  -F "mapping_profile=provider_x_format"

Auto-Detection¶

CalcBridge can automatically detect and suggest mapping profiles:

1. Upload a file without specifying a profile
2. CalcBridge analyzes column headers
3. Suggests matching profiles ranked by confidence
4. Select the best match or create a new profile

Preview Before Apply¶

Always preview mappings before applying:

{
  "source_columns": ["Current_Balance", "Coupon_Rate", "Moody_Rtg"],
  "mapped_columns": ["principal", "coupon", "moodys_rating"],
  "unmapped_columns": ["Internal_ID", "Notes"],
  "missing_required": []
}

Managing Mappings¶

Viewing Profiles¶

Navigate to Mappings to see all profiles:

Editing Profiles¶

1. Click on a profile to open it
2. Modify mappings as needed
3. Click Save Changes

Duplicating Profiles¶

Create a new profile based on an existing one:

1. Click the ... menu on a profile
2. Select Duplicate
3. Enter a new name
4. Modify as needed
5. Save the new profile

Deleting Profiles¶

1. Click the ... menu on a profile
2. Select Delete
3. Confirm deletion

Mapping Diagnostics¶

CalcBridge provides diagnostics to help troubleshoot mapping issues.

Schema Drift Detection¶

CalcBridge detects when source data schema changes:

{
  "schema_drift": {
    "new_columns": ["Additional_Field"],
    "missing_columns": ["Old_Field"],
    "type_changes": [
      {
        "column": "coupon",
        "expected": "number",
        "actual": "string"
      }
    ]
  }
}

Mapping Audit¶

View mapping audit information:

{
  "audit": {
    "total_columns": 25,
    "mapped_columns": 22,
    "unmapped_columns": 3,
    "transformation_applied": 8,
    "warnings": [
      "Column 'Notes' was not mapped"
    ]
  }
}

Validation Errors¶

Common mapping validation errors:

Best Practices¶

Naming Conventions¶

Use clear, descriptive profile names:

[Provider] - [Data Type] - [Version]

Examples:
- Bloomberg - Holdings - v2
- Internal - Monthly Report
- Trustee X - Quarterly Data

Documentation¶

Document your mappings:

1. Add descriptions to profiles
2. Note any special transformations
3. Record source data provider
4. Document any quirks or edge cases

Testing¶

Test mappings before production use:

1. Upload a sample file with the mapping
2. Verify all columns mapped correctly
3. Check transformation results
4. Validate calculated columns

Version Control¶

Maintain mapping versions:

1. Create new profiles for major changes
2. Keep old profiles for historical data
3. Document changes between versions

Mapping Hub¶

The Mapping Hub (/mappings) provides a central interface for managing all mappings.

Features¶

Workflow¶

flowchart LR
    A[Upload Sample] --> B[Auto-Detect]
    B --> C[Review Suggestions]
    C --> D[Edit Mappings]
    D --> E[Preview Results]
    E --> F[Save Profile]
    F --> G[Use in Upload]

API Reference¶

List Mapping Profiles¶

curl -X GET https://api.calcbridge.io/api/v1/mappings \
  -H "Authorization: Bearer $TOKEN"

Get Profile Details¶

curl -X GET https://api.calcbridge.io/api/v1/mappings/{profile_id} \
  -H "Authorization: Bearer $TOKEN"

Update Profile¶

curl -X PUT https://api.calcbridge.io/api/v1/mappings/{profile_id} \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Updated Profile Name",
    "mappings": { ... }
  }'

Delete Profile¶

curl -X DELETE https://api.calcbridge.io/api/v1/mappings/{profile_id} \
  -H "Authorization: Bearer $TOKEN"

Test Mapping¶

curl -X POST https://api.calcbridge.io/api/v1/mappings/{profile_id}/test \
  -H "Authorization: Bearer $TOKEN" \
  -F "file=@sample.xlsx"