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);