Snowflake Tables Not Merging in Service

[etane]

Hello.

The company I work for recently started a SaaS subscription.  This vendor provided us with a Snowflake connection.  And, I am using this connection to pull tables into PBI Service through a gateway our IT set up.  I am able to pull the tables just fine.  However, I can't merge or append tables.  I get this error:

Error: On-Prem execution not supported for entity... since it refers to another entity

I get the same error when I try to merge tables when I tested Pro license.  But, I am on F4 now, and I haven't had this error message since.

I tested using PBI desktop.  After merging Snowflake tables in PBI desktop, I published that to service, and I get this message:

Data source error: Expression.Error: ADBC: [Snowflake] 390422 (08004): Incoming request with IP/Token 40.82.254.33 is not allowed to access Snowflake.

Since our vendor's IT has to whitelist IPs to allow access to their Snowflake, I asked the vendor to whitelist this IP, and the published PBI app does refresh in service.  But, the tables loading directly from Snowflake into Service still doesn't refresh.  

What do I need to do to get Snowflake's merged tables to refresh?  

Assuming our vendor needs to unblock more IPs to access their Snowflake connection to get merged Snowflake tables to refresh, I got a file that has Azure IPs from MSFT customer service:

Azure IPs

According to our IT the file doesn't allow for a clean way to whitelist Azure IPs since it's a bunch of ranges rather than a way to dynamically whitelist.  Currently, they are not sure how to implement this.

So, another question is, is there a way to whitelist Azure IPs dynamically? 

I wish to test this because after our vendor whitelisted the 40.82.254.33 IP, the published PBI file refreshed.  So, my assumption is MSFT and Snowflake has to talk in order for transformation to take place.  And, table transformation seems to bypass our company's gateway hence needing another method to whitelist MSFT IPs dynamically. 

Just an additional datapoint, I asked our IT to pull the Snowflake tables into our on prem dhw.  And, I used Service's SQL Server connection to pull the Snowflake tables into Service.  I merged two tables together.  Tried refreshing but it gives me the same error code:

Error: On-Prem execution not supported for entity... since it refers to another entity

So, this rules out that it's a connector issue.  And, the problem seem to be source (Snowflake) specific.

[v-pnaroju-msft]

Hi etane,

Thank you for the update and for sharing the workaround to resolve the issue. This will certainly benefit other community members who may encounter a similar situation.

Kindly continue to use the Microsoft Fabric Community forum for any further queries.

Thank you.

[v-pnaroju-msft]

Hi etane,

Thank you for the update. As a ticket has already been created and you are in contact with the support team, please keep us informed of any developments so that other community members with similar queries may benefit.

Kindly continue to use the Microsoft Fabric Community forum for any further queries.

Thank you.

[etane]

We found a work around to my situation where I can't load both Snowflake and QAD data into a single dataflow.  I first loaded QAD data into a separate dataflow.  Then, I load the QAD dataflow into the dataflow that already has Snowflake data.  Then, I can merge between Snowflake and QAD tables.

I call it a work around because there's no way to repair a dataflow if both data sits in the same dataflow.  So, if another team member were to load QAD data into the working dataflow, that dataflow has to be replaced and this represents a significant down time.  But, it's the only solution at the moment.

[v-pnaroju-msft]

Hi etane,

Thankyou for the update.

Based on my understanding, the error “On‑Prem execution not supported for entity … since it refers to another entity” occurs when a dataflow mixes cloud sources (for example, Snowflake or Salesforce) with on- premises sources (for example, SQL Server or QAD), or when a deleted on-premises reference still exists in the dataflow. Power Query Online attempts to execute the query in the cloud, but on‑premises sources require an execution path through a gateway, which causes this error. A hidden dependency or computed entity in your dataflow may still reference the on‑premises SQL Server source even after it was deleted. Since on‑premises sources cannot execute in the cloud without a gateway, the refresh fails.

Please follow the steps below which might help to resolve the issue:

1. Open the dataflow and remove, or re‑create, any query that is still linked to the deleted QAD/SQL Server table.
2. In Manage Connections, delete any unused SQL Server connection and its credentials, then save and refresh.
3. If you must include on‑premises SQL tables, connect via an On‑premises Data Gateway and ensure the dataflow maps correctly to that source.
4. If the issue persists, recreate the dataflow using only the current sources (Snowflake and Salesforce).

If the error continues, please raise a Microsoft Support ticket using the following link:Microsoft Fabric Support and Status | Microsoft Fabric

Additionally, please refer to the links below:
Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn
Creating a Dataflow - Power BI | Microsoft Learn
On-premises data gateway - Power BI | Microsoft Learn

We hope the information provided will help to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[etane]

Hello.

I have many dataflows that have merged QAD and Salesforce tables without issue for years.  So, it shouldn't be a mixing of cloud and on-prem data that is causing the error.  The new variable is Snowflake data with on-prem data loaded into the same dataflow even without merge/append and after QAD table and SQL connection is removed from the dataflow.  The on-prem source is accessed through a data gateway.

Our IT has created a ticket with MSFT and MINDTREE has been conducting tests with me for the past 2 weeks.  We've overcomed merging of Snowflake and Salesforce tables.  This current issue is a new variable, and I've requested them to test and waiting for their response.

[v-pnaroju-msft]

Thankyou, @ mohitkumawat, for your response.

Hi etane,

Thankyou for the followup.

Based on my understanding, to enable Power BI Service to connect to and refresh Snowflake datasets reliably, please ensure that the following Azure service tags (West US region) are allow listed or included in the Snowflake network policy.

Required service tags:

1. AzureConnectors.WestUS (important, as it is used by Power Query Online and managed connectors)
2. PowerBI.WestUS
3. PowerQueryOnline.WestUS
4. PowerPlatformInfra.WestUS

Optional service tags (if staging or blob storage is used):

1. Storage.WestUS
2. GatewayManager.WestUS

If Snowflake does not natively support service tag syntax, extract the IP ranges corresponding to these tags from Microsoft’s published JSON file and update the network policy automatically. This JSON file is refreshed weekly. Snowflake network policies can be scripted to refresh the allowed IP prefixes automatically. After adding these tags or their IP ranges, please test a Power BI Service refresh. The connection should succeed without further IP rejections.

If the refresh still fails after adding these tags, please raise a Microsoft support ticket using the link:Microsoft Fabric Support and Status | Microsoft Fabric

Additionally, please refer to the links provided below:
Azure service tags overview | Microsoft Learn
Managed connectors outbound IP addresses | Microsoft Learn
Connect to Snowflake with Power BI - Power BI | Microsoft Learn

We hope that the information above will help to resolve the issue. Should you have any further queries, please feel free to contact the Microsoft Fabric Community.

Thank you.

[etane]

Hello.

Thank you for your detailed reply.

I have some updates.  We managed to get Snowflake tables to merge and refresh. in PBI Serivce.  After a prompt from MSFT LTIMINDTREE, we used the Snowflake connector without Data Gateway.  With this method, the Snowflake tables are able to merge and refresh in Service.

So, I started development of a new dataflow.  I merged Snowflake tables with Salesforce tables, not an issue.  Then, I pulled a QAD ERP table via SQL Server db connection.  I didn't even do anything with this table.  Just, once it's loaded, I refreshed the dataflow, I get the error message: On-Prem execution not supported for entity ...  since it refers to another entity.

Also, what's intereting is even after I deleted the QAD table and removed the connection in Service's Power Query's Manage Connection, the dataflow still wouldn't refresh.

Help?

Help?

[mohitkumawat · Accepted solution]

Hi @etane ,

Adjust the Data Privacy Level of the Snowflake data source to Organizational or Public under Gateway connection settings. moving file of IPs, so then the most dynamic way I’ve found to manage this is with whitelisting the Azure Services Tags in Snowflake. The best place for your vendor to get this list from is Microsoft's public documentation, which can be scripted by them into a process to update a Snowflake Network Policy.

If you are still facing this issue you can rasie ticket for immediate support.
https://go.microsoft.com/fwlink/?linkid=2230908 

[etane]

So, I am rummaging through the ips in the json file found in this link:

https://www.microsoft.com/en-us/download/details.aspx?id=56519

My tenant is in West US.

Which group of IPs should I ask our vendor's SF admin to whitelist?

We already did these:

PowerBI.WestUS

PowerPlatformInfra.WestUS

PowerQueryOnline.WestUS

and according to the SF admin:

     global ranges for systemService=PowerBI and systemService=Fabric

What other service tags could we try to whitelist?

ActionGroup.WestUS

ApiManagement.WestUS

AppService.WestUS

AppServiceManagement.WestUS

AzureArcInfrastructure.WestUS

AzureAttestation.WestUS

AzureBackup.WestUS

AzureBotService.WestUS

AzureCognitiveSearch.WestUS

AzureConnectors.WestUS

AzureContainerRegistry.WestUS

AzureCosmosDB.WestUS

AzureDataExplorerManagement.WestUS

AzureDevOps.WestUS

AzureDevSpaces.WestUS

AzureDigitalTwins.WestUS

AzureEventGrid.WestUS

EventHub.WestUS

AzureIoTHub.WestUS

AzureKeyVault.WestUS

AzureMachineLearning.WestUS

AzureMonitor.WestUS

AzureOpenDatasets.WestUS

AzurePortal.WestUS

AzureResourceManager.WestUS

Sql.WestUS

AzureSecurityCenter.WestUS

ServiceBus.WestUS

AzureSignalR.WestUS

Storage.WestUS

BatchNodeManagement.WestUS

DataFactory.WestUS

GatewayManager.WestUS

HDInsight.WestUS

LogicApps.WestUS

MicrosoftCloudAppSecurity.WestUS

MicrosoftContainerRegistry.WestUS

PowerBI.WestUS

PowerPlatformInfra.WestUS

PowerPlatformPlex.WestUS

PowerQueryOnline.WestUS

SCCservice.WestUS

ServiceFabric.WestUS

StorageSyncService.WestUS

WindowsVirtualDesktop.WestUS

AzureCloud.westus

[etane]

Thank you for your feedback.  I will ask our IT to look into whitelisting Azure Service Tags. 

Unforntunately, our vendor refuses to do the whitelisting and asked our IT to do it.  And, vendor will then authorize the connections we create.