What causes ARRAY index out of bounds?

Accessing variant_col[3] when the array has only 2 elements. Using a hardcoded array index in a query without first validating array length with ARRAY_SIZE. A schema change upstream reduced the number of elements in a JSON array payload. A FLATTEN and subsequent subscript access assumed a fixed array structure that changed. A JavaScript UDF or Snowpark code performed array subscript access without bounds checking

How do I fix ARRAY index out of bounds?

Use GET(array_col, index) which returns NULL for out-of-bounds access instead of raising an error.. Guard array access with ARRAY_SIZE: CASE WHEN ARRAY_SIZE(col) > 2 THEN col[2] ELSE NULL END.. Replace hardcoded array indices with FLATTEN(INPUT => array_col) to unnest all elements safely.. In JavaScript UDFs, add Array.isArray(arr) && arr.length > index checks before subscript access.. Validate the array structure upstream with a schema check before the pipeline processes the data.

Low severitydata quality

Power BI Refresh Error:
ARRAY index out of bounds

What does this error mean?

A subscript access on a Snowflake ARRAY or VARIANT value referenced an index that does not exist. Snowflake returns NULL rather than an error for most out-of-bounds accesses, but certain operations raise an explicit error when strict bounds checking is applied.

Common causes

1Accessing variant_col[3] when the array has only 2 elements
2Using a hardcoded array index in a query without first validating array length with ARRAY_SIZE
3A schema change upstream reduced the number of elements in a JSON array payload
4A FLATTEN and subsequent subscript access assumed a fixed array structure that changed
5A JavaScript UDF or Snowpark code performed array subscript access without bounds checking

How to fix it

1Use GET(array_col, index) which returns NULL for out-of-bounds access instead of raising an error.
2Guard array access with ARRAY_SIZE: CASE WHEN ARRAY_SIZE(col) > 2 THEN col[2] ELSE NULL END.
3Replace hardcoded array indices with FLATTEN(INPUT => array_col) to unnest all elements safely.
4In JavaScript UDFs, add Array.isArray(arr) && arr.length > index checks before subscript access.
5Validate the array structure upstream with a schema check before the pipeline processes the data.

Frequently asked questions

Does Snowflake throw an error or return NULL for out-of-bounds array access?

In standard SQL mode, Snowflake returns NULL for array[index] when the index is out of bounds. An explicit error is raised in JavaScript UDFs or Snowpark when bounds are not checked in application code.

How do I find the maximum array length across all rows to set a safe upper bound?

Use SELECT MAX(ARRAY_SIZE(array_col)) FROM table to find the maximum array length, then adjust your query logic accordingly.