I was assigned a tricky problem to work on. Some of the data was missing in our production environment. Everything seemed to work fine in development and test. Trouble is, I did not have access to the production environment. I got someone to run some queries on my behalf.
The source data was correct. The target tables had records inserted. But they were missing key fields. This was quite the mystery. I had to back up and look at the big picture. Normally this is not needed. But in this case, it was crucial.
We have some main data loaded into some staging tables. Then some processes run after the initial load that collect all the data and stuff it into a couple main tables. Then those main table are copied to the reporting machine. It is here that my jobs run to mine the main tables to update the reporting tables.
Turns out the copy to the reporting machine is delayed every so often. Wouldn’t you know it? My jobs are not actually scheduled to wait until the copying of source data is completed. Usually it happens way before my jobs run. But not always.
The fix is to add in the dependency, and fix all those records that got loaded with blanks. The hard part of this problem was figuring out the source of the problem. Wasn’t really a coding issue. More a timing issue.