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
6 months agoFlorinda
6 months agoKaty
6 months agoToshia
7 months agoFrancesco
7 months agoWilburn
7 months agoLashunda
7 months agoYoko
7 months agoDorathy
8 months agoVivan
8 months agoAmber
8 months agoAnnice
8 months agoKimbery
8 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