Building a Validation Issues Report Across RDM Datasets

During data migration or bulk imports, validation issues are often spread across multiple RDM datasets. Although each dataset provides its own validation results, reviewing them individually requires switching between datasets and records, making it difficult to assess the overall quality of the imported data.

A consolidated validation report provides a single view of validation issues across the implementation, allowing data stewards and developers to quickly identify affected datasets, records, attributes, and validation messages.

Retrieving Validation Results

The report follows the same retrieval pattern for every dataset. The only dataset-specific elements are the dataset name and the business key displayed in the report.

The query performs the following steps:

1. Reads validation results from the validation table (<dataset>v).

   

    

    

2. Shows validation results for both the published (<dataset>) and draft (<dataset>e) versions of the dataset.
3. Returns only validation entries whose generatedPK still exists in either the published or draft dataset.
4. Expands validation messages stored as JSON into individual rows.
5. Translates internal validation keys into user-friendly messages.

Step 1. Join Validation Results to Existing Records

The first step joins the validation table with both published and draft records.

FROM <dataset>v t
LEFT JOIN <dataset> d
       ON d.generatedPK = t.generatedPK
LEFT JOIN <dataset>e de
       ON de.generatedPK = t.generatedPK

This allows the report to return validation issues for both published and unpublished records.

Step 2. Ignore Obsolete Validation Entries

Validation entries may remain after a record has been deleted.

To ensure the report contains only current validation issues, return only records that still exist in either dataset.

WHERE COALESCE(d.generatedPK, de.generatedPK) IS NOT NULL

Step 3. Expand JSON Validation Messages

Validation results are stored as JSON within the ac_validation_result column.

The following pattern converts the JSON structure into one row per validation issue.

jsonb_to_recordset(
    (t.ac_validation_result::jsonb)->'columnMessages'
) AS col(name varchar, messages jsonb),

jsonb_to_recordset(col.messages)
AS msg(type varchar, message jsonb)

This produces one row for every affected attribute instead of returning the JSON document.

Step 4. Translate Internal Validation Keys

Validation messages stored internally are intended for the platform and are not always meaningful for business users.

A simple CASE expression can translate common validation keys into user-friendly descriptions.

Only the validation messages applicable to the current RDM configuration are included in the table below. For completeness, the full list of available RDM validation message keys is provided in the next section.

Step 5. Building the Dataset Query

The overall query structure remains the same for every dataset. Only a small number of dataset-specific elements need to be updated.

These include:

The business key displayed in the report should uniquely identify the affected record. Depending on the dataset, this may be a single attribute or a combination of multiple attributes.

For example:

'ACCS_ROLE_CD: ' ||
COALESCE(d.accs_role_cd, de.accs_role_cd, '<NULL>')

or for a composite business key:

'EMPL_NO: ' ||
COALESCE(d.empl_no, de.empl_no) ||
', START_DT: ' ||
COALESCE(d.empl_strt_dt::text, de.empl_strt_dt::text)

Using a readable business key makes it much easier for business users to locate the affected record without referring to the internal generatedPK.

Result

The final report provides a consolidated view of validation issues across all datasets. Each row identifies the dataset, affected record, attribute, issue type, and validation message.

Complete SQL Template

SELECT
    '<Dataset Name>' AS table_name,
    t.generatedPK,

    '<KEY1>: ' ||
    COALESCE(d.<key1>, de.<key1>, '<NULL>')
    || ' | <KEY2>: ' ||
    COALESCE(d.<key2>, de.<key2>, '<NULL>')
    || ' | <KEY3>: ' ||
    COALESCE(d.<key3>, de.<key3>, '<NULL>') AS primary_key,

    col.name AS column_name,
    msg.type AS message_type,

    CASE
        WHEN msg.message->>'key' = 'raw'
            THEN msg.message #>> '{values,0,s}'

        -- Required and unique key validation
        WHEN msg.message->>'key' = 'validationRequired'
            THEN 'Value required'
        WHEN msg.message->>'key' = 'validationUniqueKeyViolated'
            THEN 'Duplicate primary key'

        -- Domain / column value validation
        WHEN msg.message->>'key' = 'validationStringDoesNotSatisfyDomain'
            THEN 'String value does not satisfy domain conditions'
        WHEN msg.message->>'key' = 'validationSizeGreaterThanAllowed'
            THEN 'Value size is greater than allowed'

        -- Relationship validation
        WHEN msg.message->>'key' = 'relationshipNoParentRowIsJoined'
            THEN 'Value not found in the lookup list'
        WHEN msg.message->>'key' = 'relationshipInconsistentChildParentValues'
            THEN 'Dataset value does not match the lookup value'
        WHEN msg.message->>'key' = 'relationshipRowWithNonExistingParent'
            THEN 'Referenced record is marked for deletion'

        -- Other validation messages
        ELSE msg.message->>'key'
    END AS message

FROM <dataset>v t
LEFT JOIN <dataset> d
       ON d.generatedPK = t.generatedPK
LEFT JOIN <dataset>e de
       ON de.generatedPK = t.generatedPK,
jsonb_to_recordset(
    (t.ac_validation_result::jsonb)->'columnMessages'
) AS col(name varchar, messages jsonb),
jsonb_to_recordset(col.messages)
AS msg(type varchar, message jsonb)

WHERE COALESCE(d.generatedPK, de.generatedPK) IS NOT NULL;

By querying the validation tables together with the published and draft datasets, validation results can be consolidated into a single report covering the entire RDM implementation.

The same query pattern can be reused across all datasets by changing only a few dataset-specific elements, making it straightforward to build a centralized validation report for data migration, bulk imports, or ongoing data quality monitoring.