Unable to connect snowflake query to power bi - Syntax

Question

I have this query in snowflake. The query works fine in snowflake, but when i am trying to connect it to Power Bi, I get the Native error query. The error usually pops up when there's a syntax error. I can't find any syntax error here. Any help would be appreciated as why there's an error. Error: Native Queries aren't supported by this value.

WITH POLICIES AS(        
SELECT DISTINCT a.POLICY_NUMBER                 
,c.DST                 
,d.DOB                 
,b.ENROLLED_RPM    
,b.RATED_STATE
,a.EVENT_TIMESTAMP    
FROM PD_PRESENTATION.CUSTOMER.REQUEST_FLOW_EDGE_MOBILE_TIER as a   
LEFT JOIN PD_ANALYTICS.SVOC.POLICY as b    
ON a.POLICY_NUMBER = b.POLICY_NUMBER    
LEFT JOIN PD_ANALYTICS.SVOC.POLICY_HAS_POLICYHOLDER_PERSON as c    
ON b.ID = c.SRC    
LEFT JOIN PD_ANALYTICS.SVOC.PERSON as d    
ON d.ID = c.DST    
WHERE a.USER_GROUP = 'Customer'    
AND b.STATUS = 'InForce' 
),  

MaximumTime AS(
SELECT a.POLICY_NUMBER
       ,MAX(a.EVENT_TIMESTAMP) as MAXDATED
FROM POLICIES as a
GROUP BY a.POLICY_NUMBER
 )
 
SELECT DISTINCT a.* 
 ,b.DOB
 ,b.ENROLLED_RPM
 ,b.RATED_STATE          
 ,c.PAPERLESSPOLICYSTATUS          
 ,c.PARTIALPAPERLESSSTATUS         
 ,c.PAYPLAN      
 ,MAX(c.TENUREPOLICYYEARS) as TENURE
FROM MaximumTime as a 
LEFT JOIN POLICIES as b
ON a.POLICY_NUMBER = b.POLICY_NUMBER
LEFT JOIN  PD_POLICY_CONFORMED.PEAK.POLICY as c    
ON a.POLICY_NUMBER = c.POLICY_NUMBER
GROUP BY a.POLICY_NUMBER
        ,a.MAXDATED
        ,b.DOB, b.ENROLLED_RPM
        ,b.RATED_STATE
        ,c.PAPERLESSPOLICYSTATUS
        ,c.PARTIALPAPERLESSSTATUS
        ,c.PAYPLAN

Answer 1

Based on googling I suspect that this is caused by the driver you are using (odbc). If SQL is running fine in snowflake it means it's syntax is correct and there must be an error somewhere between powerbi and snowflake, rather than in your code.

You can try to execute your query and look at the query history in snowflake to check what is actually being executed on snowflake. https://docs.snowflake.com/en/sql-reference/functions/query_history.html

SnowFlake & PowerBI "native queries aren't support by this value"

Maybe it is lowercase / uppercase issue as explained here: https://community.powerbi.com/t5/Issues/Unable-to-query-case-sensitive-Snowflake-tables/idi-p/2028900

In debugging process I would advise you to pinpoint which part of query causes the error. It could be quotes you are using in first CTE, non uppercase table names, * character.