Troubleshooting CDC Missing Data using Change Tables

[Sonja_Bauernfeind]

Problem Description

Data quality testing is showing missing data on the Target. The customer compared the source and the target confirming the missing data.

In this article we will suggest several approaches to debugging the missing events using CT tables.
 
The Task may already have CT tables enabled, and at some cases it makes sense to add Change Tables to the existing Task. However, if the user has a large number of tables, or if the storage demand is so high, another approach is to create a temporary Task adding only a subset of tables.
 

OPTION 1 – Task already has Change Tables

Having Change Tables may give us more information and enable faster analysis of the missing data. If the missing INSERT for example appears in the CT and not in the Target table, it could mean that the missing data is not related to the SOURCE.
 
In this case it may be related to the apply process, for example the Bulk or the Target itself.
 
The Change Tables by default have the “__ct” suffix which is configurable. Change Tables include header fields and the data for each change (or event):

• INSERTs
• DELETES
• UPDATES in the form of BEFORE and AFTER images (when supported).

The header fields enable the research of the data by providing the information about each change including the operation type, transaction ID and the timestamp of the original change (review the manual for more details).
 

OPTION 2 – Existing Task

 
Replicate allows you to add the Change Tables to an existing Task. If this option is feasible, you need to careful modify the Task:

• Make sure the Task is not processing a large batch of transactions.
• Select a quiet period to Stop the Task and perform the change.
• We always recommend testing this approach using a TEST machine, and if that is not possible go to OPTION 3.

To add CT to an existing table, follow these steps:

• Stop the Task.
• Click on the Task Settings -- Change Processing -- Store Changes Settings.
• Enable the Store Change Processing by click on the Icon.

The text near the icon should read: Store changes processing is ON.

• Create the missing tables. 

Click on Run -- Advanced Run Options then select the “Metadata only. Create missing tables and stop”
 
This will create all the __ct tables in the Target. Review the target database and confirm that all the __ct tables are generated.

• Resume the Task.

OPTION 3 – Create a Temporary CT Task

 
This option requires the user to create a new Task to debug the missing data. This Task will help analyze the missing data and determine if it always occurs or if it’s intermittent.

To create a temporary CT Task, perform the followings:

• Create a CDC only Task with Store Changes:

• Add the tables you want to investigate.

If the missing change is part of a transaction that includes several tables, you may want to add these tables.

• You may set the target schema to be different than the production tables. This will isolate these CT tables if you don’t plan to add them as part of the normal work flow.

To modify the owner:
Task Settings -- Metadata -- Target Metadata

• Start the processing from a specific timestamp. This timestamp should be around the time of the suspected missing events. Usually you would want to go back more in time, the reason if this change or changes are part of a long running transaction, we would want to capture the transaction from the start.

To start from timestamp, click on the Run drop-down then Advanced Run Options and select the “Tables are already loaded. Start process changes from”:
 

 Analyzing the Change Tables

 
The resulting CT tables will allow you and Qlik support to analyze the missing data. Always attach these results to the case in the support portal in parallel to your analysis.
 
Questions to ask:

• If the missing data is in the CT and not in the target table. We need to focus on the apply process.
• If the missing data does not exist in the CT and the target, then we need to focus on the Source.