Snowflake Information Schema behavior for View tables

user_18639958 0
Viewed 47

I am new to snowflake. I am creating a view in snowflake as below

create view TABLENAME_VIEW as select * from test.stage.TABLENAME;

while running a select statement on information_schema.tables, I am getting the row_count of view table as null whereas doing select count(*) on the view tables gives the count.

Can someone please help on this.

TABLE_NAME ROW_COUNT TABLE_CATALOG TABLE_TYPE
TABLENAME 5 TEST BASE TABLE
TABLENAME_VIEW NULL TEST VIEW 
select count(*) from TABLENAME_VIEW;
COUNT(*)
5
snowflake-cloud-data-platform
1 Answers

Views are not tables (unless it an materializes view). So they have no rows. When you select from the view it becomes a projection of the underlying table/s

This can be seen by creating another view that has a “WHERE false” thus will return 0 to you select count(*) from the will return 0, but it will have no row. OR if your view had “limit 1”. It will have a count of 1, but which one is the magic part.

user_439920
Related