Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process is taking longer, there becomes a risk where as the database is still being scrubbed, developers can connect to the test database still and look at sensitive data. Is there a way to scrub and then load without adding too many steps? It already takes about half hour to reload the test database. The objective here is the make sure the sensitive data is safe at all times and somehow increase the speed of reloading the test environment's database.

share|improve this question

As a specific answer to "speed of reloading the test environment's database": you probably do not need to rebuild the test database (copy producion then randomise) every time it needs to be reset. Instead at key times take a copy, randomise it, and store that copy - then each time you reset the test environment all you have to do is restore a new copy of that database. You could speed things up further by having more than one restored at any given time: to reset drop the current DB, rename one of the other copies to take its place (testing work can now start immediately), restore another copy to replace the one you are now actively using ready for the next reset.

More generally:

Wanting to use production data as it is a good representation of the real world but not wanting to give people access to real data are, if the data is truely sensitive, completeley mutually exclusive. If you randomise the data enough that there is absolutely no way a sufficiently determined data miner couldn't infer useful real information from it then you've randomised it to the point that it no longer represents reality any more than made up test data would anyway.

An alternative is to create your own test data in a scripted manner which has a number of beneifts:

  • You don't have to worry about disguising sensitive data taken from production environments at all. From a regulatory and legal standpoint this can be a big win (if you work with banking information or personal data for instance).
  • You can control the size of the data-set. Just want a small set of data to develope a new feature against? Then generate a few tens or hundreds of people with relevant records going back a few months. Testing the performance of a new feature over large data? Generate a many Gb database with thousands upon thousands of people with data spanning many years.
  • Controlling the size of the data-set means you can generate small test sets very quickly. Larger ones may take more time but are still possible (or for larger tests you can fall back to using a munged copy of produciton data, with extra precautions to make sure no devs can touch the DB until the munging is complete).
  • You can control the inclusion of difficult edge cases. Keeping them out completly for initial delevopment of new ideas, and including every single problem case you have ever seen in production or otherwise can think of if you are about to preform regression testing on new code changes. This can then become a useful part of your QA cycle.
  • Your devs can have the flexibility to create their own local test databases easily, as and when they want without needing to coordinate with the shared testing environmemnts until they have something ready to deliver that far up the chain.

For "people" in the above substitude what-ever units your system's size is dependent on. There may be more than one key unit of course: for a training and competence tracking system with its own test managment and training material the key size determinants are the size of the question bank and the number of people, so the request to the sample data generating scripts would be something like:

  1. Create a new DB
  2. Make up a question bank of 10,000 questions spread over 20 subjects and 200 topics
  3. Add 1,000 people in teams of about 8 with a supervisory heirarchy to match
  4. Add 6 months data for each person having:
    A realistic number of test records inclduing passses, fails and resits
    A realistic number of observitions, 1:1 meetings and other interventions for that user type
    and so on.

Of course there is work involved here, which is why the "take real data and try to disguise it" approach is a common short-cut (I do it myself often enough!):

  • You have to write the scripts in the first place
  • As new features are added to your product, or features change, the scripts need to be enhanced or otherwise altered to reflect these changes
  • Generating truely realistic test data takes some domain knowledge. Generating a random question bank is pretty easy (many instances of "This is a multiple choice question, the answer is B? A: The answer is A; B: The answer is B; C: The answer is C" is a place to start). For more complex/variable data you can use some analysis of your exsiting real-but-randomised data to guide the creation of your model.
  • You need to keep an eye on how your users are actually behaving, as changes to their use of your service need to be reflected in your test data model (and if you have widely differening clients, you may need to branch the model).

There are products out there that are designed to help create and maintain this sort of test data, though for small systems or particularly complex ones hand-rolling by a good local dev may produce better results cheaper.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.