Mapping rule

What is a mapping rule?

A mapping rule is one line in a source-to-target mapping: it states how a single value gets from a source field to a target field. That can be a plain copy, or it can reshape the value on the way across.

These rules live in a source-to-target mapping document, often called an STTM, and in the tools that run it: an ETL or ELT job, a dbt model, an SSIS package, or an Azure Data Factory copy activity. The rule is the readable version of one decision, something a business owner can check; the SQL or Power Query underneath just runs it.

The kinds of move a mapping rule makes

Most mapping rules take one of a few shapes:

• Direct move. The value passes through unchanged under a new name: customer_id becomes customer_number.

• Type conversion. A text field holding 31/12/2025 in day-month-year order becomes a real DATE. Azure Data Factory does this through an interim type.

• Code-set lookup. A source code is translated through a code set: status A, I, and B become Active, Inactive, and Blocked.

• Default. When the source is empty, the rule writes a fixed value, not a blank.

• Conditional route. If country is BE, use the enterprise number, otherwise the VAT number.

Reshaping a value this way is a data transformation; a mapping rule is the smallest documented unit of one.

A worked example: land_code to country_iso2

A legacy system stores country in a field called land_code using its own short codes: B for Belgium, F for France, D for Germany. The warehouse wants a country_iso2 column in ISO 3166-1 alpha-2 form: BE, FR, DE. The rule is a lookup against a small code set, kept in dbt as a seed, a version-controlled CSV:

land_code,country_iso2
B,BE
NL,NL
F,FR
D,DE

What teams forget is the value not in the table: a typo, or a country nobody planned for. A good mapping rule states the fallback: unmatched codes get written as ZZ, a slot ISO 3166 keeps aside for user-assigned use, and the row is flagged so data lineage and data quality checks can catch it. Without it, unknown countries land blank or fail the load. SSIS builds the same choice into its no-match and error outputs.

Mapping, derivation, and validation rules

Three rules sit side by side in the same mapping spec and get mixed up. A mapping rule moves and shapes a value that already exists in a source field. A derivation rule computes a new value that has no single source, like net_revenue = gross - discount - vat or an age from a birth date. A validation rule judges a value and returns a verdict: this VAT number matches the pattern, this country_iso2 is a known code, this row passes or fails.

Put plainly: mapping decides where a value goes and what shape it takes, derivation decides what a new value is, and validation decides whether a value is allowed through. A field can be mapped and then validated, while a derived field is computed rather than mapped at all.