--Insert Statement for DATA_QUALITY_RULE table. --Rule 1: Data Quality Rule to Check the Source Schema/Layout matches the expected layout. INSERT INTO DATA_QUALITY_RULE (DATA_QUALITY_RULE_ID, RULE_NAME, RULE_DESCRIPTION, RULE_TYPE_CODE, RULE_LEVEL_CODE, SCHEMA_NAME, TABLE_NAME, DATA_NAME, RULE_SCRIPT_TYPE_CODE, RULE_SCRIPT_TEXT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(1, 'SourceFile_SchemaValidation', 'Verify that the source file has correct layout', 'GATING', 'FILE', 'Invoice_schema.txt', NULL, NULL, 'COMP_THRE_VAL_EQ_P_E', 'Val_SchemaFile_Chk', 111, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Rule 2: Data Quality Rule to verify the same file has not been processed earlier. INSERT INTO DATA_QUALITY_RULE (DATA_QUALITY_RULE_ID, RULE_NAME, RULE_DESCRIPTION, RULE_TYPE_CODE, RULE_LEVEL_CODE, SCHEMA_NAME, TABLE_NAME, DATA_NAME, RULE_SCRIPT_TYPE_CODE, RULE_SCRIPT_TEXT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(2, 'DuplicateSourceFileValidation', 'Verify that the source file name is not duplicate', 'GATING', 'FILE', 'Invoice_schema.txt', NULL, NULL, 'COMP_THRE_VAL_EQ_P_E', 'Val_DuplicateFile_Chk', 111, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Rule 3: Data Quality Rule to verify that the Invoice Date for each record in Data File is in between MinInvoiceDate and maxInvoiceDate in Control File, write failed records to results table. INSERT INTO DATA_QUALITY_RULE (DATA_QUALITY_RULE_ID, RULE_NAME, RULE_DESCRIPTION, RULE_TYPE_CODE, RULE_LEVEL_CODE, SCHEMA_NAME, TABLE_NAME, DATA_NAME, RULE_SCRIPT_TYPE_CODE, RULE_SCRIPT_TEXT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(3, 'InvoiceDateCheckDetail', 'Verify that the Invoice Date for each record in Data File is in between MinInvoiceDate and maxInvoiceDate in Control File, write failed records to results table', 'GATING', 'COLUMN', 'dbo', 'Invoice', 'InvoiceDate', 'COMP_THRE_VAL_NE_F_F', 'Val_InvoiceDate_Chk', 111, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Rule 4: Data Quality Rule to provide summary results of passed/failed records for Invoice Date against expected Date Range. INSERT INTO DATA_QUALITY_RULE (DATA_QUALITY_RULE_ID, RULE_NAME, RULE_DESCRIPTION, RULE_TYPE_CODE, RULE_LEVEL_CODE, SCHEMA_NAME, TABLE_NAME, DATA_NAME, RULE_SCRIPT_TYPE_CODE, RULE_SCRIPT_TEXT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(4, 'InvoiceDateCheckSummary', 'This rule captures summary results of passed/failed records for Invoice Date against expected Date Range', 'PASSIVE', 'TABLE', 'dbo', 'Invoice', 'InvoiceDate', 'COMP_THRE_VAL_EQ_B_E', 'InvoiceDate_Chk_Summary', 111, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Rule 5: Data Quality Rule to provide summary results of passed/failed records with StoreID being null. INSERT INTO DATA_QUALITY_RULE (DATA_QUALITY_RULE_ID, RULE_NAME, RULE_DESCRIPTION, RULE_TYPE_CODE, RULE_LEVEL_CODE, SCHEMA_NAME, TABLE_NAME, DATA_NAME, RULE_SCRIPT_TYPE_CODE, RULE_SCRIPT_TEXT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(5, 'CheckStoreIDSummary', 'This rule captures summary results of passed/failed records with StoreID being null', 'PASSIVE', 'TABLE', 'dbo', 'Invoice', 'StoreID', 'COMP_THRE_VAL_EQ_P_F', 'StoreID_CHK', 111, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________ --Insert Statements for DATA_QUALITY_RULE_EXECUTE table. --Rule 1: The rule belongs to “Invoice” Data Domain as apparent from DATA_QUALITY_DATA_SOURCE_CODE set to “Invoice”. It is “PRE_STAGE” rule that means it should be executed before the Invoice data file is even loaded into an Invoice Stage table INSERT INTO DATA_QUALITY_RULE_EXECUTE ( DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, RULE_EXECUTE_STATUS_CODE, THRESHOLD_PASS_PERCENT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES ( 'Invoice', 1, 'PRE_STAGE', 'A', '100.00', 111, 'ETL_Invoice_DQ', 'ETL_SVC_ID', CURRENT_TIMESTAMP ); --Rule 2: The rule belongs to “Invoice” Data Domain as apparent from DATA_QUALITY_DATA_SOURCE_CODE set to “Invoice”. It is “PRE_STAGE” rule that means it should be executed before the Invoice data file is even loaded into Invoice Stage table. The rule is “Active” as RULE_EXECUTE_STATUS_CODE is set to “A”. The Threshold Pass Percentage is set to 100 means even if a single record does not meet the criteria the rule would fail. INSERT INTO DATA_QUALITY_RULE_EXECUTE ( DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, RULE_EXECUTE_STATUS_CODE, THRESHOLD_PASS_PERCENT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES ( 'Invoice', 2, 'PRE_STAGE', 'A', '100.00', 111, 'ETL_Invoice_DQ', 'ETL_SVC_ID', CURRENT_TIMESTAMP ); --Rule 3: The rule belongs to “Invoice” Data Domain as apparent from DATA_QUALITY_DATA_SOURCE_CODE set to “Invoice”. It is “POST_STAGE” rule that means it should be executed after the Invoice data file is loaded into Invoice Stage table. The rule is “Active” as RULE_EXECUTE_STATUS_CODE is set to “A”. The Threshold Pass Percentage is set to 100 means even if a single record does not meet the criteria the rule would fail. INSERT INTO DATA_QUALITY_RULE_EXECUTE ( DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, RULE_EXECUTE_STATUS_CODE, THRESHOLD_PASS_PERCENT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES ( 'Invoice', 3, 'POST_STAGE', 'A', '100.00', 111, 'ETL_Invoice_DQ', 'ETL_SVC_ID', CURRENT_TIMESTAMP ); --Rule 4: The rule belongs to “Invoice” Data Domain as apparent from DATA_QUALITY_DATA_SOURCE_CODE set to “Invoice”. It is “POST_STAGE” rule that means it should be executed after the Invoice data file is loaded into Invoice Stage table. The rule is “Active” as RULE_EXECUTE_STATUS_CODE is set to “A”. The Threshold Pass Percentage is set to 100 means even if a single record does not meet the criteria the rule would fail. INSERT INTO DATA_QUALITY_RULE_EXECUTE ( DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, RULE_EXECUTE_STATUS_CODE, THRESHOLD_PASS_PERCENT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES ( 'Invoice', 4, 'POST_STAGE', 'A', '100.00', 111, 'ETL_Invoice_DQ', 'ETL_SVC_ID', CURRENT_TIMESTAMP ); --Rule 5: The rule belongs to “Invoice” Data Domain as apparent from DATA_QUALITY_DATA_SOURCE_CODE set to “Invoice”. It is “POST_STAGE” rule that means it should be executed after the Invoice data file is loaded into Invoice Stage table. The rule is “Active” as RULE_EXECUTE_STATUS_CODE is set to “A”. The Threshold Pass Percentage is set to 90% that means 90% or more of records should have StoreID populated in order for the rule to pass. INSERT INTO DATA_QUALITY_RULE_EXECUTE ( DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, RULE_EXECUTE_STATUS_CODE, THRESHOLD_PASS_PERCENT, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES ( 'Invoice', 5, 'POST_STAGE', 'A', '90.00', 111, 'ETL_Invoice_DQ', 'ETL_SVC_ID', CURRENT_TIMESTAMP) --_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________ --Insert Statements for DATA_QUALITY_RULE_RESULTS table. --Metrics for Rule 1: Related to Source File Data validation INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(11, 'Invoice', 1, 'PRE-STAGE', 0, 'Invoice_schema.txt', 'Invoice_202001.dat', 'SCHEMA_VALIDATION', '5', 'GATING', 'FILE', 'P', 5, 0, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Metrics for Rule 2: Related to Duplicate Source File validation INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(12, 'Invoice', 2, 'PRE-STAGE', 0, 'Invoice_schema.txt', 'Invoice_202001.dat', 'DUPLICATE_FILE', '0', 'GATING', 'FILE', 'P', 0, 0, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Metrics for Rule 3: Related to Invoice Date Check INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(13, 'Invoice', 3, 'POST-STAGE', 300, 'DBO', 'Invoice', 'InvoiceDate', '2007-03-04', 'GATING', 'COLUMN', 'F', 0, 0, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Metrics for Rule 3: Related to Invoice Date Check INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(14, 'Invoice', 3, 'POST-STAGE', 400, 'DBO', 'Invoice', 'InvoiceDate', '2007-03-05', 'GATING', 'COLUMN', 'F', 0, 0, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Metrics for Rule 3: Related to Invoice Date Check INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(15, 'Invoice', 3, 'POST-STAGE', 500, 'DBO', 'Invoice', 'InvoiceDate', '2007-03-06', 'GATING', 'COLUMN', 'F', 0, 0, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --Metrics for Rule 4: Related to Invoice Date Summary Check INSERT INTO DATA_QUALITY_RULE_RESULTS (DATA_QUALITY_RESULT_ROW_ID, DATA_QUALITY_DATA_SOURCE_CODE, DATA_QUALITY_RULE_ID, RULE_PROCESS_STEP_CODE, TABLE_ROW_IDENTIFIER, SCHEMA_NAME, TABLE_NAME, DATA_NAME, DATA_VALUE, RULE_TYPE_CODE, RULE_LEVEL_CODE, PASS_FAIL_OVERRIDE_CODE, PASS_COUNT, FAIL_COUNT, SYSTEM_PARTITION_IDENTIFIER, CREATE_PROCESS_ID, CREATE_PROCESS_NAME, CREATE_RECORD_USER_NAME, CREATE_RECORD_SYSTEM_TIMESTAMP) VALUES(16, 'Invoice', 4, 'POST-STAGE', 0, 'DBO', 'Invoice', 'InvoiceDate', '', 'PASSIVE', 'TABLE', 'F', 2, 3, 'Invoice_202001', 1, 'ETL_Invoice_Load', 'ETL_SVC_ID', CURRENT_TIMESTAMP); --_____________________________________________________________________________________________________________________________________________________________________________________________________________________________________