Voice of the DBA

Tag: databases

  • When Should We Be Cleaning Data?

    I was reading Grant Fritchey’s update from the Business Analyst Conference  and noticed this quote: ” There were lots of sessions on the need to clean data, which implies that we need to do a better job at data collection in order to support this wild new world.” Grant was talking about the fact that many of the sessions seemed to imply that it’s mostly the processes around data that cause issues with data, not the people.

    However is that really what we should do? Do we want to do more and more cleansing at the point where people enter data? I’m not so sure that’s the case. The more that I deal with various applications and forms, the less I want to see too many fields and values required. Certainly there are pieces of data that are necessary for action, but I often find there are lots of additional fields that analysts want, but are more of a burden than a necessity.

    Most of us as data professionals design tables to handle the needs of an application. We put fields in tables that we expect to fill with data, based on a specification. However the real world is messy, and often the data we want to collect and store isn’t always available. The question is, do we force failures in the application or do we handle missing data?

    I don’t want to eliminate DRI, or go to an EAV models for lots of data. However I do think that we need to ensure we allow defaults and work with developers to allow data in our systems that might not be complete now, but perhaps will be in the future. We should work with analysts to help them build reports that can handle defaults or missing fields. We can work with developers to allow applications to request updates to data later and then design ETL that can efficiently fill in the updated information.

    Applications and software need to be flexible to work with problematic data. We have the ability, as data professionals, to help our clients still find meaning in data that might not be as complete as we’d prefer, or they’d prefer. However we can still find valuable information in what data they have..

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • QA for Data

    Here’s a typical DBA scenario. You’re at your desk, working on something, when you get a trouble ticket, phone call, or tap on your shoulder. There’s a problem in production and an order, or some other important piece of data, is incorrect. Can you fix it?

    Many of us are happy to oblige. Often we know what needs to be changed and can write the INSERT/UPDATE/DELETE statement to correct the issue. Even if we have to dig in further, we can usually find the data in a table and determine what should be changed.

    However, what do we do then?

    If you are asked the make data changes to production, do you QA the script? Do you save the scripts in a VCS or a log? Does anyone else examine the data or code? Do you scan the script yourself, have SQL Server parse it, or bother to look for triggers that might fire? Do you bother to put the script in a transaction with error handling?

    I have to say that in most of my career, I didn’t bother with any of that. I’d examine a script, perhaps have another DBA or developer look at it, but most of the time I’d run it in production if I thought it was ready. I did often wrap the code in a transaction, with a query to check the results, and an immediate rollback to prevent blocking. However if I thought the results looked good, I’d rerun the query without the transaction. Most of the time that worked well. A few triggers and fat fingers caused issues that I had to fix again, sometimes after quite a bit of work to correct my mistakes.

    It’s hard to prevent data change errors, especially if it’s something we do often, or we work in a small environment with limited staff. Ultimately, we might not even care because if we update the data incorrectly, we can run another update to fix the new issue. That’s not necessarily what most managers would want to hear, but it’s reality.

    The one thing I have insisted upon in my environments was logging any queries run. A simple cut and paste with a timestamp. That way if things were done incorrectly, at least I know what was done.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.6MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Deploy the Database First

    One of the patterns I’ve seen in some environments is people are trying to deploy changes rapidly to their database backed applications. That’s not news, but what is interesting is some of them are staging the deployment of the database changes first. Not as in I deploy database changes at 8:00pm and then application changes at 8:30pm. These people try to deploy the database changes on Monday, and their application changes will follow on Tuesday, Wednesday, or even a month later.

    It’s an interesting concept, though I think it requires a lot of forethought in your designs, as well as very tight coding from your front ends that won’t be disturbed by extra columns in a result set.  That’s not easy to do, but it’s certainly possible, and it can even be useful if you deploy feature flags extensively in your application.

    As we become more dependent on databases for our applications, and our customers expect systems to be running constantly, I think it behooves us to find ways to make alter and enhance our applications without downtime. While there are patterns to keep applications running when the database goes down, I expect that the reality is that we need to find ways keep the database up as we alter it, which for me means making non-breaking changes.

    I think it’s possible in many cases to upgrade a database over time by carefully planning your schema changes and accounting for those changes in your front end architecture. However it’s not easy, as I mentioned, and you do need to commit to very stable and careful programming practices for your developers.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 1.9MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • What’s Coming True?

    It’s the start of the new year, and the first day of work for many of us. As I start to work this year, I see that much of my work is based on that planning we did at the end of last year. We made predictions for our business, set goals, and today begin to execute on things. However, we are assuming our predictions are somewhat accurate in order to achieve success. What if our predictions aren’t correct?

    There’s no shortage of prognosticators out there, and I found a number of predictions about IT, the Cloud, and Microsoft. I have no idea if any of these will actually come true, but I wanted to ask you this week:

    Which of these predictions will come true in 2015?

    • Increased automation and less staff
    • more BYOD acceptance and support
    • More telecommuting
    • containerization of software in the cloud (or the data center)
    • More hybrid applications using the cloud
    • IoT growth – more sensors, more data for you
    • Windows 10 will be a hit
    • SQL Server 2015 will come out
    • Windows Phone will become competitive
    • You’ll get hacked at your company.
    • You will encrypt your databases?

    The IT trends listed are fairly general, and all of them are really underway now, so I’m not sure there’s much of a prediction there. For you individually, will you see more automation and less people? More BI and cloud usage? Any of the Microsoft predictions likely to come true? Do any these apply to your environment?

    This isn’t an exhaustive list, and certainly there’s lots of people expecting 3D printing and more mobile technologies to emerge. If you think any of these will, or won’t, definitely come true, take a vote and we’ll rerun this thread at the end of the year. If you have other predictions, let us know as well.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.1MB) podcast or subscribe to the feed at iTunes and LibSyn.