SORT_KEY
Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL
Defining a SORT_KEY for an entity uses different features depending on the target database management system:
Target DMBS | Feature |
---|---|
Snowflake | CLUSTER BY |
Amazon Redshift | COMPOUND SORTKEY |
Azure SQL Database, Azure Synapse SQL | CLUSTERED INDEX |
See also:
Clustering Keys & Clustered Tables (Snowflake documentation)
Clustered and Nonclustered Indexes Described (Microsoft documentation)
Usage
Pick one or several attributes to form the key and give a name to the key.
Notes
SORT_KEY can be used with tables and materialized views in Snowflake, Amazon Redshift and Azure SQL Database. Azure SQL Database supports clustered indexes also for views.
SORT_KEY can be used with tables in Azure Synapse SQL.
Check Export SQL/Entity SQL after defining a SORT_KEY to see generated DDL.
Examples
Snowflake CLUSTER BY
Key definition:
KEY NAME: SORTKEY_F_TRIP
KEY TYPE: SORT_KEY
ATTRIBUTE 1: trip_wid
DDL generated by Agile Data Engine:
CREATE TABLE pub.F_TRIP (
...
) CLUSTER BY (trip_wid);
Amazon Redshift COMPOUND SORTKEY
Key definition:
KEY NAME: SORTKEY_F_TRIP
KEY TYPE: SORT_KEY
ATTRIBUTE 1: trip_wid
DDL generated by Agile Data Engine:
CREATE TABLE pub.F_TRIP (
...
) COMPOUND SORTKEY (trip_wid);
Azure SQL Database CLUSTERED INDEX
Key definition:
KEY NAME: SORTKEY_F_TRIP
KEY TYPE: SORT_KEY
ATTRIBUTE 1: trip_wid
DDL generated by Agile Data Engine:
CREATE TABLE [pub].[F_TRIP] (
...
);
CREATE CLUSTERED INDEX [SORTKEY_F_TRIP] ON [pub].[F_TRIP] ([trip_wid]);
Azure Synapse SQL CLUSTERED INDEX
Key definition:
KEY NAME: SORTKEY_F_TRIP
KEY TYPE: SORT_KEY
ATTRIBUTE 1: trip_wid
DDL generated by Agile Data Engine:
CREATE TABLE [pub].[F_TRIP] (
...
) WITH (CLUSTERED INDEX ([trip_wid]));