Mapping Basics

Field mappings define how data transforms from source schemas to target schemas. This guide covers the fundamentals of creating and managing mappings in Datalinx AI.

Understanding Mappings

What is a Mapping?

A mapping connects source data to a target schema:

Mapping Components

Component	Description
Source Table	The table containing raw data
Target Table	The destination table in your target schema
Field Mappings	Column-level transformations
Decorators	Additional processing like identity resolution

Creating Mappings

Using the Visual Mapper

The visual mapper provides drag-and-drop mapping creation:

1. Navigate to Mappings in your workspace
2. Click Create Mapping
3. Select a source table from the left panel
4. Select a target table from the right panel
5. Drag source fields to target fields

┌─────────────────────┐     ┌─────────────────────┐
│ Source: orders      │     │ Target: fact_orders │
├─────────────────────┤     ├─────────────────────┤
│ ○ order_id         ─┼────▶│ ● order_id          │
│ ○ customer_id      ─┼────▶│ ● customer_id       │
│ ○ order_date       ─┼────▶│ ● transaction_date  │
│ ○ total_amount     ─┼────▶│ ● revenue           │
│ ○ status           ─┼────▶│ ● order_status      │
│ ○ created_at        │     │ ● created_timestamp │
│ ○ updated_at        │     │ ● modified_at       │
└─────────────────────┘     └─────────────────────┘

AI-Assisted Mapping

Let Datalinx AI suggest mappings automatically:

1. Click Auto-Map after selecting source and target tables
2. Review AI suggestions (shown with confidence scores)
3. Accept, modify, or reject each suggestion

AI Mapping Suggestions:
┌──────────────────────────────────────────────────┐
│ customer_id → customer_id         ✓ 98%         │
│ email_addr → email                ✓ 95%         │
│ first_name + last_name → name     ✓ 87%         │
│ created_at → created_timestamp    ? 72%         │
│ phone → phone_number              ? 65%         │
└──────────────────────────────────────────────────┘

Field Mapping Types

Direct Mapping

Map one source field to one target field:

{
  "customer_id": {
    "source": "cust_id",
    "transform": null
  }
}

Expression Mapping

Apply SQL transformations:

{
  "full_name": {
    "source": null,
    "transform": "CONCAT(first_name, ' ', last_name)"
  }
}

Constant Value

Set a fixed value:

{
  "source_system": {
    "source": null,
    "transform": "'CRM'"
  }
}

Conditional Mapping

Use CASE expressions:

{
  "customer_tier": {
    "source": null,
    "transform": "CASE WHEN lifetime_value > 10000 THEN 'Gold' WHEN lifetime_value > 1000 THEN 'Silver' ELSE 'Bronze' END"
  }
}

Common Transformations

String Operations

-- Concatenation
CONCAT(first_name, ' ', last_name)

-- Uppercase/Lowercase
UPPER(email)
LOWER(product_name)

-- Trim whitespace
TRIM(customer_name)

-- Substring
SUBSTRING(phone, 1, 3)

-- Replace
REPLACE(phone, '-', '')

Date Operations

-- Extract date from timestamp
DATE(created_at)

-- Format date
TO_CHAR(order_date, 'YYYY-MM-DD')

-- Date arithmetic
order_date + INTERVAL '30 days'

-- Extract parts
EXTRACT(YEAR FROM order_date)
EXTRACT(MONTH FROM order_date)

Numeric Operations

-- Rounding
ROUND(price, 2)

-- Arithmetic
quantity * unit_price AS total

-- Absolute value
ABS(balance)

-- Coalesce (handle nulls)
COALESCE(discount, 0)

Type Conversions

-- To string
CAST(order_id AS VARCHAR)

-- To number
CAST(amount_str AS DECIMAL(10,2))

-- To date
TO_DATE(date_string, 'YYYY-MM-DD')

-- To timestamp
TO_TIMESTAMP(datetime_string, 'YYYY-MM-DD HH24:MI:SS')

Handling NULL Values

COALESCE

Provide default values for NULLs:

COALESCE(middle_name, '')
COALESCE(discount_amount, 0)
COALESCE(phone, email, 'No Contact')

NULLIF

Convert values to NULL:

NULLIF(status, 'UNKNOWN')
NULLIF(amount, 0)

NULL Checks

Conditional NULL handling:

CASE WHEN email IS NULL THEN 'No Email' ELSE email END

Mapping Status

Each mapping has a status indicating its readiness:

Status	Description
Draft	Work in progress, not validated
Valid	All fields mapped and validated
Warning	Minor issues (unmapped optional fields)
Error	Critical issues (type mismatches, missing required fields)

Validation

Automatic Validation

Mappings are validated automatically:

Validation Results:
┌──────────────────────────────────────────────────┐
│ ✓ All required target fields are mapped         │
│ ✓ Data types are compatible                     │
│ ✓ SQL expressions are valid                     │
│ ⚠ Optional field 'middle_name' is not mapped   │
│ ✓ No circular dependencies                      │
└──────────────────────────────────────────────────┘

Testing Mappings

Preview transformation results before running pipelines:

1. Click Test Mapping on any mapping
2. View sample data transformation
3. Check for unexpected results

Test Results (5 sample rows):
┌────────────┬─────────────────┬──────────────────┐
│ customer_id│ full_name       │ email            │
├────────────┼─────────────────┼──────────────────┤
│ 1001       │ John Smith      │ john@example.com │
│ 1002       │ Jane Doe        │ jane@example.com │
│ 1003       │ Bob Johnson     │ bob@example.com  │
└────────────┴─────────────────┴──────────────────┘

Mapping Management

Versioning

Mappings are versioned automatically:

• Each save creates a new version
• View version history in the mapping details
• Restore previous versions if needed

Bulk Operations

Manage multiple mappings at once:

• Export: Download mappings as JSON
• Import: Upload mapping configurations
• Clone: Duplicate mappings for similar tables

Organizing Mappings

Use tags and descriptions to organize:

Mapping: orders_to_fact_orders
Description: Transform raw orders to analytics schema
Tags:
  - sales
  - fact-table
  - daily-refresh

Best Practices

Naming Conventions

1. Use descriptive mapping names
2. Include source and target table names
3. Add prefixes for related mappings

Documentation

1. Add descriptions to every mapping
2. Document complex transformations
3. Note any business rules or assumptions

Performance

1. Keep expressions simple when possible
2. Use appropriate data types
3. Test with realistic data volumes

Maintenance

1. Review mappings when schemas change
2. Test after updates
3. Clean up unused mappings

Related Documentation

• CTE Transformations - Complex multi-step transformations
• Decorators - Adding identity resolution and enrichment
• Expressions Reference - Complete SQL expression guide
• Target Schemas - Working with target schemas