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
4 months agoScot
5 months agoColton
5 months agoKarol
5 months agoKanisha
5 months agoCecil
6 months agoMartha
6 months agoAriel
6 months agoVernice
6 months agoDulce
6 months agoLaquita
6 months agoRonny
6 months agoMargurite
6 months agoJestine
6 months agoLuisa
6 months agoVenita
6 months agoVallie
7 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