Loading Data with Stages and COPY INTO

Snowflake Loads Files Through Stages, File Formats, and COPY INTO

Most Snowflake ingestion begins with files. A source system writes CSV, JSON, Parquet, or Avro to cloud storage. Snowflake reads those files through a stage. A file format tells Snowflake how to parse the files. COPY INTO loads the parsed data into a table.

S3 / ADLS / GCS / internal stage
        │
        ▼
Snowflake stage + file format
        │
        ▼
COPY INTO RAW table
        │
        ▼
Silver cleanup and Gold marts

Load CSV Data Correctly

CSV looks simple but causes many production bugs: embedded commas, quoted strings, blank values, header rows, inconsistent column counts, and weird encodings. A file format makes those rules explicit.

CREATE OR REPLACE FILE FORMAT RAW.CSV_WITH_HEADER
  TYPE = CSV
  FIELD_DELIMITER = ','
  SKIP_HEADER = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'
  NULL_IF = ('', 'NULL', 'null')
  ERROR_ON_COLUMN_COUNT_MISMATCH = FALSE;

CREATE OR REPLACE TABLE RAW.ORDERS_LOAD (
  order_id      STRING,
  customer_id   STRING,
  order_ts      TIMESTAMP_NTZ,
  status        STRING,
  order_amount  NUMBER(10,2),
  updated_at    TIMESTAMP_NTZ
);

CREATE OR REPLACE STAGE RAW.ORDERS_STAGE
  FILE_FORMAT = RAW.CSV_WITH_HEADER;

-- Files can be uploaded to internal stages with SnowSQL PUT,
-- or you can use an external stage over S3/ADLS/GCS in production.

COPY INTO RAW.ORDERS_LOAD
FROM @RAW.ORDERS_STAGE
FILE_FORMAT = RAW.CSV_WITH_HEADER
ON_ERROR = 'ABORT_STATEMENT';

Validate Before You Load

Snowflake lets you validate files before loading. This is useful when debugging new feeds or checking a partner file before it pollutes Raw tables. Validation catches parsing and conversion problems early.

COPY INTO RAW.ORDERS_LOAD
FROM @RAW.ORDERS_STAGE
FILE_FORMAT = RAW.CSV_WITH_HEADER
VALIDATION_MODE = RETURN_ERRORS;

External Stages Connect Snowflake to Cloud Storage

Production data usually lands in S3, Azure Blob/ADLS, or Google Cloud Storage. An external stage points Snowflake at that location. Real external stages require cloud IAM/storage integration setup. The exact syntax differs by cloud, but the design principle is the same: Snowflake receives scoped permission to read a specific storage location.

-- Example shape only. Real production setup also needs
-- a storage integration / IAM role configuration.

CREATE OR REPLACE STAGE RAW.S3_ORDERS_STAGE
  URL = 's3://company-data-prod/orders/'
  FILE_FORMAT = RAW.CSV_WITH_HEADER;

LIST @RAW.S3_ORDERS_STAGE;

COPY INTO RAW.ORDERS_LOAD
FROM @RAW.S3_ORDERS_STAGE
PATTERN = '.*orders_2026_09_.*[.]csv'
ON_ERROR = 'ABORT_STATEMENT';

Know What Loaded, What Failed, and Why

A load command that says "success" is not enough for production. You need to know which files loaded, how many rows loaded, how long it took, whether rows were skipped, and whether the same file was loaded twice or ignored because Snowflake already saw it.

SELECT
  table_name,
  file_name,
  status,
  row_count,
  row_parsed,
  error_count,
  first_error_message,
  last_load_time
FROM INFORMATION_SCHEMA.LOAD_HISTORY
WHERE table_name = 'ORDERS_LOAD'
ORDER BY last_load_time DESC;

• ✓Alert when files fail to load.
• ✓Alert when expected files do not arrive by a deadline.
• ✓Reconcile source row counts with loaded row counts.
• ✓Store batch IDs or file names when you need auditability.
• ✓Avoid blindly reloading files without understanding Snowflake load metadata.

Loading Mistakes That Break Trust