STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE

Target database: Snowflake, Amazon Redshift

STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE option controls whether the clustering keys defined with SORT_KEY or INTERLEAVED_SORT_KEY are forced to recluster at deployment time when entity recreate will happen.

See also:

• STORAGE.AUTO_CLUSTERING_SUSPEND

• SORT_KEY

• INTERLEAVED_SORT_KEY

Usage

STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE: Boolean

Default: true

Notes

STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE assumes that SORT_KEY or INTERLEAVED_SORT_KEY have been defined for the entity.

Examples

Snowflake: Order by sort key in entity recreate

Example case:

Option set to true:

STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE: true

If an entity change that will trigger entity recreation is deployed, Agile Data Engine will order the data by the cluster key when inserting data into the recreated entity:

...
CREATE TABLE publish.F_TRIP_1234_ALTER ...;

INSERT INTO publish.F_TRIP_1234_ALTER ...;
SELECT ...
FROM publish.F_TRIP ORDER BY (trip_wid);
...

Amazon Redshift: Vacuum sort only in entity recreate

Example case:

Option set to true:

STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE: true

If an entity change that will trigger entity recreation is deployed, Agile Data Engine will run vacuum sort only as a last step for the recreated entity:

...
VACUUM SORT ONLY publish.F_TRIP;