How to Implement Uniqueness & Referential Integrity Checks in Aperture Data Studio

Danny Roden
Danny Roden Administrator
in Tips and tricks

Hi everyone,

Happy New Year! 🎉 I wanted to share a quick tip that came up in a recent discussion around BCBS 239 compliance and data quality checks in Aperture Data Studio. Specifically, how to handle Uniqueness and Referential Integrity validations.

Context

When working with BCBS 239 or similar regulatory frameworks, ensuring that certain fields are unique (e.g., customer_ID) and that values match an approved reference list (e.g., valid country codes) is critical. While Aperture doesn’t have a single “referential integrity” button, you can achieve this using lookups and validation rules.

How to Do It

For this example I want to check that my data has a unique customer_ID and that the country values all match to the approved list (defined in a dataset):

image.png

1. Prepare Your Reference Data

  • Make sure your approved list (e.g., valid country codes) is available as a dataset in Data Studio. This allows you to reference it in lookup functions/surface it in your workflow.
  • ℹ️Note you can also use the new Catalog & Governance module to help you: capture, surface and manage reference data directly within the platform (an increasingly hot topic in many sectors)!

2. Check for Uniqueness

  • Configure a self-lookup on the field you want to validate (e.g., customer_ID) to calculate a count of occurrences.
image.png
  • In your validation rule, simply check that count = 1. This ensures uniqueness.
image.png

3. Validate Against Reference List

  • Use lookup functions (E.g. Contains Match) to compare your field values (e.g., country) against the reference dataset.
image.png

4. Combine in Workflow

  • Add these steps into your workflow so that the validation rules produce clear pass/fail results for each record.
image.png image.png

Why This Works

By leveraging lookups and validation rules, you can enforce both uniqueness and referential integrity without needing custom scripts. It’s flexible, scalable, and aligns well with Aperture’s design.

Hope this helps anyone tackling similar challenges! If you’ve got other approaches or want to share screenshots of your workflows, feel free to add them below. 👇

Tagged:

Categories