Ingestion That Never Sleeps: Snowpipe, Streaming, and the Way Back Out

COPY INTO is a batch verb — somebody has to decide when to run it. This chapter is about deleting that somebody: Snowpipe's auto-ingest wiring, Snowpipe Streaming's row-based path, the file-size tax that quietly bills you, and the unload direction that hands data back to the rest of the world.

Look hard at the last chapter and you’ll notice something the syntax hides: every load had a human in it. You staged the file. You typed the COPY INTO. You read the error report and decided whether 997 of 1,000 rows was good enough. That’s fine when data arrives on your schedule — a monthly export, a backfill, a one-off from finance. It stops being fine the moment data starts arriving on its own schedule, which is what happens to every pipeline that survives contact with production.

COPY INTO is a batch verb. It does exactly what it’s told, exactly once, when told. So the interesting question isn’t “how do I load a file?” — you already know — but “who decides when to load?” And the honest answers are a depressing little ladder. A person, at first. Then a cron job on somebody’s laptop. Then an Airflow DAG that runs every fifteen minutes and mostly loads nothing — and then every minute, so now you pay for a warehouse to wake up sixty times an hour and discover there’s no work.

Continuous ingestion is the discipline of deleting that somebody. Snowflake gives you two ways to do it and one way to go backwards, and this chapter is all three: Snowpipe, which turns a file landing in a bucket into the trigger; Snowpipe Streaming, which deletes the file entirely and takes rows; and COPY INTO <location>, which runs the whole apparatus in reverse to hand your data to somebody else. Plus the decision in the middle of it that quietly sets your ingest bill: how big the files are.

Everything here builds directly on stages and COPY INTO. If a stage, a file format, and ON_ERROR aren’t yet reflexes, go back — none of what follows makes sense without them, because all of it is COPY INTO wearing a different hat.

Three latencies, three cost models

Before any syntax, get the shape of the decision in your head. There are three ways data gets into a Snowflake table from the outside, and they are not three points on a smooth curve — they’re three different machines with three different bills.

Batch COPY INTO. You (or an orchestrator you own) run a COPY on a warehouse you own. Latency is however often you run it. Cost is warehouse credits, billed per second while the warehouse is up, so the load competes for — and benefits from — the same compute your queries use. Good: total control, the full ON_ERROR surface, VALIDATION_MODE, FORCE, PURGE, all-or-nothing semantics across a whole set of files. Bad: somebody owns the schedule, and the warehouse spins up whether or not there’s anything to do.

Snowpipe. Files still arrive as files, but Snowflake watches the stage and loads each one within about a minute of it landing. Latency is roughly one minute plus your file cadence. Cost is serverless — Snowflake’s compute, not yours, on a separate meter that never touches your warehouse. Good: no scheduler, no idle warehouse, scales with arrival rate. Bad: no ABORT_STATEMENT, no transactional grouping, no dry run, and a file-size problem we’ll spend real time on.

Snowpipe Streaming. No files at all. A client — your app, a Kafka connector — pushes rows over a channel, and they become queryable in seconds. Latency is sub-ten-second, often around five. Cost is per uncompressed GB ingested, a third meter again. Good: the only real option when a minute is genuinely too slow, and it deletes the entire staging-file apparatus. Bad: it’s an API, so somebody has to write and operate a producer, and that somebody is you.

The ladder matters because most teams reach for a lower rung than they need. The question that decides it is not “how fresh could the data be?” — the answer is always “fresher!” — but “how fresh does the data need to be before somebody acts differently?” If the dashboard is read at 9am and the decision is made at 10am, a nightly COPY is correct and everything else is expensive theatre. If a fraud rule fires in-session, streaming isn’t a luxury. Most of the real world lives on the middle rung, which is why Snowpipe is the bulk of this chapter.

Snowpipe: a COPY that watches the door

A pipe is a Snowflake object that wraps a COPY INTO statement and can be triggered by something other than you. That’s the entire concept. Here it is:

CREATE TABLE IF NOT EXISTS orders (
    o_orderkey    NUMBER,
    o_custkey     NUMBER,
    o_orderstatus VARCHAR(1),
    o_totalprice  NUMBER(12,2),
    o_orderdate   DATE,
    region        VARCHAR
);

CREATE OR REPLACE PIPE orders_pipe
  AUTO_INGEST = TRUE
AS
  COPY INTO orders
  FROM @my_ext_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = csv_orders)
  ON_ERROR = SKIP_FILE;

That’s the same orders shape the rest of the book uses — TPCH’s order columns plus a region — and the same @my_ext_stage, csv_orders, and s3_orders storage integration you built last chapter. (In the next chapter this table gets a proper home as sales.public.orders, owned by SYSADMIN rather than by whoever happened to be logged in. For now it’s the same table, one namespace shallower.)

Read the CREATE PIPE carefully and notice how little is new. The body is a COPY INTO. The stage is a stage. The file format is the file format. The only new thing is AUTO_INGEST = TRUE, and what that says is: don’t wait for me to call you.

What a pipe is not

Before the wiring, the fine print — because the ways a pipe differs from a bulk COPY are exactly the ways people get surprised.

Several copy options are not supported inside a pipe, and the list tells you what a pipe fundamentally is:

  • ON_ERROR = ABORT_STATEMENT — unsupported. A pipe’s default is SKIP_FILE, and the alternatives are CONTINUE and the SKIP_FILE_n family. Think about why: ABORT_STATEMENT means “roll the whole load back,” but a pipe has no “whole load” — it’s a stream of independent per-file loads with no transaction spanning them. There is nothing to abort into.
  • VALIDATION_MODE — unsupported. A dry run needs a run to be dry. A pipe fires on its own; there’s no moment to interpose a rehearsal.
  • PURGE and FORCE — unsupported. No auto-delete of staged files after load, and no way to say “load it again anyway.”
  • SIZE_LIMIT, FILES, RETURN_FAILED_ONLY — also out.

PATTERN is supported, and you’ll be tempted to use it to make one pipe ignore files it doesn’t care about. Resist: Snowflake’s own advice is to filter on the cloud provider’s event configuration instead (an S3 prefix/suffix rule), because a PATTERN filter means the event still reaches Snowpipe, still gets processed, and then gets discarded. Filtering at the source is strictly cheaper than filtering at the destination.

And the one that bites hardest, months later: a pipe keeps its own load history, retained for 14 days, separate from the 64-day table-level load metadata from last chapter. Which means CREATE OR REPLACE PIPE drops that history. Recreate a pipe over a stage that still holds a thousand already-loaded files, REFRESH it, and you will cheerfully load all thousand a second time. If you must recreate a pipe, pause it first, and know exactly which files are still sitting in that stage.

The wiring: S3 event → SQS → pipe

AUTO_INGEST = TRUE doesn’t magically watch your bucket. It says “I will listen on a queue.” Your job is to make the bucket talk to that queue. On AWS the chain is: object lands in S3 → S3 emits an event notification → the event goes to an SQS queue that Snowflake created and owns → Snowpipe reads it and runs the COPY for that file.

You never create the queue. Snowflake made it when you made the pipe, and it hands you the address:

SHOW PIPES LIKE 'orders_pipe';

Look at the notification_channel column. It holds an ARN like arn:aws:sqs:us-east-1:123456789012:sf-snowpipe-AIDA.... (DESC PIPE orders_pipe; shows you the pipe’s definition and the same channel.) That ARN is the whole handoff. Copy it.

Now go to the S3 bucket — console, CloudFormation, Terraform, whatever you use — and add an event notification:

  • Event types: s3:ObjectCreated:* (the “All object create events” checkbox).
  • Prefix: the path your pipe’s stage points at — orders/. This is the filter that means your pipe isn’t woken up by every unrelated object in the bucket.
  • Destination: SQS Queue → Enter SQS queue ARN → paste the notification_channel.

Save it. Drop a file into s3://my-bucket/orders/. Within about a minute, rows appear. No warehouse ran. No cron fired. Nobody typed COPY.

The IAM permissions are worth one sentence, because “it silently doesn’t work” is the failure mode. The role your storage integration assumes needs the usual read permissions (s3:GetObject, s3:GetObjectVersion, s3:ListBucket) on the prefix — the same ones bulk COPY needed. The SQS side needs nothing from you, because the queue is Snowflake’s. If files land and nothing happens, the first thing to check is not the pipe; it’s whether the bucket’s event notification actually got saved with the right prefix.

The SNS variant, for buckets that already have opinions

One bucket, one S3 event notification per event-type-and-prefix combination. If something else already subscribes to s3:ObjectCreated:* on that prefix — a Lambda, another team’s pipeline — you can’t just add a second SQS destination and call it a day. The clean answer is to fan out through SNS: point the S3 event at an SNS topic, and let every consumer subscribe to that topic, Snowpipe included.

Snowflake supports this directly. Give the pipe the topic ARN and it subscribes its own SQS queue to your topic:

CREATE OR REPLACE PIPE orders_pipe
  AUTO_INGEST = TRUE
  AWS_SNS_TOPIC = 'arn:aws:sns:us-east-1:123456789012:s3-object-created'
AS
  COPY INTO orders
  FROM @my_ext_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = csv_orders);

Now the S3 notification goes to SNS, and SNS fans out to Snowpipe’s queue and to everyone else’s. Reach for this the moment the bucket is shared, which in a real company is roughly always.

GCS and Azure

The shape is identical; the plumbing has different names, and both need one extra Snowflake object — a notification integration — where AWS needed none.

For GCS, the bucket publishes to a Pub/Sub topic, and you create an integration bound to that topic’s subscription:

CREATE NOTIFICATION INTEGRATION gcs_orders_notify
  TYPE = QUEUE
  NOTIFICATION_PROVIDER = GCP_PUBSUB
  ENABLED = TRUE
  GCP_PUBSUB_SUBSCRIPTION_NAME = 'projects/my-project/subscriptions/orders-sub';

CREATE OR REPLACE PIPE orders_pipe
  AUTO_INGEST = TRUE
  INTEGRATION = 'GCS_ORDERS_NOTIFY'   -- uppercase, and quoted
AS
  COPY INTO orders
  FROM @my_gcs_stage/orders/
  FILE_FORMAT = (FORMAT_NAME = csv_orders);

For Azure, the storage account publishes to Event Grid, Event Grid delivers to a Storage Queue, and the integration is NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE with the queue’s URL and your tenant ID. Same three moves in all three clouds: the cloud emits an event, a queue holds it, Snowpipe drains it. Learn the pattern once and the provider is a detail.

Note the small trap in the INTEGRATION value above: it’s a string, and Snowflake wants it in the case the catalog stores it — which, for an unquoted CREATE NOTIFICATION INTEGRATION gcs_orders_notify, is GCS_ORDERS_NOTIFY. Pass it lowercase and the pipe won’t find it: identifier folding, leaking into a string literal.

Operating a pipe

A pipe is a piece of infrastructure that runs when you’re asleep, so the interesting part isn’t creating it — it’s the four questions you’ll ask it at 8:15am when somebody says the numbers look wrong.

”Is it alive?” — SYSTEM$PIPE_STATUS

SELECT SYSTEM$PIPE_STATUS('orders_pipe');

It returns a JSON blob, and the fields you care about are these:

  • executionState — RUNNING is what you want. PAUSED means someone paused it. STOPPED_MISSING_TABLE and friends mean the pipe compiled fine once and the world moved out from under it (somebody dropped the target table, or the stage).
  • pendingFileCount — files Snowpipe knows about and hasn’t loaded yet. This is your backlog gauge. A steady zero-to-small number is healthy. A number climbing all morning means arrivals are outrunning ingestion, and you should go look at file sizes.
  • oldestFileTimestamp / oldestPendingFilePath — how far behind the backlog stretches, and which file is at the back of the queue. If pendingFileCount is 4 but the oldest has been pending for six hours, that’s not a backlog, that’s a stuck file.
  • lastIngestedTimestamp and lastIngestedFilePath — the last thing that actually made it in. If this is stale and pendingFileCount is 0, nothing is arriving: your problem is upstream of Snowflake.
  • notificationChannelName and numOutstandingMessagesOnChannel — the queue and its depth. A queue with messages and a pipe that isn’t ingesting is a permissions or configuration story.
  • lastPipeErrorTimestamp / error — the last time the pipe’s COPY failed to compile (a column that no longer exists, say), and why.
  • lastPipeFaultTimestamp / fault — an internal Snowflake error. Rare; if you see it, it’s a support ticket, not a bug in your SQL.

The diagnostic flowchart writes itself. Nothing loading, queue empty, nothing pending → the bucket isn’t notifying you; check the S3 event config. Nothing loading, queue has messages → the pipe isn’t draining; check executionState. Loading, but behind → your files are too small and too many. Loading, but rows are missing → that’s not a pipe problem, that’s an ON_ERROR problem — and here’s the tool for it.

”What did it reject?” — VALIDATE_PIPE_LOAD

Bulk COPY has VALIDATE(), which replays the errors from a specific query ID. A pipe has no query you ran, so it has its own function keyed on the pipe and a time window:

SELECT file, error, line, column_name, rejected_record, row_number
FROM TABLE(VALIDATE_PIPE_LOAD(
  PIPE_NAME  => 'MY_DB.PUBLIC.ORDERS_PIPE',
  START_TIME => DATEADD('hour', -24, CURRENT_TIMESTAMP())
));

PIPE_NAME wants the fully qualified name, as a string. START_TIME is required and must fall within the last 14 days (there’s an optional END_TIME too). You get one row per error: the file, the parse error, the line and column, and the raw rejected_record — the same forensic detail VALIDATE() gives you, scoped to a pipe instead of a job.

This is the function that makes ON_ERROR = SKIP_FILE survivable. A pipe’s default is to quietly skip a bad file and keep going, which is right for a service that must not stop — but quietly is the word that costs you a week when nobody looks. Put a VALIDATE_PIPE_LOAD query on a dashboard. A pipe with no error monitoring is lying to you by omission.

For the same reason, wire up ERROR_INTEGRATION — a notification integration that pushes pipe errors out to SNS / Pub/Sub / Event Grid, so failures come to you instead of waiting to be discovered.

ALTER PIPE orders_pipe SET ERROR_INTEGRATION = pipe_errors_notify;

And because a pipe’s loads are still loads, they all show up in COPY_HISTORY — with the PIPE_NAME column populated, which bulk copies leave null:

SELECT file_name, status, row_count, error_count, first_error_message,
       last_load_time
FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(
  TABLE_NAME => 'ORDERS',
  START_TIME => DATEADD('hour', -6, CURRENT_TIMESTAMP())
))
WHERE pipe_name IS NOT NULL
ORDER BY last_load_time DESC;

That’s your “what actually got ingested overnight” query — run it before you go spelunking in SYSTEM$PIPE_STATUS.

”Turn it off” — pausing, and the pause you’ll regret

Pipes get paused — a schema migration, an upstream system emitting garbage, a cutover. One statement:

ALTER PIPE orders_pipe SET PIPE_EXECUTION_PAUSED = TRUE;
-- ... do the surgery ...
ALTER PIPE orders_pipe SET PIPE_EXECUTION_PAUSED = FALSE;

The pipe owner can do this; so can a role holding OPERATE on the pipe — which is the grant you want for an on-call engineer who should be able to stop a bad pipe at 3am without owning it.

While the pipe is paused, the bucket keeps emitting events and the queue keeps holding them — for a while. Stay paused more than 14 days and those queued messages start aging out, at which point Snowflake refuses a plain resume rather than silently ingesting a partial set. Restarting then requires an explicit acknowledgment that files may have been missed:

SELECT SYSTEM$PIPE_FORCE_RESUME('orders_pipe', 'staleness_check_override');

The same function takes an ownership_transfer_check_override argument, used after transferring a pipe’s ownership so the new owner deliberately accepts a queue they’ve never looked at. Both overrides exist to make you say out loud that you might be about to lose or duplicate data.

”Catch up on what I missed” — ALTER PIPE … REFRESH

Auto-ingest only sees files that arrive after the notification is wired. Files that landed before, or while the pipe was paused, or during the hour the S3 event config was misconfigured, are invisible to it — no event, no load. The fix:

ALTER PIPE orders_pipe REFRESH;

REFRESH walks the stage, compares against the pipe’s load history, and queues anything it hasn’t already loaded. You can narrow it:

ALTER PIPE orders_pipe REFRESH
  PREFIX = 'orders/2026-07/'
  MODIFIED_AFTER = '2026-07-10T00:00:00-07:00';

Two constraints, both easy and expensive to miss. First, REFRESH only stages files modified in the last 7 days — and MODIFIED_AFTER has a maximum (and default) lookback of 7 days too. It is not a backfill tool. To load a month of files that never got notified, use a plain COPY INTO on a warehouse, where you have FORCE and VALIDATION_MODE and the whole control surface.

Second, Snowflake frames REFRESH explicitly as a troubleshooting command, not an operating procedure. If you’re calling it on a schedule, your notification wiring is broken and you’re papering over it. Fix the wiring. The right use is the ten minutes after you create a pipe over a bucket that already has files in it: create, wire the notification, REFRESH once to sweep up what’s already there, and let auto-ingest carry it from there.

What Snowpipe actually costs (and the file-size tax)

Here’s the part that decides whether your pipeline is a line item or an incident.

Snowpipe does not use your warehouse. It runs on Snowflake-managed serverless compute: it doesn’t compete with your queries, doesn’t keep a warehouse warm, and — the part people miss — doesn’t show up when you look at warehouse credit usage. It’s a separate meter, and a team can spend months tuning warehouse sizes while a pipe quietly outspends all of them, because nobody was looking at the right table.

That table is PIPE_USAGE_HISTORY:

SELECT pipe_name,
       DATE_TRUNC('day', start_time) AS day,
       SUM(credits_used) AS credits,
       SUM(bytes_inserted) / POWER(1024, 3) AS gb_inserted,
       SUM(files_inserted) AS files
FROM SNOWFLAKE.ACCOUNT_USAGE.PIPE_USAGE_HISTORY
WHERE start_time >= DATEADD('day', -30, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY day DESC, credits DESC;

Look at that query’s shape: credits, bytes, and files, side by side. That juxtaposition is the whole lesson, and it’s about to be why.

The billing model changed, and most of the internet hasn’t noticed

If you read a Snowpipe blog post written before 2026, it will tell you Snowpipe bills two things: the serverless compute that does the load (per-second, per-core), plus an overhead of 0.06 credits per 1,000 files. That per-file charge was the source of the single most famous Snowflake cost horror story — the team that streamed one tiny JSON object per file, landed ten million files a day, and paid six hundred credits a day in overhead alone, before a byte was parsed.

That is not the model any more. Snowpipe moved to flat throughput pricing: a fixed credit amount per GB ingested, with no per-second compute charge and no per-1,000-files fee. Text formats (CSV, JSON, XML) are billed on their uncompressed size; binary formats (Parquet, Avro, ORC) on their observed size, compressed or not. For most accounts this was an enormous cut — the small-file penalty simply evaporated from the bill.

I am deliberately not printing the rate. Snowflake’s own cost documentation doesn’t either: it states the shape of the model and points you at the Snowflake Service Consumption Table for the number, because the number varies by edition and region and moves. Anyone quoting you a hard credits-per-GB figure from memory — this book included — is quoting a snapshot. Read it from the table, and read your own bill from PIPE_USAGE_HISTORY.

Two things follow, and they pull in opposite directions, which is why you have to hold both.

One: stop quoting the old number. If your cost model, your internal wiki, or your vendor’s comparison chart is built on 0.06 credits per 1,000 files, it is describing a world that no longer exists. Go look at PIPE_USAGE_HISTORY and see what you’re actually billed.

Two: small files are still a bad idea. The billing penalty is gone. The engineering penalty is not, and it never was really about the invoice:

  • Latency doesn’t improve below about a minute anyway. Snowflake’s docs are blunt: staging files more often than once a minute “can’t guarantee a reduction in latency.” Cutting a 60-second file into six 10-second files buys you nothing but six times the files. If you need faster than a minute, files are the wrong abstraction and Snowpipe Streaming is the answer — not a smaller file.
  • Per-file work is still real work. Every file is an object to list, fetch, open, parse, and account for. Ten thousand 20 KB files take dramatically longer to ingest than one 200 MB file holding the same bytes, and that shows up as a climbing pendingFileCount — a backlog, which is a latency problem even when it isn’t a cost problem.
  • The downstream damage outlives the load. Tiny files make tiny writes make poorly-populated micro-partitions, and that bill arrives later, in every query that scans the table, forever. More in micro-partitions and clustering; the short version is that a table built out of ten million dribbles scans badly for the rest of its life.

So the guidance survives the pricing change intact, and it’s Snowflake’s own: aim for files roughly 100–250 MB compressed, or larger. If your producer accumulates that much within a minute, you get good file sizes and minute-level latency for free. If it doesn’t, the documented compromise is one file per minute, whatever size that turns out to be. Below that you’re buying nothing.

Which makes the operational rule: put the aggregation upstream. A producer that buffers for sixty seconds and writes one file is a better citizen than one that writes per event and asks Snowflake to sort it out. If you can’t change the producer, land the dribble in a cheap staging prefix, compact it with something dumb (a Lambda, a scheduled job), and let the pipe watch the compacted prefix instead. Compaction is boring, and it is almost always the right answer.

Snowpipe Streaming: deleting the file

Everything above still has a file in it — and the file is a staging artifact: a bucket you pay for, an event you wire, a size you tune, a latency floor you can’t get under. If your data is genuinely a stream of rows, that file is pure overhead standing between the row and the table.

Snowpipe Streaming removes it. A client opens a channel to a table and writes rows; Snowflake buffers them server-side, commits them, and makes them queryable. No stage. No PUT. No bucket. No SQS. Rows in, rows queryable, in as little as five seconds end to end, at up to 10 GB/s per table on the current architecture.

Before you go read the documentation, know that the naming is in flux and will confuse you. There are two architectures. The original — now called the classic architecture — is the snowflake-ingest-sdk Java SDK, and Snowflake has said it is planned for deprecation. The current one is Snowpipe Streaming with high-performance architecture, GA since September 2025. Existing classic workloads keep working; new work should start on high-performance. If a tutorial has you instantiating a SnowflakeStreamingIngestClient from snowflake-ingest-sdk, check its date.

The high-performance architecture reuses a name you now know: the PIPE. A streaming pipe is a CREATE PIPE whose source isn’t a stage but a synthetic streaming source:

CREATE OR REPLACE PIPE orders_stream_pipe
AS
  COPY INTO orders (o_orderkey, o_custkey, o_orderstatus,
                    o_totalprice, o_orderdate, region)
  FROM (
    SELECT $1:orderkey::NUMBER,
           $1:custkey::NUMBER,
           $1:orderstatus::VARCHAR,
           $1:totalprice::NUMBER(12,2),
           $1:orderdate::DATE,
           $1:region::VARCHAR
    FROM TABLE(DATA_SOURCE(TYPE => 'STREAMING'))
  );

Look at what that is. It’s COPY INTO. Again. Same verb, same transform-on-load SELECT from last chapter, same $1 — except $1 is now the incoming row rather than a CSV column, and the source is DATA_SOURCE(TYPE => 'STREAMING') rather than a stage. The in-flight transformation surface (cast this, drop that, stamp a timestamp) is the one you already know. That symmetry isn’t an accident: Snowflake really only has one loading verb, pointed at four different things.

The client side is where you actually do work. The high-performance SDKs — Java, Python, and Node.js, all built on a shared Rust core — plus a REST API for lightweight producers, open a channel against the pipe and append rows. Channels give you the property that matters most for streaming ingest: exactly-once delivery, via an offset token you advance as you commit, so a client that crashes and reconnects resumes where it left off rather than double-writing. If you’re on Kafka you write none of this — the Snowflake Kafka connector speaks Snowpipe Streaming natively and maps topics to tables, which is how most streaming ingest into Snowflake actually happens.

Billing is a third model: per uncompressed GB ingested, measured on the input bytes the service received, not the bytes that end up in the table. High-performance streaming has no serverless-compute charge and no per-client-connection charge — just throughput. (Classic charged for both, which is another reason to be on the new one.) Watch the spend through METERING_HISTORY filtered to the streaming service type, or the cost pages in Snowsight.

When does streaming actually beat Snowpipe? Three cases, and I’d want to see one of them before signing off on operating a streaming client:

  1. The business acts in seconds. Fraud scoring, live ops dashboards, an alert that fires while the user is still on the page. If nobody looks at the number for an hour, you don’t have this case.
  2. The source is already a stream. You have Kafka. You have a service emitting events. Batching those into 200 MB files, writing them to a bucket, and wiring event notifications is an elaborate way to un-stream a stream — you’re building a file layer for the privilege of tearing it back down.
  3. The file layer is the cost. Millions of small events where landing files is genuinely painful — the small-file problem, solved by not having files.

If none of those hold, Snowpipe is simpler, and simpler wins. There’s no prize for the lowest latency nobody uses.

The way back out: COPY INTO <location>

Enough about getting in. The last chapter mentioned in passing that COPY INTO runs backwards; here’s the whole of it, because “how do I get data out of Snowflake” is a question that arrives sooner than anyone expects and is answered badly more often than it should be.

COPY INTO <location> takes a stage or a bucket path instead of a table, and a query instead of a set of files, and writes the results out:

COPY INTO @my_ext_stage/exports/orders/
FROM (
  SELECT o_orderkey, o_custkey, o_orderstatus,
         o_totalprice, o_orderdate, region
  FROM orders
  WHERE o_orderdate >= '2026-01-01'
)
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"')
HEADER = TRUE
MAX_FILE_SIZE = 100000000;

By default this splits the output across many files, one per parallel thread, because that’s how you get a 400 GB unload to finish before lunch. The knobs:

  • HEADER = TRUE writes column names as the first row. The default is FALSE, which is how a colleague ends up with a headerless CSV and a confused Slack message.
  • MAX_FILE_SIZE caps each output file in bytes. The default is a rather small 16 MB; the maximum is 5 GB. It’s an upper bound, not a target. Raise it: leaving it at 16 MB is how a modest export becomes four hundred files.
  • SINGLE = TRUE forces exactly one output file. Convenient, and a trap at scale — one file means one writer means no parallelism. Use it for a small, human-consumed extract; never a large one. (Quirk: with SINGLE = TRUE Snowflake ignores FILE_EXTENSION and names the file literally data. If you want orders.csv.gz, put the full name in the path.)
  • OVERWRITE = TRUE overwrites files whose names match, and does not clean out non-matching leftovers. Because multi-file unloads append per-thread suffixes, a re-run with a different thread count can leave the previous run’s files sitting alongside the new ones — and now your consumer reads both, and your export has duplicates. This is the nastiest thing in the unload story. Snowflake’s own recommendation is INCLUDE_QUERY_ID = TRUE, which stamps each file with the query’s UUID so runs can’t collide. Or, honestly: unload to a fresh empty prefix every time and delete the old one. Cheap, obvious, correct.
  • DETAILED_OUTPUT = TRUE returns one row per file written — path, size, row count — instead of one summary row. Turn it on when the consumer needs a manifest.

TYPE for unload accepts CSV, JSON, and PARQUET. Parquet is the right default for anything a machine will read: typed, columnar, compact. CSV is for humans and for the legacy system that only speaks CSV. For JSON, remember that the unload wants an object per row, so the idiom is to construct one:

COPY INTO @my_ext_stage/exports/orders_json/
FROM (SELECT OBJECT_CONSTRUCT(*) FROM orders WHERE region = 'EUROPE')
FILE_FORMAT = (TYPE = JSON COMPRESSION = GZIP);

OBJECT_CONSTRUCT(*) packs each row into a JSON object keyed by column name — the same function you’ll meet properly in semi-structured data, doing the exact inverse of the flattening you’ll do there.

PARTITION BY: the option everybody names and nobody shows

The last chapter mentioned PARTITION BY and moved on. It deserves better, because it’s the difference between “a folder of files” and “a table a lakehouse can actually read.”

PARTITION BY takes an expression that evaluates to a string, and that string becomes a path fragment. Rows are routed into files by that path. Which means you can produce Hive-style partitioned output — the layout Spark, Athena, Trino, and every lakehouse engine on earth expects — straight out of a COPY:

COPY INTO @my_ext_stage/exports/orders/
FROM (
  SELECT o_orderkey, o_custkey, o_orderstatus,
         o_totalprice, o_orderdate, region
  FROM orders
)
PARTITION BY ('region=' || region
              || '/month=' || TO_VARCHAR(o_orderdate, 'YYYY-MM'))
FILE_FORMAT = (TYPE = PARQUET)
MAX_FILE_SIZE = 128000000;

That writes out region=EUROPE/month=1998-03/data_01a2...snappy.parquet, and so on for every region-and-month combination in the result. Point Athena at the prefix and it discovers region and month as partition columns without being told. Point a Spark job at it and predicate pushdown on region works immediately. You did not write a Spark job to produce it; you wrote a SELECT.

Three rules, all learned the hard way:

  • Partition on low-cardinality, well-behaved values — dates, months, regions, statuses. Snowflake’s docs say this outright, and here’s why: the expression becomes a path, so every distinct value becomes a directory and every directory gets at least one file. Partition by o_custkey and you’ll produce hundreds of thousands of directories holding one tiny file each. You have reinvented the small-file problem, on the way out.
  • It’s mutually exclusive with SINGLE = TRUE and OVERWRITE = TRUE — obviously, since you’re asking for many files in many places. INCLUDE_QUERY_ID is forced to TRUE, which is why those filenames carry a UUID.
  • A NULL partition value becomes _NULL_ in the path — perfectly reasonable of Snowflake, and an absolutely baffling directory to find in your bucket if you weren’t expecting it. Coalesce your partition expression.

Getting the files onto your laptop: GET

If you unloaded to an external stage, you’re done — the files are in your bucket and whoever needs them can read them there. If you unloaded to an internal stage, they’re in Snowflake-managed storage, and pulling them down is GET, the mirror image of the PUT from last chapter:

LIST @my_stage/exports/;

GET @my_stage/exports/ file:///Users/me/exports/
  PARALLEL = 10;

Same constraint as PUT: this does not work from a Snowsight worksheet. The browser can’t write to your filesystem, so GET runs from SnowSQL, the Snowflake CLI, or a driver. (We set those up in connecting from outside the browser.)

When unloading is actually the right answer

“Export the data” is a request that sometimes deserves a “why?”. The honest cases:

  • Handing data to a system that can’t read Snowflake. A partner, a regulator, a vendor with a 2009 SFTP endpoint. The majority case, and completely legitimate.
  • A lakehouse handoff. Your ML team runs Spark; your data is in Snowflake. Partitioned Parquet in S3 is a clean seam, and PARTITION BY makes it a one-statement seam. (Iceberg tables are increasingly the better answer — same storage, no export — but that’s a later conversation.)
  • Archival. Cheap, long-term, readable by anything in ten years without a Snowflake account. Parquet in a bucket is a genuinely good archive format.
  • What it is not for: moving data between two Snowflake accounts. That’s secure data sharing, and sharing copies zero bytes. Unloading to S3 so another Snowflake account can COPY it back in is the most expensive possible way to do the thing that is free.

Where the managed EL tools fit

Somebody on your team is going to say “why are we writing pipes at all — can’t Fivetran do this?” It’s a fair question and it deserves a real answer instead of tribal loyalty.

What Fivetran, Airbyte, Stitch, and their peers actually sell you is connectors — specifically, the ongoing maintenance of connectors. The hard part of getting Salesforce data into a warehouse was never the COPY INTO. It was Salesforce’s API: pagination, rate limits, the bulk endpoint, custom fields, schema drift, its idiosyncratic notion of a deleted record — and doing it again next quarter when any of that changes. Multiply by every SaaS tool your company uses. That’s a real, grinding, unglamorous cost, and outsourcing it to a company whose entire business is keeping four hundred connectors alive is frequently the correct call.

What demystifies the whole category is what happens at the Snowflake end: they mostly land files in a stage and run COPY INTO. Some of the modern ones use Snowpipe Streaming. Either way the last mile is exactly the machinery in this chapter — they aren’t doing anything to Snowflake you couldn’t do, and they aren’t doing it faster. The magic, such as it is, lives entirely on the source side.

What you give up, honestly:

  • Cost, and its shape. They bill on rows, or on “monthly active rows” — so your bill scales with something you don’t control. The classic surprise is a 50-million-row table whose every row gets touched nightly, and an invoice that thinks you ingested 50 million rows every night.
  • Control at the seams. Their COPY isn’t your COPY. You don’t pick ON_ERROR, you don’t tune file sizes, you often don’t own the landing schema, and when the load step misbehaves you’re reading their logs and filing their ticket.
  • The dependency. They’re in the critical path of your data arriving, and their outage is your outage.

The line I’d draw: use a managed EL tool for the sources whose APIs you don’t want to own — SaaS apps, marketing platforms, the long tail. Own the ingestion for your own systems — your application database, your event stream, your files. You already have the credentials, the schema, and the on-call rotation for those; paying a vendor per row to move data between two systems you control is paying for a connector you didn’t need.

Choosing, in three questions

Strip away the syntax and the whole chapter is one decision, and it has three inputs.

1. What latency will the business actually act on? Not “how fresh could it be” — how fresh does it need to be before a human or a system behaves differently. Read overnight, decided in the morning → batch COPY, on a schedule you own. Read through the day, minutes are fine → Snowpipe. Acted on in-session, in seconds → Snowpipe Streaming. Be ruthless here. The number of “real-time” dashboards that get opened once a week is the great unwritten tragedy of data engineering.

2. How does the data arrive? As files someone else drops in a bucket (a partner export, an app’s batch output) → the file already exists, and Snowpipe meets it where it is. As events, continuously, from a system you own (Kafka, a service emitting records) → constructing files is work you’re inventing, and streaming skips it. As a big periodic dump → batch COPY gives you the strongest guarantees — ABORT_STATEMENT, VALIDATION_MODE, all-or-nothing across a file set — and there’s no reason to hand those back.

3. What does it cost, on the meter it actually lands on? Three meters, not interchangeable: batch COPY shows up in warehouse credits; Snowpipe in PIPE_USAGE_HISTORY (per-GB, serverless, invisible in your warehouse spend); streaming on its own throughput meter. If you can’t say which of the three a pipeline lands on, you can’t manage its cost — and a very common shape of “a Snowflake bill nobody understands” is a pipe nobody was watching because everyone was staring at warehouses.

A last word on the middle rung, where most of you will live. Snowpipe plus deliberate file sizing is the default, and it’s a good one. Land 100–250 MB compressed files, or one file a minute if you can’t fill that; wire the notification once; put pendingFileCount and VALIDATE_PIPE_LOAD on a dashboard; let the pipe run for years. Then hand the landed rows to a Stream and a Task — the change-capture-and-schedule pair from programmability — and raw arrivals become modeled tables without you touching a scheduler there either. Ingestion that never sleeps, feeding transformation that never sleeps: four objects, no cron.

Final thoughts

The deep joke of this chapter is that there’s only one verb. COPY INTO <table> loads files when you say so. A pipe is that same COPY INTO, triggered by a cloud event instead of by you. Snowpipe Streaming is that same COPY INTO, fed by a channel of rows instead of a stage of files. And COPY INTO <location> is that same COPY INTO with the arrow reversed. Learn the verb once and continuous ingestion turns out to be a wiring exercise, not a new mental model.

What is new is the operational surface, and that’s where the money and the outages live. A pipe is infrastructure: it needs a status check (SYSTEM$PIPE_STATUS), an error monitor (VALIDATE_PIPE_LOAD, plus an ERROR_INTEGRATION so failures come to you), a way to stop it (PIPE_EXECUTION_PAUSED), and a way to catch it up (REFRESH — within seven days, as a repair, not a routine). It has its own 14-day load history that a careless CREATE OR REPLACE will throw away under you. And it bills on a meter your warehouse dashboards don’t show — a per-GB meter, as of December 2025, which is cheaper than the per-file model everyone still quotes, and which does nothing at all to fix the real reason small files hurt: they don’t buy you lower latency, they build a backlog, and they leave you a badly-laid-out table forever.

So: size your files. Watch the pending count. Alert on the rejects. And when someone asks for real-time, ask them what they’ll do differently at 9:04 that they wouldn’t have done at 10:00 — then be genuinely delighted on the days they have a good answer.

Examples in this chapter are docs-checked against the series’ stated versions, but they were not executed in this repository unless a companion project explicitly says so.

Next: Roles, Grants, and Not Everyone an ACCOUNTADMIN

Comments