Compose Inconsistency

[garyclark]

Hi Team,

We are seeing inconsistency while using compose, below is the scenario:

Inserted new record in source, replicate captured the change in CT tables and inserted new recording into our landing db. However when we ran the incremental work flow in compose the change in CT table is not picked up hence data is not loaded into dwh. I still see the record in CT table even after the incremental work flow ran successfully .

Repeated the same process but this time compose picked by new record and also the previous record which was not picked by earlier and loaded both the records into DWH. This is just one example there were many instances of these issues. Can you please let us know how this can be avoided? 

[aj96 · Accepted solution]

Compose uses max(seq_id) for each ct table before running the staging query. Maybe at the time of run the changes might not have been committed in ct resulting in not picking any records for the process.  

[TimGarrod · Accepted solution]

Hi @garyclark , as AJ96 said - the first thing Compose does in a CDC ETL Task is grab the max(seq) from the __CT table.   (When we process change data, Compose needs to grab a static set of data and load to the TSTG table). 

It then uses this to grab only those values that existed when the process started - 

 So its likely that data wasn't fully committed, or wasn't in the CT table when the Compose job ran. 

If you need to validate the value that Compose captured, you can enable  DEBUG level logging for the CDC task and view the ETL logs to get the number. 
To enable DEBUG logging - under the Designer, drill into the Data Warehouse / ETL management section of the UI.

Highlight the task , select Settings and change the log level to DEBUG (in the example, I'm changing the settings for just the Sales_Source_CDC task to DEBUG).  (This does not require generation of code).

Run the ETL. 

When complete - in the Monitor select the Task > History - select the run and Click"View Log"

Search for the appropriate MaxSeq_# value - and look for the log message that provides the seq number parameter

Review that number with the value of the records in the CT table.   I expect you'll find it will be lower than the CT records.

header__change_seq description - (https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/Change%20Tables/read_chang...) 

Hope that helps!!

Tim.