Using the COPY Command in Snowflake and Advanced Options

Data loading processes are one of the most critical aspects of data engineering. Snowflake’s COPY command makes these processes fast, flexible, and reliable, especially when transferring large data sets from sources such as AWS S3, Azure Blob Storage, or Google Cloud Storage into our tables. With various settings and options, the COPY command makes these tasks much more manageable. This article explores the details and advanced options of the COPY command.

Loading Data with the COPY Command (COPY Object)

The COPY command is a core tool for loading data from external sources into Snowflake tables. The command allows us to efficiently load data into our tables, even when dealing with various file formats. The basic structure of the COPY command is as follows:

COPY INTO target_table
FROM 's3://bucket_address/path/'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

The above code loads CSV files from an Amazon S3 bucket into the table named target_table. The FILE_FORMAT parameter specifies the details of the file format.

Data Validation with Validation Mode

Validating data during loading is essential to ensure the quality of the data being loaded. The COPY command’s VALIDATION_MODE parameter helps us catch errors during the loading process. Snowflake offers two important options:

• RETURN_ERRORS: Returns only the erroneous rows and stops the loading process.
• RETURN_ALL_ERRORS: Returns all types of errors, allowing for a more detailed error analysis.

By using validation mode, potential errors can be identified during the data loading process, allowing for preemptive solutions.

COPY INTO target_table
FROM 's3://bucket_address/path/'
VALIDATION_MODE = 'RETURN_ERRORS'
FILE_FORMAT = (TYPE = 'CSV' FIELD_OPTIONALLY_ENCLOSED_BY='"');

Size Limit – Maximum File Size

When loading data, remember that large files can impact performance. Snowflake allows you to set a maximum file size for loading, which helps optimize memory usage during the loading of large datasets.

Returning Only Failed Rows in Case of Errors (ON_ERROR)

The ON_ERROR parameter specifies how to handle errors during the data load. If you want to detect errors and only return the erroneous rows while loading other data, you can use the following syntax:

COPY INTO target_table
FROM 's3://bucket_address/path/'
ON_ERROR = 'RETURN_FAILED_ONLY';

This parameter ensures that only the failed rows are returned conditionally during the loading process, while the rest of the data is loaded. This helps avoid operational interruptions and ensures business continuity.

Truncating Columns During Loading (Truncate Columns)

Sometimes, the data to be loaded exceeds the column length in the target table, causing errors. In such cases, the TRUNCATECOLUMNS parameter can be used to truncate any extra characters that exceed the column length, ensuring compatibility:

COPY INTO target_table
FROM 's3://bucket_address/path/'
TRUNCATECOLUMNS = TRUE;

This setting minimizes data loss while maintaining compatibility during loading.

COPY Option – Forcing Load (Force)

The FORCE parameter is used to forcefully reload data even if it already exists in the table. This option is particularly useful when refreshing or updating data in a table:

COPY INTO target_table
FROM 's3://bucket_address/path/'
FORCE = TRUE;

Load History

Snowflake tracks the history of loading operations, allowing you to see which files have been loaded or which loading operations have failed. This makes it easy to analyze the conditions that caused issues during data loading:

SELECT * FROM INFORMATION_SCHEMA.LOAD_HISTORY
WHERE TABLE_NAME = 'target_table';

Flattening Data

When working with structured data like JSON, Snowflake provides the FLATTEN function, which allows you to flatten nested data structures for easier processing:

SELECT VALUE
FROM target_table,
LATERAL FLATTEN(input => target_table.json_column);

This query flattens JSON data, converting each element into a separate row, making it easier to process.