pentaho/pentaho-kettle · error · KettleDatabaseException

Unable to prepare dimension lookup

Error message

Unable to prepare dimension lookup

What it means

The step failed to prepare the JDBC PreparedStatement used for the dimension lookup SELECT. Any SQLException during connection.prepareStatement() for the lookup SQL is wrapped into a KettleDatabaseException with this message and the original SQLException as cause.

Solutions

1. Check the 'Caused by' SQLException in the log for the exact SQL error
2. Use the step dialog's 'SQL' button to generate/verify the dimension table DDL and run it
3. Verify connection settings (URL, schema, credentials) and that the table/columns exist
4. Grant SELECT on the dimension table to the connection user

Example fix

// before (log)
// Unable to prepare dimension lookup ... Unknown column 'cust_ky' in 'where clause'
// after
// ALTER TABLE dim_customer CHANGE cust_ky customer_key BIGINT;  -- or fix the key field name in the dialog

// verify lookup SQL objects exist before prepare
Database db = new Database(transMeta, databaseMeta);
db.connect();
if (!db.checkTableExists(dimTableMeta)) {
  throw new IllegalStateException("Dimension table missing: " + dimTableMeta.getTableName());
}

try {
  setDimLookup();
} catch (KettleDatabaseException e) {
  logError("Lookup prepare failed: " + e.getMessage() + ", cause=" + e.getCause());
  throw e;
}

When it happens

Trigger: setDimLookup (invoked from processRow/init before lookups) builds the lookup SELECT and calls prepareStatement; a bad table name, missing column in WHERE/ORDER BY, or connection-level failure triggers it. The MySQL-specific setFetchSize(0) line in the source shows this happens after statement creation, so most failures come from SQL syntax/schema.

Common situations: Dimension table doesn't exist or is in another schema; renamed column still referenced in the key mapping; database user lacks SELECT privileges; MySQL variant specific statement attributes unsupported by the driver.

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

• Couldn't prepare statement:
• 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 :

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

Appendix: source

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

      sql += " AND ? >= " + dateFromField + Const.CR;
      sql += " AND ? < " + dateToField + Const.CR;

      data.lookupRowMeta.addValueMeta( new ValueMetaDate( meta.getDateFrom() ) );
      data.lookupRowMeta.addValueMeta( new ValueMetaDate( meta.getDateTo() ) );
    }

    try {
      logDetailed( "Dimension Lookup setting preparedStatement to [" + sql + "]" );
      data.prepStatementLookup = data.db.getConnection().prepareStatement( databaseMeta.stripCR( sql ) );
      if ( databaseMeta.supportsSetMaxRows() ) {
        data.prepStatementLookup.setMaxRows( 1 ); // alywas get only 1 line back!
      }
      if ( databaseMeta.getDatabaseInterface().isMySQLVariant() ) {
        data.prepStatementLookup.setFetchSize( 0 ); // Make sure to DISABLE Streaming Result sets
      }
      logDetailed( "Finished preparing dimension lookup statement." );
    } catch ( SQLException ex ) {
      throw new KettleDatabaseException( "Unable to prepare dimension lookup", ex );
    }
  }

  protected boolean isAutoIncrement() {
    return techKeyCreation == CREATION_METHOD_AUTOINC;
  }

  /**
   * This inserts new record into dimension Optionally, if the entry already exists, update date range from previous
   * version of the entry.
   */
  public Long dimInsert( RowMetaInterface inputRowMeta, Object[] row, Long technicalKey, boolean newEntry,
                         Long versionNr, Date dateFrom, Date dateTo ) throws KettleException {
    DatabaseMeta databaseMeta = meta.getDatabaseMeta();

    if ( data.prepStatementInsert == null
      && data.prepStatementUpdate == null ) { // first time: construct prepared statement
      RowMetaInterface insertRowMeta = new RowMeta();

View on GitHub (pinned to f3058517a1)