Mapping Source fields to Destination fields

Overview

In Etlworks, it is possible to map SOURCE fields to DESTINATION fields, exclude fields, add fields to the DESTINATION which do not exist in the SOURCE, and more.

Field names

Etlworks can automatically read field names from the source and destination objects. It then populates the SOURCE and DESTINATION drop-downs. You can manually enter the field name if it is not available in the drop-down.

Read about metadata selector.

It is also possible to define a template for the SOURCE or DESTINATION so that Etlworks  won't have to connect to the actual data source.

If a field name is not available in the drop-down, click Clear Cache at the bottom of the screen. Etlworks will reconnect to the data object and attempt to reread field names. You can always manually enter the field name. 

Default Mapping

Default Mapping is a set of rules that determine how SOURCE fields are mapped to DESTINATION fields, regardless of whether an explicit mapping is defined. These rules apply in all cases unless overridden by specific settings.

No explicit Mapping

• If no explicit Mapping is defined, the DESTINATION fields will match the SOURCE fields exactly.

When explicit Mapping is defined

• If a SOURCE field is not part of the Mapping, it will be automatically added to the DESTINATION (this is the default behavior).
• A SOURCE field must be manually excluded to prevent it from appearing in the DESTINATION.
• The default mapping does not apply when using nested mapping. Read more about Nested Mapping.

Flow-Level Field Handling Settings

Additionally, two flow-level settings affect how fields are handled across all transformations in the flow. These settings can be accessed in the Flow’s Parameters tab:

• Error if Mapped Field is Missing in Source – If enabled, the flow generates an error when a field explicitly mapped in the Mapping does not exist in the source. If disabled, a warning is logged, and the flow continues.
• Handling Extra Fields in Source – This setting changes how source fields that are not in the Mapping are processed. Previously, any new columns appearing in the source, whether due to schema changes, evolving datasets, or unexpected data variations, were automatically included in the destination. This was the default behavior and how most users expected mapping to work.

With the introduction of this setting, users now have control over how extra fields are handled. Instead of always including new fields by default, the flow can be configured to:

1. Ignore extra fields – Any columns in the source that are not explicitly mapped will be ignored and not included in the destination.

2. Error on extra fields – If extra columns appear in the source that are not part of the Mapping, the flow will fail before running any transformations, allowing early detection of unexpected schema changes.

This is a major change from the previous behavior. In the past, extra fields were always added automatically unless the “ALTER TABLE” feature was disabled for database destinations. Now, users have the ability to enforce stricter mapping rules and prevent unintended data propagation.

Key considerations:

• If the setting remains unchanged (default behavior), extra columns will continue to be processed automatically, as before.
• If “Ignore extra fields” is enabled, unmapped columns will be excluded from the transformation.
• If “Error on extra fields” is enabled, the flow will stop execution if unexpected columns are found in the source.

Select field from the list

Use a metadata selector to select a field from the available fields for the specific source or destination Connection.

Exclude a field

A field must be manually excluded to not appear in the destination.

Select or enter the SOURCE field, and click the Exclude check box to the right.

Calculated fields

By default, a DESTINATION field gets its value from its corresponding SOURCE field. The value of any field can also be changed or calculated using JavaScript, Python, and SQL (if the destination is a relational database).

Select or enter the DESTINATION field, click the pen icon to the right, and enter a program in the scripting language to calculate the field's value.

Use scripting languages

• How to use JavaScript or Python to calculate a field's value.
• Examples of calculating field value using the scripting language
• How to use native SQL to calculate a field's value.
• How to use SQL to Lookup Fields' value.

Add a pair of source and destination fields

To add a new pair of SOURCE and DESTINATION fields, click +. Select or enter the SOURCE and DESTINATION field names.

Add a field to the destination that does not exist in the source

To add a field to the destination that does not exist in the source, click the add field, enter the DESTINATION field name, and click the Add Field checkbox. Continue by clicking the pen icon and entering JavaScript to calculate the field value.

Exclude a field from the Mapping

To exclude a field from manual Mapping, simply delete it. The system will fall back to its default Mapping.

Change the field data type

By default, the destination field gets its data type from the source field. To change the destination data type, click the Data type button, select the Data type from the drop-down, and if needed, enter a Database specific type (for example, varchar(255)). You can also specify if the field is nullable.

Reorder Fields

Drag to reorder files in the Mapping. 

If the field already exists in the source, it will retain its position. The only exclusion if the destination is the CSV file, in which case it is possible to configure the destination (TO) CSV Format to honor the order of fields in the Mapping.  

Create Mapping

Etlworks can automatically create a default Mapping by simply reading the fields from the source object and Mapping them to the same fields in the destination. You can then manually change the source and destination field names.

You can choose to Create regular (flat) mapping or Create mapping with nested destination:

Clicking Create Mapping overrides the current Mapping.

Remove Mapping

You can remove all manual Mapping by clicking Remove Mapping.

Multiple columns with the same name in the destination

When the destination is not a database, the mapping editor allows the creation of a destination document (for example, an Excel file) that has multiple columns with the same name. 

Example:

This feature is not supported when the destination is a relational database or cloud data warehouse, such as Snowflake. 

Work with nested datasets

When Etlworks reads a nested data object, it parses it and represents the fields in a tree-like structure:

Use Make this field a child of the one above and Move child field up one level to control the level of nestedness for a specific field. You can also drag and drop fields up and down.

Read more about nested mapping and other techniques for working with complex nested datasets.