Optimal Data Lake File Partition Sizes

user_11407653 0
Viewed 975

The Small File Problem gets referenced a lot when discussing performance issues with Delta Lake queries. Many sources recommend file sizes of 1GB for optimal query performance.

I know Snowflake is different than Delta Lake, but I think it's interesting that Snowflake's strategy contradicts the conventional wisdom. They rely on micro-partitions, which aim to be between 50MB and 500MB before compression.

Snowflake and Delta Lake have similar features:

  1. File Pruning - Snowflake vs Delta Lake

  2. Metadata about contents of file - Snowflake vs Delta Lake

Can anyone explain why Snowflake thrives on smaller files while conventional wisdom suggests that Delta Lake struggles?

delta-lake snowflake-cloud-data-platform databricks apache-spark
1 Answers

Disclaimer: I'm not very familiar with Snowflake, so I can only say based on the documentation & experience with Delta Lake.

Small files problem usually arise when you're storing streaming data, or something like, and store that in the formats like Parquet that rely only on the listing of the files provided by storage provider. With a lot of small files, the listing of files is very expensive, and often is the place where most of time is spent.

Delta Lake solves this problem by tracking the file names in the manifest files, and then reaching objects by file name, instead of listing all files and extracting file names from there. On Databricks, Delta has more optimizations for data skipping, etc., that could be achieved by using metadata stored in the manifest files. As I see from documentation, Snowflake has something similar under the hood.

Regarding the file size - on Delta, default size is ~1Gb, but in practice it could be much lower, depending on type of data that is stored, and if we need to update data with new data or not - when updating/deleting data, you'll need to rewrite the whole files, and if you have big files, then you're rewriting more.

Alex_Ott_186270
Related