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

Latrye — Thu, 27 Aug 2026 14:52:42 +0000

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

Anonymizing a database dump sounds straightforward until the data is actually relational.
You can replace names, emails, phone numbers, and other sensitive values quite easily.
The difficult part is keeping the relationships between those values intact.
For example, imagine a database containing:
users
| id | name |
|---|---|
| 123 | Alice |
orders
| id | user_id |
|---|---|
| 901 | 123 |
If we anonymize the user ID independently:
users
| id | name |
|---|---|
| 847 | User_42 |
but the order gets a different mapping:
orders
| id | user_id |
|---|---|
| 901 | 391 |
we have successfully hidden the original value, but we've also destroyed the relationship.
The resulting dataset is much less useful for development and testing.
That was the problem I wanted to solve.

The approach

I built CloakDB, an open-source SQL dump anonymization tool written in Python.
The core idea is deterministic pseudonymization.
Instead of generating a completely new value every time a value appears, CloakDB maintains consistent mappings during processing.
Conceptually:


text
123 → 847

Every relevant occurrence of 123 can therefore be transformed into 847.

That allows related records to remain connected after anonymization.

Why streaming?

Another problem is database size.

A naive implementation could load the entire SQL dump into memory and then process it.

That becomes increasingly impractical as the dump grows.

CloakDB processes the dump as a streaming pipeline instead.

The simplified flow looks like this:

SQL dump
   │
   ▼
Scanner
   │
   ▼
PII detection
   │
   ▼
Deterministic mapping
   │
   ▼
Masking
   │
   ▼
Anonymized SQL dump

The goal is to avoid requiring the entire input dataset to exist in memory at once.

Detecting sensitive data

The tool can scan for potential PII such as:

* email addresses
* names
* phone numbers
* IP addresses
* Turkish identification numbers
* credit card numbers

The important word here is potential.

Automatic detection is never a guarantee that something is sensitive or that something sensitive will always be detected.

That’s why CloakDB has a scan and preview workflow before applying transformations.

scan
 ↓
inspect detected fields
 ↓
preview transformations
 ↓
apply

I wanted the user to be able to see what is going to happen before modifying the dump.

Deterministic pseudonymization

The mapping layer is probably the most important part of the project.

Suppose the original dataset contains:

user_id = 123

in multiple tables.

The anonymization process should not produce:

users.id       → 847
orders.user_id → 391
payments.user_id → 552

Instead, the same source value should resolve consistently:

123 → 847

so that:

users.id       → 847
orders.user_id → 847
payments.user_id → 847

The anonymized database can therefore retain its relational structure.

What CloakDB currently does

The current implementation includes:

* PII detection
* deterministic pseudonymization
* foreign-key relationship preservation
* streaming SQL processing
* scan / preview / apply workflow
* configurable masking strategies

The project is open source and available on GitHub:

https://github.com/latryee/CloakDB

What I still want to improve

There are still plenty of things I’d like to improve.

Some areas I’m interested in:

* better SQL dialect support
* more sophisticated PII detection
* larger-scale benchmarks
* additional masking strategies
* more database fixtures
* better handling of unusual SQL dump formats

I’d also like to get feedback from people who have dealt with anonymizing production database snapshots for development or testing.

Final thoughts

The interesting part of database anonymization isn’t simply replacing sensitive strings.

The real challenge is producing data that is both:

1. safe enough to use outside the original environment
2. structurally useful for development and testing

Breaking all the relationships makes anonymization much less useful.

That’s the problem CloakDB is trying to tackle.

GitHub: https://github.com/latryee/CloakDB

DEV Community: Latrye

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

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

The approach