RDM lookup display values are replicated as canonical codes in Google BigQuery

Issue

For an attribute configured with an RDM lookup and resolveLookupCode: true, the Reltio Entity API may return the expected human-readable RDM canonical value, while the Reltio Data Pipeline for Google BigQuery (GBQ) writes the canonical lookup code into the attribute's value field.

For example, assume the RDM mapping is:

Canonical code: 001
Canonical value: RDM Test

The Entity API may correctly return:

{
  "value": "RDM Test",
  "lookupCode": "001"
}

However, the corresponding GBQ representation may initially contain:

{
  "value": "001",
  "lookupCode": "001",
  "lookupRawValue": "001"
}

This can cause problems for analytics consumers that expect the value field in BigQuery to contain the human-readable RDM display value instead of the canonical code.

Example business configuration

An affected attribute may resemble the following:

{
  "label": "Service Types",
  "name": "ServiceTypes",
  "type": "String",
  "uri": "configuration/entityTypes/HCO/attributes/ServiceTypes",
  "lookupCode": "rdm/lookupTypes/ServiceTypes",
  "resolveLookupCode": true
}

Another example from the investigated configuration was:

{
  "label": "Places of Service",
  "name": "PlacesOfService",
  "type": "String",
  "uri": "configuration/entityTypes/HCO/attributes/PlacesOfService",
  "lookupCode": "rdm/lookupTypes/PlacesOfService",
  "resolveLookupCode": true
}

The tenant reviewed in the investigation had tenant-level lookup resolution disabled:

{
  "resolveLookupCode": false
}

while the affected attributes explicitly enabled it:

{
  "resolveLookupCode": true
}

This is a valid configuration. According to Reltio documentation, when the tenant-level property is false, the attribute-level resolveLookupCode value controls lookup resolution for that attribute.

Expected Reltio API behavior

When resolveLookupCode is enabled, Reltio documents an Entity API representation similar to:

{
  "value": "United Kingdom",
  "lookupCode": "GB"
}

In this mode, the API response does not normally include lookupRawValue. Reltio also notes that lookup data persisted in the tenant does not automatically update after changes to lookup configuration; affected data must be reloaded or the entity updated.

Therefore, for this example RDM mapping:

Raw/source value: RDM Test 001
Canonical code:    001
Canonical value:   RDM Test

the expected operational API representation is:

{
  "value": "RDM Test",
  "lookupCode": "001"
}

Symptoms

A typical symptom is that the Entity API and GBQ appear to disagree.

Entity API

{
  "ServiceTypes": [
    {
      "ov": true,
      "value": "RDM Test",
      "lookupCode": "001"
    }
  ]
}

GBQ before resolution

{
  "ServiceTypes_value": "001",
  "ServiceTypes_lookupCode": "001",
  "ServiceTypes_lookupRawValue": "001"
}

The lookup itself may still be working correctly. In the investigated case, the Reltio Hub and Entity API returned the human-readable RDM value, while the GBQ pipeline output retained the code in value. A targeted pipeline resynchronization by itself did not change that result.

Root cause

The issue was not caused by an invalid RDM lookup definition or an incorrect resolveLookupCode attribute setting.

Testing established that:

1. RDM successfully resolved the source value to its canonical code and display value.
2. The Entity API returned the expected human-readable canonical value.
3. The GBQ pipeline was publishing the lookup representation such that the canonical code was present in the value field.
4. Re-sending the affected entity through the pipeline without changing the GBQ pipeline configuration did not alter the output.

In this scenario, the required GBQ lookup-display behavior was controlled by an additional Data Pipeline configuration capability.

The affected tenant's working Data Pipeline configuration contained:

{
  "dataPipelineConfig": {
    "enabled": true,
    "gbqEnableAdditionalFeatures": true,
    "adapters": [
      {
        "type": "gbq",
        "enabled": true,
        "storageFormat": "COLUMNS",
        "ovOnly": false
      }
    ]
  }
}

The exact gbqEnableAdditionalFeatures property is not currently described in the public Reltio documentation located for this article. In the support case, Reltio Customer Engineering enabled and validated this setting specifically for the GBQ lookup-value requirement.

Resolution

Verify that RDM resolution is working in Reltio

Retrieve a known affected profile and confirm that the Entity API returns the expected canonical display value and code.

For example:

{
  "value": "RDM Test",
  "lookupCode": "001"
}

If instead the entity contains a lookup error such as:

{
  "value": "RDM Test 001",
  "lookupError": "1003: RDM canonical value mapping not found..."
}

resolve the RDM mapping problem first.

Reltio documents error 1003 as indicating that a canonical mapping cannot be found for the supplied value/source, which can result from a missing source mapping or a disabled lookup type, lookup code, or mapping.

Verify the business configuration

Confirm that the affected attribute is linked to the intended RDM lookup and has the required lookup-resolution configuration:

{
  "name": "ServiceTypes",
  "lookupCode": "rdm/lookupTypes/ServiceTypes",
  "resolveLookupCode": true
}

Do not disable resolveLookupCode solely to work around the GBQ representation if the business requires normalized lookup codes for matching, survivorship, or other mastering behavior.

Reltio specifically documents resolveLookupCode as the mechanism that resolves an RDM-backed attribute to its canonical value.

Have Reltio Support verify the GBQ additional-features configuration

For the case documented here, Reltio Customer Engineering enabled:

{
  "gbqEnableAdditionalFeatures": true
}

within the tenant's Data Pipeline configuration.

After the change, the pipeline produced the required lookup representation:

{
  "value": "RDM Test",
  "lookupCode": "001",
  "lookupRawValue": "001"
}

Because this property is not currently documented as a public self-service GBQ configuration parameter, customers should open a Reltio Support case to have the tenant reviewed before making this change.

Reltio's public GBQ documentation describes the Data Pipeline as Reltio-managed and directs customers to Support for configuration features that require enablement.

Resynchronize affected data

Changing pipeline behavior does not by itself rewrite records that have already been materialized in GBQ. Send the affected data back through the Data Pipeline after the configuration has been updated.

Reltio provides the syncToDataPipeline API for this purpose:

POST https://<env>.reltio.com/reltio/api/<tenant_id>/syncToDataPipeline

Reltio documents that this operation creates background reindex work and streams the selected tenant data to the Data Pipeline Hub.

For large environments, scope and scheduling should be planned appropriately rather than automatically performing a tenant-wide synchronization.

After synchronization, monitor the associated task and wait until the Data Pipeline queue has been drained before validating GBQ. Reltio recommends checking both task completion and that the Data Pipeline event count has reached zero.

Validation

Query representative lookup attributes after the resynchronization.

For example:

SELECT
  uri,
  ServiceTypes_value,
  ServiceTypes_lookupCode,
  ServiceTypes_lookupRawValue,
  PlacesOfService_value,
  PlacesOfService_lookupCode,
  PlacesOfService_lookupRawValue
FROM `<project>.<dataset>.<entity_table>`
WHERE uri = 'entities/<entity_id>';

Before the configuration change, the result may resemble:

{
  "ServiceTypes_value": "001",
  "ServiceTypes_lookupCode": "001",
  "ServiceTypes_lookupRawValue": "001"
}

After the configuration change and pipeline synchronization, the validated result was:

{
  "ServiceTypes_value": "RDM Test",
  "ServiceTypes_lookupCode": "001",
  "ServiceTypes_lookupRawValue": "001",
  "PlacesOfService_value": "RDM Test",
  "PlacesOfService_lookupCode": "001",
  "PlacesOfService_lookupRawValue": "001"
}

In ticket #137818, the customer confirmed this exact corrected representation in the sandbox GBQ environment after gbqEnableAdditionalFeatures was enabled and the affected entities were synchronized again.

Why does lookupRawValue appear in GBQ?

Do not use the Entity API response structure alone to determine which lookup-related fields should exist in the analytical GBQ schema.

For an operational Entity API response, Reltio documents that lookupRawValue is omitted when resolveLookupCode=true.

In the support case, however, the GBQ analytical representation retained:

{
  "value": "RDM Test",
  "lookupCode": "001",
  "lookupRawValue": "001"
}

This should not be interpreted as an RDM transcode failure when value and lookupCode contain the expected resolved display value and canonical code, respectively. The support investigation validated the three-field GBQ result after enabling the additional feature.

Troubleshooting

If value still contains the code after the change, verify the following in order:

1. Confirm the Entity API itself returns the expected RDM canonical value. If it does not, troubleshoot RDM before troubleshooting GBQ.
2. Check for lookupError, particularly error 1003.
3. Confirm the affected L3 attribute points to the correct rdm/lookupTypes/... lookup.
4. Confirm the intended attribute-level resolveLookupCode value.
5. Confirm the Data Pipeline configuration change has actually been applied to the correct tenant and GBQ adapter.
6. Run syncToDataPipeline or an appropriately scoped reindex after the configuration change.
7. Wait for the Data Pipeline queue to reach zero before querying GBQ.
8. Compare a single entity across the Entity API and GBQ to isolate whether the problem occurs during RDM resolution or downstream replication.

Reltio's GBQ troubleshooting guidance similarly recommends validating task completion, queue state, event counts, and entity event status when data in Reltio and GBQ do not agree.

Important note about RDM changes

RDM lookup changes are not automatically applied to values already stored in the tenant. Reltio states that after lookup values or resolveLookupCode behavior are changed, the data must be reloaded or the affected entity updated for the new lookup information to take effect.

This is separate from resynchronizing data to GBQ:

RDM/configuration change
        ↓
Update/reload lookup data in Reltio if required
        ↓
Confirm Entity API is correct
        ↓
Synchronize data to Data Pipeline
        ↓
Validate GBQ