CODEWITHSUNDEEP
snowflake-cloud-data-platformteradatadata-migration
shivani bist
shivani bist

Reputation: 25

significance of insert overwrite query at the end of snowflake query in teradata to snowflake conversion

query in teradata:

create table databasename.tablename, no fallback, no before journal, no after journal
        as
        (
          select a, b
          from databasename2.tablename2
          where x=y
        ) with data unique primary index (a,b);

getting converted to(in snowflake) :

CREATE   TABLE IF NOT EXISTS  databasename.tablename  AS  SELECT a,b  FROM  databasename2.tablename2  WHERE x = y;
INSERT OVERWRITE INTO databasename.tablename SELECT DISTINCT * FROM databasename.tablename;

i cannot understand which part of the teradata query is getting converted to the insert overwrite query.what is the significance of this insert query at end?

Upvotes: 0

Views: 376

Answers (1)

Greg Pavlik
Greg Pavlik

Reputation: 11066

The Teradata command and the Snowflake commands may or may not result in the same rows in the table. Even if they result in the same rows, whether or not the 2-step approach written for Snowflake is the most efficient approach is another matter. It probably isn't, but here' what's happening:

CREATE   TABLE IF NOT EXISTS  databasename.tablename  AS  
SELECT a,b  FROM  databasename2.tablename2  WHERE x = y;

This obviously creates tablename with from tablename2 where x = y.

INSERT OVERWRITE INTO databasename.tablename
SELECT DISTINCT * FROM databasename.tablename;

This step reads the rows from the table just created, tablename and selects only the distinct rows. From this result set, it truncates tablename and fills it with the new rows in this select (that is, the distinct rows).

In Teradata, the create statement is enforcing a unique index on columns (a,b). Since Snowflake does not enforce primary keys or unique indexes, it may be necessary to deduplicate data. However, this deduplication on Snowflake is not happening against (a,b). It's happening on *, so all columns. This can lead to differences in the tables after creation.

Upvotes: 1

Related Questions