After further investigation into it, have understood the root cause and the fix for it.

After enabling Auto Loader managed file events across the platform: 

.option("cloudFiles.useManagedFileEvents", "true")

the failed stream started discovering RocksDB checkpoint artifacts such as .zip and .log files because checkpointLocation was nested inside the monitored source path. Auto Loader then attempted to deserialize those files using the configured source format, causing FAILED_READ_FILE.NO_HINT on the rocksdb zip.

Root cause: the ingestion path contained both source data and Structured Streaming checkpoint files without file-level filtering.

Fix: Use the appropriate glob for each format such as *.parquet/ *.json/ *.csv in pathGlobalFilter.

reader_options = {
    "cloudFiles.useManagedFileEvents": "true",
    "pathGlobFilter": "*.parquet",
}

 That did the trick and resolved the issue. It would also be preferrable to place checkpointLocation and cloudFiles.schemaLocation outside the monitored source directory to prevent checkpoint self-ingestion entirely.

So, good lessons learnt.