The DISTRIBUTE BY COALESCE(1) clause is intended to reduce the number of output files to one. However, this can lead to inefficiencies and large file sizes because it forces all data to be processed by a single task, which can cause memory and performance issues. Instead of using COALESCE(1), consider using REPARTITION(1). This can help in better distributing the data and reducing the file size.

Applying compression to the CSV file can significantly reduce its size. You can use the compression option to specify the desired compression codec (e.g., gzip😞