Preparing your data files¶

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.

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.

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.

Continuous data loads — that is, Snowpipe — and file sizing¶

Snowpipe is designed to load new data typically within a minute after a file notification is sent; however, loading can take significantly longer for really large files or in cases where an unusual amount of compute resources is necessary to decompress, decrypt, and transform the new data.

In addition to resource consumption, an overhead to manage files in the internal load queue is included in the utilization costs charged for Snowpipe. This overhead increases in relation to the number of files queued for loading. This overhead charge appears as Snowpipe charges in your billing statement because Snowpipe is used for event notifications for the automatic external table refreshes.

For the most efficient and cost-effective load experience with Snowpipe, we recommend following the file sizing recommendations in File sizing best practices (in this topic). Loading data files roughly 100-250 MB or larger reduces the overhead charge relative to the amount of total data loaded to the point where the overhead cost is immaterial.

If it takes longer than one minute to accumulate MBs of 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 cost (that is, resources spent on Snowpipe queue management and the actual load) and performance (that is, load latency).

Creating smaller data files and staging them in cloud storage more often than once per minute has the following disadvantages:

• A reduction in latency between staging and loading the data can’t be guaranteed.

• An overhead to manage files in the internal load queue is included in the utilization costs charged for Snowpipe. This overhead increases in relation to the number of files queued for loading.

Various tools can aggregate and batch data files. One convenient option is Amazon Data Firehose. Firehose allows defining both the desired 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 could decrease the buffer size to trigger delivery of smaller files. Keeping the buffer interval setting at 60 seconds (the minimum value) helps 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¶

When semi-structured data is inserted into a VARIANT column, Snowflake uses certain rules to extract as much of the data as possible to a columnar form. The rest of the data is stored as a single column in a parsed semi-structured structure.

By default, Snowflake extracts a maximum of 200 elements per partition, per table. To increase this limit, contact Snowflake Support.

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.