How I Anonymized Relational SQL Dumps Without Breaking Foreign-Key Relationships

Anonymizing a relational database dump while preserving foreign key relationships proves to be a challenging task. While it is relatively simple to replace sensitive information like names, emails, and phone numbers, maintaining the integrity of relationships between data entities is crucial. For instance, consider a database with two tables: 'users' (id, name) and 'orders' (id, user_id).

If we independently anonymize the user IDs, the order table would receive a different mapping, which destroys the relationship between the two tables. This results in a less useful dataset for development and testing purposes.

To address this problem, I developed CloakDB, an open-source SQL dump anonymization tool written in Python. CloakDB utilizes deterministic pseudonymization, a method that maintains consistent mappings during the anonymization process. Rather than generating entirely new values for repeated data points, CloakDB assigns the same pseudonym to every occurrence of a specific value. This allows related records to remain connected after anonymization, ensuring the relational structure of the database is intact.

Streaming SQL processing is another key aspect of CloakDB. Instead of loading the entire SQL dump into memory, which becomes impractical for large datasets, the tool processes the dump as a streaming pipeline. The simplified flow includes the following stages: SQL dump → Scanner → PII detection → Deterministic mapping → Masking → Anonymized SQL dump. This approach allows the tool to handle large databases without requiring the entire input dataset to exist in memory simultaneously.

CloakDB's PII detection capabilities include scanning for potential sensitive data such as email addresses, names, phone numbers, IP addresses, Turkish identification numbers, and credit card numbers. However, it is important to note that automatic detection is not foolproof, and CloakDB employs a scan and preview workflow to allow users to inspect detected fields and preview transformations before applying the changes.

The deterministic pseudonymization layer is a critical component of CloakDB. For example, if the original dataset contains multiple instances of user_id = 123 in different tables, the anonymization process should consistently map this value to a single pseudonym, such as 847. This ensures that all related records reference the same pseudonym, preserving the relational structure of the database.

The current implementation of CloakDB includes the following features: PII detection, deterministic pseudonymization, foreign-key relationship preservation, streaming SQL processing, a scan/preview/apply workflow, configurable masking strategies, and support for streaming SQL processing. The project is open source and available on GitHub: https://github.com/latryee/CloakDB

There are still several areas where I aim to improve CloakDB. Some of these include enhancing SQL dialect support, developing more sophisticated PII detection techniques, conducting larger-scale benchmarks, exploring additional masking strategies, expanding support for various database fixtures, and improving handling of unconventional SQL dump formats. I am also interested in gathering feedback from individuals who have experience anonymizing production database snapshots for development or testing purposes.

In conclusion, the primary challenge in database anonymization lies not only in replacing sensitive strings but also in ensuring the resulting data remains structurally useful for development and testing purposes. CloakDB aims to tackle this challenge by providing an open-source tool that maintains consistent mappings during the anonymization process and preserves foreign key relationships, ultimately producing safe and useful anonymized datasets for various use cases.