Free Preparation Discussions
MENU
Snowflake ARA-C01 Exam - Topic 5 Question 23 Discussion
Topic #: 5
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.
Ilene
6 months agoScot
6 months agoColton
6 months agoKarol
7 months agoKanisha
7 months agoCecil
7 months agoMartha
7 months agoAriel
7 months agoVernice
8 months agoDulce
8 months agoLaquita
8 months agoRonny
8 months agoMargurite
8 months agoJestine
8 months agoLuisa
8 months agoVenita
8 months agoVallie
8 months agoLaticia
2 years agoCherilyn
2 years agoEric
2 years agoLashaun
2 years agoMira
2 years agoMira
2 years agoMira
2 years agoEzekiel
2 years agoDeeanna
2 years agoElli
2 years agoStephaine
2 years agoCory
2 years agoLuisa
2 years ago