Free Preparation Discussions
MENU
Snowflake ARA-R01 Exam - Topic 4 Question 21 Discussion
Topic #: 4
A new table and streams are created with the following commands:
CREATE OR REPLACE TABLE LETTERS (ID INT, LETTER STRING) ;
CREATE OR REPLACE STREAM STREAM_1 ON TABLE LETTERS;
CREATE OR REPLACE STREAM STREAM_2 ON TABLE LETTERS APPEND_ONLY = TRUE;
The following operations are processed on the newly created table:
INSERT INTO LETTERS VALUES (1, 'A');
INSERT INTO LETTERS VALUES (2, 'B');
INSERT INTO LETTERS VALUES (3, 'C');
TRUNCATE TABLE LETTERS;
INSERT INTO LETTERS VALUES (4, 'D');
INSERT INTO LETTERS VALUES (5, 'E');
INSERT INTO LETTERS VALUES (6, 'F');
DELETE FROM LETTERS WHERE ID = 6;
What would be the output of the following SQL commands, in order?
SELECT COUNT (*) FROM STREAM_1;
SELECT COUNT (*) FROM STREAM_2;
In Snowflake, a stream records data manipulation language (DML) changes to its base table since the stream was created or last consumed. STREAM_1 will show all changes including the TRUNCATE operation, while STREAM_2, being APPEND_ONLY, will not show deletions like TRUNCATE. Therefore, STREAM_1 will count the three inserts, the TRUNCATE (counted as a single operation), and the subsequent two inserts before the delete, totaling 4. STREAM_2 will only count the three initial inserts and the two after the TRUNCATE, totaling 3, as it does not count the TRUNCATE or the delete operation.
Novella
9 months agoFlorinda
9 months agoKaty
9 months agoToshia
10 months agoFrancesco
10 months agoWilburn
10 months agoLashunda
10 months agoYoko
10 months agoDorathy
11 months agoVivan
11 months agoAmber
11 months agoAnnice
11 months agoKimbery
11 months agoAmie
2 years agoHillary
2 years agoHarris
2 years agoNobuko
2 years agoJerry
2 years agoAnglea
2 years agoKati
2 years agoNathan
2 years agoTimmy
2 years agoWillis
2 years agoSommer
2 years agoMariko
2 years agoBuck
2 years agoMiesha
2 years agoMyrtie
2 years agoTimmy
2 years agoAnnabelle
2 years agoIra
2 years agoMeaghan
2 years ago