Preparing your data files¶

This topic provides best practices, general guidelines, and important considerations for preparing your data files for loading.

File sizing best practices¶

For best load performance and to avoid size limitations, consider the following data file sizing guidelines. Note that these recommendations apply to bulk data loads as well as continuous loading using Snowpipe.

General file sizing recommendations¶

The number of load operations that run in parallel can’t exceed the number of data files to be loaded. To optimize the number of parallel operations for a load, we recommend aiming to produce data files roughly 100-250 MB (or larger) in size compressed.

Aggregate smaller files to minimize the processing overhead for each file. Split larger files into a greater number of smaller files to distribute the load among the compute resources in an active warehouse. The number of data files that are processed in parallel is determined by the amount of compute resources in a warehouse. We recommend splitting large files by line to avoid records that span chunks.

If your data source doesn’t allow exporting data files in smaller chunks, you can use a third-party utility to split large CSV files.

If you are loading large uncompressed CSV files (greater than 128MB) that follow the RFC4180 specification, Snowflake supports parallel scanning of these CSV files when MULTI_LINE is set to FALSE, COMPRESSION is set to NONE, and ON_ERROR is set to ABORT_STATEMENT or CONTINUE.

Linux or macOS¶

The split utility enables you to split a CSV file into multiple smaller files.

Syntax:

Copy code

split [-a suffix_length] [-b byte_count[k|m]] [-l line_count] [-p pattern] [file [name]]

For more information, type man split in a terminal window.

Example:

Copy code

split -l 100000 pagecounts-20151201.csv pages

This example splits a file named pagecounts-20151201.csv by line length. Suppose the large single file is 8 GB and contains 10 million lines. Split by 100,000, each of the 100 smaller files is 80 MB (10 million / 100,000 = 100). The split files are named pagessuffix.

Windows¶

Windows does not include a native file split utility; however, Windows supports many third-party tools and scripts that can split large data files.

Size limits for database objects¶

When you use any of the available methods for loading data into Snowflake, you can store objects with sizes up to the following limits:

The default size for VARCHAR columns is 16 MB (8 MB for binary). To create tables with column sizes larger than 16 MB, specify the size explicitly. For example:

CREATE OR REPLACE TABLE my_table (
  c1 VARCHAR(134217728),
  c2 BINARY(67108864));

To use the new limits for VARCHAR columns, you can alter tables to change the column size. For example:

ALTER TABLE my_table ALTER COLUMN col1 SET DATA TYPE VARCHAR(134217728);

To apply the new size to columns of type BINARY in these tables, recreate the tables. You can’t alter the length of a BINARY column in an existing table.

For columns of type ARRAY, GEOGRAPHY, GEOMETRY, OBJECT, and VARIANT, you can store objects larger than 16 MB in existing tables and new tables, by default, without specifying the length. For example:

CREATE OR REPLACE TABLE my_table (c1 VARIANT);

If you have procedures and functions that were created in the past and that use VARIANT, VARCHAR, or BINARY values as input, you might need to recreate them (without specified length) to support objects larger than 16 MB. For example:

CREATE OR REPLACE FUNCTION udf_varchar(g1 VARCHAR)
  RETURNS VARCHAR
  AS $$
    'Hello' || g1
  $$;

For externally managed Iceberg tables, the default length for VARCHAR and BINARY columns is 128 MB. This default length applies to newly created or refreshed tables. If you have tables that were created in the past, smaller limits might apply to them. You can refresh these tables so that they support larger size limits.

For managed Iceberg tables, the default length for VARCHAR and BINARY columns is 128 MB. Tables that were created before the new size limits were enabled still have the previous default lengths. To apply the new size to columns of type VARCHAR in these tables, recreate the tables or alter the columns. The following example alters a column to use the new size limit:

ALTER ICEBERG TABLE my_iceberg_table ALTER COLUMN col1 SET DATA TYPE VARCHAR(134217728);

To apply the new size to columns of type BINARY in these tables, recreate the tables. You can’t alter the length of a BINARY column in an existing table.

Driver versions that support large objects in the result set¶

Drivers support objects larger than 16 MB (8 MB for BINARY, GEOMETRY, and GEOGRAPHY). You might need to update your drivers to the versions that support larger objects. The following driver versions are required:

If you try to use a driver that doesn’t support larger objects, an error similar to the following example is returned:

100067 (54000): The data length in result column <column_name> is not supported by this version of the client.
Actual length <actual_size> exceeds supported length of 16777216.

File sizing for Snowpipe¶

Snowpipe is designed to load new data typically within a minute after it learns about a new file. However, loading can take significantly longer for very large files, or when decompressing, decrypting, or transforming the new data requires an unusual amount of compute resources.

Snowpipe charges a fixed number of credits per GB of data loaded, with no per-file charge, so the number of files that you use to deliver your data doesn’t change what you pay to load it. For more information, see Snowpipe costs.

File size and staging frequency still affect load latency and throughput. For the most efficient load experience with Snowpipe, follow the file sizing recommendations in File sizing best practices (in this topic).

If it takes longer than one minute to accumulate 100 to 250 MB of compressed data in your source application, consider creating a new (potentially smaller) data file once per minute. This approach typically leads to a good balance between load latency and load efficiency. Creating smaller data files and staging them in cloud storage more often than once per minute doesn’t guarantee a reduction in latency between staging and loading the data.

Various tools can aggregate and batch data files. One convenient option is Amazon Data Firehose. Firehose allows defining both the intended file size, called the buffer size, and the wait interval after which a new file is sent (to cloud storage in this case), called the buffer interval. For more information, see the Amazon Data Firehose documentation. If your source application typically accumulates enough data within a minute to populate files larger than the recommended maximum for optimal parallel processing, you can decrease the buffer size to trigger delivery of smaller files. If you set the buffer interval to 60 seconds, Firehose delivers buffered data within a minute, or sooner when the buffer reaches the buffer size. This setting helps you avoid creating too many files or increasing latency.

Preparing delimited text files¶

Consider the following guidelines when preparing your delimited text (CSV) files for loading:

• UTF-8 is the default character set, however, additional encodings are supported. Use the ENCODING file format option to specify the character set for the data files. For more information, see CREATE FILE FORMAT.
• Fields that contain delimiter characters should be enclosed in quotes (single or double). If the data contains single or double quotes, then those quotes must be escaped.
• Carriage returns are commonly introduced on Windows systems in conjunction with a line feed character to mark the end of a line (\r \n). Fields that contain carriage returns should also be enclosed in quotes (single or double).
• The number of columns in each row should be consistent.

Semi-structured data files and subcolumnarization¶

Numeric data guidelines¶

• Avoid embedded characters, such as commas (for example, 123,456).
• If a number includes a fractional component, it should be separated from the whole number portion by a decimal point (for example, 123456.789).
• Oracle only. The Oracle NUMBER or NUMERIC types allow for arbitrary scale, meaning they accept values with decimal components even if the data type was not defined with a precision or scale. Whereas in Snowflake, columns designed for values with decimal components must be defined with a scale to preserve the decimal portion.

Date and timestamp data guidelines¶

• For information on the supported formats for date, time, and timestamp data, see Date and time input and output formats.
• Oracle only. The Oracle DATE data type can contain date or timestamp information. If your Oracle database includes DATE columns that also store time-related information, map these columns to a TIMESTAMP data type in Snowflake rather than DATE.