pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to prepare dimension update :

Error message

Unable to prepare dimension update :

What it means

Preparing the UPDATE PreparedStatement that closes the previous dimension version (sets date_to, last_updated, version field) failed. Any SQLException from prepareStatement for sql_upd is wrapped in a KettleDatabaseException containing the failing update SQL.

Solutions

1. Read the sql_upd included in the exception/log and compare with the actual table DDL
2. Create the missing version/date-to columns via the dialog's 'SQL' button or ALTER TABLE
3. Fix the version field name in the Dimension Lookup dialog to match the table
4. Verify UPDATE privileges for the connection user on the dimension table

Example fix

// before
// UPDATE dim_customer SET version=... WHERE ... -> Unknown column 'version'
// after
// ALTER TABLE dim_customer ADD COLUMN version INTEGER DEFAULT 1;
// (or set the Version field option to the actual column, e.g. 'row_version')

// confirm the version/date-to columns exist before the update is prepared
for (String col : Arrays.asList(meta.getVersionField(), meta.getDateToField())) {
  if (!tableColumns.contains(col)) {
    throw new IllegalStateException("Missing SCD column on dimension table: " + col);
  }
}

try {
  dimInsert(rowMeta, row);
} catch (KettleDatabaseException e) {
  logError("Update prepare failed, SQL=" + e.getMessage());
  throw e;
}

When it happens

Trigger: dimInsert (called by lookupValues on a type-1/type-2 update path) prepares the UPDATE statement against the dimension table; missing/renamed version column, wrong key columns, or SQL syntax/schema errors trigger it.

Common situations: Version field name changed in the dialog but not in the table; dimension table dropped or in a different schema; DB user lacks UPDATE privileges (surfaces at prepare on some drivers); copied transformation pointing at a table with a different layout.

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

• An error occurred executing SQL:
• Couldn't execute SQL:
• Couldn't find any rows because of an error :
• Couldn't get a result because of an error :
• Couldn't prepare statement:

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

Appendix: source

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

        }
      }

      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;
        updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ) );
      }
      sql_upd += "AND   " + databaseMeta.quoteField( meta.getVersionField() ) + " = ? ";
      updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) );

      try {
        logDetailed( "Preparing update: " + Const.CR + sql_upd + Const.CR );
        data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) );
      } catch ( SQLException ex ) {
        throw new KettleDatabaseException( "Unable to prepare dimension update :" + Const.CR + sql_upd, ex );
      }

      data.insertRowMeta = insertRowMeta;
      data.updateRowMeta = updateRowMeta;
    }

    Object[] insertRow = new Object[ data.insertRowMeta.size() ];
    int insertIndex = 0;
    if ( !isAutoIncrement() ) {
      insertRow[ insertIndex++ ] = technicalKey;
    }

    // Caller is responsible for setting proper version number depending
    // on if newEntry == true
    insertRow[ insertIndex++ ] = versionNr;

    switch ( data.startDateChoice ) {
      case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE:

View on GitHub (pinned to f3058517a1)