Combination lookup/update

This transform will…​

1. If this combination of business key fields exists, return its technical key (surrogate id);

2. If this combination of business key doesn’t exist yet, insert a row with the new key fields and return its (new) technical key

This transform creates/maintains a technical key out of data with business keys. After passing through this transform all of the remaining data changes for the dimension table can be made as updates, as either a row for the business key already existed or was created.

This transform will maintain the key information only. You must update the non-key information in the dimension table, e.g. by putting an update transform (based on technical key) after the combination update/lookup transform.

Apache Hop will store the information in a table where the primary key is the combination of the business key fields in the table. Because this process can be slow if you have a large number of fields, a “hash code” field is supported that is representing all fields in the dimension. This can speed up lookup performance dramatically while limiting the fields to index to one field only.

Options