Database Lookup
The Database Lookup Generator looks up a value from another table, such as finding a product's price using the product ID on the current row.
Use this framework when generated data must use existing reference or master data from another table.
For more information on creating a generator, see Create a generator.
Configuration fields
| Field | Description |
|---|---|
| Lookup Table Query | A SELECT statement, including joins when required, that returns the reference data. Give joined or computed columns explicit aliases. The aliases or column names returned by the query are used by Return Column and Lookup Condition. |
| Return Column | The column or alias whose value is returned when a match is found. It must exactly match a column name or alias from the lookup query. |
| Return Null if No Match Found | When enabled, returns null when no lookup match is found. |
| Default Return Value | The value returned when no match is found and null return is not selected. |
| Lookup Condition | An optional SQL WHERE-clause fragment. Use named placeholders such as :id to bind values from fields on the table being generated. Leave this field empty to look up values sequentially by row order. |
Lookup behavior
For condition-based lookup, the placeholder names in the Lookup Condition must match the field bindings configured for the generated field. A shared generator instance can be reused across fields, with each field mapping its placeholders through its generator bindings.
For sequential lookup, values are read in row order. Values repeat from the beginning for a non-unique target field. For a unique target field, lookup returns null after the reference rows are exhausted, and the batch fails if the target requires more unique values than the reference table provides.
For non-STRING target columns, keep the reference table in the same application as the dataset connectors so Synthetic Data can resolve the reference column's actual type. Otherwise, the lookup value is treated as VARCHAR(4000).
Limitations
-
Do not embed a Database Lookup Generator as a child of a Distribution Generator.
-
Multi-column lookup that returns several values from one matched row is not part of this feature.