Truncate Table
  • Dark
    Light

Truncate Table

  • Dark
    Light

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

Truncate Tables

The Truncate Tables component removes all rows from a table or partition(s), 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.
Database Select Select a database. The special value, [Environment Default], uses the Delta Lake database defined in the Matillion ETL environment.
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.

When this job runs, it will end by truncating the two tables we used so that a large amount of data is not left over on the cluster.


What's Next