I am facing a challenging SQL compilation error: Unsupported subquery type cannot be evaluated error inside a Snowflake SQL User-Defined Table Function (UDTF).

The Context & Use Cases

I have a core calculation UDTF that handles complex insurance math. It must support two distinct business use cases:

1. Power BI / Single Pass: End users pass a static group number and date range (only 3 parameters). Optional metrics parameters default to NULL or fallback values inside the function. This works perfectly.

2. Ad-hoc / Batch Processing: Users need to pass a dataset of distinct group numbers (e.g., from a driver table or VALUES clause) and run the function separately for each group using a LATERAL join. This is where it fails.

The Blocker

When calling the function dynamically across a list of separate groups using the following syntax

SELECT x.GrpNo, y.* 
FROM (VALUES ('00001'), ('ABC01')) AS x(GrpNo),
LATERAL TABLE(
    MySchema.fnMyCalculation(
        x.GrpNo, 
        TO_DATE('2025-01-01'), 
        TO_DATE('2025-12-31')
    )
) AS y;

Snowflake throws the compilation error.

Internal Function Design Constraints

Inside the SQL UDTF, the logic has to determine whether to compute In-Force metrics from historical transaction facts or use Prospect metrics overridden by optional parameter inputs.

To achieve this, the internal queries contain a series of CTEs that reference the input variable GRPNO (which is bound to the outer column x.GrpNo during the lateral loop) inside aggregation blocks (SUM, MIN, COUNT(DISTINCT)) and analytical window functions (ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)).

Because Snowflake inlines the UDTF code during a LATERAL call, the optimizer gets stuck trying to evaluate these complex aggregated CTE layers dynamically row-by-row, causing the subquery evaluator to break down.

What I Have Tried

• Cleaned up all inner nested loops and converted correlated scalar subqueries into flat dimensions using windowed QUALIFY filters.

• Attempted using function overloading (a 3-parameter signature wrapper that maps cleanly to a strict 12-parameter core signature) to preserve the clean 3-parameter input for Power BI users.

• Note: I know this can be solved using a procedural WHILE loop inside a Stored Procedure, but I am highly interested in keeping this contained within a single, unified function object to keep things simple for end-user querying.

My Questions for the Forum:

1. Is there a known rewrite pattern or compiler hint to force Snowflake to completely isolate and materialize the input variables per execution row inside a SQL UDTF when running a LATERAL loop?

2. How can I structure heavy aggregations and analytic window constraints inside an inline SQL table function so that they do not conflict with outer correlated scoped parameters?

3. If keeping it in a single UDTF is impossible due to optimizer limits, what is the cleanest design pattern to separate the aggregation layer from the math computation layer without forcing users into a Stored Procedure?

Any advice, structural design patterns, or workarounds would be greatly appreciated!