Articles in this section

Introduction to Mapping

  • Updated
Follow

Mapping is a core concept in Etlworks that defines how data from a source is transformed and routed into a destination. It allows you to reshape, rename, and reformat fields during the data integration process, ensuring compatibility between different systems, formats, and schemas.

Why Mapping Matters

In most real-world scenarios, the source and destination data structures are not identical. Mapping gives you the flexibility to:

  • Convert column names and data types

  • Restructure hierarchical data (e.g., flatten JSON or nest flat records)

  • Apply transformations, formulas, or conditional logic

  • Add or remove fields dynamically

  • Set constants or default values

  • Control how data is loaded into the destination

Without mapping, you’d be limited to raw field-to-field transfers — which rarely meet business or technical requirements.

Where Mapping Happens

Mappings are defined within source-to-destination transformations in Etlworks. These transformations are part of a flow, and each includes:

  • A source: a database table, file, API response, etc.

  • A destination: a database, file, API endpoint, etc.

  • An optional source query and/or destination query

  • A mapping that determines how data fields and structures are aligned and transformed

Mappings can be:

  • Default (implicit, based on source metadata)

  • Manual (defined in the Mapping Editor)

  • AI-assisted (suggested mapping using GPT, initiated by the user)

Types of Mappings

Etlworks supports a variety of mapping patterns at both field and object level:

Field-Level Mapping Examples

  • 1:1 field mapping

    first_name → first_name

  • Renamed fields

    fname → first_name

  • Nested mappings

    user.address.street → address_line_1

  • Calculated fields

    full_name → {first_name} + {last_name}

  • Excluded fields

    (not present in mapping but excluded using exclude=true)

  • New (added) fields

    created_at → new java.util.Date() or hardcoded: "source": "", "destination": "status", "value": "active"

  • Constants or defaults

    country → 'USA' if not present in source

Object-Level Mapping

In addition to mapping individual fields, Etlworks also allows you to define source and destination object mappings, which control how records are routed between tables, files, or other containers.

These object-level mappings are configured in the transformation settings — not inside the field-level mapping editor.

This is especially useful when:

  • Working with wildcard objects (e.g., multiple files or tables)

  • Dynamically resolving object names using {tokens}

Object-Level Mapping Examples

  • Direct mapping by name

    Source: orders_2024

    Destination: orders_archive

  • Wildcard mapping

    Source: orders_*

    Destination: archive/orders_*

    (Each source object is mapped to a destination with the same name.)

  • Token-based dynamic mapping

    Source: {source}

    Destination: staging_{source}

    (Where {source} is replaced at runtime with the actual table or file name.)

Automatic vs Manual Mapping

Etlworks supports default, manual, and AI-assisted mapping. Understanding the difference is critical to controlling what data gets loaded and how it’s transformed.

Default Mapping (No User-Defined Mapping)

When no mapping is defined by the user:

  • Etlworks uses the source metadata as-is to create or populate destination objects.

  • No automatic field-to-field mapping is applied.

  • If the destination is a relational database, only columns from the source that already exist in the destination are included. Extra columns are excluded by default.

This allows flows to run with minimal configuration, but provides limited control over how data is transformed or loaded.

Manual Mapping

Manual mapping is explicitly created or edited by the user. It can take multiple forms:

  • Simple Mapping

    All source fields are mapped directly to destination using the same names (automatically generated by Etlworks).

  • Manual Per-Field Mapping

    User selects individual fields and maps them to destination fields, optionally applying transformations, renaming, nesting, or scripting.

  • AI Mapping

    The system suggests a mapping between source and destination fields using AI (GPT). This must be explicitly initiated by the user.

Manual mapping overrides default behavior.

Note: By default, if a field is not explicitly mapped, added, or excluded, it is still included in the destination. This can be changed using a flow-wide setting to load only explicitly mapped fields.

AI-Assisted Mapping

AI-assisted mapping uses GPT-based natural language understanding to suggest mappings between source and destination fields.

  • It must be explicitly triggered by the user inside the Mapping Editor.

  • It does not run automatically.

  • You can review and edit the generated mapping before saving.

What's Next?

Learn about Setting Source and Destination in Transformation.

Related articles