Overview
The Validate transformation checks every input record against a list of rules and sends it to one of two outputs. Records that pass every rule go to Valid. Records that fail at least one rule go to Rejected, with an extra column that lists every rule they failed. Use it to load clean records and write the rest to an exceptions file for someone to fix. Validate replaces the pattern of a Select that computes a flag, a Clone, and two Filters with opposite conditions. That pattern says that a record failed but not why. Validate says why, in the record itself.- Valid records keep the input’s fields unchanged.
- Rejected records keep the input’s fields and add a reject reason column (
reject_reasonby default). - Every input record lands in exactly one output, so the Valid and Rejected record counts add up to the input count.
Configuration
1
Drag a Validate component onto the canvas and connect its input.
2
Add a row to the Rules grid for each check. Pick the Field, the Rule, and fill in the rule’s Settings. A Custom condition has no field: click the edit button in its Settings cell, build the condition in the popup, and click Apply. Drag rows to change their order.
3
Optionally enter a Message for a rule, in the cell or, for a longer one, with the cell’s edit button. Leave it empty to use the generated message shown in the cell.
4
Optionally change the Reject reason column name.
5
Connect the Valid and Rejected connectors on the canvas to the next components.
Rules
Date bounds. From and To are inclusive. Each one is a date in
yyyy-MM-dd format, today, or today plus or minus a number of days (d), weeks (w), months (m) or years (y). For example, From today-90d and To today accept dates in the last 90 days. A To date includes that whole day. today is the date the job began running on the cluster.
Date fields. When the field is already a date (for example from a database source), leave the format empty and set From or To. A date field can only use Is required, Is a valid date, =, !=, >, >=, <, <= and Custom condition.
Comparisons. The comparison rules use the same operators as the Filter transformation. For =, !=, >, >=, < and <=, the value is a number, a text in single quotes ('N/A'), another field, or an expression, just like the right-hand side of a Filter condition. When a text field is compared with a number, it is compared as a number, so abc in price > 5 fails with (could not be checked). Two text fields are compared as text, so 9.5 is not less than 10: set the fields’ type to a number in the source schema to compare them as numbers.
How rules are applied
- Empty values pass every rule that has a field, except Is required. A null or blank value has nothing to check, so add an Is required rule for the fields that must have a value. A Custom condition is different: it has no field, so its condition alone decides, and an empty value usually makes the condition fail with
(could not be checked). To let an empty value pass, handle it in the condition: choose Match any and add the field with Is null (or Text is empty for blank text) next to the check. - Every rule is checked, even after one fails, so the reject reason lists every failure in rule order, separated by
;. For example:BIRTHDAY is required; SCORE must be a number between 0 and 100. - A value that cannot be checked fails the rule. If a rule cannot be evaluated for a value, for example
abcin a Number between rule, orprice <= costwhencostis empty, the record is rejected and the message ends with(could not be checked). - All rules must pass for a record to be Valid. To accept a record when either of two checks passes, use one Custom condition rule with Match any.
Connections
- The Valid output must be connected to exactly one component.
- The Rejected output is optional. When it is not connected, rejected records are dropped and package validation shows a warning.
- Valid and Rejected must go to different components. To send an output’s records to several places, connect it to a Clone component.
Previewing
Under the rules, the Valid and Rejected buttons choose which output the preview shows. Each previews the records that output receives, including the reject reason column for Rejected. The preview reads a sample of source records, so an output’s preview can be empty even though a full run fills it.Example: write bad records to an exceptions file
A student file must have a birthday and a gender ofM or F. Load the good records into the database and write the others to a CSV file that the school can fix.
1
Add your source, then a Validate component.
2
Add two rules: BIRTHDAY / Is required, and GENDER / Is one of with the values
M, F.3
Connect Valid to your database destination.
4
Connect Rejected to a file storage destination, for example a CSV file named
student_exceptions. The file gets every input column plus reject_reason.
Record 5 is Valid because an empty GENDER passes Is one of. Add GENDER / Is required to reject it too.
FAQ
Q: What is the difference between Validate and Assert? Assert fails the whole job when a record does not meet its conditions, with one message. Validate never fails the job: it routes each failing record to the Rejected output with the reason for every rule it failed. Q: What is the difference between Validate and Filter? Filter keeps the records that match a condition and drops the rest without saying why. Validate keeps both sets, and the rejected records carry the reason. Q: Why does validation warn “Rows that fail a rule will be dropped”? The Rejected output is not connected, so failing records are not sent anywhere. The package is still valid and runs. Connect the Rejected output if you need to keep those records. Q: Why does the editor say the input already has a column named reject_reason? The input already has a column with that name, for example because it comes from another Validate or from an exceptions file written earlier. Rejected records would then have the column twice, which cannot run. Change Reject reason column to another name, such asreject_reason_2.
Q: Can I check that a value exists in another table?
Not with Validate. Use a DB Lookup to bring in the reference value first, then check it with a rule.