SNOW-1342891: Are CTEs allowed while doing `session.sql()`?

[arghhjayy]

Python version

3.11.4

Operating system and processor architecture

Windows-10-10.0.17763-SP0

Installed packages

The list is too long and is in a VM so copying is difficult. I don't think it will be needed. If needed, I'll post here.

What did you do?

I'm trying to execute a SQL using a Snowflake Python Connector session. Something like:

from snowflake.snowpark import Session

session = Session.builder.configs(SnowflakeLoginOptions()).getOrCreate()

with open('path/to/file.sql', 'r', encoding='utf-8') as f:
    df = session.sql(f.read()).collect()

where file.sql has SQL starting with a CTE, something like this:

WITH CTE AS (
..

..
)
SELECT * FROM CTE

What did you expect to see?

I expected the SQL statement to be run successfully and the data should be returned as snowflake.snowpark.DataFrame

Instead, I'm getting this:

I have a question: are CTEs not allowed in Snowflake Python Sessions or am I missing anything

[arghhjayy]

Nvm, it was an encoding error with the SQL file. Turns out it started with a special, invisible character which was causing the failure.