Defining Fact Table Creation Modifiers
You can set table creation modifiers for individual star schema (fact) tables, 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 in the Physical Table 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 if you need to append table creation modifiers to the default CREATE TABLE statement Compose uses for fact tables. Leveraging this option requires SQL coding knowledge.
-
Custom distribution and sort keys - This option is useful if you only need to define custom distribution keys or sort keys for the fact table. Although this can also be done using the Custom option (see below), the Custom distribution and sort keys option is more convenient as it does not require any prior SQL coding knowledge.
Information note- Supported with 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:
- Open the star schema and select the Physical Table tab.
- Select the Custom option.
- Click the Edit button to open the Table Creation Modifier editor.
- Enter the SQL parts you wish to append to the CREATE TABLE statement.
-
Optionally, but strongly recommended, validate the SQL in an external validation tool that supports your specific database and version.
Information noteCompose does not provide any way of validating your SQL. Therefore, make sure to validate the SQL before deploying in a production environment.
- 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 and managing custom distribution keys for Amazon Redshift tables
Set and manage distribution keys for Amazon Redshift Data Warehouse according to the table below.
To | Do This |
---|---|
Add a distribution key |
|
Set a distribution style |
From the Distribution Style drop-down, select Even, Key or All. For more information on distribution styles, see: |
Edit a distribution key |
|
Delete a distribution key |
Select the distribution key and then click the Delete button. The key is deleted. |
Change the position of a distribution key |
Select the distribution key and then click the "Up" or "Down" buttons to move the key to the desired position. |
Setting and managing custom sort keys for Amazon Redshift tables
You can define one or more of the physical table columns as sort keys. Amazon Redshift stores your data on disk in sorted order according to the sort key. The Amazon Redshift query optimizer uses sort order when it determines optimal query plans. For guidelines on choosing sort keys, visit Choose the best sort key - Amazon Redshift.
Set and manage sort keys for Amazon Redshift Data Warehouse according to the table below.
To | Do This |
---|---|
Add a sort key |
|
Edit a sort key |
|
Change the position of a sort key |
Select the sort key you want to move and then click the up or down arrows to promote or demote the key. |
Delete a sort key |
Select the sort key you want to delete and then click the Delete button. |
For more information about sort keys, visit: Choosing sort keys - Amazon Redshift.