Truncate Tables Component

Truncate Tables Component

Truncate a list of tables. 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.

  • Truncate is much faster than delete.
  • Truncate is not transaction safe - it cannot be rolled back.
  • Snowflake's Time Travel cannot recover a truncated table.
  • Delete requires a vacuum to reclaim space from the removed rows.
  • Truncate does not require a separate vacuum.

For more information, see Truncate or Delete in the Amazon Redshift documentation

For more information, see Truncate Table or Delete in the Snowflake documentation


Properties

Property Setting Description
Name Text The descriptive name for the component.
Warehouse Select The warehouse that will run the truncation.
Database Select The database that holds the table to be truncated.
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.

Example

In this example we have two very large tables named 'bugs' and '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 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 '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.

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