Changing the Past

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720952

    Comments posted to this topic are about the item Changing the Past

  • call.copse


    Points: 17240

    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.

  • Thomas Foster-Baker

    SSChasing Mays

    Points: 606

    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.

  • IMHO

    Ten Centuries

    Points: 1059

    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.

  • jeffgonnering

    SSChasing Mays

    Points: 614

    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.

  • bwillsie-842793

    Ten Centuries

    Points: 1359

    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.

  • cesullivan


    Points: 10

    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.


    SSC Guru

    Points: 281252

    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
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • krowley

    Ten Centuries

    Points: 1344

    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.

  • webrunner


    Points: 30363

    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?"

  • steven.malone

    Say Hey Kid

    Points: 688

    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.

  • DavidAlanSousa


    Points: 5

    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 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply