Streamlining Table Cloning in Teradata and Leveraging Zero-Copy Cloning in Snowflake

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:

1. Retrieve the DDL statement of the table to be cloned.
2. Create a copy of the table structure using this DDL.
3. Run an INSERT…SELECT into the empty table.

Teradata provides a streamlined alternative with the statement:

Want more practical data engineering analysis like this?

Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.

Get the next issue

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:

All attributes are also cloned when creating a table, except when cloning based on a SQL statement.

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. Unlike Teradata, you can create a clone of a table or schema using a single statement.

CREATE TABLE <TABLENAME> CLONE <TABLE_TO_CLONE>;

Snowflake's zero-copy cloning method is faster and more cost-effective as it does not duplicate data or require additional storage. Moreover, Snowflake automatically handles metadata, statistics, and primary key definitions, simplifying the cloning process. In comparison, Teradata's CREATE TABLE AS statement requires more steps to achieve the same result. Therefore, by adopting Snowflake's streamlined cloning approach, you can optimize your data warehousing operations by reducing complexity and saving time and resources.

Planning or surviving an enterprise data platform migration?

I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.

Subscribe for free and keep launch access.

Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.