Skip to content

SNOW-3545104 DataFrame.schema inference fails when SQL query contains named WINDOW clause #4226

Closed

Description

@jinseo-ok

Title: DataFrame.schema inference fails when SQL query contains named WINDOW clause

  1. What version of Python are you using?

    Python 3.10 (replace with output of python --version --version)

  2. What operating system and processor architecture are you using?

    macOS (replace with output of python -c 'import platform; print(platform.platform())')

  3. What are the component versions in the environment (pip freeze)?

    (replace with output of python -m pip freeze)

    Key packages:

    • snowflake-snowpark-python==1.25.x
    • snowflake-ml-python==1.25.1
  4. What did you do?

    Created a Snowpark DataFrame from a SQL query that uses a named WINDOW clause.
    The named WINDOW clause is valid Snowflake SQL and executes correctly when run directly via Snowflake worksheet.

    from snowflake.snowpark import Session
    
    session = Session.builder.configs({...}).create()
    
    query = """
    WITH BASE AS (
        SELECT EVENT_DATE::DATE AS EVENT_DATE, USER_ID, SALES_AMOUNT
        FROM MY_DB.MY_SCHEMA.MY_TABLE
        WHERE EVENT_DATE >= '2022-01-01'
    )
    SELECT
        EVENT_DATE,
        USER_ID,
        SUM(SALES_AMOUNT) OVER W_30D  AS SALES_30D,
        SUM(SALES_AMOUNT) OVER W_180D AS SALES_180D,
        SUM(SALES_AMOUNT) OVER W_365D AS SALES_365D
    FROM BASE
    WINDOW
        W_30D  AS (PARTITION BY USER_ID ORDER BY EVENT_DATE RANGE BETWEEN INTERVAL '30 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING),
        W_180D AS (PARTITION BY USER_ID ORDER BY EVENT_DATE RANGE BETWEEN INTERVAL '180 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING),
        W_365D AS (PARTITION BY USER_ID ORDER BY EVENT_DATE RANGE BETWEEN INTERVAL '365 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING)
    """
    
    df = session.sql(query)
    print(df.columns)  # Fails here

    Workaround: Replacing named WINDOW with inline window specifications works correctly:

    -- Instead of:  SUM(SALES_AMOUNT) OVER W_30D AS SALES_30D
    -- Use:
    SUM(SALES_AMOUNT) OVER (
        PARTITION BY USER_ID ORDER BY EVENT_DATE
        RANGE BETWEEN INTERVAL '30 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING
    ) AS SALES_30D
  5. What did you expect to see?

    Expected: DataFrame.columns should return the correct column names ['EVENT_DATE', 'USER_ID', 'SALES_30D', 'SALES_180D', 'SALES_365D'], since the named WINDOW clause is valid Snowflake SQL.

    Actual: Schema inference fails with the following error:

    (1301): You can only define aliases for the root Columns in a DataFrame
    returned by select() and agg(). You cannot use aliases for Columns in expressions.
    

    This also impacts downstream usage in Snowflake Feature Store — when FeatureView is created with such a DataFrame, register_feature_view() generates a malformed CREATE VIEW DDL with an empty column list, resulting in:

    (1300) Create view ... failed: (1304): 001003 (42000): SQL compilation error:
    syntax error line 1 at position 73 unexpected ')'.
    
  6. Can you set logging to DEBUG and collect the logs?

    (Will attach if requested)

Activity

  1. added
    status-triageIssue is under initial triage
    and removed on May 15, 2026
  2. sfc-gh-sghosh commented on May 16, 2026

    @sfc-gh-sghosh

    Hi @jinseo-ok

    Thanks for raising the issue.

    The error you are encountering (SnowparkSQLUnexpectedAliasException) occurs because the Snowpark Python client uses an internal SQL parser to map data schemas before sending queries to Snowflake. Snowpark operations are lazily evaluated.

    When you build a DataFrame or define a Window specification in Snowpark, Python only constructs the execution plan (the recipe). It does not actually run the query or fetch any data from Snowflake until you explicitly call an "action" method.

    Why it works in worksheet because, it takes raw SQL text string and throws it directly over the fence to Snowflake’s main compiler/optimizer engine. Snowflake’s native SQL database engine is fully standard-compliant and easily understands the ANSI SQL WINDOW clause definition.

    As a workaround, you can use one of the following way

    1. Inline way
    
    query = """
    WITH BASE AS (
        SELECT EVENT_DATE::DATE AS EVENT_DATE, USER_ID, SALES_AMOUNT
        FROM MYTABLE_1
        WHERE EVENT_DATE >= '2022-01-01'
    )
    SELECT
        EVENT_DATE,
        USER_ID,
        SUM(SALES_AMOUNT) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE
            RANGE BETWEEN INTERVAL '30 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING) AS SALES_30D,
        SUM(SALES_AMOUNT) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE
            RANGE BETWEEN INTERVAL '180 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING) AS SALES_180D,
        SUM(SALES_AMOUNT) OVER (PARTITION BY USER_ID ORDER BY EVENT_DATE
            RANGE BETWEEN INTERVAL '365 DAYS' PRECEDING AND INTERVAL '1 DAY' PRECEDING) AS SALES_365D
    FROM BASE
    """
    df = session.sql(query)
    print(df.columns)
    
    Output:
    ['EVENT_DATE', 'USER_ID', 'SALES_30D', 'SALES_180D', 'SALES_365D']
    
    2.  Native Snowpark Python API
    
    from snowflake.snowpark import Window
    import snowflake.snowpark.functions as F
    
    # 1. Grab base table
    df_base = session.table("MYTABLE_1").filter(F.col("EVENT_DATE") >= "2022-01-01")
    
    # 2. Define the Window (Snowpark requires range bounds in seconds for time/date offsets)
    # 1 day = 86400 seconds. 
    # 'RANGE BETWEEN 30 DAYS PRECEDING AND 1 DAY PRECEDING' translates to:
    w_30d = (
        Window.partition_by("USER_ID")
        .order_by("EVENT_DATE")
        .range_between(-30 * 86400, -1 * 86400)
    )
    w_180d = (
        Window.partition_by("USER_ID")
        .order_by("EVENT_DATE")
        .range_between(-180 * 86400, -1 * 86400)
    )
    w_365d = (
        Window.partition_by("USER_ID")
        .order_by("EVENT_DATE")
        .range_between(-365 * 86400, -1 * 86400)
    )
    
    
    df_final = df_base.select(
        F.col("EVENT_DATE"),
        F.col("USER_ID"),
        F.sum("SALES_AMOUNT").over(w_30d).alias("SALES_30D"),
        F.sum("SALES_AMOUNT").over(w_180d).alias("SALES_180D"),
        F.sum("SALES_AMOUNT").over(w_365d).alias("SALES_365D")
    )
    
    # 4. TRIGGER THE ACTION 
    df_final.show()
    
    ------------------------------------------------------------------------
    |"EVENT_DATE"  |"USER_ID"  |"SALES_30D"  |"SALES_180D"  |"SALES_365D"  |
    ------------------------------------------------------------------------
    |2022-01-07    |50         |NULL         |NULL          |NULL          |
    |2022-01-12    |50         |NULL         |NULL          |NULL          |
    |2022-01-20    |50         |NULL         |NULL          |NULL          |
    |2022-01-25    |50         |NULL         |NULL          |NULL          |
    |2022-02-03    |50         |NULL         |NULL          |NULL          |
    |2022-02-09    |50         |NULL         |NULL          |NULL          |
    |2022-02-09    |50         |NULL         |NULL          |NULL          |
    |2022-03-16    |50         |NULL         |NULL          |NULL          |
    |2022-03-17    |50         |NULL         |NULL          |NULL          |
    |2022-03-25    |50         |NULL         |NULL          |NULL          |
    ------------------------------------------------------------------------
    
  3. added
    status-triage_doneInitial triage done, will be further handled by the driver team
    and removed
    bugSomething isn't working
    status-triageIssue is under initial triage
    on May 16, 2026
  4. changed the title [-]`DataFrame.schema` inference fails when SQL query contains named WINDOW clause[/-] [+]SNOW-3545104 `DataFrame.schema` inference fails when SQL query contains named WINDOW clause[/+] on May 16, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

status-triage_doneInitial triage done, will be further handled by the driver team

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions