{"record":{"id":"814f4e6108bf8564","repo":"pentaho/pentaho-kettle","slug":"unable-to-prepare-dimension-update","errorCode":null,"errorMessage":"Unable to prepare dimension update :","messagePattern":"Unable to prepare dimension update :","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":1091,"sourceCode":"        }\n      }\n\n      sql_upd += \"WHERE \";\n      for ( int i = 0; i < meta.getKeyLookup().length; i++ ) {\n        if ( i > 0 ) {\n          sql_upd += \"AND   \";\n        }\n        sql_upd += databaseMeta.quoteField( meta.getKeyLookup()[ i ] ) + \" = ?\" + Const.CR;\n        updateRowMeta.addValueMeta( inputRowMeta.getValueMeta( data.keynrs[ i ] ) );\n      }\n      sql_upd += \"AND   \" + databaseMeta.quoteField( meta.getVersionField() ) + \" = ? \";\n      updateRowMeta.addValueMeta( new ValueMetaInteger( meta.getVersionField() ) );\n\n      try {\n        logDetailed( \"Preparing update: \" + Const.CR + sql_upd + Const.CR );\n        data.prepStatementUpdate = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql_upd ) );\n      } catch ( SQLException ex ) {\n        throw new KettleDatabaseException( \"Unable to prepare dimension update :\" + Const.CR + sql_upd, ex );\n      }\n\n      data.insertRowMeta = insertRowMeta;\n      data.updateRowMeta = updateRowMeta;\n    }\n\n    Object[] insertRow = new Object[ data.insertRowMeta.size() ];\n    int insertIndex = 0;\n    if ( !isAutoIncrement() ) {\n      insertRow[ insertIndex++ ] = technicalKey;\n    }\n\n    // Caller is responsible for setting proper version number depending\n    // on if newEntry == true\n    insertRow[ insertIndex++ ] = versionNr;\n\n    switch ( data.startDateChoice ) {\n      case DimensionLookupMeta.START_DATE_ALTERNATIVE_NONE:","sourceCodeStart":1073,"sourceCodeEnd":1109,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L1073-L1109","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Read the sql_upd included in the exception/log and compare with the actual table DDL","Create the missing version/date-to columns via the dialog's 'SQL' button or ALTER TABLE","Fix the version field name in the Dimension Lookup dialog to match the table","Verify UPDATE privileges for the connection user on the dimension table"],"exampleFix":"// before\n// UPDATE dim_customer SET version=... WHERE ... -> Unknown column 'version'\n// after\n// ALTER TABLE dim_customer ADD COLUMN version INTEGER DEFAULT 1;\n// (or set the Version field option to the actual column, e.g. 'row_version')","handlingStrategy":"try-catch","validationCode":"// confirm the version/date-to columns exist before the update is prepared\nfor (String col : Arrays.asList(meta.getVersionField(), meta.getDateToField())) {\n  if (!tableColumns.contains(col)) {\n    throw new IllegalStateException(\"Missing SCD column on dimension table: \" + col);\n  }\n}","typeGuard":null,"tryCatchPattern":"try {\n  dimInsert(rowMeta, row);\n} catch (KettleDatabaseException e) {\n  logError(\"Update prepare failed, SQL=\" + e.getMessage());\n  throw e;\n}","preventionTips":["Rename version columns in both the table and the dialog together","Check the generated sql_upd in the log against the table DDL on schema drift","Ensure the dimension table always includes version and date-to columns the step expects"],"tags":["database","jdbc","update","sql"],"backgroundTag":"sql-query-failed","analyzedSha":"f3058517a153da500bf4551f46d79b91bf8ec552","analyzedAt":"2026-09-13T14:04:16.340Z","contentChangedAt":"2026-09-13T14:04:16.340Z","schemaVersion":2},"datasetVersion":"2026-09-20T23:17:15.980Z"}