Truncate Table
The Truncate Table component removes all rows from a table or partitions, while keeping the table intact.
Snowflake, Amazon Redshift: 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.
Delta Lake on Databricks: The table must not be a view or an external or temporary table.
Note
- A truncate operation is faster than a delete operation.
- A truncate operation is not transaction safe—it cannot be rolled back.
- Amazon Redshift: Deleting Rows (e.g. via the Delete Rows component) requires a vacuum to reclaim space from the removed rows.
- Amazon Redshift: Truncate does not require a separate vacuum.
| Data platform | Truncate docs | Delete docs |
|---|---|---|
| Snowflake | TRUNCATE TABLE | DELETE |
| Delta Lake on Databricks | TRUNCATE TABLE | DROP TABLE |
| Amazon Redshift | TRUNCATE | DELETE |
| Azure Synapse Analytics | TRUNCATE TABLE | DELETE |
Properties
Name = string
A human-readable name for the component.
Warehouse = drop-down
The Snowflake warehouse used to run the queries. The special value, [Environment Default], will use the warehouse defined in the environment. Read Overview of Warehouses to learn more.
Database = drop-down
The Snowflake database. The special value, [Environment Default], will use the database defined in the environment. Read Databases, Tables and Views - Overview to learn more.
Schema = drop-down
The Snowflake schema. The special value, [Environment Default], will use the schema defined in the environment. Read Database, Schema, and Share DDL to learn more.
Tables to Truncate = dual listbox
The tables to truncate.
Name = string
A human-readable name for the component.
Catalog = drop-down
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 = drop-down
Select the Delta Lake database. The special value, [Environment Default], will use the database specified in the Matillion ETL environment setup.
Tables to Truncate = dual listbox
The tables to truncate.
Name = string
A human-readable name for the component.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on using multiple schemas, read Schemas.
Tables to Truncate = dual listbox
The tables to truncate.
Name = string
A human-readable name for the component.
Schema = drop-down
Select the table schema. The special value, [Environment Default], will use the schema defined in the environment. For more information on schemas, read the Azure Synapse documentation.
Tables to Truncate = dual listbox
The tables to truncate.
| Snowflake | Delta Lake on Databricks | Amazon Redshift | Google BigQuery | Azure Synapse Analytics |
|---|---|---|---|---|
| ✅ | ✅ | ✅ | ❌ | ✅ |