[Feature Request]: Data value validation

[mattcasters]

What would you like to happen?

One of the more tedious things to solve are database errors about invalid data.
Here are some of the things you might encounter when using transforms like Table Output, Insert/Update, Update and others:

• String too long for varchar column
• Precision too high a decimal/float/double/number column
• Invalid UTF-8 character encountered
• ...

Usually it's encountered in one column of many in one row out of millions. Good luck figuring out where the issue occurs. Since these are all database specific issues I propose to create a new transform called Database value validation that would do the following:

• Specify a target database by name
• Specify a target schema and table name
• Specify the fields to validate against the target database columns. (with an "Enter mapping" button)
• At the start of execution it would gather information about the database, the table, the constraints, the column lengths, the encodings.
• For every row a check is made for all columns to see if there wouldn't be any potential issues.
• When a error occur we should be able to say exactly which columns fails, on which rows and why
• When errors occur we should be able to send all the errors for the offending rows to error handling
• That means we should get all the errors a database would send up front in one go.

Issue Priority

Priority: 3

Issue Component

Component: Transforms

[mattcasters]

I think we should have a list of checkboxes that we can enable or disable for all the validations that can be performed.