Truncate Table
- DarkLight
Truncate Table
- DarkLight
Article Summary
Share feedback
Thanks for sharing your feedback!
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.
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.