Solved

Soft Deletes in Striim

  • 29 June 2022
  • 1 reply
  • 43 views

Userlevel 1
Badge

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?

icon

Best answer by john 29 June 2022, 19:46

View original

1 reply

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