Welcome To TechBrothersIT: Snowflake SQL Tutorial 170 - The Same COUNT Gave Me 76 And 64, And That Is The Whole Point

Snowflake SQL Tutorial 170 - The Same COUNT Gave Me 76 And 64, And That Is The Whole Point

Snowflake SQL · Episode 170

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

INFORMATION_SCHEMA is a schema that exists inside every database, and it holds views that describe that database: its schemas, its tables, its columns, its privileges. It is where you look when you want to ask a question about your objects rather than about your data.

The one thing to get clear early is that it is not a catalogue of the account. It is a set of views, scoped to one database, and the rows you get back depend on who is asking.

We will start with what is actually in there and build it up step by step. Along the way we will write a view name that has never existed, and we will run one COUNT twice and get two different answers.

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

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

On this page

Let's create a schema and a table to work with
Let's start with what is actually in there
Now the one I got wrong, three files running
But the scope rule is a default, not a law
What 002003 will not tell you
Another prediction of mine that was wrong
Now the real point: the same COUNT, two roles
And it disagrees with the SHOW that asks the same thing
Let's see how quickly it notices a change
And how quickly it forgets one
Can we write to it?
One last thing: three views for three questions
And the clean-up

Let's create a schema and a table to work with

INFORMATION_SCHEMA describes objects, so we need an object for it to describe. The script below makes a schema and a table with six columns of different types in it.

Sample data

-- Sample data for this post.
-- A schema and a table with a few different column types in it, so there is
-- something for INFORMATION_SCHEMA to describe.
USE DATABASE DEMO_DB;

CREATE OR REPLACE SCHEMA DEMO_DB.ISCHEMA_T170
    COMMENT = 'episode 170 - information schema, dropped at the end';

USE SCHEMA DEMO_DB.ISCHEMA_T170;

CREATE OR REPLACE TABLE DEMO_DB.ISCHEMA_T170.THINGS_T170 (
    THING_ID    NUMBER(10,0) NOT NULL,
    THING_NAME  VARCHAR(50),
    PRICE       NUMBER(12,4),
    MADE_ON     DATE,
    DETAILS     VARIANT,
    IS_ACTIVE   BOOLEAN DEFAULT TRUE
);

Now let's look at that table, not by selecting from it, but by asking INFORMATION_SCHEMA what it is made of.

Query

SELECT COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE,
       NUMERIC_PRECISION, NUMERIC_SCALE, CHARACTER_MAXIMUM_LENGTH,
       IS_NULLABLE, COLUMN_DEFAULT
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170'
   AND TABLE_NAME   = 'THINGS_T170'
 ORDER BY ORDINAL_POSITION;

Result6 rows · 8 columns · scroll →

COLUMN_NAME	ORDINAL_POSITION	DATA_TYPE	NUMERIC_PRECISION	NUMERIC_SCALE	CHARACTER_MAXIMUM_LENGTH	IS_NULLABLE	COLUMN_DEFAULT
THING_ID	1	NUMBER	10	0	NULL	NO	NULL
THING_NAME	2	TEXT	NULL	NULL	50	YES	NULL
PRICE	3	NUMBER	12	4	NULL	YES	NULL
MADE_ON	4	DATE	NULL	NULL	NULL	YES	NULL
DETAILS	5	VARIANT	NULL	NULL	NULL	YES	NULL
IS_ACTIVE	6	BOOLEAN	NULL	NULL	NULL	YES	TRUE

Query

SELECT COUNT(*) AS COLUMNS_REPORTED
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170'
   AND TABLE_NAME   = 'THINGS_T170';

Result1 row · 1 column

COLUMNS_REPORTED
6

Six columns, each with its type, its position, its precision and scale where it has them, whether it accepts NULL and what its default is.

The NULLs in that grid are real NULLs. A DATE column has no numeric precision, so the view says so with a NULL rather than with a blank. That is worth noticing now, because later on we will read the same sort of information out of a SHOW statement, where a missing value comes back as an empty string instead.

Let's start with what is actually in there

First, how many views INFORMATION_SCHEMA actually has. SHOW VIEWS will tell us.

Query

SHOW VIEWS IN SCHEMA DEMO_DB.INFORMATION_SCHEMA;

Query

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

Result1 row · 1 column

VIEWS_IN_INFORMATION_SCHEMA
63

Sixty three views, in one database. Let's print the first few names.

Query

SELECT "name", "is_secure", "kind"
  FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
 ORDER BY "name"
 LIMIT 12;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 28
invalid identifier '"kind"'

That is my own mistake and it is staying on the page. SHOW VIEWS has no column called kind, so asking for it is an invalid identifier.

Let's ask the same question a completely different way, through the VIEWS view instead of through a SHOW.

Query

SELECT TABLE_NAME, TABLE_TYPE
  FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
 ORDER BY TABLE_NAME
 LIMIT 12;

Query failed — Snowflake returned an error

000904 (42000): SQL compilation error: error line 1 at position 19
invalid identifier 'TABLE_TYPE'

Wrong again, and for a different reason. The VIEWS view does not have a TABLE_TYPE column. The TABLES view does, which is where I had read it.

Let's drop the column I guessed at and just count.

Query

SELECT COUNT(*) AS VIEWS_THE_VIEWS_VIEW_KNOWS
  FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA';

Result1 row · 1 column

VIEWS_THE_VIEWS_VIEW_KNOWS
63

The same number as the SHOW gave us. Let's put both counts in one row and be sure.

Query

SELECT (SELECT COUNT(*)
          FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
         WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA') AS VIA_THE_VIEWS_VIEW,
       (SELECT COUNT(*)
          FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
         WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA') AS VIA_THE_TABLES_VIEW;

Result1 row · 2 columns

VIA_THE_VIEWS_VIEW	VIA_THE_TABLES_VIEW
63	63

Sixty three either way, and the TABLES view agrees with the VIEWS view. That is worth checking rather than assuming, and it is the last thing in this post that two statements agree about.

Now the one I got wrong, three files running

Here is a confession. This is a statement I wrote in three separate query files for this course, four blocks in all, and every one of them failed.

Query

SELECT COUNT(*) AS WAREHOUSES
  FROM DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES' does not exist or not authorized. Your primary role ACCOUNTADMIN must have at least one privilege granted on TABLE DEMO_DB.INFORMATION_SCHEMA.WAREHOUSES.

002003. Only one of those four blocks is in this episode's capture file, which is the pane above. The other three were in other episodes' files, so their refusals are not on this page.

Let's finally ask the question properly. Is there anything at all with WAREHOUSE in its name in there?

Query

SELECT TABLE_NAME, TABLE_TYPE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
   AND TABLE_NAME LIKE '%WAREHOUSE%'
 ORDER BY TABLE_NAME;

Result0 rows · 2 columns

TABLE_NAME	TABLE_TYPE

Query

SELECT COUNT(*) AS THINGS_NAMED_WAREHOUSE_SOMETHING
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
   AND TABLE_NAME LIKE '%WAREHOUSE%';

Result1 row · 1 column

THINGS_NAMED_WAREHOUSE_SOMETHING
0

Nought rows. It is not a spelling mistake. There is no such view and there never was.

A warehouse is an account object. It does not belong to any database, so there is no database's INFORMATION_SCHEMA for it to live in.

But the scope rule is a default, not a law

That explanation is correct and it is also not a good enough excuse, because some of the views in there are not database-scoped at all.

Here is the first one.

Query

SELECT DATABASE_NAME, DATABASE_OWNER, IS_TRANSIENT
  FROM DEMO_DB.INFORMATION_SCHEMA.DATABASES
 ORDER BY DATABASE_NAME;

Result4 rows · 3 columns · showing first 3

Four rows. The fourth is a personal database named after the user, so it is left off this page.

DATABASE_NAME	DATABASE_OWNER	IS_TRANSIENT
DEMO_DB	ACCOUNTADMIN	NO
SNOWFLAKE_LEARNING_DB	ACCOUNTADMIN	NO
SNOWFLAKE_SAMPLE_DATA	ACCOUNTADMIN	NO

Query

SELECT COUNT(*) AS DATABASES_IT_LISTS
  FROM DEMO_DB.INFORMATION_SCHEMA.DATABASES;

Result1 row · 1 column

DATABASES_IT_LISTS
4

Every database on the account, listed inside one database's INFORMATION_SCHEMA. Nothing about that view is specific to DEMO_DB.

Here is a second one. APPLICABLE_ROLES lists the roles that can be applied to the current user.

Query

SELECT GRANTEE, ROLE_NAME, IS_GRANTABLE
FROM DEMO_DB.INFORMATION_SCHEMA.APPLICABLE_ROLES
ORDER BY ROLE_NAME
LIMIT 10;

That grid is not on this page. Its GRANTEE column is my user name on every row, and the builder here cannot drop one column out of a grid, so the whole grid goes rather than the name. The count is the part that matters.

Query

SELECT COUNT(*) AS APPLICABLE_ROLES_ROWS
  FROM DEMO_DB.INFORMATION_SCHEMA.APPLICABLE_ROLES;

Result1 row · 1 column

APPLICABLE_ROLES_ROWS
8

Eight rows, and again nothing about it is database specific. And a third view asks a related but different question: which roles are actually active in this session?

Query

SELECT ROLE_NAME
  FROM DEMO_DB.INFORMATION_SCHEMA.ENABLED_ROLES
 ORDER BY ROLE_NAME;

Result6 rows · 1 column

ROLE_NAME
ACCOUNTADMIN
PUBLIC
SECURITYADMIN
SNOWFLAKE_LEARNING_ROLE
SYSADMIN
USERADMIN

Query

SELECT COUNT(*) AS ENABLED_ROLES_ROWS
  FROM DEMO_DB.INFORMATION_SCHEMA.ENABLED_ROLES;

Result1 row · 1 column

ENABLED_ROLES_ROWS
6

So three account-scoped views, sitting inside a database-scoped schema. If every view in there were scoped to one database, WAREHOUSES would have been an obvious mistake. It is not obvious, and that is worth saying rather than pretending otherwise.

What 002003 will not tell you

Now the error code itself, because it does more work than it looks like.

QUERY_HISTORY lives in INFORMATION_SCHEMA and it is not a view. It is a table function, so it has to be called inside TABLE() with its arguments.

Query

SELECT COUNT(*) AS ROWS_THE_FUNCTION_RETURNED
  FROM TABLE(DEMO_DB.INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 10));

Result1 row · 1 column

ROWS_THE_FUNCTION_RETURNED
10

Ten rows, because we asked for ten. Here is what it hands back.

Query

SELECT QUERY_TYPE, EXECUTION_STATUS, WAREHOUSE_NAME, ROLE_NAME
  FROM TABLE(DEMO_DB.INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 5))
 ORDER BY QUERY_TYPE;

Result5 rows · 4 columns

QUERY_TYPE	EXECUTION_STATUS	WAREHOUSE_NAME	ROLE_NAME
SELECT	RUNNING	COMPUTE_WH	ACCOUNTADMIN
SELECT	FAILED_WITH_ERROR	COMPUTE_WH	ACCOUNTADMIN
SELECT	SUCCESS	COMPUTE_WH	ACCOUNTADMIN
SELECT	SUCCESS	COMPUTE_WH	ACCOUNTADMIN
SHOW	SUCCESS	COMPUTE_WH	ACCOUNTADMIN

Query type, execution status, warehouse and role. Real plumbing, and one of the rows is the query that was running when we asked.

Now let's use that same name as though it were a view. No TABLE(), no arguments.

Query

SELECT COUNT(*) AS THIS_WILL_NOT_WORK
  FROM DEMO_DB.INFORMATION_SCHEMA.QUERY_HISTORY;

Query failed — Snowflake returned an error

002003 (42S02): SQL compilation error:
Object 'DEMO_DB.INFORMATION_SCHEMA.QUERY_HISTORY' does not exist or not authorized. Your primary role ACCOUNTADMIN must have at least one privilege granted on TABLE DEMO_DB.INFORMATION_SCHEMA.QUERY_HISTORY.

002003. The identical code the missing WAREHOUSES view gave us a moment ago. But this object exists. We just used it wrong, and the error cannot tell those two apart.

Read the sentence in it: does not exist or not authorized. That is two answers in one, and a real object you simply have no privilege on is a third.

One code, three problems

So when you see 002003, the first question is not “what did I misspell”. It is one code for three different problems:
the object does not exist,
the object exists and you used it in the wrong shape,
the object exists and you have no privilege on it.
The message cannot tell you which, and it will not try.

Another prediction of mine that was wrong

I marked this next statement as one I expected to be refused, on the grounds that the table functions in there are not ordinary functions.

Query

SHOW FUNCTIONS IN SCHEMA DEMO_DB.INFORMATION_SCHEMA;

Result1146 rows · 21 columns · showing first 4 · scroll →

The first four rows of it.

created_on	name	is_builtin	is_aggregate	is_ansi	min_num_arguments	max_num_arguments	arguments	description	is_table_function	valid_for_clustering	is_secure	is_external_function	language	is_memoizable	is_data_metric	is_ai_function
2012-08-01 00:00:00-07:00	!=	Y	N	Y	2	2	!=(ANY, ANY) RETURN BOOLEAN, !=(ROW(ANY), ROW(ANY)) RETURN BOOLEAN	Checks if the first value is not equal to the second value	N	Y	NULL	N	SQL	N	N	N
2012-08-01 00:00:00-07:00	%	Y	N	N	2	2	%(NUMBER, NUMBER) RETURN NUMBER, %(FLOAT, FLOAT) RETURN FLOAT, %(DECFLOAT, DECFLOAT) RETURN DECFLOAT	Adds two numbers	N	Y	NULL	N	SQL	N	N	N
2012-08-01 00:00:00-07:00	*	Y	N	N	2	-1	(NUMBER, NUMBER) RETURN NUMBER, (FLOAT, FLOAT) RETURN FLOAT, *(DECFLOAT, DECFLOAT) RETURN DECFLOAT	Multiplies two numbers	N	Y	NULL	N	SQL	N	N	N
2012-08-01 00:00:00-07:00	+	Y	N	Y	2	-1	+(NUMBER, NUMBER) RETURN NUMBER, +(FLOAT, FLOAT) RETURN FLOAT, +(DECFLOAT, DECFLOAT) RETURN DECFLOAT	Adds two numbers	N	Y	NULL	N	SQL	N	N	N

Query

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

Result1 row · 1 column

FUNCTIONS_SHOW_REPORTED
1146

One thousand one hundred and forty six rows, and the first four are !=, %, * and +. It is not listing that schema's table functions at all. It is handing back every built-in function in Snowflake, operators included.

Now the real point: the same COUNT, two roles

This is the statement that shows what INFORMATION_SCHEMA actually is. We are going to run one COUNT, twice, without changing a character of it.

First, as ACCOUNTADMIN.

Query

SELECT COUNT(*) AS TABLES_ACCOUNTADMIN_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES;

Result1 row · 1 column

TABLES_ACCOUNTADMIN_CAN_SEE
76

Query

SELECT COUNT(*) AS SCHEMAS_ACCOUNTADMIN_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA;

Result1 row · 1 column

SCHEMAS_ACCOUNTADMIN_CAN_SEE
3

Seventy six tables and views across the database, in three schemas. Hold both of those numbers.

Now let's make a role that can see one schema and exactly one table inside it.

Setup

-- A role that can see ONE schema and exactly ONE table inside it.
CREATE OR REPLACE ROLE RISCH_T170
    COMMENT = 'episode 170 - one table, and INFORMATION_SCHEMA will prove it';

GRANT USAGE  ON DATABASE DEMO_DB                      TO ROLE RISCH_T170;
GRANT USAGE  ON SCHEMA   DEMO_DB.ISCHEMA_T170         TO ROLE RISCH_T170;
GRANT SELECT ON TABLE    DEMO_DB.ISCHEMA_T170.THINGS_T170 TO ROLE RISCH_T170;
GRANT USAGE  ON WAREHOUSE COMPUTE_WH                  TO ROLE RISCH_T170;

-- Your own user name goes here. Mine is not on this page.
GRANT ROLE RISCH_T170 TO USER YOUR_USER_NAME;

Before we wear it, secondary roles have to be off. Otherwise USE ROLE proves nothing, because the session would still be carrying ACCOUNTADMIN alongside the role we are testing, and this whole comparison would be ACCOUNTADMIN against ACCOUNTADMIN.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"ORGADMIN","value":"ALL"}

Query

USE SECONDARY ROLES NONE;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"","value":""}

Empty, so they are off. Now the role goes on, and we check again from inside.

Query

USE ROLE RISCH_T170;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
RISCH_T170	{"roles":"","value":""}

Now the identical count. Not a different query. The same text.

Query

SELECT COUNT(*) AS TABLES_THIS_ROLE_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES;

Result1 row · 1 column

TABLES_THIS_ROLE_CAN_SEE
64

Sixty four. Nothing about the statement changed and the answer dropped by twelve. Those sixty four are the sixty three views of INFORMATION_SCHEMA plus the one table this role was granted SELECT on.

And the schemas.

Query

SELECT SCHEMA_NAME
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA
 ORDER BY SCHEMA_NAME;

Result2 rows · 1 column

SCHEMA_NAME
INFORMATION_SCHEMA
ISCHEMA_T170

Query

SELECT COUNT(*) AS SCHEMAS_THIS_ROLE_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA;

Result1 row · 1 column

SCHEMAS_THIS_ROLE_CAN_SEE
2

Two instead of three. It cannot see the schema it has no USAGE on, so that schema is not in the view at all.

So the rows are filtered by what the asking role can see. A catalogue would not do that. A catalogue would list what exists and let the privileges stop you reading it.

It is not blind, though. Let's ask it about the columns of the one table this role can read.

Query

SELECT COLUMN_NAME, DATA_TYPE
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'THINGS_T170'
 ORDER BY ORDINAL_POSITION;

Result6 rows · 2 columns

COLUMN_NAME	DATA_TYPE
THING_ID	NUMBER
THING_NAME	TEXT
PRICE	NUMBER
MADE_ON	DATE
DETAILS	VARIANT
IS_ACTIVE	BOOLEAN

Query

SELECT COUNT(*) AS COLUMNS_THIS_ROLE_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'THINGS_T170';

Result1 row · 1 column

COLUMNS_THIS_ROLE_CAN_SEE
6

All six columns, with their types. Full detail on the one object it is allowed to see.

And the two account-scoped views answer differently too, each in its own way.

Query

SELECT ROLE_NAME
  FROM DEMO_DB.INFORMATION_SCHEMA.ENABLED_ROLES
 ORDER BY ROLE_NAME;

Result3 rows · 1 column

ROLE_NAME
PUBLIC
RISCH_T170
SNOWFLAKE_LEARNING_ROLE

Query

SELECT COUNT(*) AS DATABASES_THIS_ROLE_CAN_SEE
  FROM DEMO_DB.INFORMATION_SCHEMA.DATABASES;

Result1 row · 1 column

DATABASES_THIS_ROLE_CAN_SEE
4

ENABLED_ROLES gives three rows for this role where it gave six for ACCOUNTADMIN, because the question it answers is about the session. DATABASES gives the same four as before, because that one is not filtered this way at all.

Now back to ACCOUNTADMIN, and let's run the count a third time to be sure nothing else moved.

Query

USE ROLE ACCOUNTADMIN;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"","value":""}

Query

SELECT COUNT(*) AS TABLES_ACCOUNTADMIN_CAN_SEE_AGAIN
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES;

Result1 row · 1 column

TABLES_ACCOUNTADMIN_CAN_SEE_AGAIN
76

Seventy six again. One statement, two roles, two answers, and both of them true.

And it disagrees with the SHOW that asks the same thing

Something follows from all of that, and it caught me out in the last post.

In Episode 169 we counted the schemas in the SNOWFLAKE database with SHOW SCHEMAS IN DATABASE SNOWFLAKE and got 35. That grid is in that post and the number is correct.

Now let's ask the same question through that database's own INFORMATION_SCHEMA.

Query

SELECT CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER
  FROM SNOWFLAKE.INFORMATION_SCHEMA.SCHEMATA
 ORDER BY SCHEMA_NAME;

Result20 rows · 3 columns · showing first 6

The first six, alphabetically.

CATALOG_NAME	SCHEMA_NAME	SCHEMA_OWNER
SNOWFLAKE	ACCOUNT_USAGE	NULL
SNOWFLAKE	ALERT	NULL
SNOWFLAKE	BCR_ROLLOUT	NULL
SNOWFLAKE	BILLING	NULL
SNOWFLAKE	CORE	NULL
SNOWFLAKE	CORTEX	NULL

Query

SELECT COUNT(*) AS SCHEMAS_IN_SNOWFLAKE
  FROM SNOWFLAKE.INFORMATION_SCHEMA.SCHEMATA;

Result1 row · 1 column

SCHEMAS_IN_SNOWFLAKE
20

Twenty. Same database, same question, two statements, two different answers.

Neither of them is wrong. SHOW asks the engine what is there. INFORMATION_SCHEMA asks a view, and a view is filtered.

Notice also that SCHEMA_OWNER comes back as a real NULL on those rows, where the SHOW version of the same fact in Episode 169 was an empty string.

Now let's check something about scope. Does each database's INFORMATION_SCHEMA ever report another database?

Query

SELECT (SELECT COUNT(DISTINCT CATALOG_NAME)
          FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA)   AS CATALOGS_DEMO_DB_REPORTS,
       (SELECT COUNT(DISTINCT CATALOG_NAME)
          FROM SNOWFLAKE.INFORMATION_SCHEMA.SCHEMATA) AS CATALOGS_SNOWFLAKE_REPORTS;

Result1 row · 2 columns

CATALOGS_DEMO_DB_REPORTS	CATALOGS_SNOWFLAKE_REPORTS
1	1

One distinct catalogue name in each. Every database's INFORMATION_SCHEMA only ever reports its own catalogue. That is why there is one inside every database, and why they are not interchangeable.

And written with no database in front of it, it resolves against whichever database the session is pointing at.

Query

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

Result1 row · 2 columns

THE_DATABASE	THE_SCHEMA
DEMO_DB	PUBLIC

Query

SELECT COUNT(*) AS SCHEMAS_AN_UNQUALIFIED_QUERY_SEES
  FROM INFORMATION_SCHEMA.SCHEMATA;

Result1 row · 1 column

This one was run before the schema we made at the top of the post existed, which is why it reports one fewer than the count further up.

SCHEMAS_AN_UNQUALIFIED_QUERY_SEES
2

Let's see how quickly it notices a change

Now the property that makes this thing different from the view we look at in the next post. Let's go back to the schema we created at the top, and ask about it in the very next statement after creating it.

Query

SELECT SCHEMA_NAME, SCHEMA_OWNER, CREATED
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'ISCHEMA_T170';

Result1 row · 3 columns

SCHEMA_NAME	SCHEMA_OWNER	CREATED
ISCHEMA_T170	ACCOUNTADMIN	2026-10-07 12:43:41.513000-07:00

It is there, with its owner and the exact second it was created. No wait at all.

Same for the table.

Query

SELECT TABLE_NAME, TABLE_TYPE, ROW_COUNT, CREATED
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170';

Result1 row · 4 columns

TABLE_NAME	TABLE_TYPE	ROW_COUNT	CREATED
THINGS_T170	BASE TABLE	0	2026-10-07 12:43:42.328000-07:00

ROW_COUNT reads 0, because nothing is in it yet. Let's put three rows in.

Query

INSERT INTO DEMO_DB.ISCHEMA_T170.THINGS_T170
       (THING_ID, THING_NAME, PRICE, MADE_ON, DETAILS)
SELECT 1, 'brass hinge',  4.7500, '2026-01-04'::DATE, PARSE_JSON('{"box":12}')
 UNION ALL
SELECT 2, 'oak dowel',    1.2000, '2026-02-11'::DATE, PARSE_JSON('{"box":50}')
 UNION ALL
SELECT 3, 'steel bracket',8.9500, '2026-03-02'::DATE, PARSE_JSON('{"box":4}');

Result1 row · 1 column

number of rows inserted
3

Query

SELECT TABLE_NAME, ROW_COUNT, BYTES, LAST_ALTERED
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170';

Result1 row · 4 columns

TABLE_NAME	ROW_COUNT	BYTES	LAST_ALTERED
THINGS_T170	3	1536	2026-10-07 12:43:46.280000-07:00

ROW_COUNT is three and BYTES is 1536. Let's check that row count against the table itself.

Query

SELECT COUNT(*) AS ROWS_IN_THE_TABLE
  FROM DEMO_DB.ISCHEMA_T170.THINGS_T170;

Result1 row · 1 column

ROWS_IN_THE_TABLE
3

They agree. So ROW_COUNT here is not an estimate. It matched a real count on the table, taken seconds later.

Let's add two more objects of different kinds and see whether it keeps up.

Query

CREATE OR REPLACE VIEW DEMO_DB.ISCHEMA_T170.CHEAP_T170 AS
SELECT THING_ID, THING_NAME, PRICE
  FROM DEMO_DB.ISCHEMA_T170.THINGS_T170
 WHERE PRICE < 5;

Result1 row · 1 column

status
View CHEAP_T170 successfully created.

Query

CREATE OR REPLACE SEQUENCE DEMO_DB.ISCHEMA_T170.SEQ_T170 START = 1 INCREMENT = 1;

Result1 row · 1 column

status
Sequence SEQ_T170 successfully created.

Query

SELECT TABLE_NAME, TABLE_TYPE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170'
 ORDER BY TABLE_NAME;

Result2 rows · 2 columns

TABLE_NAME	TABLE_TYPE
CHEAP_T170	VIEW
THINGS_T170	BASE TABLE

The view and the table, with TABLE_TYPE telling them apart. The sequence is not in there, because sequences have a view of their own. Let's ask it.

Query

SELECT SEQUENCE_NAME, SEQUENCE_SCHEMA, START_VALUE, INCREMENT, NEXT_VALUE
  FROM DEMO_DB.INFORMATION_SCHEMA.SEQUENCES
 WHERE SEQUENCE_SCHEMA = 'ISCHEMA_T170';

Query failed — Snowflake returned an error

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

A syntax error, and another one of mine. INCREMENT is a keyword, so it cannot sit bare in a select list like that. The view exists and the query around it was wrong.

The VIEWS view, though, carries something useful.

Query

SELECT TABLE_NAME, IS_SECURE, VIEW_DEFINITION
  FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170';

Result1 row · 3 columns

TABLE_NAME	IS_SECURE	VIEW_DEFINITION
CHEAP_T170	NO	CREATE OR REPLACE VIEW DEMO_DB.ISCHEMA_T170.CHEAP_T170 AS SELECT THING_ID, THING_NAME, PRICE FROM DEMO_DB.ISCHEMA_T170.THINGS_T170 WHERE PRICE < 5

The definition text of the view we just created, read back out of a view.

And how quickly it forgets one

Let's make a table that exists only to be dropped.

Query

CREATE OR REPLACE TABLE DEMO_DB.ISCHEMA_T170.DOOMED_T170 (ID NUMBER);

Result1 row · 1 column

status
Table DOOMED_T170 successfully created.

Query

SELECT TABLE_NAME, TABLE_TYPE, CREATED
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170'
 ORDER BY TABLE_NAME;

Result3 rows · 3 columns

TABLE_NAME	TABLE_TYPE	CREATED
CHEAP_T170	VIEW	2026-10-07 12:43:47.270000-07:00
DOOMED_T170	BASE TABLE	2026-10-07 12:43:50.609000-07:00
THINGS_T170	BASE TABLE	2026-10-07 12:43:42.328000-07:00

Query

SELECT COUNT(*) AS TABLES_BEFORE_THE_DROP
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170';

Result1 row · 1 column

TABLES_BEFORE_THE_DROP
3

Three objects in that schema. Now let's drop one of them.

Query

DROP TABLE DEMO_DB.ISCHEMA_T170.DOOMED_T170;

Result1 row · 1 column

status
DOOMED_T170 successfully dropped.

Query

SELECT TABLE_NAME, TABLE_TYPE, CREATED
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170'
 ORDER BY TABLE_NAME;

Result2 rows · 3 columns

TABLE_NAME	TABLE_TYPE	CREATED
CHEAP_T170	VIEW	2026-10-07 12:43:47.270000-07:00
THINGS_T170	BASE TABLE	2026-10-07 12:43:42.328000-07:00

Query

SELECT COUNT(*) AS TABLES_AFTER_THE_DROP
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_SCHEMA = 'ISCHEMA_T170';

Result1 row · 1 column

TABLES_AFTER_THE_DROP
2

Two, in the statement straight after the drop. And is there any trace of the dropped one left anywhere in that view?

Query

SELECT COUNT(*) AS MENTIONS_OF_THE_DROPPED_TABLE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME = 'DOOMED_T170';

Result1 row · 1 column

MENTIONS_OF_THE_DROPPED_TABLE
0

None. Not a row marked deleted, not a tombstone. Gone.

Let's check whether there is even a column it could record that in.

Query

SELECT COLUMN_NAME
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
   AND TABLE_NAME   = 'TABLES'
 ORDER BY ORDINAL_POSITION;

Result32 rows · 1 column · showing first 8

The first eight of its columns, in order.

COLUMN_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_OWNER
TABLE_TYPE
IS_TRANSIENT
CLUSTERING_KEY
ROW_COUNT

Query

SELECT COUNT(*) AS COLUMNS_IN_THE_TABLES_VIEW
  FROM DEMO_DB.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA'
   AND TABLE_NAME   = 'TABLES';

Result1 row · 1 column

COLUMNS_IN_THE_TABLES_VIEW
32

Thirty two columns on the TABLES view, and not one of them is a deleted flag. There is nowhere for a dropped table to be recorded, which is the whole design.

A picture, not a history

Compare that with the other metadata source we met in Episode 169, where ACCOUNT_USAGE.DATABASES still lists databases this course destroyed many episodes ago. Neither is wrong. One is a picture of now and the other is a history, and both exist for exactly that reason. Episode 171 is about the second one.

Can we write to it?

It is a set of views, so the answer should be no. Let's check rather than assume.

Query

INSERT INTO DEMO_DB.INFORMATION_SCHEMA.TABLES (TABLE_NAME) VALUES ('NOPE_T170');

Query failed — Snowflake returned an error

003001 (42501): SQL access control error:
Insufficient privileges to operate on view 'TABLES'.

003001, insufficient privileges to operate on the view.

Query

DROP VIEW DEMO_DB.INFORMATION_SCHEMA.TABLES;

Query failed — Snowflake returned an error

003001 (42501): SQL access control error:
Insufficient privileges to operate on view 'TABLES'.

The same code and the same sentence. And afterwards:

Query

SELECT COUNT(*) AS VIEWS_STILL_IN_INFORMATION_SCHEMA
  FROM DEMO_DB.INFORMATION_SCHEMA.VIEWS
 WHERE TABLE_SCHEMA = 'INFORMATION_SCHEMA';

Result1 row · 1 column

VIEWS_STILL_IN_INFORMATION_SCHEMA
63

All sixty three still there. We are ACCOUNTADMIN and we cannot touch them, which is the right answer for a thing whose job is to describe.

One last thing: three views for three questions

Privileges are not in one view in there. They are in three, and they answer different questions. First, privileges on a table.

Query

SELECT GRANTOR, GRANTEE, TABLE_NAME, PRIVILEGE_TYPE, IS_GRANTABLE
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLE_PRIVILEGES
 WHERE TABLE_NAME = 'THINGS_T170'
 ORDER BY GRANTEE, PRIVILEGE_TYPE;

Result2 rows · 5 columns

GRANTOR	GRANTEE	TABLE_NAME	PRIVILEGE_TYPE	IS_GRANTABLE
ACCOUNTADMIN	ACCOUNTADMIN	THINGS_T170	OWNERSHIP	YES
ACCOUNTADMIN	RISCH_T170	THINGS_T170	SELECT	NO

Query

SELECT COUNT(*) AS TABLE_PRIVILEGE_ROWS
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLE_PRIVILEGES
 WHERE TABLE_NAME = 'THINGS_T170';

Result1 row · 1 column

TABLE_PRIVILEGE_ROWS
2

Two rows. OWNERSHIP to ACCOUNTADMIN and SELECT to the small role we made earlier. Now privileges on an object that is not a table.

Query

SELECT GRANTEE, OBJECT_NAME, OBJECT_TYPE, PRIVILEGE_TYPE
  FROM DEMO_DB.INFORMATION_SCHEMA.OBJECT_PRIVILEGES
 WHERE OBJECT_NAME = 'ISCHEMA_T170'
 ORDER BY GRANTEE, PRIVILEGE_TYPE;

Result2 rows · 4 columns

GRANTEE	OBJECT_NAME	OBJECT_TYPE	PRIVILEGE_TYPE
ACCOUNTADMIN	ISCHEMA_T170	SCHEMA	OWNERSHIP
RISCH_T170	ISCHEMA_T170	SCHEMA	USAGE

OWNERSHIP and USAGE on the schema. And a third view again.

Query

SELECT COUNT(*) AS USAGE_PRIVILEGE_ROWS
  FROM DEMO_DB.INFORMATION_SCHEMA.USAGE_PRIVILEGES;

Result1 row · 1 column

USAGE_PRIVILEGE_ROWS
1

One row. Three views, three questions, and asking the wrong one gives you an empty answer rather than an error.

And the clean-up

Everything this post created comes back out.

Clean-up

-- Everything this post made, taken back out again.
DROP ROLE IF EXISTS RISCH_T170;
DROP SEQUENCE IF EXISTS DEMO_DB.ISCHEMA_T170.SEQ_T170;
DROP VIEW IF EXISTS DEMO_DB.ISCHEMA_T170.CHEAP_T170;
DROP TABLE IF EXISTS DEMO_DB.ISCHEMA_T170.THINGS_T170;
USE SCHEMA DEMO_DB.PUBLIC;
DROP SCHEMA IF EXISTS DEMO_DB.ISCHEMA_T170;

Query

SELECT COUNT(*) AS MY_SCHEMAS_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.SCHEMATA
 WHERE SCHEMA_NAME = 'ISCHEMA_T170';

Result1 row · 1 column

MY_SCHEMAS_LEFT
0

Query

SELECT COUNT(*) AS MY_TABLES_LEFT
  FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_NAME LIKE '%\\_T170' ESCAPE '\\';

Result1 row · 1 column

MY_TABLES_LEFT
0

Query

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

Result1 row · 1 column

MY_ROLES_LEFT
0

Nothing left behind. And the secondary roles go back the way we found them, because we switched them off in the middle of this post and a session setting outlives the statement that made it.

Query

USE SECONDARY ROLES ALL;

Result1 row · 1 column

status
Statement executed successfully.

Query

SELECT CURRENT_ROLE() AS THE_ROLE, CURRENT_SECONDARY_ROLES() AS THE_SECONDARY_ROLES;

Result1 row · 2 columns

THE_ROLE	THE_SECONDARY_ROLES
ACCOUNTADMIN	{"roles":"ORGADMIN","value":"ALL"}

Key points

INFORMATION_SCHEMA is a set of views — sixty three of them — inside every database. It is not a catalogue of the account.
INFORMATION_SCHEMA.WAREHOUSES does not exist and never did. A warehouse is an account object and has no database to live under.
But DATABASES, APPLICABLE_ROLES and ENABLED_ROLES are account-scoped anyway, so the scope rule is a default rather than a law.
002003 is one code for three problems: the object does not exist, it exists and you used it in the wrong shape, or it exists and you have no privilege on it.
QUERY_HISTORY in there is a table function, not a view. Call it inside TABLE() with its arguments.
The same COUNT over the TABLES view gave 76 to ACCOUNTADMIN and 64 to a role with one schema and one table. The rows are filtered by what the asking role can see.
That small role still read all six columns of its one table, so it is filtered rather than blinded.
SHOW SCHEMAS and SCHEMATA gave different counts for the same database. SHOW asks the engine; INFORMATION_SCHEMA asks a view.
CATALOG_NAME is always one value, because each database's INFORMATION_SCHEMA reports only its own database.
It is immediate. An object appears in it in the next statement after being created and disappears in the next statement after being dropped, and ROW_COUNT agreed with a real count.
It is read only. INSERT and DROP VIEW both give 003001, even to ACCOUNTADMIN.
Privileges live in three separate views: TABLE_PRIVILEGES, OBJECT_PRIVILEGES and USAGE_PRIVILEGES.

Next in this series

In the next post we look at the other metadata source: ACCOUNT_USAGE, which forgets nothing and is always behind. We will measure how far behind, and the answer is not one number.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.36.101) running the demo script for this episode. The two-role comparison was captured with USE SECONDARY ROLES NONE proved off before it and again inside it; without that control it would have been ACCOUNTADMIN against ACCOUNTADMIN and would have proved nothing. Grids that identify the account or the user are deliberately left off the page, and the post says so each time one is missing. Everything created here is dropped here. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake INFORMATION_SCHEMA documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 170 - The Same COUNT Gave Me 76 And 64, And That Is The Whole Point

Let's create a schema and a table to work with

Let's start with what is actually in there

Now the one I got wrong, three files running

But the scope rule is a default, not a law

What 002003 will not tell you

Another prediction of mine that was wrong

Now the real point: the same COUNT, two roles

And it disagrees with the SHOW that asks the same thing

Let's see how quickly it notices a change

And how quickly it forgets one

Can we write to it?

One last thing: three views for three questions

And the clean-up

Key points

No comments:

Post a Comment