Categories:
Overview of DML Commands (Data Loading)

COPY INTO table

Loads data from staged files to an existing table. The files must already be staged in one of the following locations:

  • Snowflake location (user, table, or internal stage). Files are staged in this location using the PUT command.
  • External stage or location (currently only AWS S3 buckets are supported).
See also:
COPY INTO location

Syntax

Load without data transformation:

COPY INTO [<namespace>.]<table_name>
  FROM { <internal_location> | <external_location> }
      [ CREDENTIALS = (AWS_KEY_ID = <string> AWS_SECRET_KEY = <string> [ AWS_TOKEN = <string> ] ) ]
      [ ENCRYPTION = ( [TYPE = <encryption_type>] [ { MASTER_KEY = '<string>' | KMS_KEY_ID = '<string>' } ] ) ]
     -- REGION -- No longer used
  [ FILES = ( '<file_name1>' [ , '<file_name2>', ... ] ) ]
  [ PATTERN = '<regex_pattern>' ]
  [ FILE_FORMAT = ( { FORMAT_NAME = '<format_identifier>' | TYPE = <format_type> [ <format_options> ] } ) ]
  [ <copy_options> ]
  [ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]

Load with data transformation:

COPY INTO [<namespace>.]<table_name> [ ( <column_name1> [, <column_name2>, ...  ] ) ]
  FROM ( SELECT <select_column_name1> [, <select_column_name2>, ...  ] FROM { <internal_location> | <external_location> } )
      -- REGION -- No longer used
  [ FILES = ( '<file_name1>' [ , '<file_name2>', ... ] ) ]
  [ PATTERN = '<regex_pattern>' ]
  [ FILE_FORMAT = ( { FORMAT_NAME = '<format_identifier>' | TYPE = <format_type> [ <format_options> ] } ) ]
  [ <copy_options> ]
  [ VALIDATION_MODE = RETURN_<n>_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS ]

Where:

-- <internal_location> is either:

    { @~[/<path>] |
      @[<namespace>.]%<table_name>[/<path>] |
      @[<namespace>.]<internal_stage_name>[/<path>] }

-- <external_location> is either:

    { @[<namespace>.]<external_stage_name>[/<path>] |
      's3://<bucket>[/<path>]' }

Note

Data loading transformations only support selecting data from user stages and named stages (internal or external).

-- <format_type> is either:

   { CSV | JSON | AVRO | ORC | PARQUET | XML }

       -- For CSV, <format_options> can be one or more of:

            COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
            RECORD_DELIMITER = '<character>' | NONE
            FIELD_DELIMITER = '<character>' | NONE
            SKIP_HEADER = <integer>
            DATE_FORMAT = '<string>' | AUTO
            TIME_FORMAT = '<string>' | AUTO
            TIMESTAMP_FORMAT = '<string>' | AUTO
            BINARY_FORMAT = HEX | BASE64 | UTF-8
            ESCAPE = '<character>' | NONE
            ESCAPE_UNENCLOSED_FIELD = '<character>' | NONE
            TRIM_SPACE = TRUE | FALSE
            FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | NONE
            NULL_IF = ( '<string>' [ , '<string>' , ... ] )
            ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE | FALSE

       -- For JSON, <format_options> can be one or more of:

            COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
            ENABLE_OCTAL = TRUE | FALSE
            ALLOW_DUPLICATE = TRUE | FALSE
            STRIP_OUTER_ARRAY = TRUE | FALSE
            STRIP_NULL_VALUES = TRUE | FALSE
            IGNORE_UTF8_ERRORS = TRUE | FALSE

       -- For AVRO, <format_options> can only be:

            COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE

       -- For ORC, <format_options> is not applicable. No options are supported.

       -- For PARQUET, <format_options> is not applicable. No options are supported.

            -- Note: Snowflake supports loading Parquet files compressed using Snappy or gzip, i.e.
            -- <filename>.snappy.parquet or <filename>.gz.parquet. Because these compressed files adhere to
            -- the Parquet format, do not set the COMPRESSION file format option.

       -- For XML, <format_options> can be one or more of:

            COMPRESSION = AUTO | GZIP | BZ2 | BROTLI | ZSTD | DEFLATE | RAW_DEFLATE | NONE
            IGNORE_UTF8_ERRORS = TRUE | FALSE
            PRESERVE_SPACE = TRUE | FALSE
            STRIP_OUTER_ELEMENT = TRUE | FALSE
            DISABLE_SNOWFLAKE_DATA = TRUE | FALSE
            DISABLE_AUTO_CONVERT = TRUE | FALSE
-- <copy_options> can be one or more of:

    ON_ERROR = { CONTINUE | SKIP_FILE | SKIP_FILE_<number> | SKIP_FILE_<number>% | ABORT_STATEMENT }
    SIZE_LIMIT = <number>
    PURGE = TRUE | FALSE
    RETURN_FAILED_ONLY = TRUE | FALSE
    ENFORCE_LENGTH = TRUE | FALSE
    TRUNCATECOLUMNS = TRUE | FALSE
    FORCE = TRUE | FALSE
    LOAD_UNCERTAIN_FILES = TRUE | FALSE
-- <encryption_type> can be one of the following:

    AWS_CSE (default) | AWS_SSE_S3 | AWS_SSE_KMS | NONE

Parameters

Required:

[namespace.]table_name

Specifies the fully-qualified name of the table into which data is loaded.

Namespace is the database and/or schema in which the table resides, in the form of <database_name>.<schema_name> or <schema_name>. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.

select_column_name1 [ , select_column_name2 , ... ]

Specifies an explicit set of columns in data files staged in either internal_location or external_location, in the form of:

( SELECT <select_column_name1> [, <select_column_name2>, ...  ] FROM { <internal_location> | <external_location> } )

The SELECT list defines a numbered set of columns in the set of CSV data files you are loading from. The SELECT list must match the sequence of columns in the target table. You can use the optional column_name1 , column_name2, ... parameters to specify a list of columns in the target table.

Snowflake currently supports only the following subset of functions when using a query as the source for the COPY command:

For more information about using queries to select data, see SELECT.

{ internal_location | external_location }

Specifies the source of the data to be loaded, where:

  • internal_location

    URI specifier for the location in Snowflake where files containing data to be loaded are staged:

    @~[/<path>] Loads data from files in the stage for the current user.
    @[<namespace>.]%<table_name>[/<path>] Loads data from files in the stage for the specified table.
    @[<namespace>.]<internal_stage_name>[/<path>] Loads data from files in the specified internal named stage.
    • namespace is the database and/or schema in which the table or internal stage resides. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.
    • The optional path parameter restricts the set of files being loaded to the files under the folder prefix. If path is specified, but no file is explicitly named in the path, all data files in the path are loaded.
  • external_location

    URI specifier for the named external stage or external location (i.e. S3 bucket) where files containing data to be loaded are staged:

    @[<namespace>.]<external_stage_name>[/<path>] Loads data from files in the specified external named stage.
    's3://<bucket>[/<path>]' Loads data from files in the specified S3 bucket.
    • namespace is the database and/or schema in which the external stage resides. It is optional if a database and schema are currently in use within the user session; otherwise, it is required.
    • The optional path parameter restricts the set of files being loaded to the files under the folder prefix. If path is specified, but no file is explicitly named in the path, all data files in the path are loaded.

Note

  • The URI string for an S3 bucket must be enclosed in single quotes; however, you can enclose any other URI string in single quotes, which allows special characters, including spaces, in location and file names. For example:

    '@~/path 1/file 1.csv'

    '@%my table/path 1/file 1.csv'

    '@%my stage/path 1/file 1.csv'

    's3://bucket 1/prefix 1/file 1.csv'

  • Relative path modifiers such as /./ and /../ are interpreted literally, because “paths” in S3 are literal prefixes for a name; e.g., COPY INTO mytable FROM s3://mybucket/./../a.csv looks for a file literally named ./../a.csv in the S3 bucket.

  • Data loading transformations only support selecting data from user stages and named stages (internal or external).

Optional:

( column_name1 [, column_name2, ... ] )

Specifies an explicit list of table columns into which you want to insert data:

  • The first column consumes the values produced from the first column extracted from the loaded files.
  • The second column consumes the values produced from the second column extracted from the loaded files.
  • And so on, in the order specified.

Columns cannot be repeated in this listing. Any columns excluded from this column list are populated by their default value (NULL, if not specified). Omitted columns cannot have a sequence as their default value.

CREDENTIALS = ( AWS_KEY_ID = string AWS_SECRET_KEY = string [ AWS_TOKEN = string ] )

Used only when loading directly from a private/protected S3 bucket; not used when loading from internal or external stages, or a public S3 bucket.

Specifies the security credentials for connecting to AWS and accessing the private/protected S3 bucket where the files to load are staged. Credentials are generated by AWS:

  • Temporary (aka “scoped”) credentials are generated by AWS Security Token Service (STS). Temporary credentials consist of 3 components: AWS_KEY_ID, AWS_SECRET_KEY, and AWS_TOKEN. All three are required to access a private/protected bucket. After a designated period of time, temporary credentials expire and can no longer be used. You must then generate a new set of valid temporary credentials.
  • The COPY command also allows permanent (aka “long-term”) credentials to be used; however, for security reasons, Snowflake does not recommend using them.

Important

COPY commands contain complex syntax and sensitive information, such as credentials. In addition, they are executed frequently and are often stored in scripts or worksheets, which could lead to sensitive information being inadvertently exposed. Therefore, as a best practice, do not use permanent credentials in COPY commands. Instead, use temporary credentials. If you must use permanent credentials, use external stages, for which credentials are entered once and securely stored, minimizing the potential for exposure.

In addition, Snowflake recommends periodically generating new permanent credentials for external stages.

ENCRYPTION = ( MASTER_KEY = ‘key‘ )

Used only when loading directly from a S3 bucket that contains encrypted files; not used when loading from internal or external stages.

Specifies the encryption settings used to decrypt encrypted files in the S3 bucket and extract data.

TYPE = encryption_type

Specifies the encryption type used. Possible values are:

  • Client-side encryption:

    AWS_CSE: Requires a MASTER_KEY value. Note that when a MASTER_KEY value is provided, Snowlake assumes a TYPE value of AWS_CSE. That is, when a MASTER_KEY value is provided, specifying TYPE = AWS_CSE is optional.

  • Server-side encryption:

    • AWS_SSE_S3: Requires no additional encryption settings.
    • AWS_SSE_KMS: Accepts an optional KMS_KEY_ID value.

For more information about the encryption types, see the AWS documentation for client-side encryption or server-side encryption.

MASTER_KEY = ‘string
AWS_CSE encryption only: Specifies the client-side master key that was used to encrypt the files in the bucket. The master key must be a 128-bit or 256-bit key in Base64-encoded form. Snowflake requires this key to decrypt encrypted files in the bucket and extract data for loading.
KMS_KEY_ID = ‘string
AWS_SSE_KMS encryption only: Optionally specifies the ID for the AWS KMS-managed key that was used to encrypt files in the bucket. If no KMS_KEY_ID value is provided, your default KMS key ID is used to decrypt encrypted files and extract data for loading.
REGION
No longer used. Any value specified for this parameter is ignored by Snowflake.
FILES = ( ‘file_name1‘ [ , ‘file_name2‘, ... ] )

Specifies a list of one or more files to be loaded. The files must already have been uploaded to either the Snowflake location (for a user or table or stage) or an external_location (S3 bucket) specified in the command.

The maximum number of files that can be specified is 1000.

PATTERN = ‘regex_pattern

A regular expression pattern string, enclosed in single quotes, specifying the filenames and/or path to match.

Tip

For the best performance, try to avoid applying patterns that filter large numbers of files.

FILE_FORMAT = ( { FORMAT_NAME = ‘format_identifier‘ | TYPE = format_type [ format_options ] } )

Specifies the format of the data files to load. Can either reference a file format object (by name) or specify the file format directly in the command, optionally followed by options that are format-specific:

format_identifier
Specifies an existing file format object to use for loading data into the table. The specified file format object determines the format type (e.g. CSV, JSON) and other format options for data files. If FORMAT_NAME is specified, then TYPE is not used and no additional format options are specified. Instead, the named file format object defines the other file format options used for loading data.
format_type
Specifies the type of files (e.g. CSV, JSON) to load into the table. If TYPE is specified, then FORMAT_NAME is not used and additional format-specific options can be specified.
format_options

Format-specific options that can be specified if TYPE is specified.

For details about the file format options you can specify in the command, see CREATE FILE FORMAT.

copy_options

One or more of the following options separated by blanks space, or optionally commas or new lines:

ON_ERROR = { CONTINUE | SKIP_FILE | SKIP_FILE_number | SKIP_FILE_number% | ABORT_STATEMENT }

String that specifies the action to perform when an error is encountered while loading data from a file:

  • CONTINUE: Continue loading the file.
  • SKIP_FILE: Skip file if any errors encountered in the file.
  • SKIP_FILE_number (e.g. SKIP_FILE_10): Skip file when the number of errors in the file exceeds the specified number.
  • SKIP_FILE_number% (e.g. SKIP_FILE_10%): Skip file when the percentage of errors in the file exceeds the specified percentage.
  • ABORT_STATEMENT: Abort the COPY statement if any error is encountered.

Default value is ABORT_STATEMENT.

SIZE_LIMIT = number

Number (> 0) that specifies the maximum size (in bytes) of data to be loaded for a given COPY statement. Note that at least one file is loaded regardless of the value specified for SIZE_LIMIT unless there is no file to be loaded. A null value indicates no size limit.

Default value is null (no size limit).

PURGE = TRUE | FALSE

Boolean value that specifies whether to purge the data files from the location automatically after the data is successfully loaded.

Default value is FALSE.

RETURN_FAILED_ONLY = TRUE | FALSE

Boolean value that specifies whether to return only files that have failed to load in the statement result.

Default value is FALSE.

ENFORCE_LENGTH = TRUE | FALSE

Alternative syntax for TRUNCATECOLUMNS

Boolean value that specifies whether to truncate text strings that exceed the target column length:

  • If the value is TRUE, the COPY statement produces an error if a loaded string exceeds the target column length.
  • If the value is FALSE, strings are automatically truncated to the target column length.

Default value is TRUE.

Note

This option is functionally equivalent to TRUNCATECOLUMNS, but has the opposite behavior. It is provided for compatibility with other databases. It is only necessary to include one of these two parameters in a COPY statement to produce the desired output.

TRUNCATECOLUMNS = TRUE | FALSE

Alternative syntax for ENFORCE_LENGTH

Boolean value that specifies whether to truncate text strings that exceed the target column length:

  • If the value is TRUE, strings are automatically truncated to the target column length.
  • If the value is FALSE, the COPY statement produces an error if a loaded string exceeds the target column length.

Default value is FALSE.

Note

This option is functionally equivalent to ENFORCE_LENGTH, but has the opposite behavior. It is provided for compatibility with other databases. It is only necessary to include one of these two parameters in a COPY statement to produce the desired output.

FORCE = TRUE | FALSE

Boolean value that specifies to load all files, regardless of whether they’ve been loaded previously and have not changed since they were loaded. This option is generally best suited for deliberately overwriting previously-loaded table data from files that have not changed.

Default value is FALSE.

LOAD_UNCERTAIN_FILES = TRUE | FALSE

Boolean value that specifies to load files for which the load status is unknown. The COPY command skips these files by default.

The load status is unknown if all of the following conditions are true:

  • The file’s LAST_MODIFIED date (i.e. date when the file was staged) is older than 64 days.
  • The initial set of data was loaded into the table more than 64 days earlier.
  • If the file was already loaded successfully into the table, this event occurred more than 64 days earlier.

To force the COPY command to load all files regardless of whether the load status is known, use the FORCE option instead.

For more information about load status uncertainty, see Loading Older Files.

Default value is FALSE.

VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS

String constant that specifies the COPY command to not load rows into the corresponding table. Instead, the command validates the data to be loaded and returns results based on the validation option specified. Can be one of the following values:

RETURN_n_ROWS
Returns the specified number of rows, if no errors are encountered; otherwise, fails at the first error encountered in the rows.
RETURN_ERRORS
Returns all errors (parsing, conversion, etc.) across all the specified files.
RETURN_ALL_ERRORS
Returns all errors across all the specified files, including files with errors that have been partially loaded during an earlier load because the ON_ERROR copy option was set to CONTINUE during the load.

Note

Snowflake produces an error if you attempt to validate a COPY statement that reorders CSV columns.

Usage Notes

  • If loading into a table from the table’s own stage, the FROM clause, which specifies the internal_location to load from, is not required and can be omitted.

  • When using a query as the source for the COPY command, i.e. transforming data during a load:

    • Both CSV and semi-structured file types are supported; however, even when loading semi-structured data, e.g. JSON, you should set CSV as the file format type (default value). You could use the JSON file format, but any error in the transformation would stop the COPY operation, even if you set the ON_ERROR option to continue or skip the file.

      To take advantage of Snowflake’s error management policies, you should structure your JSON data as newline delimited JSON (NDJSON) when separating JSON data into different columns during a load.

    • The DISTINCT keyword is not supported, e.g.:

      COPY INTO mytable
       FROM (SELECT DISTINCT t.$1 ....);
      
  • If referencing a file format in the current namespace, you can omit the single quotes around the format identifier.

  • CREDENTIALS and ENCRYPTION only apply if you are loading directly from a private/protected S3 bucket. If you are loading from a public bucket, no credentials are required, and if you are loading from a named external stage, the stage provides all the credential information required for accessing the bucket.

  • If you encounter errors while running the COPY INTO table command, after the command completes, you can validate the files that produced the errors using the VALIDATE table function.

    Note

    The VALIDATE function does not currently support COPY expressions that load a subset of data columns or reorder data columns.

  • Unless you explicitly specify FORCE = TRUE as one of the copy options, the command ignores staged data files that were already loaded into the table. To reload the data, you must either specify FORCE = TRUE or modify the file and stage it again, which generates a new checksum.

  • The COPY command does not validate data type conversions for Parquet files.

Examples

Note

For examples of transformations during a data load, see Transforming Data During a Load.

Loading Files from an External Stage

Load files from a S3 bucket into a table from an external stage that you created previously using the CREATE STAGE command. The external stage includes the S3 bucket credentials and other details:

COPY INTO mycsvtable
  FROM @mystage/tutorials/dataloading/contacts1.csv;

Loading Files from an Internal Stage

Load files from the user’s personal stage into a table. The files were copied to the stage earlier using the PUT command.

COPY INTO mytable from @~/staged
FILE_FORMAT = (FORMAT_NAME = 'mycsv');

Load files from the table stage into a table. The files were copied to the stage earlier using the PUT command.

COPY INTO mytable
FILE_FORMAT = (TYPE = CSV);

Note

When copying data from files in a table location, the FROM clause can be omitted because Snowflake automatically checks for files in the table’s location.

Load files from a named stage into a table. The files were copied to the stage earlier using the PUT command.

COPY INTO mytable
FROM @my_int_stage;

Using Pattern Matching

Load files from the table stage into a table. Using pattern matching, only load data from compressed CSV files in any path:

COPY INTO mytable
  FILE_FORMAT = (TYPE = 'CSV')
  PATTERN='.*/.*/.*.csv.gz';

Load files from the table stage into a table. Using pattern matching, only load files whose names start with the string sales:

COPY INTO mytable
  FILE_FORMAT = (FORMAT_NAME = myformat)
  PATTERN=‘.*sales.*.csv';

Loading JSON Data into a VARIANT Column

The following example loads JSON data into a table with a single column of type VARIANT.

The staged JSON array comprises three objects separated by new lines:

[{
    "location": {
      "city": "Lexington",
      "zip": "40503",
      },
      "sq__ft": "1000",
      "sale_date": "4-25-16",
      "price": "75836"
},
{
    "location": {
      "city": "Belmont",
      "zip": "02478",
      },
      "sq__ft": "1103",
      "sale_date": "6-18-16",
      "price": "92567"
}
{
    "location": {
      "city": "Winchester",
      "zip": "01890",
      },
      "sq__ft": "1122",
      "sale_date": "1-31-16",
      "price": "89921"
}]
/* Create a JSON file format that delimits records using the new line character and strips the outer array. */

CREATE OR REPLACE FILE FORMAT json_format
  TYPE = 'JSON'
  RECORD_DELIMITER = '\\n'
  STRIP_OUTER_ARRAY = TRUE;

/* Create an internal stage that references the JSON file format. */

CREATE OR REPLACE STAGE mystage
  FILE_FORMAT = json_format;

/* Stage the JSON file. */

PUT file:///tmp/sales.json @mystage AUTO_COMPRESS=TRUE;

/* Create a target table for the JSON data. */

CREATE OR REPLACE TABLE house_sales (src VARIANT);

/* Copy the JSON data into the target table. */

COPY INTO house_sales
   FROM @mystage/sales.json.gz;

SELECT * FROM house_sales;

+---------------------------+
| SRC                       |
|---------------------------|
| {                         |
|   "location": {           |
|     "city": "Lexington",  |
|     "zip": "40503"        |
|   },                      |
|   "price": "75836",       |
|   "sale_date": "4-25-16", |
|   "sq__ft": "1000",       |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "location": {           |
|     "city": "Belmont",    |
|     "zip": "02478"        |
|   },                      |
|   "price": "92567",       |
|   "sale_date": "6-18-16", |
|   "sq__ft": "1103",       |
|   "type": "Residential"   |
| }                         |
| {                         |
|   "location": {           |
|     "city": "Winchester", |
|     "zip": "01890"        |
|   },                      |
|   "price": "89921",       |
|   "sale_date": "1-31-16", |
|   "sq__ft": "1122",       |
|   "type": "Condo"         |
| }                         |
+---------------------------+

Reloading Files

Load files from the table stage into a table. Load specific files, then reload the files. In this example, the first command loads the specified files and the second command forces the same files to be loaded again (producing duplicate rows), even though the contents of the files have not changed:

COPY INTO load1 FROM @%load1/data1/
    FILES=('test1.csv', 'test2.csv')
    FILE_FORMAT=(SKIP_HEADER=1 NULL_IF=('') FIELD_OPTIONALLY_ENCLOSED_BY='"');

COPY INTO load1 FROM @%load1/data1/
    FILES=('test1.csv', 'test2.csv')
    FILE_FORMAT=(SKIP_HEADER=1 NULL_IF=('') FIELD_OPTIONALLY_ENCLOSED_BY='"')
    FORCE=TRUE;

If FORCE=TRUE is not specified in the second command, Snowflake does not reload the files and the command completes successfully with zero files processed.

Purging Files After Loading

Load files from the table stage into a table and purge files after loading. By default, COPY does not purge loaded files from the location. To purge the files after loading:

  • Set the PURGE copy option to TRUE for the table:

    ALTER TABLE mytable SET STAGE_COPY_OPTIONS = (PURGE = TRUE);
    
    COPY INTO mytable;
    
  • You can also override any of the copy options directly in the COPY command:

    COPY INTO mytable PURGE = TRUE;
    

Validating Staged Files

Validate files in the stage without loading:

  • Run the COPY command in validation mode and see all errors:

    COPY INTO mytable VALIDATION_MODE = 'RETURN_ERRORS';
    
    +-------------------------------------------------------------------------------------------------------------------------------+------------------------+------+-----------+-------------+----------+--------+-----------+----------------------+------------+----------------+
    |                                                         ERROR                                                                 |            FILE        | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |  CODE  | SQL_STATE |   COLUMN_NAME        | ROW_NUMBER | ROW_START_LINE |
    +-------------------------------------------------------------------------------------------------------------------------------+------------------------+------+-----------+-------------+----------+--------+-----------+----------------------+------------+----------------+
    | Field delimiter ',' found while expecting record delimiter '\n'                                                               | @MYTABLE/data1.csv.gz  | 3    | 21        | 76          | parsing  | 100016 | 22000     | "MYTABLE"["QUOTA":3] | 3          | 3              |
    | NULL result in a non-nullable column. Use quotes if an empty field should be interpreted as an empty string instead of a null | @MYTABLE/data3.csv.gz  | 3    | 2         | 62          | parsing  | 100088 | 22000     | "MYTABLE"["NAME":1]  | 3          | 3              |
    | End of record reached while expected to parse column '"MYTABLE"["QUOTA":3]'                                                   | @MYTABLE/data3.csv.gz  | 4    | 20        | 96          | parsing  | 100068 | 22000     | "MYTABLE"["QUOTA":3] | 4          | 4              |
    +-------------------------------------------------------------------------------------------------------------------------------+------------------------+------+-----------+-------------+----------+--------+-----------+----------------------+------------+----------------+
    
  • Run the COPY command in validation mode for a specified number of rows. In this example, the first run encounters no errors in the specified number of rows and completes successfully, displaying the information as it will appear when loaded into the table. The second run encounters an error in the specified number of rows and fails with the error encountered:

    COPY INTO mytable VALIDATION_MODE = 'RETURN_2_ROWS';
    
    +--------------------+----------+-------+
    |        NAME        |    ID    | QUOTA |
    +--------------------+----------+-------+
    | Joe Smith          |  456111  | 0     |
    | Tom Jones          |  111111  | 3400  |
    +--------------------+----------+-------+
    
    COPY INTO mytable VALIDATION_MODE = 'RETURN_3_ROWS';
    
    FAILURE: NULL result in a non-nullable column. Use quotes if an empty field should be interpreted as an empty string instead of a null
      File '@MYTABLE/data3.csv.gz', line 3, character 2
      Row 3, column "MYTABLE"["NAME":1]