Field mapping consistency rules
Field mapping consistency rules check value mappings between two fields in the same table. Define source values and expected destination values, count inconsistent records within the check scope, and require a count of 0. The rules apply to fields within a table and do not perform cross-table join validation.
Define the mapping and check scope
Assume that user_code and user_name in the same table must follow the mappings below. The example checks records where user_code is 100 or 101.
| Source field user_code | Expected value of destination field user_name | Inconsistency condition in the example |
|---|---|---|
100 | Alice | user_name is not Alice or is NULL |
101 | Tony | user_name is not Tony or is NULL |
For example, a record with user_code='100' and user_name='Alice' matches the mapping. A record with user_code='100' and user_name='Tony' satisfies the inconsistency condition. The explicit conditions below also select records whose destination field is NULL.
Count inconsistent records with an assertion
Use row_count to count inconsistent records selected by filter, and set the pass condition to when = 0. The following example includes an inconsistency condition for each source value. It uses IS NULL to explicitly check for null destination values.
rules:
- assertion: row_count
name: Consistent user code and name mappings
filter: "(user_code='100' AND (user_name!='Alice' OR user_name IS NULL)) OR (user_code='101' AND (user_name!='Tony' OR user_name IS NULL))"
pass:
- when = 0filter defines both the source value scope and the inconsistency conditions, so this assertion does not check other source values. IS NULL is a condition selected for this example. It does not establish verified null handling for the system template. Do not pass fieldMap or valueMap as parameters to row_count.
This example is a rules fragment in a Spec configuration. For dataset configuration and other settings, see Data quality Spec configuration.
System rule template example
Use SYSTEM:fields:mapping_consistency_count:fixed to configure field mapping consistency checks. Specify field mappings in templateParameters.fieldMap and value mappings in templateParameters.valueMap. Both parameters are lists of mapping objects.
The following example maps source value 100 to Alice and 101 to Tony. It requires an inconsistent record count of 0.
rules:
- templateId: SYSTEM:fields:mapping_consistency_count:fixed
name: Consistent user code and name mappings
templateParameters:
fieldMap:
- user_code: user_name
valueMap:
- '100': Alice
- '101': Tony
pass:
- when = 0In fieldMap, each list item maps a source field to a destination field. In valueMap, each list item maps a source value to an expected destination value. Quote numeric keys to write them as strings.
Configuration items
| Configuration item | Applicable configuration | Description |
|---|---|---|
assertion: row_count | Assertion example | Counts inconsistent records selected by filter |
filter | Assertion example | Limits source values to 100 and 101, and explicitly checks for mismatched destination values or NULL |
pass | Assertion and template examples | when = 0 requires an inconsistent record count of 0. |
templateId | Template example | The template ID is SYSTEM:fields:mapping_consistency_count:fixed. |
templateParameters.fieldMap | Template example | A list of mapping objects. Each item maps a source field to a destination field. |
templateParameters.valueMap | Template example | A list of mapping objects. Each item maps a source value to an expected destination value. |