Target field

What is a target field?

A target field is the column in the destination schema that receives a value once a source field has been mapped, transformed, or derived. It is where data lands after it leaves the source: a table in a data warehouse, a column in a semantic model, or a field in a system you write back into.

The part that trips people up is that the target schema is designed, not inherited. You decide what the target field is called, what type it holds, whether it may be empty, how much precision and scale it keeps, and what unit it is measured in. A source field describes where data comes from and how it happens to be stored. The target field describes how your organisation wants to use it, which is why target fields are often written down as a data contract.

A value reaches a target field through a mapping rule, usually after a data transformation or a derivation rule has changed it on the way.

Narrowing and truncation

The biggest risk with a target field is quietly losing information when a wide source value is squeezed into a narrower target.

In PostgreSQL, a numeric value with more decimal places than the target's scale is rounded to fit. If the digits to the left of the decimal point exceed the target's precision, you get an error instead. So NUMERIC(3,1) stores 23.57 as 23.6, but rejects 123.4 outright.

Truncation is often silent. An explicit cast to varchar(n) in PostgreSQL cuts an over-length string with no error, and in Azure Data Factory copy activity the allowDataTruncation setting is on by default, so a decimal source mapped to an integer target is cut down rather than blocked. A validation rule on the target field is what stops this before it reaches a report.

Many source fields, one target field

Mappings are rarely one to one. Several source fields often feed a single target field. When the same fact lives in more than one system, the mapping rule picks a winner: a COALESCE takes the first non-null value in a set order of precedence, so a customer email might come from the CRM first and fall back to the billing system only when the CRM value is empty.

One source field can also feed several target fields, as long as each keeps its own clear meaning. Recording which source fields feed which target is the backbone of data lineage, and it is the first thing you check when a source changes shape and schema drift breaks a load.

A worked example

A reporting model has one target field net_amount_eur DECIMAL(12,2) NOT NULL: the order value in euros, two decimals, never empty. Two systems feed it. The web shop stores shop_orders.amount_cents as an integer in cents; the point-of-sale system stores pos_sales.total as a double in euros. The mapping rule converts and coalesces them, with the web shop taking precedence:

net_amount_eur =
  COALESCE(shop_orders.amount_cents / 100.0,
           pos_sales.total)

Three target-field decisions are doing the work. The unit: cents must be divided by 100, or every web order lands a hundred times too high. The precision: a double such as 45.987 is rounded to 45.99 to fit DECIMAL(12,2). The nullability: because the field is NOT NULL, a row where both sources are empty fails the load instead of writing a blank amount.