Data Loading: Stages, COPY INTO & File Formats

Loading data into Snowflake is a fundamental task for any data engineer or analyst. Whether you're migrating from a legacy system, ingesting streaming data, or loading daily exports, understanding Snowflake's data loading mechanics is crucial for performance, cost, and reliability. Snowflake offers a powerful and flexible loading pipeline: stages, file formats, and the COPY INTO command. Stages act as temporary storage locations for your data files, either managed by Snowflake (internal) or in cloud storage (external). File formats define the structure and parsing rules for your data (CSV, JSON, Parquet, etc.). The COPY INTO command is the workhorse that loads data from a stage into a table, with options for error handling, validation, and transformation. In this tutorial, you'll learn how to set up stages, define file formats, and use COPY INTO to load data efficiently. We'll cover production-ready examples, common pitfalls, and debugging techniques to ensure your data loads are robust and performant.

Understanding Stages in Snowflake

Stages are logical locations where data files are stored before loading into tables. Snowflake supports two types: internal stages (managed by Snowflake) and external stages (pointing to cloud storage like S3, Azure Blob, or GCS). Internal stages come in three flavors: user stages (for individual users), table stages (automatically created per table), and named stages (reusable across tables). Named stages are best for production because they allow centralized management of file format and encryption settings. To create a named internal stage: CREATE OR REPLACE STAGE my_stage;. For external stages, you need to provide cloud storage credentials: CREATE OR REPLACE STAGE my_ext_stage URL='s3://my-bucket/path/' CREDENTIALS=(AWS_KEY_ID='...' AWS_SECRET_KEY='...');. Stages can also have directory tables for listing files. Use LIST @my_stage; to see files. Stages are the entry point for all data loading operations.

-- Create an internal named stage
CREATE OR REPLACE STAGE my_stage
  FILE_FORMAT = my_csv_format
  ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE');

-- Create an external stage pointing to S3
CREATE OR REPLACE STAGE my_ext_stage
  URL = 's3://my-bucket/data/'
  CREDENTIALS = (AWS_KEY_ID = 'AKIA...' AWS_SECRET_KEY = '...')
  FILE_FORMAT = my_json_format;

-- List files in the stage
LIST @my_stage;

Defining File Formats

File formats tell Snowflake how to parse your data files. You can define them as standalone objects or inline in COPY INTO. Common formats: CSV, JSON, Avro, Parquet, ORC, and XML. For CSV, key options include FIELD_DELIMITER, SKIP_HEADER, NULL_IF, and FIELD_OPTIONALLY_ENCLOSED_BY. For JSON, you can specify STRIP_OUTER_ARRAY or allow duplicate columns. Parquet and Avro are self-describing, so fewer options are needed. Create a file format: CREATE OR REPLACE FILE FORMAT my_csv_format TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1 NULL_IF = ('NULL', '') EMPTY_FIELD_AS_NULL = TRUE;. You can also set DATE_FORMAT, TIME_FORMAT, and TIMESTAMP_FORMAT to handle date/time strings. File formats can be shared across stages and COPY commands, ensuring consistency. Always test your file format with a SELECT query on stage files: SELECT $1, $2 FROM @my_stage (FILE_FORMAT => my_csv_format) LIMIT 10;.

-- CSV file format with header and null handling
CREATE OR REPLACE FILE FORMAT my_csv_format
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  NULL_IF = ('NULL', 'N/A', '')
  EMPTY_FIELD_AS_NULL = TRUE
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;

-- JSON file format
CREATE OR REPLACE FILE FORMAT my_json_format
  TYPE = JSON
  STRIP_OUTER_ARRAY = TRUE;

-- Parquet file format (minimal options)
CREATE OR REPLACE FILE FORMAT my_parquet_format
  TYPE = PARQUET;

-- Test parsing a file
SELECT $1, $2, $3 FROM @my_stage (FILE_FORMAT => my_csv_format) LIMIT 5;

The COPY INTO Command: Basics

COPY INTO is the primary command to load data from a stage into a table. Basic syntax: COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = my_csv_format);. You can also specify a list of files: FILES = ('file1.csv', 'file2.csv') or a pattern: PATTERN = '.2025.\.csv'. COPY INTO automatically tracks which files have been loaded to prevent duplicates (using metadata). To force reload, use FORCE = TRUE. You can also load into a specific subset of columns: COPY INTO my_table (col1, col2) FROM ... . Error handling options: ON_ERROR = 'CONTINUE' (skip bad rows), 'SKIP_FILE' (skip entire file), or 'ABORT_STATEMENT' (default). Use VALIDATION_MODE = RETURN_ERRORS to preview errors without loading. After a COPY, you can query the result with RESULT_SCAN. Example: COPY INTO my_table FROM @my_stage VALIDATION_MODE = RETURN_ERRORS; SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));.

-- Simple COPY INTO with file format
COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

-- Load specific files with pattern
COPY INTO my_table
  FROM @my_stage
  PATTERN = '.*2025.*\.csv'
  FILE_FORMAT = (FORMAT_NAME = my_csv_format);

-- Validate without loading
COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_csv_format)
  VALIDATION_MODE = RETURN_ERRORS;

-- Check load history
SELECT * FROM INFORMATION_SCHEMA.LOAD_HISTORY
  WHERE TABLE_NAME = 'MY_TABLE'
  ORDER BY LAST_LOAD_TIME DESC;

Transformations During COPY

Snowflake allows you to transform data during the COPY operation using SELECT expressions. This is powerful for cleaning, casting, or deriving columns on the fly. Syntax: COPY INTO my_table (col1, col2) FROM (SELECT $1::INT, $2::VARCHAR FROM @my_stage) FILE_FORMAT = (FORMAT_NAME = my_csv_format);. You can also use functions like TRY_CAST, NULLIF, or CASE statements. For JSON data, you can extract fields: COPY INTO my_table FROM (SELECT $1:id::INT, $1:name::VARCHAR FROM @my_stage) FILE_FORMAT = (FORMAT_NAME = my_json_format);. Transformations can also include subqueries referencing other tables, but be cautious about performance. Use transformations to handle data quality issues like trimming whitespace, converting date formats, or mapping codes to descriptions. This reduces the need for post-load cleanup.

-- Transform CSV data during load
COPY INTO my_table (id, name, age)
FROM (
  SELECT
    $1::INT AS id,
    TRIM($2) AS name,
    NULLIF($3, '')::INT AS age
  FROM @my_stage
)
FILE_FORMAT = (FORMAT_NAME = my_csv_format);

-- Extract fields from JSON
COPY INTO my_table (id, name)
FROM (
  SELECT
    $1:id::INT,
    $1:name::VARCHAR
  FROM @my_stage
)
FILE_FORMAT = (FORMAT_NAME = my_json_format);

-- Use CASE for data cleaning
COPY INTO my_table (status)
FROM (
  SELECT
    CASE WHEN $1 = 'active' THEN 'A'
         WHEN $1 = 'inactive' THEN 'I'
         ELSE 'U'
    END
  FROM @my_stage
)
FILE_FORMAT = (FORMAT_NAME = my_csv_format);

Error Handling and Validation

Production data loads often encounter errors: malformed files, type mismatches, or constraint violations. Snowflake provides several mechanisms to handle errors gracefully. The ON_ERROR option in COPY INTO controls behavior: 'ABORT_STATEMENT' (default) stops on first error, 'SKIP_FILE' skips the entire file, 'CONTINUE' skips bad rows and logs them. Use VALIDATION_MODE to test files before loading: RETURN_ERRORS returns all errors, RETURN_ALL_ERRORS includes warnings, and RETURN_ROW_COUNT returns row counts. After a load, you can query the result using RESULT_SCAN to see rejected rows. For ongoing monitoring, query INFORMATION_SCHEMA.LOAD_HISTORY and INFORMATION_SCHEMA.COPY_ERRORS. Example: SELECT * FROM TABLE(VALIDATE(my_table, job_id=>'...')). You can also set up notifications for load failures using Snowflake's event notifications.

-- Use ON_ERROR to skip bad rows
COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_csv_format)
  ON_ERROR = 'CONTINUE';

-- Validate and see errors
COPY INTO my_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_csv_format)
  VALIDATION_MODE = RETURN_ERRORS;

-- View rejected rows after a load
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

-- Query load history
SELECT * FROM INFORMATION_SCHEMA.LOAD_HISTORY
  WHERE TABLE_NAME = 'MY_TABLE' AND STATUS = 'FAILED';

Performance Best Practices

Loading large datasets efficiently requires attention to file size, parallelism, and warehouse size. Snowflake recommends files between 10-100 MB (compressed) for optimal performance. Larger files should be split into smaller chunks. Use multiple COPY statements in parallel to load different files simultaneously. You can also use a larger warehouse (e.g., X-SMALL to MEDIUM) to speed up loads, but note that COPY INTO automatically uses parallel processing regardless of warehouse size. For extremely large loads, consider using Snowpipe for continuous ingestion. Also, avoid loading into tables with many indexes or constraints during bulk loads; drop and recreate them after. Use the PURGE option to automatically delete files from the stage after successful load: COPY INTO ... PURGE = TRUE. Finally, compress files (gzip, bzip2, etc.) to reduce network transfer time.

-- Use a larger warehouse for faster loads
ALTER WAREHOUSE my_wh SET WAREHOUSE_SIZE = 'MEDIUM';

-- Load multiple files in parallel (separate sessions)
-- Session 1
COPY INTO my_table FROM @my_stage PATTERN = '.*part1.*';
-- Session 2
COPY INTO my_table FROM @my_stage PATTERN = '.*part2.*';

-- Purge files after successful load
COPY INTO my_table FROM @my_stage PURGE = TRUE;

-- Check load performance
SELECT * FROM INFORMATION_SCHEMA.LOAD_HISTORY
  WHERE TABLE_NAME = 'MY_TABLE'
  ORDER BY BYTES_LOADED DESC;

Loading Semi-Structured Data (JSON, Parquet)

Snowflake excels at loading semi-structured data like JSON, Avro, Parquet, and ORC. For JSON, you can load the entire document into a VARIANT column or extract fields during COPY. Parquet and Avro are columnar formats that preserve schema; Snowflake can infer the schema automatically. Example: CREATE TABLE my_json_table (data VARIANT); COPY INTO my_json_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = my_json_format);. Then query using dot notation: SELECT data:id, data:name FROM my_json_table. For Parquet, you can load directly into a table with columns matching the Parquet schema. Use MATCH_BY_COLUMN_NAME to align columns by name instead of position: COPY INTO my_table FROM @my_stage FILE_FORMAT = (FORMAT_NAME = my_parquet_format) MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';. This is especially useful when Parquet files have columns in different order.

-- Load JSON into a VARIANT column
CREATE OR REPLACE TABLE raw_json (data VARIANT);
COPY INTO raw_json
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_json_format);

-- Query JSON data
SELECT data:id::INT AS id, data:name::VARCHAR AS name
FROM raw_json;

-- Load Parquet with column matching
CREATE OR REPLACE TABLE parquet_table (id INT, name VARCHAR);
COPY INTO parquet_table
  FROM @my_stage
  FILE_FORMAT = (FORMAT_NAME = my_parquet_format)
  MATCH_BY_COLUMN_NAME = 'CASE_INSENSITIVE';