Press "Enter" to skip to content

Category: Architecture

Building a Type 2 SCD with dbt Snapshot

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

Azure SQL DB Hyperscale versus Distributed SQL Solutions

Anna Hoffman clarifies:

In my last post, I broke down the Socrates paper, the research that became Azure SQL Database Hyperscale. Writing it made me want to tackle a question I think trips a lot of people up when they’re choosing a database: is Azure SQL Database Hyperscale “distributed SQL”? 

It’s a fair question. “Distributed SQL” has become shorthand for “the relational database that scales in the cloud,” and anything outside that bucket tends to get filed under “the relational one my company already has.” I’d rather not leave that to assumption, so here’s how I think about it. 

Read on for Anna’s take.

Leave a Comment

Load Testing Azure SQL GP and BC

Reitse Eskens continues a series on load testing. First up is Azure SQL Database General Purpose edition:

My previous post on load testing Azure SQL covered the Premium Edition; today I’m moving to the General Purpose Tier.

The GP tier comes in two flavours: regular and serverless. Serverless has only one hardware tier: Gen5. With provisioned, you can also select memory-optimised or confidential compute. In earlier tests, I never found significant differences between serverless and provisioned, so I skipped testing both SKUs. Given the nature of these tests, going memory-optimised didn’t seem to make much sense either. Also, I’ve never seen any other deployment than the regular Gen5 General Purpose database.

And then there’s Business Critical:

My previous post on load testing Azure SQL covered the General Purpose Edition, let’s move it up to the Business Critical Tier

The Business Critical tier comes in two flavours: General and confidential compute. For my testing, I’ve stuck with the General SKU. I haven’t seen confidential compute with customers, and it has very specific use cases: you want to protect your threads on the processor itself. A lot happens behind the scenes to make that work, and it costs more. There are scenarios where this makes sense, but not in my work. For example, using 8 cores for regular business-critical workloads costs about 3,650 euros each month. Confidential Compute will cost about 6,000 euros each month. So yeah, you need a use case for that.

Click through for results on each.

Leave a Comment

Tips for Planning a Microsoft Fabric Solution

Paul Turley shares some tips around the various Microsoft Fabric workloads:

Fabric hands you an entire toolbox of analytics workloads. Using all of them isn’t the goal — using the right ones is.

Resist the shiny-object trap. Fabric packages Power BI, Data Factory, Data Engineering, Data Warehouse, Data Science, and Real-Time Intelligence into one platform, and it’s tempting to adopt all of them at once. Map each workload to a business problem you already have, and bring in only the pieces that solve something real today. Every workload you adopt without a real use case behind it is one more thing your team has to learn, maintain, and eventually explain to an auditor.

Click through for a half-dozen such tips.

Comments closed

Where the Business Logic Lives in Power BI

Teo Lachev answers a question:

A current engagement calls for migrating a rather elaborate financial dashboard from Qlik to Power BI. Just when we think there is light at the end of the tunnel, another business rule is unearthed in Qlik that we need to convert. These rules range from business calculations and dummy rows in dimension tables to allocation overrides.

This brings us to an important question: Where should all this business logic live?

Click through for Teo’s answer to this question.

Comments closed

Things to Note for a SQL Server to Postgres Migration

Amy Abel has a list:

This week I went back through my PostgreSQL notes and listed what is different between SQL Server and Postgres. These are the things I would pay close attention to when doing a migration.

Most migration issues are not caused by the data. They come from assuming Postgres works the same way as SQL Server.

Click through for the list. Some of these things are fairly straightforward, but others can be a pretty big deal and necessitate significant rewrites of existing code bases.

Comments closed

Choosing a Path for Data Warehouse Migrations

James Serra offers up some alternatives:

If you are migrating a data warehouse, there is a question worth asking before you start: should you recreate the existing solution on the new platform, or use the move to make it better? This applies whether you are moving from an on-premises Netezza system to Microsoft Fabric or from Azure Synapse Analytics to Fabric. My preference is to modernize during the migration when the business case and timeline support it. Even a conservative migration takes substantial work, so it is worth considering which additional improvements would make that investment more valuable for years to come.

Click through for pros and cons on four approaches, as well as James’s recommendations.

Comments closed

Choosing a Chunking Strategy for RAG Applications

Bala Priya C puts together a decision tree:

In this article, you will learn how to choose the right chunking strategy for a retrieval-augmented generation system by working through a structured decision tree tied to the properties of your documents and retrieval task.

Topics we will cover include:

  • What each major chunking strategy assumes about your documents, and when those assumptions hold.
  • A six-question decision tree that maps concrete document and retrieval conditions to specific chunking strategies.
  • The failure signals that indicate a chosen strategy is not working, and the fixes that tend to resolve them.

Click through for the list of strategies and when to include each.

Comments closed

Interpreting the Azure Landing Zone as a DBA

Jordan Boich reviews an architecture:

VNet, subnet, NSG and UDR are four key acronyms that exist between me and my DR replica. The first time I started peering into the network world, trying to figure out the nuance how Azure networking impacts SQL Server was a confusing experience. Honestly, I didn’t know what the heck I was looking at. I found through this project, however, that success lies in the foundation. And, setting up that foundation makes all of the downstream processes much easier. This post is going to cover setting up that foundation in Azure. The empty network will harness all of our future objects.

Read on for an extended analogy that helps explain the key networking concepts in Azure.

Comments closed

Stretching an Availability Group to Azure

Jordan Boich begins a series:

Every IT person has lived this outage: the app is down, the database team says the database is fine, the network team says the network is fine, and infrastructure team says the DCs are fine. Everyone is right, and the app is still down. The problem is in the seam between us. My goal is to bridge that gap. Not only for myself, but to show others out there that from the DBA’s chair, the network, infrastructure, and the database don’t have to be such completely separate knowledge domains where one is a black box to the other.

To blame the network, you must understand the network.

Okay, that’s not true at all. But still, it’s a good idea to know this stuff.

Comments closed