I have setup a bucket with a lot of small XML files to be loaded into snowflake with snowpipe. But, I have ~5 million small files(one file per row) that needs to be loaded into snowflake. I have a list of these files that need to loaded to snowflake. I realize this is not an optimal situation, but, what if any can i do make this more palatable? What are the implications of running 5 million copy commands from a billing perspective? Anything I can do to reduce the cost here? Maybe pre-process the XML into a single file? Worth the effort in terms of savings?
0
votes
2 Answers
2
votes
Snowpipe's primary charges are based on the compute time used for performing the load. For a lot of small files, the overhead associated with performing a full load instruction per file (discover, open, read/parse, insert, close, next) can grow to a very significant value of wasted compute time otherwise spent on actual data reads and inserts.
Furthermore, there is an overhead charge related to the number of files directly:
In addition to resource consumption, an overhead is included in the utilization costs charged for Snowpipe: 0.06 credits per 1000 files notified or listed via event notifications or REST API calls. This overhead is charged regardless if the event notifications or REST API calls resulted in data loaded.
For ~5 million files, that's ~300 credits in utilization fees when using Snowpipe, on top of the actual compute credits.
Anything I can do to reduce the cost here? Maybe pre-process the XML into a single file?
Pre-processing the files to larger pieces is certainly a good idea. Snowflake supports reading each root XML node as its own row, so merging the XML data into larger files of roughly 100 MiB each can help them be loaded quicker and cheaper.
Keep in mind that no individual XML document row can exceed 16 MiB.
Note: If this will be a one time operation, and the larger need is to have the files loaded to begin using Snowflake soon, then I'd recommend loading it as-is since you mention the bucket is already prepared. You can use the merge approach for future repetitions.
