How to obtain an equivalent to Pandas's "describe" method for tables in Snowflake?

user_11298925 0
Viewed 469

I want to be able to get the main information regarding the various columns of tables located in Snowflake, like a df.describe() could do in Pandas:

  • column names,
  • data types,
  • min/max/average for numeric types,
  • and ideally unique values for string types
  • maybe other things that I'm missing

Granted, you could simply pull all the data into a local DataFrame then do the "describe" in Pandas, but this would be too costly for Snowflake tables counting millions of rows.

Is there a simple way to do this?

snowflake-cloud-data-platform pandas sql
2 Answers

column names

data types

You could always query INFORMATION_SCHEMA:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ILIKE 'table_name';

Or

DESCRIBE TABLE 'TABLE_NAME';

min/max/average for numeric types,

and ideally unique values for string types

maybe other things that I'm missing

Automatic Contextual Statistics

Select columns, cells, rows, or ranges in the results table to view relevant information about the selected data in the inspector pane (to the right of the results table). Contextual statistics are automatically generated for all column types. The statistics are intended to help you make sense of your data at a glance.

...

  • Filled/empty meters
  • Histograms
  • Frequency distributions
  • Email domain distributions
  • Key distributions
user_50708790

There is no equivalent to df.describe().

The simplest way might be a query that replicates it. For example, you could compose a UDF that took as input the result of get_ddl() for the table and returned as output a query that had the correct SQL (min/max/avg etc) for each column.

If approximate answers are sufficient, one alternative would be to do what you described in a local DataFrame but implement a TABLESAMPLE clause to avoid loading all the data.

If you pursue the query route, the good news is that it should be mostly metadata-only operations which are very fast.

user_20199780
Related