SnowPro Advanced: Architect free question 7 / 10 · Domain 3: Data Engineering
Schema STG has a permanent table ORDERS that other processes use. In one ETL session, CREATE TEMPORARY TABLE STG.ORDERS (...) is run to make a working table, 10 rows are then added in the same session with INSERT INTO STG.ORDERS, and finally DROP TABLE STG.ORDERS is run. Which description is consistent with the official documentation?
- The 10 inserted rows go into the temporary table, but DROP TABLE drops the permanent ORDERS table that has the same name
- The 10 inserted rows go into the permanent ORDERS, and DROP TABLE drops the temporary table that was created in the session
- Both the INSERT and the DROP TABLE act on the temporary table, and the permanent ORDERS keeps its rows and stays in place unchanged
- CREATE TEMPORARY TABLE fails because a table of the same name exists in the schema, and the later statements act on the permanent ORDERS
Show the answer and explanation
Answer: C
Why this is correct
A temporary table belongs to the session, so it is not bound by the uniqueness of names within a schema and can be created even when a permanent table of the same name exists in the schema. The documentation says that within the session the temporary table takes precedence over other tables with the same name in the same schema, hiding the existing table. Every query and other operation on that name later in the session then affects the temporary table alone. The 10-row INSERT and the DROP TABLE both act on the temporary table, and the permanent ORDERS that other processes use is unchanged.
Why the other options are wrong
"CREATE TEMPORARY TABLE fails because a table of the same name exists in the schema, and the later statements act on the permanent ORDERS" … The documentation says a temporary table belongs to the session and is not bound by the same uniqueness rules, so a temporary and a non-temporary table can share a name in a schema. CREATE TEMPORARY TABLE does not raise an error.
"The 10 inserted rows go into the temporary table, but DROP TABLE drops the permanent ORDERS table that has the same name" … The documentation says that queries and other operations on the table within the session act on the temporary table. DDL is no exception, so DROP TABLE drops the temporary table.
"The 10 inserted rows go into the permanent ORDERS, and DROP TABLE drops the temporary table that was created in the session" … The temporary table hides the table of the same name within the session, so the INSERT also goes into the temporary table. DML does not go to the permanent table while DDL goes to the temporary one.
Key point
The documentation says this precedence needs particular care when you drop a table in a session and restore it with Time Travel, or when you create a table with CREATE OR REPLACE. CREATE OR REPLACE drops and re-creates the table, so if a temporary table of the same name remains, it may not act on the table you intended.