pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to prepare dimension punchThrough update statement :

Error message

Unable to prepare dimension punchThrough update statement :

What it means

When the update type is 'Punch through' (update the past/beginning-of-history rows), the step prepares a dedicated UPDATE statement (sql_upd) on the JDBC connection. A SQLException during prepareStatement is wrapped in this KettleDatabaseException with the SQL appended. Like the regular prepare error, it indicates the SQL or the schema is unacceptable to the database, specifically for the punch-through statement.

Solutions

Inspect the wrapped SQLException and the sql_upd text in the message for the actual database error
Verify all punch-through update fields and the date-from field exist in the dimension table
Confirm the connection user has UPDATE privileges on the table
Check schema/table naming and identifier quoting in the step matches the database
If punch-through is unintended, change the field update type from 'Punch through' to 'Insert'

Example fix

// before: punch-through field removed from table
// UPDATE dim_customer SET dept = ? WHERE dim_customer_tk = ?  -- 'dept' dropped
// after
ALTER TABLE dim_customer ADD COLUMN dept VARCHAR(100);
// or change the field's update type from "Punch through" to "Insert" in the step dialog

Defensive patterns

Strategy: validation

Validate before calling

// Java: verify punch-through fields exist in the table before enabling punch-through updates
RowMetaInterface tableFields = db.getTableFields(schemaTable);
for (String f : meta.getFieldUpdate()) {
  if (tableFields.searchValueMeta(f) == null)
    throw new IllegalStateException("Punch-through field missing in table: " + f);
}

Try / catch

try {
  punchThrough(...);
} catch (KettleDatabaseException e) {
  if (e.getMessage().contains("punchThrough update statement")) {
    logError("Punch-through prepare failed: " + e.getCause());
    // fall back to Insert update type or fix schema/privileges
  } else throw e;
}

Prevention

Re-map punch-through fields whenever the dimension table changes
Verify UPDATE privileges for the connection user
Prefer 'Insert' update type unless history correction is genuinely needed
Check identifier quoting for the target database type

When it happens

Trigger: conn.prepareStatement(stripCR(sql_upd)) throws SQLException while preparing the punch-through UPDATE built from the key field, date-from field, and punch-through fields when meta.isUpdate() (punch through) is enabled.

Common situations: Punch-through fields no longer exist in the table; date-from field misconfigured; identifier quoting mismatch for the DB; insufficient UPDATE privilege; schema/table prefix wrong.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

AI-assisted analysis of pentaho/pentaho-kettle@f3058517a1 (2026-09-13). Data as JSON: /api/errors/d8b5671b5cd4aed8. Report an issue: GitHub.

Appendix: source

Thrown at engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java:1421

          sql_upd += ", " + databaseMeta.quoteField( valueMeta.getName() ) + " = ?" + Const.CR;
          data.punchThroughRowMeta.addValueMeta( valueMeta );
        }
      }

      sql_upd += "WHERE ";
      for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {
        if ( i > 0 ) {
          sql_upd += "AND   ";
        }
        sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[ i ] ) + " = ?" + Const.CR;
        data.punchThroughRowMeta.addValueMeta( rowMeta.getValueMeta( data.keynrs[ i ] ) );
      }

      try {
        data.prepStatementPunchThrough =
          data.db.getConnection().prepareStatement( meta.getDatabaseMeta().stripCR( sql_upd ) );
      } catch ( SQLException ex ) {
        throw new KettleDatabaseException( "Unable to prepare dimension punchThrough update statement : "
          + Const.CR + sql_upd, ex );
      }
    }

    Object[] punchThroughRow = new Object[ data.punchThroughRowMeta.size() ];
    int punchIndex = 0;

    for ( int i = 0; i < meta.getFieldLookup().length; i++ ) {
      if ( meta.getFieldUpdate()[ i ] == DimensionLookupMeta.TYPE_UPDATE_DIM_PUNCHTHROUGH ) {
        punchThroughRow[ punchIndex++ ] = row[ data.fieldnrs[ i ] ];
      }
    }
    for ( int i = 0; i < meta.getFieldUpdate().length; i++ ) {
      switch ( meta.getFieldUpdate()[ i ] ) {
        case DimensionLookupMeta.TYPE_UPDATE_DATE_INSUP:
        case DimensionLookupMeta.TYPE_UPDATE_DATE_UPDATED:
          punchThroughRow[ punchIndex++ ] = new Date();
          break;

View on GitHub (pinned to f3058517a1)