Loading Data into Snowflake using Snowpark DataFrames

Overview

Snowpark is a developer framework from Snowflake that allows developers to interact with Snowflake directly and build complex data pipelines using Python. 

This article explores using Snowpark DataFrames to write data into Snowflake tables.

How can you write data from a Snowpark DataFrame into a Snowflake table?

DataFrameWriter class in Snowflake provides methods for writing data from a DataFrame to desired destinations within the Snowflake ecosystem.

To write data from DataFrame into a table:

• Create a DataFrame containing the data to be written into a Snowflake table.
• Create a DataFrameWriter object by calling the DataFrame.write property on the DataFrame.
• Specify the write mode by calling the mode() method on the DataFrameWriter object. This returns a new DataFrameWriter object that is configured with the specified mode.
• Call the save_as_table method on the DataFrameWriter object to save the contents of the DataFrame to a specified table.

Syntax:

DataFrame.write.mode(save_mode).save_as_table(table_name)

Methods:

mode(save_mode): Configures the DataFrameWriter’s save mode. The following values are supported by the save_mode

• “append”: Appends data from the DataFrame to the existing table. If the table does not exist, it creates a new one.
• “overwrite”: Overwrite the existing table with the data from the DataFrame.
• “errorifexists”: Throws an exception if the table already exists.
• “ignore”: Ignore this operation if the table already exists.

save_as_table(table_name) : Writes the data to the specified table in a Snowflake database

Demonstration:

To illustrate, let’s read data from an existing Snowflake table and construct a DataFrame. The data that we have extracted from Snowflake will next be converted, and in the end, the changed data will be saved as a new table.

In the example below, data is read from a Snowflake table

OverWrite Data:

The following code writes the contents of the df_customer_select DataFrame to the specified Snowflake table, overwriting the table’s existing data if it already exists.

When this code runs, the Snowpark API in Snowflake translates it and runs it as SQL. The SQL statement that is produced is as follows:

The following code confirms that the table is created and displays the count of records loaded into the CUSTOMER table.

Append Data:

The following code appends the contents of the df_customer_select DataFrame to the specified Snowflake table, adding new records to the existing ones.

[Click here to read the complete blog...]