Is there a way to tag a Snowflake view as "safe" for result reuse?

Question

Reading the Snowflake documentation for Using Persisted Query Results, one of the conditions that must be met for result reuse is the following:

The query does not include user-defined functions (UDFs) or external functions.

After some experiments with this trivial UDF and view:

create function trivial_function(x number)
        returns number
        as
        $$
          x
        $$
        ;
create view redo as select trivial_function($1) as col1, $2 as col2
  from values (1,2), (3,4);

I've verified that this condition also applies to queries over views that use UDFs in their definitions.

The thing is, I have a complex view that would benefit much from result reuse, but employs a lot of UDFs for the sake of clarity. Some UDFs could be inlined, but that would make the view much more difficult to read. And some UDFs (those written in Javascript) are impossible to inline.

And yet, I know that all the UDFs are "pure" in the functional programming sense: for the same inputs, they always return the same outputs. They don't check the current timestamp, generate random values, or reference some other table that might change between invocations.

Is there some way to "convince" the query planner that a view is safe for result reuse, despite the presence of UDFs?

score 1 · Answer 1 · answered May 02 '21 at 05:34

1

There is parameter called IMMUTABLE:

CREATE FUNCTION
 VOLATILE | IMMUTABLE
Specifies the behavior of the UDF when returning results:

VOLATILE: UDF might return different values for different rows, even for the same input (e.g. due to non-determinism and statefullness).

IMMUTABLE: UDF assumes that the function, when called with the same inputs, will always return the same result. This guarantee is not checked. Specifying IMMUTABLE for a UDF that returns different values for the same input will result in undefined behavior.

Default: VOLATILE

answered May 02 '21 at 05:34

Lukasz Szozda

162,964
23
234
275

Interesting! That should mark the UDF as "safe" for result reuse. However, making my trivial_function IMMUTABLE doesn't seem to enable result reuse in the view, according to the [Query Profile Interface](https://docs.snowflake.com/en/user-guide/ui-query-profile.html#query-profile-interface). – danidiaz May 02 '21 at 07:08
@danidiaz Then I assume tat the condition "The query does not include user-defined functions (UDFs) or external functions." is final. – Lukasz Szozda May 02 '21 at 08:26

Is there a way to tag a Snowflake view as "safe" for result reuse?

1 Answers1