topic Apply Latency increasing at Snowflake on Azure in Qlik Replicate

Apply Latency increasing at Snowflake on Azure

raghujakkani — Thu, 28 Sep 2023 16:24:42 GMT

Hello,

Task configured with full Load + Apply changes.

Source : Oracle database

Target : Snowflake on Azure.

Full load completed fine and Apply changes ran without issue for few days but later the Apply Latency kept on building up.

Here are my Task settings.

Target End Point Advanced settings :

Re: Apply Latency increasing at Snowflake on Azure

Heinvandenheuvel — Thu, 28 Sep 2023 18:12:58 GMT

Run for 30 minutes or an hour with TARGET_APPLY logging set to TRACE. ROLL the log before and AFTER. Next carefully study to individual applies to see which is slow and try to explain/understand. You can also approach this from the Snowflake angle looking for longer running queries by the Replicate connection.

For the parameters shown - please enable 'apply concurrently' that way, if there is one slower apply at least the other applies in the batch do not have to wait but can proceed at the same time. Of course this is no garantuee because the applies are done alphabetically by tablename and the slowest table might be the very last to be started. I also thing 100MB for the files is a bit small, that's like 10 pictures worth these days right ? :-). Go to 500 and see if it helps some.

As TARGET_APPLY DEBUG gives a bunch of information you may want a structured tool to analyze the output. For that I created a PERL script execute with '-h' or read the code to check for help/options, it has helped many Replicate users. For mere money I'll help you use it, but the code is attached for your testing.

Hein.

Re: Apply Latency increasing at Snowflake on Azure

Dana_Baldwin — Thu, 28 Sep 2023 19:54:02 GMT

@raghujakkani Many customers have had improved performance with the file size set to the max of 2000 MB. For a more nuanced approach, you can adjust based on the Snowflake Warehouse size XS/S/M/L as a starting point. Set the Max file size on the Advanced table of connection to either 500/1000/1500/2000.

Also - you can increasing logging for component Performance to trace just to confirm where the latency is, source, target, or on the Replicate server.

Thanks,

Dana

Re: Apply Latency increasing at Snowflake on Azure

william75 — Mon, 02 Oct 2023 10:18:27 GMT

We use a bootleg merge on read approach in my current company. The data is loaded to a raw table with snowpipe streaming. Complex transformation is applied daily to materialize a table. Then, in a view we union the materialized table and the transformation results for the raw data we havent processed yet. The view can serve low latency data with not much of a performance hit this way.