CloverETL Data Quality

Licensed under CloverETL Data Quality package (see Product Comparison).

For detailed overview of rules see List of Rules.

The component is located in Palette → Data Quality.

Short Description

Validator validates data based on specified rules.

	Note
	To be able to use this component, you need a separate Data Quality license.

Component	Same input metadata	Sorted inputs	Inputs	Outputs	Each to all outputs	Java	CTL	Auto-propagated metadata
Validator	-		1	1-2

Icon

Ports

Port type	Number	Required	Description	Metadata
Input	0		Input data records to be validated.	Any
Output	0		Data records that passed the validation.	Based on Input 0. [1]
	1		Optional output port with data that failed to validate. [2]	Any
[1]  Metadata of validated fields can contain more specific data type than input. For example input metadata can contain a string field with date values and corresponding field on the first output port can have date as its field type. After validating that the string value is a date, Validator can convert the value to the date type. [2]  Metadata on the second output port can be enriched with fields containing details of validation failure. Available fields are listed in Error output mapping.

Validator Attributes

Attribute	Req	Description	Possible values
Basic
Validation rules	[1]	Setup of rules used by validation. See Validator rules editor
Validation rules URL	[1]	URL to external file describing validation rules. Use exclusively this field or previous one.	${PROJECT}/validator
Error output mapping		Field mapping for error output port. See Error output mapping
[1]  Either Validation rules or Validation rules URL must be filled in.

Details

Validator allows you to specify a set of rules and check the validity of incoming data based on these rules. Validation rules can check various criteria like date format, numeric value, interval match, phone number validity and format, etc.

The component sends validated data to the first output port. The first output port has the same metadata as the input port with the exception of validated fields that can have output metadata modified to a more specific data type.

Data that failed to validate are sent to the second output port. When metadata on the second output port are the same as the metadata on the input port, invalid records are automatically sent to this output port. Second output port can be enriched with details of validation failure.

Validator rules editor

Validator rules editor provides you with all the power and features needed to set up validation rules. There are two tabs enabling to set up validation rules using different approaches: Use the Rules tab providing graphical interface to validation rules setup or switch to the Code tab with text editor and type rules by hand in form of xml.

The Rules tab of the dialog is split up into three parts: Available rules , Active rules and Rule parameters.

Tab Code contains text editor for editing validation rules in XML form, options to import and export of the validation rules, and an option to return to initial state of validation rules.

Figure 60.8. Validator rules editor

Validation rules

Validation rules are the cornerstone of Validator. A validation rule is an evaluable condition that needs to be fulfilled to ensure that field being validated contains valid value. The evaluation of the condition if affected by corresponding environment settings.

	Example of validation rule
	Check field value to contain decimal number. The condition is for the input field value to be a decimal number. The evaluation of condition is affected by locale setting specifying number format.

For description of particular available validation rules see Available rules.

Active rules

Active rules pane contains a tree of active validation rules. The rules can be added to the tree from list of Available rules on the left site.

A group named as All rules is a root of the tree of active validation rules. If any of active validation rules is chosen, the details are of the rule settings are displayed in rule parameters below.

Figure 60.9. Validator - Active rules

	Important
	In case of more validation rules having the same output field, the value acquired from the last one is used.

	Tip
	The new active validation rule can be added also by pressing the + button above validation tree.

Select rule type

If you drag a field from metadata and drop it onto any of groups from active rules or between the rules of the group, Select rule type dialog will appear. The dialog enables user to choose available rule to be added into group of rules. The added rule will be applied to preselected metadata field.

	Tip
	Use filter to find the rule by name.

Rule parameters

Each validation rule is configurable by several parameters. The parameters are split up in to groups for better lucidity.

There are Basic parameters, Locale and format settings and Common parameters.

Basic parameters

Most of basic parameters are rule specific parameters. See Available rules.

Locale and format settings

Validation rules can be affected by locale and format settings. These settings are inherited from the parent group by default.

Common parameters

Common parameters are present for all rules.

Error output mapping

Error output mapping provides a setup of mapping of fields to optional second output port.

If second output port has same metadata as input port, no additional error output mapping is needed and the fields not passing the validation will be redirected to second output port. In this case the Validator works in the same way as Filter. See Filter.

Validator provides much more functionality than Filter. Validator enables you to get detailed information, why validation of particular record fails and error output mapping provides graphical interface to map fields with validation failure details to corresponding metadata fields on second output port.

Validation failure details from following fields can be used. The fields can be seen as an additional secondary input port and error output mapping enables you to set up output mapping for the fields like in the reformat component. See Reformat.

Figure 60.10. Validator - Error output mapping

Validator error codes

Validator error codes for particular available validation rules are listed in following table.

Available rules

Available rules contain all available validation rules, Filter enabling fast access to particular items and list of Input and output metadata for easy use.

The available validation rules are furthermore categorized to Groups, If - then - else, Basic rules, String rules, Assignment rules, User rules and Imports.

	Tip
	The rule can be added into list of active rules by double click on the rule in list of available rules or by drag and drop from list of available rules to desired place to tree of active rules.

Filter

To find desired available validation rule or metadata field start typing a rule name into the filter input field. The rules and metadata fields are filtered on the fly.

Input and output metadata

Input metadata contains a list of input metadata fields. Data type of the field is shown in square brackets after the field name. Fields from metadata can be assigned to any active rule by drag and drop from the list of metadata onto the active rule.

Groups

The rules can be grouped together into rule groups. Each group of rules can have user-defined name, the rule group including child rules can be enabled or disabled in the same way as a rule. The rule group can contain another rules or rule groups. Operator and Lazy evaluation settings can be set per rule group.

Validation result of a rule group will be computed from validation results of rules in this group and the selected Operator of the given group.

Operator: AND

All rules from the group need to be valid in order for the group of rules to be considered as valid.

Operator: OR

At least one rule from the group needs to be valid in order for the group of rules to be considered as valid.

Lazy evaluation: enabled

Lazy evaluation setting affects whether all rules will be evaluated in given group or if evaluation should skip rules that cannot affect the validation result of the whole group.

Default value is enabled. Does not continue evaluating all rules in given group once the result of the group is known. For example, as soon as a rule is evaluated as valid in an OR group, no more rules will be evaluated from this group because the result of the group is already known - group is valid.

Lazy evaluation: disabled

All rules in given group will always be evaluated.

Error reporting

By default, multiple error records can possibly be produced for single input record. Every rule evaluated as false will send one validation error record to error output port. Every rule evaluated as false in an OR group will produce one validation error record. Every rule evaluated as false in an AND group with lazy evaluation disabled will produce one validation error record.

By changing the Produce error message settings from by rules to by group, each group will only produce one validation error record, even if more than one rule evaluated the validated record as invalid. Additionally, Error message and Status code settings can be set to specify what values this record will contain.

	Note
	All groups is a root of tree of all validation rules. All above mentioned setting regarding to groups are valid for root group in the same way.

If - then - else

The If - then - else enables you to validate fields conditionally.

The validation rule consist of condition and two subtrees of validation rules to check. If the condition is met, then first subtree of validation rules is applied (the then branch). If the condition in not fulfilled, the second subtree of validation rules is applied (the else branch).

The condition can be a single rule or group of rules. The condition needs to return boolean value. If the condition contains only assignment rule(s), which does not return boolean value, the execution of graph fails. The condition itself works as a group - it can contain more rules as a child nodes.

The conditionally processed validation subtrees work as groups too - zero, one or more validation rules or groups can be assigned to the then or else.

The Else branch is optional, it can be empty or it can be omitted. User can delete the Else if the else branch is not needed.

Figure 60.11. Validator - If - then - else without else branch

	Rule usage example
	Input data contains fields type, weight and pieces. Type is type of cargo: bulk for bulk goods and piece for piece goods, weight is weight of cargo and pieces stands for number of pieces. If type is bulk check field weight to contain numeric value and field pieces must be empty. If type is not bulk check field pieces be a natural number a weight to be empty.

Compatibility

Validator is available since CloverETL 3.5.0-M2.

Since CloverETL 4.1.0 you can use Strict validation parameter of Locale and format settings.

See also