Whenever, we “imported” a model into a CTE at the top of the file (CTE1), and then called that CTE in two separate CTEs (CTE2 and CTE3) with WHERE statements to get a slice of the data in each of them, Snowflake performed a full table scan.
This prompted us to test what’s going to happen if we “ref” that table twice rather than import it once at the top of the file. The results were beyond our expectations!
They promptly responded and confirmed this is the desired behaviour of the optimiser. They advised us that they are working on improving this feature of the optimiser but were not able to provide an ETA.