Table cloning is required for purposes such as data backup, testing, and replication. Teradata, a leading data warehousing platform, provides an efficient method for cloning tables. However, Snowflake, a cloud-based data warehousing platform, offers an even more effective approach with its zero-copy cloning feature. This article will compare table cloning in Teradata with Snowflake’s zero-copy cloning.
Teradata Clone Tables: Conventionally, cloning tables in Teradata entails a three-step process:
- Retrieve the DDL statement of the table to be cloned.
- Create a copy of the table structure using this DDL.
- Run an INSERT…SELECT into the empty table.
Teradata provides a streamlined alternative with the statement:
CREATE TABLE <TABLENAME> AS <TABLE_TO_CLONE> WITH [NO] DATA [AND STATISTICS]
The CREATE TABLE AS statement simplifies the process of creating a new table, whether it is empty or populated, eliminating an extra step from the traditional process.
Teradata’s CREATE TABLE AS statement provides multiple advantages:
The process of transferring statistics or definitions from the original table to the cloned table is streamlined, saving time and resources formerly used to recollect statistics.
The CREATE TABLE AS method enables SQL-based cloning, which allows for the creation and population of clone tables using SQL statements, including joins and aggregations.
Rules and attributes for cloning tables in Teradata include:
CTAS does not copy every constraint, index or attribute. Inspect SHOW TABLE and explicitly recreate required definitions; table-form and query-form CTAS have different rules.
Depending on the SQL syntax, you can modify table and column attributes during cloning.
Specifying the primary index when cloning from a SQL statement is essential for optimum performance and avoiding skewed data.
Snowflake’s Zero-Copy Cloning:
Snowflake’s zero-copy cloning feature further simplifies the cloning process. Both Teradata CTAS and Snowflake CLONE can use one statement. Snowflake initially shares underlying data storage through metadata references.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free bookCREATE TABLE <TABLENAME> CLONE <TABLE_TO_CLONE>;
Snowflake zero-copy clones initially share data, but subsequent changes and retention can increase storage charges. They are not independent backups. Teradata CTAS can also create and populate a table in one statement, but physically copies data. Compare costs, supported objects and recovery requirements; no Snowflake runtime test was performed here.
Planning a Teradata-to-Snowflake migration?
Review the assessment, conversion, reconciliation and cutover work needed for a controlled transition.
Get the complete Teradata tuning book, free
Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis
206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.
Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.
About the free book