Data Masking and Anonymization

Test environments need realistic data without exposing real customer information. Data masking transforms production data into safe test data while preserving format, relationships, and statistical properties. Compliance regulations (GDPR, HIPAA, PCI DSS) make this not just a best practice but a legal requirement.

Why Data Masking Matters

Masking Techniques

Masking Requirements

1. Format Preservation

Masked data must look like the original data type. Emails must look like emails. Phone numbers must be valid phone number format.

def test_masked_emails_have_valid_format(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("SELECT email FROM users LIMIT 100")
    for row in cursor.fetchall():
        email = row[0]
        assert "@" in email, f"Invalid email format: {email}"
        assert "." in email.split("@")[1], f"Invalid domain: {email}"

2. Relationship Preservation

The same customer should have the same masked name across all tables. If "john@company.com" becomes "user_8a3f@masked.test" in the users table, it should be the same in the orders table and the audit log.

def test_masked_relationships_consistent(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("""
        SELECT u.email AS user_email, o.customer_email AS order_email
        FROM users u
        JOIN orders o ON o.user_id = u.id
        LIMIT 50
    """)
    for row in cursor.fetchall():
        assert row[0] == row[1], \
            f"Email mismatch: users.email={row[0]}, orders.customer_email={row[1]}"

3. Referential Integrity

Foreign keys must still resolve. Masked data should not break joins.

def test_masked_data_referential_integrity(masked_db):
    cursor = masked_db.cursor()

    # No orphaned orders (all orders reference existing users)
    cursor.execute("""
        SELECT COUNT(*)
        FROM orders o
        LEFT JOIN users u ON u.id = o.user_id
        WHERE u.id IS NULL
    """)
    orphans = cursor.fetchone()[0]
    assert orphans == 0, f"Found {orphans} orders referencing non-existent users"

def test_masked_foreign_keys_valid(masked_db):
    cursor = masked_db.cursor()
    cursor.execute("""
        SELECT COUNT(DISTINCT o.user_id) AS referenced,
               (SELECT COUNT(*) FROM users) AS total_users
        FROM orders o
    """)
    row = cursor.fetchone()
    # All referenced user IDs should exist in the users table
    assert row[0] <= row[1]

4. Irreversibility

Masking must be one-way. It should not be possible to reverse the masking to recover original data.

def test_masking_is_irreversible(original_db, masked_db):
    """No original values should appear in masked data."""
    # Get a sample of original emails
    orig_cursor = original_db.cursor()
    orig_cursor.execute("SELECT email FROM users LIMIT 100")
    original_emails = {row[0] for row in orig_cursor.fetchall()}

    # Check none of them appear in masked data
    masked_cursor = masked_db.cursor()
    masked_cursor.execute("SELECT email FROM users")
    masked_emails = {row[0] for row in masked_cursor.fetchall()}

    overlap = original_emails & masked_emails
    assert len(overlap) == 0, f"Original emails found in masked data: {overlap}"

Statistical Preservation

For analytics and reporting tests, masked data should preserve statistical properties:

def test_masked_data_preserves_statistics(original_db, masked_db):
    """Distribution of key metrics should be similar after masking."""
    # Compare order count distribution
    for db, label in [(original_db, "original"), (masked_db, "masked")]:
        cursor = db.cursor()
        cursor.execute("SELECT COUNT(*) FROM orders")
        count = cursor.fetchone()[0]
        cursor.execute("SELECT AVG(total) FROM orders")
        avg_total = cursor.fetchone()[0]
        print(f"{label}: {count} orders, avg total ${avg_total:.2f}")

    # Exact numbers will differ, but order of magnitude should be the same

Compliance Requirements

GDPR-Specific Tests

def test_gdpr_deletion_removes_all_references(api, db):
    """GDPR right to erasure: deleting a user removes ALL their data."""
    # Create a user with orders and activity
    user = create_user_with_full_history(api)

    # Request deletion (GDPR right to erasure)
    api.delete(f"/users/{user['id']}/gdpr-delete")

    # Verify complete removal
    cursor = db.cursor()

    cursor.execute("SELECT * FROM users WHERE id = %s", (user["id"],))
    assert cursor.fetchone() is None, "User record still exists"

    cursor.execute("SELECT * FROM orders WHERE user_id = %s", (user["id"],))
    assert cursor.fetchone() is None, "User orders still exist"

    cursor.execute("SELECT * FROM audit_log WHERE user_id = %s", (user["id"],))
    assert cursor.fetchone() is None, "User audit trail still exists"

    cursor.execute("SELECT * FROM session_logs WHERE user_id = %s", (user["id"],))
    assert cursor.fetchone() is None, "User session logs still exist"

Masking Tools

Simple Masking with Faker

from faker import Faker

fake = Faker()

def mask_users_table(source_conn, target_conn):
    source = source_conn.cursor()
    target = target_conn.cursor()

    source.execute("SELECT id, name, email, phone FROM users")
    for row in source.fetchall():
        target.execute(
            "INSERT INTO users (id, name, email, phone) VALUES (%s, %s, %s, %s)",
            (row[0], fake.name(), fake.email(), fake.phone_number())
        )
    target_conn.commit()

Practical Exercise

1. Write a masking script that transforms emails and phone numbers while preserving format
2. Write tests that verify masked data: format preservation, relationship consistency, referential integrity
3. Write a GDPR deletion test: create a user with activity across 4 tables, delete them, verify all traces are removed
4. Test that no original values appear in the masked dataset
5. Compare statistics (count, average, distribution) between original and masked datasets

Key Takeaways

• Data masking is a legal requirement (GDPR, HIPAA, PCI DSS), not just a best practice
• Masked data must preserve format, relationships, and referential integrity
• Masking must be irreversible — no way to recover original data
• Test masking quality: format, consistency, referential integrity, irreversibility
• GDPR right to erasure requires testing that deletion removes ALL references across ALL tables

Interview Talking Point: "I use SQL as a verification layer in my test automation — after an API call creates a record, I query the database to confirm data was persisted with the right defaults and constraints. I am comfortable with JOINs, GROUP BY, subqueries, and transaction-based test data management. I also test database migrations separately: apply, verify data integrity, roll back, and verify again. For NoSQL stores like Redis and MongoDB, I test TTL behavior, document structure, and cache invalidation."