**Temp tables, CTEs, or RESULT_SCAN? Here’s how I decide.**Every time I’m building a data transformation in Snowflake, I ask myself the same three questions. Getting these right has become one of the most practical ways I optimize both performance and cost.The problem? Most teams pick one pattern and use it everywhere. I did this too early on—defaulting to temp tables for everything because they felt “safe.”**Here’s the framework I’ve developed:****Question 1: How long do I actually need this data?**→ Just iterating on analysis right now?   **RESULT_SCAN**   – Reuse cached results from expensive queries   – Zero compute cost for subsequent filters/aggregations   – Perfect for exploration, but expires in 24 hours   – When: Refining a report, exploring segments, debugging→ Need it for multiple operations in this session?   **TEMP TABLES**   – Materialize once, reference many times   – Supports updates, joins, clustering   – Persists through the session   – When: Multi-step ETL, quality checks, complex workflows→ Just organizing logic within one query?   **CTEs**   – Maximum readability, zero storage   – Snowflake optimizes the entire plan together   – NOT materialized (this surprised me initially)   – When: Breaking down complex business logic**Question 2: Is this ad-hoc or production?**Ad-hoc analysis → Lean toward RESULT_SCAN and CTEsProduction pipeline → Temp tables for reliability and testability**Question 3: Am I reusing this computation?**If you’re filtering/joining the same expensive base query multiple ways, that’s your signal to materialize it somehow—either as a temp table or by leveraging RESULT_SCAN.**What this looks like in practice:**Imagine a daily reporting workflow:- Expensive aggregation across billions of rows → TEMP TABLE (computed once)- Logical transformations on that data → CTEs (readable, optimized)- Stakeholders request variations → RESULT_SCAN (free iterations)This hybrid approach combines reliability, readability, and cost efficiency.**The shift in mindset:**I stopped asking “which pattern should I use?” and started asking “what does this specific transformation actually need?”Snowflake gives us different tools for different jobs. The art is knowing when each one fits.**What decision-making frameworks have helped you optimize your Snowflake workflows?**#Snowflake #DataEngineering #CloudDataWarehouse #CostOptimization—

0