Destination Improvement: Option to use additional merge match criteria
Destination: Snowflake
Source: MongoDB (any other DB, possibly)
Issue: Merge performance suffers on large tables with non-numeric primary keys (e.g. GUIDs). This appears to be due to Snowflake not being able to performantly prune relevant micro-partitions, as the partition metadata will not include a list of the non-numeric primary keys.
In this case, merge performance can likely be improved by including an additional match criteria, like create date, which could allow for better partition pruning.
This is not something that would be needed in all instances, and possibly is something that may only be needed for individual tables.
-
Official comment
Hi Chris,
I think it will be difficult for us to offer a feature that is so dependent on the internal behavior details of the query optimizer. Have you tried clustering the table? By explicitly asking Snowflake to cluster on the primary key, that might be able to improve the performance of pruning.
Please sign in to leave a comment.
Comments
1 comment