Lookup Items

Lookup items allow you to use unmanaged reference data in ONE. They provide a list of predefined, formatted, and cleansed values that can be compared to raw data in order to help with data cleansing, verification, matching, and enrichment. While lookups can be used both in DQ evaluation rules and in detection rules, modifying or extending them requires advanced knowledge of the platform.

Therefore, we strongly recommend using managed reference datasets instead, which can be easily created in ONE Data through data deduplication (see Import Data from a Catalog Item, section Export deduplicated data). Additionally, using ONE Data tables enables real-time rule maintenance as any changes made to data can be automatically propagated to the relevant rule.

Lookup files can be found in Data Quality > Lookup Items.

The following guide is intended for advanced users.

Lookup types

Lookup items have one of the following types:

Local: The lookup data is built into a lookup file (.lkp), either from a catalog item attribute or from a file you upload.
Snowflake: The lookup points to one column of a Snowflake table, and rules match values directly in Snowflake, so the reference data never leaves the warehouse. Snowflake lookups can be used only in rules evaluated in pushdown. See Snowflake lookups.

Lookups created before version 17.2.0 are local lookups.

Create lookup item

The following steps create a local lookup. To create a Snowflake lookup, see Create a Snowflake lookup.

In Data Quality > Lookup Items select Create.
Provide general information about the lookup item, such as name and description (optional).

Provide descriptive names, or descriptions, to make it easier to locate the lookup in MinIO.

Select whether you want to create the lookup from a catalog item attribute, or upload a lookup file.

If you select Choose from Catalog Items, select the required catalog item and attribute.

You can create lookups directly from attributes, in which case this information will be pre-filled for you.

You can do this from the catalog item view from multiple tabs, including Overview, Data, and Profile & DQ insights, or from the attribute view. Use the three-dot menu and select Create new lookup.

If you select Upload from computer, drag and drop the lookup file to the space provided, or browse files.

Bulk upload of lookup items is not supported.

In Data Configuration, define the following:
1. Rebuild strategy: Choose when the lookup data is updated.
  - NEVER: Data is not updated.
  - ON DATA CHANGE: Data is updated automatically every time after the original catalog item is updated (with a delay of several seconds).
    
    This option is only available for ONE Data catalog items.
  - SCHEDULED: Schedule regular updates for the lookup data. To enable scheduled rebuilds, first create a schedule. Use the three-dot menu and select Schedule rebuild.
2. Duplicates handling:
  - First: One duplicated value is permitted for each value in the lookup item. The one allowed duplicate is the first occurrence of the duplicated value.
  - Omit: No duplicated values are accepted in lookup items.
  - Accept: All duplicates are accepted and there is no limit to the number of repeated values in a lookup item.
3. Matching values:
  - Remove accents: If selected, removes all diacritic marks (accents).
  - Remove repeated characters: If selected, removes repeated characters.
  - Squeeze whitespaces: If selected, replaces repeated white space characters with a single space character.
  - Upper case: If selected, converts all words to upper case.
4. Supported characters: Define the supported characters using the format [:letter:] [:number:].
  
  If defined, characters not contained in this set are removed or substituted. By default, all characters are supported ([:all:]).
5. Substitute with: Define a string to substitute any unsupported characters, if you have defined specific supported characters in Supported Characters.
Select Save.
Publish the draft.
Select Build Lookup.
The lookup is built. You can now edit lookup metadata, manage access, view and restore historical versions, and edit, delete, or update the lookup.

Edit lookups

Go to Data Quality > Lookup Items. In the three-dot menu of the lookup item you want to modify, select Edit.

The editing options available depend on the lookup item type. If you created the lookup using a local file, you can only edit the general information. Otherwise, you need to upload a new lookup version.

Lookups can also be built as a result of rule configuration. This happens when you select the is (not) from catalog item option when defining rule logic.

System-created lookups, that is, those that were created automatically during rule implementation, are also listed on the lookups screen. You can edit them, but changes made here can break the rule. To check whether a lookup is still used, see where a lookup is used.

Delete lookups

Before you delete a lookup, check whether it’s used: in Data Quality > Lookup Items, select the lookup and see what its Occurrence tab shows.

Whether you can delete the lookup depends on what the tab lists:

Rules: Lookups used in published rules can’t be deleted. Unpublished rules and unpublished changes to a rule don’t count. For each listed rule, do one of the following:
- Remove the lookup from the rule and publish the rule.
- Delete the rule.
DQ Firewalls: Lookups used in DQ firewalls can be deleted. Make sure that none of the listed DQ firewalls needs the lookup.

To delete the lookup, go to Data Quality > Lookup Items. In the three-dot menu of the lookup item you want to delete, select Delete.

If you can’t delete the lookup even though no rules are listed, the lookup is used in rules you don’t have access to.

Update lookups

Lookup items that have been uploaded cannot be updated within ONE, only reuploaded. See Upload a new lookup version.

Select Update lookup to rebuild lookups with the latest available data. To schedule these updates, use the three-dot menu and select Schedule rebuild.

Upload a new lookup version

To upload an updated version of a lookup item:

Select the required item.
Select Upload new version.
Drag and drop files to the area provided, or select Browse files to use the file explorer to search for lookup files (.lkp).
Once the lookup has been uploaded, it is in draft state. Publish the page to complete the update.

Lookup paths are automatically updated in Rules when the lookup is updated.

Lookup storage

Any lookups in ONE are part of OneFileSystem in MinIO. Specifically, they are stored in the bucket LOOKUPS.

Lookup files in ONE Desktop

Lookup files contain reference data that can help with the cleansing, verification, matching and enrichment of records by comparing raw data records to formatted and cleansed records.

Lookup files are an integral part of data quality and can be created in ONE Desktop using the Lookup Builder step, and incorporated into plans using the Lookup Reader step.

Lookup files built using the Lookup Builder step in ONE Desktop are available in Files > data > out > [name].lkp , and can then be uploaded to ONE.

Use lookups in rules

To reference lookups in rule conditions, use the options is from lookup and is not from lookup. For Snowflake lookups, use is in lookup table and is not in lookup table. See Use Snowflake lookups in rules.

You can also create a lookup directly in a rule condition using is from catalog item or is not from catalog item. However, this creates a new lookup each time, even if one for the same attribute already exists, which results in duplicates and extra rebuild jobs for scheduled lookups. We recommend creating a lookup once in Data Quality > Lookup Items and then referencing it using is from lookup in all rules that need it. Use is from catalog item only when you need a new lookup.

If you are using Advanced Expression and not Condition Builder, use the syntax isInFile to reference lookup files. Use isInFile and the name of the lookup in the format lookup:<name>, for example: (isInFile(ATTRIBUTE_1, "lookup:city")).

Alternatively, use the <lookupItemId>, where <lookupItemId> is the ID visible in the browser address. For example: (isInFile(ATTRIBUTE_1, "lookup:e1326a77-ee1b-4d57-acf5-55fe990d731e")).

isInFile works only with local lookups. For Snowflake lookups, use isInLookupTable. See Use Snowflake lookups in rules.

See where a lookup is used

The Occurrence tab of a lookup shows which rules and DQ firewalls use it. This helps you check what a change to the lookup would affect and decide whether you can delete it. See Delete lookups.

To view the tab, you need at least View metadata access to the DQ Configuration entity. By default, all governance roles have this access. See Governance Roles.

To open the tab, in Data Quality > Lookup Items, select a lookup and go to its Occurrence tab.

The tab has two categories, each with the number of items found:

Rules: Published rule versions that use the lookup in one of the following ways:
- In a condition, such as is from lookup, is not from catalog item, or is in lookup table.
- In an advanced expression that references the lookup by its ID, for example, isInFile(ATTRIBUTE_1, "lookup:<lookupItemId>") or isInLookupTable(ATTRIBUTE_1, "lookup:<lookupItemId>").
  
  Rules that reference the lookup by name, for example, isInFile(ATTRIBUTE_1, "lookup:city"), are currently not listed and don’t prevent the lookup from being deleted.
DQ Firewalls: Deployed DQ firewalls that use the lookup. A DQ firewall is listed based on its own record of the lookups it uses, not on the rules it contains. If the lookup has never been built (for example, a Snowflake lookup), no DQ firewalls are listed.

Each rule is listed once, even if it uses the lookup several times. Only the rules you have access to are listed.

The tab can show one of the following messages:

When no rule or DQ firewall uses the lookup: This lookup is not referenced by any rule or firewall. It may be safe to delete.

This message also appears when the lookup is used only in rules you don’t have access to. In that case, you still can’t delete the lookup.
When the search for rules fails or the usage information can’t be loaded: Usage information may be incomplete — some associations could not be fully resolved.

To find duplicate lookups, filter the Lookup Items list by Source Catalog Item and Key Attribute. Duplicates have the same source catalog item and key attribute. Then open the Occurrence tab of each lookup to see whether any rule or DQ firewall uses it.

Snowflake lookups

A Snowflake lookup points to one column of a catalog item stored in Snowflake. This column is called the lookup key column. Rules that use the lookup check values directly in Snowflake, so the reference data never leaves the warehouse.

Use Snowflake lookups when your reference tables are stored in Snowflake and you evaluate data quality in pushdown.

A Snowflake lookup is a live link to the table:

There is no lookup file, no build step, and no rebuild schedule. Rebuilding the lookup and uploading a lookup file are not available for Snowflake lookups.
The lookup can be used as soon as it is published.
Changes to the Snowflake table are used in the next DQ evaluation.

The lookup detail page shows a preview of the data from the source catalog item. To see the preview, you need View data access to the catalog item.

Prerequisites

The catalog item that you create the lookup from must meet the following conditions:

The catalog item is a published Snowflake table, identified by its schema and table name.
Pushdown processing is enabled on its Snowflake connection and isn’t turned off for the catalog item.

If a condition isn’t met, the lookup can’t be created and an error message explains which condition failed. For example, if pushdown is turned off, the error Pushdown is disabled for the catalog item is shown with the detail Pushdown is disabled on catalog item '<id>'.

Create a Snowflake lookup

Before you start, make sure you have the required permissions.

In Data Quality > Lookup Items, select Create.
In Lookup type, select Snowflake.
Select the Snowflake catalog item and the attribute to use as the lookup key column.
Optionally, enter a name and description. If you don’t enter a name, a name is generated from the names of the catalog item and attribute.
Select Save.
Publish the draft.

The lookup is ready to use in rules.

You can also start from the attribute: in the three-dot menu of the attribute, select Create lookup. The catalog item and attribute are then pre-filled.

Use Snowflake lookups in rules

Before you start, make sure you have the required permissions.

In Condition Builder, use the is in lookup table or is not in lookup table condition in the List of values group. These conditions list only Snowflake lookups. The is from lookup and is not from lookup conditions list only local lookups.

In Advanced Expression, use the isInLookupTable function with the name or ID of the lookup in the format lookup:<name> or lookup:<lookupItemId>. For example: isInLookupTable(ATTRIBUTE_1, "lookup:country_codes").

The function returns TRUE when the value exists in the lookup key column. For details, see String functions.

When you publish the rule, the following is checked:

The lookup is published. If the lookup is a draft, publishing fails with an error that asks you to publish the lookup first.
The data type of the rule input matches the data type of the lookup key column. Otherwise, publishing fails with the error Lookup '<name>' key type <type> does not match expected type <type>. The same applies to local lookups.
isInLookupTable is used only with Snowflake lookups, and isInFile only with local lookups. If you mix them up, publishing fails with an error that names the function to use instead.

Rules that use Snowflake lookups

A rule that references a Snowflake lookup can be evaluated only in pushdown. The rule detail and the rule list show a flag that explains this, for example: Uses pushdown-only SNOWFLAKE lookup '<name>'.

Such rules have the following restrictions:

You can apply the rule only to catalog items that use the same Snowflake connection as the lookup and don’t have pushdown turned off. Applying the rule to other catalog items is blocked when you publish, with a message that says which of the two conditions isn’t met.
You can’t deploy a DQ firewall that contains the rule, because DQ firewalls never evaluate in pushdown.
You can’t test the rule with Test Rule or on the DQ firewall Debug tab. See Test rule.

If a DQ evaluation or a monitoring project run contains such a rule on a catalog item that has pushdown turned off or uses a different Snowflake connection than the lookup, the job isn’t submitted. The error starts with Cannot submit DQ evaluation:, followed by one line for each affected catalog item:

Rule '<rule>' uses pushdown-only SNOWFLAKE lookup '<lookup>'; catalog item '<item>' has pushdown disabled.
Rule '<rule>' uses SNOWFLAKE lookup '<lookup>' from a different connection than catalog item '<item>'.

You can also apply the rule to terms. If the term is used on a catalog item that has pushdown turned off or uses a different Snowflake connection than the lookup, the rule is skipped for that catalog item during DQ evaluation and is shown as not applicable in the list of assigned rules.

Permissions for Snowflake lookups

To create a Snowflake lookup, you need the same access as for creating a local lookup from a catalog item:

Editing access or Full access to the DQ Configuration entity. By default, the ONE Administrator, Data Owner, and Data Steward roles have this access. See Governance Roles.
At least View metadata access to the catalog item and its connection.

You don’t need access to the catalog item data.

To use a Snowflake lookup in a rule, you need to be able to view the lookup item. Access to the underlying catalog item is not required. During DQ evaluation, the credentials of the connection of the evaluated catalog item are used.

Snowflake lookups: Good to know

Only Snowflake is supported. Other pushdown sources, such as Databricks or BigQuery, are not supported.
Matching is exact. Case-insensitive and accent-insensitive matching is not available.
The values of the lookup key column are converted to the data type of the checked value. If a value can’t be converted, the DQ evaluation fails.
Rules that use Snowflake lookups can’t be evaluated without pushdown and can’t be part of a deployed DQ firewall.
Rules that use Snowflake lookups can only be applied to catalog items from the same Snowflake connection as the lookup.
You can’t test rules that use Snowflake lookups in ONE. To verify such a rule, run DQ evaluation in pushdown on a Snowflake catalog item, or run a monitoring project.

Troubleshooting

Uploading a lookup file fails

Problem: When trying to manually upload a lookup file (Lookup Items tab), it fails with the following error: Upload of <user_file>.lkp failed.
Possible cause: To determine what caused the upload issue, open the log file and look for the error message. If the error is Refused to connect to <URL>, the property that allows the connection from ONE to ONE Object Storage (MinIO) is likely misconfigured or missing.
Possible solution: Make sure the MinIO URL is correctly configured in the property ataccama.one.webserver.content-security.extra-urls.

/opt/ataccama/one/one-webapp/application.properties

ataccama.one.webserver.content-security.extra-urls={'img-src':{'<link_to_minio>'}, 'connect-src':{'<link_to_minio>'}}.

Depending on how your configuration is managed, the property ataccama.one.webserver.content-security.extra-urls is configured in the application.properties file located in /opt/ataccama/one/one-webapp/etc/ folder.

For more information, see ONE Web Application Configuration.

Capability overrides not displaying in DPM Admin Console

Problem: Running a freshness job causes capability overrides not to display.
Solution: Restart DPE remotely from the console before accessing capability overrides.

Was this page useful?