Defining Table Creation Modifiers

You can set table modifiers for individual entities in the Physical Model tab, thereby overriding the default settings in the project settings'Table creation modifiers tab Table modifiers allow you to append additional table properties to the default Compose CREATE TABLE statement.

The available options are located below the Columns list on the right of the tab, and are as follows:

• Project settings default - When this option is selected (the default), the settings from the project settings' Table creation modifiers tab will be used.
• Custom - This option is useful for appending additional table properties to the default Compose CREATE TABLE statement. Leveraging this option requires SQL coding knowledge.

• Custom distribution keys - This option is useful if you only need to define custom distribution keys for individual entities. Although this can also be done using the Custom option (see below), the Custom distribution keys option is more convenient as it does not require any prior SQL knowledge.

  Information note

  • Supported with Microsoft Azure Synapse Analytics and Amazon Redshift only.
  • The default distribution key for all data warehouse tables is the ID column.

Setting table creation modifiers

By default, Compose creates tables in the data warehouse using the standard CREATE TABLE statement. However, organizations often need tables to be created with custom properties for better performance, special permissions, custom collation, and so on. For example, in Microsoft Azure Synapse Analytics, it’s possible to create a table as a HEAP, which is optimized for smaller tables. By default, Compose creates tables in Microsoft Azure Synapse Analytics as a CLUSTERED COLUMNSTORE INDEX, which offers the best overall query performance for large tables.

The procedure for settings table modifiers is as follows:

1. In the Physical Model tab, select the desired entity.
2. Select the Custom option.
3. Click the Edit button to open the Table Creation Modifier editor.
4. Enter the SQL parts you wish to append to the CREATE TABLE statement.

5. Optionally, but strongly recommended, validate the SQL in an external validation tool that supports your specific database and version. For instance, if you are validating

   Information note

   Compose does not provide any way of validating your SQL. Therefore, make sure to validate the SQL before deploying in a production environment.

6. Click OK to close the editor and save your SQL parts.

Example of a Valid Table Creation Modifier

In the following example, the Compose CREATE TABLE statement (rows 1-5) is appended with an SQL part instructing Compose to create the table as a HEAP (row 6).

CREATE TABLE MyTable

(

column1 integer,

column2 varchar(50),

)

WITH (HEAP)

Setting Custom Distribution Keys

This section describes how to set a custom distribution key for tables created in Amazon Redshift and Microsoft Azure Synapse Analytics. Note that depending on the selected Distribution Style (Amazon Redshift) or Distribution Method (Microsoft Azure Synapse Analytics), some of the options may not be available.

Setting a distribution key for Amazon Redshift Data Warehouse

Select and entity and then set a distribution key for Amazon Redshift Data Warehouse according to the table below.

Setting a distribution key for Microsoft Azure Synapse Analytics

Select and entity and then set a distribution key for Microsoft Azure Synapse Analytics according to the table below.