4

I have pseudo-ownership of a fairly old db (original data from 30 years ago; current design is >15 years old). In my opinion, the schema is pretty broken, and one of the implications of this is that there are a lot of inconsistencies/issues with the data. I'm planning to write a new schema and port the data across, which is a fairly simple task as new data comes in rarely.

Would you attempt to fix the inconsistencies in the old database first, or iron them out as part of the migration process? I'm tempted to go with the latter - since I'll need proper validation anyway, and some errors will pop out naturally with the different schema design - but fixing the data first would break the task into smaller chunks and allow people familiar with the old db to validate the fixes.

Thoughts?

james.haggerty
  • 173
  • 2
  • If new data comes in rarely, then do you *really* need a new schema? Don't be quick to replace something just because it's old - if its functional and meets the "rare" need, then you might be best leaving it alone – Unknown Coder Oct 31 '14 at 15:52
  • A reasonable concern, and one I've thought about a lot. Factors that bear on the decision: it's currently in Access, so it's moving out of its current form regardless (needs better multi-user/network support), and it would be nice to have GIS lookups on position data (i.e. slight alteration). More importantly, though, I don't want to spend a lot of effort cleaning the mess up without doing something to stop the mess happening again. – james.haggerty Nov 06 '14 at 05:43

1 Answers1

5

Your latter strategy is likely the better choice. It will be difficult to find all the problems in the data while it is still resting in its current format.

I would treat this an ETL process of sorts, combined with an iterative approach. Something like this:

  1. Build a beta version of the schema.
  2. Write a program to read the old data, scrub/transform it, and finally load it to the new schema. Log any new and unexpected problems with the data that your program's scrub/transform logic and/or the schema can't handle.
  3. Review problems detected in step #2 and change the schema and/or ETL program.
  4. Delete all the saved data
  5. Run the revised program against the revised schema.
  6. Lather rinse, and repeat until you and the data experts are comfortable with the schema design and transformed data. Move data to production.

Edit

If the data experts are concerned about what will happen during the transform process, make extra effort to keep an open dialog going with them concerning what you are seeing in the data and how they want to you to handle it.

This process will likely be a great way to clarify what the business rules and logic are for the data. As the load program attempts to confirm the data to these rules, it may discover problems with the data that no one knew even existed. It may also discover previously unknown scenarios that require new rules. The end result is both better data and a better understanding of what it can tell you.

poke
  • 570
  • 4
  • 12
  • Thanks, yeah, that sounds like what was in my head (in an admittedly less articulate form!). Although I'm still not entirely convinced - I mean, I _know_ I won't find all the problems the way it currently is, but I worry that too much magic will happen in the ETL process for outsiders to easily understand the changes. – james.haggerty Oct 31 '14 at 03:05
  • @james.haggerty: At least you will understand the magic. I'm afraid that the "magic" of fixing it first will be understood by no one, especially if there is something in there that silently gets nuked by an scrubbing query against the existing data format. – poke Oct 31 '14 at 03:12
  • @james.haggerty: Added additional information based on your concern. – poke Oct 31 '14 at 03:25
  • @james.haggerty: the important part is in step 2, "Log any new and unexpected problems...": make sure your ETL produces logs showing all *real* problems in a business-friendly way. Don't produce tons of logs with success messages in a technical form. – Doc Brown Oct 31 '14 at 12:28