I don’t think the issue I had was caused by the sleep mode. It is more likely due to the limitation of UART itself. Due to its asynchronous nature, there is an inevitable non-trivial error rate. If large files are involved, SPI, as used in DISCO-L475VG , seems like a better option. If I control chunk size under 250KB, which is still quite large by embedded system standard, and add checksum to each chunk as suggested by @JohnnyK , I think I am good now.
Several years ago, someone asked for support to use ESP32 via SPI, but that didn’t go anyway unfortunately.