Qlik Community

Qlik Compose for Data Warehouses

Discussion board for collaboration on Qlik Compose for Data Warehouses.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
ThiebaudS
Partner
Partner

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

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

Labels (2)
1 Solution

Accepted Solutions
TimGarrod
Employee
Employee

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 

View solution in original post

7 Replies
Madhavi_Konda
Support
Support

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
Partner
Partner
Author

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

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
Partner
Partner
Author

Thank you @TimGarrod 

okuneva
Contributor
Contributor

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

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.

royvfrank
Contributor
Contributor

All about baby care babyperiod.com your address is also waiting for you!