Press "Enter" to skip to content

Curated SQL Posts

Working through an Azure SQL DB Vulnerability Assessment

Published 2025-05-08 by Kevin Feasel

Josephine Bush digs in:

But do you always need them? Not necessarily. A vulnerability assessment might confirm what you already know. On the other hand, if you’re managing multiple environments or collaborating across teams, these tools can catch things you might miss, like forgotten test accounts or relaxed security rules.

They’re not a silver bullet, but can be a smart way to stay proactive. Ensure you understand what you are trying to achieve, not just blindly accept what they tell you.

Click through for an example and some solid advice.

Comments closed

Caching Database Calls in Python with Redis

Published 2025-05-08 by Kevin Feasel

Levi Masonde stands up a Redis instance:

Databases play a vital role in software applications—they need to keep updated data or state, which is served by the database acting as the source of truth for the application. How the database performs affects how the entire application performs. Besides obvious factors that affect the performance of a database (hardware, database type, networking infrastructure), there are techniques designed to help you improve the performance of your database and ultimately, your applications. One way to do this is to add caching to your database. But which cache technique works best for which application requirements? This article sheds light on one strategy to implement a cache system.

Click through for one pattern of interaction between cache and database. My preference with the cache-aside pattern is to hide the two data platforms from the calling application as much as possible. In a classic object-oriented language like C#, the actual database + cache calls would be in a separate project and would expose methods on classes that were database-agnostic. With Python, I’d use different .py files in the same project unless I wanted to build a wheel file and deploy it to multiple projects, but the concept would still be the same. I’m not the biggest fan of the way that Levi did it, forcing API developers to have knowledge of both the cache and the database, as that increases the risk of a future developer messing something up.

Comments closed

Review those Logs

Published 2025-05-08 by Kevin Feasel

Kevin Hill has a public service announcement:

Most SQL Server crashes don’t come out of nowhere.
They leave breadcrumbs – red flags that something’s not right. The problem? If you don’t know where to look, you miss the signs…until it’s 2am and your CEO’s calling.

Let’s talk about how to listen for those whispers before they turn into full-blown alarms.

Click through for some advice on the topic. I’ll also note that everything Kevin mentions, you can automatically retrieve and centralize in a monitoring system, and once you have more than a couple of SQL Server instances, I’d recommend doing so.

Comments closed

Working with the JSON Data Type in Azure SQL DB

Published 2025-05-08 by Kevin Feasel

Dennes Torres tries out the JSON data type in Azure SQL Database:

Before this new field type, JSON data was typically stored in varchar(max) columns. There are many features to use with JSON values stored in varchar(max) columns and variables, but storing JSON as regular strings is still limited.

The built-in JSON type expands the possibilities. Using an actual JSON column, it becomes easier to build constraints related to JSON columns, for example.

Dennes also spends a lot of the article covering the JSON_ARRAYAGG() and JSON_OBJECTAGG() functions.

Comments closed

The Dual Perils of Overfitting and Data Leakage

Published 2025-05-07 by Kevin Feasel

John Mount shares notes on a theme:

One of the bigger risks of iterative statistical or machine learning fitting procedures is over-fit or the dreaded data leak.

Over-fit is when: a model performs better on training data than on future data. Some degree of over-fit is expected. A data leak is when: the model learns things about the evaluation set that it would not know about the future data the model will be applied on. This can drive models that look great on training and (supposedly) held-out data, but don’t work in practice.

Click through for the rest of the story, and be sure to check out the comments for a notebook digging further into one of the topics.

Comments closed

The APPLY Operator in T-SQL

Published 2025-05-07 by Kevin Feasel

I have a new video:

In this video, I explain the history behind the APPLY operator in T-SQL. I also demonstrate one use case for it: executing table-valued functions.

As I mention at the end of the video, this is by far the least interesting use case for the APPLY operator. Subsequent videos in the series get a lot more exciting. But we have to start at the beginning, unless we’re going full Pulp Fiction.

Comments closed

Organizing a Microsoft Fabric Data Platform with Domains

Published 2025-05-07 by Kevin Feasel

Jon Vöge does a bit of organization:

A topic which seems more relevant than ever, is the question of how to organize the contents of your Microsoft Fabric Platform.

Through the contents of a few blogs, I will give you an overview of things to consider, as well as suggestions that you can choose from when designing your platform.

This first week, we’ll take a look at Domains in Microsoft Fabric.

Read on to understand why domains can be valuable and a solid way to structure them.

Comments closed

Choosing a Warehousing Data Architecture

Published 2025-05-07 by Kevin Feasel

James Serra compares and contrasts OLAP architectures:

As discussed in my blog and book “Deciphering Data Architectures: Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh” (Amazon), organizations are often challenged with choosing the right data architecture to meet their business goals—especially as AI and data-driven decision-making take center stage. To help clarify, here’s a quick review of the four core architectures, followed by guidance on when to use each. Each architecture includes five stages of data movement – ingest, store, transform, model, and visualize (described here).

Click through for James’s take on how each of them works and when you might choose one over the other.

Comments closed

Writing to Microsoft FabricDelta Tables in Python via DuckDB

Published 2025-05-07 by Kevin Feasel

Gilbert Quevauvilliers does a bit of writing:

When I was exploring how to easily write to Delta Tables with a Python notebook, it took me a considerable amount of time to find out how to do this.

This is my learnings below, and from my point of view it makes it easy to write to a Lakehouse table, like what is done with a PySpark notebook.

Click through for one very important note, as well as the process.

Comments closed

Tracking Wait Classes in Oracle

Published 2025-05-07 by Kevin Feasel

Kellyn Gorman looks for performance bugbears:

When we talk about optimization in Oracle, many DBAs immediately jump into metrics like CPU utilization, I/O throughput, or specific SQL queries. But there’s a critical layer of understanding that often gets overlooked and that’s Oracle Wait Classes. These categories are essential to making sense of what’s really going on inside your database, and they’re often the starting point for diagnosing and optimizing performance.

Read on to learn what wait classes are and why they’re so important for Oracle DBAs.

Comments closed