Welcome To TechBrothersIT: Snowflake SQL Tutorial 122 - Two Of Your Stages Already Exist And You Never Created Either

Snowflake SQL Tutorial 122 - Two Of Your Stages Already Exist And You Never Created Either

Snowflake SQL · Episode 122

In this post we are going to cover stages in Snowflake.

A stage is a place that files live in, so that Snowflake can read them or write them. Every load and every unload needs one, which is why this is the first post of the level.

Three different things are called a stage, and you only ever create one of them. We will start by looking inside two stages that we never created, and build it up step by step.

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

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

On this page

Let's create some sample data
Let's look inside a stage we never created
Now let's create the one that does not exist yet
Let's describe it, and this is the part nobody expects
Now let's get a file into all three, without a single PUT
Now let's look at the three paths carefully
And LIST is a command, not a query
Let's read the file back out of the stage
What happens when we try to drop the other two?
And two more kinds of named stage
One last thing: let's take our files back out

Let's create some sample data

We are going to write a file out of a table and then read it back, so we need a small table first. The script below creates SALES_T122 and puts six rows into it.

Sample data

-- Sample data for this post. Six sales, and that is all we need.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

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

INSERT INTO SALES_T122 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00),
       (2, 'north', 250.50),
       (3, 'south',  75.25),
       (4, 'south', 310.00),
       (5, 'east',   90.00),
       (6, 'west',  420.00);

Let's look at what we just created.

Query

SELECT SALE_ID, REGION, AMOUNT FROM SALES_T122 ORDER BY SALE_ID;

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	90.00
6	west	420.00

Six rows and three columns. A sale ID, a region and an amount. That is the whole setup.

Let's look inside a stage we never created

LIST shows you the files in a stage. Let's point it at @~, which is the name of your own user stage.

We have not created anything yet. Not a stage, not a file, nothing.

Query

LIST @~/t122/;

Result0 rows · 4 columns

name	size	md5	last_modified

Zero rows. Now look at what did not happen. There is no error. Snowflake did not say the stage does not exist. It said the stage is empty.

@~ was already there. There is one per user, it belongs to you, and we did nothing at all to bring it into being.

Now the second one. A table has a stage of its own, and its name is @% followed by the table name.

Query

LIST @%SALES_T122;

Result0 rows · 4 columns

name	size	md5	last_modified

Zero rows again, and again no error. That one arrived with the table. We created the table in the section above and we got a stage with it whether we wanted one or not.

Now let's ask for the third kind, the named stages in this schema.

Query

SHOW STAGES IN SCHEMA PUBLIC;

Result0 rows · 17 columns · scroll →

created_on	name	database_name	schema_name	url	has_credentials	has_encryption_key	owner	comment	region	type	cloud	notification_channel	storage_integration	endpoint	owner_role_type	directory_enabled

Zero rows here too, but this time the zero means something different. There really are none, because a named stage is the only one of the three you have to make yourself.

The three of them

@~ — your user stage. One per user, always there.
@%TABLENAME — the table's stage. One per table, always there.
@MY_STAGE — a named stage. The only one you create or configure.

Now let's create the one that does not exist yet

A named internal stage needs nothing but a name. Let's give it a comment as well, so we have something to change later.

Query

CREATE OR REPLACE STAGE STAGE_T122
    COMMENT = 'episode 122 probe stage';

Result1 row · 1 column

status
Stage area STAGE_T122 successfully created.

Created. Here is what SHOW STAGES says about it.

Query

SHOW STAGES LIKE 'STAGE_T122' IN SCHEMA PUBLIC;

Result1 row · 17 columns · scroll →

Seventeen columns. Scroll the grid sideways for the whole row.

created_on	name	database_name	schema_name	url	has_credentials	has_encryption_key	owner	comment	region	type	cloud	notification_channel	storage_integration	endpoint	owner_role_type	directory_enabled
2026-09-30 04:03:38.892000-07:00	STAGE_T122	DEMO_DB	PUBLIC		N	N	ACCOUNTADMIN	episode 122 probe stage	NULL	INTERNAL	NULL	NULL	NULL	NULL	ROLE	N

That is a wide row, so let's pull the interesting columns out of it.

Query

SELECT "name" AS THE_NAME, "type" AS THE_TYPE, "comment" AS THE_COMMENT,
       "cloud" AS THE_CLOUD, "url" AS THE_URL
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 5 columns

THE_NAME	THE_TYPE	THE_COMMENT	THE_CLOUD	THE_URL
STAGE_T122	INTERNAL	episode 122 probe stage	NULL

type is INTERNAL. cloud is a NULL. And the url cell is empty, which is not the same thing as a null.

Now let's ask the catalog the same question. This is a view, so it is easier to query than a SHOW.

Query

SELECT STAGE_NAME, STAGE_TYPE, STAGE_URL, COMMENT
FROM INFORMATION_SCHEMA.STAGES
WHERE STAGE_NAME = 'STAGE_T122';

Result1 row · 4 columns

STAGE_NAME	STAGE_TYPE	STAGE_URL	COMMENT
STAGE_T122	Internal Named	NULL	episode 122 probe stage

The catalog calls it Internal Named, and it reports STAGE_URL as a real NULL where SHOW STAGES gave us an empty string. Same missing url, two different answers from two different sources.

Why the two grids look like they disagree

Have a look at those two grids again. An empty cell on this site is marked empty and means an empty string. A cell reading NULL in grey is a real SQL null. SHOW STAGES gives the first one and INFORMATION_SCHEMA.STAGES gives the second, for the same stage, at the same moment. If you ever write WHERE url IS NULL against the wrong one of those, this is why it finds nothing.

Either way there is no url, and the reason is worth holding on to. An internal stage is Snowflake's own storage. There is nowhere else for it to point at.

Let's describe it, and this is the part nobody expects

We made that stage with one line. Let's ask Snowflake what it actually built.

Query

DESCRIBE STAGE STAGE_T122;

Result34 rows · 5 columns

The whole thing, as it came back.

parent_property	property	property_type	property_value	property_default
STAGE_FILE_FORMAT	TYPE	String	CSV	CSV
STAGE_FILE_FORMAT	RECORD_DELIMITER	String	\n	\n
STAGE_FILE_FORMAT	FIELD_DELIMITER	String	,	,
STAGE_FILE_FORMAT	FILE_EXTENSION	String
STAGE_FILE_FORMAT	SKIP_HEADER	Integer	0	0
STAGE_FILE_FORMAT	PARSE_HEADER	Boolean	false	false
STAGE_FILE_FORMAT	DATE_FORMAT	String	AUTO	AUTO
STAGE_FILE_FORMAT	TIME_FORMAT	String	AUTO	AUTO
STAGE_FILE_FORMAT	TIMESTAMP_FORMAT	String	AUTO	AUTO
STAGE_FILE_FORMAT	BINARY_FORMAT	String	HEX	HEX
STAGE_FILE_FORMAT	ESCAPE	String	NONE	NONE
STAGE_FILE_FORMAT	ESCAPE_UNENCLOSED_FIELD	String	\\	\\
STAGE_FILE_FORMAT	TRIM_SPACE	Boolean	false	false
STAGE_FILE_FORMAT	FIELD_OPTIONALLY_ENCLOSED_BY	String	NONE	NONE
STAGE_FILE_FORMAT	NULL_IF	List	[\\N]	[\\N]
STAGE_FILE_FORMAT	COMPRESSION	String	AUTO	AUTO
STAGE_FILE_FORMAT	ERROR_ON_COLUMN_COUNT_MISMATCH	Boolean	true	true
STAGE_FILE_FORMAT	VALIDATE_UTF8	Boolean	true	true
STAGE_FILE_FORMAT	SKIP_BLANK_LINES	Boolean	false	false
STAGE_FILE_FORMAT	REPLACE_INVALID_CHARACTERS	Boolean	false	false
STAGE_FILE_FORMAT	EMPTY_FIELD_AS_NULL	Boolean	true	true
STAGE_FILE_FORMAT	SKIP_BYTE_ORDER_MARK	Boolean	true	true
STAGE_FILE_FORMAT	ENCODING	String	UTF8	UTF8
STAGE_FILE_FORMAT	MULTI_LINE	Boolean	true	true
STAGE_COPY_OPTIONS	ON_ERROR	String	ABORT_STATEMENT	ABORT_STATEMENT
STAGE_COPY_OPTIONS	SIZE_LIMIT	Long
STAGE_COPY_OPTIONS	PURGE	Boolean	false	false
STAGE_COPY_OPTIONS	RETURN_FAILED_ONLY	Boolean	false	false
STAGE_COPY_OPTIONS	ENFORCE_LENGTH	Boolean	true	true
STAGE_COPY_OPTIONS	TRUNCATECOLUMNS	Boolean	false	false
STAGE_COPY_OPTIONS	FORCE	Boolean	false	false
STAGE_LOCATION	URL	String
DIRECTORY	ENABLE	Boolean	false	false
DIRECTORY	AUTO_REFRESH	Boolean	false	false

Thirty-four rows, for a stage we created with a name and a comment.

Read the first column. The rows are grouped by a parent property, and there are four of those: STAGE_FILE_FORMAT, STAGE_COPY_OPTIONS, STAGE_LOCATION and DIRECTORY. Most of the rows sit under the first one.

And look at what those are. TYPE is CSV. FIELD_DELIMITER is a comma. SKIP_HEADER is 0. PARSE_HEADER is false. That is a whole file format, sitting inside a stage.

Have a look at the property_value column as well. Every row's value is the same as its default, because we named none of them. And the URL row under STAGE_LOCATION is an empty cell, matching what SHOW STAGES told us.

Remember this one

A stage carries a file format inside it, and that is the format you get when you read a file and name no format at all. We are going to watch that happen further down this page, and it does not do what people expect.

Now let's get a file into all three, without a single PUT

The usual way to get a file into a stage is PUT, which needs a file on your own machine. That is the next post. Here we are going to write the file out of the table instead, with COPY INTO.

First a named file format, so that all three writes agree with each other.

Query

CREATE OR REPLACE FILE FORMAT CSV_T122
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 0
    COMPRESSION = NONE;

Result1 row · 1 column

status
File format CSV_T122 successfully created.

Now COPY INTO with the stage on the left and the table on the right. That direction writes files out.

Query

COPY INTO @STAGE_T122/t122/sales
FROM SALES_T122
FILE_FORMAT = (FORMAT_NAME = 'CSV_T122')
OVERWRITE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
6	86	86

Six rows unloaded, and Snowflake reports the bytes in and the bytes out in its own columns.

Now the same thing into my user stage.

Query

COPY INTO @~/t122/sales
FROM SALES_T122
FILE_FORMAT = (FORMAT_NAME = 'CSV_T122')
OVERWRITE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
6	86	86

Six rows again. The user stage takes a COPY INTO like any other.

And now the one I genuinely did not know the answer to. The table's own stage.

Query

COPY INTO @%SALES_T122/t122/sales
FROM SALES_T122
FILE_FORMAT = (FORMAT_NAME = 'CSV_T122')
OVERWRITE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
6	86	86

Six rows, and it worked. So all three kinds take an unload, including the one that is not really a place at all. That was a real question and that is the answer.

Now let's look at the three paths carefully

Three files written by three almost identical statements. Let's list them one at a time and read the names.

The named stage

LIST @STAGE_T122/t122/;

Result1 row · 4 columns

name	size	md5	last_modified
stage_t122/t122/sales_0_0_0.csv	96	10580e8adee6e0c7eaff51404f86ae0f	Wed, 30 Sep 2026 11:03:42 GMT

The user stage

LIST @~/t122/;

Result1 row · 4 columns

name	size	md5	last_modified
t122/sales_0_0_0.csv	96	3bbe8bd190cde26dd624bbdcc7bef1f0	Wed, 30 Sep 2026 11:03:42 GMT

The table stage

LIST @%SALES_T122;

Result1 row · 4 columns

name	size	md5	last_modified
t122/sales_0_0_0.csv	96	c396a45708a9dd9941c6d9e5e507f14c	Wed, 30 Sep 2026 11:03:42 GMT

The named stage put its own name on the front of the path, in lower case, even though we wrote it in capitals. The other two did not. That is worth knowing before you write one of these paths by hand.

And one more thing in those grids. COPY INTO reported the bytes it wrote a moment ago, and LIST reports a bigger number for the file now sitting there. Both are Snowflake's own numbers and this post is not going to guess at the difference. Episode 124 has an answer for it.

And LIST is a command, not a query

You cannot put a WHERE on a LIST, because it is not a SELECT. What you can do is run it and then read its output back as a table.

Run this first

LIST @STAGE_T122/t122/;

Result1 row · 4 columns

name	size	md5	last_modified
stage_t122/t122/sales_0_0_0.csv	96	10580e8adee6e0c7eaff51404f86ae0f	Wed, 30 Sep 2026 11:03:42 GMT

Query

SELECT "name" AS THE_FILE, "size" AS SIZE_IN_BYTES, "md5" AS THE_MD5
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

THE_FILE	SIZE_IN_BYTES	THE_MD5
stage_t122/t122/sales_0_0_0.csv	96	10580e8adee6e0c7eaff51404f86ae0f

RESULT_SCAN(LAST_QUERY_ID()) turns the last statement's output into something you can select from, so the file name, its size and its checksum are now columns you can filter and join.

Let's read the file back out of the stage

A CSV file has no column names, so you ask for its columns by position with $1, $2 and $3.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @STAGE_T122/t122/ (FILE_FORMAT => 'CSV_T122')
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	90.00
6	west	420.00

There are the six rows, straight out of a file, with no table in the query at all.

And the same read from my user stage.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @~/t122/ (FILE_FORMAT => 'CSV_T122')
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	90.00
6	west	420.00

Identical six rows. Now watch this one. The same read again, with no file format named at all.

Query

SELECT $1 AS THE_WHOLE_FIRST_FIELD
FROM @STAGE_T122/t122/;

Result6 rows · 1 column

THE_WHOLE_FIRST_FIELD
1
2
3
4
5
6

One column, six rows, and the values are the sale IDs. Not the whole line.

So it still split the line on the comma. That is the DESCRIBE from earlier doing its job: the stage carries a format of its own, that format is CSV with a comma delimiter, and that is what the read used.

Naming no format does not mean no parsing. It means you got the stage's format without being told.

The metadata columns work on a stage read too.

Query

SELECT METADATA$FILENAME     AS THE_FILE,
       METADATA$FILE_ROW_NUMBER AS THE_ROW_IN_THAT_FILE,
       $1                    AS SALE_ID
FROM @STAGE_T122/t122/ (FILE_FORMAT => 'CSV_T122')
ORDER BY $1;

Result6 rows · 3 columns

THE_FILE	THE_ROW_IN_THAT_FILE	SALE_ID
t122/sales_0_0_0.csv	1	1
t122/sales_0_0_0.csv	2	2
t122/sales_0_0_0.csv	3	3
t122/sales_0_0_0.csv	4	4
t122/sales_0_0_0.csv	5	5
t122/sales_0_0_0.csv	6	6

METADATA$FILENAME tells you which file each row came out of, and METADATA$FILE_ROW_NUMBER tells you where it sat in that file.

Let's count it rather than eyeball it.

Query

SELECT COUNT(*)                      AS ROWS_READ_BACK,
       COUNT(DISTINCT METADATA$FILENAME) AS FILES_THEY_CAME_FROM,
       SUM($3)                       AS TOTAL_AMOUNT
FROM @STAGE_T122/t122/ (FILE_FORMAT => 'CSV_T122');

Result1 row · 3 columns

ROWS_READ_BACK	FILES_THEY_CAME_FROM	TOTAL_AMOUNT
6	1	1245.75

And the same two numbers straight from the table.

Query

SELECT COUNT(*) AS ROWS_IN_THE_TABLE, SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T122;

Result1 row · 2 columns

ROWS_IN_THE_TABLE	TOTAL_AMOUNT
6	1245.75

Six rows and a total of 1245.75 from the file, six rows and 1245.75 from the table. Nothing was lost going out and coming back.

What happens when we try to drop the other two?

This is the other half of the episode. A named stage is an object. Let's find out whether the other two are.

First, dropping a table's stage.

Refused

DROP STAGE @%SALES_T122;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 11 unexpected '@%SALES_T122'.

Read that message carefully, because it is not what you would expect. It is a syntax error. Not “you are not allowed to” and not “no such object”. Snowflake could not parse the statement at all.

Altering it gives the same kind of refusal.

Refused

ALTER STAGE @%SALES_T122 SET COMMENT = 'not possible';

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 12 unexpected '@%SALES_T122'.

Same error, one character further along. DROP STAGE is eleven characters and ALTER STAGE is twelve, and the position Snowflake reports is simply where the keyword ends and the thing it cannot read begins.

Now the user stage.

Refused

DROP STAGE @~;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 11 unexpected '@~'.

The same again, on @~.

And one I was not sure about. I thought DESCRIBE might be allowed where DROP is not, since describing something changes nothing.

Refused

DESCRIBE STAGE @~;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 15 unexpected '@~'.

It is not. So @~ and @%TABLENAME are not nouns the DDL grammar takes. They are names you can read files through, and that is all.

The named one takes everything. Let's change its comment.

Query

ALTER STAGE STAGE_T122 SET COMMENT = 'altered in episode 122';

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW STAGES LIKE 'STAGE_T122' IN SCHEMA PUBLIC;

Result1 row · 17 columns · scroll →

Scroll sideways for the comment column.

created_on	name	database_name	schema_name	url	has_credentials	has_encryption_key	owner	comment	region	type	cloud	notification_channel	storage_integration	endpoint	owner_role_type	directory_enabled
2026-09-30 04:03:38.892000-07:00	STAGE_T122	DEMO_DB	PUBLIC		N	N	ACCOUNTADMIN	altered in episode 122	NULL	INTERNAL	NULL	NULL	NULL	NULL	ROLE	N

Query

SELECT "name" AS THE_NAME, "comment" AS THE_COMMENT
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

THE_NAME	THE_COMMENT
STAGE_T122	altered in episode 122

Statement executed successfully, and the comment reads back changed. Alter, comment, drop. The named stage takes all of it because it is a real object.

And two more kinds of named stage

There are two variations worth seeing before we finish. First a temporary stage, which goes away with your session.

Query

CREATE OR REPLACE TEMPORARY STAGE TEMPSTAGE_T122;

Result1 row · 1 column

status
Stage area TEMPSTAGE_T122 successfully created.

And second, a stage with a directory table, which is a queryable index of the files in it.

Query

CREATE OR REPLACE STAGE DIRSTAGE_T122
    DIRECTORY = (ENABLE = TRUE);

Result1 row · 1 column

status
Stage area DIRSTAGE_T122 successfully created.

Let's write a file into that one.

Query

COPY INTO @DIRSTAGE_T122/t122/sales
FROM SALES_T122
FILE_FORMAT = (FORMAT_NAME = 'CSV_T122')
OVERWRITE = TRUE;

Result1 row · 3 columns

rows_unloaded	input_bytes	output_bytes
6	86	86

A directory table does not notice a new file on its own. You have to refresh it.

Query

ALTER STAGE DIRSTAGE_T122 REFRESH;

Result1 row · 3 columns

file	status	description
stages/257a0e4b-c210-46b4-a0ae-43e68bdfdb6a/t122/sales_0_0_0.csv	REGISTERED_NEW	File registered successfully.

REGISTERED_NEW, and File registered successfully. It found the file we had just written.

Now we can select from the directory table itself.

Query

-- The grid for this one is not on the page. See the note below.
SELECT RELATIVE_PATH, SIZE, FILE_URL
FROM DIRECTORY(@DIRSTAGE_T122);

Why that one has no grid

That is the one statement on this page with no result grid under it. Its FILE_URL column returns the full host name of the account this course is recorded on, so the episode trims that column on screen and the post leaves the grid out rather than publish it. Run it on your own account and you will get your own url. Everything else on this page is a real captured result.

Now let's ask the same question of the stage that has no directory table.

Refused

SELECT RELATIVE_PATH, SIZE FROM DIRECTORY(@STAGE_T122);

Query failed — Snowflake returned an error

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

DIRECTORY not enabled for the stage STAGE_T122. That is a good error to recognise, because the fix is not in the query. It is on the CREATE STAGE that made the stage in the first place.

And here are all three named stages we have built, side by side.

Query

SHOW STAGES IN SCHEMA PUBLIC;

Result3 rows · 17 columns · scroll →

Scroll sideways for the type and the directory column.

created_on	name	database_name	schema_name	has_credentials	has_encryption_key	owner	comment	region	type	cloud	notification_channel	storage_integration	endpoint	owner_role_type	directory_enabled
2026-09-30 04:03:46.337000-07:00	DIRSTAGE_T122	DEMO_DB	PUBLIC	N	N	ACCOUNTADMIN		NULL	INTERNAL	NULL	NULL	NULL	NULL	ROLE	Y
2026-09-30 04:03:38.892000-07:00	STAGE_T122	DEMO_DB	PUBLIC	N	N	ACCOUNTADMIN	altered in episode 122	NULL	INTERNAL	NULL	NULL	NULL	NULL	ROLE	N
2026-09-30 04:03:46.001000-07:00	TEMPSTAGE_T122	DEMO_DB	PUBLIC	N	N	ACCOUNTADMIN		NULL	INTERNAL TEMPORARY	NULL	NULL	NULL	NULL	ROLE	N

Query

SELECT "name" AS THE_NAME, "type" AS THE_TYPE, "directory_enabled" AS DIRECTORY_ENABLED
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE RIGHT("name", 5) = '_T122'
ORDER BY "name";

Result3 rows · 3 columns

THE_NAME	THE_TYPE	DIRECTORY_ENABLED
DIRSTAGE_T122	INTERNAL	Y
STAGE_T122	INTERNAL	N
TEMPSTAGE_T122	INTERNAL TEMPORARY	N

Three rows, each one saying what it is. The ordinary one and the directory one are INTERNAL. The temporary one has a type of its own, INTERNAL TEMPORARY. And directory_enabled is Y on exactly the one we asked for it on.

One last thing: let's take our files back out

This is the part that matters more here than usual, and it is the reason the whole demo is careful.

A named stage I can drop, and everything in it goes with it. The user stage and the table stage are different. They are shared, they are permanent, and anything written into them stays in the account after the post ends. So everything we wrote to them went under a t122/ prefix, and now it comes out.

Cleanup

-- Put the account back the way we found it.
-- The first two REMOVEs matter most: those two stages are shared and
-- permanent, and anything left in them stays there after the post ends.
REMOVE @~/t122/;
REMOVE @%SALES_T122;

DROP STAGE       IF EXISTS STAGE_T122;
DROP STAGE       IF EXISTS TEMPSTAGE_T122;
DROP STAGE       IF EXISTS DIRSTAGE_T122;
DROP FILE FORMAT IF EXISTS CSV_T122;
DROP TABLE       IF EXISTS SALES_T122;

Query

REMOVE @~/t122/;

Result1 row · 2 columns

name	result
t122/sales_0_0_0.csv	removed

Query

REMOVE @%SALES_T122;

Result1 row · 2 columns

name	result
t122/sales_0_0_0.csv	removed

REMOVE names each file and says removed. Now let's check both of them.

Query

LIST @~/t122/;

Result0 rows · 4 columns

name	size	md5	last_modified

Query

LIST @%SALES_T122;

Result0 rows · 4 columns

name	size	md5	last_modified

Zero rows, and zero rows. Then the three stages, the file format and the table get dropped, and the last query counts what is left.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T122') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE RIGHT(STAGE_NAME, 5) = '_T122') AS PROBE_STAGES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILE_FORMATS
        WHERE RIGHT(FILE_FORMAT_NAME, 5) = '_T122') 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.

And one final look in the user stage, after everything.

Query

LIST @~/t122/;

Result0 rows · 4 columns

name	size	md5	last_modified

Empty. That is the only way to show that nothing of mine was left behind in a stage that nobody else can see into.

Key points

@~ is your user stage and @%TABLENAME is a table's own stage. Both already exist and LIST works on them immediately, with no error.
A named stage is the only one of the three you create or configure.
DROP, ALTER and DESCRIBE on the other two are syntax errors, not permission refusals. They are not nouns the DDL grammar takes.
An internal stage has no url, because it is Snowflake's own storage.
SHOW STAGES reports that missing url as an empty string and INFORMATION_SCHEMA.STAGES reports it as a real NULL.
DESCRIBE STAGE returns thirty-four rows, and most of them are a file format.
So a read with no format named still splits on a comma. It used the stage's own format.
COPY INTO writes files into all three kinds, including the table stage.
The named stage prefixes its own name, lower-cased, onto the path. The other two do not.
RESULT_SCAN(LAST_QUERY_ID()) turns a LIST or a SHOW into a table you can query.
METADATA$FILENAME and METADATA$FILE_ROW_NUMBER work on a stage read.
A temporary stage lists as INTERNAL TEMPORARY.
A directory-enabled stage answers Y in directory_enabled, reports REGISTERED_NEW on refresh, and has a DIRECTORY() table you can select from.
Anything you write to a user stage or a table stage has to be REMOVEd on the way out, because those two are shared and permanent.

Next in this series

Episode 123 — PUT and GET. How files get in from your own machine, why the file in the stage ends up with a different name from the one you uploaded, and what PUT does when you run it 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. The _T122 objects are created and dropped by the script, and everything written to the user stage and the table stage is removed again at the end and shown to be gone. Empty cells marked empty are empty strings and cells reading NULL are real nulls, which matters on this page because two grids report the same missing url both ways. One statement, the DIRECTORY() read, is shown without its grid because that grid carries the account's own host name. Nothing on this page is timed; the byte counts are Snowflake's own report columns, quoted as they came back, and no performance claims are made anywhere in this series. Stage behaviour follows the Snowflake staging documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 122 - Two Of Your Stages Already Exist And You Never Created Either

Let's create some sample data

Let's look inside a stage we never created

Now let's create the one that does not exist yet

Let's describe it, and this is the part nobody expects

Now let's get a file into all three, without a single PUT

Now let's look at the three paths carefully

And LIST is a command, not a query

Let's read the file back out of the stage

What happens when we try to drop the other two?

And two more kinds of named stage

One last thing: let's take our files back out

Key points

No comments:

Post a Comment