Fixing Load Errors, Running DML and Preparing External Tables in Snowflake

1.Finding and resolving data import errors

Each COPY INTO <table> returns one row per file. That row shows STATUS (loaded, load failed or partially loaded), ROWS_PARSED, ROWS_LOADED, ERRORS_SEEN, and the first error together with its line, character position and column name. To look at loads from earlier, the troubleshooting guide begins with COPY_HISTORY. Its STATUS column has the same three outcomes, and its FIRST_ERROR_MESSAGE gives the reason. As the name says, it reports only the first error in each set of files.

ON_ERROR decides what COPY does when a row is bad. Choose the value deliberately. The documentation warns that the default suits common cases but isn't always the best option.

To see every error and not just the first, run the same COPY with VALIDATION_MODE. This option loads nothing. The syntax also accepts RETURN_<n>_ROWS and RETURN_ERRORS, but the troubleshooting guide's example uses RETURN_ALL_ERRORS. It then takes the query ID and writes the rejected records to a file on the stage, so you can fix the source data:

COPY INTO mytable
  FROM @mystage/myfile.csv.gz
  VALIDATION_MODE=RETURN_ALL_ERRORS;

SET qid=last_query_id();

COPY INTO @mystage/errors/load_errors.txt FROM (SELECT rejected_record FROM TABLE(result_scan($qid)));

The guide describes two more faults. Error 003139 ('Integration … cannot be found') means a stage has lost its storage integration. Stages point to integrations by a hidden ID, and CREATE OR REPLACE STORAGE INTEGRATION drops the integration and recreates it with a new ID. To fix it, run ALTER STAGE stage_name SET STORAGE_INTEGRATION = storage_integration_name on every affected stage. Load timestamps that look too early usually come from a column defaulting to CURRENT_TIMESTAMP. That value is evaluated when the load is compiled, not when it commits, so it comes out earlier than the LOAD_TIME in COPY_HISTORY. Use METADATA$START_SCAN_TIME instead.

Sources12

2.General DML on loaded tables: INSERT, UPDATE and DELETE

After data is loaded, you change it with ordinary DML. Snowflake's DML group covers INSERT (single-table and multi-table), MERGE, UPDATE, DELETE and TRUNCATE TABLE, and COPY INTO <table> is listed in that group too. PUT, GET, LIST and REMOVE are a separate set. They manage files on stages and don't perform DML. The sources here list INSERT but give no syntax for it, so this section concentrates on UPDATE and DELETE.

UPDATE <target_table>
       SET <col_name> = <value> [ , <col_name> = <value> , ... ]
        [ FROM <additional_tables> ]
        [ WHERE <condition> ]

UPDATE without a WHERE clause changes every row. In SET, write the bare column name: UPDATE t1 SET t1.col = 1 is invalid. If FROM joins in another table, one target row can match several source rows. By default Snowflake then updates it from one of them, chosen nondeterministically. Set ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE to make that situation an error.

DELETE FROM <table_name>
            [ USING <additional_table_or_query> [, <additional_table_or_query> ] ]
            [ WHERE <condition> ]

DELETE without WHERE removes every row but keeps the table. USING lets other tables or subqueries pick the rows to remove. The point that ties DML to loading is load history. TRUNCATE TABLE clears the table's external file load history, but DELETE leaves it in place. So if you DELETE rows that came from a staged file, a plain COPY won't load that file again, because its checksum is already recorded. You either modify and re-stage the file, or override the history with FORCE = TRUE, which deliberately reloads unchanged files and can create duplicate rows.

COPY INTO load1 FROM @%load1/data1/ FILES=('test1.csv', 'test2.csv')  ? =TRUE;

Sources3452

3.Preparing external tables

Sometimes the data should stay in cloud storage and not be loaded at all. An external table lets you query files in an external stage as if they were a Snowflake table, while Snowflake stores only file-level metadata. It reads any format COPY supports except XML. External tables are read-only: you can query them, join them and build views on them, but you can't run DML against them. Queries can be slower than on native tables. A materialized view over the external table can help, and for Parquet the documentation suggests Apache Iceberg tables instead. If a query hits a bad file, it skips that file and moves on to the next, so a query can return partial results without failing.

You don't need to know the schema in advance, only the file format. Every external table has three columns. VALUE is a VARIANT holding one row of the file. METADATA$FILENAME is the staged file's name and path. METADATA$FILE_ROW_NUMBER is the row's position within that file. SELECT * always returns VALUE. If you do know the schema, you can add virtual columns defined as expressions over VALUE and the pseudocolumns. Their data types must match the external data, which gives you type checking.

Snowflake strongly recommends partitioning. It needs files arranged in logical paths, such as by date or country. A partition column is an expression that parses METADATA$FILENAME, and it is declared with PARTITION BY when the table is created. With automatic partitions, Snowflake adds partitions whenever metadata refreshes. That happens once at creation, can be set up to happen when new files arrive, or can be run manually with ALTER EXTERNAL TABLE … REFRESH. With user-specified partitions (PARTITION_TYPE = USER_SPECIFIED), the owner adds partitions using ALTER EXTERNAL TABLE … ADD PARTITION. This option is mainly used to stay in sync with metastores such as AWS Glue or Hive, and it can't be refreshed automatically. Once the table exists, you can't change which method it uses.

CREATE EXTERNAL TABLE
  <table_name>
     ( <part_col_name> <col_type> AS <part_expr> )
     [ , ... ]
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  ..

Sources6

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.COPY_HISTORY's FIRST_ERROR_MESSAGE tells you everything that is wrong with a failed set of files.Why is that wrong?

   It shows only the first error. To see every error, use VALIDATION_MODE = RETURN_ALL_ERRORS, which loads nothing.

   Covered in Finding and resolving data import errors

2. 2.After a DELETE of bad rows, re-running the same COPY reloads the corrected data from the same staged file.Why is that wrong?

   DELETE leaves the file load history in place, so COPY treats the unchanged file as already loaded. Modify and re-stage the file, or use FORCE = TRUE.

   Covered in General DML on loaded tables: INSERT, UPDATE and DELETE

3. 3.You can fix bad values in an external table with UPDATE or DELETE.Why is that wrong?

   External tables are read-only. Fix the files in cloud storage, or load the data into a native table and change it there.

   Covered in Preparing external tables