Voice of the DBA

Author: way0utwest

Archive to the Lake

Microsoft Fabric was announced at Build in May 2023. This is the next evolution of data warehousing from Microsoft, folding in Synapse and a number of other technologies to create a simpler location for storing and analyzing data. We’ve published some articles on the platform and there’s a great presentation from Mr. Paul Andrew on Linked In. It’s worth listening to, even in the background. Paul has a nice style and a great voice.

Part of this platform is OneLake. This is a data lake for your org, just one of them, and while it’s able to store data in many formats, it’s mainly optimized to read tabular data in the delta parquet format. This is essentially a compressed text file that allows for some transactional changes to the original data in parquet format.

I don’t do a lot of work with text files, and I’ve been suspicious of using lots of CSV or other text files in a warehouse environment, which is what a lot of people were advocating a few years ago. Exporting tables into lots of files split on some field, like date, while easy, didn’t seem like the best way to move data for reporting.

Fabric, however, is optimized for reading delta files. A few presentations I’ve seen from people have advocated for exporting your data from SQL Server (or other platforms) into parquet. While I don’t know there’s a native way to do this (yet), I suspect one is coming. I’ve seen lots of articles (one, two, three, more) about how to do this now. We also have SQL Server able to read these files with external file formats already, so I’m sure we’ll have an easy way to write them soon.

Many of us struggle with large systems, especially with query performance. We’d love to archive off data, though that’s often impractical. However, in an amazing, wonderful world, maybe we’ll get lots of people doing this, writing about it in the media, and our bosses will start to let us establish an archive in the lake. We could move some data there, especially old, unchanging data. We could delete that from source systems. We could have all our users happy.

I don’t know if I see lots of data moving to the lake, but I certainly expect lots of it to be copied. If you haven’t thought about archives, data lakes, and text formats, it’s an area that seems to have a lot of growth. Perhaps it’s of interest to you and you might find a new career.

Or maybe you just hope it gets widely adopted to relieve some pressure on your OLTP server.

Steve Jones

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

August 7, 2023
100 Days to the Data Community Summit Keynote

The start of learning at the 2023 PASS Data Community Summit is 100 days away. I checked.

The Summit starts for many of us on Monday with precons or an early arrival and spending time with friends. However, the first keynote is Wednesday, and that’s the main conference for most people.

100 days away.

It’s amazing to think we’re that close. Speakers are working on their sessions, polishing decks and practicing demos. Hopefully attendees have gotten all their travel arranged. The sessions are out, and you can build a schedule. You can even still add a pre-con if you want.

Hopefully I’ll see you there. If you’re valuable at work, if you get things done, make a case to send you to the Summit to learn things and network with others. Those contacts might be invaluable in the future when you have a question or problem.

And if you’re going, consider coming to SQL Saturday Oregon the Saturday before and taking the train up with us. It’s a fun time.

August 7, 2023
Copilot Experiments: A little PowerShell help
It’s been a little while since I’ve had time to relax a bit and try some AI help. This is another experiment I made.

A user on SSC asked about PowerShell to copy files with a date appended.

This is part of a series of experiments with the ChatGPT and other AI systems. Lots of Copilot lately.

I added some code to a new file and typed a prompt:

If I run this, it does work. Sort of.

It made a folder copy, not a file copy. However, the filter worked.

Let’s try again. I’ll modify the prompt and get Copilot to explain what it’s doing in the code. I get this:

Which works:

Hmmm, can it do what I want.

I tried a few prompts in the code window, but I kept getting things that wouldn’t work, like call copyfiles.bat, or something that didn’t work.

Let’s move on.

Copilot Chat

I got access to the Copilot Chat as part of Redgate. There is a new chat extension to add to VS Code, which I did. I opened it and got this with my prompt:

Good, this code will copy the files, but does all of them.

One advantage of AI bots is I don’t need to start over. I did this:

This worked correctly.

This was a simple example, but it produced about the same code as I did, albeit slightly cleaner. Mine was this:
```
$source="c:\fileloading" #location of starting directory
$destination="c:\filecopy"; #location where files will be copied to
$files="*dys_ihhist*" #files matching this pattern

# write a powershell command to get a list of files in $source matching the $files pattern
$a = get-childitem $source -filter $files
$a | foreach {write-host $($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}

# write a powershell command to copy files from source to destination appending the date to the filename
$a | foreach {copy-item $_.fullname $destination\$($_.basename)-$(get-date -f yyyyMMdd)$($_.extension)}
```
I don’t know enough PoSh to know which is really better. And honestly, I don’t feel like testing at scale. Let me know if you have knowledge here.

However, the chat window for copilot produced this quicker than I did, without me having to try and remember the PoSh parameters and structures of the functions. I had to dig around on SO to remember basename was what I needed and look up the parameter for get-date.

The code window isn’t great, and partially I think because I don’t know how to get prompts to work in the comments, but I do like the chat window. I’ll keep playing.
August 4, 2023
Flexible Work Policies are Preferred

The great post-pandemic, post-Great-Resignation, hybrid/remote/in-the-office work debate continues. It seems almost every week I see more stories that report on, hype, attempt to prove, or otherwise stoke emotions about whether the future of work for many people is more likely in any of these three situations. One company mandates everyone back in the office, another goes completely remote, and it seems many can’t decide and have some sort of hybrid approach.

Your boss might have strong opinions on one of these situations, which may or may not align with your organization. I see CEOs wanting one thing and individual managers sometimes wanting another. As much as I like remote working, and I’ve been based out of my home for over two decades, I also like seeing people and I am regularly traveling to the various Redgate offices (along with Grant and Ryan).

If you want to try and sway your boss to let you work more flexibly, which is likely remote or hybrid, perhaps this article will help. There’s research from at least one company showing that having a flexible policy has helped a number of companies hire faster in 2023. While this isn’t in any particular geography, industry, or role, it does show that finding employees seems to be faster with flexible policies. That makes sense as the more remote you are, the more people in your pool of potential candidates.

Of course, this doesn’t say if these people stick around, or if they’re qualified. Certainly many of us in the tech industry see plenty of candidates (or even coworkers) that don’t seem qualified for many, or any, roles. Hiring faster isn’t always good if there aren’t good candidates, but having more choices of who to hire does give you an edge in finding qualified candidates.

It also gives you more work to do in separating those you’d consider hiring from those you wouldn’t. The flip side as well is that if you are flexible, then perhaps you can convince some already employed, talented workers to come to your organization.

To be fair, many of us with these options are very lucky. There are lots of jobs where this isn’t a debate and people must show up to work every day. Cooks, taxi drivers, retail workers, and many, many more. This is a luxury issue, but it is still an issue. My view is that lots of knowledge work can be done remotely. However, I also think that teams gain something when they bond and get to know each other in person. Day-to-day work is great remote, but brainstorming and creativity work better sometimes when people are in the same room. Not always, but there are times it is better.

So let your boss know that they might be able to better fill their open positions if they’re flexible. Maybe you’ll convince them to be more flexible with you as well.

Steve Jones

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

August 4, 2023