Compose - Truncate DW target table when a Replicate source table is truncated

[Pierre_G]

How do I tell Compose to truncate a DW table when a source table is truncated. Setup = Qlik compose is sourced from Replicate Landing zone using change tables (__ct)

[shashi_holla]

Hi @Pierre_G 

Compose cannot truncate Datawarehouse tables when the source table in Replicate is truncated.  Compose tables have to be manually truncated.

[Pierre_G]

Is there a way to detect the source table truncate in Replicate and instruct to update a column or insert a row in a special table?  Then I can use Compose pre-ETL to truncate the table based on that.

[shashi_holla]

@Pierre_G 

If we were using Delete on the source, then we can use the soft delete operation and capture it in the change table.

Transformation - Operation Indicator - Archive Use... - Qlik Community - 1969998

But with Truncate this will not work.

[robertcur]

Hi Pierre_G,

I'm not 100% sure what you ask, but will answer what I understand.  If I understand the question.  You want the COMPOSE warehouse table to act in the same way as the REPLICATE table (or landing table).  

If that is correct,  and assuming the table is a TYPE 1 table,  then do a FULL RELOAD on the table each time you run compose.  This will keep the table the same as on REPLICATE right?  

If I understood incorrectly, please provide a little more info on the what and the why.

[Pierre_G]

The table is type 1 but I perform a CDC task in Compose, not a Full Reload.

I want to truncate the target table when Compose CDC detects a truncate DDL in the source table located in the replicate layer.

Replicate Screenshot of a truncate:

[robertcur]

Ok maybe something I have not came across yet.  In my experience the DDL change no Replicate does not automatically adjust Compose right.  I'm actually not sure how you are going to perform this.  Would like to play around with this though...

I will look around and post something should I find anything.

[Pierre_G]

1. The Qlik Replicate Global Rules may be a workaround.  Set a transformation column to identify at which record the truncate happened (timestamp). Let's say OLTP_TRUNCATE_DTTM. Then in Qlik Compose, a pre-etl that deletes the DW records if any of the __CT  records has a value in OLTP_TRUNCATE_DTTM.

or

2. Qlik Replicate to insert a record in a 'Control Table' specifying which table is truncated and when. QLik Compose ETL to delete the records.

I am not an expert in Qlik Replicate. Not sure how if it is feasible

[robertcur]

@Pierre_G , no 1 seems feasible.  Would like some feedback if you tried it though.  

No.2 might be an issue if you make use of "Soft Deletes" in warehouse as we do.  We do not physically delete anything, but rather flag it as deleted.  So then a delete in the Control Table, might not work.

Please keep us posted once you've test these.  

[Nanda_Ravindra · Accepted solution]

@Pierre_G  Compose does not detect truncate/delete from replicate. The Compose is built to load a data warehouse and typically data warehouses don't truncate/delete data. So, the compose table has to be manually deleted to address your use case (be aware that hard deletes can cause data integrity issues in the data vault if you are not careful).

Like Shashi mentioned, you can implement soft_delete in Replicate with a global transformation that adds a column and uses operation_indicator. But with Truncate this will not work.

The only other option is to drop and recreate your DWH.

Hope this helps!