Datometry does not recommend or endorse any particular approach to development or change management. Below is a summary of our experience with other customers, but this should be validated with appropriate review and due diligence by your internal owners. Datometry is an independent software vendor (ISV) and not a systems integrator (SI).
Manage Database Objects
Typically customers manage the lifecycle of objects using their Teradata definition and apply these changes to Azure Synapse Analytics using the Teradata definition through Datometry Hyper-Q.
The Teradata definition of an object produced by the Hyper-Q SHOW TABLE implementation (
physical_props_in_show_table) captures Teradata-specific properties such as SET, MULTISET, UPI and custom format strings as well as Synapse-specific tuning of the table distribution, layout, and partitioning in respective Hyper-Q hints.
To learn more about the the Hyper-Q hint
physical_props_in_show_table, see Display Table Properties Using SHOW TABLE.
Use SHOW TABLE to Display the Table Definition
physical_props_in_show_table hint lets you display applicable hints related to the distribution and physical layout in SHOW TABLE output. The output returned by SHOW TABLE is the Data Definition Language (DDL) for the specified table. Using the information returned by SHOW TABLE, you can recreate a table in Azure Synapse using the exact same table definitions.
Below is an example of the SHOW TABLE output generated when using the Hyper-Q hint
SHOW TABLE table_name;
CREATE SET TABLE table_name -- @dtm_override_distribution_style=replicate -- @dtm_override_table_layout=clustered_index -- @dtm_override_table_layout_cols=col2 -- @dtm_synapse_partitions_override=col1 RANGE LEFT FOR VALUES ('10','20') ( col1integer , col2dateFORMAT'YYYY-MM-DD' , col3varchar (10) CHARACTERSETUNICODECASESPECIFIC ) UNIQUEPRIMARYINDEX ( col1 , col2 );
When you run SHOW TABLE, the output generated by Hyper-Q displays the embedded Hyper-Q hints for Azure Synapse distribution, layout, and partitioning of the native table on Azure Synapse that is created with the identical distribution, layout, and partitioning as the original table. The Teradata-specific properties are stored in the Hyper-Q Metadata Store and are identical to the original table on which the SHOW TABLE command was run.
Modifications made with ALTER commands supported by Hyper-Q or natively on Azure Synapse are also reflected when running SHOW TABLE, because SHOW TABLE synthesizes its output from the current state of the object in Synapse. You can also use SHOW VIEW, SHOW MACRO, and SHOW PROCEDURE outputs to manage the lifecycle of views, macros, and procedures, respectively.
The output of the Hyper-Q SHOW object commands is the ideal entity to use for schema maintenance in a Hyper-Q and Azure Synapse environment, and can be version controlled using your preferred source control framework.