SORT_KEY

Target database: Snowflake, Amazon Redshift, Azure SQL Database, Azure Synapse SQL, Google BigQuery

Defining a SORT_KEY for an entity uses different features depending on the target database management system:

See also:

• STORAGE.SORTKEY_RECLUSTER_IN_ENTITY_RECREATE

• Clustering Keys & Clustered Tables (Snowflake documentation)

• Working with sort keys (Azure Redshift documentation)

• Clustered and Nonclustered Indexes Described (Microsoft documentation)

• Create and use clustered tables (Google BigQuery 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]));

Google BigQuery 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);