DROP,TRUNCATE and DELETE - Nimbus Intelligence

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:

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:

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:

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:

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.