January 20, 2011 at 9:30 pm
Comments posted to this topic are about the item Changing the Past
January 21, 2011 at 2:25 am
I can't really think of any time appropriate to alter past data apart from when an error has been picked up that did not affect the situation in a major way - currently I am zeroing some starting account balance figures generated automatically by data uploaded before it should have been (a couple of months ago). It was not seen, because the balance was not noted, due to the fact that it should not have even been there - no-one looks for something they do not expect.
In general any 'used' data needs to be corrected rather than amended.
January 21, 2011 at 2:34 am
should we consider using transactions tables in our databases so any errors can be amended with adjustments to transactions so all corrections can be followed as we do in account programs.
This would please the auditors and give a clear picture of what has happened.
January 21, 2011 at 7:30 am
IMHO the whole point of collecting data is to know the truth about something. If you are changing archives to mislead someone then, unless your motive is to directly save people's lives, it's no different than lying. But, if you're changing data because it misrepresents the truth in its archived form, then by all means. Say for instance, a CEO authorized the entry of false data into the database in order to make himself/herself look better. Years later, from his/her jail cell the CEO confesses that he added 10% to his/her market research numbers. I don't see any problem with going back and adjusting the data so that it would now accurately reflect what it was intended to reflect. You could always just delete the data, but, assuming you trust the jail cell confession, it's possible it could still be usable.
The three biggest mistakes in life...thinking that power = freedom, sex = love, and data = information.
January 21, 2011 at 8:13 am
For all of you that have perfect data entry personnel, or don't have to deal with 3rd shift temp workers :-), then changing bad data is nothing to worry about. For the rest of us, fixing incorrect data is a business necessity. As long as you follow separation of duties, audit the update with the who, when, where, why, and an auditor can see the before and after data, you would not be violating even the strictest compliance on earth. Not medical, defense, you name it.
January 21, 2011 at 8:39 am
When would I change archived data? Never.
Would I add in offsetting transactions if appropriate, auditable, and agreed upon by higher powers? Yes. Without all three? Never.
January 21, 2011 at 10:31 am
A number of years ago, my employer used Social Security Numbers as employee ID numbers. These were embedded in payroll transactions that were part of budgets. When a new employee ID numbers were issued, the old payroll records had to be changed.
January 21, 2011 at 12:49 pm
Even in the case presented in the editorial, the data was not changed - just amended. I'm not opposed to amending in a fashion where a new record is added and a query combines the records upon display. Please don't alter archive data though.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 21, 2011 at 3:45 pm
Something not mentioned here is when court ordered to do so, for instance to completely remove any information about a particular client from your records because the court feels this is necessary.
January 24, 2011 at 9:32 am
Thomas Foster-Baker (1/21/2011)
should we consider using transactions tables in our databases so any errors can be amended with adjustments to transactions so all corrections can be followed as we do in account programs.This would please the auditors and give a clear picture of what has happened.
I agree. This is a fascinating issue that I should learn more about, but it seems to me that as long as the chain of correction is audited and logged as well, then there would be no danger of being accused of covering up or lying. To follow Steve's example, if a tax error was found and corrected, then, in something analogous to a version control system, the original archived data would be preserved in a sense by showing what it was before, then what changes were needed to correct the error, and then what the post-change data was. That would also allow the system to track what "new" historical data leads up to the current state of the system. In some ways that is better because it won't leave people with questions about apparent inconsistencies in the data trail, which would likely come up if the archived data were "silently" corrected without any other logging of what happened to change it.
As far as I am concerned, the only reason changing historical data becomes suspect is when certain changes are left out to give the impression that the original data was different from the start, i.e., rewriting the past in Animal Farm fashion, otherwise known as fraud. As long as all changes to the data are tracked and associated with the people who changed it, and of course as long as the changes themselves are legal or necessary to bring the data in line with the law or with whatever the accepted business requirements are, then I don't see a problem with such presumably necessary modifications.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
January 25, 2011 at 5:31 am
It depends on who is asking what question. For example: a wrongfully terminated employee (Bob) is reinstated and his personnel record is supposed to be "cleared". If someone runs a report of employees with negative actions, Bob should not appear on report. If someone else runs a report to determine staffing levels on a date between Bob's firing and reinstatement then Bob should not be on the list. If someone wants to run a report of wrongfully terminated employees then like duh, Bob should be on that list.
Some things are done on a timely basis and some are not. Hiring is usually done in real time but separations frequently are not. Before the final check can be cut their leave has to be audited to be sure of how many hours of unused leave must be paid; some companies do not pay final commissions until 30 days later in case a big purchase is returned.
Most times and places preserving the "raw" data in the "historical ODS" but having "corrected" data in the Data Marts works pretty well.
January 6, 2012 at 10:47 am
My answer? NEVER, well, ALMOST never.
I recently had a very interesting case where changing past data was required. Without going into to many details, the college decided to change all classes from a QUARTER credit system to a SEMESTER credit system. This change was able to be done with no issues EXCEPT for the student transcript details and GPA summaries. Those would have been either very complicated, or downright impossible to correct given that they are generated by a third party software.
Bottom line? We followed suit done by other colleges and changed our historic data so that everything was in semester credits, recording exactly what records were changed along with the original and corrected values. We then put a notation on ALL of our transcripts (bold in a red box) stating that we had made this change as a college and that earlier transcripts would reflect quarter credits instead of semester.
My goal is to avoid historic changes if at all possible. If not, make sure that every change is fully documented, preferably in a table inside the database, so that the change log stays with the database, for the life of the database.
David A. Sousa
Database Administrator
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply