Virtual Warehouse is a compute engine to execute/run select, insert, update, delete, merge and copy command etc.. Worksheet is place where you want to keep all your commands
1.Pick duplicate records to an intermediate table. 2.Delete the same from the main table. 3.Insert unique rows form intermediate table to main table. This approach in most cases will make sure that less number of micro partitions are affected.
@@VCKLYTech One more thing, optional suggestion that to make it video short data population in table should be in advance so that only actual concept we can focus.
Here you inserted 4 columns, so you were able to insert timestamps two times, once with current date and then with current date -1.. but what if we have millions of records in the table & we have duplicates
@@VCKLYTech Ok the can we delete these duplicate records using below query. With q1 as (select a.*, Row_number() over(partition by deptno order by deptno) rno from dept) Delete from q1 where rno > 1; Or Delete from (select a.*, Row_number() over(partition by deptno order by deptno) rno from dept) where rno>1;
only the swap table with intermediate tables and usng distinct and creating will works
very good one. most commonly asked scenario.
Hi Sir, Very well explained. Thank you..
thank you
Thank you. It was helpful
You're welcome!
Thank you so much Video is very useful, please provide your exercise text copy on description
Thanks. copied in description
@@VCKLYTech thank you so much sir
Gm Sir,
This is selvavinayagam.
I am followers.
I have an question.
Q: kindly advice on difference bt warehouse vs worksheets?
Thanks.
Virtual Warehouse is a compute engine to execute/run select, insert, update, delete, merge and copy command etc..
Worksheet is place where you want to keep all your commands
@@VCKLYTech sir
I am seeing all sql execution at worksheet.
Is there mani difference?
Thanks sir
Nice explain
Thank you
what if you have million records? Is it practical to insert all the records
1.Pick duplicate records to an intermediate table.
2.Delete the same from the main table.
3.Insert unique rows form intermediate table to main table.
This approach in most cases will make sure that less number of micro partitions are affected.
Please make vidio on one project
Will try it.
Cannot delete duplicate without adding extra column insert_ts in case 3 and 4 methods
If we are dropping table and renaming then will grant given to some role revoked ?
Yes you are correct. This case can can go with Alter table SWAP WITH
@@VCKLYTech One more thing, optional suggestion that to make it video short data population in table should be in advance so that only actual concept we can focus.
@@chandangupta1989 Sure . I will consider your suggestion. thank you
Here you inserted 4 columns, so you were able to insert timestamps two times, once with current date and then with current date -1.. but what if we have millions of records in the table & we have duplicates
Sir,In interview if they ask what have you done as a snowflake developer in your previous project what all we can say in shot ..
Can you share interview questions.....
Can we use with clause query in snowflakes ?
Yes . We can use WITH Clause in snowflake
@@VCKLYTech Ok the can we delete these duplicate records using below query.
With q1 as (select a.*, Row_number() over(partition by deptno order by deptno) rno from dept)
Delete from q1 where rno > 1;
Or
Delete from (select a.*, Row_number() over(partition by deptno order by deptno) rno from dept) where rno>1;
@@praveendeshmukh1952 No, will not work because there is no such column rno from the dept table.
@@VCKLYTech Thank you for your reply 👍
Sir tell me how to do without adding extra column in case 3 and 4
Can you make a project explanation....
Hi