SNOW-654710: Drop cached temporary tables that are no longer being referenced

What is the current behavior?

cache_result does not clean up a temporary table when it is no longer being referenced. This results in unnecessary storage credits throughout the entirety of the session whenever a result is re-cached, which is common thing to do in an interactive session.

>>> dff = session.create_dataframe([1,2,3])

>>> dff.cache_result().explain()

---------DATAFRAME EXECUTION PLAN----------
Query List:
1.
SELECT  *  FROM (SNOWPARK_TEMP_TABLE_X3FCJ1U38A)
...

--------------------------------------------

>>> dff.cache_result().explain()

---------DATAFRAME EXECUTION PLAN----------
Query List:
1.
SELECT  *  FROM (SNOWPARK_TEMP_TABLE_Z9H68STVDH)
....

What is the desired behavior?

Ideal behavior would be a temporary table cleanup by defining a __del__ method for snowflake.snowpark.dataframe.DataFrame. I'll admit that I don't have a deep understanding of snowpark's innerworkings, but on the surface it seems like all cached results are essentialy a select * from (temp_table_name) which seems generalizable. In pseudo-ish code, it could be something like:

# Module: snowflake.snowpark.dataframe.DataFrame

class DataFrame:
...

    def __del__(self):
        if self.is_cached:
            temp_table = # extract name from self.queries string or some other method
            self._session.sql(f'drop table {temp_table').collect()

How would this improve snowflake-snowpark-python?

Avoid overcharging on storage credits :)

References, Other Background

Start in snowflake.snowpark.dataframe.DataFrame and trace cache_result, explain, and the session query handling to understand how cached temporary tables are tracked. Determine a safe lifecycle for unreferenced cached results, then verify that re-caching does not retain unnecessary temporary tables while still preserving referenced results.