This query was rejected due to current capacity constraints - use of row_number for identity field

[tjreaume]

In the last week started getting capacity constraint failures on a query that populates a fact table.  Note this query and the exact same  data set ran over and over again WITHOUT issue until very recently.  We have an F64 capacity and the fact table consists of less than 350,000 rows.  

I've managed to determine it is  the use of row_number() to generate an sysid field, as recommended here    https://learn.microsoft.com/en-us/fabric/data-warehouse/generate-unique-identifiers, that is causing the issue.

Something has changed in how Fabric / Fabric capacity manages the row_number() syntax.  

If we can no longer use row_number() without blowing our capacity what other method should be being used instead?

This is just a POC dataset and this is raising concerns on how viable it will be to move to real data scenarios.   

[v-veshwara-msft]

Hi @tjreaume ,
Thanks for engaging with Microsoft Fabric Community,

The error "This query was rejected due to current capacity constraints" has been reported by multiple users recently, and it appears to be a known limitation in Microsoft Fabric. Previously, the function worked without issues, but recent updates may have affected how Fabric manages resource allocation for such queries.

Here is a similar thread:

This query was rejected due to current capacity co... - Microsoft Fabric Community

As a workaround, you may consider alternative approaches like using DENSE_RANK(), NEWID(), or precomputing row numbers in a staging table.

However, if ROW_NUMBER() was integral to your process and is now causing failures, we strongly recommend raising a support ticket with Microsoft to investigate further.

Microsoft Fabric Support and Status | Microsoft Fabric

If you need any assistance further, please let us know.

Best regards,

Vinay.