How does Compose for DW deals with deleted rows in the source db ?

[ThiebaudS]

Hi everyone,

I don't really understand how Compose for DW deals with deleted rows in the source database/landing zone.

If I delete some rows in my source database, the Qlik Replicate task will delete the rows in my landing zone. This is OK.

After that, if I run the "CDC ETL Set" in Compose, it will not detect any changes and the deleted rows will still be in my data warehouse tables, and therefore of course, still be in my datamart.

Can someone help me understand this behaviour ?

Thanks a lot.

Best regards,

Thiebaud

[Madhavi_Konda]

Hi Thiebaud,
Could you please answer the following?
What is your Compose for Datawarehouses version?
What are Source and target databases for this behavior?

Thanks,
Madhavi

[ThiebaudS]

Hi @Madhavi_Konda 

Thank you for your answer.

I'm using Compose 6.6.0.104, with an Oracle database as source, and Snowflake as target.

Best regards,

Thiebaud

[TimGarrod · Accepted solution]

Thiebud - you are correct that Compose does not automatically handle delete operations currently. 

 Since Compose is built to load a data warehouse and typically data warehouse don't delete data.     

However there are of course reasons for performing deletes and if you have that requirement, there is a methodology to support soft deletes and to then determine if you want to perform a hard delete (be aware that hard deletes can obviously cause data integrity issues in the data vault if you are not careful, and a best practice is to configure Replicate and Compose to handle soft deletes.)

We are in fact about to publish a how-to-guide soft deletes in this forum.  I can provide you with an advanced copy while its finalized.  Will DM you for your email address 

[ThiebaudS]

Thank you @TimGarrod 

[okuneva]

I have asked the same question to my friend and he told me to use cPanel

Why do people think that everyone uses cPanel? It's old geometry dash 

[barbm]

If you add a soft delete field using Global Transformations in Replicate (something like this: LandingIsDeleted =  COALESCE(operation_indicator(1, 0, 0),0) ), then the record will be flagged as 1 for deleted in the Landing database, which is the source for DW.  In Compose for DW, include that delete flag field for each entity and it will update accordingly.

[Mnaiosa]

we are about to publish a how-to guide for soft deletes in this forum. I can send you an advance copy while it is being finalized. puppet hockey