Agree.

TL;DR Control the Delta raw table updates frequency (batch incoming data), and/or reduce the log retention period. This is assuming you're currently on DBR 14.2+, where Delta Logs compaction is already enabled.

Only for clarity, I believe it'll be good to write down the process and pinpoint the bottleneck:

When a daily job with trigger=availableNow initiates to process a Delta table, the latestOffset calculation follows these steps:

1. Identify the Latest Checkpoint:

   • The system locates the most recent checkpoint file in the _delta_log directory. Checkpoints are Parquet files that capture the table's state at specific versions, enabling efficient state reconstruction.

2. Determine the Starting Version:

   • Based on the latest checkpoint, the system identifies the starting version for processing. If the checkpoint corresponds to version 1000, the starting version would be 1001.

3. List Subsequent Log Files:

   • The system lists all JSON log files from the starting version up to the current latest version. Each JSON file represents a commit that records changes to the table.

4. Apply Changes from Log Files:

   • The system sequentially applies the changes recorded in each JSON log file to reconstruct the table's current state. This process includes adding or removing data files as specified in the logs.

5. Identify Active Data Files:

   • After processing the log files, the system identifies the set of active data files that constitute the latest state of the table.

6. Calculate latestOffset:

   • The system determines the latestOffset by identifying the most recent data available for processing, based on the active data files and the latest version processed.

Bottleneck:

Even when utilizing the latest checkpoint and processing up to 100 versions (given the default delta.checkpointInterval of 100), the system may still need to list all files in the _delta_log directory to identify the latest checkpoint and subsequent JSON log files. This file listing operation can be time-consuming, especially in environments like AWS S3, where listing operations are relatively slow.

Rough calculation of file accumulation in the _delta_log directory, considering your estimate of 18k commits per day:

• Daily Commits: With approximately 18,000 commits per day and a delta.checkpointInterval of 100, each day would generate:

  • Checkpoint Files: 18,000 commits / 100 = 180 checkpoint files
  • JSON Log Files: 18,000 commits - 180 checkpoints = 17,820 JSON log files

• Retention Period: With a log retention period of 7 days, the _delta_log directory would accumulate:

  • Checkpoint Files: 180 checkpoints/day * 7 days = 1,260 checkpoint files
  • JSON Log Files: 17,820 JSON logs/day * 7 days = 124,740 JSON log files

In total, this results in approximately ~126,000 files in the _delta_log directory over a 7-day period. Note: there may be even more (or less), as I'm not considering checkpoint sidecar files (checkpoint v2) or delta log minorCompaction if available.

"...It doesn't look like OPTIMIZE, VACUUM or other configs can help..."

OptimizeWrite, if your application implementation can leverage this optimization, then by generating fewer larger files, the system decreases the number of commit operations required. Each commit file corresponds to a json log entry; thus, fewer commits result in fewer log entries. Same applies for autoCompact, but we agree that these features are designed to optimize write operations and compact small files, and their effectiveness can be limited when dealing with isolated high-frequency, minimal-size updates.

Optimization Strategies:

1. Control Update Frequency:

   • Batch incoming data to reduce the frequency of updates to the Delta raw table. This approach decreases the number of commits and, consequently, the number of JSON log files generated.

2. Adjust Log Retention Period:

   • Reduce the log retention period to limit the accumulation of log files. This change ensures that older log files are purged more frequently, reducing the total number of files in the _delta_log directory.
3. Log Compaction: Make sure deltaLog.minorCompaction.useForReads is set to true. Note: If I'm not wrong, this is only available starting from DBR 14.2+ and recent versions, where it indeed defaults to True.