We have large data sets in my current project. Every year tons more data is loaded into the system. So we only keep the majority of data for 4 years. After that, we get rid of it. There are some specific items in the data that the customer wants to keep around forever. So we identify those important records on a yearly bases, and "archive" them.
Archive in this sense means to copy the important records to another table. That other table mimics the structure of the original table. We do run into extra work when structural changes are made to the original table. We need to ensure that those changes are made to the archival table as well. This undertaking is done once a year when the archive process is run.
Right now the archive time is coming due soon. So I am working on this task. I need to identify all the changes made to the original table. Actually there are a total of 37 original tables. Who knows what kind of changes were made in the last year. How can I be sure I account for all of them? Well previously this was a manual process, reviewing the changes made during the year. Now I am turning to the data dictionary.
It is easy to find newly added columns. I just search for columns in the original table that are missing from the archive table. Little more involved when data type changed in the original table. I must find where the type change to an entirely different type, or where something like the scale or precision changed. The data dictionary knows all. Just takes a SQL query to unearth the answers.
