Here are some considerations/ideas you might be interested in:

1. Automatic Caching Mechanism Interference: Yes, there is a possibility that an automatic caching mechanism interferes with your dataset. Spark employs several caching mechanisms:

   • The DataFrame cache in Spark SQL APIs stores DataFrame/Dataset data in memory when .cache() or .persist() is explicitly called.
   • The Disk Cache or Delta Cache (DBIO Cache) automatically caches Parquet or Delta files on the local storage of executors for improved performance. This feature is enabled by default on certain clusters and can be controlled via the spark.databricks.io.cache.enabled Spark configuration parameter.
   If this automatic caching mechanism refuses to update or invalidate correctly, stale data (empty or older versions) might be retrieved during jobs.
2. Cache Containing Version X While Storage is at X+1: Yes, this can happen when the underlying cache is not cleared or invalidated after an update. For Delta tables, the disk cache mainly relies on file timestamps and metadata to determine cache invalidation. If updates to Delta tables are not reflected correctly in the cache, such scenarios may arise. It is advised to explicitly run REFRESH TABLE or spark.catalog.uncacheTable("table_name") after updates to ensure cache invalidation and syncing with the latest consistent state.
3. Empty Dataset from Cache Unexpectedly: Spark reads an empty dataset from the cache when the cache holds no valid data for the requested operation, possibly due to:

   • Stale or corrupted cached data linked to previous operations.
   • Inconsistent cache state due to automatic caching mechanisms failing to handle updates to Delta tables.
   • Misalignment between cached metadata and subsequent queries.
   The InMemoryTableScan you observe indicates that Spark is scanning cached in-memory data. If a DataFrame was empty when it was previously cached, Spark would return the empty dataset on subsequent accesses. Without explicit invalidation or clearing (e.g., using .unpersist(true)), Spark might not reload it from disk/storage even when newer data exists.