SnowPro Advanced: Security Engineer Question 182
Select 3Leverage features including transient tables, temporary tables, and auto-drop configurationsA security engineering team is redesigning how a Snowflake environment handles sensitive intermediate data produced by nightly fraud-detection jobs. The team must minimize long-term data retention exposure and storage costs while still allowing each analyst session to use scratch data during investigations. They also want non-production staging data to avoid Fail-safe charges because the data can be recreated. Which combination of Snowflake objects and configurations best meets these requirements?
- A
Store analyst session scratch data in temporary tables, because they are session-scoped and automatically purged at the end of the session.
- B
Store recreatable non-production staging data in transient tables, because they do not have a Fail-safe period.
- C
Configure permanent tables with DATA_RETENTION_TIME_IN_DAYS = 0 to eliminate both Time Travel and Fail-safe for recreatable staging data.
- D
Use temporary tables for shared staging datasets across multiple user sessions, because temporary tables remain available until explicitly dropped by any authorized user.
- E
Rely on Snowflake auto-drop behavior for temporary tables to reduce residual data exposure when users disconnect or sessions end.
Show answer and explanation
Correct answers: A, B, E
Explanation
The best solution is to use temporary tables for per-session scratch data and transient tables for recreatable staging data that should persist beyond a single session but avoid Fail-safe storage and recovery overhead. In Snowflake, temporary tables are session-specific and automatically dropped when the session ends, making them useful for minimizing exposure of sensitive intermediate results. Transient tables persist until dropped but do not have a Fail-safe period, which is why they are commonly used for staging or non-critical data that can be rebuilt. A key exam trap is confusing DATA_RETENTION_TIME_IN_DAYS with Fail-safe behavior: retention settings affect Time Travel, but they do not remove Fail-safe from permanent tables. Snowflake documentation on table types and data retention distinguishes permanent, transient, and temporary tables, including their differences in persistence, visibility, Time Travel, and Fail-safe.
- A. Correct.
Correct. Temporary tables are designed for session-specific work and are automatically dropped at the end of the session. This makes them well suited for analyst scratch space containing sensitive intermediate data that should not persist beyond the investigation session. Because they are session-bound, they reduce the risk of leftover objects remaining accessible after the session ends.
- B. Correct.
Correct. Transient tables are appropriate for data that must persist beyond a single session but does not require the protection of Fail-safe. They support Time Travel with limited retention settings but do not include the 7-day Fail-safe period that applies to permanent tables. For recreatable staging data in non-production environments, this is a common cost and retention optimization.
- C. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS = 0 on a permanent table can reduce or eliminate Time Travel retention, but permanent tables still have Fail-safe. This option reflects a common misconception that retention settings remove Fail-safe from permanent objects. To avoid Fail-safe, the object type must be transient rather than permanent.
- D. Incorrect.
Incorrect. Temporary tables are not intended for shared datasets across multiple sessions. They are visible only within the session in which they are created and are automatically dropped when that session ends. A shared staging dataset that needs to persist across sessions should use a different object type, such as a transient table if Fail-safe is not needed.
- E. Correct.
Correct. Snowflake automatically drops temporary tables when the session ends, which is effectively an auto-drop behavior for session-scoped data. This helps reduce residual sensitive data exposure without requiring separate cleanup jobs, provided the use case is truly session-local.