When would you choose Snowpipe over a scheduled COPY INTO in Snowflake, and what does it cost?

## What a pipe is ```sql CREATE PIPE orders_pipe AUTO_INGEST = TRUE AS COPY INTO orders FROM @ext_orders FILE_FORMAT = (FORMAT_NAME = my_parquet); ``` A pipe is a named object holding a `COPY INTO` statement that Snowflake runs for you, one file at a time, as files show up. It is not a scheduler and not a stream processor — it is event-driven bulk load at file granularity. ## Two ways to trigger it **Auto-ingest.** `AUTO_INGEST = TRUE` on a pipe over an **external** stage makes Snowflake subscribe to a cloud notification channel. `SHOW PIPES` returns the channel to point your bucket's event configuration at (an SQS ARN on AWS; on Azure and GCS you wire Event Grid or Pub/Sub through a notification integration). When an object lands, the notification arrives and the file is queued for load. **REST.** A client calls the `insertFiles` endpoint listing files to ingest, using key-pair authentication. This is what the Kafka connector and many custom loaders do, and it is the option for internal stages, where no cloud notification exists. ## Latency, ordering and errors Snowpipe targets loading a file shortly after it is notified — think roughly a minute under normal conditions, longer when a queue backs up or files are very large. It makes **no ordering guarantee**: files are not necessarily loaded in the order they arrived, so anything order-sensitive must be reconstructed from a column in the data, not from arrival. Its default `ON_ERROR` is `SKIP_FILE`, not the bulk `COPY` default of `ABORT_STATEMENT` — a bad file is set aside and the stream keeps flowing. That is right for a continuous pipeline and wrong for a batch you need atomically. Pipe-level error notifications can push failures to a cloud notification service so a bad file is not discovered days later. Pipes keep their own load history, retained for a shorter window than the 64 days of table load metadata, and `SYSTEM$PIPE_STATUS('orders_pipe')` returns execution state, pending file count and the last error. `ALTER PIPE orders_pipe REFRESH` re-queues recently staged files that were missed — the usual repair after a notification outage — but its lookback is limited, so a long outage needs a manual `COPY` over the affected prefix instead. ## Cost model Snowpipe does not consume a virtual warehouse. Snowflake runs the load on its own compute and charges per compute-second used, **plus a fixed overhead per file processed**. Two consequences: - **Tiny files are expensive per byte.** A pipeline emitting a file per second pays the per-file overhead 86,400 times a day for very little data. Micro-batch at the producer instead — accumulate for roughly a minute and write one file in the usual 100–250 MB compressed range where volume permits. - **Large steady batches are cheaper on your own warehouse.** A nightly 2 TB dump loaded by a `COPY` on a right-sized warehouse that auto-suspends afterwards typically beats paying per-file serverless overhead for the same files. `SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY` gives credits and bytes per pipe, which is how you settle the argument with numbers rather than intuition. ## Choosing between them Reach for **Snowpipe** when: files arrive unpredictably and continuously; freshness of minutes is a product requirement; you do not want to own warehouse scheduling; the producer is a streaming sink already writing objects to a bucket. Reach for **scheduled `COPY`** when: data arrives in large periodic drops; you need all-or-nothing semantics or a validation dry run before loading; the load is one step of a larger transformation job that also needs a warehouse anyway; the volume is large enough that per-file overhead matters. A very common production shape is both: Snowpipe for the continuous stream into a raw landing table, plus a scheduled task that merges and reconciles, with a periodic `COPY` sweep as the backstop for anything the notification channel dropped. ## Snowpipe Streaming is a different thing Snowpipe Streaming is a separate API that writes **rows** into a table over a channel, with no files and no stage, aimed at lower latency than file-based Snowpipe. Know that it exists and that it is not the same product surface as a pipe over a stage; do not conflate their cost or trigger models. ## What interviewers want They want the cost sentence — serverless compute plus per-file overhead — the freshness sentence — about a minute, no ordering guarantee — and evidence that you would not pick a streaming path for a nightly batch just because it sounds modern.