Pythian Group

IT programs only teach you the programming and software architecture perspective. In DBA work, even when scripting, you have to consider the data as a tool. I’ll show you what I mean.

Recently, during a data center migration, my team and I found that one of our important client’s warehouse statistics were not being calculated. On top of that, we learned that they wanted the stats calculated differently than originally implemented, so we had to recalculate statistics for the previous month with a slightly modified script. But on top of that, some of those statistics were totals that could not be modified because they could only be calculated on the day the stats were collected (I’ll call these date-sensitive attributes in this post).

The team decided to keep the date-sensitive attributes as they were calculated before, but recalculate the attributes gathered after the day in question (date-insensitive attributes).

As someone who enjoys crafting a script or isolating a bug, my first thought was to hack the script so that it would ignore the values that must be ignored and modify only those that must be modified. But this would have meant I had to go through each statistic collected (there are 152 of them, for each day), and for each, modify the script to either keep the value already created or recalculate it. A long process, but more importantly, one very prone to error and bugs.

The more I modified the code or the data, the more I would be prone to error. Since copying is the thing computers do best, the other way to see it is: the more data I restore, the less I am prone to error. That’s when it clicked: make a backup copy of the table, modify the script to make calculations on all values according to the new requirements, and then restore the date-sensitive values.

The result: less coding, less risk of bugs. The lesson: you’re a DBA, so let the data — rather than the logic — do some work.

One bug that took me some time to squish was a lack of implicit date conversion between date and char data types. But that’s probably the subject of another post.