COF-C03 Question 99
Single answerPermanentA data engineering team stores critical finance tables in Snowflake and must retain the data for 7 years for audit purposes. The team also wants protection against accidental drops and the ability to recover data when needed. A developer suggests creating the tables as TRANSIENT to reduce storage costs because Snowflake Time Travel will still allow recovery. Which action best meets the retention and recovery requirements?
- A
Create the finance tables as PERMANENT tables and configure the required data retention period so Time Travel and Fail-safe protections apply
- B
Create the finance tables as TRANSIENT tables and rely on Fail-safe for long-term recovery after the tables are dropped
- C
Create the finance tables as TEMPORARY tables because they support recovery during the user session without incurring Fail-safe storage
- D
Create the finance tables as EXTERNAL TABLES because the underlying cloud storage provides all Snowflake recovery capabilities automatically
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use PERMANENT tables. In Snowflake, permanent tables are the default table type and are designed for durable business data. They support Time Travel for recovering historical data and dropped objects within the configured retention period, followed by Fail-safe for an additional recovery window managed by Snowflake. By contrast, TRANSIENT tables do not include Fail-safe, and TEMPORARY tables are session-based and unsuitable for persistent regulated data. EXTERNAL TABLES only expose externally stored data and do not provide Snowflake-managed Fail-safe for that underlying data. Snowflake documentation on table types and data protection distinguishes permanent, transient, and temporary objects specifically by persistence and recovery capabilities, making permanent tables the correct design choice for critical finance datasets.
- A. Correct.
Correct. Permanent tables are the appropriate choice for critical long-lived data that requires the highest level of recoverability in Snowflake. Permanent tables support Time Travel and, after the Time Travel period ends, Fail-safe. This aligns with audit-oriented recovery requirements better than transient or temporary objects. The retention requirement for business records may also involve keeping the data itself for years, which is separate from object recovery windows; permanent tables are the standard object type for such critical data.
- B. Incorrect.
Incorrect. Transient tables support Time Travel but do not have Fail-safe protection. This is a common misconception because users often assume all Snowflake table types have the same recovery features. Choosing transient may reduce storage costs by eliminating Fail-safe, but that directly weakens recovery protection for accidental drops or data changes.
- C. Incorrect.
Incorrect. Temporary tables are session-scoped and are removed at the end of the session. They are intended for short-lived working data, not regulated finance data requiring multi-year retention or administrative recovery controls. They also are not suitable for persistent audit datasets.
- D. Incorrect.
Incorrect. External tables reference data stored outside Snowflake and do not provide the same internal storage and recovery model as permanent Snowflake tables. Recovery capabilities depend largely on the external storage platform and configuration, not Snowflake Time Travel and Fail-safe for internal table data.