Using Data as a Tool

Posted in: Technical Track

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.

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Julien Lamarche is a DBA with The Pythian Group since 2006. He graduated from the University of Ottawa with Magna Cum Laude (B.A.Sc. Software Engineering, 2004). In the past, he has added multilingual capabilities to the weblets sourceforge project as well as collaborated with on their CMS back-end. Outside of Pythian life, Julien works with the Green Party of Canada Ottawa-Vanier Greens. In 2005, Julien went accross Canada with 12 other cyclists for the environmental education NGO The Otesha Project. Julien's Pythian blog posts can be viewed at

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *