Doing Data Fixes the Right Way

I was having a rather spirited debate with an old friend the other day about data fixes.  (Since he was buying lunch I thought it only polite that I listen while he talk.  That and I didn't want him to take away the food if I disagreed with him.)  For the most part we agreed on many of the items:

• when possible data fixes should be tested in UAT prior to Production


• they should be scheduled much like any other deployment


• business areas should approve the data fix prior to the data being committed

We disagreed, however, about what constitutes a data fix.  While we both agreed on the general principle of "a data fix is used to correct data that is in an invalid state in a database", I preferred to add one additional word "unexpected".  in my definition the data needs to be in "...an invalid and unexpected state ..."  In my friends world he is used to doing data fixes on a daily basis to correct the data issues that crop up in the inventory system that he is maintaining.  The data fixes are pretty much the same SQL run over and over again, with just the input parameters changing.  When I asked him why he just didn't fix the program he complained that management didn't want him to spend time fixing the code because "... changes were coming ..."

If you expect data fixes as a regular part of your daily operations, then you have a problem with your application.  Fix it!!!!  I know that in some circumstances it isn't easy to fix.  There may be forces outside of the control of your application that cause the data to be invalid.  However, in those cases there is an easier fix than the submission of a data fix every time the business area needs some data changed.  Create a page, a really simple page, that accepts the parameters from the business user and executes a stored procedure to make the changes. 

What does this do?  Well, it eliminates the middle (wo)man, the DBA who needs to create and or run the SQL.  It gives the business area more control over what they want to do when they want to do it.  It can provide a full audit trail of who made the change and when.  And, most importantly, it can be implemented very quickly and will pay for itself very quickly.  The effort around a single data fix, no matter how small, consumes a considerable amount of time.  By letting the user do their own "data fix" the developer/DBA can work on fixing the real problem, not just the symptoms. 

I didn't win the argument, however, as my friend is a consultant and gets paid to do the data fixes, so he actually liked the way it was set up.  I did manage to finish lunch before disagreeing with him, however, so from that perspective I won.