Welcome To TechBrothersIT: Snowflake SQL Tutorial 140 - A View That Returns Different Rows Depending On Who Is Looking

Snowflake SQL Tutorial 140 - A View That Returns Different Rows Depending On Who Is Looking

Snowflake SQL · Episode 140

In this post we are going to cover session variables in Snowflake.

A session variable is a value you give a name to with SET, and read back with a dollar sign in front of the name. It belongs to your session and it disappears with it.

One sentence explains everything a session variable does and everything it refuses: a session variable holds a value. It does not hold a piece of SQL.

We will start with one number 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 140 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's set a variable and read it back
Now let's set a string, a date and a NULL
Can we set several at once?
Where can a variable go? Anywhere a literal can
And in an INSERT and an UPDATE
Where can the value come from?
Now the part everybody hits: a name is not a value
Where else does IDENTIFIER work?
And where IDENTIFIER does not help
Now the one that will catch somebody: a view that mentions a variable
Names, unsetting and where a variable lives
One last thing: the teardown

Let's create some sample data

A small table with a few amounts in it, so we have something for the variables to filter.

Sample data

-- Sample data for this post.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

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

INSERT INTO SALES_T140 (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), (6, 'south',  88.25);

Query

INSERT INTO SALES_T140 (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), (6, 'south',  88.25);

Result1 row · 1 column

number of rows inserted
6

Six rows across four regions. We will see all of them in a moment, once we have a variable to look at them with.

Let's set a variable and read it back

First, let's check what this session is already holding.

SHOW VARIABLES

SHOW VARIABLES;

Result0 rows · 7 columns · scroll →

session_id	created_on	updated_on	name	value	type	comment

Nothing. A clean session. Now let's set one.

Query

SET THE_CUTOFF = 100;

Result1 row · 1 column

status
Statement executed successfully.

And read it back, with a dollar sign in front of the name.

Query

SELECT $THE_CUTOFF AS THE_CUTOFF;

Result1 row · 1 column

THE_CUTOFF
100

A hundred. That is the whole mechanism. SET to store, dollar sign to read.

Now let's set a string, a date and a NULL

A variable does not have to hold a number.

A string

SET THE_REGION = 'north';

Result1 row · 1 column

status
Statement executed successfully.

A date

SET THE_DAY = '2026-01-15'::DATE;

Result1 row · 1 column

status
Statement executed successfully.

And a NULL

SET THE_NOTHING = NULL;

Result1 row · 1 column

status
Statement executed successfully.

Now let's ask the session what it is holding.

SHOW VARIABLES

SHOW VARIABLES;

Result4 rows · 7 columns · scroll →

session_id	created_on	updated_on	name	value	type
4761357795041302	2026-10-03 13:52:21.483000-07:00	2026-10-03 13:52:21.528000-07:00	THE_CUTOFF	100	fixed
4761357795041302	2026-10-03 13:52:21.858000-07:00	2026-10-03 13:52:21.886000-07:00	THE_DAY	2026-01-15	date
4761357795041302	2026-10-03 13:52:22.017000-07:00	2026-10-03 13:52:22.040000-07:00	THE_NOTHING	NULL	text
4761357795041302	2026-10-03 13:52:21.738000-07:00	2026-10-03 13:52:21.775000-07:00	THE_REGION	north	text

Four variables, each with a name, a value and a type. The types read fixed, date and text, which is a summary rather than the real answer.

Two things worth noticing in that grid. The session_id is the same on every row, because these four belong to one session and nothing else. And the blank cells are not all the same kind of blank: the value for THE_NOTHING is a real NULL, while every comment cell is an empty string, which the grid marks as empty.

Now let's ask more precisely what types we actually got.

Query

SELECT SYSTEM$TYPEOF($THE_CUTOFF)  AS THE_NUMBER_TYPE,
       SYSTEM$TYPEOF($THE_REGION)  AS THE_STRING_TYPE,
       SYSTEM$TYPEOF($THE_DAY)     AS THE_DATE_TYPE,
       SYSTEM$TYPEOF($THE_NOTHING) AS THE_NULL_TYPE;

Result1 row · 4 columns

THE_NUMBER_TYPE	THE_STRING_TYPE	THE_DATE_TYPE	THE_NULL_TYPE
NUMBER(3,0)[SB1]	VARCHAR(5)[LOB]	DATE[SB4]	NULL[LOB]

So the type is inferred from the literal we gave it, and it is narrow. A hundred did not get us a big number. It got us three digits. And north got us a varchar exactly as wide as the word.

Narrow by default

This matters the first time you try to put a bigger value into the same variable later in a script, or compare one variable with another. The declared width came from one literal typed once, and nothing warned you how narrow it was.

Can we set several at once?

We can, with brackets on both sides.

Query

SET (THE_LOW, THE_HIGH) = (10, 900);

Result1 row · 1 column

status
Statement executed successfully.

And both of them

SELECT $THE_LOW AS THE_LOW, $THE_HIGH AS THE_HIGH;

Result1 row · 2 columns

THE_LOW	THE_HIGH
10	900

Ten and nine hundred. The names on the left line up with the values on the right, in order.

Where can a variable go? Anywhere a literal can

Let's put two of them in a WHERE clause.

Query

SELECT SALE_ID, REGION, AMOUNT
FROM SALES_T140
WHERE AMOUNT >= $THE_LOW
  AND AMOUNT <= $THE_HIGH
ORDER BY SALE_ID;

Result6 rows · 3 columns

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

All six rows came back, because every amount in the table falls between ten and nine hundred. The query ran, the variables resolved, and nothing was excluded.

So let's write one that does exclude something, with a variable in a comparison and a variable in an equality.

Query

SELECT COUNT(*)    AS ROWS_THAT_MATCH,
       SUM(AMOUNT) AS TOTAL_AMOUNT
FROM SALES_T140
WHERE REGION = $THE_REGION
   OR AMOUNT > $THE_CUTOFF;

Result1 row · 2 columns

ROWS_THAT_MATCH	TOTAL_AMOUNT
4	705.50

Four rows and 705.50. The two northern rows came in on the equality and two more came in on the comparison. Both variables did their job.

Now one in the select list, and one inside an expression.

Query

SELECT SALE_ID,
       AMOUNT,
       AMOUNT - $THE_CUTOFF AS OVER_THE_CUTOFF,
       $THE_REGION          AS THE_REGION_WE_ASKED_FOR
FROM SALES_T140
ORDER BY SALE_ID
LIMIT 3;

Result3 rows · 4 columns

SALE_ID	AMOUNT	OVER_THE_CUTOFF	THE_REGION_WE_ASKED_FOR
1	100.00	0.00	north
2	250.50	150.50	north
3	75.25	-24.75	north

Three rows, because that query ends with a LIMIT 3. The amount minus the cutoff is worked out per row, and the region we asked for is printed beside it on every row, because it is the same value every time.

And one in the LIMIT itself, which is the one people are most surprised by.

Query

SELECT SALE_ID, AMOUNT
FROM SALES_T140
ORDER BY SALE_ID
LIMIT $THE_LOW;

Result6 rows · 2 columns

SALE_ID	AMOUNT
1	100.00
2	250.50
3	75.25
4	310.00
5	45.00
6	88.25

Read that one carefully. The limit is ten and the table has six rows, so all six came back. The grid shows us that a variable in a LIMIT parses and runs. It does not show us a limit cutting anything off, because there was nothing to cut.

And in an INSERT and an UPDATE

Let's set an id and insert a row whose every value comes from a variable.

An id

SET THE_NEW_ID = 7;

Result1 row · 1 column

status
Statement executed successfully.

The insert

INSERT INTO SALES_T140 (SALE_ID, REGION, AMOUNT)
VALUES ($THE_NEW_ID, $THE_REGION, $THE_CUTOFF);

Result1 row · 1 column

number of rows inserted
1

And what it put there

SELECT SALE_ID, REGION, AMOUNT FROM SALES_T140 WHERE SALE_ID = $THE_NEW_ID;

Result1 row · 3 columns

SALE_ID	REGION	AMOUNT
7	north	100.00

One row, and it holds the values from the variables rather than their names. Nothing stored the word $THE_REGION.

An UPDATE works the same way, in both the SET and the WHERE.

Query

UPDATE SALES_T140 SET AMOUNT = $THE_HIGH WHERE SALE_ID = $THE_NEW_ID;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
1	0

And the table

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

Result1 row · 2 columns

ROWS_IN_THE_TABLE	TOTAL_AMOUNT
7	1769.00

Seven rows and 1769.00. Seven is every row the table has, so nothing was removed by any of that.

A variable works in a DELETE in exactly the same place a literal would, but no delete was run on this account for this episode, so there is no grid for it here and we are not going to show you one.

Where can the value come from?

It does not have to be a literal we typed. Let's set one from a subquery.

Query

SET THE_BIGGEST = (SELECT MAX(AMOUNT) FROM SALES_T140);

Result1 row · 1 column

status
Statement executed successfully.

And what it got

SELECT $THE_BIGGEST AS THE_BIGGEST;

Result1 row · 1 column

THE_BIGGEST
900

Nine hundred, which is the largest amount in the table. So a scalar subquery is allowed. Which raises the obvious next question.

Query

SET THE_MANY = (SELECT AMOUNT FROM SALES_T140);

Query failed — Snowflake returned an error

090150 (22000): Single-row subquery returns more than one row.

090150, single-row subquery returns more than one row. Fair enough. A variable holds one value, so the subquery has to give one value.

Now one I expected to work. An array.

Query

SET THE_LIST = ARRAY_CONSTRUCT(1, 2, 3);

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'assignment from non-constant source expression'.

000002, unsupported feature, assignment from a non-constant source expression. And an object gives exactly the same thing.

Query

SET THE_SHAPE = OBJECT_CONSTRUCT('region', 'north', 'amount', 100);

Query failed — Snowflake returned an error

000002 (0A000): Unsupported feature 'assignment from non-constant source expression'.

Which is interesting, because a subquery was fine and ARRAY_CONSTRUCT is not. So the rule is not about complexity. It is one scalar value, from something Snowflake treats as constant.

Let's see what survived.

SHOW VARIABLES

SHOW VARIABLES LIKE 'THE\\_%';

Result8 rows · 7 columns · scroll →

session_id	created_on	updated_on	name	value	type
4761357795041302	2026-10-03 13:52:26.312000-07:00	2026-10-03 13:52:26.415000-07:00	THE_BIGGEST	900	fixed
4761357795041302	2026-10-03 13:52:21.483000-07:00	2026-10-03 13:52:21.528000-07:00	THE_CUTOFF	100	fixed
4761357795041302	2026-10-03 13:52:21.858000-07:00	2026-10-03 13:52:21.886000-07:00	THE_DAY	2026-01-15	date
4761357795041302	2026-10-03 13:52:22.419000-07:00	2026-10-03 13:52:22.504000-07:00	THE_HIGH	900	fixed
4761357795041302	2026-10-03 13:52:22.419000-07:00	2026-10-03 13:52:22.474000-07:00	THE_LOW	10	fixed
4761357795041302	2026-10-03 13:52:24.338000-07:00	2026-10-03 13:52:24.363000-07:00	THE_NEW_ID	7	fixed
4761357795041302	2026-10-03 13:52:22.017000-07:00	2026-10-03 13:52:22.040000-07:00	THE_NOTHING	NULL	text
4761357795041302	2026-10-03 13:52:21.738000-07:00	2026-10-03 13:52:21.775000-07:00	THE_REGION	north	text

Eight variables, and neither the array nor the object is among them. The refusals were refusals. Nothing was half set.

No lists

If you need a list in a variable, you need a table or a string you parse yourself. A session variable will not hold one.

Now the part everybody hits: a name is not a value

Let's put a table name in a variable.

Query

SET THE_TABLE = 'SALES_T140';

Result1 row · 1 column

status
Statement executed successfully.

And read it back

SELECT $THE_TABLE AS WHAT_IS_IN_THE_VARIABLE;

Result1 row · 1 column

WHAT_IS_IN_THE_VARIABLE
SALES_T140

It is a string. Ten characters spelling out the table's name. So let's select from it.

Query

SELECT COUNT(*) AS ROWS_IN_IT FROM $THE_TABLE;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 35 unexpected '$THE_TABLE'.

001003, a syntax error at the variable. Which is not Snowflake being awkward. The parser needs a table name in that position and we handed it a value.

The fix is a function whose entire job is this.

Query

SELECT COUNT(*) AS ROWS_IN_IT FROM IDENTIFIER($THE_TABLE);

Result1 row · 1 column

ROWS_IN_IT
7

Seven rows. IDENTIFIER takes the string and uses it as a name.

Where else does IDENTIFIER work?

Four places worth trying. First, an insert.

Query

INSERT INTO IDENTIFIER($THE_TABLE) (SALE_ID, REGION, AMOUNT)
VALUES (8, 'east', 8.00);

Result1 row · 1 column

number of rows inserted
1

And the count

SELECT COUNT(*) AS ROWS_IN_IT FROM IDENTIFIER($THE_TABLE);

Result1 row · 1 column

ROWS_IN_IT
8

Eight rows where there were seven. Yes.

Second, a CREATE TABLE.

A name

SET THE_NEW_TABLE = 'MADE_BY_VARIABLE_T140';

Result1 row · 1 column

status
Statement executed successfully.

The create

CREATE OR REPLACE TABLE IDENTIFIER($THE_NEW_TABLE) (
    NOTE VARCHAR(40)
);

Result1 row · 1 column

status
Table MADE_BY_VARIABLE_T140 successfully created.

And whether it exists

SELECT TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME = 'MADE_BY_VARIABLE_T140';

Result1 row · 2 columns

TABLE_NAME	TABLE_TYPE
MADE_BY_VARIABLE_T140	BASE TABLE

Created, and INFORMATION_SCHEMA agrees it is there as a base table.

Third, a column name.

A column name

SET THE_COLUMN = 'AMOUNT';

Result1 row · 1 column

status
Statement executed successfully.

The select

SELECT SALE_ID, IDENTIFIER($THE_COLUMN) AS THE_COLUMN_WE_ASKED_FOR
FROM SALES_T140
ORDER BY SALE_ID
LIMIT 3;

Result3 rows · 2 columns

SALE_ID	THE_COLUMN_WE_ASKED_FOR
1	100.00
2	250.50
3	75.25

Three rows of the table, and the column we asked for by name out of a variable. That query has a LIMIT 3 on it, which is why there are three.

Fourth, a fully qualified name.

A qualified name

SET THE_QUALIFIED = 'DEMO_DB.PUBLIC.SALES_T140';

Result1 row · 1 column

status
Statement executed successfully.

The select

SELECT COUNT(*) AS ROWS_IN_IT FROM IDENTIFIER($THE_QUALIFIED);

Result1 row · 1 column

ROWS_IN_IT
8

Eight rows again. Database, schema and table, all out of one variable.

And where IDENTIFIER does not help

Two things it will not do. First, a whole column list.

A list

SET THE_COLUMNS = 'SALE_ID, AMOUNT';

Result1 row · 1 column

status
Statement executed successfully.

The select

SELECT IDENTIFIER($THE_COLUMNS) FROM SALES_T140;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 18
invalid identifier '$THE_COLUMNS'

000904, invalid identifier. IDENTIFIER names one object. It does not expand into several.

Second, a keyword.

A keyword

SET THE_ORDER = 'DESC';

Result1 row · 1 column

status
Statement executed successfully.

The order by

SELECT SALE_ID FROM SALES_T140 ORDER BY SALE_ID $THE_ORDER;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 48 unexpected '$THE_ORDER'.

001003 again. A keyword is not a value either, and there is no IDENTIFIER for grammar.

Now the one that will catch somebody: a view that mentions a variable

This is the reason this episode comes early in the level. First, what the cutoff is right now.

Query

SELECT $THE_CUTOFF AS THE_CUTOFF;

Result1 row · 1 column

THE_CUTOFF
100

A hundred. Now let's create a view whose WHERE clause mentions that variable.

Query

CREATE OR REPLACE VIEW OVER_CUTOFF_T140 AS
SELECT SALE_ID, REGION, AMOUNT
FROM SALES_T140
WHERE AMOUNT > $THE_CUTOFF;

Result1 row · 1 column

status
View OVER_CUTOFF_T140 successfully created.

Created. No warning, no complaint, no note anywhere. Let's ask it a question.

Query

SELECT COUNT(*) AS ROWS_THE_VIEW_SHOWS FROM OVER_CUTOFF_T140;

Result1 row · 1 column

ROWS_THE_VIEW_SHOWS
3

Three rows. Now let's ask the catalog what it thinks the view actually is.

Query

SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND TABLE_NAME = 'OVER_CUTOFF_T140';

Result1 row · 2 columns

TABLE_NAME	VIEW_DEFINITION
OVER_CUTOFF_T140	CREATE OR REPLACE VIEW OVER_CUTOFF_T140 AS SELECT SALE_ID, REGION, AMOUNT FROM SALES_T140 WHERE AMOUNT > $THE_CUTOFF

Read the stored definition carefully. It contains the dollar sign. Not the number.

Snowflake stored the reference, not the value. So the view does not mean “amount over a hundred”. It means “amount over whatever THE_CUTOFF says when somebody runs me”.

Let's change the variable and ask the same view the same question.

Query

SET THE_CUTOFF = 300;

Result1 row · 1 column

status
Statement executed successfully.

The same question, to the same view

SELECT COUNT(*) AS ROWS_THE_VIEW_SHOWS FROM OVER_CUTOFF_T140;

Result1 row · 1 column

ROWS_THE_VIEW_SHOWS
2

Two rows where it said three, and we never touched the view or the table. The only thing that changed is the session.

So is the view wrong? Let's check it against a plain query with the new value.

Query

SELECT COUNT(*) AS ROWS_A_PLAIN_QUERY_SHOWS
FROM SALES_T140
WHERE AMOUNT > $THE_CUTOFF;

Result1 row · 1 column

ROWS_A_PLAIN_QUERY_SHOWS
2

Two, the same. The view is not wrong. It is just not fixed.

What was shown, and what follows from it

One thing to be straight about. The grids above are one session changing its own variable. A second session and a second user were not part of this run, so nothing here measures what you would see sitting beside me.
What the grids do establish is the mechanism, and it is the stored definition that makes the consequence plain: the view keeps $THE_CUTOFF, and $THE_CUTOFF is resolved when the view is read. A view like this has no one answer, and nothing in the catalog warns you.

Names, unsetting and where a variable lives

Let's set one in lower case.

Query

SET lower_name = 'set in lower case';

Result1 row · 1 column

status
Statement executed successfully.

Read in upper case

SELECT $LOWER_NAME AS READ_IN_UPPER_CASE;

Result1 row · 1 column

READ_IN_UPPER_CASE
set in lower case

It works. And the catalog agrees with the upper case too.

SHOW VARIABLES

SHOW VARIABLES LIKE '%NAME%';

Result1 row · 7 columns · scroll →

session_id	created_on	updated_on	name	value	type	comment
4761357795041302	2026-10-03 13:52:32.958000-07:00	2026-10-03 13:52:32.994000-07:00	LOWER_NAME	set in lower case	text

LOWER_NAME. An unquoted name folds to upper case, like every other unquoted identifier in Snowflake.

So what about a quoted name, with a space in it?

Query

SET "Mixed Case" = 'quoted name';

Result1 row · 1 column

status
Statement executed successfully.

Accepted. Snowflake made the variable. Now let's read it back.

Query

SELECT $"Mixed Case" AS READ_BACK_QUOTED;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 1 at position 7 unexpected '$'.

A syntax error at the dollar sign. So you can create a session variable that you cannot read. I did not expect that one.

Now unsetting

Let's unset the NULL one and then read it.

Unset it

UNSET THE_NOTHING;

Result1 row · 1 column

status
Statement executed successfully.

And read it

SELECT $THE_NOTHING AS THE_NOTHING;

Query failed — Snowflake returned an error

002211 (02000): SQL compilation error: error line 1 at position 7
Session variable '$THE_NOTHING' does not exist

002211, the session variable does not exist. Clear enough. Now let's unset the same one again.

Query

UNSET THE_NOTHING;

Result1 row · 1 column

status
Statement executed successfully.

No error at all. And one that never existed?

Query

UNSET NEVER_EXISTED_T140;

Result1 row · 1 column

status
Statement executed successfully.

Also fine. But reading one that was never set is not.

Query

SELECT $NEVER_EXISTED_T140 AS NEVER_SET;

Query failed — Snowflake returned an error

002211 (02000): SQL compilation error: error line 1 at position 7
Session variable '$NEVER_EXISTED_T140' does not exist

So UNSET is forgiving and reading is not. Worth knowing before you write a cleanup block.

And where a variable lives

There is a function form as well as the dollar sign.

Query

SELECT GETVARIABLE('THE_REGION') AS THE_REGION;

Result1 row · 1 column

THE_REGION
north

Now let's change schema and try to read a variable from the other one.

Change schema

USE SCHEMA INFORMATION_SCHEMA;

Result1 row · 1 column

status
Statement executed successfully.

And read it

SELECT $THE_REGION AS STILL_THERE, CURRENT_SCHEMA() AS WHERE_WE_ARE;

Result1 row · 2 columns

STILL_THERE	WHERE_WE_ARE
north	INFORMATION_SCHEMA

Still there, from a different schema. A session variable belongs to the session. It does not belong to a database or a schema.

One last thing: the teardown

Back to PUBLIC

USE SCHEMA PUBLIC;

Result1 row · 1 column

status
Statement executed successfully.

Teardown

DROP VIEW IF EXISTS OVER_CUTOFF_T140;

Result1 row · 1 column

status
OVER_CUTOFF_T140 successfully dropped.

Teardown

DROP TABLE IF EXISTS MADE_BY_VARIABLE_T140;

Result1 row · 1 column

status
MADE_BY_VARIABLE_T140 successfully dropped.

Teardown

DROP TABLE IF EXISTS SALES_T140;

Result1 row · 1 column

status
SALES_T140 successfully dropped.

Then the variables, one UNSET each. Including the two that were never created, because UNSET does not mind.

Teardown

UNSET THE_CUTOFF;

Result1 row · 1 column

status
Statement executed successfully.

Teardown, for one that was never set

UNSET THE_LIST;

Result1 row · 1 column

status
Statement executed successfully.

Teardown, for the one we could not read

UNSET "Mixed Case";

Result1 row · 1 column

status
Statement executed successfully.

That last one is worth a smile. We could never read it, and UNSET removed it without any trouble at all.

And the checks.

Where we are

SELECT CURRENT_DATABASE() AS THE_DATABASE,
       CURRENT_SCHEMA()   AS THE_SCHEMA;

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

And what is left

SHOW VARIABLES;

Result0 rows · 7 columns · scroll →

session_id	created_on	updated_on	name	value	type	comment

And the tables

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC'
  AND ENDSWITH(TABLE_NAME, '_T140');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

Back in PUBLIC, no variables left in the session and no tables of ours left behind. Back where we started.

Key points

SET THE_CUTOFF = 100 stores a value. $THE_CUTOFF reads it back. That is the whole mechanism.
The type is inferred from the literal and it is narrow. 100 gives you three digits, and 'north' gives you a varchar exactly as wide as the word.
SET (A, B) = (x, y) sets several in one statement.
A variable goes anywhere a literal goes: a WHERE, a select list, an expression, a LIMIT, an INSERT, an UPDATE.
A scalar subquery can set one. A subquery returning several rows is 090150, and an array or an object is 000002.
FROM $THE_TABLE is a syntax error, because a name is not a value. IDENTIFIER($THE_TABLE) is the fix.
IDENTIFIER works for a table, a column, a CREATE and a fully qualified name.
IDENTIFIER names one object, not a column list, and there is no equivalent for a keyword.
A view whose WHERE clause mentions a variable stores the reference. The catalog holds $THE_CUTOFF, not the number.
So that view has no single answer. Change the variable and the same view answers differently, and nothing in the catalog warns you.
An unquoted variable name folds to upper case. A quoted name can be set and then cannot be read at all.
UNSET is forgiving twice over, and reading something unset is 002211.
A session variable survives USE SCHEMA, because it belongs to the session.

Next in this series

Episode 141 — scalar SQL UDFs. Three lines of SQL with a name on them, what the catalog keeps of one, and what the RETURNS clause turns out not to do.

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 view section is one session changing its own variable, and the post says so where it says it. The last three grids show the session back in PUBLIC with no variables and no tables of its own left behind. 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 SET documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 140 - A View That Returns Different Rows Depending On Who Is Looking

Let's create some sample data

Let's set a variable and read it back

Now let's set a string, a date and a NULL

Can we set several at once?

Where can a variable go? Anywhere a literal can

And in an INSERT and an UPDATE

Where can the value come from?

Now the part everybody hits: a name is not a value

Where else does IDENTIFIER work?

And where IDENTIFIER does not help

Now the one that will catch somebody: a view that mentions a variable

Names, unsetting and where a variable lives

Now unsetting

And where a variable lives

One last thing: the teardown

Key points

No comments:

Post a Comment