Press "Enter" to skip to content

Curated SQL Posts

Analyzing Delta Table Measures in Microsoft Fabric

Published 2024-11-11 by Kevin Feasel

Sandeep Pawar has a script for us:

I have been sitting on this code for a long time. I shared the first version in one of my blogs on Direct Lake last year. I have been making updates to it since then as needed. I waited for the lakehouse schema to become available and then forgot to blog about it. Yesterday, someone reached out asking if the above could be used for warehouse delta tables in Fabric, so here you go. It’s 250+ lines so let me just explain what’s going on here:

Read on for the explanation, the script itself, a demonstration, and several additional notes.

Comments closed

Function Volatility in PL/pgSQL

Published 2024-11-11 by Kevin Feasel

Deepak Mahto shares some advice:

The PL/pgSQL language, available as a default extension in PostgreSQL, provides powerful tools and flexibility for application developers to build complex, enterprise-scale functionality within the database. Through PL/pgSQL’s functions and procedures, developers can choose different volatility categories—IMMUTABLE, STABLE, or VOLATILE—that offer varying performance benefits, especially in terms of result caching and data state awareness. For a deeper dive, refer to the official documentation. Choosing the appropriate volatility for a function is crucial; as they say, “With great power comes great responsibility.“

Read on to learn more. It seems like it would be quite easy to mess this up.

Comments closed

Cmdlets vs Functions in Powershell

Published 2024-11-11 by Kevin Feasel

Adam Bertram differentiates the two:

Whenever you execute or run anything in PowerShell, you’re running a “command.” A command is an informal term for executable code, which can be a built-in PowerShell cmdlet, a custom function, or even an object method.

Cmdlets are compiled executable code provided by Microsoft, created in a language like C#, compiled, and added to a PowerShell module by software developers. Functions, on the other hand, are created directly in PowerShell by users. Although functions and cmdlets are technically different, they can be thought of as similar when you’re starting out.

Read on for more information, including a demo. I normally call them all cmdlets, but apparently I’ve been doing it wrong all this time.

Comments closed

Continuous Deployment in Practice

Published 2024-11-11 by Kevin Feasel

Bravin Wasike completes a series on continuous integration and continuous deployment:

Continuous Deployment (CD) is an advanced CI/CD practice that takes automation to the next level. It automatically deploys every code change that passes the automated testing phase to production. Unlike Continuous Delivery, which requires manual approval before deploying to production, Continuous Deployment eliminates manual intervention. This allows for seamless and frequent software updates.

Click through for the full article.

Comments closed

Notes on Temporal Tables

Published 2024-11-11 by Kevin Feasel

Teo Lachev describes some benefits and properties of temporal tables:

At the same time, temporal tables are somewhat more difficult to work with. For example, you must disable system versioning before you alter the table. Here is the recommended approach for altering the schema by the documentation:

I think the main drawback to using temporal tables in this way is that we can only use system time as the separator, unless you manually load data into the history table. It’d be great to have a user time capability to open up temporal tables to these sort of warehousing scenarios, such as using them for type-2 slowly-changing dimensions.

Comments closed

Scenarios Leading to Autovacuum in Postgres

Published 2024-11-11 by Kevin Feasel

Semab Tariq covers autovacuum:

However, one side effect of MVCC is the creation of dead tuples—old versions of data rows that are no longer needed but still occupy space. 

Dead tuples also lead to a phenomenon known as table bloat, which refers to the excessive unused space in a table caused by dead tuples that haven’t been cleaned up, resulting in inefficient storage and reduced performance

To address the issues of dead tuples and table bloat, autovacuum comes into play. It’s an automatic process designed to clean up these dead tuples and maintain optimal database performance.

Read on for a list of scenarios that can trigger autovacuum.

Comments closed

Apache Kafka 3.9 Now Available

Published 2024-11-08 by Kevin Feasel

Colin McCabe announces Apache Kafka 3.9:

We are proud to announce the release of Apache Kafka 3.9.0. This is a major release, the final one in the 3.x line. This will also be the final major release to feature the deprecated Apache ZooKeeper® mode. Starting in 4.0 and later, Kafka will always run without ZooKeeper.

That’s a pretty big change, but there are also quite a few other significant changes here to check out.

Comments closed

Configuring Azure Database Watcher

Published 2024-11-08 by Kevin Feasel

Rod Edwards configures Azure Database Watcher to watch databases in Azure:

First off, at the time of writing, this is still in Preview, and is only for Azure SQL PaaS offerings, namely Azure SQL DB and SQL Managed Instance, so if you’re out of luck if you’re using SQL on VM. Expect this to be added at some point in future, its number 2 on the published roadmap.

Preview or GA…the long and short of it is that it allows collection of performance AND config data into a central datastore for ALL of your SQL MI and Azure DB estate. With all of the data in one place, then dashboards are connected to here for easier estate-wide visualisations.

Read on for a step-by-step guide on configuring it. But also pay attention to Rod’s note near the end that troubleshooting setup is a pain—there aren’t many useful logs that show exactly why it isn’t working.

Comments closed

Reviewing Kusto Query History in Microsoft Fabric

Published 2024-11-08 by Kevin Feasel

Dennes Torres looks over prior commands:

We can consume a Kusto database in Fabric from many different places: Notebooks, semantic models, real time dashboards and more. Kusto register all queries sent by the consumers in the query history.

Sometimes, either for logging purpose or to analyze and fix some bug, we need to identify the queries the database is receiving and executing.

Read on to see what you can do with query history in Kusto.

Comments closed