Load Data Warehouse" job failed due to a unique constraint violation during the load of a dynamic lookup table.
search cancel

Load Data Warehouse" job failed due to a unique constraint violation during the load of a dynamic lookup table.

book

Article ID: 435295

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity FedRAMP Clarity PPM On Premise

Issue/Introduction

Upon configuration of dynamic lookup, load data warehouse is failing with below errors 

duplicate key value violates unique constraint "dwh_lkp_XX_XX_XX_dyn_u1"

Environment

Clarity 16.4.1

Cause

The failure occurred during a "Full Insert" operation into the DWH_LKP_XX_XXX_XXX_DYN table. The process attempted to insert data from the source view DWH_LKP_XX_XXX_XXX_DYN_V, but this view returned duplicate records for a set of columns governed by a unique index (typically the lookup code and language combination)

Resolution

In Clarity PPM, tables ending in _DYN are used for dynamic lookups. This error indicates that the underlying NSQL query for the dynamic lookup associated with DWH_LKP_XX_XXX_XXX_DYN is returning more than one row for the same lookup code per language. Because the Data Warehouse requires these codes to be unique, the job fails when it encounters duplicates.

Recommended Actions

  • Identify the Lookup: Locate the dynamic lookup in Clarity PPM that corresponds to the DWH_LKP_XX_XXX_XXX__DYN table.
  • Review NSQL: Examine the NSQL query defined for this dynamic lookup. Ensure that it is written to guarantee a single unique row for every code and language_code combination.
  • Check Source Data: Verify if there is any duplicate data in the application tables referenced by the lookup's NSQL.
  • Resolve and Re-run: Once the query or data duplication is corrected, re-run the Load Data Warehouse job. A "Full Load" may be necessary to ensure the DWH table is properly truncated and rebuilt.

 

Additional Information

Best Practice for writing NSQL Lookup for DWH: To prevent unique constraint violations (like the one in your log) and ensure your dynamic lookups function correctly in the Data Warehouse (DWH), you should follow these specific NSQL coding standards.

  • Mandatory Columns for DWH: For a dynamic lookup to be compatible with the DWH, the NSQL must include these specific aliases:
    • LAST_UPDATED_DATE: A date field (e.g., @SELECT:tp.last_updated_date:LAST_UPDATED_DATE@). This is required so the DWH knows when to incrementally update records.
    • LANGUAGE_CODE: Required if your lookup supports multiple languages. It must return the standard 2-character language code (e.g., 'en', 'fr').
    • LANGUAGE_ID: Highly recommended to ensure the DWH can map translations to the correct language keys
  • Ensuring Uniqueness: The error in your log occurred because the lookup returned more than one row for the same Hidden ID + Language combination.
    • The Golden Rule: For every unique "Hidden Value" (the first column in your NSQL), there must be exactly one row per language.
    • Avoid Cross Joins: Ensure all joined tables in your NSQL have proper join conditions. If a join is missing, the query will return a Cartesian product, causing duplicate IDs.
    • Filter by Language: Always include the @WHERE:PARAM:LANGUAGE@ parameter in your WHERE clause. This ensures that when Clarity or the DWH calls the lookup, it only pulls the relevant translation
  • Handling Parameters: If your lookup is Parameterized (dependent on another field), the DWH extractor often ignores these parameters during its full load
    • Best Practice: Ensure the lookup provides a valid "fallback" or is written so that it doesn't return duplicate rows when parameters are null.
    • Note: If a lookup query is designed to return different values for the same ID based on a parameter, it will fail in the DWH because the DWH table structure is flat and expects a 1:1 mapping of ID to value per language
  • Recommended NSQL Structure: Below is a template for a DWH-compatible dynamic lookup:
    SELECT     @SELECT:DIM:USER_DEF:IMPLIED:REQ:r.status_code:ID@, -- Hidden ID    
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQ:r.status_name:NAME@, -- Display Name    @SELECT:DIM_PROP:USER_DEF:IMPLIED:REQ:r.last_updated_date:LAST_UPDATED_DATE@, -- Mandatory    
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQ:lang.language_code:LANGUAGE_CODE@, -- Mandatory for multi-lang    
@SELECT:DIM_PROP:USER_DEF:IMPLIED:REQ:lang.id:LANGUAGE_ID@ -- Recommended
FROM     my_custom_status_table r JOIN     cmn_languages lang 
ON r.language_id = lang.id WHERE     r.is_active = 1    AND 
@FILTER@    
AND lang.language_code = @WHERE:PARAM:LANGUAGE@ -- Ensures the query only returns the current session's language
Powered by Wolken Software