Database value validation transform Icon Database value validation

Description

The Database value validation transform checks incoming rows against a target table before you write them.

Relational inserts often fail on one column of one row among millions, with a database message that does not name the stream field or the value. This transform loads the table’s column types, lengths, nullability and encoding at start, then checks every mapped field on every row. Valid rows are passed through unchanged. Invalid rows are sent to an error handling hop with all failing fields of that row in one go.

Typical use: Source → Database value validation → Table Output / Insert / Update / PostgreSQL bulk loader, with an error hop from the validator to a text file or reject table.

This is complementary to Data Validator, which uses hand-authored rules. Database value validation keeps the rules in sync with the live table.

Supported Engines

Hop Engine

Supported

Single Threaded

Supported

Native Spark

Not Supported

Beam Spark

Not Supported

Beam Flink

Not Supported

Beam Dataflow

Not Supported

Options

Option Description

Transform name

Unique name of this transform in the pipeline

Connection

Relational database connection of the table to validate against

Schema

Optional schema of the target table. You can use a variable, for example '${SCHEMA_NAME}'.

Table

Name of the table to validate against. You can use a variable, for example '${TABLE_NAME}'.

Browse table

Select the schema and table from the database

Get fields / Enter mapping

Map stream fields to table columns. When the mapping is empty, stream fields are matched to table columns by name.

Omit values from error messages

Show failed values as ? so they do not appear in the log or on the error hop

Fail if required columns are not mapped

Fail at start when a NOT NULL table column without a default is missing from the mapping

Error separator

String used to concatenate multiple field errors on the same row. Default is '; '.

Checks

The checks that run depend on the database dialect. A generic JDBC profile always covers nullability, string length, numeric precision/scale, integer ranges and type conversion. PostgreSQL also checks:

  • varchar / char length in Unicode characters (not Java String.length())

  • NUL (U+0000) in text, which PostgreSQL reports as invalid byte sequence for encoding "UTF8": 0x00

  • values that cannot be encoded in the database character set (SHOW server_encoding)

  • numeric(p,s) overflow (after rounding to scale, matching PostgreSQL)

  • int2 / int4 / int8 ranges

  • UUID format and JSON/JSONB parse

Unique keys, foreign keys and CHECK constraints are not pre-flighted. Those still fail at write time.

Error hop

Enable error handling on this transform. The error description, fields and codes name every failing column on the row:

Code Meaning

DBV001

Null in a NOT NULL column

DBV002

String too long

DBV003

Encoding / NUL character

DBV004

Numeric overflow

DBV005

Type conversion

DBV006

Integer out of range

DBV007

Invalid UUID

DBV008

Invalid JSON

At the end of the stream the transform logs how many rows were checked, how many were rejected, and error counts per column.

Notes

  • Table metadata is loaded once at start. After that the transform does not talk to the database.

  • An empty mapping matches incoming field names to table columns by name (case-insensitive).

  • If there is no error hop, the first bad row fails the transform, but the message still lists every failing field on that row.