Snowflake row count: how to count rows in tables and views

Snowflake row count: how to count rows in tables and views

Counting rows in Snowflake helps data teams validate data loads and detect unexpected changes in data volume. COUNT(*) retrieves counts for tables and views, while ACCOUNT_USAGE.TABLES, INFORMATION_SCHEMA.TABLES, and SHOW TABLES provide table row counts from metadata. These methods differ in scope, permissions, and how current their results are. Standard (non-materialized) views store query definitions rather than results, so their row counts must be calculated with COUNT(*). Materialized views store query results and have row-count metadata.

An unexpected drop in row count may indicate an incomplete data load, while an increase may reflect duplicate records or legitimate growth in upstream activity. Comparing counts over time helps teams identify changes that warrant investigation; however, row counts alone cannot establish whether the data is complete or correct.

How do you get the row count of a table in Snowflake?

For a single table, SELECT COUNT(*) FROM retail_db.analytics.orders; returns the row count. Snowflake also provides metadata methods for retrieving counts across multiple tables. The table below compares these approaches, including when each is useful and the permissions and limitations to consider.

How do you count rows with COUNT(*) in Snowflake?

COUNT(*) returns the exact number of rows visible to a query, including rows containing NULL values. It works with both tables and standard views.

The examples use a hypothetical retail_db.analytics.orders table and retail_db.analytics.v_open_orders view. Object and column names should match accessible database objects.

The following query counts the rows in the orders table:

-- Snowflake row count from a table scan for a specific table.
SELECT COUNT(*) AS row_count
FROM retail_db.analytics.orders;

COUNT(column) behaves differently. It only counts rows where the specified column is non-NULL. For example, COUNT(customer_id) excludes orders with a NULL customer ID, so its result may be lower than the table’s total row count.

Snowflake can use table statistics to optimize simple counts, so counting a large table does not necessarily require a full scan. Filters and complex view definitions may require additional processing, while row access policies require checks to determine which rows are visible to the querying role. Execution time and compute cost therefore depend on the query and its execution plan.

How do you get row counts from ACCOUNT_USAGE.TABLES?

SNOWFLAKE.ACCOUNT_USAGE.TABLES provides table row counts through its ROW_COUNT column. Because the view covers databases across the account, it is useful for retrieving counts for multiple tables without running a separate COUNT(*) query against each one.

The following query retrieves the stored row count for the orders table:

-- Snowflake row count from ACCOUNT_USAGE for a specific table.
-- Unquoted identifiers are stored in uppercase, so filter on 'ORDERS'.
SELECT table_catalog, table_schema, table_name, row_count
FROM snowflake.account_usage.tables
WHERE table_catalog = 'RETAIL_DB'
  AND table_schema = 'ANALYTICS'
  AND table_name = 'ORDERS'
  AND deleted IS NULL;

The DELETED IS NULL condition excludes dropped tables, whose records remain in Account Usage. The uppercase filter values match Snowflake’s handling of identifiers created without double quotes.

These counts can lag behind table changes by up to 90 minutes. They represent stored metadata rather than estimates but may not reflect a recent load or deletion. This makes the method more suitable for reviewing table volumes across an account than for validating a load immediately after it finishes.

The querying role needs access to Account Usage’s TABLES view, which can be granted through the SNOWFLAKE.OBJECT_VIEWER database role. The query retrieves metadata rather than counting the underlying table’s rows but still uses warehouse compute and can incur compute charges.

How do you get row counts from INFORMATION_SCHEMA.TABLES?

INFORMATION_SCHEMA.TABLES provides table row counts through its ROW_COUNT column. Each database has its own Information Schema, so this method retrieves counts within one database. It returns current metadata without the reporting delay of ACCOUNT_USAGE.TABLES.

The following query retrieves the row count for the orders table:

-- Snowflake row count from INFORMATION_SCHEMA for a specific table.
SELECT table_catalog, table_schema, table_name, row_count
FROM retail_db.information_schema.tables
WHERE table_schema = 'ANALYTICS'
  AND table_name = 'ORDERS';

The retail_db prefix identifies the database being queried. Without that prefix, INFORMATION_SCHEMA.TABLES refers to the current database.

The view only returns objects accessible to the active role and excludes dropped tables. Standard views and external tables can appear in its output, but their ROW_COUNT is NULL.

ROW_COUNT provides the table’s stored metadata count. COUNT(*) counts rows visible to a query and supports filters and non-materialized views. Queries against INFORMATION_SCHEMA.TABLES require a running warehouse and can incur compute charges. Filtering by schema and table name, when appropriate, helps performance.

How do you get row counts for all tables in a Snowflake database?

The following query retrieves a separate row count for each base table visible to the active role across the retail_db database. The schema name identifies tables that share a name across different schemas.

-- Snowflake row counts from INFORMATION_SCHEMA for all tables.
SELECT table_schema, table_name, row_count
FROM retail_db.INFORMATION_SCHEMA.TABLES
WHERE table_type = 'BASE TABLE'
ORDER BY table_schema, table_name;

The BASE TABLE filter excludes views and other table types, including temporary and external tables. Transient tables are included, because they report as BASE TABLE. For databases with many objects, additional schema filters may be needed to avoid Information Schema’s limits on queries that return too much metadata.

How do you get row counts with SHOW TABLES?

SHOW TABLES returns table metadata, including a rows column containing each table’s row count. The command requires no running warehouse, making it useful for checking counts without starting warehouse compute.

The following example retrieves metadata for the orders table, then selects the identifying columns and row count from the result:

-- Snowflake row counts from SHOW TABLES for a specific table.
SHOW TABLES LIKE 'ORDERS' IN SCHEMA retail_db.analytics;

SELECT "database_name", "schema_name", "name", "rows"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

The two statements should run consecutively in the same session. LAST_QUERY_ID() returns the most recent query’s ID, so an intervening statement can cause RESULT_SCAN to read a different result. Double quotes preserve the lowercase column names returned by SHOW TABLES.

For large inventories, SHOW TABLES without a LIMIT clause fails if its result exceeds 10,000 records. Pagination or an Information Schema query can handle broader inventories.

Why don’t non-materialized Snowflake views have a stored row count?

A standard, non-materialized view stores a query definition rather than the query’s results. Its rows are determined when the view is queried, so Snowflake doesn’t maintain a stored row count for it. Metadata methods therefore cannot provide its row count.

The following query counts the rows returned by the v_open_orders view:

-- Snowflake row count from a table scan for a specific view.
SELECT COUNT(*) AS row_count
FROM retail_db.analytics.v_open_orders;

The result reflects the view’s filters, joins, and other query logic, so it may differ from the row counts of its underlying tables. Execution time and compute cost depend on the work required to evaluate that logic.

Materialized views store query results and have row-count metadata. The rows column returned by SHOW MATERIALIZED VIEWS reports their stored row counts.

How do you monitor Snowflake row counts over time?

Scheduled row-count monitoring records each table’s count and flags counts outside the expected range. Checks should reflect the expected load schedule and account for normal changes in activity.

For example, an orders table that normally gains rows after a nightly load may warrant investigation if its count remains unchanged or grows much less than usual. Comparisons should account for expected differences, such as lower order volumes on weekends. A successful pipeline run can still produce incomplete data. Further checks establish the cause of an unexpected count.

Datadog Data Observability monitors Snowflake row counts on a schedule using table metadata, only falling back to table scans when required for views or subsets of tables that match a condition. Teams can configure thresholds or use anomaly detection to learn expected ranges from historical behavior, including trends and seasonal patterns. Row-count alerts help teams identify unexpected changes in data volume alongside freshness and column-level quality checks. Teams can configure Slack notifications to route alerts to the responsible channel.

Using row counts to check Snowflake data

The right row-count method depends on the scope of the check, available permissions, and how current the result needs to be. Counts can help validate data loads and reveal unexpected volume changes, but they cannot establish completeness or accuracy on their own. Teams can start by monitoring critical tables after scheduled loads, then investigate unexpected changes alongside freshness and column-level quality checks.

To learn how to configure scheduled Snowflake row-count checks and alerts, see our documentation on Data Observability monitors.

FAQs

Does COUNT(*) count NULL values in Snowflake?

Yes. COUNT(*) counts every row, including rows containing NULL values. COUNT(column) counts only rows where that column is non-NULL. For example, COUNT(customer_id) excludes orders with a NULL customer ID, so it may return a lower count than COUNT(*).

How do you count rows that match a condition in Snowflake?

A WHERE clause limits the rows included in COUNT(*). For example, the following query counts orders with an order_total greater than 100:

-- Snowflake row count for all rows in a table matching a condition.
SELECT COUNT(*) AS row_count
FROM retail_db.analytics.orders
WHERE order_total > 100;

Metadata row counts describe the table as a whole and cannot provide counts for a filtered subset.

Why do Snowflake row-count methods return different results?

Differences can arise from metadata delays, query filters, access policies, or changes between checks. ACCOUNT_USAGE.TABLES can lag by up to 90 minutes, while COUNT(*) counts rows visible to the query. Comparisons should reference the same table and account for differences in scope and timing.