Skip to main content

Varicent ELT Help Center

Hierarchy validator

Abstract

The Hierarchy validator tool identifies any errors that could break the hierarchy.

The Hierarchy validator tool identifies any errors that could break the hierarchy. In this example, we want to validate the records in a customer accounts hierarchy file and clean up any invalid records.

Parameters

The Hierarchy validator requires the following parameters:

  1. Key: This is the unique column that rolls up to the Parent Key.

  2. Parent Key: This column identifies the parent of each key.

  3. Key Name (optional): Select the name or description of the key column if the column is available.

  4. Parent Key Name (optional): Select the name or description of the parent key if the column is available.

Output

Important

Build your pipe first, before cleaning any invalid rows, to ensure that the entire data set is used to validate your hierarchy.

The output contains the columns selected in the Hierarchy validator tool, plus two additional columns indicating if a record is invalid and the type(s) of hierarchy errors identified. Records tagged with FALSE value in the "Valid" column and an error message in the "Hierarchy Error" will require your review and updates. You can export the invalid records for review Export for Review (Export) or clean them directly in Symon. A detailed explanation for each error and the suggested change can be found in the following error descriptions:

  • Circular reference error: This error indicates that a higher-level value is trying to roll up to a lower-level value and vice versa. This error would appear in all records.

    Suggested change: In this scenario, you want to review all related records to ensure that you resolve the circular reference.

  • Invalid parent key error: This error indicates a parent key is missing in the key column.

    Suggested change: The parent key either needs to be added to the "Key" column or this record should roll up to a different parent key that exists in the "Key" column.

  • Duplicate key error: This error indicates this key could exist in multiple spots. Each key must roll up to one parent only and have a unique name. This error would appear beside all rows with the same key.

    Suggested change: Remove duplicate records and ensure that each key has a consistent name and rolls up to only one parent.

Clean-up and validate the updated hierarchy

In the turquoise branch, we remove duplicate keys using the Unique tool and corrected the parent key of two invalid records Replace Replace .

After we update all invalid rows, then the Union tool the cleans up records with the valid records Filter For Valid Records (Filter), and then we want to re-run the Hierarchy Validator to ensure all of the issues are resolved. All records are valid. The data is ready for export for downstream processing. Export For Downstream Processing (Export)