{"record":{"id":"6588504180401e19","repo":"pentaho/pentaho-kettle","slug":"error-inserting-unknown-row-in-dimension","errorCode":null,"errorMessage":"Error inserting 'unknown' row in dimension [","messagePattern":"Error inserting 'unknown' row in dimension \\[","errorType":"exception","errorClass":"KettleDatabaseException","httpStatus":null,"severity":"error","filePath":"engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java","lineNumber":1678,"sourceCode":"    RowMetaAndData r = data.db.getOneRow( sql );\n    Long count = r.getRowMeta().getInteger( r.getData(), 0 );\n    if ( count.longValue() == 0 ) {\n      String isql = null;\n      try {\n        if ( !databaseMeta.supportsAutoinc() || !isAutoIncrement() ) {\n          isql =\n            \"insert into \"\n              + data.schemaTable + \"(\" + databaseMeta.quoteField( meta.getKeyField() ) + \", \"\n              + databaseMeta.quoteField( meta.getVersionField() ) + \") values (0, 1)\";\n        } else {\n          isql =\n            databaseMeta.getSQLInsertAutoIncUnknownDimensionRow( data.schemaTable, databaseMeta.quoteField( meta\n              .getKeyField() ), databaseMeta.quoteField( meta.getVersionField() ) );\n        }\n\n        data.db.execStatement( databaseMeta.stripCR( isql ) );\n      } catch ( KettleException e ) {\n        throw new KettleDatabaseException( \"Error inserting 'unknown' row in dimension [\"\n          + data.schemaTable + \"] : \" + isql, e );\n      }\n    }\n  }\n\n  @Override\n  public boolean init( StepMetaInterface smi, StepDataInterface sdi ) {\n    meta = (DimensionLookupMeta) smi;\n    data = (DimensionLookupData) sdi;\n\n    if ( super.init( smi, sdi ) ) {\n      meta.actualizeWithInjectedValues();\n      data.min_date = meta.getMinDate();\n      data.max_date = meta.getMaxDate();\n\n      data.realSchemaName = environmentSubstitute( meta.getSchemaName() );\n      data.realTableName = environmentSubstitute( meta.getTableName() );\n","sourceCodeStart":1660,"sourceCodeEnd":1696,"githubUrl":"https://github.com/pentaho/pentaho-kettle/blob/f3058517a153da500bf4551f46d79b91bf8ec552/engine/src/main/java/org/pentaho/di/trans/steps/dimensionlookup/DimensionLookup.java#L1660-L1696","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","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"],"exampleFix":"// before: unknown row insert fails with duplicate key on second run\n// after: ensure the unknown row exists once, e.g.\nINSERT OR IGNORE INTO dim_customer (dim_customer_tk, version, date_from, date_to)\nVALUES (0, 1, NULL, '2199-12-31');","handlingStrategy":"try-catch","validationCode":"// SQL: pre-create the unknown row and check privileges before running the transformation\nSELECT COUNT(*) FROM dim_customer WHERE dim_customer_tk = 0; -- should be 1 after first init\nGRANT INSERT ON dim_customer TO etl_user;","typeGuard":null,"tryCatchPattern":"try {\n  processRow();\n} catch (KettleDatabaseException e) {\n  if (e.getMessage().startsWith(\"Error inserting 'unknown' row\")) {\n    if (e.getCause() instanceof KettleDatabaseException && String.valueOf(e.getCause()).contains(\"duplicate\")) {\n      logBasic(\"Unknown row already present; continuing\"); // idempotent case\n    } else { logError(\"Unknown-row insert failed: \" + e.getCause()); throw e; }\n  } else throw e;\n}","preventionTips":["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"],"tags":["database","insert","dimension-lookup","unknown-row"],"backgroundTag":"database-write-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"}