Azure Parallel Data Warehouse (PDW) Errors #1: cancelled transactions

Microsoft’s Azure data warehouse is a cloud hosted PaaS offering. Which implies compute and storage resources are managed by Microsoft / Azure fabric.

Scheduled and unscheduled maintenance activities occur which are supposed to be transparent, i.e. should not impact the platform. This is a false assumption.

On some occasions maintenance will impact the platform. For example back-end “node movements”, rotating compute / storage resources will cause errors on one of the compute / data nodes to bubble up into a transaction causing the transaction to enter a cancelled state.

Protect your transactions

The best way to protect a transaction when dealing with Azure DW, is to integrate retry logic into code.

$retry = 5
while ($retry -gt 0)

{
    try
    {
        # Connect here and perform SQL / ETL operation ...

        # Finally end the loop
        $retry = 0
    }
    catch
    {
        $retry =- 1
        if ($retry -gt 0)
        {
            Start-Sleep -Seconds 30
        }
    }
}