Skip to main content
Solved

Soft Deletes in Striim

  • June 29, 2022
  • 1 reply
  • 153 views

Sweta Garai
Forum|alt.badge.img+1

How do I handle soft deletes in Striim? Does Striim performs an update process to mark some data as deleted instead of physically deleting it from a table in the database?

Best answer by john

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))))

View original
Did this topic help you find an answer to your question?

john
Forum|alt.badge.img
  • Community Manager
  • June 29, 2022

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))))


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings