Are there best practices for checking for errors in business logic for DB refreshes?

Question

I'm working for a company with very large databases which all refresh overnight, ready for users to interrogate in the morning. These over night loads essentially truncate all the tables, and reload again using pretty complicated business logic.

This is all fine and good, but occasionally, rather than going through the proper routes, a quick fix is needed in the business logic, and changes will be made on the "Live" server to the business logic without too much testing.

This has, as I'm sure you can imagine, resulted in subtle but significant mistakes being made. I basically want to now independently write logic (in T-SQL probably) which will try to spot any abnormal changes, so at least I'm ahead of the game, and can report them sooner.

My question is, fairly obviously, does anyone have any advice on this? Is there any theory or general good practice which I could incorporate to hopefully limit the impact of future "quick and dirty" changes to business logic?

Just so you know, we do use source control, and use Live, UAT, and Dev server environments, its just that sometimes, changes need to made immediately, rather than going through the stricter channels.

Answer 1

The use of transactions should pay off here. If any issues arise, don't commit the changes being made, simply roll it back to either the beginning or at a another point in time (break it up into multiple transactions with tables for each step). If you use additional tables for each step in your process, you can go through the data changes made at each point and pinpoint where the error occurred.

Based on the feedback you had given me for my original answer. We know a few things about you environment:

(1) It's a corporate environment where there is little communication between teams. This is evidenced by the fact it appears to be difficult for you to obtain log files from the DBAs.

(2) It's a behemoth of a system.

Before you can fix a problem, you need to understand it. In your case, getting the log files and keeping a diary/journal of the errors and types of errors you are getting will give you a better idea of what's happening. Are you getting data entry errors? Is there data missing? Is referential integrity being violated anywhere? If you can theme the errors, you will have a better understanding of what is going wrong. If getting the logs is as troublesome as you say, speak to your manager about the problem and ask him if he can obtain the logs for you. In a corporate environment, people tend to be very territorial about their work and don't answer to everybody as they are probably under a lot of stress with minimum bandwidth to deal with other issues. If the request comes from someone higher up, you may get a more positive response.

Once you have got the log files and managed to theme the problems, you can have a second discussion with your manager. Give him feedback on what you have found and suggest two or three solutions to resolving this. Outline the short term and long term benefits of each some and a rough estimate of how long you would think it would take to implement. The key here is communication. Your replies to my initial answer indicates there is a complete lack of it, so you need to make a strong effort to promote it. Having your manager supporting you will give you an additional medium to communicate with the DBAs as he can speak to their manager if they become unresponsive.

If all this fails and you are unhappy with the situation, my only other advice is that you look for another place to work. This issues happens in many companies and to different degrees, so it's down to whatever you can tolerate. After it happened to me a few times, I came to the conclusion that it simply wasn't my company and the issues that came up were down to management. If it made life difficult for me, I'd simply move on to other things. Life is too short.

Obviously, you can make use of the standard log files that come with your DBMS on-top of this, but if your company operates under a "hurry up mantra" with regards to getting stuff out the door and it's causing a lot of delays, you should make an effort to discuss these problems with management. Show them what their ignorance of testing is costing them. I've been in a similar scenario in a corporate environment and it was always about short term benefits and they never thought about looking at the existing business logic and improving it.

Answer 2

If you have many such changes, then your organization has to face the fact that the installed software does not match the business requirements. Accordingly, the organization should launch a project to determine the gap and fix the existing software and eventually publish a new release or version.

If your organization desire quality, there is no substitute for following proper development, testing and promotion procedures which take time and effort.

Answer 3

I'm presuming that the overnight run is calling stored procedures. If you can't write a text log & get access to it fight for it. DBAs can sometimes be a bit possessive of logs because they don't like developers reading them and asking difficult questions ;).

Assuming you lose the fight, create a simple logging table and a logging library (if your RDBMS doesn't have one). Then during the run write entries to the table. You want these entries outside of the main transaction so you get "encountered error type XYZ in record ABC rolling back" type entries in the log instead of having the error log rolled back as well.

Then, first thing you do in the morning is scan the days log entries & look for issues.

Over time you may encounter data issues that don't get logged. If you get an unlogged issue, write code to identify and log the problem before you fix the code (a bit like TDD). That way, like unit tests the assumptions about the data will be documented and tested and exceptions logged so you can fix them.