Unexpected Data Refresh Failure and Query Execution from Wrong Snowflake Warehouse in Power BI

[sushovan93]

We have observed an unusual issue with one of our Power BI datasets connected to Snowflake. During scheduled refresh, the dataset intermittently fails with a duplicate key / masked data error. However, upon investigation in Snowflake, we noticed that the query is not being executed from the configured warehouse and service user but instead from a completely different warehouse and user.

This is unexpected because:

• The dataset is configured to run from WHS_XXX_Reporting.

• Query history in Snowflake shows execution from WHS_YYY_ETL.

• The configured Power BI dataset user does not have access to other Snowflake warehouses, so it should not be possible to switch.

• We have not made any changes to dataset configuration, nor did we reassign/re-takeover ownership of the dataset.

• The failure occurred twice randomly, while runs before and after completed successfully.

This raises concerns about whether Power BI service/gateway is somehow routing queries incorrectly or if there is an issue in how the gateway/service identity is handling refresh requests.

Expected Behavior:

• Queries should always execute from the dataset-configured warehouse (WHS_XXX_Reporting) and the correct service user.

• No arbitrary switching to other warehouses (WHS_YYY_ETL) should occur.

We created MS Support ticket but there is no hope as they are not able to find any root cause of this I would appreciate guidance on:

1. If there are known scenarios where refresh might get rerouted to another warehouse.

2. How we can ensure that refreshes always run against the configured warehouse.

[sushovan93]

@v-dineshya and @tayloramy - now I checked the Snowflake logs for the particluar Query tag and and have not seen the issue since 10th Sep. and it stated around 3rd Sep. if I go by time line

 3rd Sep- 1
4th Sep- 1
6th Sep- 1
9th Sep- 2

10th Sep- 2

[tayloramy]

@sushovan93 

Very interesting, I wonder if there was some back end bug that was quietly patched? 

If this helped, consider giving some kudos. If I answered your question or helped solve your problem, mark this post as the solution to help futute forum users find it. 

[v-dineshya]

Hi @sushovan93 ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @tayloramy , Thank you for your prompt responses.

Hi @sushovan93 ,

Please try below things to fix the issue.

1. Please check that each Snowflake connection in the gateway is uniquely named and mapped to only one dataset. Avoid having multiple Snowflake connections in the same gateway unless absolutely necessary.

2. Fix the gateway logging issue to inspect the actual query routing and connection used. Enable verbose logging and check for MashupEngine and SnowflakeConnector logs.

3. Please check that the service user cannot switch roles or access other warehouses. Use session parameters to enforce role and warehouse.

ALTER USER SVC_A SET DEFAULT_ROLE = 'ROLE_A';
ALTER USER SVC_A SET DEFAULT_WAREHOUSE = 'WHS_XXX_Reporting';

4. Use query tags to trace execution. Use Snowflake’s Access History view to correlate user, role, warehouse, and query source.

5. Please try to refresh the dataset manually using Power BI Desktop with the same gateway to see if the issue reproduces.

6. Please clone the dataset and remove all but the problematic table. Test refresh behavior to isolate whether the issue is with the table/query or the dataset configuration.

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[sushovan93]

Hi @v-dineshya 
1. Gateway connection name is unique and those 2 uses seperate warehouse in the connection and only mapped to their related datasets.

2. For the fix of Gateway log I'm still working with MSFT.

3. the role assignment is already there, but I'll dauble check that again and thank you for suggestion on seeting the default warehouse. will try that.

4. the problem is it's happening intermitently. let say we have 24 api trigger refresh per day for the dataset and 1 or 2 of them are behaving like this and that also not in consecutive run. that's where our problem is if it was executing all the runs with the wrong warehouse we can definitely say there is something wrong with either gateway connection or dataset configuration but lets say 10 runs execute with WH_A and SVC_A then 1 in the middle is executing with WH_B and SVC_B then again next 2-3 runs are fine and aging in one of the run it exceutes with WH_B/SCV_B and after that it works fine again.

I'll try to test with that particular table now the in the gateway schedule and see the results.

[v-dineshya]

Hi @sushovan93 ,

Thank you for the update. As you mentioned in your previous response that, you are checking with Microsoft team to fix the Gateway log. please share the support ticket link or Service request number , it will help us to check the current status of the thread. 

Regards,

Dinesh

[sushovan93]

@v-dineshya 

2509040040013333- this one's for the gateway logs.

[v-bmanikante]

Hi @sushovan93 ,

I am also a member of CST team and since you have already submitted the ticket, the support team will handle the issue and resolve it soon.

Thank you for your patience. 

Regards,

B Manikanteswara Reddy

[sushovan93]

We have multiple queries triggred from the report but I have seen issue with one particular table. 

Yes in the Query we are specifying the Wharehouse/Role/ SCHEMA/ Databse everything explicitely. the Connection is going through a gateway, so we have configured the gateway connection with SVC User. bt iterestingly when we went to check in the Snowflake Query history I can see in the Query details it was executed under a diffrerent warehouse and a different service user, as the service user does not have the right permision so we got masked data nd the failure, otherwise there was no way to tell that this happened, also I refrenced the activity ID from the Snowflake Query tag to the gateway activity ID and call it's the same Query that was triggerd from the same report. and too bads for me that I can not see the Query from the gateway logs asI have another issue with gateway log file 😑

[tayloramy]

Hi @sushovan93, 
I am quite confused at this behaviour. 

Let me make sure I understand correctly. 

In Snowflake, you have two warehouses, let's call them WH_A and WH_B. 
You also have two service users, SVC_A and SVC_B. 

In Fabric, you have a data gateway with a snowflake connection set up. THis connection uses WH_A and SVC_A. 

In Fabric, you are running a semantic model refresh using the gateway connection. 

After this refresh runs, in Snowflake, you see that it was actually executed as SVC_B on WH_B. 

THis is very puzzling. 

For SVC_B and WH_B, do you have any connections on the gateway at all that use these credentials and warehouse?

If this helped, consider giving some kudos. If I answered your question or helped solve your problem, mark this post as the solution to help futute forum users find it. 

[sushovan93]

@tayloramy  yes that excatly what is happening.
and also I have connection with WH_B and SVC_B in the same gateway.

However Its very unlikely as the under the dataset I can see only one connection maaping that's for WH_A and aslo the user who configured the dataset does not have access to the other connection at all

[tayloramy]

@sushovan93 

Hmm okay.... So the semantic model has to be for some reason using this other connection sometimes, that is the only way it would be abl eto get the credentials. 

I'm sure you've already done this, but can you tripple check the connection being used? I've never seen a gateway randomly switch the connection like this, it seems very strange. 

Can you also verify the permissions on both connections just to be sure?

This is sounding a lot like a bug. 

If this helped, consider giving some kudos. If I answered your question or helped solve your problem, mark this post as the solution to help futute forum users find it. 

[tayloramy]

@sushovan93 Here's an idea, 

is the SVC_B connection the first one you made on the gateway? 

If a dataset is bound to a gateway without specifying the data source ID, the bind can attach to the first matching data source on that gateway - which might be your WH_B/SVC_B entry. Microsoft calls this out in the REST API (Datasets – Bind To Gateway).

If this helped, consider giving some kudos. If I answered your question or helped solve your problem, mark this post as the solution to help futute forum users find it. 

[tayloramy]

Hi @sushovan93, 

This is very facinating, and if it is behaving the way you describe, should not be possible..

How many Snowflake queries exist in the model? Is it just this one or are there multiple? 

Does the connection specify a warehouse to use, or is it relying on using the default warehouse for the user? 

Is the WHS_YYY_ETL warehouse one that you also have access to? Is there any chance that a refresh ran under the owner's credentials once instead of under the configured credentials? 

Could this possibly be an issue with version 2.0 of the snowflake connector that came out in July? Does that timeline line up with when this started? Can you try adding Implementation="1.0" to the connector and see what the behaviour is?
Source = Snowflake.Databases("contoso.snowflakecomputing.com", "CONTOSO_WH", [Implementation="1.0"])

I'm very curious to see what the problem is here and where exactly this bug is sitting, but if it is behaving the way you describe it is very much a bug, and it seems like a pretty large one at that. 

If this helped, consider giving some kudos. If I answered your question or helped solve your problem, mark this post as the solution to help futute forum users find it.