Error 390101 during CDC

[desmondchew]

I am replicating from MySQL into Snowflake. The table was successfully loaded. But during CDC, it failed and went into Error. I can't resume the task and need to reload each time. This is very painful. Could you please provide a fix on this?

Fatal error codes are: 390101 (provider_syntax_manager.c:3013) 00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Table error codes are: 904,40050 (provider_syntax_manager.c:2984) 00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Table error codes are: 904,40050 (provider_syntax_manager.c:2984)

00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Data failure error codes are: 100078,100072,2043,100069,100100 (provider_syntax_manager.c:2955)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Data error encountered (ar_odbc_util.c:1124)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Failed to execute bulk insert statement. 'INSERT INTO "SCHEMA"."EX29" ("col 1........col9") SELECT CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col1", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col2", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col3", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col4", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col5", null ) as VARCHAR(255)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col6", null ) as VARCHAR(200)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col7", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col8", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col9", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col10", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col11", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col12", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col13", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col14", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col15", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col16", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col17", null ) as VARCHAR(25)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col18", null ) as NUMBER(20,8)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col19", null ) as NUMBER(20,8)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col20", null ) as VARCHAR(20)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col21", null ) as VARCHAR(20)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col22", null ) as NUMBER(15,5)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col23", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col24", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col25", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col26", null ) as NUMBER(15,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col27", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col28", null ) as NUMBER(15,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col29", null ) as NUMBER(9,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col30", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col31", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col32", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col33", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col34", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col35", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col36", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col37", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col38", null ) as NUMBER(7,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col39", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col40", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col41", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col42", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col43", null ) as NUMBER(7,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col44", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col45", null ) as NUMBER(6,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col46", null ) as NUMBER(6,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col47", null ) as NUMBER(6,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col48", null ) as NUMBER(5,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col49", null ) as NUMBER(4,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col50", null ) as VARCHAR(4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col51", null ) as VARCHAR(4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col52", null ) as VARCHAR(3)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col53", null ) as VARCHAR(2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col54", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col55", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col56", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col57", null ) as VARCHAR(1)) FROM "SCHEMA"."attrep_changesACC417CB1D0C8E44" WHERE "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255' [1022509] (cloud_bulk.c:583)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: bulk_operations_applier_apply_updates: bulk_operations_applier_resolve_return_status(pBulkOperationsApplierInt, iStatus, pBulkApplyOperation, pBulkApplyOperation->bCachedEvent) failed (bulk_operations_applier.c:196)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]V: bulk_operations_applier_apply_updates:196: Failed (bulk_operations_applier.c:199)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: bulk_operations_applier_apply_operation: bulk_operations_applier_apply_updates(pBulkOperationsApplierInt, pBulkApplyOperation, pTableDesc, iRecordsCount, iStartSeq, iEndSeq) failed (bulk_operations_applier.c:354)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Bulk ROLLBACK ignored because auto_commit is true (cloud_bulk.c:499)

Thank you.
Desmond

[john_wang]

Hello Desmond, @desmondchew 

Thanks for reaching out to Qlik Community!

The blow lines do not mean the real errors, these messages are trace level information:

[TARGET_APPLY ]T: Fatal error codes are: 390101

[TARGET_APPLY ]T: Table error codes are: 904,40050

Would you please if you get any lines include "]E:", or "]W:" in the task log file?

thanks,

John.

[desmondchew]

I have masked the important information.

Can we know where's the error?

00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Data error encountered (ar_odbc_util.c:1124)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Failed to execute bulk insert statement. 'INSERT INTO "LZ_MERIDIAN"."EX29" ("LIN4","LIN3","PROD","LIN1","LotNumber","CustMtrlName29","ColemanPONumber","HazPackGroup","PROD29","PID729","AssignedInvoiceNumber","MRNNumber29","UNPRUOMTEXT","EXPL29","ECCN29","DeliveryOrderNumber","PNBR29","UNPR29","Allowance","FlashPoint","SubsidiaryRisk","NOUS29","GWGT29","NETW29","VOL29","FOBV29","MeasurmentValue","TotalInvoiceAmt","SKEY29","HARM29","NQT129","NQT229","USMC29","GWUM29","NUOM29","VUOM29","PUOM29","NOP129","UNUM29","HarmUOM","HarmUOM2","IMOClass","LicCgoExpDate29","TotalLicCurr29","GSEQ29","LINE29","ControlTemp29","NPL129","NOCT29","CONO29","ISOC29","CURR29","CurrentProc29","HZFG29","DFFG29","FL0129","FL0229") SELECT CAST ( IFF( "LZ_MERIDIAN"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col1", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col2", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col3", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col4", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col5", null ) as VARCHAR(255)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col6", null ) as VARCHAR(200)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col7", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col8", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col9", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col10", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col11", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col12", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col13", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col14", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col15", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col16", null ) as VARCHAR(30)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col17", null ) as VARCHAR(25)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col18", null ) as NUMBER(20,8)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col19", null ) as NUMBER(20,8)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col20", null ) as VARCHAR(20)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col21", null ) as VARCHAR(20)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col22", null ) as NUMBER(15,5)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col23", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col24", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col25", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col26", null ) as NUMBER(15,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col27", null ) as NUMBER(15,4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col28", null ) as NUMBER(15,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col29", null ) as NUMBER(9,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col30", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col31", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col32", null ) as VARCHAR(12)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col33", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col34", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col35", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col36", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col37", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col38", null ) as NUMBER(7,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col39", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col40", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col41", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col42", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col43", null ) as NUMBER(7,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col44", null ) as VARCHAR(10)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col45", null ) as NUMBER(6,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col46", null ) as NUMBER(6,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col47", null ) as NUMBER(6,2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col48", null ) as NUMBER(5,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col49", null ) as NUMBER(4,0)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col50", null ) as VARCHAR(4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col51", null ) as VARCHAR(4)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col52", null ) as VARCHAR(3)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col53", null ) as VARCHAR(2)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col54", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col55", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col56", null ) as VARCHAR(1)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col57", null ) as VARCHAR(1)) FROM "SCHEMA"."attrep_changesACC417CB1D0C8E44" WHERE "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255' [1022509] (cloud_bulk.c:583)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: bulk_operations_applier_apply_updates: bulk_operations_applier_resolve_return_status(pBulkOperationsApplierInt, iStatus, pBulkApplyOperation, pBulkApplyOperation->bCachedEvent) failed (bulk_operations_applier.c:196)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]V: bulk_operations_applier_apply_updates:196: Failed (bulk_operations_applier.c:199)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: bulk_operations_applier_apply_operation: bulk_operations_applier_apply_updates(pBulkOperationsApplierInt, pBulkApplyOperation, pTableDesc, iRecordsCount, iStartSeq, iEndSeq) failed (bulk_operations_applier.c:354)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]T: Bulk ROLLBACK ignored because auto_commit is true (cloud_bulk.c:499)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]E: Failed (retcode -1) to execute statement: INSERT INTO "SCHEMA"."EX29" ("LIN4","LIN3","PROD","LIN1","LotNumber","CustMtrlName29","ColemanPONumber","HazPackGroup","PROD29","PID729","AssignedInvoiceNumber","MRNNumber29","UNPRUOMTEXT","EXPL29","ECCN29","DeliveryOrderNumber","PNBR29","UNPR29","Allowance","FlashPoint","SubsidiaryRisk","NOUS29","GWGT29","NETW29","VOL29","FOBV29","MeasurmentValue","TotalInvoiceAmt","SKEY29","HARM29","NQT129","NQT229","USMC29","GWUM29","NUOM29","VUOM29","PUOM29","NOP129","UNUM29","HarmUOM","HarmUOM2","IMOClass","LicCgoExpDate29","TotalLicCurr29","GSEQ29","LINE29","ControlTemp29","NPL129","NOCT29","CONO29","ISOC29","CURR29","CurrentProc29","HZFG29","DFFG29","FL0129","FL0229") SELECT CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col1", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col2", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col3", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col4", null ) as VARCHAR(65536)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col5", null ) as VARCHAR(255)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col6", null ) as VARCHAR(200)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col7", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col8", null ) as VARCHAR(100)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col9", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col10", null ) as VARCHAR(50)), CAST ( IFF( "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" >= 122 and "SCHEMA"."attrep_changesACC417CB1D0C8E44"."seq" <= 255, "SCHEMA"."attrep_changesACC417CB1D0C8E44"."col11", null ) as VARCHAR(50)), CAST ( IF... [1022502] (ar_odbc_stmt.c:5038)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] (ar_odbc_stmt.c:5046)
00015944: 2024-07-08T03:21:44:327133 [TARGET_APPLY ]E: Failed to execute bulk insert statement. 'INSERT INTO "SCHEMA"."EX29"

[sureshkumar]

Hello @desmondchew 

The task failing with same "NULL result in a non-nullable column" issue, please open a support ticket to analyze further.

Regards,

Suresh

[Heinvandenheuvel]

>> [TARGET_APPLY ]E: RetCode: SQL_ERROR SqlState: 22000 NativeError: 100072 Message: NULL result in a non-nullable column [1022502] 

You have a NULL column coming in where that is not allowed. The root cause can be tricky to find.

Best way forward is probably to set the internal parameter keepCSVFiles on the Snowflake target. Then upon failure use something like Notepad++, a perl, python or PowerShell script to look for the empty column value.

The parameter emptyFieldAsNull is still set to the default False is it?

Another way is to ALLOW null, for a while for investigation purposes on prod, or do a shadow task to such table in DEV, See how far that gets. You may want to limit the batch size for a while.

Finally you could set up a global transformation for tables matching the suspect table name and check  $AR_M_SOURCE_COLUMN_DATA for NULL replacing with  in something recognizable like "BAD NULL" and look in the target table, all columns for that "BAD NULL" once a day or every hour. To make that query easy your can sometimes  concatenate all target columns and look for the match returning a PK, or just check each column in a single where clause.