Voice of the DBA

Category: Editorial

  • ETL or ELT

    I was reading a piece on ETL patterns and found it interesting that the author noted that one way to reduce the challenge of ETL processes keeping up with changing schemas is to move to an ELT process. That’s an interesting perspective, and I wonder what those who work with the relational model think. IS this a pattern you like?

    In a traditional ETL pattern, we get data from a source (Extract), manipulate it to suit the target schema (Transform) and then Load it into the target. The hassles here, and the reason this is hard, is that the transformations are often complex. We deal with ragged rows, various delimiters and terminators, and more. Lots of consultants have made quite a bit of money by building these transformational applications and processes and maintaining them as requirements change.

    The flip of the last two parts, moving from TL to LT, looks to defer some of the transform work until the data is in the destination. This means that the transformation could be a schema-on-read, as the article proposes. The idea here is that all data is ingested into the target system as is. This means that structured CSVs reside alongside spatial data and Word documents.  Then, once you have the data, you can transform it as needed, to suit whatever requirements exist at that time.

    Certainly this sounds appealing, but for many of us in organiztions, the challenge is still determining how to transform the data. This is often dependent on what the data is and how it can be used. We still need to do the work to understand how this data is structured and change something to make data available to the end user. Whether we do this in the middle or after the data is in a target system doesn’t matter. I know some products, like the MarkLogic one in the post and Azure Data Lake want to reduce must of the ETL effort, but I’m not sure that will work for really important data. For some, sure, we can collect it and sort out the transformations later. However, for useful data, data that the business wants regularly and quickly, most of us need to just get a flow working that will move data on a regular basis.

    I’ll admit that I often prefer to transform data inside of a SQL Server target, OLTP or DW database. The reason is that mass changes are often easier with T-SQL, working sets of data rather than row by row. I recognize that doesn’t always work, but I know that for most data that business people do find useful, I’d rather have an ETL process build sooner rather than later.

    Steve Jones

    The Voice of the DBA Podcast

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

  • A New New Microsoft

    Microsoft is changing again. Microsoft is reorganizing and the head of Windows, Terry Myerson, is leaving. The reorganization isn’t that surprising, as like most large companies, Microsoft usually does something each year. What is interesting is that they are moving away from their major divisions focusing on products. We’ve had divisions for Windows, Servers, Devices, etc. Now we move to Experiences and Devices as one major group. The other will be Cloud + AI Platform, which seems more product oriented, but still is rather amorphous. Gaming is still separate and there’s still a large Research engineering group.

    Why are they doing this? There’s some thoughts at ZDNet and Geekwire. It seems that the cloud is becoming the most profitable part of Microsoft, and I’d expect that we will continue to see more push for cloud and subscription type software. Along with the advances in AI, Microsoft seems to be hoping that more of us will start to do work using their infrastructure. That makes sense for some, but not for others.

    For those of us using the data platform, I do think that we ought to be thinking cloud first unless we already have substantial infrastructure and automation capabilities to quickly stand up new instances. As development and other departments look to get work done, especially around data analysis, we need to meet their needs or many people will start to use the easy to provision and use cloud services, especially those for machine learning and AI. Companies are entranced by new technology, even if we aren’t, and the media hype around ML and AI will put pressure on us to do better. We don’t need to learn R or Python, but we should be able to run those scripts against data if our business demands the capability.

    The GDPR and other security initiatives might seem to slow cloud deployment, but in many cases the cloud isn’t more or less secure than our own systems. Certainly we may have issues with privacy with the new US CLOUD Act, but for most of our businesses, that won’t really matter. The conflicts there are more around illegal activities, and if you are in that space, US based companies might not be your choice for hosting data.

    For many of us, we do need to understand how to build and enforce more secure applications, and I’d argue that Azure makes this as easy any any other system. Our recent move at SQLServerCentral to AWS was essentially the same as if we’d have moved to a VM at Azure, Google, or even back to the Redgate office. The systems look the same, it’s the cost, the firewall and networking, and things beyond the data platform that are just different. And that’s the key, they’re not necessarily better or worse, but different.

    As Microsoft changes, I think their code quality improves, though certainly their rapid pace means that people getting updates too quickly are on the bleeding edge. Their DevOps deployment, however, means that the bleeding edge usually doesn’t last tool long if the issues are severe.

    Steve Jones

     

  • dbatools is Growing Up

    The dbatools.io project is growing up rapidly. I went to check something the other day and I was amazed at the command list. This used to fit on one page. Now it’s not close. Here’s the first page:

    In fact, it doesn’t fit on two pages. Here’s the part below the fold.

    It’s crazy how quickly this project has grown, and how many cmdlets are available to help you not only migrate objects and settings between instances, but perform useful operations on your systems and test or change their configuration. I’m starting to think that this needs to be required for most people that manage systems. The ways that you can easily manage your systems in a programmatic manner has dramatically changed with this module.

    I was surprised the other day when I saw a blog post for a cmdlet I didn’t know existed. I went to try it on my system and it wasn’t available. I’d updated the module the previous week, but apparently there are new changes on a regular basis. I might need to update my module more often, and there’s a cmdlet to help me do that!

    Those of us that have a lot of experience with SQL Server might find some of these tasks easier in T-SQL. I certainly spend more time inside the platform than out of it, but there are definitely advantages to using PowerShell to enforce consistency and easily work with multiple machines is nice. These utilities are often like scripts or procedures I’ve written to handle simple tasks in a wrapper-like fashion. dbatools do that from PoSh, and allow me to easily string together disparate comands. I won’t give up T-SQL anytime soon, but I’ll do more with dbatools than I might do with PoSh otherwise.

    If you haven’t tried this project, you ought to. This will make it easier to learn PoSh and work with SQL Server. Maybe you’ll enjoy it enough to built your own module and contribute to the project. There are still holes, especially in the SSIS area, though I expect new cmdlets to appear any day.

    Steve Jones

    The Voice of the DBA Podcast

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

  • The Nightmare Letter

    I’m not sure if this imaginary GDPR letter is a nightmare, but I do know that in most of the organizations where I’ve worked, this type of request would result in a crash project for me. I’d be working long hours, contacting lots of people and trying to manage a complex spreadsheet of information about an individual. I’d like to think that I’d compile this information in a general sense to understand our data better and anticipate future requests, building a process that I could repeat, but I know that under pressure that might not always happen. I’m sure I’d grab some data without capturing and saving the metadata or query. I’d probably have to perform duplicate work when the next request came in.

    GDPR enforcement begins in a couple months, and organizations receiving this type of letter will have 30 days to respond. Companies can also charge a reasonable fee based on administrative costs for information requested. The fee that’s reasonable for getting a few of these letters a month might not be sufficient if hundreds or thousands of individuals start requesting this information, and I’m sure companies and authorities will be arguing about the rates.

    With the focus on privacy in the media, and the mishandling of data regularly by companies, I wouldn’t be surprised if there are going to be large numbers of requests by individuals. In fact, I’m wouldn’t be surprised if there are scripts or applications being built now to facilitate the ability for lots of individuals to ask for this information from companies about their data processing.

    Really all of this information should be documented and any decisions made about securing sensitive data should always be followed. Any organization should know how they handle data, where it’s stored, and how it’s secured. This is just practical and good administrative practice. The items about how data is processed and used are good business knowledge points. After all, should we be processing data without some justification for the resources involved? I think too often a company decides to implement some process without evaluating if it makes sense in the context of their mission. If it does, we should know why it does and be able to measure that. If it doesn’t, we ought to stop.

    If you do business in the EU or with EU citizens, you might wish to start ensuring you have a way to export the information requested in this letter. Being prepared for some of these items might make it much easier to respond to any or all of these requests.

    Whether you think this might happen to your organization or not, you might want to just save a copy of this letter. I know I will, with the idea that I might send this off to companies that store my data. Knowledge can help me protect myself by being aware of what’s being done with information related to me. If there are issues, having this information might help ensure my rights are protected. I’ll also be sure that I have a form letter to ask for removal of information. I’ve felt this wasn’t possible in the past, but at least in the EU, where I regularly travel, I can exert some control over my data.

    Steve Jones

    The Voice of the DBA Podcast

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