title: Merge Or Create Table And Swap?
body: <p>I have a table in snowflake that I merge changes into often (1-2 times per day). Row updates are streamed into an updates table and I query the updates table as will as the primary table at the same time. When I do merge the updates into the primary table, I use the the MERGE command:</p>
  <pre><code>MERGE INTO primary p
          USING (
              SELECT ... FROM (
                 SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY last_updated_at DESC) rn FROM updates
              ) WHERE rn = 1
          ) se
          ON (p.id = u.id)
          WHEN MATCHED THEN
              UPDATE SET ...
          WHEN NOT MATCHED THEN
              INSERT (...)
              VALUES (...)
  </code></pre>
  <p>The number of updates and the size of the table means that my primary table is entirely rewritten when this happens. While not ideal, this alone is not an issue for us because we don't do it often and when we do we use a large enough warehouse to complete the merge in under 10m.</p>
  <p>My question is, since the table gets rebuilt anyway, is there a reason not to merge results into an entirely new table where the results can be pre-clustered (order by cluster columns) and then swap the tables? Is merge doing anything more efficiently here?</p>