Imagine that you want to load semi-structured data in the form of JSON into Snowflake. If the JSON contains an outer array and if you did not strip it, the entire dataset would be loaded into a single row in the target table.
Sometimes that is acceptable and you can process the data in a later step. Whenever the dataset results in a row that is larger than 16 mb however, you have a problem. A VARIANT (datatype that is used often when loading JSON data) can only contain maximum 16 mb per row which means the dataset would not fit in the case mentioned.
STRIP_OUTER_ARRAY can help you out when loading the data in such a case. STRIP_OUTER_ARRAY will remove the outer array which will result in multiple rows instead of only one. This solves the problem encountered when trying to copy a dataset larger than 16 mb, because now the data will be divided over several rows instead of only one.
COPY INTO <target table>
FROM @~/<file>.json
FILE_FORMAT = (TYPE = 'JSON' STRIP_OUTER_ARRAY = true);