How to Get Row Count of Database Tables in Snowflake?

  • Post author:
  • Post last modified:August 12, 2021
  • Post category:Snowflake
  • Reading time:4 mins read

Counting the number of records from the database tables is one of the mandatory checks when you migrate data from one server to another. Snowflake is very rich in metadata information. It captures many useful information such as details about tables, columns, views, etc. In this article, we will write few useful queries to get row count of Snowflake database tables.

Get Row Count of Database Tables in Snowflake

You can look for object metadata information either in INFROMATION_SCHEMA for a particular database or utilize the ACCOUNT_USAGE that Snowflake provides data dictionary object metadata, as well as historical usage data, for your account via a shared database named SNOWFLAKE.

Following are the two approaches that you can use to get row count of Snowflake database tables.

Now, let us check queries for these two methods.

Snowflake Tables Row Count using INFORMATION_SCHEMA

The Snowflake Information Schema consists of a set of system-defined views and table functions that provide extensive metadata information about the objects created in your account.

Following query provide row count of all tables present in the selected database.

SELECT table_schema
       || '.'
       || table_name AS "table_name",
       row_count
FROM   information_schema.tables
WHERE  table_type = 'BASE TABLE'
ORDER  BY row_count;
+---------------------------+-----------+
| table_name                | ROW_COUNT |
|---------------------------+-----------|
| PUBLIC.ARR_TABLE          |         1 |
| PUBLIC.JSON_TB            |         1 |
| PUBLIC.MYTABLE            |         3 |
| PUBLIC.MERGE_TEST         |         4 |
| PUBLIC.REC_TEST           |         5 |
| PUBLIC.EMPLOYEE           |         9 |
| PUBLIC.EMPLOYEE_TEMP      |         9 |
| PUBLIC.EXAMPLE_CUMULATIVE |        16 |
+---------------------------+-----------+

Snowflake Tables Row Count using Account Usage Share

Snowflake provides data dictionary object metadata, as well as historical usage data, for your account via a shared database named SNOWFLAKE. The SNOWFLAKE database is a system-defined, read-only shared database, provided by Snowflake. You should have an ACCOUNTADMIN role to access this shared database. By default, only account administrators can access the SNOWFLAKE database and schemas within the database, or perform queries on the views.

Following query provide row count of all tables present in given database.

USE ROLE ACCOUNTADMIN;
SELECT table_name,
       row_count
FROM   snowflake.account_usage.tables
WHERE  table_catalog = 'TEST_DB'
       AND table_type = 'BASE TABLE'
       AND deleted IS NULL
ORDER  BY 2; 
+--------------------+-----------+
| TABLE_NAME         | ROW_COUNT |
|--------------------+-----------|
| ARR_TABLE          |         1 |
| JSON_TB            |         1 |
| MYTABLE            |         3 |
| MERGE_TEST         |         4 |
| REC_TEST           |         5 |
| EMPLOYEE_TEMP      |         9 |
| EMPLOYEE           |         9 |
| EXAMPLE_CUMULATIVE |        16 |
+--------------------+-----------+

Note that, this method has some latency, but the advantage is that you can query all databases and schemas at the same time. You will be limited to one database if you use INFORMATION_SCHEMA.

Related Articles,

Hope this helps 🙂

Tags: , , ,