What causes "Error inserting 'unknown' row in dimension [" in pentaho/pentaho-kettle?

Check the wrapped cause and the isql in the message for the real database error (most often a duplicate-key or constraint violation)

pentaho/pentaho-kettle · error · KettleDatabaseException

Error inserting 'unknown' row in dimension [

Error message

Error inserting 'unknown' row in dimension [

What it means

The step inserts an 'unknown' placeholder row (the row returned for unmatched lookups, dated from null to infinity) into the dimension table via execStatement. Any KettleException during this insert is wrapped in a KettleDatabaseException with this message plus the schema-qualified table and the SQL. It means the default/unknown dimension member could not be added to the table.

Solutions

Check the wrapped cause and the isql in the message for the real database error (most often a duplicate-key or constraint violation)
Verify whether the 'unknown' row already exists (key value 0 or the DB-generated one); if so, the step's 'unknown' insert is typically a one-time operation and existing rows are fine
Ensure the connection user has INSERT privileges on the dimension table
Add defaults or make non-key columns nullable so an all-NULL unknown row can be inserted
Verify schema/table name and the database type's auto-increment unknown-row SQL support

Example fix

// before: unknown row insert fails with duplicate key on second run
// after: ensure the unknown row exists once, e.g.
INSERT OR IGNORE INTO dim_customer (dim_customer_tk, version, date_from, date_to)
VALUES (0, 1, NULL, '2199-12-31');

Defensive patterns

Strategy: try-catch

Validate before calling

// SQL: pre-create the unknown row and check privileges before running the transformation
SELECT COUNT(*) FROM dim_customer WHERE dim_customer_tk = 0; -- should be 1 after first init
GRANT INSERT ON dim_customer TO etl_user;

Try / catch

try {
  processRow();
} catch (KettleDatabaseException e) {
  if (e.getMessage().startsWith("Error inserting 'unknown' row")) {
    if (e.getCause() instanceof KettleDatabaseException && String.valueOf(e.getCause()).contains("duplicate")) {
      logBasic("Unknown row already present; continuing"); // idempotent case
    } else { logError("Unknown-row insert failed: " + e.getCause()); throw e; }
  } else throw e;
}

Prevention

Give non-key columns defaults or allow NULL so an all-unknown row can be inserted
Treat the unknown-row insert as one-time initialization; don't clear it between runs
Ensure INSERT privilege on the dimension table for the ETL user
Verify schema.table naming and the DB-specific auto-increment unknown-row SQL

When it happens

Trigger: data.db.execStatement(isql) throws while executing the 'unknown' row INSERT, either a plain INSERT or a database-specific getSQLInsertAutoIncUnknownDimensionRow statement when the key is auto-increment.

Common situations: Unknown row already inserted by a previous run (duplicate key on the technical key field); missing INSERT privileges; NOT NULL columns in the table with no defaults; wrong schema/table name; auto-increment insert helper SQL unsupported by the DB version.

Related errors

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

Appendix: source

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

    RowMetaAndData r = data.db.getOneRow( sql );
    Long count = r.getRowMeta().getInteger( r.getData(), 0 );
    if ( count.longValue() == 0 ) {
      String isql = null;
      try {
        if ( !databaseMeta.supportsAutoinc() || !isAutoIncrement() ) {
          isql =
            "insert into "
              + data.schemaTable + "(" + databaseMeta.quoteField( meta.getKeyField() ) + ", "
              + databaseMeta.quoteField( meta.getVersionField() ) + ") values (0, 1)";
        } else {
          isql =
            databaseMeta.getSQLInsertAutoIncUnknownDimensionRow( data.schemaTable, databaseMeta.quoteField( meta
              .getKeyField() ), databaseMeta.quoteField( meta.getVersionField() ) );
        }

        data.db.execStatement( databaseMeta.stripCR( isql ) );
      } catch ( KettleException e ) {
        throw new KettleDatabaseException( "Error inserting 'unknown' row in dimension ["
          + data.schemaTable + "] : " + isql, e );
      }
    }
  }

  @Override
  public boolean init( StepMetaInterface smi, StepDataInterface sdi ) {
    meta = (DimensionLookupMeta) smi;
    data = (DimensionLookupData) sdi;

    if ( super.init( smi, sdi ) ) {
      meta.actualizeWithInjectedValues();
      data.min_date = meta.getMinDate();
      data.max_date = meta.getMaxDate();

      data.realSchemaName = environmentSubstitute( meta.getSchemaName() );
      data.realTableName = environmentSubstitute( meta.getTableName() );

View on GitHub (pinned to f3058517a1)