transformations:verify
Table of Contents
VERIFY DATA IN ANOTHER TABLE
Category: Transform / Advanced
Description
This action verifies the quality of data in another table and can accumulate verification results.
Use cases
Multiple Verify actions can be chained together to create data validation pipelines that perform various checks on the same dataset.
Use this action to confirm the data integrity of a dataset before passing it downstream. Apply cleaning actions and/or branch the dataset if data verification fails.
Action settings
| Setting | Description |
|---|---|
| Table | Select the table to perform verifications on. |
| Mode | Select the verification mode to perform. Options: General verification, Verify text values, Verify numbers, Verify dates, Verify value content, Verify column names, Custom rules. |
| Append results | Check on to accumulate all verification results within this action's dataset. |
| Columns | Select which columns to apply the verification mode to. Options: All columns, Selected columns (and select the desired columns), All except selected (and select the columns not to verify). |
General verification options
| Option | Description |
|---|---|
| Contains no duplicates | This verification fails if duplicate values are detected within a column. |
| Has non-empty or non-whitespace values | This verification fails if any empty or whitespace characters detected. |
| Contains no empty values | This verification fails if any empty characters are detected. Whitespace characters are not checked with this option. |
| Contains no errors | This verification fails if any cells in the selected columns are returning errors, and returns the error text. |
| Matches regular expression* | Enter a regular expression in the field to match values against. Verification fails if any values do not match the expression, and the values are returned. |
| Only contains/Doesn't contain any values from column | This option requires the Verify action to reside with an action chain that produces an incoming dataset. Select a column in the incoming dataset to compare to. If Only contains is selected, this verification fails for any values that do not appear in the selected column of the incoming dataset. If Doesn't contain any is selected, the verification fails for any values that do appear in the selected column of the incoming dataset. Note: this setting is case-sensitive. |
* Setting can be specified using a parameter.
Verify text values options
| Option | Description |
|---|---|
| Contains only text values | This verification fails for values that are not text values. |
| Contains no trailing whitespace | This verification fails for text values containing trailing spaces/whitespace. |
| Contains no leading whitespace | This verification fails for text values containing leading spaces/whitespace. |
| Isn't whitespace | This verifcation fails cells containing only spaces/whitespace. |
| Contains no linebreaks | This verification fails for text values containing linebreaks. |
| Contains no system (hidden) characters | This verification fails for text values containing system (hidden) characters. |
| Isn't in Uppercase/Lowercase | If Uppercase is selected, verification fails for values containing any capital letters. If Lowercase is selected, verification fails for values containing any lowercase letters. |
| Text length is less than* | Select a number to represent the text length. This verification fails if the text length is greater than or equal to this value. |
| Text length is greater than* | Select a number to represent the text length. This verification fails if the text length is less than or equal to this value. |
| Only contains/Doesn't contain…* | Enter characters to match against. If Only contains is selected, verification fails if values do not contain any of the characters. If Doesn't contain is selected, verification fails if values contain any of the characters. Note: This action is case insensitive. |
| Has at least…* | Enter the minimum number of words tested for. Verification fails if cell values have fewer than the number of words entered. |
| Has at most…* | Enter the maximum number of words tested for. Verification fails if cell values have more than the number of words entered. |
| Exceptions | Options: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values. |
* Setting can be specified using a parameter.
Verify numbers options
| Option | Description |
|---|---|
| Contains only numbers | This verification fails for values that are non-numeric. |
| Is not (specific number)* | This verification fails for values equal to the number entered. |
| Is (integer/fractional) | If Integer is selected, verification fails for all values with a fractional component. If Fractional is selected, verification fails for all whole number values. |
| Is (greater/equals)* | Enter a number to test against. If Greater is selected, verification fails for values less than this number. If Greater or equals is selected, verification fails for values less than or equal to the number. |
| Is (less/equals)* | Enter a number to test against. If Less is selected, verification fails for values greater than this number. If Less or equals is selected, verification fails for values greater than or equal to the number. |
| Exceptions | Options: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values. |
* Setting can be specified using a parameter.
Verify dates options
| Option | Description |
|---|---|
| Dates are | Select what format dates appear in. Options: Number dates or Text dates formatted as (and enter a valid date format). |
| Contains only dates | This verification fails for values that do not represent dates. |
| Later than (date)* | Select a date to test against. This verification fails for dates that occur prior to the selected date. |
| Earlier than (date)* | Select a date to test against. This verification fails for dates that occur after the selected date. |
| Has/Doesn't have the time part | If Has is selected, verification fails for values that don't include the decimal time part. If Doesn't have is selected, verification fails for values that include the date part. |
| Later than (days)* | Select a time range (days, weeks, months, years) and enter the number of time ranges to test against. This verification fails for dates that occur prior to the number of time ranges selected. |
| Earlier than (days)* | Select a time range (days, weeks, months, years) and enter the number of time ranges to test against. This verification fails for dates that occur after the number of time ranges selected. |
| Exceptions | Options: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values. |
* Setting can be specified using a parameter.
Verify value content options
| Option | Description |
|---|---|
| Content type | Select the Content type to test for. Options: Email, JSON, XML, URL, US/Canada phone number, or GUID/UUID. This verification fails for values that are not of the type selected. |
| Exceptions | Options: Allow empty values and Allow error values. Check either of these on to ignore empty and/or error values. |
Verify column names options
| Option | Description |
|---|---|
| Only contains/Doesn't contain (names from column) | This option requires the Verify action to receive a dataset from the previous action. Select a column from the incoming dataset. If Only contains is selected, verification fails for column names that are not found in the selected (incoming) column. If Doesn't contain any is selected, verification fails for column names found in the selected (incoming) column. |
| Only contains/Doesn't contain (characters)* | Enter a string of characters. If Only contains is selected, verification fails for column names that don't include all of the characters entered. If Doesn't contain any is selected, verification fails for any column names that include any of the characters entered. This action is case-sensitive. |
| There always exists column* | Enter a column name to search for. Verification fails if the column name does not appear in the selected table. The only return value for this option appears in the "Verification rule" column. |
| There never exists column* | Enter a column name to search for. Verification fails if the column name appears in the selected table. The only return value for this option appears in the "Verification rule" column. |
| All column names contain* | Enter a string of characters to search for. Verification fails for column names that do not contain this string. |
| No column names contain* | Enter a string of characters to search for. Verification fails for column names that contain this string. |
| Column count is less than* | Enter a number of columns to verify against. Verification fails if the table contains fewer columns than the value entered. The only return value for this option appears in the "Verification rule" column. |
| Column count is greater than* | Enter a number of columns to verify against. Verification fails if the table contains more columns than the value entered. The only return value for this option appears in the "Verification rule" column. |
* Setting can be specified using a parameter.
Custom rules options
Enter one or more if…then…* statements to test values against. Use column names within the selected "Table" field to build the if tests. Enter a text value that represents the if test's description for the then argument (as this value is written into the "Verification rule" column of the action's output.
* Setting can be specified using a parameter.
Remarks
For a majority of the options, you can define a Custom verdict (return Verification rule value) by clicking the blue pencil icon at the right of the option and entering the verdict text. The custom verdicts can be helpful, for instance, if you want to return validation results in other languages than English.
The "Verify data in another table" action can also be chained with the "Call with another table" action that can be used to build custom data validation rules.
transformations/verify.txt · Last modified: 2024/10/29 18:10 by dmitry