How to connect to Snowflake with snowsql and test data manipulation with temporary tables

The aim of this page📝 is to explain how to create and manipulate temporary tables in Snowflake based on the particular example of creating a random table with 3 columns, adding a row of appropriate data into the 3 columns, adding another column to that table to have 4 columns, and adding a row of appropriate data with 4 columns.

• A temporary table is a table that exists temporarily.
• Temporary tables are useful for storing immediate result sets that are accessed multiple times.
• Temporary tables are automatically deleted as soon as the last connection is terminated.
• Temporary tables help to store and process intermediate results and are very useful when you need to store temporary data.
• In Snowflake, the process for dropping a temporary table is similar to dropping a regular table. You can use the DROP TABLE statement to remove a temporary table from the current or specified schema.
• Dropping a table in Snowflake does not permanently remove it from the system. A version of the dropped table is retained in Time Travel for the number of days specified by the DATA_RETENTION_TIME_IN_DAYS parameter for the table.
• Within the Time Travel retention period, a dropped table can be restored using the UNDROP TABLE command.

CODE

• First, connect with snowsql CLI tool

snowsql -a <snowflake_account>.eu-west-1 -u <snowflake_username> -d <snowflake_database> -s <snowflake_schema> -w <snowflake_warehouse>
Password:

…you’ll be prompted for a password that needs to be inserted

-- Create a random table with 3 columns
CREATE OR REPLACE TEMPORARY TABLE mytable (col1 STRING, col2 NUMBER, col3 DATE);

-- Add a row of appropriate data into the 3 columns
INSERT INTO mytable VALUES ('test', 123, CURRENT_DATE);

-- Add another column to that table to have 4 columns
ALTER TABLE mytable ADD COLUMN col4 TIMESTAMP;

-- Add a row of appropriate data with 4 columns
INSERT INTO mytable VALUES ('test2', 456, CURRENT_DATE, CURRENT_TIMESTAMP);

-- Delete temp table
DROP TABLE mytable;

• Finally quit snowsql with the !quit command

This query creates a temporary table called mytable with three columns: col1 of type STRING, col2 of type NUMBER, and col3 of type DATE. It then inserts a row of data into the table with the values 'test', 123, and the current date. The query then adds a fourth column to the table called col4 of type TIMESTAMP. Finally, it inserts another row of data into the table with the values 'test2', 456, the current date, and the current timestamp.

LINKS

• Working with Temporary and Transient Tables | Snowflake Documentation