CODEWITHSUNDEEP
snowflake-cloud-data-platform
Snyder Fox
Snyder Fox

Reputation: 174

How to apply ROW ID in snowflake? Oracle code conversion to Snowflake

Is there any way of converting the last a.ROWID > b.ROWID values in below code in to snowflake? the below is the oracle code. Need to take the ROW ID to snowflake. But snowflake does not maintain ROW ID. Is there any way to achieve the below and convert the row id issue?

DELETE FROM user_tag.user_dim_default a
WHERE EXISTS (SELECT 1
              FROM rev_tag.emp_site_weekly b
              WHERE a.number = b.ID 
              AND a.accountno = b.account_no 
              AND a.ROWID > b.ROWID)

Upvotes: 1

Views: 5596

Answers (2)

Ed Crean
Ed Crean

Reputation: 11

ROWID is an internal hidden column used by the database for specific DB operations. Depending on the vendor, you may have additional columns such as transaction ID or a logical delete flag. Be very carful to understand the behavior of these columns and how they work. They may not be in order, they may not be sequential, they may change in value as a DB Maint job runs while your code is running, or someone else runs an update on a table. Some of these internal columns may have the same value for more than one row for example.

When joining tables, the RowID on one table has no relation to the RowID on another table. When writing Dedup logic or delete before insert type logic, you should use the primary key, and then additionally an audit column that has the date of insert or date of last update in combo with that. Check the data model or ERD digram for the PK/FK relationships between the tables and what audit columns are available.

Upvotes: 0

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26003

So this Oracle code seem very broken, because ROWID is a table specific pseudo column, thus comparing value between table seem very broken. Unless the is some aligned magic happening, like when user_tag.user_dim_default is inserted into rev_tag.emp_site_weekly is also written. But even then I can imagine data flows where this will not get what you want.

So as with most things Snowflake, "there is no free lunch", so the data life cycle that is relying on ROW_ID needs to be implemented.

Which implies if you are wanting to use two sequences, then you should do explicitly on each table. And if you are wanting them to be related to each other, it sounds like a multi table insert or Merge should be used so you can access the first tables SEQ and relate it in the second.

Upvotes: 2

Related Questions