Icon

wkflw_​validate_​data_​infile_​wip

1. Source file checksFile-name parsing:<market>-<ss prefix>-<file type index>-<ingestion type code>-<storage area><market>-<ss prefix>-<file type index>-<ingestion type code>-<storage area>-cntSample data filesRO-MSD-2-DBBTCH-LZ-1-20220127174355.txtRO-MSD-2-DBBTCH-LZ-1-20220127174355-cnt.txtRO,MSD => Ref-table-1 (market, ss-prefix)2 => Ref-table-5 (File-Type-code)DBBTCH => Ref-table-2 (ingestion-Type-code)LZ => Ref-table-1(Storage-area)1 => Ref-table-3 (Layout-Type-code) 2. Data Dictionary checksif (Ref-table-3 (Layout-Type-code)) == 1Get the File type code in the reference file and compare it with the 3rd parameter of the file name.Acceptance Criteria for file-type code: The file type code should exist in the Ref table 5.Get the length of each fieldAcceptance Criteria for field-length: field length computed should not exceed field length in the Ref-table-5Get the type of each fields Acceptance Criteria for field-type: field type should match with field_type from the Ref-table-5. Get the order of each field Acceptance Criteria for field-order: field order should match in sequence with field_order from the Ref-table-5 Get the Primary key field Acceptance Criteria for primary-key: Primary-key /Composite key field should be Unique and Null value not acceptable 3. File Balance check 1. Read the data file using : a) CSV reader for delimited files b) Line/File Reader for fixed-length files 2.Read the number of records using Extract TableDimensions node. 3.Read the count file corresponding to the data file in Step1.. 4. Compare the numbers in Steps 2 & 3 Acceptance Criteria: a) CSV/Delimited: Record count (data file) -1 =Record count(count file) Filename Validation DD Check (WIP) File Balance Check Fetch data &references filename_s3_bkt_Arr[0] => Marketfilename_s3_bkt_Arr[1] => SS Prefixfilename_s3_bkt_Arr[2] => File Type Codefilename_s3_bkt_Arr[3] => Ingestion Type Codefilename_s3_bkt_Arr[4] => Storage Areafilename_s3_bkt_Arr[5] => Layout Type Codefilename_s3_bkt_Arr[6] => Created DatetimeThe Filename validation passes if all of the above are"True" Node 0S3 Bucket Path (Data File)File name pattern(Data File)LZ incomingdata file source connTemporary Local Pathto store downloads(Data Files)Node 57S3 Bucket Path (Reference File)File name pattern(Ref-Table-*)Temporary Local Pathto store downloads(Data Files)Node 62LZ incomingdata file source ConnectionNode 65Node 69Node 70Node 86PASS vs FAIL flaggingoutput flow variablefile_balance_check_pass_vs_failNode 93Node 94Node 95CSV layout type Transfer files fromSource File System meta_load_ref_data_to_knime_lz String Widget String Widget Connect to AWS String Widget meta_extract_split_filename_lz meta_infile_name_validation String Widget String Widget String Widget Transfer files fromSource File System Connect to AWS Table Reader Passing ref_table_3for DD checks meta_check_for_delimited_vs_fixedwidth meta_get_counts_cnt_n_data_files Rule EngineVariable meta_read_ref_table_3(layout type code)) Passing ref_table_3for DD checks Integer Widget Metanode 1. Source file checksFile-name parsing:<market>-<ss prefix>-<file type index>-<ingestion type code>-<storage area><market>-<ss prefix>-<file type index>-<ingestion type code>-<storage area>-cntSample data filesRO-MSD-2-DBBTCH-LZ-1-20220127174355.txtRO-MSD-2-DBBTCH-LZ-1-20220127174355-cnt.txtRO,MSD => Ref-table-1 (market, ss-prefix)2 => Ref-table-5 (File-Type-code)DBBTCH => Ref-table-2 (ingestion-Type-code)LZ => Ref-table-1(Storage-area)1 => Ref-table-3 (Layout-Type-code) 2. Data Dictionary checksif (Ref-table-3 (Layout-Type-code)) == 1Get the File type code in the reference file and compare it with the 3rd parameter of the file name.Acceptance Criteria for file-type code: The file type code should exist in the Ref table 5.Get the length of each fieldAcceptance Criteria for field-length: field length computed should not exceed field length in the Ref-table-5Get the type of each fields Acceptance Criteria for field-type: field type should match with field_type from the Ref-table-5. Get the order of each field Acceptance Criteria for field-order: field order should match in sequence with field_order from the Ref-table-5 Get the Primary key field Acceptance Criteria for primary-key: Primary-key /Composite key field should be Unique and Null value not acceptable 3. File Balance check 1. Read the data file using : a) CSV reader for delimited files b) Line/File Reader for fixed-length files 2.Read the number of records using Extract TableDimensions node. 3.Read the count file corresponding to the data file in Step1.. 4. Compare the numbers in Steps 2 & 3 Acceptance Criteria: a) CSV/Delimited: Record count (data file) -1 =Record count(count file) Filename Validation DD Check (WIP) File Balance Check Fetch data &references filename_s3_bkt_Arr[0] => Marketfilename_s3_bkt_Arr[1] => SS Prefixfilename_s3_bkt_Arr[2] => File Type Codefilename_s3_bkt_Arr[3] => Ingestion Type Codefilename_s3_bkt_Arr[4] => Storage Areafilename_s3_bkt_Arr[5] => Layout Type Codefilename_s3_bkt_Arr[6] => Created DatetimeThe Filename validation passes if all of the above are"True" Node 0S3 Bucket Path (Data File)File name pattern(Data File)LZ incomingdata file source connTemporary Local Pathto store downloads(Data Files)Node 57S3 Bucket Path (Reference File)File name pattern(Ref-Table-*)Temporary Local Pathto store downloads(Data Files)Node 62LZ incomingdata file source ConnectionNode 65Node 69Node 70Node 86PASS vs FAIL flaggingoutput flow variablefile_balance_check_pass_vs_failNode 93Node 94Node 95CSV layout type Transfer files fromSource File System meta_load_ref_data_to_knime_lz String Widget String Widget Connect to AWS String Widget meta_extract_split_filename_lz meta_infile_name_validation String Widget String Widget String Widget Transfer files fromSource File System Connect to AWS Table Reader Passing ref_table_3for DD checks meta_check_for_delimited_vs_fixedwidth meta_get_counts_cnt_n_data_files Rule EngineVariable meta_read_ref_table_3(layout type code)) Passing ref_table_3for DD checks Integer Widget Metanode

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: anant
Created at: 2022-02-03
On NodePit since: 2022-03-17
Last update: 2024-11-18
Created with KNIME version: v4.5.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!