Press "Enter" to skip to content

Day: November 10, 2026

Building a Type 2 SCD with dbt Snapshot

Published 2026-11-10 by Kevin Feasel

Koen Verbeeck creates a dimension:

An SCD Type 2 dimension preserves history by inserting a new record every time a tracked column changes, rather than overwriting the old value. Building one by hand usually means writing custom merge logic, but dbt’s built-in dbt snapshot feature can do this for you without any extra code.

This article picks up where my previous guide on loading a Type 1 slowly changing dimension with macros left off. This time, we’ll use dbt snapshots to build a full SCD Type 2 customer dimension in a Microsoft Fabric warehouse: from the underlying view, through the snapshot configuration, to the final dimension model.

Click through to see how.

Leave a Comment

The IsAvailableInMDX Flag in Power BI Semantic Models

Published 2026-11-10 by Kevin Feasel

Phil Seamark explains:

There is a property in Power BI semantic models you can set per column called IsAvailableInMDX. When most people discuss this property, the first question often asked is, “Are you using Excel to query the semantic model?”. If the answer is no, the usual follow up is “turn it off everywhere, you will save memory and your refresh will be faster”.

For Import models, turning it off can save memory by removing a supporting structure from a column. But a DAX query may have been making good use of that structure, particularly when filtering the column. Direct Lake is different: it uses its column dictionary for these DAX optimisations, but the setting still matters for Excel field availability.

Read on to learn whether your environment might benefit from this, as well as other considerations.

Leave a Comment

Reviewing the Always Encrypted Paper

Published 2026-11-10 by Kevin Feasel

Anna Hoffman continues a series on reviewing important academic papers in SQL Server’s history:

Encryption at rest solves one threat. It stops someone who walks off with the disk. It does nothing about someone with administrative access to the running server, because the database decrypts data into memory to do anything useful with it.

For a lot of workloads that gap doesn’t matter. For health records, financial data, or anything where the customer’s threat model includes the cloud operator, it matters a great deal.

Azure SQL Database Always Encrypted (SIGMOD 2020) describes how SQL Server runs real queries, including range comparisons and LIKE patterns, against data it never holds the keys to.

Read on for Anna’s thoughts about the paper, its claims at the time, and how Always Encrypted has changed (for the better) since the original paper.

Leave a Comment

Standing up Active Directory and a Windows Cluster

Published 2026-11-10 by Kevin Feasel

Dualcore DBA is doing some sysadmin work. First up is setting up a domain controller:

In this post, we’ll walk through the process of how I set up an Active Directory (AD) in my home lab. I know, this blog is normally database focused, so why are we looking at Active Directory? Well, yes, it isn’t strictly database related. However, where there is an enterprise SQL Server estate, there is normally Active Directory. In the next few posts, we’ll be looking at Availability Groups, which, whilst configurable without AD, generally are run in AD environments. Also, it isn’t really a great deal of work to set up in the home lab. It takes longer to install the Windows server than to actually set up AD, so it’s not really an issue to set it up to have a more realistic lab environment.

I am using Windows Server 2022 and have set up a specific VM for this purpose which is fairly modest with 2 CPU cores and 4GB of memory.

After that, try on creating a cluster without a domain setup:

This post documents the process of setting up a non-domain Windows Server Failover Cluster, officially known as a Workgroup Cluster. The process is similar to Setting Up a Windows Cluster, however there is a slight difference in this scenario which is that the nodes in the cluster are not part of an Active Directory domain. This process can be used to set up a SQL Server Availability Group where the nodes are not in an Active Directory environment.

And creating a cluster inside of a domain:

In this post, we’ll walk through setting up a Windows Server Failover Cluster (WSFC). This is a requirement of SQL Server Availability Groups, something which we’ll be using on this blog and so I will be setting one up that can be used in the home lab I use to write my blogs. A Windows Failover Cluster is a group of independent servers that work together, continuously monitoring each other, so that if one server fails or goes offline, another can automatically take over its workload with minimal interruption to users. WSFC handles the “which node should be primary and what do we do if it isn’t” on behalf of a SQL Server Availability Group.

Leave a Comment