Planning a Snowflake Data Load: File Sizing, Load Metadata and Error Handling

1.Warehouses and file sizing for bulk loads

Loading large data sets can slow down queries, so Snowflake recommends separate warehouses for loading and for querying. How much a load can run in parallel depends on two things: the compute in the warehouse and the number of files. You can't have more parallel load operations than you have files. That is why file size matters more than warehouse size. Aim for files of roughly 100–250 MB (or larger) compressed. This guidance applies to bulk loads and to Snowpipe.

Avoid both extremes. Many tiny files add per-file overhead, so combine them. Very large files (100 GB or more) are not recommended. Split them, by line so that no record spans two chunks. On Unix-like systems the split utility does this. For example, splitting an 8 GB file of 10 million lines every 100,000 lines gives 100 files of about 80 MB each. One exception: Snowflake can scan large uncompressed CSV files (over 128 MB, RFC4180-compliant) in parallel, as long as MULTI_LINE is FALSE, COMPRESSION is NONE, and ON_ERROR is ABORT_STATEMENT or CONTINUE.

split  ?  100000 pagecounts-20151201.csv pages

Sources12

2.File cadence for continuous loading with Snowpipe

Snowpipe usually loads new data within a minute of the file notification. Very large files, or files that take a lot of work to decompress, decrypt or transform, take longer. Cost is also driven by file count, because managing the internal load queue adds overhead that grows with the number of queued files. With files of about 100–250 MB or larger, that overhead becomes immaterial.

If your source takes more than a minute to build up a few MB, write a new file about once per minute. Staging files more often than that only adds queue overhead, and latency is not guaranteed to improve. Batching tools can help. Amazon Data Firehose, for example, lets you set a buffer size and a buffer interval, and keeping the interval at its 60-second minimum avoids creating too many files.

Sources2

3.Choosing files and the 64-day load metadata

COPY can pick a subset of a stage's files in three ways. This lets you run several COPY statements at once over different subsets.

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

PATTERN behaves differently in the two methods. A bulk load applies the regex to the whole storage location in the FROM clause. Snowpipe first trims off the path segments that are already in the stage definition. For Snowpipe, Snowflake prefers cloud event filtering to PATTERN.

COPY records metadata on the target table for each loaded file: its name, size, ETag, rows parsed, last load time and errors. This stops parallel COPY statements from loading the same file twice. Files that fail are marked load failed, and a later COPY can pick them up. If many concurrent COPY statements load the same table, Snowflake recommends Snowpipe instead.

The metadata expires after 64 days. When a file's LAST_MODIFIED date and its last load are both older than that, and the table's initial load was also more than 64 days ago, COPY can't tell whether the file was already loaded, so it skips the file by default. Setting LOAD_UNCERTAIN_FILES to true loads those files and still uses whatever metadata is available. FORCE ignores the metadata completely and reloads every file, which can duplicate data.

Sources3

4.Preparing file formats and detecting schemas

Delimited files default to UTF-8, and you can set another encoding with the ENCODING file format option. Semi-structured formats support UTF-8 only. For CSV, put quotes around any field that contains the delimiter or a carriage return, escape any quotes inside the data, and give every row the same number of columns.

Two file format options fix common problems. In a VARIANT column, JSON nulls are stored as the string "null". If those nulls just mean a value is missing, STRIP_NULL_VALUES = TRUE avoids wasting storage and slowing queries. Some exporters put a space before each opening quote, which makes Snowflake read the quotes as part of the data. TRIM_SPACE removes that space:

COPY INTO mytable
FROM @%mytable
FILE_FORMAT = (TYPE = CSV TRIM_SPACE=true FIELD_OPTIONALLY_ENCLOSED_BY = '0x22');

You don't have to write the target table by hand. The INFER_SCHEMA table function reads staged files and returns their column definitions. CREATE TABLE ... USING TEMPLATE then builds a table from that output, and the same works for external and Iceberg tables. INFER_SCHEMA supports Parquet, Avro, ORC, JSON and CSV.

Sources425

5.Error handling with ON_ERROR and VALIDATION_MODE

The ON_ERROR copy option decides what a load does when it hits bad rows. The values are CONTINUE, SKIP_FILE, SKIP_FILE_num, 'SKIP_FILE_num%' and ABORT_STATEMENT. Snowflake's documentation says the default suits common scenarios but isn't always the best choice. (The source excerpts used for this lesson don't say which value is the default, so check the COPY INTO <table> reference.)

CONTINUE keeps loading the file. COPY then reports at most one error per file, and the difference between ROWS_PARSED and ROWS_LOADED tells you how many rows had errors. To see every error, use the VALIDATION_MODE parameter or the VALIDATE function. SKIP_FILE skips any file that contains an error, and the numeric forms skip a file only once its error rows reach a count or percentage. SKIP_FILE has to buffer the whole file, so it is slower than CONTINUE or ABORT_STATEMENT. Large files raise the stakes. Skipping one because of a few errors wastes time and credits, and a load that runs past the 24-hour limit can be aborted without committing any of the file.

Sources62

Exam traps

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

1. 1.A slow bulk load is best fixed by scaling the warehouse up to X-Large or larger.Why is that wrong?

   Load parallelism is limited by the number of files. Unless you are loading hundreds or thousands of files at once, a Small to Large warehouse is usually enough, and sizing files to 100–250 MB helps more.

   Covered in Warehouses and file sizing for bulk loads

2. 2.FORCE is the safe way to load old files that COPY skipped because their load status is unknown.Why is that wrong?

   FORCE ignores load metadata and reloads every file, which can duplicate data. LOAD_UNCERTAIN_FILES loads the uncertain files and still uses whatever metadata is available.

   Covered in Choosing files and the 64-day load metadata

Practise it for real

Load a split file set from a table stage with an explicit FILES list, then check that load metadata prevents a duplicate load

1. 1.Split a large CSV export by line, for example: split -l 100000 pagecounts-20151201.csv pages

   Why: Splitting by line produces several moderate-sized files, so the load can run in parallel and no record spans two chunks.

   You should see: A set of smaller files whose names start with the prefix pages

2. 2.Upload the split files to the target table's stage with the PUT command

   Why: The PUT command uploads local files to any internal stage, including a table stage.

   You should see: The files are listed on the table stage. Only the table owner can stage files there.

3. 3.Run COPY INTO with a FILES list naming the staged files

   Why: A discrete FILES list is usually the fastest way to select files, up to 1,000 per statement.

   You should see: The named files load into the table

4. 4.Run the same COPY statement again without FORCE

   Why: COPY keeps load metadata for each file on the table so it doesn't load the same file twice.

   You should see: The files that already loaded are not loaded again