Category: Data Modeling

Table Distributions in Azure Synapse Analytics Dedicated SQL Pools

Published 2026-10-30 by Kevin Feasel

When designing tables in a Synapse Dedicated SQL Pool, I initially focussed on the ‘SQL’ and not so much the ‘pool’. How tables are stored is fundamentally different to other Azure SQL offerings, so it’s easy to get tripped up and impact efficiency and performance.

Let’s see how the SQL pools work differently and how to tailor your tables for optimal performance.

Click through for an explanation of the three distribution strategies and when you might use each. As a quick note, the REPLICATED option sounds like it’d be really great, but in practice, there are some major drawbacks as table sizes get larger. That’s a big part of why we save them for small, rarely-changing dimensions.

Comparing JSONB to hstore in Postgres

Published 2026-10-19 by Kevin Feasel

Hubert Lubaczewski re-creates an issue:

Some time ago I read this blogpost.

They wrote about logging changes to table using a trigger, but one thing that caught my attention was:

Storing only the changed columns made the log five times smaller and the trigger slower.

Figured I can try it myself…

Unfortunately they didn’t provide schema of table, nor test updates. So, let’s make test case:

Click through for the demo, and how a change from JSONB to hstore for changed data improved performance a fair bit.

Near-Zero Downtime Database Schema Changes in MySQL

Published 2026-10-15 by Kevin Feasel

Lukas Vileikis makes some changes:

Every growing application eventually faces the same challenge: your database schema needs to change, but your users can’t afford the downtime. Whether you’re adding a column, creating an index, or restructuring a table with billions of rows, schema changes can lock tables, strain server resources, and disrupt live traffic if handled carelessly.

The good news is that MySQL offers several ways to make these changes while keeping your database online and responsive. This guide breaks down the main causes of schema-change downtime and walks through the tools and techniques — from built-in online DDL to third-party utilities like Percona Toolkit and gh-ost — that let you evolve your database safely without taking your application offline.

Click through for some thoughts and recommendations.

An Introduction to Data Dict

Published 2026-09-28 by Kevin Feasel

Malte Grosser describes a new project:

A data dictionary can record what each row represents, how values were measured and how tables fit together. Data Dict provides a format for writing this down alongside rules the data should satisfy. Both live in one file, data-dict.yaml. Its command-line tool checks the rules against the data and turns the dictionary into readable documentation.

The open-source project was initiated by Hadley Wickham and is supported by Posit. The format is designed for teams working across R, Python and SQL.

The post combines a high-level description of the Data Dict project, as well as one of the examples in frog jumping. H/T R-Bloggers.

Comments closed

Digging into Entity-Attribute-Value Tables

Published 2026-09-21 by Kevin Feasel

Greg Low has started a series on entity-attribute-value tables. The first post covers what they are:

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

The second post covers pros and cons:

In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.

Greg is very much against EAV, and I agree with this. I do like Greg’s alternative of using something like JSON, with the proviso that the database simply become a whole-record storage and retrieval engine rather than trying to strip out and splice in new JSON via T-SQL. Otherwise, spend the time on proper data modeling and take advantage of what the platform can do for you.

1 Comment

Making a Minimally Intrusive Schema Change

Published 2026-09-14 by Kevin Feasel

Jerry Nixon performs a change:

Schema change is inevitable. We do not get everything right the first time, and even when we do, the world around the database keeps changing. Requirements mature, products evolve, and assumptions that made sense years ago eventually stop matching reality.

Businesses change too. We acquire companies, merge with other customers and systems, enter new markets, and adapt to new opportunities. Sometimes a schema has to change because the original design was wrong. More often, it changes because the business is no longer the same business that existed when the schema was designed.

That is normal. The challenge is not avoiding schema change. The challenge is making those changes without interrupting the applications and users that depend on the database.

Click through for an example of a schema migration. I disagree with Jerry about doing the work in “the application,” mostly because it’s rarely one application controlling a database. If it is, and if there are no automated jobs, ad hoc PowerShell scripts, secondary applications, or other processes in place that could write data to the same database, then fine. But I typically find that to be less common than the alternative.

Comments closed

What “Clean Data” Means in Microsoft Fabric

Published 2026-09-11 by Kevin Feasel

Christian Henrik Reich lays it out:

You might have heard countless times that AI needs high-quality and clean data to work properly. However, this is often not accompanied by an explanation of what it actually means. In this post, I will explain how you can achieve this in Microsoft Fabric.

I’ll explain the foundation for how you can prepare your data so your solution can answer business questions, either through reporting or AI.

Clean data alone is not enough. Data also needs to be structured, understandable, and accessible in a way that allows your solutions to answer business questions reliably.

The fancy buzzword that describes a lot of what Christian covers is “ontology” but I appreciate this more detailed description versus relying on a buzzword as a crutch.

Comments closed

Implementing SCD Type 2 in Microsoft Fabric

Published 2026-09-08 by Kevin Feasel

Nikola Ilic reminds us that the Kimball model is alive and well:

Slowly Changing Dimensions (SCD) are one of the fundamental concepts in dimensional modeling. If you are not sure what dimensional modeling is, I suggest you first check the series of articles I wrote on data modeling some time ago.

And, when it comes to SCD in particular, SCD Type 2 is rightly considered “the queen” of the SCDs for analytical workloads. Without going into details (since the main goal of this article is to show you HOW to implement the SCD Type 2 in Microsoft Fabric), I’ll just briefly introduce the general concept behind the SCD Type 2.

Click through for a primer and examples in the warehouse and lakehouse.

Comments closed

Detecting Schema and Data Drift via SSDT

Published 2026-07-16 by Kevin Feasel

Andy Brownsword goes back to SQL Server Data Tools:

Schema drift is an inevitable part of environments where database changes are applied manually. Sometimes it’s dev-ing in production, other times it’s lax change control. Either way you’ve got a problem, and SSDT comparisons may be the solution.

In this post I want to look at how using the Schema Compare and Data Compare features of SSDT can compare different environments to detect movements in schema and core data. The key points are consistency and specificity

The engine that the Visual Studio extension uses for schema and data comparisons is pretty solid, though my recollection was that it was difficult to script these comparisons or make them work across a number of databases that should be equivalent. Back in the day, we ended up purchasing Redgate tooling for that reason, because it had an API for its schema and data comparison features. But if you’re doing a one-off comparison, the free version built into Visual Studio is pretty good.

Comments closed

Building a Type-2 Slowly Changing Dimension

Published 2026-06-12 by Kevin Feasel

Kristyna Ferris builds a dimension:

This is a blog that I am writing for future me and hopefully it’ll help a few of you save some time too! It’s not often that I get to build out a data warehouse from scratch, but when I do, I want to make sure I do it well with best practices in place. Because this is not something I do a lot of, I frequently forget lessons I’ve learned and have to go back and drop tables to recreate them in the best way before it’s too late. One table type that is vital to do right the first time is a Slowly Changing Dimension Type 2 (SCD2 for short).

Click through for an explanation, as well as example scripts for both SQL Server-adjacent products and the Microsoft Fabric warehouse.

Comments closed