Skip to main content

Add lookups

A lookup resolves a foreign-key column back to the right row in the target database at deploy time. Use them whenever your table references another table by a transient identity key.

Why lookups exist​

Alternative keys solve the problem of porting a row's own identity across environments. Lookups solve the other half: references to other rows.

Example: ORDER_ITEM has a PRODUCT_ID column that points at PRODUCT. The ID is transient: PRODUCT_ID = 5 in dev might be PRODUCT_ID = 12 in UAT. A deployment that hard-codes PRODUCT_ID = 5 will either fail or silently point at the wrong product.

A lookup tells DataStar: "don't copy the literal ID; find the target row by its business key and substitute the right ID."

Basic lookup​

<Column Name="PRODUCT_ID">
  <Lookup Table="PRODUCT" Column="ID">
    <Criteria Column="PRODUCT_CODE"/>
  </Lookup>
</Column>

What this means:

  • Table. The table to look the ID up in (the parent table).
  • Column. The column in that table holding the ID we want.
  • <Criteria>. The business-key column(s) used to find the right row.

At extract time, DataStar reads PRODUCT_CODE from the source database. At deploy time, the generated script uses that code to find the matching ID in the target's PRODUCT table and substitutes it. The literal ID value is never deployed.

Compound criteria​

If one column isn't enough to uniquely identify the lookup, add more:

<Column Name="PARENT_ID">
  <Lookup Table="PARENT_TABLE" Column="ID">
    <Criteria Column="REGION"/>
    <Criteria Column="CODE"/>
  </Lookup>
</Column>

DataStar joins on all criteria columns together.

Filtering the lookup​

Sometimes a column can reference one of several parent tables (a discriminated reference). Scope the lookup with a filter:

<Column Name="PARENT_ID">
  <Lookup Table="PARENT_TABLE" Column="ID">
    <Criteria Column="NAME"/>
    <Filters>
      <Value Column="TYPE" Value="A"/>
    </Filters>
  </Lookup>
</Column>

Here, only rows where TYPE = 'A' in PARENT_TABLE are candidates for the lookup.

When to add a lookup​

Any column meeting all of these is a candidate:

  1. It's a foreign key (or semantically acts like one).
  2. The referenced table's key is transient across environments.
  3. The referenced table has a stable business key (i.e. its template declares an alternative key).

If any of those isn't true, a lookup won't help. In particular, a lookup against a parent table that doesn't have alternative keys will resolve to whatever ID happens to exist in the source and carry that value over, no better than a plain copy.

Verifying the generated script​

A lookup appears in the MERGE as a correlated subquery in the USING clause:

USING (VALUES (...,
  (SELECT ID FROM PRODUCT
    WHERE PRODUCT_CODE = 'SKU-100'),
  ...))

If you instead see a literal ID (..., 5, ...), the lookup isn't being applied; check the Column and Criteria attributes match real column names.

Common mistakes​

  • Pointing the lookup at the wrong table. The Table attribute is the parent (the one being referenced), not the current table.
  • Criteria column isn't unique. If the criteria match multiple rows, the generated subquery returns more than one row and the MERGE fails. Fix by adding more criteria columns or scoping with a filter.
  • Forgetting to mark the foreign-key column as non-transient on the child. The child row needs Transient="false" (or no Transient attribute) on the FK column; lookups only make sense on values DataStar is trying to deploy.

See also​

Last updated on