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
3 months agoFlorinda
3 months agoKaty
3 months agoToshia
4 months agoFrancesco
4 months agoWilburn
4 months agoLashunda
4 months agoYoko
4 months agoDorathy
5 months agoVivan
5 months agoAmber
5 months agoAnnice
5 months agoKimbery
5 months agoAmie
1 year agoHillary
1 year agoHarris
1 year agoNobuko
1 year agoJerry
1 year agoAnglea
1 year agoKati
1 year agoNathan
1 year agoTimmy
1 year agoWillis
1 year agoSommer
1 year agoMariko
1 year agoBuck
1 year agoMiesha
1 year agoMyrtie
1 year agoTimmy
1 year agoAnnabelle
1 year agoIra
1 year agoMeaghan
1 year ago