> For the complete documentation index, see [llms.txt](https://docs.veza.com/4yItIzMvkpAvMVFAamTf/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.veza.com/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/transformers/lookup-tables.md).

# Lookup Tables

Use lookup tables to transform identity attributes for target systems

### Overview

You can use Lookup transformers to convert identity attributes from a source system into appropriate values for target systems based on CSV reference tables. This is particularly useful when mapping values between systems that use different naming conventions, codes, or formats for the same conceptual data.

For example, you might need to transform a "Location" attribute from Workday (which might be stored as location codes like "MN001") into corresponding values for country, country code, or city names in a target system.

Use Table Lookup Transformers when:

* You need to map source attribute values to different values in target systems
* You have standardized reference data that must be consistent across applications
* You need to extract different pieces of information from a single attribute value
* You have complex mapping requirements that built-in transformers cannot support

#### Examples

1. **Geographic Information**:
   * Transform location codes to country, region, city, or time zone information
   * Map office codes to physical addresses or facility types
2. **Organizational Mapping**:
   * Convert department codes to department names or business units
   * Map cost centers to budget codes or accounting categories
3. **System-Specific Configurations**:
   * Transform job titles to role designations in target systems
   * Convert skill codes to certification requirements or training needs

### How It Works

The Table Lookup Transformer references CSV-based mappings between source and destination values. When synchronizing user attributes, Veza:

1. Takes the source attribute value
2. Looks up this value in the specified lookup table
3. Returns the corresponding value from the designated return column
4. Applies this value to the target attribute

#### Lookup Table Structure

Lookup tables are CSV files with columns that map values from a source of identity to destination values. Each row represents a mapping entry. The first row must contain the column headers.

For example, a location mapping table might look like:

```csv
location_code,state_code,state,city
MN001,MN,Minnesota,Minneapolis
CA001,CA,California,Los Angeles
TX001,TX,Texas,Houston
TX002,TX,Texas,Austin
```

### Creating and Managing Lookup Tables

#### Creating a Lookup Table

To create a new lookup table:

1. Open a draft version of your policy (you can only add lookup tables to a draft) and select the **Workflow Components** tab
2. Select the **Lookup Tables** card
3. Click **+** on the card. When the policy has no lookup tables yet, you can also click **Create Lookup Table**
4. Provide a **Name** and optional **Description** for the lookup table
5. Drag a CSV file or click **Browse** to upload your reference data (one CSV file, up to 10 MB)
6. Review the preview of the uploaded rows
7. Click **Save** to store the lookup table

#### Managing Lookup Tables

From the **Lookup Tables** card, you can:

* **Export** the table's CSV
* **Edit** the table's name, description, or CSV. Renaming a table also updates the formatters that reference it by its old name
* **Delete** tables that are no longer needed. Delete is unavailable while an action, workflow, condition, transformer, or transformer function still references the table

#### Updating a lookup table

To update an existing lookup table:

1. Create a new policy draft version.
2. Delete the CSV from the existing lookup table (do not delete the table itself).
3. Upload the updated CSV.
4. Publish the draft.

### Using Table Lookup Transformers

#### Basic Syntax

To use a Table Lookup Transformer in a common or action-synced attribute:

1. In **Destination Attribute**, choose the attribute on the target entity that will be updated
2. In **Formatter**, choose the source attribute to transform
3. In **Then Apply**, enter the lookup function: the lookup table name, the column to match against, the column containing values to return, and (optionally) a default value to return when no match is found.

![Configuring an action-level attribute transformer using lookup tables.](https://1967633068-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F-MZDkWMxox3pekd0NsZJ%2Fuploads%2Fgit-blob-dffd8ea131d182ba722cbc9581f46921b33de307%2Flookup-tables.png?alt=media)

**Then Apply** holds the function chain on its own, with no braces and starting with a pipe, for example `| LOOKUP, "locationTable", "location_code", "city"`. Veza applies it to the value that **Formatter** produces.

The equivalent complete expression, used wherever a single field carries both the source value and its transformations, is:

```txt
{<value> | LOOKUP, "<table_name>", "<column_name>", "<return_column_name>", "<default_value>"}
```

Where:

* `<value>` is the source attribute to transform (for example, `location`)
* `<table_name>` is the name (or ID) of the lookup table to use
* `<column_name>` is the column in the table to match against
* `<return_column_name>` is the column containing the value to return
* `<default_value>` is an optional value to return when no matching row is found. Omit it to make the transformation fail when there is no match. See [Handling Missing Values](#handling-missing-values).

Separate the function name from its parameters with a comma, and enclose each parameter in double quotes, as shown above. Veza matches the table name and both column names exactly, including case.

#### Examples

Assuming a user has `"location": "CA001"` and a lookup table named `locationTable` structured as shown earlier:

| Formatter                                                              | Result          |
| ---------------------------------------------------------------------- | --------------- |
| `{location \| LOOKUP, "locationTable", "location_code", "city"}`       | `"Los Angeles"` |
| `{location \| LOOKUP, "locationTable", "location_code", "state"}`      | `"California"`  |
| `{location \| LOOKUP, "locationTable", "location_code", "state_code"}` | `"CA"`          |

### Advanced Features

#### Pipeline Transformations

You can combine lookup transformations with other transformation functions in a pipeline:

```txt
{location | LOOKUP, "locationTable", "location_code", "state_code" | LOWER}
```

This would look up the `state_code` corresponding to the `location` value and convert it to lowercase.

#### Handling Missing Values

By default, when a source value is not found in the lookup table, the transformation fails for that attribute. To return a fallback value instead of failing, supply an optional fourth parameter that sets a default value:

```txt
{location | LOOKUP, "locationTable", "location_code", "city", "Unknown location"}
```

When the source value is not found in the table, the transformer returns the default value (`Unknown location` in this example) instead of failing. To return an empty string on a miss, pass an empty default (`""`).

The default value applies only when the lookup runs but finds no matching row. It does not cover configuration errors: if the lookup table, the match column, or the return column cannot be found, the transformation still fails, even when a default value is provided.

For full coverage, ensure your lookup table includes entries for all possible source values that may be encountered during provisioning, or supply a default value to handle unmatched inputs.

To ensure robust provisioning workflows, include all expected values in your lookup table, validate source data before implementing lookup transformations, and test transformations with representative data sets.

### Technical Details

#### Implementation Notes

* Lookup tables are immutable and automatically deleted when no longer referenced by any policy version
* Multiple policy versions can reference the same lookup table (for example, an active version and a draft version)
* Lookup tables are defined at the policy level and can be referenced by any transformer within the policy
* Lookup tables can have multiple columns to support different transformations from the same reference data

#### Best Practices

1. **Standardize Naming**: A lookup-based transformer references the table by the **Name** you gave it when you created it (or by its table ID), not by the CSV file name. Apply consistent conventions for both the table and columns.
2. **Document Mappings**: Add descriptions for each lookup table to explain its purpose
3. **Validate Data**: Ensure lookup tables are complete and accurate before using them in transformers. Consider how lookup tables will be maintained over time, especially for values expected to change.

### Troubleshooting

#### Common Issues

| Issue                             | Resolution                                                                                                                     |
| --------------------------------- | ------------------------------------------------------------------------------------------------------------------------------ |
| Value not found in lookup table   | Add the missing mapping to the lookup table, or supply a default value as the fourth LOOKUP parameter                          |
| Incorrect column name referenced  | Check the column names in your lookup table (they are case-sensitive). A default value does not cover this error               |
| Lookup table name not found       | Check that the name matches the table's **Name** exactly, including case, and that the table exists in the same policy version |
| Unexpected transformation results | Verify the lookup table content and ensure the correct columns are specified                                                   |

### Related Topics

* [Attribute Transformers](/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/transformers.md)
* [Common Transformers](/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/transformers.md#common-transformers)
* [Then Apply](/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/transformers.md)
* [Lifecycle Management Workflows](/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/policies-workflows/policies.md#add-workflows-to-policies)


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.veza.com/4yItIzMvkpAvMVFAamTf/features/lifecycle-management/transformers/lookup-tables.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.