Voice of the DBA

Category: Editorial

  • Self-Healing ETL

    The process of building ETL flows is cumbersome and time consuming. Many consultants have made a lot of money by building ETL processes that manage the movement of data in and out of databases, including to data warehouses. There is a lot of importance placed on consistent, quick, and accurate data loading. In fact, one of the most popular job needs these days is still for ETL developers. Even as data science grows and becomes popular, quite a bit of the work in data science is data preparation, which often involves ETL, or ELT.

    One of the struggles with an ETL process is the tedious nature of building lots of flows that are very similar. There may be a wide variety of ways to get data to move, especially as we have a crazy set of inconsistent formats in which data appears, but once we have the flow, we often repeat that for many different files or tables. I think Biml makes this better, but it’s still not simple, and there can be plenty of issues that still arise as the source inputs change.

    There was an interesting article that describes the use of AI to assist in ETL flows. While there is some basic matching up of source and target data right now, what if AI would add a few things. The idea of looking at the data you’ve matched and suggesting alterations that can potentially help with data quality is interesting. The system would have to learn what issues occur over time and what good data looks like, but perhaps this could even extend when the source data gets slightly mangled in formatting. Perhaps detecting fixed width fields that have grown and shifted all columns would be possible, and even adjust the data flow.

    I am actually more interested in detecting PII data and alerting developers, or even operations people that the ETL process needs to be changed or secured in some way. AI should be able as well to help Operations people with scheduling, even pausing or moving ETL work to other machines to better scale the flow. My view would be that any AI system ought to be looking for anomalies and detecting those, as pattern recognition is one thing AI is good at.

    I’d think this would be more of an alert capability as I’m not sure I trust an AI system to actually adjust data. ETL flows are often too important to have data mangled in an unexpected way. Perhaps that’s too cautious, as that’s what developers do all the time. They mandle data, and we find ways to recover. Ultimately, I think that having lots of logging on what a developer or AI system does is more important. This might be especially true if voice commands are used to build the flows. I could see lots of ambiguities and mistakes from business analysts that are trying to describe how to build an ETL flow.

    Steve Jones

    The Voice of the DBA Podcast

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

  • Republish – Normal Data

    Busy today at the InfoSec conference, so republishing Normal Data.

  • Remember the Simple Things

    This is a hardware post, and not at all database related, but it reminds me of troubleshooting problems in my career, and of a lesson that I thought I’d learned well. Apparently that isn’t the case.

    Over the last year I’ve been using a tablet to record my daughter’s volleyball matches. We’ve saved off the video between matches, often uploading it or sending it to college coaches. After the season, the tablet wasn’t used much, mostly for some movies on trips, but often it sat idle. Not too long ago a software update appeared for the Android operating system and I applied it, promptly forgetting about this.

    I recorded a couple matches earlier this year, but was too busy to download the video. As I was getting ready for a match last week, I realized that I needed to copy some of the video off the device to free up space. I connected it with a USB cable to my PC and …

    Nothing.

    No device appeared, and I couldn’t access video. I played with the settings in Android, even tried some software from Samsung. Nothing worked. I had read that modern versions of Android included some protection from random USB connections, so I ended up prying open the rubber case, removing the small SD card, cursing a few times as I tried to find a micro adapter to read it, watching the clock the entire time. Rushing to copy video and get out the door in time to make the match is not the least stressful part of my life.

    The next day I ran more searches, trying to find a way to connect remotely to the tablet from a Windows device. I kept seeing references that enabling the device visibility should work and Windows should detect the file system. It didn’t work, and this was frustrating. I was ready to purchase some software to allow remote connections to the device.

    Finally, I saw a note that some cables don’t allow data transfer. Apparently a micro USB cable is not a micro USB cable. I decided to try another one I had laying around, but no change. I did notice the message that the device was charging slowly, so I wandered through my desk to find the original cable that came with the tablet. I connected it and

    Voila.

    I had a file system to which I could copy video. I felt both relieved and rather ashamed of myself. I can’t remember the number of printer problems I’ve solved in my career by changing cables. Or the number of times a motherboard wouldn’t boot, but changing a power cable fixed the issue.

    Consider the simple things first when troubleshooting. They won’t always solve your issue, but if they do, you’ll be glad you tried them first.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Single Pane of Glass

    One of the things that I’ve seen talked about quite in the DevOps movement is a single pane of glass. That phrase seems to be in use to describe a way of quickly understanding the state of your environment. It could be the view of the software versions deployed in environments throughout the development process or the status and capabilities of your infrastructure. In either case, it’s a holistic view of your portion of the organization.

    I don’t think this is much different from the idea of a global dashboard that might be produced for business people. I’ve seen no shortage of those during my career. Certainly lots of business intelligence projects have been based on the idea of a single view of data that provides insight and assists decision making. Those ideas have been adopted by plenty of organizations that are moving to a DevOps process for software development.

    I’m wondering if any of you have a dashboard for your particular job? This could be in infrastructure as a DBA, where you have a single view of your database servers, maybe status and alerts? Perhaps you just a monitoring system like SQL Monitor to keep track of important data related to all the instances under your responsibility. Perhaps you have custom information important for your organization such as the flow of data in ETL processes, numbers of transactions, or some other measures to measure health or performance.

    Maybe you’re a developer that wants to know the status of all builds for the various projects under active development. As teams look to produce more reliable software, they often want to know the where their changes are deployed, if the code passes all tests, and even which work items are in which states. Azure Boards or DLM Dashboard are examples of how teams keep track of their responsibilities.

    There are any number of ways that organizations can keep an eye on the state of their systems, but I do think that any high performing group will have some way of measuring their work. After all, if you don’t know the state of your system, how can you determine if you’re getting better or worse.

    Steve Jones

    The Voice of the DBA Podcast

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