ADA-C01 Question 402
Single answerLatency and data retention considerationsA financial services company uses Snowflake Enterprise Edition for a production database that supports near-real-time reporting. The largest fact table is updated continuously throughout the business day, and analysts have complained that some dashboard queries are slower immediately after large UPDATE and DELETE operations. At the same time, the company wants to reduce storage costs caused by historical data versions, but auditors require the ability to recover data mistakes for up to 7 days. The administrator needs to choose a configuration that balances query latency and retention requirements for this production database. Which action should the administrator take?
- A
Set DATA_RETENTION_TIME_IN_DAYS to 0 on the production database to eliminate historical versions and improve query latency immediately.
- B
Keep DATA_RETENTION_TIME_IN_DAYS at 7 for the production database and rely on automatic services to manage old versions, because reducing retention below the audit requirement would violate recovery needs.
- C
Increase DATA_RETENTION_TIME_IN_DAYS to 90 so Snowflake has more historical metadata available to optimize pruning after DML operations.
- D
Disable Fail-safe on the production database and keep DATA_RETENTION_TIME_IN_DAYS at 7 to reduce storage costs while preserving 7 days of recovery.
Show answer and explanation
Correct answer: B
Explanation
The key requirement is to preserve recoverability for 7 days while understanding the storage and latency implications of retained historical versions. In Snowflake, UPDATE and DELETE operations rewrite data into new micro-partitions; older versions remain available for Time Travel for the configured retention period on permanent tables. Those retained versions can increase storage usage until they age out of Time Travel and then pass through Fail-safe. However, an administrator should not lower DATA_RETENTION_TIME_IN_DAYS below explicit audit or recovery requirements. For Enterprise Edition, up to 90 days of Time Travel can be configured for eligible permanent objects, but that would increase storage and is unnecessary here because the requirement is specifically 7 days. Also, Fail-safe for permanent data is system-managed and cannot be turned off. Best practice is to set the minimum retention that still satisfies business, audit, and recovery objectives; in this scenario, that is 7 days.
- A. Incorrect.
Incorrect. Setting DATA_RETENTION_TIME_IN_DAYS to 0 would reduce or eliminate Time Travel retention, which may lower storage associated with retained historical versions, but it would not meet the stated auditor requirement to recover data for up to 7 days. In addition, for permanent objects in Enterprise Edition, production recovery requirements commonly rely on Time Travel, and query latency after heavy DML is not solved simply by forcing retention to 0 when the business explicitly needs 7 days of recoverability.
- B. Correct.
Correct. A 7-day Time Travel setting aligns with the stated audit requirement. In Snowflake, updates and deletes create new micro-partition versions, and historical versions are retained for the configured Time Travel period before Fail-safe applies. While retained history contributes to storage, administrators should not reduce retention below business and compliance recovery requirements just to save cost. This option correctly prioritizes the 7-day recovery need while acknowledging that Snowflake manages retained versions automatically.
- C. Incorrect.
Incorrect. Increasing retention to 90 days is not supported for standard Time Travel on Enterprise Edition databases and would significantly increase historical storage if it were possible. More retention does not improve query pruning or reduce latency after DML. This distractor reflects a misconception that additional history somehow helps performance tuning; in reality, longer retention increases storage exposure rather than improving query speed.
- D. Incorrect.
Incorrect. Fail-safe cannot be disabled for permanent tables in Snowflake. It is a system-managed recovery period that follows Time Travel for permanent objects. This option is therefore not a valid administrative action. It is a plausible distractor because candidates often confuse configurable Time Travel retention with non-configurable Fail-safe behavior.