Press "Enter" to skip to content

Category: Data Modeling

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.

Leave a Comment

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.

Leave a Comment

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.

Leave a Comment

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

Building a Type-6 Slowly Changing Dimension

Published 2026-06-11 by Kevin Feasel

Dinesh Asanka creates a dimenson:

In a data warehouse, one important concept is to retain historical data. This data is typically not available in operational systems. One approach in data warehouses is the use of Slowly Changing Dimensions (SCDs). What are the SCD options and are there any new approaches?

Click through for a quick depiction of Types 0 through 3, and then where 6 fits into the mix. I’m not 100% sure I’ve ever actually used a Type-6 slowly changing dimension in a production environment, though there are specific circumstances in which one could be quite useful.

Comments closed

Defining a Data Contract

Published 2026-04-10 by Kevin Feasel

Buck Woody becomes accountable:

A businessperson pulls a report from a data warehouse, runs the same query they’ve used for two years, and gets a number that doesn’t match what the finance team presented at yesterday’s board meeting. Nobody changed the report. Nobody changed the dashboard. But somewhere upstream, an engineering team renamed a field, shifted a column type, or quietly altered the logic in a pipeline, and nobody thought to mention it because there was no mechanism to mention it.

While we think of this as an engineering failure, it’s more of an implied contract failure. More precisely, it’s the absence of a formal contract. Data contracts are one of the most practical tools a data organization can adopt, and one of the most underused. The idea is not complicated: a data contract is a formal, enforceable agreement between the team that produces data and the team that consumes it. It defines what the data looks like, what quality standards it must meet, who owns it, and what happens when something changes. Think of it as the API layer for your data, the same guarantee a software engineer expects from a well-documented endpoint, applied to the datasets and pipelines your business depends on. This post is about why that matters at the CDO level and how to get them put in place.

Click through to learn more about what data contracts are and why they are useful. This post stays at the architectural level rather than the practitioner level, but lays out why it’s important to think about these sorts of things.

Comments closed

An Overview of Fabric IQ

Published 2025-12-02 by Kevin Feasel

Brian Bonk talks ontologies:

If you followed along with the announcements from Microsoft Ignite, you might have stumbled upon the new Fabric IQ service.

For many people, this new service can seem a bit strange to see the point in, so in this blogpost I will try to help you understand the usage and business value of the new service.

Ontologies aren’t new—it’s mostly a metadata management exercise—but there are several companies (like Palantir) pushing this hard in their tools, and Microsoft is working that market segment. But instead of using all of this metadata management for data quality or master data management reasons, it’s for feeding into language models.

Comments closed