I have a table used to record measurements sampled at regular intervals on different sensors. Each row records the time, the identifier of the quantity being measured, and the value itself.

Now and again measurement errors occur and garbage is being recorded in the value field. How should I deal with these errors:

  1. Delete the offending rows entirely, losing the information that there had been an error;
  2. Keep the row as is, and asking of client code to deal with the errors;
  3. Replace the values with NULL, losing the original erroneous value?

Or is there another option that I have not considered?


An excellent way of dealing with this kind of situation is to move the offending rows to a table named something like xxx_quality_issues, which contains the same column definitions as the main table. This prevents bad data from polluting statistical analysis of the good data saved in the main table, while retaining the rows that are potentially fixable.

I'd build a system to monitor rows in the xxx_quality_issues table. The table might have several extra columns detailing the problem row, including Status, IdentifiedIssue, ResolutionType, etc. If rows in the problem table can be fixed, they can be moved back into the main table.

| improve this answer | |

I would keep the data, if at any point in the future you want to find out what was wrong with it you still have it.

It can provide you with some information later on about how often measurements fail, where they originate from, what the offset is and things like that.

Deleting the data is unrecoverable, handling them in some way can always be changed later on. For example you could handle them in your client application for now and if you have some issue with that store them physically in a table_cleaned table.

If you deleted them or replaced them with NULL the information is gone for good.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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