Delta Streaming and Optimize

[brickster_2018]

I have a master delta table that is continuously getting written by a streaming job. I have optimize writes enabled and in addition, I run the OPTIMIZE command every 3 hours.

However, I think the downstream streaming jobs which are streaming the data from the master delta table do not get any benefit from the OPTIMIZE job. I tried stopping the downstream job and started it after completing the OPTIMIZE command, still, the downstream job is reading the un-optimized files

[brickster_2018 · Accepted solution]

This is working as expected. For Delta streaming, the data files created in the first place will be used for streaming. The optimized files are not considered the downstream streaming job.

This is the reason it's not recommended to run VACUUM with fewer retention hours