View Only

 Truncate table in Table Output step

  • Pentaho
  • Kettle
  • Pentaho
  • Pentaho Data Integration PDI
Dirk Sachse's profile image
Dirk Sachse posted 06-27-2019 14:25

How does "Truncate table" in the Table Output step work? 

The documentation says: 

"Select if you want the table to be truncated before the first row is inserted into the table"

If I interpret that correctly, the table should be truncated before a row is inserted. Now what happens if the insert fails? 

From my observation, the table is not truncated, but this is not what the documentation says. Is PDI simulating the insert or something?

David da Guia Carvalho's profile image
David da Guia Carvalho

I dont remember the exact moment but the truncate goes off on either the activation or validation of the step during the transformation execution. It means it doesnt matter if at some point it fails to insert, the table gets truncated before the error (unless the error is related to accessing the table itself)! 

To give you an idea you can have a table truncated, and a parcial dataset inserted on it before the error!

Johan Hammink's profile image
Johan Hammink

All steps are initialized when a transformation start. Then also the truncation take place. So when in your first step there is an error and no data comes to the table output step. The table is already truncated

Dirk Sachse's profile image
Dirk Sachse

I did some testing and I monitored the database server and I saw that Pentaho does a transaction rollback if there is a failure. So it first truncates the table, but reverts it when an insert fails.

Ana Gonzalez's profile image
Ana Gonzalez

Yes, but keep in mind that TRUNCATE isn't a DML (data manipulation language) order, so there's no rollback for a TRUNCATE, the rollback only affects the inserts after the TRUNCATE.


Johan Hammink's profile image
Johan Hammink

You are correct that the truncation will be rollback when an error occur when "Make the tranformation database transactional" in the Miscellaneous tab in the Transformation properties. The database must also allow that