Order by vs. cluster by

[graciegoheen]

What is the difference between order by & cluster by and when to use one over the other?

[matt-winkler]

Noting we have some documentation on this

[jtcohen6]

I'd also call out:

It will implicitly order the table results by the specified cluster_by fields

dbt does this using order by explicitly, added to the end of your model's compiled SQL

[gwenwindflower]

my understanding is:

order by is part of a select statement and determines the interior order of the result set itself whether you’re storing it or simply retrieving it — this is not necessarily related to how it is split and stored across disks in snowflake’s servers though -- if often might be, but not always.

cluster by, on the other hand, is a dml clause configuration purely related to the create/replace of objects — it does determine the way an object is split and stored across disks — these two could both be used in a statement together actually, but in 95% of cases you’re better off to let snowflake determine clustering on its own.

for example, you could order by created_at and cluster by user_id, that’d be valid, and it would mean user_ids were grouped and indexed across disks (snowflake would try not to split multiple records with the same user_id across disks) and it would try to store them ‘sequentially’ but when you queried an unordered select * they’d return in created_at order still — you probably wouldn’t do that though — in reality, if this was - for example - a customers table, and most often retrieved by created_at date (eg past two years, cohorts, etc), you could just order by created_at in your dbt model and snowflake would probably automatically cluster by user_id and created_at so that user_ids weren’t split and records were ordered by created_at