Transformations and quality checks

Choose by the intended effect

Use Row filter to remove rows, Field selector to remove columns and Field mapper to rename them. To change the exact value jorge to Jorge, use Replace values with field=name, find=jorge, replacement=Jorge, matchMode=exact and caseSensitive=true. No additional function is needed when that rule is already defined.

For name cleanup, use Clean text with trim, capitalize or title, according to the expected result. capitalize uppercases the first character and lowercases the remainder; title capitalizes each word. Calculated field interpolates text: LEFT(name,1) does not execute a function.

Quality contract

Quality includes Default value and Deduplicate, which modify data. The other six checks are gates: Quality check, Required fields, Type check, Pattern check, Range check and Allowed values. If invalid percentage exceeds the threshold, the whole execution fails. Otherwise every row passes, including invalid ones. They do not produce a rejected-row output. With zero threshold, one invalid row in nonempty input causes failure.

Transformations and quality

Row filter (filter)

Keeps rows matching one field condition and preserves all their columns and values.

Settings: field, operator, value.

operator: equals, not_equals, contains, starts_with, ends_with, gt, gte, lt, lte, not_empty.

Synthetic example:

{
  "field": "status",
  "operator": "equals",
  "value": "active"
}

Limits: equals and not_equals compare case-sensitive text. contains, starts_with and ends_with ignore case. gt/gte/lt/lte convert to numbers. not_empty excludes null and blank text.

Field selector (select)

Projects each row and its output schema onto only the configured existing fields, in configured order. Drops all other fields while keeping row count.

Settings: fields.

Synthetic example:

{
  "fields": [
    "First_name",
    "Last_name"
  ]
}

Limits: Does not transform values, rename fields or filter rows. At least one selected field must exist. Unexpected final fields require checking the executed revision, connected branch and downstream components.

Field mapper (rename)

Renames fields listed in mapping while preserving their values, unmapped fields and all rows.

Settings: mapping.

Synthetic example:

{
  "mapping": {
    "old_name": "new_name"
  }
}

Limits: Output names must remain unique. It does not replace values or drop unmapped fields; add Field selector when projection is needed.

Calculated field (derive)

Adds or replaces one field using literal text and ${field} or ${param.name} interpolation. Copies all other fields and rows.

Settings: field, template.

Synthetic example:

{
  "field": "label",
  "template": "ID-${id}"
}

Limits: It is not an expression engine: no arithmetic, LEFT, SUBSTR, slicing or initial capitalization. Output is text bounded to 4,000 characters.

Convert type (convert)

Converts a field to string, integer, number, boolean, date or datetime. An empty outputField replaces the source; a named output keeps the source and writes the converted result there.

Settings: field, targetType, outputField, onError, defaultValue.

targetType: string, integer, number, boolean, date, datetime; onError: fail, null, keep, default.

Synthetic example:

{
  "field": "amount",
  "targetType": "number",
  "outputField": "amount_number",
  "onError": "fail"
}

Limits: date requires YYYY-MM-DD; datetime uses ISO 8601. There is no custom date format. onError accepts fail, null, keep or default; numbers must be finite.

Clean text (clean)

Applies operations in the configured order only to fields. Preserves all rows and columns; unselected fields keep their values and types, and null stays null.

Settings: fields, operations. Type at least one letter in Operations to see available matches and choose an operation.

To transform jORGE into Jorge, configure:

{
  "fields": ["first_name"],
  "operations": ["trim", "capitalize"]
}

Order matters: trim removes surrounding whitespace before capitalization. Selected nonnull values become text; avoid capitalize when the remainder of a name must retain its original case.

Replace values (replace)

Replaces literal text in one field while preserving other columns and all rows. It is the direct choice for changing exactly jorge to Jorge.

Settings: field, find, replacement, matchMode, caseSensitive.

matchMode: contains, exact, starts_with, ends_with.

Synthetic example:

{
  "field": "name",
  "find": "jorge",
  "replacement": "Jorge",
  "matchMode": "exact",
  "caseSensitive": true
}

Limits: It does not use regular expressions. matchMode accepts exact, contains, starts_with, ends_with; caseSensitive controls case matching. This specific replacement does not capitalize arbitrary names.

Split field (split)

Splits text by a literal delimiter into outputFields. keepSource controls retaining the original field; trimParts trims pieces.

Settings: field, delimiter, outputFields, keepSource, trimParts.

Synthetic example:

{
  "field": "full_name",
  "delimiter": " ",
  "outputFields": [
    "first_name",
    "last_name"
  ],
  "keepSource": true,
  "trimParts": true
}

Limits: It does not extract characters by position. Splitting occurs at most one fewer time than the output count, leaving the remainder in the last field. Missing pieces become empty strings.

Concatenate fields (concat)

Joins values as text into outputField using separator; preserves source fields and all rows.

Settings: fields, outputField, separator, skipEmpty.

Synthetic example:

{
  "fields": [
    "first_name",
    "last_name"
  ],
  "outputField": "full_name",
  "separator": " ",
  "skipEmpty": true
}

Limits: It does not add numbers. skipEmpty skips blank or whitespace-only pieces; null becomes empty text.

Join (join)

Combines exactly two immediate inputs on equality keys. Supports inner, left, right, full; emits both schemas and uses rightPrefix for right-side name collisions.

Settings: joinType, leftInputId, rightInputId, leftKey, rightKey, conditions, keyType, strategy, caseSensitive, trimStrings, nullsEqual, rightPrefix.

joinType: inner, left, right, full; keyType: auto, string, integer, number, boolean, date, datetime; strategy: auto, hash, sorted.

Synthetic example:

{
  "joinType": "left",
  "leftInputId": "customers",
  "rightInputId": "orders",
  "leftKey": "id",
  "rightKey": "customer_id",
  "keyType": "auto",
  "strategy": "auto",
  "caseSensitive": true,
  "trimStrings": true,
  "nullsEqual": false,
  "rightPrefix": "right_"
}

Limits: Set valid leftInputId, rightInputId and keys. Multiple conditions use AND. Many-to-many matches multiply rows. Nulls do not match by default. Expansion limits and auto, hash, sorted strategies apply.

Default value (defaults)

Fills a field when null, blank or both according to when; preserves present values, other columns and all rows. It may add a missing field.

Settings: field, value, when.

when: null_or_empty, null, empty.

Synthetic example:

{
  "field": "name",
  "value": "Unknown",
  "when": "null_or_empty"
}

Limits: Zero and false are retained. It does not replace nonempty literal values; use Replace values for that goal.

Limit rows (limit)

Keeps a contiguous window of rows after offset, preserving columns and values.

Settings: count, offset.

Synthetic example:

{
  "count": 10,
  "offset": 0
}

Limits: count: 1–250,000; offset: 0–250,000. It does not sort: add Row sort first for a top-N by a field.

Deduplicate (dedupe)

Keeps the first row of each key combination with all its fields. Empty fields compares all input columns.

Settings: fields.

Synthetic example:

{
  "fields": [
    "customer_id"
  ]
}

Limits: Drops duplicates but does not merge values or automatically choose the latest record. Sort first if the retained first row matters. Comparison uses typed JSON values.

Row sort (sort)

Sorts rows by one field in asc or desc direction, preserving all columns and rows.

Settings: field, direction.

direction: asc, desc.

Synthetic example:

{
  "field": "amount",
  "direction": "desc"
}

Limits: Numerically convertible values sort numerically; other text ignores case. This is not multi-key sorting. Streaming requires a staging step.

Group aggregate (aggregate)

Produces one row per groupBy group, containing only grouping fields and the metric output. count counts rows; sum and avg aggregate one field.

Settings: groupBy, metric, field, outputField.

metric: count, sum, avg.

Synthetic example:

{
  "groupBy": [
    "region"
  ],
  "metric": "sum",
  "field": "amount",
  "outputField": "total"
}

Limits: It does not calculate between columns in the same row and has no min/max. sum/avg ignore values that cannot become finite numbers. Metric output is text. Empty groupBy groups all nonempty input.

Unpivot (unpivot)

Turns each column in fields into a row: keyField receives its original name and valueField its value. Keeps keepFields; when empty, retains the other input fields.

Settings: fields, keepFields, keyField, valueField, dropNulls.

Synthetic example:

{
  "fields": [
    "jan",
    "feb"
  ],
  "keepFields": [
    "id"
  ],
  "keyField": "month",
  "valueField": "amount",
  "dropNulls": false
}

Limits: Expands rows. Output names cannot overwrite existing columns. dropNulls removes only null, keeping zero, false and empty text. This is not pivot.

Run sequence (sequence)

Adds an integer sequence start + row index × step, preserving rows, fields and order.

Settings: outputField, start, step.

Synthetic example:

{
  "outputField": "sequence",
  "start": 1,
  "step": 1
}

Limits: Restarts each run; it is not a persistent or globally unique identity. step cannot be zero and output cannot overwrite an existing field. Safe integer bounds apply.

Field fingerprint (hash_fields)

Adds a SHA-256 hexadecimal fingerprint over selected field names and canonical typed JSON values. Preserves original data.

Settings: fields, outputField.

Synthetic example:

{
  "fields": [
    "id",
    "name"
  ],
  "outputField": "fingerprint"
}

Limits: Null and empty text differ. This is neither encryption nor anonymization. Input fields must exist and output cannot overwrite them; JSON depth is limited to 20 and numbers must be finite.

Partition rank (rank)

Adds a numeric position within partitionBy groups while retaining original row order. dense shares consecutive ranks; rank leaves gaps; row_number assigns unique positions.

Settings: partitionBy, field, direction, mode, outputField.

direction: asc, desc; mode: dense, rank, row_number.

Synthetic example:

{
  "partitionBy": [
    "region"
  ],
  "field": "amount",
  "direction": "desc",
  "mode": "dense",
  "outputField": "rank"
}

Limits: The ordered field must be finite numeric on every row; null, blank and boolean fail. Output cannot overwrite existing columns. Empty partitionBy treats all input as one group. Final output is not sorted.

Quality check (validate)

Checks one field for empty values. Fails execution when their percentage exceeds maxNullPercent; otherwise passes every row and field unchanged.

Settings: field, maxNullPercent.

Synthetic example:

{
  "field": "name",
  "maxNullPercent": 0
}

Limits: Null and whitespace-only text are empty; zero and false are not. The threshold is 0–100. It does not fill or remove values.

Required fields (quality_required)

Marks a row invalid when any required field is null or blank. Execution fails if the percentage exceeds maxInvalidPercent.

Settings: fields, maxInvalidPercent.

Synthetic example:

{
  "fields": [
    "id",
    "name"
  ],
  "maxInvalidPercent": 0
}

Limits: On threshold success, even invalid rows remain. It does not fill data or discard records.

Type check (quality_type)

Checks whether values can convert to expectedType without converting them. Preserves rows and values when the threshold is not exceeded.

Settings: field, expectedType, allowEmpty, maxInvalidPercent.

expectedType: string, integer, number, boolean, date, datetime.

Synthetic example:

{
  "field": "amount",
  "expectedType": "number",
  "allowEmpty": false,
  "maxInvalidPercent": 0
}

Limits: Use Convert type for transformation. allowEmpty controls missing values. Numeric text may pass and remain text.

Pattern check (quality_pattern)

Checks full-field matching against a safe regular expression. Preserves data when maxInvalidPercent is not exceeded.

Settings: field, pattern, allowEmpty, maxInvalidPercent.

Synthetic example:

{
  "field": "code",
  "pattern": "[A-Z]{3}",
  "allowEmpty": false,
  "maxInvalidPercent": 0
}

Limits: Safety limits apply to pattern. It does not extract, replace or filter values. allowEmpty controls missing values separately.

Range check (quality_range)

Checks finite numbers against minimum and/or maximum, with inclusive or exclusive bounds. Keeps data on threshold success.

Settings: field, minimum, maximum, inclusive, allowEmpty, maxInvalidPercent.

Synthetic example:

{
  "field": "age",
  "minimum": "18",
  "maximum": "100",
  "inclusive": true,
  "allowEmpty": false,
  "maxInvalidPercent": 0
}

Limits: At least one bound is required. It does not clamp or replace out-of-range values. allowEmpty controls missing values.

Allowed values (quality_domain)

Checks exact textual membership in allowedValues, respecting caseSensitive. Preserves data on threshold success.

Settings: field, allowedValues, caseSensitive, allowEmpty, maxInvalidPercent.

Synthetic example:

{
  "field": "status",
  "allowedValues": [
    "active",
    "inactive"
  ],
  "caseSensitive": true,
  "allowEmpty": false,
  "maxInvalidPercent": 0
}

Limits: The list cannot be empty. It does not map or replace values; use Replace values to transform them. allowEmpty controls missing values.

Execution compatibility

Generic streaming requires one linear path with a compatible source and target. Join and multi-input graphs need an execution path supporting that topology; do not change the algorithm merely to hide a compatibility error. Row sort, Group aggregate, Partition rank and quality gates may stage data before emitting results. Streaming does not mean unlimited memory or immediate output for every component.

Verify the result

Validate the graph, save and publish deliberately. Run a synthetic sample through authorized runtime and compare schema, rows, ordering and values. If CSV has extra fields after Field selector, check its saved settings, the Mapping revision used by the Session and downstream components: the selector does remove fields from both schema and rows.