Voice of the DBA

Author: way0utwest

Data Debt

I had never heard of data debt until I saw this article on the topic. In reading it, I couldn’t help thinking that most everyone has data debt, it creates inefficiencies, and it’s unlikely we’ll get rid of it. And by the way, it’s too late to get this under control. I somewhat dismissed the article when I saw this: “addressing data debt in its early stages is crucial to ensure that it does not become an overwhelming barrier to progress.” I know it’s a barrier, as I assume most of you also know, but it’s also not stopping us. We keep building more apps, databases, and systems, and accruing more data debt. Somehow, most organizations keep running.

The description of debt might help here. How many of you have inconsistent data standards, where you might define a data element differently in different databases? Maybe you have duplicated data that is slow to update (think ETL/warehouses), maybe you have different ways of tracking a completed sale in different systems. Maybe you even store dates in different formats (int, string, or something weirder). How many of you lack some documentation on what the columns in your databases mean? Maybe I should ask the reverse, where the few of you who have complete data dictionaries can raise your hands.

For most of my career I’ve heard a couple of terms that I’ve never really seen implemented. There’s the famous “single version of the truth” for a system, which seems to break down whenever we add a reporting or warehousing system. Even inside a single database, often an OLTP one, it’s hard to get a truth because values are changing so fast. The other term is MDM (master data management), which promises to ensure that every element is tracked and tagged the same way. No misspelled customer names or outdated addresses. There have been no shortage of products I’ve seen to help people tackle this problem, but ultimately I think the amount of data debt is too high. When we realize we need MDM, we’ll never pay down that debt, mostly because too many developers have too many habits and legacy ways of capturing data that will never get integrated into any MDM dictionary.

The article seems like a great academic set of principles. Make sure you label all your data. Put governance in place, with good access controls. Train workers, establish accountability to properly manage data. Invest in scalable architectures. How many of you can add scale to your system easily? It’s always taken me jumping through a variety of hoops to do that. The cloud makes it easy.

For a month. Then when the bill comes, you’ll be scaling back down.

Really, the chaos of the real world, where organizations are not one thing, but a large number of people and groups, each with their own goals and processes, just trying to get enough done to keep the organization moving forward is where we live. There’s no real time to deal with data debt.

Except if you’re the ETL person. We mostly pay you to move data around and clean it as best you can. At least then the problem remains hidden from the report readers, who trust you’ve actually done the T portion of ETL correctly.

Steve Jones

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

Note, podcasts are only available for a limited time online.

January 31, 2025
Advice I Like: Why am I doing this?

You are never too young to wonder “Why am I still doing this?” You need to have an excellent answer – from Excellent Advice for Living

I’d say that you’re also never too old to ask these questions either. When you realize that you’re doing something out of habit or familiarity and not for other reasons, I think it’s worth re-evaluating the situation.

It’s very easy to get into a rut and keep doing those things, even things you don’t like, from habit, obligation, or just because.

Don’t do that. Instead, make sure you actively choose to do things. Things you want, things that you are obligated to do, committed to, or even things that matter to you. Don’t just keep doing something for no reason.

I’ve been posting New Words on Fridays from a book I was reading, however, a friend thought they were a little depressing. They should be as they are obscure sorrows. I like them because they make me think.

To counter-balance those, I’m adding in thoughts on advice, mostly from Kevin Kelley’s book. You can read all these posts under the advice tag.

January 31, 2025
Creating vs. Maintaining

If your job as a developer or DBA has been like mine, it’s a constant stream of requests to change something, often without enough information and short deadlines that create a bit of stress. There’s always more work to be done, and while it might be a great job, you’re often trying to finish something quickly enough to get to the next thing.

In this mode, how often do you think about creating (or modifying) the thing you’re working on for today vs maintaining it for tomorrow. In other words, do you consider how easily your work can be understood, is documented, is designed to allow for flexibility, and can be enhanced without many (any?) side effects, or anything else.

In other words, is it maintainable?

We often build things to solve a problem and we can be very creative. We design a solution that works well, solves a problem, and may work very efficiently. However, I know I often haven’t thought about future maintenance. I haven’t considered how difficult it may be to understand the context around what I put together by anyone else. Sometimes I’ve built things that things that were very clever, but weren’t easily understood by my team or able to easily adapt to new requirements when those arise.

Often I’ve been looking at the problem from only a “it’s this tree that’s important” perspective, and forgetting that my particular thing is part of a wider system (a forest). I hadn’t been considering future maintenance, which often led someone in the future (often a future-me) to tear it down and rebuild something new.

That’s technical debt.

When some structure isn’t maintainable, it’s debt. It’s a burden for the team in the future. Designing things quickly and building them within a deadline, while making them maintainable requires some knowledge, experience, and also discipline to work with the patterns, and avoiding the anti-patterns, that make code difficult. The same thing applies to managing systems. Custom jobs on every server and separate configurations make life hard. At the same time, a one-size-for-everyone approach also isn’t maintainable. We need a balance of well-written solutions that solve our problems, but are easily maintainable.

Part of becoming a better engineer or admin is learning how to build maintainable things that others will continue to use for a long time, not because they have to, but because they want to because the code works well.

Steve Jones

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

Note, podcasts are only available for a limited time online.

January 29, 2025
A Quick Test Data Manager Eval with My Database Backup

I wrote about getting the Redgate Test Data Manager set up in 10 minutes before, and it was a great post. In that one, the sample database Northwind was created and used. However, Alex Yates has modified the scripts to work with backup files, and I’ll show you how easy this is in just a few minutes.

This is part of a series of posts on TDM. Check out the tag for other posts.

The Setup

I’ve filtered my SSMS to only show databases with BB in the name. You can see I have none.

I also have a backup file of a baseball database on my d: drive. My local instance has access to this folder as I use for backups and restores in dev/test work.

While I can pass these parameters in, it’s easy to just change the values in the file after cloning the repo. This way it’s easy to see what’s going on.

That’s it, now let’s fire up PowerShell.

Running the Eval

When I run the file, I see it start up and report the various values. You can see that it’s set the base database name to “BB” and I should see the two databases with the suffixes created. I also see my backup path.

This runs and in a few minutes, I see that the databases have been created and we are ready to subset.

Checking SSMS with a refresh, I see the databases.

If I type “y”, the subsetter runs, and very quickly. This isn’t a massive database, but it is thousands of rows, which makes it easy to play with.

If I run counts, I see this. The left is the full restore, which has 16k records. The subset, on the right, has about 10% of that, with 1644 rows. Pretty cool. So far, this has taken less than a couple of minutes.

Now let’s continue to press “y” and get the classification, mapping, and masking done. Two tables were found with PII (names) and masked.

If I query the tables, I see the results below. Notice that not all values were moved, as the first ID in the subset is 11, but we can see IDs 11 and 22 were masked.

This was a very quick look at running an eval with my own database backup, not a sample db. We’ve had a few people ask to do this for their own testing, and we modified the scripts to work with backups.

Give TDM a try today from the repo and a trial, or contact one of our reps and get moving with help from our sales engineers.

Video Walkthrough

Check out a video of my demoing this below:

January 29, 2025