Welcome To TechBrothersIT: Snowflake SQL Tutorial 130 - COPY FILES Won't Touch Your User Stage, And Doesn't Say Why

Snowflake SQL Tutorial 130 - COPY FILES Won't Touch Your User Stage, And Doesn't Say Why

Snowflake SQL · Episode 130

In this post we are going to cover COPY FILES in Snowflake.

COPY FILES copies files from one stage to another without them leaving Snowflake. Before it existed, moving a file between two stages meant a GET down to your machine and a PUT back up again. Two network trips, for a file that never needed to go anywhere.

We will start with two stages and two files and build it up step by step. Along the way we will find out what it reports back, whether it is a copy or a move, what it does when nothing matches, and which kinds of stage it flatly refuses to work with.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 130 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's copy the files across
Is it a copy, or a move?
Now let's read the copied file back
Now have a look at the size and the md5
Now let's narrow it down with FILES and PATTERN
What happens when nothing matches?
Now let's run the same copy twice
Can we copy into our own user stage?
And a table stage?
What else does it refuse?
One last thing: the teardown, and a mistake in it

Let's create some sample data

We need two stages. One with files on it, and one with nothing on it. We also need a table, because that is where the files are going to come from.

This post writes no files from a laptop at all. The files we copy are unloaded out of a table inside the same script, so the names are ours and you can run every line of this on your own account.

Sample data

-- Sample data for this post.
-- Two stages, a CSV format, and a small table to make files out of.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE STAGE SOURCE_T130;
CREATE OR REPLACE STAGE TARGET_T130;

CREATE OR REPLACE FILE FORMAT CSV_T130
    TYPE = CSV
    FIELD_OPTIONALLY_ENCLOSED_BY = '"'
    COMPRESSION = NONE;

CREATE OR REPLACE TABLE SALES_T130 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

INSERT INTO SALES_T130 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'north', 250.50),
       (3, 'south',  75.25), (4, 'south', 310.00),
       (5, 'east',   45.00), (6, 'east',   88.25);

Now let's unload that table twice, into two files on the source stage. SINGLE = TRUE gives us one file with the name we asked for instead of a set of split files.

The whole table

COPY INTO @SOURCE_T130/in/sales_all.csv FROM SALES_T130
FILE_FORMAT = (FORMAT_NAME = 'CSV_T130')
SINGLE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
6	97	97

Just the north rows

COPY INTO @SOURCE_T130/in/sales_north.csv FROM (
    SELECT SALE_ID, REGION, AMOUNT FROM SALES_T130 WHERE REGION = 'north'
)
FILE_FORMAT = (FORMAT_NAME = 'CSV_T130')
SINGLE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
2	34	34

The first file took all six rows and the second took the two north rows. Snowflake tells us how many rows it unloaded and how many bytes it wrote.

Now let's look at the stage itself.

The source stage

LIST @SOURCE_T130/in/;

Result2 rows · 4 columns

name	size	md5	last_modified
source_t130/in/sales_all.csv	112	bdf2b77a3e760d32aaeed61f5d744a2a	Thu, 1 Oct 2026 18:14:21 GMT
source_t130/in/sales_north.csv	48	c44a11a01c57e1cb615ec820d2d3a55e	Thu, 1 Oct 2026 18:14:22 GMT

Two files, with the names we gave them. Hold on to the size and md5 columns, because we are going to come back to both of them.

The target stage

LIST @TARGET_T130/;

Result0 rows · 4 columns

name	size	md5	last_modified

Nothing. No rows at all. That is where we want the files to end up.

Let's copy the files across

The statement is short. COPY FILES INTO, the destination, FROM, the source. That is the whole thing.

Query

COPY FILES INTO @TARGET_T130/out/ FROM @SOURCE_T130/in/;

Result2 rows · 1 column

file
out/sales_all.csv
out/sales_north.csv

One column, called file, and a row for each file it handled.

Look at what is in that column. It says out/sales_all.csv, not in/sales_all.csv. It is reporting the destination path, not the source path.

Now let's list the target stage and see whether the files are really there.

The target stage now

LIST @TARGET_T130/out/;

Result2 rows · 4 columns

name	size	md5	last_modified
target_t130/out/sales_all.csv	112	585d98016c47b09e5470900952a70aba	Thu, 1 Oct 2026 18:14:23 GMT
target_t130/out/sales_north.csv	48	3b9d3db7a9ed9937b419f8cbf2402d57	Thu, 1 Oct 2026 18:14:23 GMT

Both files arrived, with the same names and the same sizes the source stage reported.

A stage listing is a bit awkward to work with, because its column names are lowercase and quoted. We can always pull it into a normal grid with RESULT_SCAN.

The same listing, as a query

SELECT "name" AS THE_FILE, "size" AS SIZE_IN_BYTES
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY THE_FILE;

Result2 rows · 2 columns

THE_FILE	SIZE_IN_BYTES
target_t130/out/sales_all.csv	112
target_t130/out/sales_north.csv	48

Is it a copy, or a move?

This is the question to settle before you point the statement at anything you care about. Let's list the source stage again.

The source stage, after the copy

LIST @SOURCE_T130/in/;

Result2 rows · 4 columns

name	size	md5	last_modified
source_t130/in/sales_all.csv	112	bdf2b77a3e760d32aaeed61f5d744a2a	Thu, 1 Oct 2026 18:14:21 GMT
source_t130/in/sales_north.csv	48	c44a11a01c57e1cb615ec820d2d3a55e	Thu, 1 Oct 2026 18:14:22 GMT

Both files are still there, with the same sizes and the same hashes they had before. Nothing was taken away.

So it is a copy. The word is in the name, and the name is honest.

Now let's read the copied file back

The files arrived and the listing says the sizes match. That is not the same as the data being intact. Let's read the copy on the target stage and see what comes out of it.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @TARGET_T130/out/ (FILE_FORMAT => 'CSV_T130', PATTERN => '.*sales_all.*')
ORDER BY $1;

Result6 rows · 3 columns

SALE_ID	REGION	AMOUNT
1	north	100.00
2	north	250.50
3	south	75.25
4	south	310.00
5	east	45.00
6	east	88.25

Six rows, with the sale ids from one to six and three regions. That is what we unloaded.

Let's count it against the table it came from rather than trust our own eyes.

Query

SELECT (SELECT COUNT(*) FROM SALES_T130)    AS ROWS_IN_THE_TABLE,
       (SELECT SUM(AMOUNT) FROM SALES_T130) AS TOTAL_IN_THE_TABLE,
       (SELECT COUNT(*) FROM @TARGET_T130/out/
            (FILE_FORMAT => 'CSV_T130', PATTERN => '.*sales_all.*'))
                                            AS ROWS_IN_THE_COPIED_FILE;

Result1 row · 3 columns

ROWS_IN_THE_TABLE	TOTAL_IN_THE_TABLE	ROWS_IN_THE_COPIED_FILE
6	869.00	6

The table has six rows and they add up to 869.00, and the copied file on the target stage reads back as six rows. The data survived the trip.

Now have a look at the size and the md5

This is the part we noticed by accident while checking the sizes, and it is worth a section of its own.

Start with the size. The unload at the top of this post said it wrote 97 output bytes for sales_all.csv and 34 for sales_north.csv. The stage listing says 112 and 48.

Those are not the same numbers, and neither grid is wrong. The unload is telling us how many bytes it wrote. The listing's size column is telling us how much the stage is holding. Episode 124 looked at this column on its own and found the same thing about uploaded files.

Now the hash. Here are the two listings again, source first.

Result2 rows · 4 columns

The source stage

name	size	md5	last_modified
source_t130/in/sales_all.csv	112	bdf2b77a3e760d32aaeed61f5d744a2a	Thu, 1 Oct 2026 18:14:21 GMT
source_t130/in/sales_north.csv	48	c44a11a01c57e1cb615ec820d2d3a55e	Thu, 1 Oct 2026 18:14:22 GMT

Result2 rows · 4 columns

The copy on the target stage

name	size	md5	last_modified
target_t130/out/sales_all.csv	112	585d98016c47b09e5470900952a70aba	Thu, 1 Oct 2026 18:14:23 GMT
target_t130/out/sales_north.csv	48	3b9d3db7a9ed9937b419f8cbf2402d57	Thu, 1 Oct 2026 18:14:23 GMT

Same names. Same size column. Different md5, on both files.

And we already know the content is the same, because we read the copy back a moment ago and counted it against the table the source file was unloaded from.

What that tells us

Whatever the md5 column on a stage listing is, it is not a hash of your file's contents. If it were, two files with identical contents could not have different values in it.
Neither of those two columns describes your file. They describe what the stage is holding.

Now let's narrow it down with FILES and PATTERN

COPY FILES takes the same two narrowing options as COPY INTO. We can name the files we want, or we can describe them with a regular expression.

Let's name one.

FILES

COPY FILES INTO @TARGET_T130/one/ FROM @SOURCE_T130/in/
FILES = ('sales_north.csv');

Result1 row · 1 column

file
one/sales_north.csv

One row this time, and again it is the destination path.

The new prefix

LIST @TARGET_T130/one/;

Result1 row · 4 columns

name	size	md5	last_modified
target_t130/one/sales_north.csv	48	2a852ad187433ec9fd27a0669e882e11	Thu, 1 Oct 2026 18:14:26 GMT

One file, where we asked for one file.

Now let's ask the same question the other way, with a pattern.

PATTERN

COPY FILES INTO @TARGET_T130/pat/ FROM @SOURCE_T130/in/
PATTERN = '.*north.*';

Result1 row · 1 column

file
pat/sales_north.csv

The new prefix

LIST @TARGET_T130/pat/;

Result1 row · 4 columns

name	size	md5	last_modified
target_t130/pat/sales_north.csv	48	2b0e37618aadf699591d448d92756435	Thu, 1 Oct 2026 18:14:27 GMT

The same file again, reached a different way.

While we are here, look at the md5 in those last two grids and compare it with the copy we made earlier. The same file, copied to three different prefixes, has a different hash in all three listings.

What happens when nothing matches?

Let's give it a pattern that cannot match anything on that stage.

Query

COPY FILES INTO @TARGET_T130/none/ FROM @SOURCE_T130/in/
PATTERN = '.*nothing_here.*';

Result0 rows · 1 column

file

Zero rows. And no error.

Did it at least leave an empty prefix behind? Let's look.

The prefix we aimed at

LIST @TARGET_T130/none/;

Result0 rows · 4 columns

name	size	md5	last_modified

Nothing there either. The statement did nothing at all, and reported that by handing us an empty result.

Now let's try the other way of asking for something that is not there. A file name, in the FILES list, that is not on the source stage.

Query

COPY FILES INTO @TARGET_T130/missing/ FROM @SOURCE_T130/in/
FILES = ('not_a_file.csv');

Result0 rows · 1 column

file

Zero rows again. No error, no warning, no complaint about the name we got wrong.

Zero rows is not an error

This is the silence to watch for. A typo in a FILES list and a PATTERN that matches nothing both look exactly like a successful copy of no files. If your script checks whether the statement failed, it will not notice either one. Count the rows it returns.

That is where COPY INTO behaves differently. Episode 126 asked a loading statement for a file name that was not on the stage, and got a refusal back — 091016, remote file not found.

Episode 126's statement, for comparison

-- From Episode 126. This statement was NOT run on this page.
COPY INTO TARGET_T126
FROM @STAGE_T126/in/
FILES = ('t126_does_not_exist.csv')
FILE_FORMAT = (FORMAT_NAME = 'CSV_T126')
FORCE = TRUE;

There is no capture of that refusal on this page, because this episode never ran it. The grids above are what COPY FILES did here, and the point of putting the other statement beside them is the contrast. Same question, two statements, two completely different answers. One tells you. One does not.

Now let's run the same copy twice

The files are already sitting in out/. Let's run the first copy again, unchanged, and see what it makes of that.

Query

COPY FILES INTO @TARGET_T130/out/ FROM @SOURCE_T130/in/;

Result2 rows · 1 column

file
out/sales_all.csv
out/sales_north.csv

It works. The same two destination paths come back, and there is no complaint about the files already being there.

The destination now

LIST @TARGET_T130/out/;

Result2 rows · 4 columns

name	size	md5	last_modified
target_t130/out/sales_all.csv	112	981ddee93a71c006fb56ba643d72fd1c	Thu, 1 Oct 2026 18:14:31 GMT
target_t130/out/sales_north.csv	48	771c3014cbd1f39780c8c18fd4d7ce5a	Thu, 1 Oct 2026 18:14:31 GMT

Counted

SELECT COUNT(*) AS FILES_IN_THE_DESTINATION
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

FILES_IN_THE_DESTINATION
2

Still the same two files, not four. And the md5 has changed again on both of them.

It overwrites, quietly

COPY FILES overwrote both files, and nothing in the result it handed back says so. The second run looks exactly like the first one. Know that before you aim it at a prefix whose contents you want to keep.

Can we copy into our own user stage?

Episode 122 showed COPY INTO writing happily to all three kinds of internal stage: a named stage, the user stage at @~, and a table stage at @%TABLE. So this ought to be fine.

Query

COPY FILES INTO @~/t130/ FROM @SOURCE_T130/in/;

Query failed — Snowflake returned an error

508003 (42601): SQL compilation error: Copy files not support stage kind: 'USER' for stage '~'.

Refused. And read the message, because it is a good one. It says Copy files not support stage kind: 'USER' for stage '~'. It names the thing it will not do and the stage it will not do it to.

So this is not a permission problem and it is not a syntax problem. It is a kind problem, and the error says which kind.

And the user stage

LIST @~/t130/;

Result0 rows · 4 columns

name	size	md5	last_modified

Empty, as you would expect from a statement that refused to run.

Two different kinds of no

It is worth keeping this and the last section apart. This refusal explains itself. The one that says nothing is the zero-row case: a FILES name or a PATTERN that matches nothing, which comes back empty with no message of any kind.

And a table stage?

Let's try the third kind of internal stage, the one that belongs to a table.

Into a table stage

COPY FILES INTO @%SALES_T130/ FROM @SOURCE_T130/in/;

Query failed — Snowflake returned an error

508003 (42601): SQL compilation error: Copy files not support stage kind: 'TABLE' for stage 'SALES_T130'.

The same code, and the same shape of message, with 'TABLE' where it said 'USER' before.

Maybe it is only the destination that has to be a named stage. Let's copy out of a table stage into our named one and find out.

Out of a table stage

COPY FILES INTO @TARGET_T130/fromtable/ FROM @%SALES_T130/;

Query failed — Snowflake returned an error

508003 (42601): SQL compilation error: Copy files not support stage kind: 'TABLE' for stage 'SALES_T130'.

The same error again. So it is both directions, not just the destination.

The table stage

LIST @%SALES_T130/;

Result0 rows · 4 columns

name	size	md5	last_modified

Still empty. Nothing happened in either attempt.

Named stages only. That is the limitation to carry away from this episode, and it is the one that surprises people who have already used COPY INTO on all three kinds.

What else does it refuse?

Let's try to copy a stage onto itself, with the same prefix on both sides.

Query

COPY FILES INTO @SOURCE_T130/in/ FROM @SOURCE_T130/in/;

Query failed — Snowflake returned an error

508001 (42601): SQL compilation error: Copy files into same stage with same prefix.

Copy files into same stage with same prefix. That is a sensible thing to refuse, and it refuses it clearly.

And a stage name that does not exist, on each side in turn.

A source that is not there

COPY FILES INTO @TARGET_T130/nope/ FROM @SOURCE_T130_NOPE/in/;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Stage 'DEMO_DB.PUBLIC.SOURCE_T130_NOPE' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on STAGE DEMO_DB.PUBLIC.SOURCE_T130_NOPE.

A destination that is not there

COPY FILES INTO @TARGET_T130_NOPE/nope/ FROM @SOURCE_T130/in/;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Stage 'DEMO_DB.PUBLIC.TARGET_T130_NOPE' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on STAGE DEMO_DB.PUBLIC.TARGET_T130_NOPE.

Both refused at compile time, with the usual Snowflake wording about an object that does not exist or is not authorized.

So the names are checked. It is only the missing files that pass without a word.

One last thing: the teardown, and a mistake in it

Let's empty both stages.

The source stage

REMOVE @SOURCE_T130/;

Result2 rows · 2 columns

name	result
source_t130/in/sales_north.csv	removed
source_t130/in/sales_all.csv	removed

The target stage

REMOVE @TARGET_T130/;

Result4 rows · 2 columns

name	result
target_t130/out/sales_all.csv	removed
target_t130/one/sales_north.csv	removed
target_t130/out/sales_north.csv	removed
target_t130/pat/sales_north.csv	removed

Two files came off the source stage and four came off the target. Those four are the two we copied into out/, the one in one/ and the one in pat/. The prefixes we aimed at and missed left nothing behind to remove.

Then we wanted to prove both stages were empty in a single query, so we asked a directory table for a count.

Query

SELECT (SELECT COUNT(*) FROM DIRECTORY(@SOURCE_T130)) AS FILES_ON_THE_SOURCE,
       (SELECT COUNT(*) FROM DIRECTORY(@TARGET_T130)) AS FILES_ON_THE_TARGET;

Query failed — Snowflake returned an error

393901 (55000): DIRECTORY not enabled for the stage SOURCE_T130

DIRECTORY not enabled for the stage SOURCE_T130. This one is our own fault, and it stays in the post for the same reason it stayed in the video.

A directory table is not something a stage has by default. You ask for it when you create the stage, with DIRECTORY = (ENABLE = TRUE). Look at the CREATE OR REPLACE STAGE lines at the top of this post: there is no such clause on either of them.

Episode 122 put this exact error on screen as one to recognise. We created these two stages without the clause a few episodes later and walked straight into it. LIST would have answered the question without complaint. DIRECTORY has to be asked for.

Then everything else goes.

Teardown

DROP STAGE IF EXISTS SOURCE_T130;

Result1 row · 1 column

status
SOURCE_T130 successfully dropped.

Teardown

DROP STAGE IF EXISTS TARGET_T130;

Result1 row · 1 column

status
TARGET_T130 successfully dropped.

Teardown

DROP FILE FORMAT IF EXISTS CSV_T130;

Result1 row · 1 column

status
CSV_T130 successfully dropped.

Teardown

DROP TABLE IF EXISTS SALES_T130;

Result1 row · 1 column

status
SALES_T130 successfully dropped.

And a check that we left nothing behind.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T130') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE STAGE_NAME LIKE '%_T130%')      AS PROBE_STAGES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILE_FORMATS
        WHERE RIGHT(FILE_FORMAT_NAME, 5) = '_T130') AS PROBE_FORMATS_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 6 columns · scroll →

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	PROBE_STAGES_LEFT	PROBE_FORMATS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	0	10

No probe tables, no probe stages, no probe file formats, and the demo database still reads. Back where we started.

And the user stage

LIST @~/t130/;

Result0 rows · 4 columns

name	size	md5	last_modified

Key points

COPY FILES copies between stages inside Snowflake, with no round trip through your machine.
It returns one column, file, and it holds the destination path.
It is a copy, not a move. The source stage still has everything after it runs.
The data survives: the copied file read back as six rows against a table of six rows totalling 869.00.
The md5 on a stage listing is not a hash of your file. The same content copied to different prefixes gets a different value every time.
The size column is not your file's byte count either. The unload said 97 for a file the listing calls 112.
Run it twice and it overwrites, with no complaint and nothing in the result to say so.
FILES and PATTERN narrow it the same way they narrow COPY INTO.
A PATTERN that matches nothing, or a FILES name that is not on the stage, gives you zero rows and no error. That is the silence in this episode.
COPY FILES works on named stages only. A user stage or a table stage is refused with 508003, and the message names the stage kind, so that refusal does explain itself.
The refusal is the same in both directions, into a table stage and out of one.
A stage onto itself with the same prefix is refused with 508001.
A directory table is not automatic. Without DIRECTORY = (ENABLE = TRUE) on the stage you get 393901, and LIST is the way to ask instead.

Next in this series

Episode 131 — Snowpipe. A pipe is a COPY INTO statement that Snowflake holds for you. We will create one, put a file where it is watching, find out what REFRESH actually reports, and end up with a landing table holding every row of that file twice.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. This episode writes no local files: both files it copies were unloaded from a table inside the same script. One statement on this page was not run here — the COPY INTO from episode 126, shown as plain code for comparison, with no result grid because this episode has no capture of it. The byte counts are Snowflake's own columns and nothing here is timed. Behaviour follows the Snowflake COPY FILES documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 130 - COPY FILES Won't Touch Your User Stage, And Doesn't Say Why

Let's create some sample data

Let's copy the files across

Is it a copy, or a move?

Now let's read the copied file back

Now have a look at the size and the md5

Now let's narrow it down with FILES and PATTERN

What happens when nothing matches?

Now let's run the same copy twice

Can we copy into our own user stage?

And a table stage?

What else does it refuse?

One last thing: the teardown, and a mistake in it

Key points

No comments:

Post a Comment