Voice of the DBA

Tag: databases

  • Flawed Data Integration

    I recently had to travel by airplane after a surgery. My mobility was limited and I requested wheelchair assistance, which I used from Denver to Houston to Amsterdam. Across three airports and two countries, the process was interesting, especially the data integration needed to get things to happen in the real world. As often is the case, there are all sorts of ways that systems don’t work well together.

    In this case, there are actually multiple digital systems integrated. Since airlines contract with passengers, they record their own data that a particular person needs something. However, the actual service is provided by the airport, and each airport has their own system. A classic case of data transfer being needed between multiple systems, in this case three different airport systems.

    Like the digital applications and databases many of us work with, there are disparate ways the data is handled in different systems. In Denver, the young man pushing me had to update his location constantly, so that there was tracking of how I moved through the airport from check-in to security to the train to the gate. I assume some notification gets to a person at the gate, but it was late. Without my wife, I wouldn’t necessarily have had a good way to flag down someone. I assume the delays I had at all gates were more human than digital, but I really don’t know. As a “customer” here, I have no idea when data moves from one application to another, much less when the humans in the real world get notified.

    Many of our digital transformations are taking place with the idea of bringing more transparency to various processes, either digital or analog. Our DevOps changes are to make it clearer how we build and deploy software. Many new app features are built to help employees better understand some part of their business. Others are there to inform customers and give them more reasons to stay our customers and not move on to a competitor.

    I think it’s important that our software developers and UX designers keep evolving and growing to better meet the needs of our customers. I think it’s amazing how well software has grown and changed from a one-size-fits-all to adapting and customizing for different users. We all might use and react to software a little differently, so allowing the software to meld with the way we work is incredibly helpful to reducing the friction and flaws in the human to computer integration.

    This was a good reminder to me of how important data integrations can be between systems, and how much of an impact those friction points can have on a customer. The trip back was much smoother, with people waiting at the airplane doors and showing up in time to get me onto the next plane. That was a wonderful reminder of just how data integration between applications can mesh with humans to create a incredible experience for the customer.

    Steve Jones

  • Column vs Row

    I’ve been working with databases for a long time. They’ve always been relational databases to me, unless they were a key-value, document, or NoSQL class of store. A few years ago at the Pass Summit, David Dewitt gave a keynote on changes to storage that Microsoft was implementing, talking about columnar storage.

    At first the structure felt confusing, but as he proceeded, it started to make sense. We don’t like SELECT * for many reasons, but one is that lots of unnecessary data gets moved off disk, into memory, and across a network. This is the nature of a row based store, which is what we usually have in relational databases.

    The columnar store puts all the columns together. The row values from different columns are separate, but if you are aggregating values in columns, the columnar store works very well.

    So well, that we have columnstore indexes in SQL Server, which copy your data into a column-oriented format. While this might seem wasteful, you decide what gets copied, and you get the benefits of this format, which dramatically speeds up some types of queries.

    The reason we have both stores is that we have a need for both to fulfill different query needs. I wouldn’t keep both stores for every table, but for some, it’s the best way to ensure your clients don’t spend a lot of time waiting for results.

    I see more clients using columnstore indexes, and I was still seeing some sessions, but not as many as a few years back when the technology was new. If you’ve never tried building a columnstore index, this might be something you experiment with in development systems and understand how this can change your query performance and storage needs. We have a great Stairway Series to get you started, so take some time this year and read through it and practice the examples.

    Steve Jones

    Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

  • A Database Playlist for Your Mom

    Recently someone asked me if I had a video explaining what a database was. I didn’t, but was curious. Apparently, this person wanted to learn a bit about databases as a layman, someone that isn’t technical and doesn’t want to be. They just wanted to understand the terminology.

    I was about to record one, but then decided to see what was out there. Turns out, there are lots of them. Here’s a few I sent over:

    Beginner Videos:

    If you know a bit, then maybe this one would help you differentiate: SQL vs NoSQL or MySQL vs MongoDB

    A course on databases, maybe too much for most non technical people, but seemed not to bad of the couple I watched: Database Lesson #1 of 8 – Introduction to Databases

  • The Database Giveth and Taketh Away

    How many of you have ever run a database query with the wrong parameters and produced a report that you sent to fulfill a request? How quickly did you send another note to ask the report be discarded as a new one was coming? Probably a few of you. I know I have been in that situation during my career. If not a bad query, perhaps some of you incorrectly gathered data from other sources or made the mistake of killing the wrong session or updating the wrong data. Database errors occur, and often we have to decide how to pick of the pieces from a mistake.
    Recently there was a fairly high profile database query error with Southwest Airlines. Apparently a mass email went out to let customers know they had been awarded the valuable “Companion Pass” from the company. Many customers with a heavy travel schedule aim for this to allow a companion to travel for free with them. After receiving the email, many contacted the company as they didn’t expect the award. As someone that travels a lot, I know most of us do know exactly when we achieve some status and when we’re short of the level.
    Southwest admitted this was a “database error” and gave away a few coupons to customers, but this an embarrassment as well as an annoyance for customers. Most probably got the notice and didn’t feel bad when it was taken away, but a few people were probably close enough to try to change plans and book another person, only to find they weren’t getting a reward. I know, this is a first world problem, but still somewhat embarrassing for Southwest IT.
    I doubt this was a database error, unless a DBA updated some rows incorrectly. That’s certainly possible as an UPDATE with the wrong (or missing) WHERE clause is a common occurrence. My guess is that this was likely either a bug in some software that posted mileage completed or it was a poorly written query used to send out emails to customers. In any case, this likely wasn’t a big issue, but it is something that has occurred before. Sometimes with worse consequences.
    There will always be data entry errors, and certainly query mistakes in what we run. I don’t know of how we’ll eliminate more of these mistakes, other than to create more alerting that looks for anomalies and let’s someone know to double check that the values are correct. Actually, to me this is one area where AI is useful. It can detect things that look wrong, as it does with spell check or next word suggestions, and then let a user decide to keep or change the data. That might be something we would appreciate in SSMS. It could offer to correct my “selcet” to “select” while also letting me know that setting a price to $150 might be wrong when all other prices are $1500 or greater.
    Steve Jones