Snowflake SQL Tutorial 153 - I Ran The Same Query Twice And Got 4 Then 1

Snowflake SQL · Episode 153

In this post we are going to cover RESULT_SCAN and LAST_QUERY_ID in Snowflake.

Together they let you read a previous query's output as if it were a table. LAST_QUERY_ID() gives you the id of a query that already ran, and RESULT_SCAN takes that id and hands you back its rows.

It is more useful than it sounds, and it has one trap in it that catches almost everybody. So we are going to walk straight into the trap on purpose, and then look at the thing you should do instead.

We will start with what an id actually is and build it up step by step. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

One thing about this post in particular. Because the subject is query ids, and because these posts are built from a real account, several grids on this page would have printed a real query id. Those grids are left out, the statement is shown on its own, and the text says so every time it happens.

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

On this page
  1. Let's create some sample data
  2. Let's start with what an id actually is
  3. Now the idiom itself
  4. So now let's count them instead
  5. So how do we reach past the scan?
  6. Now the thing to actually do: keep the id
  7. And now let's put some distance in
  8. And scanning does not use the result up
  9. Now the use that earns this function its keep
  10. And DESCRIBE does the same
  11. So what else leaves something behind to scan?
  12. What about a result with no rows in it?
  13. And a plan, which is where we came in
  14. Can a scan go inside a CTE, a join or a view?
  15. And now the answer I did not expect
  16. Now some ordinary refusals
  17. One last thing: a failed query still has an id
  18. And let's leave the schema the way we found it

Let's create some sample data

First let's see where the session is pointing.

Query
SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_VERSION()   AS THE_VERSION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMATHE_VERSION
DEMO_DBPUBLIC10.35.101

Now a small sales table, and five rows in it.

Sample data
CREATE OR REPLACE TABLE SALES_T153 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);
Result1 row · 1 column
status
Table SALES_T153 successfully created.
Sample data
INSERT INTO SALES_T153 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00),
       (5, 'north',  45.00);
Result1 row · 1 column
number of rows inserted
5
Query
SELECT * FROM SALES_T153 ORDER BY SALE_ID;
Result5 rows · 3 columns
SALE_IDREGIONAMOUNT
1north100.00
2south250.50
3east75.25
4west310.00
5north45.00

And a second small table, so there is more than one thing to look at when we get to SHOW TABLES.

Sample data
CREATE OR REPLACE TABLE ARCHIVE_T153 (
    SALE_ID INT,
    NOTE    VARCHAR(40)
);
Result1 row · 1 column
status
Table ARCHIVE_T153 successfully created.
Sample data
INSERT INTO ARCHIVE_T153 (SALE_ID, NOTE)
VALUES (1, 'closed last year'), (3, 'refunded');
Result1 row · 1 column
number of rows inserted
2
Query
SELECT * FROM ARCHIVE_T153 ORDER BY SALE_ID;
Result2 rows · 2 columns
SALE_IDNOTE
1closed last year
3refunded

Let's start with what an id actually is

Here is a query worth keeping. Four regions and their totals.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00

And now let's ask Snowflake for the id of the query that just ran.

Query
SELECT LAST_QUERY_ID() AS THE_LAST_ID;

That grid is not on this page. The id itself is one of its columns, this blog runs on a real Snowflake account, and the builder cannot leave one column out of a grid, so the whole grid comes out.

Then the demo asks for the id a second time, so the two can be compared, and then asks how long an id is and what type it is.

Query
SELECT LAST_QUERY_ID() AS THE_LAST_ID;
Query
SELECT LAST_QUERY_ID() AS THE_LAST_ID,
       LENGTH(LAST_QUERY_ID()) AS HOW_MANY_CHARACTERS,
       TYPEOF(TO_VARIANT(LAST_QUERY_ID())) AS WHAT_TYPE_IT_IS;

Same reason, both left out.

So let's get at the same facts from a statement whose result we can put on the page. This one asks three questions and answers all of them without printing an id.

Query
SELECT IFF(LAST_QUERY_ID(1) = LAST_QUERY_ID(-1),
           'the same query', 'a different query')     AS POSITIVE_VS_NEGATIVE,
       LENGTH(LAST_QUERY_ID(1))                       AS OFFSET_ONE_IS_STILL_AN_ID,
       IFF(LAST_QUERY_ID(2) = LAST_QUERY_ID(1),
           'the same query', 'a different query')     AS TWO_VERSUS_ONE;
Result1 row · 3 columns
POSITIVE_VS_NEGATIVEOFFSET_ONE_IS_STILL_AN_IDTWO_VERSUS_ONE
a different query36a different query

Read the middle column first. An id is thirty six characters long. That is all it is, a string of that length, and you can hold it in a variable like any other string.

And the two comparison columns tell us LAST_QUERY_ID takes an argument. A positive offset is accepted, it names a different query from the negative one, and offset two names a different query again from offset one.

Now let's reach much further back than the session goes.

Query
SELECT LAST_QUERY_ID(-100000) AS MILES_BACK;
Query failed — Snowflake returned an error
001066 (22023): SQL compilation error:
Value for parameter 1 exceeds maximum allowable value (10,000).

001066, and it tells us the limit. So the offset is a real argument with a real range, and ten thousand is as far back as it reaches.

The one thing to remember

Keep hold of one sentence from this section: an id is just a string. Everything that happens in the rest of this post follows from that, including the trap.

Now the idiom itself

Let's run a query with an obvious number of rows in it.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00

Four rows. And this reads them straight back.

Query
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00

The same four rows. That is the pattern you will see everywhere, and it works exactly as advertised.

So now let's count them instead

Counting the rows of the last result is the kind of thing you would actually want. Let's run the query again, and then count.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00
Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
HOW_MANY_ROWS
4

Four. Exactly right, four regions in that result.

Now let's run that identical statement a second time. Nothing in between. The same text.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
HOW_MANY_ROWS
1

One.

Not four. One.

And here is why. By the time the second one ran, the thing that had run last was the first count, and that returned a single row. So the second count counted the first count's answer.

Let's run it a third time to be sure what it is doing.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
HOW_MANY_ROWS
1

One again, and now it is stable. It will count its own kind of answer for ever.

The trap, in one line

LAST_QUERY_ID() moves every time you run a query, including the query that reads it. A scan is a query. The moment you write a second statement that uses LAST_QUERY_ID(), the first one is what it points at.

So how do we reach past the scan?

With the offset. Let's set the same situation up again.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00
Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
HOW_MANY_ROWS
4

Four, because that count is the first one after the query. Now there is a scan in the way, so let's step over it with -2.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID(-2)));
Result1 row · 1 column
HOW_MANY_ROWS
4

Four. So the offset does get us past our own scan.

It works, but counting backwards is fragile. Add one statement anywhere above and every number in your script is wrong, and nothing will tell you.

Now the thing to actually do: keep the id

Run the query you care about, and then put its id in a session variable straight away.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00
Query
SET THE_ID = LAST_QUERY_ID();
Result1 row · 1 column
status
Statement executed successfully.

Now the demo looks at what got kept.

Query
SELECT $THE_ID AS THE_KEPT_ID;

That grid is not on this page. The id itself is one of its columns, this blog runs on a real Snowflake account, and the builder cannot leave one column out of a grid, so the whole grid comes out. What it holds is one string, sitting in a variable, going nowhere.

And now let's put some distance in

Three completely unrelated queries, so the one we care about is well out of reach of any offset we would want to count.

Query
SELECT COUNT(*) AS ROWS_IN_SALES FROM SALES_T153;
Result1 row · 1 column
ROWS_IN_SALES
5
Query
SELECT COUNT(*) AS ROWS_IN_ARCHIVE FROM ARCHIVE_T153;
Result1 row · 1 column
ROWS_IN_ARCHIVE
2
Query
SELECT CURRENT_DATE() AS TODAYS_DATE;
Result1 row · 1 column
TODAYS_DATE
2026-10-06

And now let's scan the kept id anyway.

Query
SELECT * FROM TABLE(RESULT_SCAN($THE_ID));
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00

The same four rows. Because an id is just a string, and that string still means what it meant when we saved it.

This is the habit to take away from the whole post. The moment you have an id you want, put it somewhere. Then nothing you run afterwards can move it.

And scanning does not use the result up

Let's scan the very same id again, and this time count and sum it.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS, SUM(TOTAL_AMOUNT) AS SUM_OF_THE_TOTALS
  FROM TABLE(RESULT_SCAN($THE_ID));
Result1 row · 2 columns
HOW_MANY_ROWSSUM_OF_THE_TOTALS
4780.75

Four rows and a total of 780.75. So a scan is not destructive. You can read the same id as many times as you like.

And look at what that sum proves on its own. The scan kept the column names and the types, because we could add the totals up. Let's make that obvious.

Query
SELECT REGION, TOTAL_AMOUNT * 2 AS DOUBLED
  FROM TABLE(RESULT_SCAN($THE_ID)) ORDER BY REGION;
Result4 rows · 2 columns
REGIONDOUBLED
east150.50
north290.00
south501.00
west620.00

We referred to both columns by the names they had, and we multiplied one of them. So it came back as a number, not as text.

And what about the order of the rows?

Query
SELECT REGION FROM TABLE(RESULT_SCAN($THE_ID));
Result4 rows · 1 column
REGION
east
north
south
west

That is the order the kept result was in. But there is no ORDER BY in that query, and one run cannot promise it always will be. If the order matters to you, put an ORDER BY on the scan.

Now the use that earns this function its keep

SHOW TABLES is the way you have always listed tables. It is lovely to read and impossible to filter.

Query
SHOW TABLES LIKE '%\\_T153';

That output is not on this page either, and for a different reason. It is twenty seven columns wide and one of them is an owner column, so the whole grid comes out. The narrow grids below carry the same two rows, which is the point of this section anyway.

So let's point RESULT_SCAN at it and ask for just the columns we want, by name.

Query
SELECT name, rows FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 13 unexpected 'rows'.

001003, and read which word it is complaining about. Our statement asked for two columns and the message names only rows.

rows is a reserved word, which is why it is the one that broke. Whether the other name on its own would have been fine, this grid cannot tell us, so we are not going to say.

The habit to take away is to quote all of them. A plan's and a SHOW's column names are lower case, so they need the quotes anyway.

Query
SELECT "name", "rows", "bytes" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name";
Result2 rows · 3 columns
namerowsbytes
ARCHIVE_T15321024
SALES_T15351024

Two rows and three columns, out of a twenty-seven column output. And once it is a table, it filters and sorts like anything else.

Query
SELECT "name", "rows"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 WHERE "rows" > 2
 ORDER BY "name";
Result1 row · 2 columns
namerows
SALES_T1535

One table left after the filter. Try writing that against SHOW TABLES on its own.

And is that rows column text, or a number?

Query
SELECT "name", TYPEOF(TO_VARIANT("rows")) AS WHAT_TYPE_ROWS_IS,
       "rows" + 1 AS ROWS_PLUS_ONE
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) ORDER BY "name";
Result2 rows · 3 columns
nameWHAT_TYPE_ROWS_ISROWS_PLUS_ONE
ARCHIVE_T153INTEGER3
SALES_T153INTEGER6

An integer, and adding one to it works. So the types survive the scan here too.

And DESCRIBE does the same

Query
DESCRIBE TABLE SALES_T153;
Result3 rows · 13 columns · scroll →
nametypekindnull?defaultprimary keyunique keycheckexpressioncommentpolicy nameprivacy domainwrite default
SALE_IDNUMBER(38,0)COLUMNYNULLNNNULLNULLNULLNULLNULLNULL
REGIONVARCHAR(10)COLUMNYNULLNNNULLNULLNULLNULLNULLNULL
AMOUNTNUMBER(10,2)COLUMNYNULLNNNULLNULLNULLNULLNULLNULL

Thirteen columns, and no owner among them, so this one is on the page. Most of what we want is in the first few columns.

Query
SELECT "name", "type", "null?"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result3 rows · 3 columns
nametypenull?
SALE_IDNUMBER(38,0)Y
REGIONVARCHAR(10)Y
AMOUNTNUMBER(10,2)Y

Three columns out of thirteen, and the same quoting rule.

And here is the same question asked of INFORMATION_SCHEMA instead.

Query
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'SALES_T153'
 ORDER BY ORDINAL_POSITION;
Result3 rows · 3 columns
COLUMN_NAMEDATA_TYPEIS_NULLABLE
SALE_IDNUMBERYES
REGIONTEXTYES
AMOUNTNUMBERYES

The same three facts in a different vocabulary. DESCRIBE gives you the type with its precision and a Y, and INFORMATION_SCHEMA gives you the type family and a YES. Neither is wrong. They are different interfaces to the same fact, and it is worth knowing which one your script is reading.

Three ways of showing nothing

A word about the empty-looking cells in that DESCRIBE grid, because Snowflake has three ways of saying nothing and they are not the same thing. The grids on this site print a real SQL NULL as a grey NULL, which is what every unset property above is. A SHOW command reports a property that was never set as an empty string instead, and this site prints that as an empty cell marked empty. And DESCRIBE USER puts the four characters null in as ordinary text, which prints as text. So if two grids look like they disagree about a missing value, check which of the three you are reading before you write a WHERE clause against it.

So what else leaves something behind to scan?

Let's start with an insert, because its row count comes back as a grid like anything else.

Query
INSERT INTO ARCHIVE_T153 (SALE_ID, NOTE) VALUES (5, 'written to be scanned');
Result1 row · 1 column
number of rows inserted
1
Query
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
number of rows inserted
1

There it is, heading and all. And a CREATE TABLE's status line does the same.

Query
CREATE OR REPLACE TABLE SCRATCH_T153 (N INT);
Result1 row · 1 column
status
Table SCRATCH_T153 successfully created.
Query
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
status
Table SCRATCH_T153 successfully created.

So anything that comes back as a grid has an id, and anything with an id can be scanned. That includes the one line of status text you usually ignore.

What about a result with no rows in it?

Query
SELECT SALE_ID FROM SALES_T153 WHERE AMOUNT > 100000;
Result0 rows · 1 column
SALE_ID

Nothing at all. Now let's scan it.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
Result1 row · 1 column
HOW_MANY_ROWS
0

Zero. Not an error. An empty result is still a result, it still has an id, and scanning it gives you an empty table rather than a complaint.

And a plan, which is where we came in

We used this in the last post to read every plan on that page. Here it is on its own.

Query
EXPLAIN SELECT * FROM SALES_T153;
Result3 rows · 10 columns · scroll →
stepidparentOperatorsoperationobjectsaliasexpressionspartitionsTotalpartitionsAssignedbytesAssigned
NULLNULLNULLGlobalStatsNULLNULLNULL111024
10NULLResultNULLNULLSALES_T153.SALE_ID, SALES_T153.REGION, SALES_T153.AMOUNTNULLNULLNULL
11[0]TableScanDEMO_DB.PUBLIC.SALES_T153NULLSALE_ID, REGION, AMOUNT111024
Query
SELECT "operation", "objects" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "id";
Result3 rows · 2 columns
operationobjects
ResultNULL
TableScanDEMO_DB.PUBLIC.SALES_T153
GlobalStatsNULL

Three rows, two columns, and the quoting rule again. A plan is a result, so of course it scans.

Can a scan go inside a CTE, a join or a view?

Let's get a result and keep its id, the same way as before.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00
Query
SET TOTALS_ID = LAST_QUERY_ID();
Result1 row · 1 column
status
Statement executed successfully.

Now a scan inside a WITH clause.

Query
WITH SCANNED AS (
    SELECT * FROM TABLE(RESULT_SCAN($TOTALS_ID))
)
SELECT REGION, TOTAL_AMOUNT FROM SCANNED WHERE TOTAL_AMOUNT > 100
 ORDER BY REGION;
Result3 rows · 2 columns
REGIONTOTAL_AMOUNT
north145.00
south250.50
west310.00

Three of the four regions, filtered inside the outer query. Now a scan joined to a real table.

Query
SELECT S.REGION, S.TOTAL_AMOUNT, COUNT(*) AS HOW_MANY_SALES
  FROM TABLE(RESULT_SCAN($TOTALS_ID)) S
  JOIN SALES_T153 R ON R.REGION = S.REGION
 GROUP BY S.REGION, S.TOTAL_AMOUNT
 ORDER BY S.REGION;
Result4 rows · 3 columns
REGIONTOTAL_AMOUNTHOW_MANY_SALES
east75.251
north145.002
south250.501
west310.001

Four regions, their totals from the scan, and how many sales each one had from the table. A kept result joins to live data as happily as two tables do.

And a view?

Query
CREATE OR REPLACE VIEW SCANVIEW_T153 AS
SELECT * FROM TABLE(RESULT_SCAN($TOTALS_ID));
Result1 row · 1 column
status
View SCANVIEW_T153 successfully created.

Created. Worth knowing that is allowed, and worth thinking twice before you do it, because that view is pinned to one id from one session.

And now the answer I did not expect

This is the trap's bigger brother. Let's write a procedure whose body scans the last query.

Create the procedure
CREATE OR REPLACE PROCEDURE SCANNER_T153()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    HOW_MANY NUMBER;
BEGIN
    SELECT COUNT(*) INTO :HOW_MANY FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    RETURN :HOW_MANY;
END;
$$;
Result1 row · 1 column
status
Function SCANNER_T153 successfully created.

And let's run a four row result immediately before calling it, so there is no doubt what the last query was.

Query
SELECT REGION, SUM(AMOUNT) AS TOTAL_AMOUNT
  FROM SALES_T153 GROUP BY REGION ORDER BY REGION;
Result4 rows · 2 columns
REGIONTOTAL_AMOUNT
east75.25
north145.00
south250.50
west310.00
Query
CALL SCANNER_T153();
Query failed — Snowflake returned an error
000709 (02000): Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 4 : Statement NULL not found

Read the message. Statement NULL not found. Not a wrong id. No id at all.

So inside a procedure LAST_QUERY_ID() is null, and the whole pattern is unavailable in there. If a procedure needs a result, pass it the id as an argument.

Now some ordinary refusals

An id that is not an id at all.

Query
SELECT * FROM TABLE(RESULT_SCAN('not-a-query-id'));
Query failed — Snowflake returned an error
000709 (02000): Statement not-a-query-id not found

And a properly formed id that belongs to nothing. This one is made up, all zeroes, which is why it is safe to show.

Query
SELECT * FROM TABLE(RESULT_SCAN('01b00000-0000-0000-0000-000000000000'));
Query failed — Snowflake returned an error
000709 (02000): Statement 01b00000-0000-0000-0000-000000000000 not found

Same code both times, and it quotes back whatever you gave it. An empty string gives the same code with a gap where the id would be.

Query
SELECT * FROM TABLE(RESULT_SCAN(''));
Query failed — Snowflake returned an error
000709 (02000): Statement  not found

And a NULL gives a different one.

Query
SELECT * FROM TABLE(RESULT_SCAN(NULL));
Query failed — Snowflake returned an error
001045 (22023): SQL compilation error:
argument needs to be a string: '1'

Argument needs to be a string, which is the same fact we started the post with, arriving from the other direction.

One more. RESULT_SCAN is a table function, so it needs TABLE(...) around it.

Query
SELECT * FROM RESULT_SCAN($TOTALS_ID);
Query failed — Snowflake returned an error
001003 (42000): SQL compilation error:
syntax error line 1 at position 25 unexpected '('.

One last thing: a failed query still has an id

That syntax error we just saw was a query, and it failed. So the demo's next statement scans it, to see what a failure leaves behind.

Query
SELECT COUNT(*) AS HOW_MANY_ROWS FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

That error pane is not on this page, because Snowflake's message names the query by its id and an error pane on this site is never edited. What it says is 000711, and the complaint is that the query has no result because it failed.

Which is the thing worth taking away. A failed query still gets an id. The id resolves. There is simply nothing behind it to give you, and that is a different refusal from the 000709 we got for an id that was never issued.

And let's leave the schema the way we found it

Two session variables to let go of, and then the objects.

Query
UNSET (THE_ID, TOTALS_ID);
Result1 row · 1 column
status
Statement executed successfully.
Query
DROP VIEW IF EXISTS DEMO_DB.PUBLIC.SCANVIEW_T153;
Result1 row · 1 column
status
SCANVIEW_T153 successfully dropped.
Query
DROP TABLE IF EXISTS DEMO_DB.PUBLIC.SCRATCH_T153;
Result1 row · 1 column
status
SCRATCH_T153 successfully dropped.

And the checks afterwards.

Query
SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION;
Result1 row · 3 columns
THE_DATABASETHE_SCHEMAANY_OPEN_TRANSACTION
DEMO_DBPUBLICNULL
Query
SELECT COUNT(*) AS PROBE_OBJECTS_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T153');
Result1 row · 1 column
PROBE_OBJECTS_LEFT
0

Same database and schema, nothing open, and none of this post's tables are left behind.

Key points

  • LAST_QUERY_ID() gives you a thirty six character string. That is all an id is, so you can keep one in a variable.
  • The argument is an offset. A positive one is accepted, consecutive offsets name different queries, and 001066 tells you ten thousand is the furthest back it reaches.
  • SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID())) reads the previous result back. That is the idiom you will see everywhere.
  • And here is the trap. The same count ran twice gave four and then one, because by the second time the thing that ran last was the first count.
  • An offset of -2 steps over your own scan, but counting backwards breaks the moment you add a statement.
  • So put the id in a session variable the moment you have it. Three unrelated queries later it still gives the same four rows.
  • Scanning is not destructive, and the scan keeps the column names and the types, so you can do arithmetic on what comes back.
  • The rows came back in order on this run. There was no ORDER BY, so put one on the scan if the order matters.
  • SHOW and DESCRIBE output becomes filterable, sortable rows. Quote the column names, because they are lower case and rows is a reserved word.
  • DESCRIBE and INFORMATION_SCHEMA give the same facts in different vocabulary.
  • An insert's row count, a create table's status line, an empty result and an EXPLAIN plan all have ids and all scan.
  • A scan goes inside a CTE, a join and even a view.
  • Inside a procedure LAST_QUERY_ID() is null, so pass the id in as an argument instead.
  • RESULT_SCAN needs TABLE(...) around it, a string rather than a NULL, and an id that was really issued.
  • A failed query still has an id. It resolves, and then tells you there is no result because it failed.
Next in this series

In the next post we start reaching outside Snowflake: external access, network rules and what a trial account will and will not let you do with them.

Every query, every result grid and every error pane on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. Eight grids are deliberately left out, because this episode's subject is query ids and a real account's query ids are not going on a public page; each one is shown as the statement that ran, and the text says so where it happens. The six SHOW TABLES grids are left out for the same mechanical reason, because one of their twenty seven columns is an owner column. The only id printed anywhere above is the made-up all-zeroes one, which belongs to nothing. No account identifier and no user name appears anywhere on this page. Every count in the text is read off one of the grids on this page. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake RESULT_SCAN documentation.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments (Atom)