Truncate Table

Dark

Light

This article is specific to the following platforms - Snowflake - Redshift - Synapse - Delta Lake.

Truncate Table

The Truncate Table component removes all rows from a table or partitions, while keeping the table intact. The table must not be a view or an external or temporary table.

Truncate is implemented in two different ways, depending upon whether the current flow is in the middle of a database transaction. The first is by using a TRUNCATE statement. The second is by using a DELETE FROM statement, which is preferable if the current job is using a transaction.

Important Information

A truncate operation is faster than a delete operation.
A truncate operation is not transaction safe—it cannot be rolled back.
Deleting Rows (e.g. via the Delete Rows Component) requires a vacuum to reclaim space from the removed rows.
Truncate does not require a separate vacuum.

To learn more about the Redshift truncate command, read Truncate.

To learn more about the Redshift delete command, read Delete.

To learn more about the Snowflake truncate command, read Truncate Table.

To learn more about the Snowflake delete command, read Delete.

To learn more about the Synapse truncate command, read Truncate Table.

To learn more about the Delta Lake on Databricks truncate command, read Truncate Table.

Properties

Snowflake Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Warehouse	Select	The Snowflake warehouse used to run the queries. The special value, [Environment Default], uses the warehouse defined in the Matillion ETL environment.
Database	Select	The Snowflake database. The special value, [Environment Default], uses the database defined in the Matillion ETL environment.
Schema	Select	The Snowflake schema. A schema is a logical grouping of database objects such as tables and views. The special value, [Environment Default], uses the schema defined in the Matillion ETL environment.
Target Table	Select	Select all of the tables you want to truncate.

Redshift Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Schema	Select	Select the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.
Tables to Truncate	Select	Select all of the tables you want to truncate.

Synapse Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Schema	Select	Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, please see the Azure Synapse documentation.
Table	Table Select	Select any tables to truncate.

Delta Lake Properties
Property	Setting	Description
Name	Text	A human-readable name for the component.
Catalog	Select	Select a Databricks Unity Catalog. The special value, [Environment Default], will use the catalog specified in the Matillion ETL environment setup. Selecting a catalog will determine which databases are available in the next parameter.
Database	Select	Select the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
Table	Table Select	Select any tables to truncate.

Example

In this example, we have two very large tables. One named "bugs" and one named "cases", and we wish to truncate them to quickly clean up after a job. The job is shown below.

Here we decide to use the default schema. Ensure that any tables you wish to truncate belong to this schema.

The Tables to Truncate property allows us to select one or more tables belonging to that schema to truncate by moving our selection into the right-hand column, shown below.

The Target Table property allows us to select one or more tables belonging to that schema to truncate by moving our selection into the right-hand column, shown below.