Snowpro Core · Free Practice Question Medium
Question 49
Consider a database with the name MARKETING. The database has a table called CUSTOMER in the PUBLIC schema. The CUSTOMER table has 10,000 rows. The PUBLIC schema also has a view called CUSTOMER_COUNT with the following definition.
“SELECT COUNT(*) FROM MARKETING.PUBLIC.CUSTOMER;”
You create a temporary table with the same name, i.e., CUSTOMER, in the PUBLIC schema of the MARKETING database. The temporary table has zero rows.
Which of the following correctly describes the results when the view is queried?
Select two answers.
-
A
A "SELECT * FROM CUSTOMER_COUNT;" query will return zero as the result when executed in the same session in which the temporary table was created.
-
B
A "SELECT * FROM CUSTOMER_COUNT;" query will return zero rows as the result executed in a new session.
-
C
A "SELECT * FROM CUSTOMER_COUNT;" query will return 10,000 as the result when executed in a new session.
-
D
A "SELECT * FROM CUSTOMER_COUNT;" query will return 10,000 as the result when executed in the same session in which the temporary table was created.
Reveal correct answers
Correct answers: A, C
Explanation
If a temporary table is created in a schema with the same name as a permanent (or transient) table, the temporary table effectively hides the permanent table in that session. Queries and other operations during the session will affect only the temporary table.
This behavior can affect the views as well. In the same session where a temporary table was created, a temporary table can hide the permanent table used by a view, resulting in unexpected results. However, the view is unaffected when queried from a different session because temporary tables are limited to the session where they are created.
https://docs.snowflake.com/en/user-guide/tables-temp-transient#potential-naming-conflicts-with-other-table-types
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
