Loading data from Databricks pyspark dataframe into Snowflake

Question

Hello,

I am trying to load a large dataframe (~ 50 million records) from Databricks into Snowflake. I am using the below code and it takes more than 2 hours to load the data into the target Snowflake table.

Is there a different configuration/approach that I can use to load the data effectively?

df = spark.sql("SELECT * FROM DatabricksTempView")
df.write \
            .format("snowflake") \
            .options(**snowflake_options) \
            .option("dbtable",SnowflakeTable)  \
            .mode("append") \
            .save()

The Snowflake compute size is X-Small, could that also be a reason for the slow load?

The Databricks cluster configuration is as below

Answer 1

@Alok Thampi - Thanks for the question and using MS Q&A platform.

Loading a large dataframe from Databricks into Snowflake can take a significant amount of time, especially if the Snowflake compute size is small. There are several approaches that you can use to optimize the data load process and reduce the time taken to load the data into Databricks into Snowflake.

1. Use a larger Databricks cluster: You can try using a larger Databricks cluster to improve the load performance. A larger cluster can process the data more quickly and reduce the time taken to load the data into Snowflake.
2. Use partitioning: If your dataframe is partitioned, you can use partitioning to load the data into Snowflake more efficiently. Partitioning can help reduce the amount of data that needs to be loaded into Snowflake and can improve the load performance. You can partition the dataframe based on a column that is frequently used in queries, such as a date column.
3. Use a different file format: You can try using a different file format, such as Parquet or ORC, to write the dataframe to a file. These file formats are highly optimized for columnar storage and can improve the load performance.
4. Use the COPY command: The COPY command is a highly optimized command in Snowflake that can load data from a variety of sources, including S3, Azure Blob Storage, and Google Cloud Storage. You can use the COPY command to load data from a Databricks dataframe into Snowflake. To use the COPY command, you will need to write the dataframe to a file in a supported format, such as CSV or Parquet, and then use the COPY command to load the data into Snowflake.
5. Use the JDBC driver: You can use the Snowflake JDBC driver to load data from Databricks into Snowflake. This approach can be slower than using the COPY command, but it can be useful if you need to perform transformations on the data before loading it into Snowflake.

I hope these suggestions help you optimize the data load process and reduce the time taken to load the data from Databricks into Snowflake.

For more details, refer to Best practices for performance efficiency and Compute configuration recommendations.

Hope this helps. Do let us know if you have any further queries. 

 ------------------

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.