SQLTeam.com | Weblogs | Forums

Pii question (removing personal data)


I need to scrub our Development and QA Databases from personal information.

I finished something but Developers say I overdid it.

Question, if I scrub someones: Name, Date of Birth and SIN no, is it OK to leave the address as it is from the Production Database? We have issues with fake or blank addresses, such as errors will occur if an address does not exist, or is in a city that doesn't belong to that persons geographic region or Branch location etc...


We obfuscate such data with a one-way algorithm, so it can't be reversed. The one-way algorithm is repeatable, so the data will be "same next time". But that would break your address validation ... I reckon if you leave the addresses and they connect to, say, financial information then someone could see what a particular house had spent and, ergo, the person(s) who live there ...

Could you randomly (but repeatably) swap addresses, so that they were connected to the "wrong" financial information? Maybe that would break something else (total revenue per geographical area or somesuch)


Thanks. Why do you need it repeatable?

We just have a list of 15 fake addresses. We choose one of the 15 by dividing the ID column by 15 and using the remainder. Similar for Name and Note fields.


In our case so that when we refresh the TEST DB, from Production, we have like-for-like with the previous data - in case we have a query that we want to re-run and then compare the results with the previous run - e.g. "geographic spread" or somesuch.