25

I need to take production data with real customer info (names, address, phone numbers, etc) and move it into a dev environment, but I'd like to remove any semblance of real customer info.

Some of the answers to this question can help me generating NEW test data, but then how do I replace those columns in my production data, but keep the other relevant columns?

Let's say I had a table with 10000 fake names. Should I do a cross-join with a SQL update? Or do something like

UPDATE table
SET lastname = (SELECT TOP 1 name FROM samplenames ORDER By NEWID())
Community
  • 1
  • 1
BradC
  • 39,306
  • 13
  • 73
  • 89

3 Answers3

19

This is easier than it sounds if you understand the database. One thing that is necessary is to understand the places where personal info is not normalized. For instance, the customer master file will have a name and address, but the order file will also have a name and address that might be different.

My basic process:

  1. ID the data (i.e. the columns), and the tables which contain those columns.
  2. ID the "master" tables for those columns, and also the non-normailzed instances of those columns.
  3. Adjust the master files. Rather than trying to randomize them, (or make them phony), connect them to the key of the file. For customer 123, set the name to name123, the address to 123 123rd St, 123town, CA, USA, phone 1231231231. This has the added bonus of making debugging very easy!
  4. Change the non-normal instances by either updating from the master file or by doing the same kind of de-personalization

It doesn't look pretty, but it works.

Oliver Salzburg
  • 21,652
  • 20
  • 93
  • 138
tomjedrz
  • 511
  • 3
  • 9
  • This is also my prefered way to set up test data from scratch. Fake phone numbers and email addresses can also be created by the unique ID of the rows (using MySQL LPAD() of RPAD() to get certain lengths!). – Piemol May 16 '18 at 10:00
  • This works nicely! I had client names copied in some texts (notifications, etc) I just updated all of those to "description anonymized", which worked for me. Just watch out for these when doing this. – Tony Vlcek Jun 05 '19 at 14:12
14

Anonymizing data can be tricky and if not done correctly can lead you to trouble, like what happened to AOL when they released search data a while back. I would attempt to create test data from scratch at all costs before I tried to convert existing customer data. Things may lead you to be able to figure out who the data belonged to using things such as behavioral analysis and other data points that you might not consider sensitive. I would rather be safe than sorry.

John Lemp
  • 5,029
  • 3
  • 28
  • 36
8

There are a couple of tools out there to remove sensitive data from databases that I've found. Note that I haven't tried any of them myself:

There's also a collection of sanitisation DB scripts here which might be helpful: https://gist.github.com/Tyriar/d3635c6b6e32ac406623

Sam
  • 5,997
  • 5
  • 46
  • 66
  • The link for Data::Anonymization above no longer worked for me. I found it here: https://github.com/sunitparekh/data-anonymization – Guerry Aug 02 '22 at 20:45