Press "Enter" to skip to content

Author: Kevin Feasel

Optimizing Multiple Lookup Transformations in SSIS

Published 2025-06-30 by Kevin Feasel

Andy Brownsword doesn’t want to keep hitting the database:

Lookup transformations provide us a way to access related values from another source, such as retrieving surrogate keys in data warehousing. When we need multiple lookups to the same reference data we can improve performance through the use of a Cache.

If we consider data warehousing, a prime example of this would be an order table which has values for Order Date, Dispatch Date, Delivery Date, etc. All of these would require a lookup to a calendar dimension.

This is a perfect use case for a cache.

Read on to see how the cache connector works.

Leave a Comment

PostgreSQL and Covering Indexes

Published 2025-06-30 by Kevin Feasel

Kendra Little does a bit of index management:

Dear Postgres, Why won’t you use my covering index?

Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.

Why in the world would it do that?

Read on to learn why.

Leave a Comment

Upsert Patterns and Duplicate Keys in T-SQL

Published 2025-06-30 by Kevin Feasel

Ajay Dwivedi runs into an error:

I work with developers quite a lot. There are scenarios where data is received from various sources in an application, and asynchronously pushed to database in multiple sessions/connections.

There are situations where concurrency and transaction speed is high enough to cause below UPSERT code blocks to fail with error message like Cannot insert duplicate key row in object dbo.person with unique index ‘pk_person’

Click through for one way to do things. I will note that Ajay has concerns about the MERGE operator, but Hugo Kornelis took a deep dive into all of the known problems in MERGE and found that most of them were fixed. Hugo’s post does make it clear when using MERGE is a bad idea, as there are still some situations in which it won’t work effectively, but for something like this, it would be fine.

I’ll say that I’m generally not a fan of app locking. There are specific circumstances in which it’s the best answer, but those are rare. Here, I’d rather just use a ROWLOCK table hint or change the serialization level.

Leave a Comment

Changes to Power BI’s Publish to Web

Published 2025-06-30 by Kevin Feasel

Boniface Muchendu looks at some changes:

Power BI includes a powerful feature called Publish to Web, which allows users to share interactive reports publicly without requiring viewers to sign in. While this tool simplifies access, it can also create security risks if misused. In this guide, you’ll learn what “Publish to Web” does, how Microsoft updated it for better governance, and how to manage access responsibly.

Click through to see what’s new.

Leave a Comment

The Costs of Implicit Conversion

Published 2025-06-27 by Kevin Feasel

Andy Brownsword changes things around:

Implicit conversion happens in SQL Server when the engine detects a mismatch in data types and automatically converts from one type to another. This can be helpful as it makes different types interchangeable and is generally transparent to the client, but it can come with issues.

Usually the downside from implicit conversion is seen through performance impact. But we’ve got something a little different today – an inconsistent result set.

Read on for Andy’s example.

Leave a Comment

Customer Managed Keys in OneLake

Published 2025-06-27 by Kevin Feasel

Harmeet Gill shows us how we can bring our own keys to data in OneLake:

One of the highly requested features in Microsoft Fabric is now available: the ability to encrypt data in OneLake using your own keys. As organizations face growing data volumes and tighter regulatory expectations, Customer-Managed Keys (CMK) offer a powerful way to enforce enterprise-grade security and ensure strict ownership of encryption keys and access.

With Microsoft’s OneLake, we’ve built a unified data lake that’s open, secure, and ready for enterprise scale. Now, with support for CMK, we’re giving customers the power to take encryption into their own hands.

Read on to learn more about Microsoft’s default for data encryption, and how you can use your own keys to encrypt the data.

Leave a Comment

Microsoft Fabric Extension for VS Code Update

Published 2025-06-27 by Kevin Feasel

Sunitha Muthukrishna has an update:

Microsoft Fabric Extension for VS Code enables users to manage Fabric items efficiently from within VS Code. We are announcing two new features for Microsoft Fabric Extension for VS Code that allow you to manage Fabric items directly within your workspace. These enhancements are based on customer feedback, and we welcome further input to improve this product.

Click through to see what’s new in the product.

Leave a Comment