Snowflake Swap Feature-CodePudding

Say I am swapping a prod table table_a with another table table_b which is in dev by using the below query-

alter table prod.table_a swap with dev.table_b;

Does it retain all the metadata like retention_time, roles, grants etc in the original table i.e, table_a? Also will there be any issues if I am doing time travel on the prod table like after doing the swap?

Still can't get my head over it as I am doing some data fixes in dev table and after that I want to swap with the prod table.

Please let me know your inputs.

CodePudding user response：

ALTER TABLE SWAP command will swap the contents and metadata between two specified tables, including any integrity constraints defined for the tables and access control privilege grants. Retention_time will also be swapped. It can be considered as two tables being renamed in a single transaction.

https://docs.snowflake.com/en/sql-reference/sql/alter-table.html#parameters

Time travel will be based on the retention time defined in the table and against the table mapping internally.

describe table testdb1.testsch1.testtab1; --- with 1 column and retention 1

describe table testdb2.testsch2.testtab2; --- with 2 columns and retention 2

insert into testdb1.testsch1.testtab1 values (1),(2);

delete from testdb1.testsch1.testtab1 where col1=1; --- Query ID is 01a609ef-0604-911c-0000-01d10b9cd2ae

alter table testdb1.testsch1.testtab1 swap with testdb2.testsch2.testtab2;

describe table testdb1.testsch1.testtab1; --- with 2 columns and retention 2

describe table testdb2.testsch2.testtab2; --- with 1 column and retention 1

select * from testdb2.testsch2.testtab2 before(statement => '01a609ef-0604-911c-0000-01d10b9cd2ae'); -- show the data

select * from testdb1.testsch1.testtab1 before(statement => '01a609ef-0604-911c-0000-01d10b9cd2ae'); -- will not show the data

CodePudding user response：

This statement:

alter TABLE_A swap with TABLE_B

is the equivalent of doing this in a single transaction:

alter TABLE_A      rename to TABLE_A_TEMP;
alter TABLE_B      rename to TABLE_A;
alter TABLE_A_TEMP rename to TABLE_B;

So this is literally a renaming operation. It swaps nothing but the names of the tables. Everything else remains the same.

The reason the swap command exists is because of this note in the documentation:

Explicit transactions should contain only DML statements and query statements. DDL statements implicitly commit active transactions (for details, see the DDL section).

https://docs.snowflake.com/en/sql-reference/transactions.html

So the swap command provides a way to rename TABLE_A to TABLE_B and vice versa in a single transaction. It cannot be done using begin and commit in a single transaction. The swap command enables this when it needs to be done in a single transaction. This is so it does not miss DML operations in pipelines or break select queries on a highly-active table in the brief instant the swap would take in the three-line renaming process.