Change data Capture (CDC) is not working after the full load with Snowflake End Point

[Srini_Manne]

Change data Capture (CDC) is not working after the full load with Snowflake End Point and running with 90 hour latency. Diagnostics package enclosed.

[Steve_Nguyen]

1. the DP does not have the logs folder , so i do not see the task log for review.

2. task is set for backup only

3. enable source_capture ,, trace, you will see that task is trying to query for the backup log. most likely it is not available .

[SwathiPulagam]

Hi @Srini_Manne ,

As Steve mentioned we are not sure what is the scenario.

Please add these parameters and reload the task one more time:

Please add the below mentioned value to snowflake endpoint-->Advanced-->Additional ODBC connection properties: CLIENT_SESSION_KEEP_ALIVE=true;ABORT_DETACHED_QUERY=true;CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=900;

After reloading if you still see the Latency issue please create a support case for further analysis.

Thanks,

Swathi

[Srini_Manne]

Hi Swathi, We are using SF connector and not ODBC. Is your advice valid for SF Connector too?

[shashi_holla]

Hi @Srini_Manne 

Few of the basic checks for Snowflake endpoint latency:

1) Data volume and warehouse size

2) Does all the tables in the task have Primary key

3) The file size settings in the Snowflake endpoint Advanced tab. Increasing it from default 100 MB.

4) I checked your task and can add the setting mentioned by @SwathiPulagam in the "Additional ODBC connection properties:" as you using the default Snowflake ODBC driver.

Thank you,

[Srini_Manne]

We tried everything suggested by you and still loading is failing.

[Heinvandenheuvel]

>> We tried everything suggested by you and still loading is failing.

Hmm. So far  not a single bit of useful  information was provided. Therefor any suggestion so far is just a wild guess based on common issues. There is really no good reason other than dumb luck things would have changed. All suggestion are good efforts,  just like the diagnostic package was a good effort, but without the logs nobody knows what is really happening. The provided file StatisticsLog\metricsLog.txt shows taks failures, but no error details. Those would be in the task logs, and would have been on the GUI under Monitor.

Oddly (to me) the metricsLog.txt shows many NETEZZA target components, and just a few SNOWFLAKE. The problem description does not explain that either.

Looking at the task_status a 'start by timestamp' was attempted correct? Has a real full-load been tried recently? task_status  also shows some changes where read and committed and we see "applied_in_progress": 8096578. That somewhat suggest issues on the target side. I'd recommend switching PERFORMANCE and TARGET APPLY LOG LEVEL to TRACE until all is resolved, and also recommend creating a few minutes of log with TARGET_APPLY VERBOSE. Roll the log just before and immediate after!

My WAG? - LOB Lookup on the source during the target apply.

Good luck!

Hein

fresh start Tuesday, September 27, 2022 19:47:02.071
full start Tuesday, September 27, 2022 19:47:52.170
full done Tuesday, September 27, 2022 19:50:44.085
last start Wednesday, September 28, 2022 5:21:39.888

8,346,935,212 estimated rows.

[Dana_Baldwin · Accepted solution]

Hi @Srini_Manne 

I am assigned to the support case you opened. We still do not have the log files associated with the task. Are you seeing any error messages in the user interface?

I shared some "best practice" settings on the case, will copy them here also:

Max file size for target to 2000 MB
To increase the size of the batches sent to Snowflake, please edit the settings in Task Settings \ Change Processing \ Change Process Tuning:

Longer than (seconds) to 299
But less than (seconds) to 300
Force apply a batch when memory exceeds to 2000

To keep transactions in memory as much as possible (rather than disk):
Transaction offload tuning:
Total transactions memory size exceeds (MB) to 5000
Transactions duration exceeds (seconds) to 86400

Thanks,

Dana

[Bill_Steinagle]

Srini,

Thank you for the post to the Replicate Forums. For this issue you are having trouble with the CDC on the Salesforce Source as mentioned to better understand the issue you want to enable SOURCE_CAPTURE to VERBOSE using a Test Task with one Salesforce Table with the same Salesforce Source Endpoint to collect the Logs for the CDC issue with Salesforce. I would also advise to check and ensure you are not hitting any limitations as outlined in the Replicate User Guide as noted below.

Steps to collect the Logs for the CDC issue

- create a Test Task using the Salesforce Source Endpoint

- add one Table you have defined in the problematic Task to this Test Task

- CDC only Task and set the SOURCE_CAPTURE Logging to VERBOSE

-start the Task and the log created would be helpful to determine root cause

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/salesforce/salesforce_limitatio...

Bill