With saving the csv, the file storage location will be local if the flow is run locally on my machine. What happens if I run this flow through a cloud deployment? Will prefect create a temp directory to store that file; and then the temp directory gets destroyed upon finishing the flow execution?

Answer is coming up in a bit. Short answer, yes. But you can set a cloud storage bucket as your storage location.