Snowflake – Loading Data with Stages and COPY INTO

Loading is where most Snowflake projects spend their first week, and where most of the avoidable pain lives. The mechanism itself is simple — put files somewhere Snowflake can see, then COPY INTO a table — but three things about it are not obvious: how stages differ, why file size matters so much, and what happens to the rows that fail.

Stages

A stage is a location holding files. There are four kinds, and picking the right one is mostly a question of who owns the storage.

Internal stages are Snowflake-managed storage you upload to with PUT. External stages point at a bucket you already own, which is what most production pipelines use — the data is being written there anyway, and Snowpipe (lesson 8) can watch it.

-- Internal, for files from your laptop.
CREATE STAGE learn_snowflake.staging.internal_stage
  FILE_FORMAT = (TYPE = CSV);

-- External, on S3. The storage integration holds the IAM role, so no keys
-- appear in SQL and no key ends up in query history.
CREATE STAGE learn_snowflake.staging.s3_stage
  URL = 's3://my-bucket/incoming/'
  STORAGE_INTEGRATION = my_s3_integration
  FILE_FORMAT = (TYPE = CSV);

LIST @learn_snowflake.staging.s3_stage;

Create the storage integration once, with an IAM role rather than embedded credentials. Putting an access key in a CREATE STAGE statement writes it into query history where anyone with the right role can read it.

File formats as objects

You can specify format options inline on every COPY, and you will get them subtly wrong somewhere. Define them once:

CREATE OR REPLACE FILE FORMAT learn_snowflake.staging.csv_standard
  TYPE                         = CSV
  FIELD_DELIMITER              = ','
  SKIP_HEADER                  = 1
  FIELD_OPTIONALLY_ENCLOSED_BY = '"'          -- quoted fields containing commas
  NULL_IF                      = ('', 'NULL', 'null', '\\N')
  EMPTY_FIELD_AS_NULL          = TRUE
  TRIM_SPACE                   = FALSE
  DATE_FORMAT                  = 'YYYY-MM-DD'
  TIMESTAMP_FORMAT             = 'YYYY-MM-DD HH24:MI:SS'
  COMPRESSION                  = AUTO;

CREATE OR REPLACE FILE FORMAT learn_snowflake.staging.json_standard
  TYPE                    = JSON
  STRIP_OUTER_ARRAY       = TRUE      -- a file that is one big [ ... ]
  COMPRESSION             = AUTO;

FIELD_OPTIONALLY_ENCLOSED_BY is the one people forget. Without it, a quoted address field containing a comma splits into two columns and every row after it is shifted — which fails loudly if the types differ and, far worse, silently if they do not.

Getting files in, and loading them

# PUT is a client-side command: it runs in the CLI, not in a worksheet.
# Files are compressed and encrypted in transit by default.
snow sql -q "PUT file:///data/orders_*.csv @learn_snowflake.staging.internal_stage \
             AUTO_COMPRESS=TRUE PARALLEL=8"

CREATE TABLE learn_snowflake.staging.orders (
  order_id     INT,
  customer_id  INT,
  order_date   DATE,
  status       STRING,
  total        NUMBER(12,2)
);

COPY INTO learn_snowflake.staging.orders
FROM   @learn_snowflake.staging.internal_stage
FILE_FORMAT = (FORMAT_NAME = learn_snowflake.staging.csv_standard)
PATTERN     = '.*orders_.*[.]csv[.]gz'
ON_ERROR    = 'ABORT_STATEMENT';

COPY INTO can also transform on the way in, which saves a staging table when the change is simple. The source is referenced positionally as $1, $2:

COPY INTO learn_snowflake.staging.orders (order_id, customer_id, order_date, status, total)
FROM (
  SELECT $1::INT,
         $2::INT,
         TRY_TO_DATE($3, 'YYYY-MM-DD'),
         UPPER($4),
         TRY_TO_NUMBER($5, 12, 2)
  FROM @learn_snowflake.staging.internal_stage
)
FILE_FORMAT = (FORMAT_NAME = learn_snowflake.staging.csv_standard);

File size is the performance lever

This is the part that is genuinely counter-intuitive, and it is the difference between a load that keeps up and one that does not.

A COPY distributes files across the threads of the warehouse. One thread handles one file. So the shape of your input, not the size of your warehouse, sets the ceiling:

• One enormous file — one thread does everything. A larger warehouse cannot help, because there is nothing to hand the other nodes. This is the most common reason a load is slow.
• Thousands of tiny files — per-file overhead dominates. Snowflake spends its time opening and closing rather than reading.
• Many medium files — every thread stays busy, and doubling the warehouse roughly halves the time.

Snowflake's own guidance is to aim for files of roughly 100–250 MB compressed. If your source produces one huge export, split it before loading — split -l, or a partitioned unload — and the load parallelises for free.

The same applies in reverse when unloading. COPY INTO <location> splits output into multiple files by default, and that default is right:

COPY INTO @learn_snowflake.staging.internal_stage/export/
FROM   learn_snowflake.staging.orders
FILE_FORMAT = (TYPE = PARQUET)
MAX_FILE_SIZE = 128000000            -- ~128 MB per file
HEADER = TRUE;

When rows fail

ON_ERROR decides what a bad row does to the rest of the batch:

Check before you commit to anything. VALIDATION_MODE runs the load without writing and reports what would have happened:

COPY INTO learn_snowflake.staging.orders
FROM   @learn_snowflake.staging.internal_stage
FILE_FORMAT     = (FORMAT_NAME = learn_snowflake.staging.csv_standard)
VALIDATION_MODE = 'RETURN_ERRORS';

-- Or: show me the first 10 rows as they would be parsed.
COPY INTO learn_snowflake.staging.orders
FROM   @learn_snowflake.staging.internal_stage
FILE_FORMAT     = (FORMAT_NAME = learn_snowflake.staging.csv_standard)
VALIDATION_MODE = 'RETURN_10_ROWS';

Choosing a file format for the source

If you control what the upstream system writes, the format is worth a moment's thought — it affects load speed, and it affects how much of the schema you have to restate.

For Parquet and other self-describing formats, INFER_SCHEMA saves writing the DDL by hand and, more usefully, saves getting one column's type subtly wrong:

-- What does Snowflake think is in these files?
SELECT *
FROM TABLE(INFER_SCHEMA(
  LOCATION => '@learn_snowflake.staging.s3_stage/orders/',
  FILE_FORMAT => 'learn_snowflake.staging.parquet_standard'));

-- Create the table straight from that inference.
CREATE TABLE learn_snowflake.staging.orders_inferred
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    FROM TABLE(INFER_SCHEMA(
      LOCATION => '@learn_snowflake.staging.s3_stage/orders/',
      FILE_FORMAT => 'learn_snowflake.staging.parquet_standard')));

Read the inferred DDL before you trust it. Inference will happily give you a FLOAT for a money column, which lesson 6 explains is the one place that matters.

The load metadata that skips your file

Snowflake records which files a table has already loaded and, for 64 days, refuses to load the same file again. That idempotence is a gift — a retried pipeline does not double-count — and it is also the source of the most confusing loading bug there is: you fix a file, re-upload it under the same name, run COPY, and nothing happens. No error. Zero rows loaded.

-- What did this table load, and what happened?
SELECT file_name, status, row_count, row_parsed, first_error_message, last_load_time
FROM   TABLE(information_schema.copy_history(
         TABLE_NAME  => 'LEARN_SNOWFLAKE.STAGING.ORDERS',
         START_TIME  => DATEADD('day', -7, CURRENT_TIMESTAMP())))
ORDER  BY last_load_time DESC;

-- Deliberately reload files already recorded as loaded.
COPY INTO learn_snowflake.staging.orders
FROM   @learn_snowflake.staging.internal_stage
FILE_FORMAT = (FORMAT_NAME = learn_snowflake.staging.csv_standard)
FORCE       = TRUE;

FORCE = TRUE is the escape hatch, and it is a loaded gun: it will happily load the same rows twice. The better habit is to give every file a unique name — a timestamp or a batch id in the path — so the question never arises.

Next: Snowpipe, for when a nightly COPY is not often enough.