What date does CDC use to load a record?

[RonFusionHSLLC]

I have a number of replicated tables that get come across in __CT tables.  Typically, I run CDC at 1am every day.  That seems to pickup everything in the __CT tables with yesterday's date.

However, I have a set of tables that get updated at the beginning of my main compose task.  These updates generate __CT records for those tables, and a separate replication job lands them in the landing zone.  The header__timestamp is today for each of these.

The __CT from those tables does not get loaded until the next day.  It seems that all other __CT tables that have timestamps of today-1 load into the whse/marts, but those with timestamps of today do not.

How exactly does the CDC process work, what dates or ??? does it use to determine what to load.

My Compose task looks like this...

Load key value tables and generate cdc in landing zone (header__timestamp = today)

run CDC

run mart loads

I was under the impression that whenever CDC runs, it loads everything in the __CT tables and cleans them out; as of the runtime of the CDC load.  It certainly seems that it loads based on some dates since I see records that where created (in the __CT) today right before the CDC load that are still in the __CT table after the CDC load.

Ron

[TimGarrod]

Hi, 

When Replicate loads the __CT tables, the header_timestamp is based on when the change ocurred in the source sytem.    

Compose CDC jobs pickup whatever data is in the __CT table at the stop of the Task.   Internally, Compose gathers the MAX(change_seq_number) for the given __CT table and then uses 'WHERE header_change_seq < &&MAX) .   This ensures that Compose uses a 'static' set of CT data for error mart and TSTG data loading. 

If data is coming in from Replicate during the DWH process - it will remain in the CT tables, and not be picked up in that run.  (Again, Compose wants to ensure a 'consistent' set of changes are processed. 

If you are saying - at the start of Compose you update tables - (which are replicated via Replicate to a LANDING ) and within that Compose task you expect the CDC task to pickup those changes - its likely Compose is picking up the max seq number before those records have actually landed in the __CT tables.   

[RonFusionHSLLC]

I see...the task 'Load_Key_Tables' is where these records are created along with the __CT table updates.  So from what you described, if I moved the Load_key_Tables task it's own task, then ran the cdc and mart loads in a different task - CDC would pickup the changes that were introduced today?

[TimGarrod]

When you say "where these records are created along with the __CT table updates" - are you doing that with a pre-load step ? and are you generating your own '__CT table' records in that preload step ?
Or - is the flow what I suggested - you are updating tables, using Replicate to push those changes into __CT tables - and then trying to execute CDC ?
If the latter - then running CDC right after the update - you may still not get all the __CT records - it depends on how much volume Replicate is moving. You may want to add the replicate control table Replicate Status into that pipeline- https://help.qlik.com/en-US/replicate/May2023/Content/Replicate/Main/Control%20Tables/replication_st....
Instead, in your 'Update' or in a pre-load step in Compose - you can be checking the replication status table to see the SOURCE_TIMESTAMP_APPLIED - when it matches the value you expect - (in a loop perhaps in a stored procedure) - then just exit the proc, and allow the rest of the CDC task to execute.

[RonFusionHSLLC]

Nice...I'll check that out.  That might be the issue.

edit: checked target db...there is no attrep_status table, not sure what that means as Replication runs in real-time?

Ron

[RonFusionHSLLC]

The CDC is from Replicate (MS CDC - Sql Server).  The process that (loadkeytables in the diagram) creates records in a database that is replicated to the ODS.  There are several other processes that run after that, giving Replicate plenty of time to move all the new data.