topic Re: Optimizing Writes from Databricks to Snowflake in Data Engineering

Optimizing Writes from Databricks to Snowflake

pvignesh92 — Fri, 10 Mar 2023 08:26:51 GMT

My job after doing all the processing in Databricks layer writes the final output to Snowflake tables using df.write API and using Spark snowflake connector. I often see that even a small dataset (16 partitions and 20k rows in each partition) takes around 2 minutes to write. Is there any way, the write can be optimized?

Re: Optimizing Writes from Databricks to Snowflake

-werners- — Fri, 10 Mar 2023 10:52:31 GMT

afaik the spark connector is already optimized. Can you try to change the partitioning of your dataset? for bulk loading larger files are better.

Re: Optimizing Writes from Databricks to Snowflake

pvignesh92 — Fri, 10 Mar 2023 12:12:53 GMT

Yes. Brought that down to 4 partitions while doing my transformations and then tried as well. On an average, it takes 2 minutes for the write. I'm not sure if that's the expected behavior with jdbc connection.

Re: Optimizing Writes from Databricks to Snowflake

-werners- — Fri, 10 Mar 2023 12:16:35 GMT

seems slow to me.

Are you sure you do not do any spark processing?

because if so: a chunck of that 2 minutes is spark transforming the data.

Re: Optimizing Writes from Databricks to Snowflake

Vartika — Mon, 03 Apr 2023 11:21:32 GMT

Hi @Vigneshraja Palaniraj

Hope all is well!

Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!

Re: Optimizing Writes from Databricks to Snowflake

pvignesh92 — Mon, 03 Apr 2023 11:32:26 GMT

There are few options I tried out which had given me a better performance.

Caching the intermediate or final results so that while writing the dataframe computation does not repeat again.
Coalesce the results into the partitions 1x or 0.5x your number of cores and also ensure that your partitions are equal to or more than 128 MB blocks so that the writes are happening efficiently.

Re: Optimizing Writes from Databricks to Snowflake

pvignesh92 — Mon, 03 Apr 2023 11:32:55 GMT

Thanks @Vartika Nain for following up. I closed this thread.