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.
- Snowflake Database Tables Record Count using INFORMATION_SCHEMA
- Snowflake Database Tables Record Count using Account Usage Share
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 🙂