Nest 0 Cart
Nest Account
Popular Exams
All Categories...
Nest
Nest 0
Contact
Log In / Sign Up
Copyright 2024 © store. All rights reserved.
Home Snowflake SnowPro Advanced Data Engineer

Snowflake SnowPro Advanced Data Engineer Exam Practice Questions (P. 4)

  • Full Access (143 questions)
  • One Year of Premium Access
  • Access to one million comments
  • Seamless ChatGPT Integration
  • Ability to download PDF files
  • Anki Flashcard files for revision
  • No Captcha & No AdSense
  • Advanced Exam Configuration
Get Contributor Access
Question #16
A company is building a dashboard for thousands of Analysts. The dashboard presents the results of a few summary queries on tables that are regularly updated. The query conditions vary by topic according to what data each Analyst needs. Responsiveness of the dashboard queries is a top priority, and the data cache should be preserved.
How should the Data Engineer configure the compute resources to support this dashboard?
  1. A
    Assign queries to a multi-cluster virtual warehouse with economy auto-scaling. Allow the system to automatically start and stop clusters according to demand.
  2. B
    Assign all queries to a multi-cluster virtual warehouse set to maximized mode. Monitor to determine the smallest suitable number of clusters.
  3. C
    Create a virtual warehouse for every 250 Analysts. Monitor to determine how many of these virtual warehouses are being utilized at capacity.
  4. D
    Create a size XL virtual warehouse to support all the dashboard queries. Monitor query runtimes to determine whether the virtual warehouse should be resized.

Correct Answer:
B
chatbot-img GPT-4o - Answer
For optimal responsiveness in a scenario with a high volume of diverse queries and continuous updates, as seen in large-scale dashboards, setting up a multi-cluster virtual warehouse in maximized mode is crucial. This configuration pre-starts all designated clusters, ensuring immediate maximum resource availability, thus reducing response time significantly. This setup is particularly advantageous when user demand is constant and substantial, which aligns perfectly with the requirement for fast, reliable access for a large number of analysts without delays in scaling up from an economy mode. This ensures that responsiveness, a critical priority in this context, is maintained.

Question #17
A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
How long will Snowflake use the persisted query results from the result cache, provided that the underlying data has not changed?
  1. A
    12 hours
  2. B
    24 hours
  3. C
    14 days
  4. D
    31 days

Correct Answer:
B
chatbot-img GPT-4o - Answer
Right, the retention period for persisted query results in Snowflake is set at 24 hours. This means if the data hasn't changed and the exact same query is re-run within this period, Snowflake will retrieve results from the cache, not performing the query again. However, it's essential to note that each usage within these 24 hours resets the timer, extending the potential caching period up to a maximum of 31 days, assuming regular, repeated use of the exact same query. This setup is designed to optimize performance by minimizing redundant compute operations on unchanged data.

Question #18
A Data Engineer ran a stored procedure containing various transactions. During the execution, the session abruptly disconnected, preventing one transaction from committing or rolling back. The transaction was left in a detached state and created a lock on resources.
What step must the Engineer take to immediately run a new transaction?
  1. A
    Call the system function SYSTEM$ABORT_TRANSACTION.
  2. B
    Call the system function SYSTEM$CANCEL_TRANSACTION.
  3. C
    Set the LOCK_TIMEOUT to FALSE in the stored procedure.
  4. D
    Set the TRANSACTION_ABORT_ON_ERROR to TRUE in the stored procedure.

Correct Answer:
A
chatbot-img GPT-4o - Answer
To address the issue of a transaction left in a detached state due to an abrupt disconnection, the recommended approach is to manually intervene and utilize the SYSTEM$ABORT_TRANSACTION function. This action will effectively abort the transaction, releasing any locked resources it was holding. This operation is vital to prevent prolonged resource locking, which can negatively impact other transactions relying on those resources. It’s indeed critical for maintaining system availability and efficiency in managing data concurrency issues.

Question #19
A database contains a table and a stored procedure defined as:

The log_table is initially empty and a Data Engineer issues the following command:
CALL insert_log(NULL::VARCHAR);
No other operations are affecting the log_table.
What will be the outcome of the procedure call?
  1. A
    The log_table contains zero records and the stored procedure returned 1 as a return value.
  2. B
    The log_table contains one record and the stored procedure returned 1 as a return value.
  3. C
    The log_table contains one record and the stored procedure returned NULL as a return value.
  4. D
    The log_table contains zero records and the stored procedure returned NULL as a return value.

Correct Answer:
D

Question #20
When would a Data Engineer use TABLE with the FLATTEN function instead of the LATERAL FLATTEN combination?
  1. A
    When TABLE with FLATTEN requires another source in the FROM clause to refer to.
  2. B
    When TABLE with FLATTEN requires no additional source in the FROM clause to refer to.
  3. C
    When the LATERAL FLATTEN combination requires no other source in the FROM clause to refer to.
  4. D
    When TABLE with FLATTEN is acting like a sub-query executed for each returned row.

Correct Answer:
B
chatbot-img GPT-4o - Answer
Using TABLE with the FLATTEN function is appropriate when no additional source is needed in the FROM clause. This option simplifies your SQL query structure, enabling direct flattening of compound values into separate rows without the complexity of joining additional data from other tables. The FLATTEN function's output is treated as a standalone source without necessitating references to external sources, thus keeping queries efficient and straightforward. This approach greatly enhances data processing speed by avoiding unnecessary joins.

Previous Questions Next Questions

All Pages