Working with semi-structured data types like Arrays is a core competency for a Snowflake Data Analyst. In Snowflake, arrays are zero-indexed, meaning the first element is at position 0. Consequently, the index of the last element is always the total number of elements minus one ($Size - 1$).
To retrieve an element from a specific index, Snowflake provides the GET() function. This function takes the array column and the calculated index as arguments. When combined with ARRAY_SIZE(), which returns the total count of elements in the array, the formula ARRAY_SIZE(COL_B)-1 accurately targets the final index regardless of the array's length.
Evaluating the Options:
Option B is incorrect because using ARRAY_SIZE as the index directly (without subtracting 1) results in an "out-of-bounds" error or returns NULL, because the index equals the length (e.g., in an array of 3 items, the max index is 2).
Option C is incorrect. While some programming languages (like Python) allow negative indexing to start from the end, Snowflake SQL does not support this shorthand for arrays; it would simply return NULL.
Option D is incorrect because LAST_VALUE is an Analytic/Window function used to find the last row in a sorted result set, not the last element within a single array cell.
Option A is the 100% correct approach. It uses the standard, robust method for dynamic index calculation. This ensures that even if different rows have arrays of different lengths, the query will always successfully "pluck" the final item from each. This skill is vital for Data Transformation tasks, such as extracting the most recent status from a history array.
