SnowPro Associate: Platform Question 72
Single answer● Run codeA data engineering team wants to automate a simple data quality check directly in Snowflake. They need to run code that counts rows in a staging table and returns a status message such as 'PASS' or 'FAIL' based on whether the row count is above a threshold. The solution must run inside Snowflake without requiring an external application server. Which Snowflake feature is the best fit for this requirement?
- A
Create a stored procedure using Snowflake Scripting or a supported handler language, and execute it in Snowflake
- B
Create an external function because all procedural logic in Snowflake must run outside the platform
- C
Use a materialized view because it can execute conditional code and return custom status messages
- D
Create a file format object and use it to run the row-count logic on staged files
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use a stored procedure. In Snowflake, stored procedures are the primary mechanism for running procedural code inside the platform. They support control-flow logic, variable handling, SQL execution, and returned values, which makes them well suited for tasks like row-count validation and status reporting. By contrast, external functions rely on services outside Snowflake, materialized views are for query result optimization rather than procedural logic, and file formats only define parsing rules for staged data. Snowflake documentation on stored procedures and Snowflake Scripting describes these use cases and positions stored procedures as the standard approach for in-platform code execution.
- A. Correct.
Correct. A stored procedure is designed for running procedural code in Snowflake. It can contain SQL logic, variables, control flow, and return values such as a status message. Snowflake supports stored procedures using Snowflake Scripting and supported languages such as Java, Python, and Scala for handlers, depending on the feature. This is the most appropriate way to run the described logic entirely within Snowflake.
- B. Incorrect.
Incorrect. External functions call code outside Snowflake through a remote service, typically via API integration. They are useful when logic must execute in an external system, but the scenario specifically requires the solution to run inside Snowflake without depending on an external application server. This option reflects the misconception that procedural logic cannot run natively in Snowflake.
- C. Incorrect.
Incorrect. A materialized view stores the results of a query for performance optimization. It is not intended to execute procedural control flow such as IF/ELSE logic or return custom status messages on demand. Someone might choose this because materialized views can precompute results, but they do not solve the requirement to run code and return a procedural outcome.
- D. Incorrect.
Incorrect. A file format object defines how Snowflake should parse data files, such as CSV or JSON. It does not execute SQL or procedural code. This distractor targets confusion between objects used for data loading and features used for running logic.