search

reapit / foundations

Foundations platform mono repo
57 stars 21 forks source link

Snowflake creating duplicate records on JNL table when marking as synchdel = 2 #11364

Open chestertons opened 1 month ago

chestertons commented 1 month ago

Instance CHT, for and example record,

select * from JNL where code = 'CAM231662' and register in ('2023-11-08 10:17:56.000000','2024-05-31 08:58:57.000000') order by register,synchrec;

This is particularly worrying, as synchdel = 2 appears to be related to GDPR requests, and means "delete this record immediately"

Additionally, the synchrec column is not, as I would expect, being updated on either relevant row. For most tables (though thankfully not this one) we create synch deltas based on the synchrec column.

_FIVETRAN_ID REGISTER ENTRYTYPE CODE SYNCHREC PRPCODE NEGCODE ENTRY TABLETYPE _FIVETRAN_DELETED _FIVETRAN_SYNCED _FIVETRAN_INDEX SYNCHDEL "k3/Ni9Bb4yFCHO89ZCLdxcXqu6c=","2023-11-08 10:17:56.000000000","AR","CAM231662","2023-11-08 10:17:56.000000000","","LPT","Sales Applicant Registered: [deleted by me ****]","app","false","2024-04-18 15:11:53.101000000 +00:00",\N,"0" "jwygVIEVgms9HwpzUpxnPAuClFw=","2023-11-08 10:17:56.000000000","AR","CAM231662","2024-05-31 08:59:40.000000000","","LPT","Sales Applicant Registered: [deleted by me ****]","app","false","2024-05-31 08:01:48.577000000 +00:00","20753","2" "thosVYmrgOw2ERen5y73TMRilsA=","2023-11-08 10:17:56.000000000","AR","CAM231662","2023-11-08 10:17:56.000000000","","LPT","Sales Applicant Registered: [deleted by me ****]","app","true","2024-05-31 08:01:48.577000000 +00:00","20752","0" "SULbnIkxnL/WxP1RPH4wW66ZdEQ=","2024-05-31 08:58:57.000000000","DA","CAM231662","2024-05-31 08:59:40.000000000","","CALM","Applicant archived: [deleted by me ****]","app","false","2024-05-31 08:01:48.592000000 +00:00","20907","2" "MbKoFWAC3qchSl+1wIyFcSrb2+0=","2024-05-31 08:58:57.000000000","DA","CAM231662","2024-05-31 08:58:57.000000000","","CALM","Applicant archived: [deleted by me ****]","app","true","2024-05-31 08:01:48.592000000 +00:00","20906","0"

github-actions[bot] commented 1 month ago

Thank you for taking the time to report a bug. We prioritise bugs depending on the severity and implications, so please ensure that you have provided as much information as possible. If you haven’t already, it really helps us to investigate the bug you have reported if you provide ‘Steps to Replicate’ and any associated screenshots. Please ensure any personal information from the production database is obscured when submitting screenshots. This issue will be reviewed in our weekly refinement sessions and assigned to a specific project board. We may also update the ticket to request additional information, if required. For more information on our processes, please click here

chestertons commented 1 month ago

This probably goes a little deeper

"synchdel = 1" and synchdel = 0 records are created on JNL_A, but nothing on JNL, so there's no actual JNL record that the action has been taken

select * from REAPIT_RAW.AURORA_CHT_RPS_CHT.JNL t where code = 'NOL140257' and register in ('2024-07-15 10:29:19.000000');

select * from REAPIT_RAW.AURORA_CHT_RPS_CHT.JNL_A t where code = 'NOL140257' and register in ('2024-07-15 10:29:19.000000');