title: SnowFlake's performance on group by vs partition on vs distinct
body: <p>I have a table in Snowflake. One of the columns in the table is called <code>obj_key</code>(object key).
  The table size is very large (in TBs), so performance is desirable. </p>
  
  <p>Now, a new entry is added to the table everytime an update on an object is done. The newly inserted row has the same <code>obj_key</code>, but a different entry in the <code>time_modified</code> column. Suppose I want to fetch distinct <code>obj_key</code> from the table, subject to certain conditions. </p>
  
  <p>There are three approaches which I have:</p>
  
  <p>Approach 1:</p>
  
  <pre><code>SELECT obj_key 
  FROM my_table
  WHERE some_condition
  GROUP BY obj_key;
  </code></pre>
  
  <p>Approach 2:</p>
  
  <pre><code>SELECT distinct(obj_key) 
  FROM my_table
  WHERE some_condition;
  </code></pre>
  
  <p>Approach 3:</p>
  
  <pre><code>SELECT obj_key
  FROM my_table
  WHERE some_condition
  QUALIFY ROW_NUMBER() OVER (PARTITION BY obj_key ORDER BY obj_key) = 1;
  </code></pre>
  
  <p>So essentially my question boils down to these:</p>
  
  <p>I had read that <code>distinct</code> on multiple columns is performed by <code>group_by(col1, col2, ..., col n)</code>. So how does the performance differ on the two (if it does)? </p>
  
  <p>Since <code>PARTITION BY</code> also requires an <code>ORDER BY</code>, does it not drastically reduce the performance?</p>
  
  <p>I'd love if someone could provide the nitty-gritties of how these queries run on SnowFlake. </p>