topic Too many small files from updates in Data Engineering

Too many small files from updates

pradeepvatsvk — Thu, 30 Jan 2025 16:20:44 GMT

Hi ,

I am updating some data into a delta table , each time I only need to update one row due to which after every update statement it is creating new file, How do I tackle this issue , it doesn't make sense to run optimize command after every update command

Re: Too many small files from updates

JakubSkibicki — Thu, 30 Jan 2025 17:11:28 GMT

Usually this problem is solved with autooptimize property.

https://docs.databricks.com/en/delta/tune-file-size.html#auto-compaction-for-delta-lake-on-databricks

For Managed tables this option is enabled by default

Re: Too many small files from updates

Rjdudley — Thu, 30 Jan 2025 20:21:55 GMT

Depending on your table settings, those may be log files or version use for Time Travel. Unless you've mastered partitioning, you really shouldn't worry about the files and let the system do what it does.

Re: Too many small files from updates

pradeepvatsvk — Fri, 31 Jan 2025 11:30:02 GMT

But it is making a performance head , every update command is taking more time than previous since it has to filter in more files

Re: Too many small files from updates

saurabh18cs — Fri, 31 Jan 2025 12:15:36 GMT

set following spark session properties and give a try:

'spark.databricks.delta.properties.defaults.autoOptimize.optimizeWrite': 'true'

'spark.databricks.delta.optimizeWrite.enabled': 'true'

'spark.sql.shuffle.partitions': 'auto'

Re: Too many small files from updates

Rjdudley — Fri, 31 Jan 2025 14:56:24 GMT

OK something isn't right, I work with massive datasets and this is not an issue for a single update. If your architecture and Unity Catalog configuration is correct, and there's also not some weird bug, you should not be aware of the underlying files. Are you working against the data files directly or are you querying the tables in Unity Catalog?

Re: Too many small files from updates

Lakshay — Fri, 31 Jan 2025 18:42:49 GMT

If you performing 100s of update operations on the delta table, you can opt to run an optimize operation after a batch of 100 updates. There should be no significant performance issue up to 100 such updates