Snowflake, Inc.
|
DOCUMENTATION
  • Community
  • Resources
  • Blog
      • LANGUAGES:
      • English
      • Deutsch
      • Français
      • 日本語
  • Getting Started
  • Introduction to Snowflake
  • Tutorials, Videos & Other Resources
  • Release Notes
  • Connecting to Snowflake
  • Loading Data into Snowflake
    • Overview of Data Loading
    • Summary of Data Loading Features
    • Data Loading Considerations
    • Preparing to Load Data
    • Bulk Loading Using COPY
    • Loading Continuously Using Snowpipe
    • Loading Using the Web Interface (Limited)
    • Querying Data in Staged Files
    • Querying Metadata for Staged Files
    • Transforming Data During a Load
    • Data Loading Tutorials
      • Using the Tutorials
      • Tutorial: Bulk Loading from a Local File System Using COPY
      • Tutorial: Bulk Loading from Amazon S3 Using COPY
        • Prerequisites
        • Step 1. Create File Format Objects
        • Step 2. Create a Named Stage Object
        • Step 3. Copy Data Into the Target Table
        • Step 4. Resolve Data Load Errors Related to Data Issues
          • Validate Errors
          • Fix Errors and Load Again
        • Step 5. Verify the Loaded Data
        • Step 6. Congratulations!
      • Script: Loading JSON Data into a Relational Table
      • Script: Loading and Unloading Parquet Data
  • Unloading Data from Snowflake
  • Using Snowflake
  • Sharing Data Securely in Snowflake
  • Managing Your Snowflake Organization
  • Managing Your Snowflake Account
  • Managing Security in Snowflake
  • Developing Applications in Snowflake
  • General Reference
  • SQL Command Reference
  • SQL Function Reference
  • Appendices
Next Previous |
  • Docs »
  • Loading Data into Snowflake »
  • Data Loading Tutorials »
  • Tutorial: Bulk Loading from Amazon S3 Using COPY »
  • Step 4. Resolve Data Load Errors Related to Data Issues

Step 4. Resolve Data Load Errors Related to Data Issues¶

.

Step List

  • Prerequisites

  • Step 1. Create File Format Objects

  • Step 2. Create a Named Stage Object

  • Step 3. Copy Data Into the Target Table

  • Step 4. Resolve Data Load Errors Related to Data Issues

  • Step 5. Verify the Loaded Data

  • Step 6. Congratulations!

Use the VALIDATE function to validate the data files you loaded and return all errors encountered during the load.

Validate Errors¶

The following process returns errors by query ID and saves the results to a table for future reference.

You can view the query ID for the COPY job on the History History tab page of the web interface:

  1. Log into the Snowflake web interface.

  2. Change to the role you have been using to run the tutorial SQL statements.

  3. Click History History tab.

  4. Click the Query ID column link for the COPY INTO command. The Details panel opens.

  5. In the command line interface (e.g., SnowSQL), execute the following command. Replace <query_id> with the Query ID value.

    CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
    
  6. Query the results table:

    SELECT * FROM SAVE_COPY_ERRORS;
    

    Snowflake returns the following results:

    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
    | ERROR                                                                                                                                                                | FILE                                | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |   CODE | SQL_STATE | COLUMN_NAME                   | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD                                                                                                                                     |
    |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------|
    | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error | tutorials/dataloading/contacts3.csv |    3 |         1 |         234 | parsing  | 100080 |     22000 | "MYCSVTABLE"[11]              |          1 |              2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 |
    | Field delimiter '|' found while expecting record delimiter '\n'                                                                                                      | tutorials/dataloading/contacts3.csv |    5 |       125 |         625 | parsing  | 100016 |     22000 | "MYCSVTABLE"["POSTALCODE":10] |          4 |              5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188                  |
    +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
    

Snowflake encountered two data errors in tutorials/dataloading/contacts3.csv:

  • Number of columns in file (11) does not match that of the corresponding table (10)

    In Row 1, a hyphen was mistakenly replaced with the pipe (|) character, the data file delimiter, effectively creating an additional column in the record.

    Example 1 data error in record
  • Field delimiter '|' found while expecting record delimiter '\n'

    In Row 5, an additional pipe (|) character was introduced after a hyphen, breaking the record.

    Example 1 data error in record

Fix Errors and Load Again¶

In regular use, you would fix the problematic records manually and write them to a new data file. Alternatively, you could regenerate a new data file from the data source containing only the records that did not load.

You would then stage the fixed data files to the S3 bucket and attempt to reload the data from the files.

Next: Step 5. Verify the Loaded Data

Next Previous |
  • Ask the Community
  • Contact Support
  • Report Doc Issue
Snowflake Inc.
  • Platform
    • Cloud Data Platform
    • Architecture
    • Pricing
    • Data Marketplace
  • Solutions
    • Snowflake for Healthcare & Life Sciences
    • Snowflake for Marketing Analytics
    • Snowflake for Retail
    • Snowflake for Education
    • Snowflake for Developers
  • Resources
    • Resource Library
    • Webinars
    • Community
    • Legal
  • Explore
    • News
    • Blog
    • Trending
  • About
    • About Snowflake
    • Leadership & Board
    • Careers
    • Contact
  • Privacy Notice
  • Site Terms

450 Concard Drive, San Mateo, CA, 94402, United States | 844-SNOWFLK (844-766-9355)

© 2021 Snowflake Inc. All Rights Reserved