A permanent table and temporary table have the same name, TBL1, in a schema. What will happen if a user executes select * from TBL1 ;?

In Snowflake, when a temporary table and a permanent table share the same name within the same schema, the temporary table takes precedence for all operations within the session that created it. Temporary objects are session-scoped, meaning they are only visible and accessible to the user session in which they were created. This design allows users to create their own versions of tables for specific tasks without conflicting with or altering the underlying permanent objects. Therefore, the SELECT statement will query the data from the temporary table TBL1.

B. This is incorrect. Snowflake's name resolution logic prioritizes session-level objects, like temporary tables, over permanent ones.
C. This is incorrect. Snowflake explicitly permits a temporary table to have the same name as a permanent table in the same schema because their scopes are different.
D. This is incorrect. Precedence is determined by the object's scope (session vs. permanent), not by its creation timestamp.

1. Snowflake Documentation. (2024). Temporary and Transient Tables | Understanding Temporary Tables. Retrieved from https://docs.snowflake.com/en/user-guide/tables-temp-transient.

Reference Point: The section "Understanding Temporary Tables" explicitly states: "When a temporary table has the same name as another table in the same schema

all queries and operations performed on the table in the session affect the temporary table

not the other table."

2. Snowflake Documentation. (2024). Object Name Resolution. Retrieved from https://docs.snowflake.com/en/sql-reference/name-resolution.

Reference Point: The documentation on name resolution clarifies that session-specific objects are resolved before permanent objects within the same schema context.