Press "Enter" to skip to content

Day: November 11, 2024

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