How to load data into Snowflake: COPY INTO, Snowpipe, CSV files, S3 stages and bulk loading

How do I load data into Snowflake?

Snowflake has four native loading paths and one managed layer on top of them. Use COPY INTO for scheduled bulk loads from a stage, Snowpipe for files that arrive continuously, Snowpipe Streaming for rows written directly with no file step, and Openflow or a third-party connector when the source is a live database or SaaS application you do not want to write extraction code against. Everything else is a wrapper around one of those.

Which one you want depends almost entirely on where the data is coming from and how fresh it needs to be:

Six ways to load data into Snowflake, by source, best use and the main caveat

Method	Source	Best for	What to know
Snowsight load data UI	A file on your laptop	One-off loads and a first look at a new file	No SQL required, but it is a manual action and it does not become a pipeline
PUT then COPY INTO	Local files staged internally	Scripted loads from a machine you control	PUT uploads to a stage, COPY INTO loads from it. PUT is not available in the web UI worksheet
COPY INTO from an external stage	S3, Google Cloud Storage, Azure	Scheduled bulk loads and historical backfills	The workhorse. Runs on your virtual warehouse, so you control the compute it uses
Snowpipe	Files arriving in a stage continuously	Files landing throughout the day	Typically loads within a minute of the file notification. Serverless, billed per second
Snowpipe Streaming	Rows written directly from an application	Event streams with no file step at all	Skips staged files entirely, which is why it reaches lower latency and lower cost per row
Openflow or a managed connector	A database or SaaS application	Replicating a live system on a schedule	You configure a source instead of writing load code. Openflow is Snowflake first-party, built on Apache NiFi

Worth correcting a claim that still appears in a lot of published comparisons: Snowflake does now ship its own ingestion product. Openflow, built on Apache NiFi, reached general availability for Snowflake Deployments running on Snowpark Container Services on 4 November 2025, and its Oracle connector went generally available on 27 February 2026. If an article tells you Snowflake has no native ETL tooling, check its date. The full landscape, first-party and third-party, is compared on our Snowflake ETL tools page.

How to load data into Snowflake from a CSV file

For a single file you are exploring, the load data wizard in Snowsight is the fastest route: pick the table, pick the file, check the inferred file format, load. For anything you will repeat, script it instead. The scripted version is two commands. PUT uploads the local file to a stage, and COPY INTO loads from the stage into the table.

PUT file:///Users/you/data/orders.csv @%orders AUTO_COMPRESS=TRUE;

COPY INTO orders
  FROM @%orders
  FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1)
  ON_ERROR = ABORT_STATEMENT;

Two things about that snippet matter more than they look. PUT runs from a client such as SnowSQL or a driver, not from a Snowsight worksheet, which is the first thing that trips people up. And FIELD_OPTIONALLY_ENCLOSED_BY is what lets a quoted field contain your delimiter, which is why an address column with a comma in it is the classic first failure of a CSV load.

COPY INTO is more capable than a straight file-to-table copy. It supports column reordering, column omission, casts, and truncating text strings that exceed the target column length, so you can do a meaningful amount of shaping during the load rather than in a follow-up statement.

How to load data into a Snowflake stage

A stage is just a location Snowflake can read files from. There are two families. Internal stages live inside Snowflake and come in three flavors: the user stage, referenced as @~ and private to you; the table stage, referenced as @%table_name and tied to one table; and a named internal stage you create explicitly and can share across tables and users. External stages point at your own Amazon S3, Google Cloud Storage or Azure container.

Use a named stage for anything a team depends on. User and table stages are convenient for quick work and awkward to reason about later, because permissions and lifecycle are implicit. One constraint to plan around: stages cannot read data held in archival cloud storage classes, so files that have aged into a deep archive tier have to be restored before a load can see them.

How to load data into Snowflake from S3

Create a storage integration so Snowflake authenticates to your bucket with a role rather than with keys pasted into SQL, then create an external stage on top of it and COPY INTO from that stage. The integration is the part worth doing properly, because it is the difference between rotating a credential and rotating a credential that is written into forty scripts.

CREATE STAGE orders_stage
  STORAGE_INTEGRATION = s3_int
  URL = 's3://my-bucket/orders/'
  FILE_FORMAT = (TYPE = PARQUET);

COPY INTO orders FROM @orders_stage
  MATCH_BY_COLUMN_NAME = CASE_INSENSITIVE;

Partition your bucket by date and load from the explicit path rather than the bucket root. Snowflake has to list files before it loads them, and listing a prefix with two objects is cheap while listing one with two million is not. This is the single most effective change on a slow S3 load after file sizing.

How to load data into Snowflake using Python

The Snowflake Connector for Python is the standard client. For a pandas DataFrame, the write_pandas helper does the whole staging dance for you: it chunks the frame into Parquet files, PUTs them to a temporary stage, and issues the COPY INTO. That is almost always better than looping over rows with INSERT, which is slow enough to be a bug at any real volume.

from snowflake.connector import connect
from snowflake.connector.pandas_tools import write_pandas

conn = connect(user=..., account=..., warehouse=..., database=..., schema=...)
success, nchunks, nrows, _ = write_pandas(conn, df, "ORDERS")

Note the uppercase table name. Snowflake folds unquoted identifiers to uppercase, so a table created as orders is stored as ORDERS, and a Python string of "orders" is a quoted lowercase identifier that will not match it. If you prefer working in Snowpark, its write API on a DataFrame covers the same ground with the transformation happening in Snowflake rather than on your machine.

How to load bulk data in Snowflake

Bulk loading is where file preparation stops being a detail. Snowflake recommends producing data files of roughly 100 to 250 MB compressed, and explicitly advises against loading very large files of 100 GB or more. The reason is mechanical: Snowflake parallelizes ingestion across the files you give it, so one enormous file cannot be split across your warehouse threads, and ten thousand tiny files pay per-file overhead ten thousand times.

If your files are too large, split them by line so no record spans a boundary. On Linux or macOS that is one command:

split -l 100000 pagecounts-20151201.csv pages

The numbers worth knowing before a first bulk load, all from Snowflake's documentation as of 13 August 2026:

Snowflake data loading limits and recommended values, with the reason each one matters

Limit or recommendation	Value	Why it matters
Recommended data file size, compressed	100 to 250 MB or larger	Load parallelism is bounded by how many files you provide, and every file carries overhead
File size Snowflake advises against	100 GB or larger	Explicitly not recommended. Split it before loading rather than after the job times out
Default COPY job timeout	24 hours	A single COPY loading millions of files can hit it. Split very large jobs into smaller ones
Maximum VARIANT, VARCHAR or ARRAY value	128 MB	A single wide JSON document can exceed this and fail the row
VARCHAR default size	16 MB	Declare it larger explicitly if you need more, up to the 128 MB ceiling
Maximum BINARY value	64 MB	Half the VARIANT ceiling, which surprises people storing encoded blobs
Elements extracted from semi-structured data	200 per partition, per table	Snowflake auto-extracts up to this many elements. Beyond it, flatten the fields you query

One more bulk-loading note that is easy to miss: if your workload is many concurrent COPY statements loading into the same table, Snowflake's own guidance is to use Snowpipe instead, because the service is built to handle that concurrency and manages table metadata for parallel operations better than competing COPY jobs do.

How to load incremental data in Snowflake

The first load is a one-time cost. Everything after it should move only what changed, and there are three honest options. A watermark query pulls rows where an updated-at column is newer than the last run, which is simple and misses hard deletes plus anything updated by a process that does not touch the timestamp. Change data capture reads the database transaction log, catches both, and costs you database configuration. Full reload each time is fine for small dimension tables and nothing else.

Whichever you choose, land the new rows in a staging table and MERGE into the target on a stable key rather than appending. A load you can safely run twice is a load you can rerun at 2am without arithmetic. Inside Snowflake, streams and tasks give you the same idea natively: a stream tracks what changed in a table since you last read it, and a task runs the merge on a schedule. For the source-side half of this decision, our comparison of change data capture tools covers log-based capture against polling, and Postgres change data capture goes through logical decoding in detail.

How to load Excel data into Snowflake

Snowflake does not read .xlsx files natively. Export the sheet to CSV and load that, which is fine for a one-off and a bad habit for anything recurring, because spreadsheets carry formatting that quietly changes values. Watch for three specific things: dates that Excel has rendered as serial numbers, numbers stored as text with thousands separators, and leading zeros stripped from account or ZIP codes. All three load without error and are wrong.

If a spreadsheet is a recurring source, the honest fix is to stop treating it as one. Move the upstream export to a database, an API, or a system with a schema. When finance data is arriving as a monthly workbook, the underlying problem is usually that the accounting system was never connected to anything.

How to load API data into Snowflake

Land the JSON first and shape it second. Write the raw API response into a VARIANT column, then build views or models that flatten the fields you actually query. That way a change in the API shape does not break ingestion, and you keep the original payload for when someone asks why a number moved.

Two limits apply here. A single VARIANT value caps at 128 MB, and Snowflake automatically extracts a maximum of 200 elements per partition, per table from semi-structured data, so very wide documents need explicit flattening of the paths you care about rather than relying on auto-extraction.

Writing and maintaining the API client itself is the part teams underestimate. Pagination, token refresh, rate limits, retry with backoff, and incremental cursors are each simple and collectively a permanent job. That is what a managed connector buys you, and the build versus buy math is usually decided by the second and third source rather than the first.

Common Snowflake load errors and what causes them

Most first-load failures come from a short list. Snowflake's DML error logging helps here: it captures supported errors such as NOT NULL violations, type conversion failures and precision or scale incompatibilities into an error table rather than failing the whole operation, so you can inspect what went wrong instead of guessing.

Common Snowflake data loading errors, their causes and fixes

Symptom	Usual cause	Fix
Numeric value is not recognized	Thousands separators, currency symbols or an empty string where a number is expected	Set FIELD_OPTIONALLY_ENCLOSED_BY and NULL_IF in the file format, or cast in the COPY transformation
Timestamp values shift by hours	The source had an offset and the target column is TIMESTAMP_NTZ, which stores no zone	Use TIMESTAMP_TZ when the offset matters, or normalize to UTC before the load and document it
Decimals silently lose their fraction	NUMBER defaults to precision and scale (38,0), so 42.50 lands as 42 or fails	Declare NUMBER(19,4) or similar on money columns before the first load, not after
Column not found, but it looks right	Unquoted identifiers fold to uppercase, so created_at became CREATED_AT and "createdAt" did not	Pick one convention. Either quote identifiers everywhere or let everything fold and use uppercase
String is too long and would be truncated	A source value exceeds the declared VARCHAR length	Widen the column, or truncate deliberately in the COPY transformation so you choose what is lost
The load is slow and the warehouse is idle	Thousands of tiny files, so there is nothing to parallelize	Combine into files of 100 to 250 MB compressed. This usually beats resizing the warehouse

Use ON_ERROR deliberately. ABORT_STATEMENT is the right default for a load feeding financial reporting, because a partial load is worse than no load. CONTINUE is right when you are ingesting messy third-party data and would rather capture 99 percent and review the rest. Choosing it by accident is how a table ends up quietly missing rows.

What to do after the data lands

Two jobs start the moment the first load succeeds. The first is monitoring, and the failure mode to design against is not the loud one. It is the pipeline that stopped three weeks ago while a dashboard kept rendering the last numbers it saw. Check freshness and row counts against the source, and route the alert to someone who is actually on duty.

The second is making the data usable by people who do not write SQL. Once the tables are clean and modeled, the bottleneck moves from loading to access, and the analysts queueing behind one data engineer are the real cost. Letting the finance or ops team ask questions of it in plain English removes more of that queue than another dashboard does.

If the source you are loading is a production database or a business application rather than a folder of files, the whole path above collapses into configuration. Adapters maps source columns onto Snowflake columns visually, runs the sync on your schedule with retries and per-record error logs, and bills a flat monthly price rather than a row meter. The lanes people ask about most are Postgres to Snowflake, MySQL to Snowflake, SQL Server to Snowflake and Salesforce to Snowflake. To compare the platforms that do this work, including Snowflake's own, start with Snowflake ETL tools.