Press "Enter" to skip to content

Curated SQL Posts

Trying out the Databricks For-Each Task

Published 2024-10-02 by Kevin Feasel

Chen Hirsh goes in a loop:

Databricks recently added a for-each task to their workflow capability. Workflows are Databricks jobs, like Data factory pipelines, or SQL server jobs, a pipeline that you can schedule, that include a number of tasks that together complete some business logic.

Theoretically, the long-awaited for-each task should make the run of multiple processes easier. for example, one of the things I often do is run a list of notebooks, each processing a different table, with no dependencies between them. At the moment I use parallel notebooks – https://docs.databricks.com/en/notebooks/notebook-workflows.html#run-multiple-notebooks-concurrently

As you will see later, this use case is not supported yet. But before that, let’s see what we can do with the for-each task.

Read on to see what it currently can do, and what it cannot.

Comments closed

Programmatic Power BI Report Modification via semantic-link-labs

Published 2024-10-02 by Kevin Feasel

Kurt Buhler makes a change:

Whether building reports in Power BI Desktop or in the web browser via the Power BI service, you have limited options to batch or streamline changes. Put another way; it’s tedious and slow to make many small changes to one or more Power BI reports. It’s also easy to make mistakes

When initially designing or building a report, this is not so much of a problem. Unless you’re using a template, you want to control report layout and formatting, yourself. However, certain changes can be little more than a waste of time. Some examples include:

  • Replacing fields when there’s a broken reference due to i.e. renaming a model measure or column.
  • Swapping one measure or column for another in the report
  • Changing visual container styles, like background, border, and shadow/glow.
  • Changing text or text styles across multiple visuals, pages, or reports.
  • Changing chart formatting (like color) or properties (like edit interactions) across multiple visuals, pages, or reports.

Read on to see how you can make some of these changes in Python code using the semantic-link-labs library.

Comments closed

Monitoring for Blocked Processes and Deadlocks with Extended Events

Published 2024-10-02 by Kevin Feasel

Lori Brown’s speaking my language:

Here is a way to set up an extended events session that can be used to collect blocked processes and deadlock records.  I use similar code to collect this type of data each day and have a job that pulls the data into tables which can be used to evaluate the resources that are being blocked and deadlocked on.

When checking for blocked process records, you must set the blocked process threshold in the configuration.  I have mine configured with the threshold set to 30 seconds.  This means that blocked process reports are generated every 30 seconds. 

Click through for that configuration setting, as well as the Extended Events session to do the work, and even code to pull the results into a table. It doesn’t get much simpler than that.

Comments closed

Reviewing the DATE_BUCKET() and DATETRUNC() Commands

Published 2024-10-02 by Kevin Feasel

Chad Callihan checks out a pair of functions new to SQL Server 2022:

If you haven’t experimented much with SQL Server 2022, you may not be familiar with the new DATE_BUCKET and DATETRUNC functions. Both of these functions are useful when it comes to aggregating data. Let’s take a look at each function and walk through a few examples.

The DATE_BUCKET() function takes a bit of time getting used to, especially when you don’t use clean intervals like 1 hour or 1 minute. DATETRUNC(), meanwhile, is something I’ve wanted for quite some time.

Comments closed

Managing Power BI Assets with semantic-link-labs

Published 2024-10-01 by Kevin Feasel

Kurt Buhler takes us through a Python library:

Thus far, the part of Microsoft Fabric that I’ve personally found the most interesting is not Copilot, Direct Lake, or its data warehousing capabilities, but a combination of notebooks and simple file/table storage via Lakehouses. Specifically, the library semantic link and its “expansion pack” semantic-link-labs, spearheaded by Michael Kovalsky. These tools help you build, manage, use, and audit the various items in Fabric from a Python notebook, including Power BI semantic models and reports.

Semantic-link-labs provide a lot of convenient functions that you can use to automate and streamline certain tasks during Power BI development; both of models and reports. For me, I’m particularly interested in the reporting functionalities, because this is where I typically find that I lose the most time, and because there is a drought of tools to address this area.

Read the whole thing.

Comments closed

The Internals of Data Updates in PostgreSQL

Published 2024-10-01 by Kevin Feasel

Cary Huang explains how update operations work:

In previous blogs, we talked about an overview of PostgreSQL’s table access method API here , how sequential scan is handled within this API here, and how data insertion is handled here. Today in this blog, we will look closely into how PostgreSQL handles update. A successful update in PostgreSQL can be viewed as “insert a new record” while “marking the old record as invisible” due to the MVCC technique that PostgreSQL employs. It sounds simple enough, but there are quite a lot of considerations in place to make a successful update. Let’s dive in.

There’s a lot going on behind the scenes, and Cary does a good job of explaining it all.

Comments closed

Techniques for Unpivoting Data in SQL Server

Published 2024-10-01 by Kevin Feasel

Jared Westover performs a technique showdown:

A few weeks ago, I helped someone combine multiple integer columns into a single column. The common term for this process is unpivoting or transposing. The table they worked with had millions of rows, so they needed the most efficient method to accomplish this task. If you search online, you’ll find several suggestions for doing this. But which performs best? Is one easier to maintain than the others?

Click through for a review of three separate techniques: using the UNPIVOT operator, using UNION ALL, and using CROSS APPLY. The dataset was relatively small, but even at that size, CROSS APPLY did a good job. But I won’t spoil too much here.

Comments closed

Query Store Not Capturing Runtime Stats for Unfinished Queries

Published 2024-10-01 by Kevin Feasel

Matt Changchien does a bit of testing:

It’s beneficial to understand how Query Store captures query runtime stats when they are used for performance troubleshooting. According to public documentation: upon the query execution, runtime statistics are sent to Query Store.

I am curious whether Query Store will still capture execution statistics under certain scenarios. Hence, in this blog post, I will be testing the two following scenarios with my Azure SQL Database:

  1. Whether Query Store captures runtime stats for query that has completed within an uncommitted transaction, then the transaction rolls back due to a scaling process.
  2. Whether Query Store captures runtime stats for query that is blocked and does not finish before a disconnect caused by a disruptive event (e.g., a service tier scale-up).

Read on to learn more about these two scenarios. The specific context is Azure SQL Database, though the same rules would apply on-premises as well.

Comments closed

Not all Window Function Calls are Deterministic

Published 2024-10-01 by Kevin Feasel

Chen Hirsh reminds us of an important fact:

One of the scariest things that can happen when you develop SQL code is getting indeterminate results. When you run your query multiple times and each time gets a different value.

How can that happen?

SQL functions or queries can be categorized into two groups – deterministic vs. non-deterministic.

I even have a demo where I show off the non-deterministic nature of ROW_NUMBER() versus, say, RANK() or DENSE_RANK(). But there’s nothing too scary about it: just ensure that your PARTITION BY criteria are guaranteed to be unique and you’re good to go. H/T Madeira Data Solutions blog.

Comments closed