Free Access Snowflake.DAA-C01.v2025-12-20.q172 Practice Test (Page 22)

Question 101

You are designing a data pipeline that requires executing a sequence of SQL statements conditionally based on the success or failure of previous steps. You want to encapsulate this logic within a stored procedure in Snowflake. Which of the following approaches can be used to achieve this conditional execution within a stored procedure, and what are their key differences regarding error handling and control flow?

A.Using 'TRY...CATCH' blocks within the stored procedure to handle exceptions and control the flow based on error codes. Each SQL statement is wrapped in a TRY block, and if an error occurs, the CATCH block is executed to log the error and potentially execute alternative steps. This ensures that the procedure continues to execute even if some steps fail.

B.Using 'IF...THEN...ELSE' statements to check the 'SQLCODE' or 'SQLSTATE after each SQL statement. If the 'SQLCODE indicates an error, the procedure can execute alternative steps or terminate gracefully. This allows for fine-grained control over the execution flow based on the success or failure of individual statements.

C.Using Javascript as the stored procedure language and leveraging its built-in 'try...catcW blocks. SQL statements are executed using the Snowflake Javascript API, and Javascript's error handling mechanisms are used to manage exceptions and control the flow. This offers more flexibility in handling complex error scenarios and integrating with external systems.

D.Utilizing Snowflake's Task feature to define dependencies between different SQL operations. Each SQL statement is defined as a separate Task, and dependencies are created to ensure that Tasks are executed only if their preceding Tasks succeed. This provides a robust and scalable way to manage complex data pipelines with conditional execution.

E.All of the above approaches can be used, but they differ in terms of complexity, performance, and error handling capabilities.

Question 102

How do row access policies and Dynamic Data Masking impact the creation of dashboards concerning data visibility and security?

A.Dynamic Data Masking doesn't influence dashboard creation.

B.They enhance data visibility for all users without limitations.

C.Row access policies limit data visibility based on user privileges.

D.Both policies restrict data visibility for better security.

Question 103

When maintaining reports and dashboards, why is it essential to build automated and repeatable tasks?

A.Automated tasks increase dashboard management complexity.

B.Repeatable tasks hinder data updates in dashboards.

C.They ensure inconsistency in reports and dashboards.

D.Automated tasks reduce manual efforts, ensuring consistency.

Question 104

You have a Snowflake table called 'PRODUCT SALES' with columns 'PRODUCT ID (INT), 'SALE DATE' (DATE), and 'SALES AMOUNT' You want to implement a data integrity rule to prevent duplicate records based on 'PRODUCT ID and 'SALE DATE. Which of the following methods provides the most effective way to achieve this in Snowflake, and why?

A.Create a view that filters out duplicate records using 'ROW NUMBER()' and partitioning by 'PRODUCT ID and 'SALE DATE. This guarantees data integrity during query execution.

B.Create a stored procedure that runs periodically to identify and delete duplicate records based on 'PRODUCT and 'SALE DATE'. This approach fixes integrity issues reactively.

C.Add a composite UNIQUE constraint on 'PRODUCT ID' and 'SALE DATE. Snowflake will automatically prevent the insertion of duplicate rows during data loading or insertion.

D.Implement data validation within your ETL pipeline before loading data into Snowflake to prevent duplicates from entering the table. This approach keeps the table clean from the start.

E.Create a user defined function (UDF) that checks for the existance of data before insert. If data already exist, don't insert it.

Question 105

You're working with a large dataset containing website user activity, including 'session_id', 'user_id', 'timestamp', and 'page_view'. You suspect bot activity is skewing your engagement metrics. Bots tend to have very short session durations and high page view counts within those sessions. Which of the following Snowflake SQL queries, used in combination, would be MOST effective in identifying and flagging potential bot sessions, considering performance on a large dataset? Assume you have access to Snowflake's statistical functions.