Map transformation columns

The Mapping step connects the columns a transformation's query returns with the columns of the target table. Every row on the left, under Source, is one of the query's columns; on the right, under Target, you attach the table columns it fills. The step sits between Dependencies and Validation, and it must pass its checks before you can save or move on. The full build, test and finalize flow is on Build, test and finalize a transformation.

Attach a target column

1. Open the transformation and go to Transformation > Mapping. The step reads Loading column mapping while its columns load.
2. Find the row of the column your query returns. Use Search source or target columns... to filter the list; when nothing matches, the step reads No columns match your search.
3. Click the field on the right side of the row and choose a column of the target table in the picker. Until you attach anything, the field reads Select source columns.
4. Attach as many columns as the row needs. A row can take several columns; a column of the table can be attached to one row only.
5. Clear anything the checks name. The arrow between the sides, the icons on attached columns and the save messages point at every problem; the sections below explain each one.
6. Click Apply to save the step, or Next to save and move on to Validation.

How the rows work

• Each row stands for one column the transformation's query returns, with its row number and type, and a badge when its state is New, Updated or Removed.
• A Removed row is a column the query no longer returns. It stays in the list, dimmed, with its picker disabled, and the checks ignore it.
• Rows sort with removed first, then new, the rest after; row numbers are fixed when the list loads, so a row keeps its number while you search.
• The rows are built from the transformation's query, so write and save it first; see Write and validate a transformation query.
• The step re-reads the available columns every time you open it, so the list stays current; your unsaved picks are kept.

The column picker

• The picker lists the columns of the target table. Search it with Search columns...; when nothing matches, it reads No columns found.
• A column that another row already uses is left out of the list, and picking it is refused with Column Already Mapped. The message names the column it is already mapped to.
• Choosing a column that is already attached to the row again detaches it.
• Mandatory columns carry a warning icon, so the ones that must be mapped stand out.
• The list keeps a fixed order: Snapshot Day first, then Snapshot Datetime and Snapshot Year, then the other columns, with Source ID and Delete Flag at the end.

The Direct switch

• Direct marks an attached column as insert-only: on diff patterns it is never updated.
• The switch does not appear on Fact tables, or on a column named Source ID.
• A new pick starts with the switch off, and the switch saves together with the mapping.

Check type compatibility

• Every pair is checked as you attach it. The arrow between the two sides colours by the row's state, and the tooltip beside an attached column names any problem; a pair that passes reads "Data compatibility validation passed".
• Blocking problems are refused with "* Type incompatibility:" followed by the column name. Examples: "Cannot cast negative values to unsigned type", "Impossible to cast between these types", "Cannot convert NULL value to non-Nullable type".
• Warnings do not block. Examples: "Fractional part will be lost during casting", "Precision loss may occur during casting", "Value might not fit in the target type range".
• To clear a blocking pair, attach a different column, or change the column's Data Type on the table first; see Define table columns.

What blocks the save

Apply and Next are refused until the step passes its checks:

• Every active row needs a column. An active row is one that the query returns and that is not Removed; each must have at least one column attached. The message is "* Every active column must have at least one mapping to proceed".
• Mandatory columns must be attached. While some are missing, the step shows Unmapped mandatory fields with their names, and the save is refused with "* Mandatory columns are not mapped:" followed by those names. Mandatory columns include Source ID.
• No pair may have a blocking type problem, as described above.

A transformation with the Diff Update or Diff Delete pattern skips both must-be-mapped checks; see Transformation patterns and load settings.

If the columns were mapped for you

• When the platform matched columns for you, the step shows a Columns automatically mapped prompt with "These columns were mapped for you. Finish mapping any missing fields, then confirm to save."
• Click Confirm to store the mapping.
• While the prompt is showing, clicking Next saves the mapping before moving on, even when you changed nothing; that save counts as the confirmation.

Save the mapping

• Apply saves the step and keeps you on it. The save replaces the whole mapping: what the step shows now is what the transformation keeps.
• Success shows "Column mapping saved successfully". If the save fails, the message explains why; when nothing comes back, it reads "Failed to update Transformation", or "Failed to register Transformation" for a transformation that has not been saved yet.
• Revert drops your unsaved changes.
• Next moves on to Validation; Run and monitor transformation tests covers what comes next. If the step has unsaved changes, the Unsaved Changes dialog asks first.

Questions, answered

Why is my save refused on the Mapping step?

The step must pass its checks first. Every active row needs at least one column on the right, mandatory columns of the table must be attached, and no pair may have a blocking type problem. The message names the problem it found; fix it and save again.

What does "Unmapped mandatory fields" mean?

Some columns of the target table are mandatory and nothing is attached to them yet. The banner lists them by name, and mandatory columns include Source ID. Attach each one to a row, then save again.

Can I attach the same column to two rows?

No. A column of the target table can be attached to one row only; the picker leaves out a column that another row already uses, and picking it is refused with Column Already Mapped. A row itself can take several columns.

Why does Snapshot Day come first in the picker?

The picker keeps a fixed order: Snapshot Day first, then Snapshot Datetime and Snapshot Year, then the other columns, with Source ID and Delete Flag at the end.

What does "Columns automatically mapped" mean?

The platform matched columns for you when the step loaded, so most pairs are already in place. Finish anything still missing, then click Confirm. If you save or move on first, that save counts as the confirmation.

Why is the Direct switch not shown everywhere?

It does not appear on Fact tables, or on a column named Source ID. Everywhere else it sits on each attached column and saves together with the mapping.

Why do Removed rows stay in the list?

A Removed row is a column the query no longer returns. It stays visible so you can see what changed: it is dimmed, its picker is disabled, and the checks ignore it. Removed rows sort to the top of the list.

Why do the row numbers stay the same while I search?

Numbers are fixed when the list loads, before the search filters it, so every row keeps its number while you work.

How do I fix a type incompatibility?

The icon next to the attached column and its tooltip name the problem, and the save is refused with "* Type incompatibility:" followed by the column name. Blocking messages cover pairs that cannot be cast; warnings, such as "Fractional part will be lost during casting", do not block. Attach a different column, or change the column's Data Type on the table first; see Define table columns.

Does saving send only the rows I changed?

No. The mapping saves as a whole: the save replaces the mapping with what the step shows when you click Apply. There is no partial save.

Why did the list refresh when I came back to the step?

The step re-reads the available columns every time you open it, so the list stays current; your unsaved picks are kept.