Skip to main content

Validation Activity (2.14)

Validates the fields in a staging table and generates a report on any validation failures.

Introduction

A validation activity examines a staging table and validates the fields in it. For each field, the options depend on the data type and the function used for validation. For each field, you can also specify how to handle a validation failure.

The results of the validation checks are written to a validation report file.

Options

For each field to be validated, the following options apply:

  • Function Type —Specifies how to validate the field; see Validation Functions.

  • Validation Name —Specifies a short name for this field validation.

  • Result Field Name —Specifies a name for the report field that will store either 1 (if the field value passes the validation rule) or 0 (if it does not). It is useful for the name to indicate the field being examined, for example CheckItemCount.

  • Validation Failure —Specifies how the system should respond when it encounters a field value that fails this specific rule. A Fatal failure halts the recipe. A Warning failure logs a warning. Both kinds of failures are visible in the Workflow Inbox.

There are additional options depending on Function Type; see the next section.

Validation Functions

The available validation functions are as follows:

Note:

For each field, you can create only one validation of each validation type. You cannot apply multiple validations of the same type to a single field. For example, you can apply only one Not Null validation to a given field.

Code Table

Available for string and numeric fields. For this function, the field value fails validation if it does not match a value in a code table. Also specify:

  • Code Table Name —Specify the name of the code table to use.

  • Code Table Key Column —Specify the name of the key column in this table.

Custom SQL Expression

Available for all types of fields. For this function, the field value fails validation if a SQL predicate expression returns false. (You can, for example, compare the value in this field to the value in another field in the same record.) Also specify:

  • SQL Operator —Select a comparison operator.

  • SQL Expression —Enter a scalar SQL expression such as a field name or a constant.

DayOverDay

Available for numeric fields. Use this function to compare the current value of a field with its value from the previous data load. The validation fails when the difference exceeds a specified absolute or percentage tolerance. For this function type, also specify:

  • Validation Name —Type the name of the validation.

  • Validation Failure —Choose how the recipe responds if one or more rows fail this validation:

    • Warning — The recipe continues to run. Validation failures are recorded in the validation report for later review.

    • Failure — The recipe stops and starts a human workflow, allowing a user to review the validation report, abort the recipe, or modify the validation and retry the validation step.

  • Entity —Select the entity or calendar that defines the expected data load schedule. The selected entity determines which previous data load is used for comparison.

  • Days Type —Choose whether elapsed time is measured using business days or calendar days when identifying the previous comparison period:

    • Business Days —Counts business days, excluding weekends.

    • Calendar Days —Counts calendar days.

  • Tolerance Type —Choose how the acceptable difference between the current value and the previous value is measured:

    • Absolute Tolerance—Uses the numeric difference between the previous value and the current value.

    • Percentage Tolerance—Uses the percentage difference between the previous value and the current value.

  • Absolute Tolerance — (If Absolute Tolerance is selected) Specify the numeric difference that triggers validation failure. Decimal values are supported.

  • Percentage Tolerance (%)— (If Percentage Tolerance is selected) Specify the percentage difference that triggers validation failure. Decimal values are supported.

Does Not Match Regular Expression

Available for string fields. For this function, the field value fails validation if it does match the given regular expression. Also specify Regular Expression. This function uses the ObjectScript$MATCH functionOpens in a new tab.

Fixed Numeric Range

Available for numeric fields. For this function, the field value fails validation if it falls outside of a fixed range of numeric values. For the Fixed Numeric Range function type, also specify:

  • Minimum — Minimum permitted numeric value.

  • Maximum — Maximum permitted numeric value.

  • Tolerance —Specify the tolerance, or the amount by which the value is permitted to be outside of the specified range. This can be an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)

Glob Pattern Matching

Available for string fields. For this function, the field value fails validation if it does not match the given pattern. Also specify:

  • Glob Pattern —Specify a glob pattern.

  • Escape Character —Specify an escape character.

This function uses the InterSystems SQL %MATCHES predicateOpens in a new tab.

IRIS Pattern Matching

Available for string fields. For this function, the field value fails validation if it does not match the given pattern. Also specify Pattern . This function uses the InterSystems SQL %PATTERN predicateOpens in a new tab.

Matches Regular Expression

Available for string fields. For this function, the field value fails validation if it does not match the given regular expression. Also specify Regular Expression. This function uses the ObjectScript $MATCH functionOpens in a new tab .

Not Null

Available for all types of fields. For this function, the field value fails validation if it is null.

Numeric Change Within Range

Available for numeric, date, and time fields. For this function, the field value fails validation if the value obtained in this recipe run is too different from the value in the previous run of the recipe. Also specify Tolerance—the amount by which the value is permitted to be different from the previous value. This can be either an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)

If the field is a date field, the unit is a day. If the field is a datetime field, the unit is a second.

Stale

Available for string, time and numeric fields. This function is intended to verify data that may be out of date, or stale. This validation tracks whether a field's value changes across data loads. If the value remains unchanged for the specified number of days, the validation fails and the field is flagged as stale. This function is most useful for fields whose values are expected to change over time. To use this function type, also specify:

  • Validation Failure —Choose how the recipe responds if one or more rows fail this validation:

    • Warning —The recipe continues to run. Validation failures are recorded in the validation report for later review.

    • Failure —The recipe stops and starts a human workflow, allowing a user to review the validation report, abort the recipe, or modify the validation and retry the validation step.

  • Entity —Select the entity or calendar that defines the expected data load schedule. The selected entity determines which days are considered when evaluating whether data is stale.

  • Days Type —Choose how to count the elapsed time when determining whether data is stale:

    • Business Days —Counts business days.

    • Calendar Days —Counts calendar days.

  • Days Stale —Specify how long a value can remain unchanged before it is considered stale. Decimal values are supported. For example, 2.25 represents 2 days and 6 hours.

String Change Within Code Table

Available for string fields. This function is intended for the scenario where there is a code table that represents an ordered sequence such as a set of ratings, and the goal is to check that the field value has not changed too much within that sequence, since the previous successful load of data. For example, in this code table, the rating AAA may be represented as 10, the rating AA+ may be represented as 9, and so on. To use this function type, also specify:

  • Tolerance —Specify the tolerance, or the amount by which the value is permitted to be outside of the specified range. This can be an absolute value or a percentage. (To specify a percentage, include a percent sign at the end.)

  • Code Table Name —Specify the name of the code table to use.

  • Code Table Key Column —Specify the name of the key column in this table. The key values are treated as strings.

  • Code Table Value Column —Specify the name of the value column in this table. The values in this column must be numeric.

This function examines the previous and current values of the field, both of which are strings such as AAA and AA+, converts them to the corresponding numbers, and compares the numbers, taking the tolerance into account. If the numbers match or are within the tolerance amount of each other, the new field value passes validation.

Value Changed Since Previous

Available for all types of fields. For this function, the field value fails validation if the value is different from the value obtained in the previous successful run of the recipe. Use this rule if you expect the value to always be the same.

See Also

Reconciliation Activity (2.14)Next section
Previous sectionTransformation Activity (2.14)
FeedbackOpens in a new tab