SnowPro Advanced: Security Engineer Question 183
Single answerLeverage features including transient tables, temporary tables, and auto-drop configurationsA security engineering team manages a Snowflake environment used by analysts to investigate suspected fraud. During investigations, analysts regularly create intermediate tables containing sensitive customer data. The security team wants to reduce long-term data exposure and storage of these working datasets while still allowing analysts to use standard SQL workflows during their active sessions. They also want to avoid retaining unnecessary historical data where possible. Which approach BEST meets these requirements?
- A
Require analysts to create temporary tables for investigation working data, because these tables are session-scoped and are automatically dropped at the end of the session.
- B
Require analysts to create permanent tables with a short data retention period, because permanent tables can be configured to auto-drop when the session ends.
- C
Require analysts to create transient tables for all working data, because transient tables are automatically dropped at the end of each session and do not support Fail-safe.
- D
Create secure views instead of tables for investigation working data, because secure views automatically remove persisted intermediate results when a session ends.
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use temporary tables for analyst-created investigative working data. In Snowflake, temporary tables exist only within the session in which they are created and are automatically purged when the session ends. This directly supports the security goal of minimizing the persistence of sensitive intermediate data while preserving normal SQL-based analysis workflows. Transient tables can also be useful when reduced historical retention is desired because they do not have Fail-safe, but they persist until explicitly dropped and therefore do not meet the auto-drop requirement in this scenario. Permanent tables are intended for durable storage and include Fail-safe, making them less appropriate for short-lived sensitive working data. Snowflake documentation distinguishes these table types clearly: temporary tables are session-bound and auto-dropped; transient tables persist beyond sessions but omit Fail-safe; permanent tables provide the highest durability features. For security engineering, selecting the shortest appropriate persistence model is a key best practice for reducing unnecessary data exposure.
- A. Correct.
Correct. Temporary tables are designed for session-specific work and are automatically dropped at the end of the user session. This makes them well suited for short-lived investigative datasets that should not persist longer than necessary. They support normal SQL operations during the session, which aligns with analyst workflows. From a security perspective, this minimizes the risk of sensitive intermediate data lingering in the environment after the analyst disconnects.
- B. Incorrect.
Incorrect. Permanent tables are not auto-dropped at session end. While permanent tables can have Time Travel retention configured, that does not make them ephemeral, and they still include Fail-safe. They are intended for durable data storage, which conflicts with the requirement to minimize long-term persistence of sensitive working datasets.
- C. Incorrect.
Incorrect. Transient tables do not have Fail-safe, which can reduce long-term storage overhead compared to permanent tables, but they are not automatically dropped at the end of a session. They persist until explicitly dropped, so they do not best satisfy the requirement to automatically remove working data after active use. This option mixes a true statement about Fail-safe with a false statement about session-based auto-drop.
- D. Incorrect.
Incorrect. Secure views protect the exposure of underlying data definitions and can help with controlled data access, but they do not replace the need for temporary working storage in this scenario. Also, secure views do not provide session-end auto-drop behavior for analyst-created intermediate datasets. This distractor reflects a common misconception that secure objects inherently solve data lifecycle requirements.