Working on our OneTrust implementation, I noticed a potential gap between the consent logs it generates and our actual user state. The logs say a user consented, but what if they were already deactivated in our backend system? This could lead to compliance drift.
I built a Python script to cross-reference the OneTrust consent dump (pulled via API) against our internal user directory (we use a PostgreSQL table). The goal is to flag "orphaned" consent records for users who no longer exist in our system. It's a simple reconciliation check, but it's saved us from a few headaches during audit prep.
Here's the core part of the script that does the join and highlights discrepancies. You'll need to adapt the data sources, but the logic is straightforward.
```python
import pandas as pd
import psycopg2
# Load OneTrust consent data (CSV export from API/SFTP)
ot_df = pd.read_csv('onetrust_consent_logs.csv')
ot_df['user_email'] = ot_df['user_email'].str.lower().str.strip()
# Fetch active users from internal DB
conn = psycopg2.connect(**your_db_params)
query = "SELECT lower(email) as email, user_id, status FROM users WHERE status = 'ACTIVE';"
internal_users_df = pd.read_sql_query(query, conn)
conn.close()
# Perform an outer join to find mismatches
merged_df = pd.merge(
ot_df,
internal_users_df,
how='outer',
left_on='user_email',
right_on='email',
indicator=True
)
# Flag the anomalies
anomalies = merged_df[merged_df['_merge'] != 'both']
orphaned_consent = merged_df[merged_df['_merge'] == 'left_only'] # Consent for non-existent users
missing_consent = merged_df[merged_df['_merge'] == 'right_only'] # Active users with no consent log
# Output results
print(f"Orphaned consent records to review: {len(orphaned_consent)}")
print(f"Active users potentially missing consent: {len(missing_consent)}")
# Save for investigation
orphaned_consent.to_csv('orphaned_consent_records.csv', index=False)
```
Key things it checks:
* **Orphaned Consents:** Consent records where the user email doesn't match any `ACTIVE` user in our DB. These might need to be purged.
* **Missing Logs:** Active internal users with no corresponding consent log entry in the selected timeframe—might indicate a capture issue.
This is a basic cost-control mindset applied to compliance data: waste (orphaned records) creates risk and storage bloat. Has anyone else built similar validation checks? I'm curious if you've found other data integrity gaps between OneTrust and backend systems.