While inserting data into Snowflake, its good to be aware of limitations and practices that result in more accurate and faster insertion of data.
1. Inserting data via SQL statements while correct will not provide optimal performance
2. The fastest way to bulk insert data into Snowflake is using CSV files from your server, staged into Snowflake and then inserted into the database itself.
3. For very large tables, break up your rows into batches such that the size of the CSV files is of the order of several megabytes.
4. Use the PUT and COPY TO commands provided by Snowflake to move your CSV file to staging and then insert into the database.
5. Use compression on the CSV file for faster network performance and lower RAM scenarios.
6. Be exceptionally careful to specify the table schema (column names) properly in the CSV file.
7. Make sure that your values in the columns of the CSV file meet the data limits of Snowflake For example, ensure that text and binary data are not longer than Snowflake maximums. For example, ensure that BIGINT value is not present on a column defined as SMALLINT. Data mismatches will result in the entire CSV being rejected by Snowflake.
8. Verify the results of the PUT and COPY INTO for each batch.