Press "Enter" to skip to content

Curated SQL Posts

Lessons Learned from Replicating BigQuery to Microsoft Fabric

Teo Lachev shares some knowledge:

A recent engagement required replicating some DW tables from Google BigQuery to a Fabric Lakehouse. We considered the Fabric mirroring feature (back then in private preview, now publicly available) and learned some lessons along the way:

1. 400 Error during replication configuration – Caused by attempting to use a read-only GBQ dataset that is linked to another GBQ dataset but the link was broken.

Read on for additional tips, including a major one around permissions.

Leave a Comment

Postgres Migration via Logical Replication

Elizabeth Christensen makes a move:

Moving a Postgres database isn’t a small task. Typically for Postgres users this is one of the biggest projects you’ll undertake. If you’re migrating for a new Postgres major version or moving to an entirely new platform or host, you have a couple options:

Read on for those three options, when logical replication can possibly work, and the process for upgrade. It’s definitely a bit more fiddly than other options, but it’s hard to beat with respect to downtime.

Leave a Comment

Accounting for Index Usage over Availability Groups

Aaron Bertrand wants a cross-replica accounting system:

As a part of optimizing performance, I evaluate index usage across many instances and databases. I often find that some indexes aren’t used much or, at least on first glance, appear to go unused. Since we use availability groups (AG), different workloads run against replicas in different roles. All writes happen on the primary, obviously. However, some queries only happen on read-only secondaries (either because read-only routing is in use, or because some processes are manually directed at specific secondaries, or both). Unfortunately, index usage is not rolled up anywhere across all replicas. This means that looking at the primary alone gives an incomplete picture. How do I make sure I account for index activity everywhere, not just on the primary?

This is part one of a series but already has enough to get us started.

Leave a Comment

Updates to Fabric Data Factory

Abhishek Narain has a list of updates:

Workspace Private Link Support for Data Factory (Preview): Microsoft Fabric enables secure data integration through Private Link support in Dataflows Gen2, Pipelines, and Copy jobs. This ensures that inbound data access remains isolated and compliant within protected workspaces. By leveraging VNet data gateways, organizations can securely connect to data sources across Private Link-enabled environments—eliminating exposure to public networks and reinforcing enterprise-grade security for sensitive data operations.

Most of these are security-related updates, with a mixture of things now GA, things currently in preview, and a pair of items coming soon.

Leave a Comment

Error Handling in PySpark Jobs

Ram Ghadiyaram adds some error handling logic:

In PySpark, processing massive datasets across distributed clusters is powerful but comes with challenges. A single bad record, missing file, or network glitch can crash an entire job, wasting compute resources and leaving you with stack traces that have many lines. 

Spark’s lazy evaluation, where transformations don’t execute until an action is triggered, makes errors harder to catch early, and debugging them can feel like very, very difficult.

Read on for five patterns that can help with error handling in PySpark.

Leave a Comment

Performance of User-Defined Functions in Fabric Warehouses

Jared Westover shares some findings:

In Part One, we saw that simple scalar user-defined functions (UDFs) perform as well as inline code in a Fabric warehouse. But with a more complex UDF, does performance change? If it drops, is the code-reuse convenience worth the price?

I’m surprised that the performance profile was so good. I had assumed it would perform like T-SQL user-defined functions—namely, worse in general.

Leave a Comment

Building an UPDATE … LIMIT in PostgreSQL

Laurenz Albe doesn’t have MySQL envy:

If you are reading this hoping that PostgreSQL finally got UPDATE ... LIMIT like MySQL, I have to disappoint you. The LIMIT clause is not yet supported for DML statements in PostgreSQL. If you want to UPDATE only a limited number of rows, you have to use workarounds. This article will describe how to do this and how to avoid the pitfalls and race condition you may encounter. Note that most of the following also applies to DELETE ... LIMIT!

Click through for what you can do in PostgreSQL instead. In T-SQL, we can use UPDATE TOP(n).

Leave a Comment

RCSI Scenarios

Haripriya Naidu digs into a few scenarios:

When RCSI is enabled, I’d like to discuss three scenarios:

  1. UPDATE is in progress, and SELECT starts to run.
    Where does SELECT read from?
  2. SELECT runs, and there are no concurrent operations or uncommitted transactions. Where does SELECT read from?
  3. SELECT is running, and now an UPDATE starts to run concurrently. What happens to SELECT that is in progress? What about the UPDATE that started? Does it wait for SELECT to finish?

Click through to see what happens during each of these scenarios.

Leave a Comment

Creating Database Snapshots with sp_snapshot

David Fowler announces a tool update:

Presenting you with an updated version of our sp_snapshot procedure, allowing you to easily create database snapshots.

This new version fixes a bug that we’ve found in version 2 where snapshots will fail for databases with multiple data files.

We’ve also added the @STMTOnly parameter, allowing you to generate the scripts for creating the required snapshots without actually doing so.

Click through for more information, as well as where you can go to download the script.

Leave a Comment