Transformations and quality rules: verify values, rows and ordering

Transformation components can change fields, values, row count and order. Before connecting a target, declare which properties must be preserved and which should change. A valid graph can still produce incorrect business results if keys, join cardinality or rule order are wrong.

The following table preserves the declared Transform and Quality configuration fields. These identifiers are component contracts, not an open programming language. Source and target configuration is described in Mapping components.

Transform and Quality catalog

Component / identifier	Category	Declared configuration
Row filter / `filter`	Transform	`field`; `operator` (equals, not_equals, contains, starts_with, ends_with, gt, gte, lt, lte, not_empty); `value`
Field selector / `select`	Transform	`fields`
Field mapper / `rename`	Transform	`mapping`
Calculated field / `derive`	Transform	`field`; `template`
Convert type / `convert`	Transform	`field`; `targetType` (string, integer, number, boolean, date, datetime); `outputField`; `onError` (fail, null, keep, default); `defaultValue`
Clean text / `clean`	Transform	`fields`; `operations`
Replace values / `replace`	Transform	`field`; `find`; `replacement`; `matchMode` (contains, exact, starts_with, ends_with); `caseSensitive`
Split field / `split`	Transform	`field`; `delimiter`; `outputFields`; `keepSource`; `trimParts`
Concatenate fields / `concat`	Transform	`fields`; `outputField`; `separator`; `skipEmpty`
Join / `join`	Transform	`joinType` (inner, left, right, full); `leftInputId`; `rightInputId`; `leftKey`; `rightKey`; `conditions`; `keyType` (auto, string, integer, number, boolean, date, datetime); `strategy` (auto, hash, sorted); `caseSensitive`; `trimStrings`; `nullsEqual`; `rightPrefix`
Default value / `defaults`	Quality	`field`; `value`; `when` (null_or_empty, null, empty)
Limit rows / `limit`	Transform	`count`; `offset`
Deduplicate / `dedupe`	Quality	`fields`
Row sort / `sort`	Transform	`field`; `direction` (asc, desc)
Group aggregate / `aggregate`	Transform	`groupBy`; `metric` (count, sum, avg); `field`; `outputField`
Quality check / `validate`	Quality	`field`; `maxNullPercent`
Required fields / `quality_required`	Quality	`fields`; `maxInvalidPercent`
Type check / `quality_type`	Quality	`field`; `expectedType` (string, integer, number, boolean, date, datetime); `allowEmpty`; `maxInvalidPercent`
Pattern check / `quality_pattern`	Quality	`field`; `pattern`; `allowEmpty`; `maxInvalidPercent`
Range check / `quality_range`	Quality	`field`; `minimum`; `maximum`; `inclusive`; `allowEmpty`; `maxInvalidPercent`
Allowed values / `quality_domain`	Quality	`field`; `allowedValues`; `caseSensitive`; `allowEmpty`; `maxInvalidPercent`

Projection, calculation and conversion

Field selector keeps the selected fields. Field mapper establishes an output schema by renaming fields; check collisions rather than letting distinct concepts share one name. Calculated field uses a text template: ${customer} reads a record field and ${param.region} reads a parameter. It is not an arithmetic expression and does not execute SQL or JavaScript.

Convert type accepts string, integer, number, boolean, date and datetime. Leaving output field blank replaces the source field; another name can retain the original. Error policy chooses fail, null, keep or default. Keeping an invalid original can make a column heterogeneous, so verify it before writing to a typed database.

Text normalization

Clean text applies ordered trim, collapse_whitespace, lower, upper and remove_accents operations. Normalize both sides of a join key consistently. Keep an original field when legal or business requirements require its exact spelling; a comparison field can be normalized separately.

Replace values distinguishes contains, exact, starts-with and ends-with matching, with case sensitivity. Split field has delimiter/output names and source/trim options. Concatenate fields controls separator, output name and skipping empty values. Review sample edge cases; these controls are not an arbitrary regular-expression program.

Rows, joins and aggregates

Filter operators differ from Workflow decision operators. Convert textual numbers before numeric comparisons. Test values exactly at a boundary as well as above/below it. Limit rows with offset is a controlled window, not acceptance of the full population.

Sort controls one field/direction. Aggregate offers count, sum or average and the declared group/value/output fields; do not assume undeclared median, percentile or multiple implicit metrics. Deduplicate uses configured match fields; verify which record remains rather than assuming a latest-update rule.

Join offers inner, left, right and full modes with explicit left/right parents, keys, key types and supported strategies. Case sensitivity, trimming and null equality affect matching. The initial collision prefix is right_. Two matching left rows and three matching right rows can multiply records. Test unmatched keys, unique matches, duplicates and nulls before aggregating.

Test quality controls step by step

Prepare id,name,amount with two valid records and one missing amount.
Connect Flat File input → Clean text → Convert type → Required fields → Flat File output.
Apply trim/upper to name, convert amount with Set null on error, and require id/amount.
Set Maximum invalid percent to zero when no invalid record is acceptable.
Validate and publish the laboratory definitions, then run once.
Expect a quality failure caused by the invalid synthetic record; do not accept it as a completed load.
Correct the input, run a new attempt and verify three accepted records with expected types and names.
Inspect partial destination effects before retrying when a remote target is involved.

Quality percentage thresholds are acceptance limits. They do not create an automatic quarantine output unless the design explicitly implements one. Continue with customer normalization, regional summary and expressions.