Skip to main content

Three ways to delete your data.

We compare three commands on different characteristics such as: the family of commands they belong to, their usage, the syntax of the command, the memory management, the permissions needed to access the functionality and an evaluation on the speed of execution of each of them, also the reversibility of the operation is taken in consideration.

This is the starting table on which we will try the commands:

Starting Table

The results of the various commands are evaluated using the following query:

SELECT * FROM nimbus_table;

DELETE

Description

The DELETE command is part of the DML : Data Manipulation Language. It is useful to delete one or more records in the table therefore a specific query can be made to locate the rows we are interested in.
The syntax is as follows :

DELETE FROM nimbus_table WHERE Country = 'Italy';

This is the result on the initial table:

Table after the DELETE command
performance

As far as memory is concerned, this command is not advantageous because the memory that has already been allocated for the table is not deallocated after the records are deleted.
Also for execution time, among the three commands examined, this is the slowest, this is due to the fact that the DELETE request is targeted and therefore the system needs to analyze the table contents row by row ( or at least micro-partition by micro-partition)
Data is retained for recovery reasons in case of Time Travel, this command does not delete the history regarding the loading of data from external files. This prevents loading data from the same file more than once.

TRUNCATE

description

The TRUNCATE command is part of the Data Definition Language (DDL), which is the one used to define the structures on which you operate (Database, schema, table).
This deletes all the contents of the table while retaining the row containing the column names; it also keeps the privileges and restrictions on the table intact.
You cannot, therefore, specify delete conditions. The syntax is this:

TRUNCATE TABLE nimbus_table;

This is the result on the initial table:

Table after the TRUNCATE command
performance

Memory is not deallocated, so the space allocated to the table is not freed after TRUNCATE.
The execution speed is faster than the other two because the table is deleted all at once.
Data is retained for recovery reasons in case of time travel.

DROP

description

The DROP function is also part of the DDL. It removes the entire table from the Database.
The syntax is this:

DROP TABLE nimbus_table;

This is the result on the initial table:

Table after the DROP command
performance

If it is in our interest to free up memory within the database, surely this command conquers the first step of the podium since the space that was intended for the table is completely made available.
As far as execution speed is concerned, DROP is in the middle between DELETE and TRUNCATE since it deletes the whole table but only after deleting its rows.
It preserves a version of the table in case you want to execute an UNDROP command.
To drop a table, you must use a role that has ownership privilege over the table.

Sources

https://docs.snowflake.com/en/sql-reference/sql/drop-table
https://docs.snowflake.com/en/sql-reference/sql/truncate-table
https://docs.snowflake.com/en/sql-reference/sql/delete
https://www.scaler.com/topics/difference-between-delete-drop-and-truncate/

Auteur

  • Niccolo Racioppi

    Graduated in biomedical engineering from University Federico II of Naples with specialization in biomedical instrumentation from Politecnico di Torino. Worked as firmware developer for the last 2 years in a small consulting company. He lives in Milan and is in love with the city . In his spare time he likes to play basketball, watch movies (preferably good dramatic movies). Passionate about associationism he is part of a group that helps the homeless and another helps children with their homework. He chose the Nimbus academy because he saw in it a great chance to enter the data world, to be trained from the technical aspect but also from the point of view of interacting with other actors in the business world. It seemed like a great opportunity for him to take a step into the future.

Niccolo Racioppi

Graduated in biomedical engineering from University Federico II of Naples with specialization in biomedical instrumentation from Politecnico di Torino. Worked as firmware developer for the last 2 years in a small consulting company. He lives in Milan and is in love with the city . In his spare time he likes to play basketball, watch movies (preferably good dramatic movies). Passionate about associationism he is part of a group that helps the homeless and another helps children with their homework. He chose the Nimbus academy because he saw in it a great chance to enter the data world, to be trained from the technical aspect but also from the point of view of interacting with other actors in the business world. It seemed like a great opportunity for him to take a step into the future.