I understand when clustering is enabled Snowflake will run a background task to automatically cluster your records. There are situations where I want to rebuild an entire table or populate a new table with an insert into select statement. When I do this I find that the new table is built unclustered even when I have predefined the clustering. It seems wasteful to load the table first, then rearrange it.
Is there anyway to convince snowflake to use the clustering i’ve defined when rebuilding a table or loading a new table?
>Solution :
You can use ORDER BY that matches CLUSTER BY to have the data clustered at the same time its ingested:
create or replace table delete_test_cluster
cluster by (a, b)
as
select *
from (select 1 a, 2 b)
order by a, b