Soft Deletes in Striim

Great questions! Striim has a capability called Continuous Query (CQ) which can be used to meet your specific requirement for Soft Deletes. Please find details below.

The below example of a Continuous Query option changes ‘DELETE’ to an insert and adds a field to userdata called ISDELETED with a value of 1 and a CDC_TIMESTAMP. This is just for reference , we can customize the way we needed.

Fyi we’re using Snowflake as an example target here. In append mode , updates/deletes are considered as inserts in Snowflake.

CASE
WHEN TO_STRING(META(s,‘OperationName’)) = “DELETE”
THEN
ChangeOperationToInsert(PutUserData(s,‘ISDELETED’,1,‘CDC_TIMESTAMP’, DADD(DNOW(),DHOURS(0))))

Here is a complete sample TQL for soft delete, inserts updates applied but deletes either marked as delete or not touched.

CREATE OR REPLACE CQ CaseStatementCQ
INSERT INTO CaseStatementCQStream
SELECT
CASE
WHEN TO_STRING(META(s,‘OperationName’)) = “DELETE”
THEN
ChangeOperationToInsert(PutUserData(s,‘emp_id_n’,0,‘emp_id_u’,“NA”,‘ISDELETED’,1,‘CDC_TIMESTAMP’, DADD(DNOW(),DHOURS(0))))
WHEN TO_STRING(META(s,‘OperationName’)) = “INSERT”
THEN
PutUserData(s,‘emp_id_n’,data[0],‘emp_id_u’,“NA”,‘ISDELETED’,0,‘CDC_TIMESTAMP’, DADD(DNOW(),DHOURS(0)))
ELSE
PutUserData(s,‘emp_id_n’,0,‘emp_id_u’,“y”,‘ISDELETED’,0,‘CDC_TIMESTAMP’, DADD(DNOW(),DHOURS(0)))
END
FROM MSSQLCDC_Source_Stream s;CASE
WHEN TO_STRING(META(s,‘OperationName’)) = “DELETE”
THEN
ChangeOperationToInsert(PutUserData(s,‘ISDELETED’,1,‘CDC_TIMESTAMP’, DADD(DNOW(),DHOURS(0))))