Snowflake stores every table in micro-partitions: compressed, columnar files of roughly 50 to 500 MB of data each. For every partition it keeps the minimum and maximum of each column. A filter like WHERE order_ts >= '2025-06-01' lets it skip every partition whose range can't match. That's pruning, and it's the biggest lever on speed and cost.
Pruning works best when rows with similar values sit together. Data loaded in date order clusters by date naturally. For a huge table filtered on something else, a clustering key (ALTER TABLE ... CLUSTER BY (region)) has Snowflake keep it sorted that way in the background, for a cost.
Then there are caches. The result cache returns the same query's result for 24 hours, without a warehouse, if the data hasn't changed. Each warehouse also caches the data it has read on local disk, which is lost when it suspends.
In the Query Profile, look at partitions scanned against partitions total, and at bytes spilled: spilling to remote storage means the warehouse is too small for the query.