COPY INTO & Snowpipe: Batch and Continuous Ingestion

Load data in bulk or continuously as files arrive, without manual intervention.

COPY INTO reads files from a stage and inserts them into a table. It is how bulk data gets into Snowflake.

COPY INTO is like manually running a delivery truck on a schedule to pick up packages from a depot; Snowpipe is like having packages automatically loaded onto the truck the instant they arrive at the depot, with no dispatcher needed.

Key Concepts

    COPY INTO orders FROM @my_stage FILE_FORMAT = (FORMAT_NAME = csv_std);

The feature that makes it safe to schedule: it remembers. Snowflake records which files it has already loaded, by name and checksum, for 64 days.

Re-run the same statement and already-loaded files are skipped:

    run 1:  a.csv b.csv        -> both loaded
    run 2:  a.csv b.csv c.csv  -> only c.csv loaded

So a job can run every hour against the same stage without duplicating anything. No bookkeeping table, no moving files to a processed folder.

The two consequences people hit: reloading a file deliberately needs FORCE = TRUE, and a file that changes but keeps its name after 64 days will be loaded again.

FORCE = TRUE

Snowpipe removes the schedule. Instead of running COPY INTO yourself, Snowpipe loads each file shortly after it appears — triggered by a cloud storage event notification, or by a REST call:

COPY INTO

    CREATE PIPE orders_pipe AUTO_INGEST = TRUE AS
      COPY INTO orders FROM @lake_stage;

Latency from file landing to queryable is typically under a minute.

It is serverless, and that is the real difference. Snowpipe uses Snowflake-managed compute, billed per second of actual work. No warehouse to size, and nothing running while no files arrive.

Which one to use is a file-size question.

    many small files arriving continuously   -> Snowpipe
    few large files on a schedule            -> COPY INTO on a warehouse

Snowpipe has a per-file overhead, so thousands of tiny files cost more in overhead than in data. Snowflake's guidance is files of roughly 100-250 MB compressed; well under that and you are paying mostly to process file headers.

If your producer writes 2 KB files every second, the fix is upstream — batch them before they land, rather than accepting the ingest cost.

Debugging loads. COPY_HISTORY shows what loaded, what was skipped and why; PIPE_USAGE_HISTORY covers Snowpipe. A load that appears to do nothing is nearly always a file already recorded as loaded, or a file format that does not match the file.

COPY_HISTORYPIPE_USAGE_HISTORY

Error handling is a choice you should make explicitly. ON_ERROR defaults to aborting the whole load on the first bad row. CONTINUE skips bad rows, SKIP_FILE abandons that file. Aborting is usually right for financial data and wrong for clickstream, so decide rather than accept the default.

ON_ERRORCONTINUESKIP_FILE

What the interviewer is probing.1. "When do you choose Snowpipe over COPY?" Probing: the fit. Stalls: "Snowpipe is better." Moves up: continuous small-file arrival where latency matters; scheduled bulk loads are cheaper and simpler with COPY on a warehouse.

2. "What file size should you aim for?" Probing: the practical number. Stalls: "Any." *Moves up:* roughly 100-250MB compressed — many tiny files cost per-file overhead, and one huge file cannot be parallelised.

3. "How does Snowpipe avoid duplicate loads?" Probing: the mechanism. Stalls: "It checks the data." Moves up: load metadata tracks file names for 14 days, so a re-submitted file is skipped — which also means a renamed re-upload loads twice.

4. "How is Snowpipe billed?" Probing: the difference. Stalls: "By warehouse." Moves up: serverless credits per compute time plus a per-file overhead charge, with no warehouse of your own.