Press "Enter" to skip to content

Curated SQL Posts

Data Mesh and Event Streaming

Published 2021-12-16 by Kevin Feasel

Adam Bellemare takes us through an example of implementing data mesh ideas in Confluent Cloud:

Data mesh. This oft-talked-about architecture has no shortage of blog posts, conference talks, podcasts, and discussions. One thing that you may have found lacking is a concrete guide on precisely how to get started building your own data mesh implementation. We have you covered. In this blog post, we’ll show you how to build a data mesh using event streams powered by Confluent Cloud, highlighting our design decisions, and the key benefits, and the key benefits and challenges you’ll need to consider along the way. In fact, we’ll go one better: we’ve built a data mesh prototype for you to check out on your own to see what this would look like in action, or fork to bootstrap a data mesh for your own organization.

Read on for the example.

Comments closed

Bullet Charts

Published 2021-12-16 by Kevin Feasel

Amy Esselman is number one with a bullet:

Our November challenge was inspired by a recent workshop question about bullet graphs. We invited the community to find some data of interest and build their own bullet graph. More than three dozen members tackled this less familiar chart type in a variety of tools, including Excel, Tableau, Python, Illustrator, Datawrapper, PowerBI, and R.

For many challenge participants, this was their first attempt at creating a bullet graph. They discovered, as you may find yourself, that even though bullet graphs are not a typical chart type, they can be powerful in the right situation. 

Click through for examples of bullet charts in action.

Comments closed

Save Money with Spot Instances

Published 2021-12-16 by Kevin Feasel

I have a post on using spot instances in the cloud:

Spot instances are an idea which came out of Amazon Web Services. Specifically, the people at AWS realized that they had excess capacity on servers and in the cloud, excess capacity is typically a bad thing, as you’re paying for resources not in use. Going back to basic economics, when you have excess capacity, you have a surplus. There are two ways to deal with a surplus: decrease supply (shift the supply curve back) or decrease prices (move down the demand curve).

There are some complicating factors here which make it tough for AWS or other cloud vendors to do either.

Of course I wasn’t going to let a discussion of spot instances go without hitting a bit of economic theory. Just be happy I didn’t break out the supply and demand curve visuals…

Comments closed

Understanding SQL Server I/O Size

Published 2021-12-16 by Kevin Feasel

Anthony Nocentino dives into a topic:

A Master File Table (MFT) is the data structure that describes files and directories on NTFS. In Figure 1, you can see an MTF record has several sections describing the metadata about the file and pointers to blocks that make up the file. A block, also referred to as a cluster in Windows, is an abstraction over one or more physical structures (sectors or pages depending on the media) presented by the underlying disk. A block/cluster is also the atomic allocation unit from a file system and has a configurable size. On NTFS, this is referred to as the NTFS Allocation Unit Size and is a configurable attribute of the file system. By default, it is 4KB and can be as large as 2MB. Since a block is a unit of allocation, if a file is between 1 byte and the file system’s allocation unit size, it will take up exactly one block/cluster on the file system. As the file grows, more blocks/clusters are allocated to represent the file. The MFT data structure tracks which blocks make up a file. The block allocator of the file system will try to ensure blocks are physically adjacent on the disk and groups them together in runs.

A 4KB NTFS Allocation Units size is considered best practice on general-purpose file systems. And 64KB is considered best practice for SQL Server…but why? Let’s keep digging…

I was sitting at a lunch table with Anthony when it all clicked and that was fun to see.

Comments closed

Microsoft.DataFactory and Storage Event Triggers in Synapse

Published 2021-12-16 by Kevin Feasel

Cathrine Wilhelmsen troubleshoots an Azure issue:

I ran into an issue today while trying to publish a storage event trigger in Azure Synapse Analytics. After publishing, I got error messages that said “failed to subscribe” and “failed to activate”. The storage event trigger had been published, but it wouldn’t start. Help!

Click through for some resources on documentation, a few things which didn’t work, and what finally resolved the issue.

Comments closed

Building a Data Mesh in Azure

Published 2021-12-16 by Kevin Feasel

Paul Andrew starts a new series:

The concepts and principals of a data mesh architecture have been around for a while now and I’ve yet to see anyone else apply/deliver such a solution in Azure. I’m wondering if the concepts are so abstract that it’s hard to translate the principals into real world requirements, and maybe even harder to think about what technology you might actually need to deploy in your Azure environment.

Given this context (and certainly no fear of going first with an idea and being wrong ) here’s what I think we could do to build a data mesh architecture in the Microsoft cloud platform – Azure.

Click through for Paul’s take on the first data mesh principle.

Comments closed

The Importance of Data Dictionaries

Published 2021-12-16 by Kevin Feasel

John Morehouse takes us through data dictionaries:

Data professionals—whether they’re database administrators (DBAs), developers, or data scientists—work in a wide and varied landscape usually in flux and filled with challenges. These challenges could range from changing business requirements to keeping up with the sheer velocity at which technology evolves.

It’s also critical for these professionals to understand their organization’s data and how it applies to a given application or business unit. Better outcomes usually come from employing data dictionaries throughout the organization. Through many years of experience in IT professions, I’ve seen the utilization of data dictionaries range from “not at all” to “I’m documenting every possible data attribute known to humankind.” In my experience, data-related projects with data dictionaries as part of the process are far more likely to be successful than projects without them (even extremely populated data dictionaries are more useful than nothing at all). Trust me on this.

Click through to understand why you should trust John on this one.

Comments closed

The Continuing Relevance of Feature Engineering

Published 2021-12-15 by Kevin Feasel

Pete Warden points out something which is obvious and still needs to be said:

One of the most exciting aspects of deep learning’s emergence in computer vision a few years ago was that it didn’t appear to require any feature engineering, unlike previous techniques like histograms-of-gradients or Haar cascades. As neural networks ate up other fields like NLP and speech, the hope was that feature engineering would become unnecessary for those domains too. At first I fully bought into this idea, and saw any remaining manually-engineered feature pipelines as legacy code that would soon be subsumed by more advanced models.

Over the last few years of working with product teams to deploy models in production I’ve realized I was wrong. I’m not the first person to raise this idea, but I have some thoughts I haven’t seen widely discussed on exactly why feature engineering isn’t going away anytime soon. One of them is that even the original vision case actually does rely on a *lot* of feature engineering, we just haven’t been paying attention. 

Read the whole thing.

Comments closed

Changing Case in SSMS

Published 2021-12-15 by Kevin Feasel

Steve Jones has a quick tip for us:

I never knew I could change case for objects in SSMS easily. This actually was something that another individual pointed out to me, but once I tried it, I liked it and know I’ll use it at times.

Click through to see how to change your code to lower-case or upper-case in a single command.

Comments closed