Voice of the DBA

Category: Editorial

  • Modeling and Design Are More Important in DevOps

    I’ve run across a few customers that are adopting DevOps processes for the database. This makes sense as I deal with Redgate Customers, many of whom are doing this. As they adopt a Compliant Database DevOps process, it seems often that there is a push for database developers to react to feature requests quickly, at the same rate that the application developers do, and push out new code regularly to support changes. This new code often includes schema changes to support new features and functions, which often means table changes because the data elements need to be stored somewhere. This, of course, means the data model changes.

    In many cases, trying to release a new feature today or tomorrow, or even next week, means making quick decisions. As an example, if we capture lots of cost and price information in an application and are enhancing this to add currency values, we’ll be adding fields to any table that stores financial data. How we do this could vary. It’s easy to add a currency lookup field to all tables, and that might be what many application developers want to do. If they’re using an ORM, they might just add this as a property to their object definition, which generate a series of ALTER TABLE statements to add the related fields. Certainly, a database developer could just generate those same scripts.

    Whether this is the best choice, or if some more normalized structures are needed, is unclear. That’s dependent on the problem domain, and if date stamps are needed to capture currency differentials at times, or even if we need additional FKs to ensure there is referential integrity, many junior developers and DBAs might not think about the implications to the data model. As time passes, this could mean additional technical debt to deal with, limiting future enhancements. This could also mean fundamental flaws in how financial data is calculated, potentially opening impacting revenue if the data model doesn’t support accurate calculations.

    Moving to DevOps doesn’t necessarily mean moving fast. It can, but it’s really about making focused, small changes at the rate that matters for your business. It also means that your data model and design of data store structures becomes more important than ever. While we can make decisions quickly, this takes experience and understanding of the business impacts, as well as the potential downfalls from different types of structures. DevOps asks us to give feedback about potential problems up and down the software development pipeline, which should include a data architect of data modeler. Someone with experience here can help to consider future implications and even provide some flexible designs that can adapt in situations where we have incomplete knowledge.

    Since we build our software on the data, we need to ensure we are properly capture the data in a way doesn’t create too much technical debt. There are many, many stories of organizations that struggle to grow their applications over time, often because of very poor data models. Many of these issues could have been avoided by consulting with senior developers, DBAs, and data architects/modelers for an hour before making a fundamental change. Even if this means keeping a consultant on retainer. The investment in reviewing and understanding the data model can pay off tremendously in the future, especially as the cost of data processing is often one of the larger costs of running an application. Whether this is an RDBMS like SQL Server or an alternative structure like CosmosDB. A little investment in modeling early can prevent the need to over-provision resources later.

    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.

  • 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.