Hi everyone,

I am facing a data consistency issue in my Databricks incremental pipeline where records are being skipped because of a time gap between when a record is processed and when the physical file is finalized in Azure Blob Storage (ABFS).

Our Architecture:
1. Ingestion: Data is pulled from Kafka and written as  Delta Files at a blob path. During this write, we create a column called `loadts` using `current_timestamp()`.
2. Downstream ETL: A scheduled batch job reads from these Delta files incrementally. It fetches the `max(loadts)` from a audit table and filters the source: `WHERE loadts >= last_max_date`.

The Issue:
We are seeing a significant gap (often 20+ minutes) between the `loadts` inside the data and the system-level `_file_modified_at` timestamp for some of the delta files.

Example: A record is assigned a `loadts` of 09:05:00(when the Spark executor processes it).
Latency: Due to write volume and Delta transaction overhead, the file is not committed and visible in ABFS until 09:27:55.
The Conflict: If an incremental job runs at 09:15:00, it doesn't "see" that file yet. The job finishes, and the checkpoint moves forward. When the next job runs at 10:00:00, it searches for data where `loadts >= 09:15:00`. Consequently, the record born at 09:05:00 (but delivered at 09:27:55) is skipped forever.

Current Workaround:
We currently use a hardcoded 15-minute look-back window: `timestampadd(MINUTE, -15, last_max_date)`. However, this is brittle because processing times vary, and we cannot guarantee that a 15 or even 30-minute window will always cover the commit latency.

Question:
What is the industry-standard way to handle this in Databricks without "guessing" a look-back window?

Any advice on how to make this pipeline bulletproof would be greatly appreciated.

Note: Using Serverless Performance optimized cluster, as the data need to be highly available