SnowflakeProfessional levelADA-C01Page 5 of 6

ADA-C01 exam dumps: questions 401 to 500 of 565

Page 5 of the free ADA-C01 question bank for the SnowPro® Advanced: Administrator exam. Questions 401 to 500 are listed below, the first 5 in full with answers and explanations. Back to page 1 for the exam overview and FAQ.

Question bank last updated April 2026

Start timed practice exam Questions 401 to 500

Free ADA-C01 practice questions

Questions 401 to 405 of 565

Pick an answer before you open the explanation. Each question also has its own page with a permalink.

ADA-C01 Question 401

Single answerLatency and data retention considerations

A Snowflake administrator manages a production database that contains several large transient staging tables used only during daily ETL processing. The tables are loaded overnight and truncated after downstream loads complete. The company wants to reduce storage cost and shorten the time required for operational recovery activities, but it still needs production fact tables in the same database to support 7 days of Time Travel for accidental update recovery. Which action should the administrator take to best meet these requirements?

A
Set DATA_RETENTION_TIME_IN_DAYS = 0 on the transient staging tables, while keeping the production fact tables at 7 days
B
Convert the entire database to transient and set DATA_RETENTION_TIME_IN_DAYS = 7 at the database level
C
Keep all tables permanent, but disable Fail-safe on the staging tables to reduce storage cost and recovery latency
D
Set MAX_DATA_EXTENSION_TIME_IN_DAYS = 0 on the staging tables so that Time Travel history is removed immediately after truncation

Show answer and explanation

Correct answer: A

Explanation

The best answer is to use object-appropriate table types and retention settings. In Snowflake, permanent tables support Time Travel and Fail-safe, while transient tables support Time Travel but do not include Fail-safe. For staging data that is short-lived and reloadable, transient tables with DATA_RETENTION_TIME_IN_DAYS = 0 are a common best practice to reduce storage costs associated with historical data and to minimize unnecessary recovery overhead. For production fact tables that require recovery from accidental changes, keeping them as permanent tables with 7 days of Time Travel preserves the needed protection. This scenario tests understanding of retention design, the differences between permanent and transient objects, and the fact that Fail-safe is not user-disableable for permanent tables. Relevant Snowflake documentation includes guidance on table types, Time Travel, Fail-safe, and object parameters such as DATA_RETENTION_TIME_IN_DAYS and MAX_DATA_EXTENSION_TIME_IN_DAYS.

A. Correct.
Correct. Transient tables do not have Fail-safe, which reduces storage cost compared to permanent tables, and setting DATA_RETENTION_TIME_IN_DAYS = 0 on the staging tables minimizes Time Travel retention for objects that do not need recovery. At the same time, permanent production fact tables can remain configured with 7 days of Time Travel. This approach applies retention settings at the object level to align cost, recovery needs, and operational behavior.
B. Incorrect.
Incorrect. Converting the entire database to transient would remove Fail-safe protection for all objects in that database, including production fact tables. In addition, setting a 7-day retention at the database level would not help reduce retention-related storage overhead for the staging tables. This is too broad and does not satisfy the requirement to preserve stronger recovery capabilities for production data while minimizing overhead for staging data.
C. Incorrect.
Incorrect. Fail-safe cannot be disabled on permanent tables. Permanent tables include Fail-safe by design, while transient tables and temporary tables do not. This option reflects a common misconception that Fail-safe is independently configurable per permanent table.
D. Incorrect.
Incorrect. MAX_DATA_EXTENSION_TIME_IN_DAYS is related to how Snowflake can extend retention for streams to avoid staleness, not a control to immediately remove Time Travel history after DML or truncation. It does not replace DATA_RETENTION_TIME_IN_DAYS for managing object-level retention and would not achieve the stated goal.

ADA-C01 Question 402

Single answerLatency and data retention considerations

A 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.

ADA-C01 Question 403

Single answerLatency for historical views

An administrator is building an operations dashboard that must display near-real-time warehouse load, recent query activity, and task failures across the account. The first design uses ACCOUNT_USAGE views, but the dashboard often appears 1-3 hours behind actual activity, causing delayed incident response. The administrator needs the best source for lower-latency historical monitoring data while still querying with SQL. Which approach should the administrator choose?

A
Query the corresponding Information Schema table functions, such as QUERY_HISTORY and WAREHOUSE_LOAD_HISTORY, instead of relying on ACCOUNT_USAGE views for the dashboard
B
Continue using ACCOUNT_USAGE views, but run the dashboard queries on a larger virtual warehouse to reduce metadata latency
C
Create a materialized view on top of SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY to force faster refresh of the underlying historical data
D
Enable search optimization on the SNOWFLAKE database so ACCOUNT_USAGE views refresh more quickly

Show answer and explanation

Correct answer: A

Explanation

The key distinction is between ACCOUNT_USAGE historical views and lower-latency monitoring access patterns. SNOWFLAKE.ACCOUNT_USAGE is useful for reporting, trending, auditing, and account-level history, but many of its views are not intended for real-time operational dashboards because they can lag behind current activity. For near-real-time administrative monitoring, Snowflake documentation recommends using Information Schema table functions and related monitoring functions where available, such as QUERY_HISTORY and TASK_HISTORY. Increasing warehouse size, adding search optimization, or building a materialized view may improve query performance in some designs, but none of these changes reduce the inherent latency of when ACCOUNT_USAGE data becomes available. Best practice is to choose the metadata source based on freshness requirements: Information Schema for more recent operational visibility, ACCOUNT_USAGE for broader historical analysis and governance reporting.

A. Correct.
Correct. For operational monitoring that needs lower latency, the Information Schema table functions are the better choice. Snowflake's ACCOUNT_USAGE views are historical and can have latency before new records appear, whereas Information Schema functions such as QUERY_HISTORY, TASK_HISTORY, and related monitoring functions are intended for more recent activity with lower latency. This is the practical design choice when a SQL-based dashboard must reflect recent account activity more quickly.
B. Incorrect.
Incorrect. Warehouse size can improve execution speed of the dashboard query itself, but it does not change when metadata becomes available in ACCOUNT_USAGE. The issue in the scenario is data freshness latency in the historical view, not query compute performance.
C. Incorrect.
Incorrect. A materialized view can only refresh based on data already visible from its source. It cannot make SNOWFLAKE.ACCOUNT_USAGE publish new metadata sooner. This option reflects a common misconception that downstream objects can reduce source-system latency.
D. Incorrect.
Incorrect. Search optimization can improve selective query performance for supported workloads, but it does not alter the refresh schedule or publication latency of ACCOUNT_USAGE historical metadata. It addresses access speed, not monitoring-data freshness.

ADA-C01 Question 404

Single answerLatency for historical views

A Snowflake administrator is troubleshooting a monitoring dashboard that relies on ACCOUNT_USAGE historical views to report warehouse credit consumption and query activity. Operations complains that the dashboard often appears incomplete for the most recent time period, especially after workload spikes. The administrator needs to set correct expectations and reduce false incident escalations. Which action is the MOST appropriate?

A
Continue using ACCOUNT_USAGE views for near-real-time alerting, because these views are updated immediately after each query completes
B
Switch the dashboard to ORGANIZATION_USAGE views, because they have lower latency than ACCOUNT_USAGE for recent operational monitoring
C
Redesign the dashboard to account for ACCOUNT_USAGE view latency and use lower-latency Information Schema table functions where near-real-time visibility is required
D
Enable Search Optimization Service on the SNOWFLAKE database so historical views refresh faster for recent rows

Show answer and explanation

Correct answer: C

Explanation

The key concept is that Snowflake historical views, especially those in SNOWFLAKE.ACCOUNT_USAGE, are not guaranteed to be real-time. They are widely used for administration, governance, and cost analysis, but administrators must understand and communicate their documented latency characteristics. When teams need fresher operational visibility, Snowflake documentation generally points to Information Schema sources, including relevant table functions for active and recent activity, as the better fit. In practice, the right solution is not to force ACCOUNT_USAGE into a real-time monitoring role, but to design dashboards and alerting around the intended freshness of each metadata source. This is a common SnowPro Advanced Administrator scenario: choosing the correct metadata interface based on latency and operational requirements rather than relying on a single historical source for every use case.

A. Incorrect.
Incorrect. ACCOUNT_USAGE views are historical views in the shared SNOWFLAKE database and are not intended for immediate, real-time monitoring. Many of these views have documented latency before new records appear. Using them for near-real-time alerting commonly causes missing or incomplete recent results, which is exactly the issue in the scenario.
B. Incorrect.
Incorrect. ORGANIZATION_USAGE is also intended for historical reporting and governance across accounts, not lower-latency operational monitoring. It does not solve the recent-data delay problem and may introduce additional scope or access considerations rather than improving freshness.
C. Correct.
Correct. This is the best administrative response. ACCOUNT_USAGE views are excellent for historical analysis, trending, auditing, and chargeback, but they can have latency before data becomes visible. For operational dashboards or alerting that require fresher data, Snowflake best practice is to use the appropriate Information Schema table functions or views where available, since they generally provide lower-latency access to current activity. The dashboard should also clearly communicate that historical views may lag.
D. Incorrect.
Incorrect. Search Optimization Service improves selective query performance on user tables in supported scenarios; it does not change how quickly Snowflake populates metadata or historical views in the SNOWFLAKE shared database. This option reflects a common misconception between query performance optimization and metadata refresh latency.

ADA-C01 Question 405

Single answerManage costs for virtual warehouses

A Snowflake administrator is reviewing compute spend and finds that a BI virtual warehouse named BI_WH incurs high costs overnight. Query history shows many short dashboard queries between 6:00 AM and 11:00 PM, but only a few sporadic queries after hours. Users also report that dashboards must remain responsive during business hours. The warehouse is currently configured as MEDIUM, AUTO_SUSPEND = 600, AUTO_RESUME = TRUE, and MIN_CLUSTER_COUNT = MAX_CLUSTER_COUNT = 1. Which change would best reduce compute cost while preserving the required user experience?

A
Reduce AUTO_SUSPEND to 60 seconds so the warehouse stops sooner during low-activity periods while still automatically resuming when queries arrive
B
Disable AUTO_RESUME so that users manually restart the warehouse only when needed, preventing unnecessary resumes overnight
C
Increase the warehouse size to LARGE so queries finish faster and total credits consumed decrease
D
Convert the warehouse to a multi-cluster warehouse with MAX_CLUSTER_COUNT = 3 to improve concurrency and reduce nightly costs

Show answer and explanation

Correct answer: A

Explanation

The most effective cost-control action is to reduce idle compute time while keeping the warehouse easy for BI tools and dashboard users to access. Snowflake best practices for warehouse cost management emphasize configuring AUTO_SUSPEND and AUTO_RESUME appropriately so warehouses do not run unnecessarily between bursts of activity. In this case, a 600-second auto-suspend setting leaves the warehouse running for long periods after brief overnight queries. Lowering AUTO_SUSPEND to 60 seconds is a practical optimization because it reduces wasted runtime while preserving seamless access through AUTO_RESUME. Alternatives such as disabling AUTO_RESUME harm usability, increasing warehouse size targets the wrong problem, and enabling multi-cluster addresses concurrency rather than idle-cost reduction. Relevant Snowflake documentation includes guidance on virtual warehouse cost control, warehouse billing behavior, and using AUTO_SUSPEND/AUTO_RESUME to minimize unnecessary compute consumption.

A. Correct.
Correct. Snowflake bills warehouses per-second with a minimum charge when a warehouse starts or resumes. In this scenario, the key issue is that the warehouse stays running for up to 10 minutes after the last query because AUTO_SUSPEND is set to 600 seconds. Since overnight usage is only sporadic, lowering AUTO_SUSPEND to 60 seconds will reduce idle runtime and therefore lower compute cost. AUTO_RESUME = TRUE preserves the user experience because queries can still start the warehouse automatically when needed.
B. Incorrect.
Incorrect. Disabling AUTO_RESUME may reduce some automatic restarts, but it creates a poor user experience because dashboard users would encounter failures or delays until someone manually resumes the warehouse. The scenario explicitly requires dashboards to remain responsive during business hours. This option shifts operational burden to users and administrators rather than optimizing cost efficiently.
C. Incorrect.
Incorrect. Increasing the warehouse to LARGE generally increases the credit consumption rate per unit of runtime. While larger warehouses can complete some workloads faster, that does not make this the best choice here because the main identified waste is idle time overnight, not insufficient compute power during query execution. Upsizing would likely increase cost rather than reduce it.
D. Incorrect.
Incorrect. Multi-cluster warehouses are designed to handle concurrency by adding clusters when many queries run at the same time. The scenario does not indicate concurrency bottlenecks overnight; instead, it describes sparse after-hours usage. Adding multi-cluster capability would not address the idle-cost problem and could increase spending if additional clusters are used.

Timed practice exam

Take a ADA-C01 practice test under exam conditions

65 questions in 115 minutes, drawn from this bank, with a score report and a per-question review when you finish.

Start timed exam

ADA-C01 practice questions 401 to 500 of 565

Every question has a page with the answer and explanation. Numbers are stable, so you can bookmark or share them. The bank is split into 6 pages of up to 100 questions.