We worked with Qlik and Snowflake to determine why full load and CDC Replicate performance seemed to be slower than expected (client portal case 1966818). The issue in brief is that Snowflake resources are given single files to load with the COPY command, forcing it to work in a single-threaded manner. Performance would be greatly improved by partitioning files and using COPY <list of files to load>.
Full load - for each table, a single large file is created, and once that is complete, a single COPY command is run. Multiple different tables can be run in parallel this way. But there is an opportunity here to break the very large single table files up (which take the longest to load, gating the job) into smaller files, and then issue parallel COPY commands as those complete (reassembling them in proper sequence afterwards). CDC is actually more important than full load, so read on.
CDC load - Even after adjusting tuning parameters for size of files to drop during CDC, we still see performance can be improved, as each file is loaded sequentially. Instead, partition the file into arbitrary multiple files (add a pseudo-column representing file seq # if necessary) , and use COPY <list of files> to load the data in parallel, which will merge the data.
