Press "Enter" to skip to content

Curated SQL Posts

Saving Money in Microsoft Fabric by Automatic Pause and Resume

Soheil Bakhshi saves us some cash:

If you work in data and analytics, particularly within the Microsoft Data Platform, you have likely heard of Microsoft Fabric and its many capabilities. However, one of the biggest challenges organisations face is managing costs effectively.

In previous blogs and videos, I have covered how to optimise Microsoft Fabric capacity costs by automating the pause and resume process using Logic Apps. This approach ensures that your Fabric capacity runs only when needed, reducing unnecessary expenses. But how much can this method actually save? In this post, I share the real cost-saving results after applying this automation over the past few months.

Click through for some information on how much Soheil saved over a six-month period.

Comments closed

Customer KPIs in T-SQL

I have a new video:

In this video, we will take a look at several useful KPIs for measuring customer growth. We will also demonstrate the utility of DATETRUNC(), calendar tables, and the LAG() and LEAD() window functions.

The calendar table (or date dimension) interlude may seem a little bit weird at first, but I firmly believe that calendar tables absolutely belong on basically every SQL Server instance, even if only in a utility database.

Comments closed

An Overview of PostgreSQL Performance Monitoring via pgNow

Grant Fritchey announces a product:

I’ve been putting together a new PostgreSQL session called “Performance Monitoring for the Absolute Beginner.” There are several ways to get an understanding of how well your queries are running in PostgreSQL, but, frankly, all of them are a bit of a pain to someone coming from the land of Extended Events (ah, my one true love). Because of this, I saw it as an opportunity to help those just getting going in PostgreSQL. I’ll be presenting it for the first time at Postgres Conference in Orlando on March 19, 2025. Come on by.

Anyhoo, wouldn’t it be nice to maybe have a shortcut, an easier way to look at this information?

Well, there is. Redgate has been working on a completely free tool for leveraging just this sort of data called pgNow. Go here to check it out yourself, but I’ll do a quick run through here.

Click through to see how it works.

Comments closed

Reading Delta Table Metadata in Power Query

Chris Webb gives us the scoop:

There’s a new M function rolling out now that allows you to read metadata from Delta tables (at the time of writing it’s available in Dataflows Gen2 and will be available soon in Desktop). It builds on the DeltaLake.Table M function that allows you to read data from Delta tables and is similar to the Parquet.Metadata function that was released last year. 

Click through for an example of how to use it against a Delta table in OneLake.

Comments closed

Using a Naming Convention for Microsoft Fabric Items

Marc Lelijveld asks, what’s in a name?

In Fabric, you can have many different items in your Workspace. So many, that you easily get lost! Luckily there are tools at hand like Taskflows and Workspace folders. But still, it can be challenging to easily find all your items that ingest data, or find all items that are used for inbetween layers to transform data.

In this blog, I will tell you more about my personal best practice for naming convention of Fabric items that helps me to structure everything in my workspace.

This kind of thing typically doesn’t matter much when you only have a dozen or so items in your workspace. But as that number increases and different teams are working on different sets of items, it gets harder to figure out what’s going on without a proper naming convention.

Comments closed

Finding Empty Attributes in Powershell

Patrick Gruenauer looks for what’s missing:

We are often looking for attributes, but what about the empty attributes? How can I find out if an attribute is empty? That is the focus of this article. I will show a practical example of how to find these empty attributes.

Let’s start with how to find attributes which are NOT empty. The following code retrieves all svchost process Attributes which have a value.

Read on for the pipeline to see which attributes have a value, followed by a similar pipeline to find missing values, as well as one practical use case for why you might use this.

Comments closed

Migrating Azure PostgreSQL Single Server to Flex via pg_dump

Josephine Bush changes server type:

This is more complicated than using the Azure Migration method, but because it’s maxed out on resources for the last week in the east regions (and possibly central), and who knows when they will fix it, I had to resort to other methods. I’m getting on flex sooner than later. I want to get this over with and get to those performance improvements and better features. I will preface this all by saying, if you have big databases, this may not be the right path for you. Look into streaming replication or wait for Microsoft to fix their migration tool and do an online migration via that. Also, if you don’t have strong Postgres skills, this is far more complicated than the migration tool in Azure, far more complicated.

Click through for the step-by-step instructions.

Comments closed

Configuring and Testing Power BI Aggregation Functions

Reza Rad does a bit of configuration:

Aggregation can speed up the performance of DirectQuery sourced tables significantly. To use it, firstly, you need to create an aggregation table. Secondly, you must set up proper storage modes for tables in the model. Finally, you have to configure the aggregation functions, which I will explain in this post. You can learn about aggregation in other posts of this series and continue the example here. If you like to learn more about Power BI, read the Power BI book from Rookie to Rock Star.

Read on to learn more.

Comments closed

Dealing with Schema Drift in Azure Data Factory

Rayis Imayev deals with change:

I will jump straight to the problem statement without a “boring” introduction, which, in a sense, already feels like an opening statement.

Moving data between two or more endpoints is a common task. Sometimes it’s as simple as migrating data from one place to another. Other times, it’s a request to copy specific documents from source environments. In more complex cases, you might need to consolidate multiple data files into the same destination, such as loading several separate files into a single database table.

This was the bete noir of SSIS’s existence. Minor metadata changes would cause the entire system to break down.

Comments closed