Dynamic Data Validation

Question

I want to validate the incoming data which will be in CSV format. I have Target table defined for each file. Now I want to develop a common pipeline which should validate the data based on the rule for each file.
Validation Rules - Not null, length and datatype. I can extract these validation information from target database dynamically using sql queries based on the file name. How do I merge this rules with incoming data ? My goal is to develop one pipeline and even if there are changes in database for new field addition or datatype change, I shouldn’t change my pipeline. my sql queries can pull the latest fields, datatype and other req information.
Basically i’m trying to verify the data in the file before I apply them to target table. So I capture error records before I perform my DML operation

philliperamos · Answer

Thanks. We’re thinking of doing something similar for the dynamic validation approach, as the way I described above is leading to huge memory problems.

tstack · Answer

Ksivagurunathan:

I want to validate the incoming data which will be in CSV format. I have Target table defined for each file. Now I want to develop a common pipeline which should validate the data based on the rule for each file.

The DataValidator snap is useful if the constraints are statically known.  But, since you’re trying to do this dynamically, we’ll need to find another way.

Ksivagurunathan:

I can extract these validation information from target database dynamically using sql queries based on the file name. How do I merge this rules with incoming data ?

Can you give an example of what these rules look like (preferably in the JSON format that would be returned by a SQL Select snap).

ksivagurunathan · Answer

Thanks for your quick response. Here is the example - “{SOURCE_COLUMN_NAME: COLUMN_NAME1:[{DATATYPE: NUMBER, MAX_LENGTH: null,PRECISION :25,NUMERIC_SCALE:7,NULLABLE:NO}] }”
“{SOURCE_COLUMN_NAME: COLUMN_NAME2:[{DATATYPE: VARCHAR, MAX_LENGTH: 50 ,PRECISION :null ,NUMERIC_SCALE:null ,NULLABLE:NO}] }”
We could change this to different format if required. I have challenge in joining this with actual data.

tstack · Answer

Ksivagurunathan:

We could change this to different format if required. I have challenge in joining this with actual data.

The simplest thing would be to collect all the schema documents together with a GroupByN snap and then use a Join snap to add the schema to all the documents.

Ksivagurunathan:

Basically i’m trying to verify the data in the file before I apply them to target table. So I capture error records before I perform my DML operation

Can I ask why you want to do the validation yourself instead of letting the database do the validation?  If the tables in the DB are already setup with the appropriate constraints, I would think that would be the most straightforward approach.

ksivagurunathan · Answer

we want to validate the data sooner than doing in our batch processing which loads data to table. We could create triggered task and use that to validate the errors in data immediately and that way we could reuse some of the validation code rather developing one for each and every file. Data validation rules are going to be similar for any delimited files.

Forum Discussion

Dynamic Data Validation

Recent Discussions

Pagination and nextCursor in header

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?