DESCRIBE Command in Snowflake - Complete SQL Guide

Free online DESCRIBE Command in Snowflake - Complete SQL Guide tool. No signup required.

Free & instant 100% client-side No signup needed

Basic DESCRIBE Syntax

-- Describe a table
DESCRIBE TABLE table_name;
DESC TABLE table_name;

-- Describe a view
DESCRIBE VIEW view_name;

-- Describe a schema
DESCRIBE SCHEMA schema_name;

-- Describe a stage
DESCRIBE STAGE stage_name;

Last updated

What is DESCRIBE in Snowflake?

The DESCRIBE command (or its alias DESC) in Snowflake displays detailed metadata about database objects. It's essential for understanding table structures, column data types, constraints, and other object properties without querying the data itself.

DESCRIBE TABLE - Most Common Usage

The most frequent use of DESCRIBE is to view table structure:

SQL

Quick Reference

  • DESC TABLE schema.table_name — column definitions and constraints
  • DESC VIEW schema.view_name — view output columns
  • DESC SCHEMA schema_name — all objects in a schema
  • DESC DATABASE db_name — all schemas in a database
  • DESC STAGE stage_name — stage URL, credentials, file format
  • DESC FILE FORMAT format_name — file format options
  • DESC INTEGRATION integration_name — external integration settings

The DESCRIBE command is one of the most useful tools for exploring unfamiliar Snowflake schemas, debugging data loading pipelines, and documenting database structures. It requires no special permissions beyond the ability to use the object being described.

DESCRIBE TABLE customers;

-- Output columns:
-- name: Column name
-- type: Data type (VARCHAR, NUMBER, DATE, etc.)
-- kind: COLUMN
-- null?: Y or N (nullable or not)
-- default: Default value
-- primary key: PK if primary key
-- unique key: UK if unique key
-- check: Check constraint
-- expression: For computed columns
-- comment: Column comment

DESCRIBE TABLE Example Output

name type kind null? default primary key
CUSTOMER_ID NUMBER(38,0) COLUMN N NULL PK
FIRST_NAME VARCHAR(100) COLUMN N NULL
EMAIL VARCHAR(255) COLUMN Y NULL
CREATED_AT TIMESTAMP_NTZ(9) COLUMN N CURRENT_TIMESTAMP()

DESCRIBE vs SHOW Commands

Command Purpose Example
DESCRIBE TABLE Show structure of one table DESC TABLE customers;
SHOW TABLES List all tables in schema SHOW TABLES;
DESCRIBE VIEW Show structure of one view DESC VIEW sales_view;
SHOW VIEWS List all views in schema SHOW VIEWS;

DESCRIBE Object Types

All DESCRIBE Variants
-- Tables and Views
DESCRIBE TABLE my_table;
DESCRIBE VIEW my_view;
DESCRIBE EXTERNAL TABLE my_external_table;
DESCRIBE MATERIALIZED VIEW my_mat_view;

-- Schemas and Databases
DESCRIBE SCHEMA my_schema;
DESCRIBE DATABASE my_database;

-- Stages and File Formats
DESCRIBE STAGE my_stage;
DESCRIBE FILE FORMAT my_format;

-- Functions and Procedures
DESCRIBE FUNCTION my_function(NUMBER, VARCHAR);
DESCRIBE PROCEDURE my_proc(NUMBER);

-- Sequences and Streams
DESCRIBE SEQUENCE my_sequence;
DESCRIBE STREAM my_stream;

Practical Examples

Example 1: Check Column Data Types
-- Before inserting data, verify column types
DESCRIBE TABLE orders;

-- Check if column accepts NULL
-- Look for 'null?' column in output
Example 2: Find Primary Keys
-- Identify primary key columns
DESCRIBE TABLE customers;

-- Look for 'PK' in 'primary key' column
Example 3: View Default Values
-- See which columns have default values
DESCRIBE TABLE events;

-- Check 'default' column for CURRENT_TIMESTAMP(), etc.

DESCRIBE with Fully Qualified Names

-- Use fully qualified names for clarity
DESCRIBE TABLE database_name.schema_name.table_name;

-- Or set context first
USE DATABASE my_database;
USE SCHEMA my_schema;
DESCRIBE TABLE my_table;

Common Use Cases

  • Schema Discovery: Understand table structure before writing queries
  • Data Type Verification: Confirm column types match expected formats
  • Constraint Checking: Identify primary keys, unique keys, and NOT NULL columns
  • Documentation: Generate table documentation from DESCRIBE output
  • Migration Planning: Compare source and target table structures
  • Debugging: Troubleshoot data type mismatches and constraint violations

DESCRIBE Output in Scripts

Query DESCRIBE Results
-- Store DESCRIBE output in a table
CREATE OR REPLACE TABLE table_metadata AS
SELECT * FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "name" = 'DESCRIBE TABLE my_table';

-- Filter DESCRIBE results
DESCRIBE TABLE customers;
-- Then query specific columns from result set

Best Practices

  • Use DESC as shorthand for faster typing
  • Always DESCRIBE tables before bulk inserts to verify schema
  • Check 'null?' column to understand required vs optional fields
  • Review 'default' column to see auto-populated values
  • Use fully qualified names in production scripts
  • Combine with SHOW commands for complete object discovery

DESCRIBE vs Information Schema

For programmatic access to metadata, use INFORMATION_SCHEMA views:

-- Alternative to DESCRIBE TABLE
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'CUSTOMERS'
  AND table_schema = 'PUBLIC';

-- More flexible for filtering and joining

Examples

Example 1: DESCRIBE TABLE

The most common use — inspect a table's columns, data types, and constraints:

DESCRIBE TABLE mydb.public.orders;
-- or shorthand:
DESC TABLE mydb.public.orders;

Sample output:

name          | type          | kind   | null? | default | primary key | unique key | check | expression | comment
--------------+---------------+--------+-------+---------+-------------+------------+-------+------------+--------
order_id      | NUMBER(38,0)  | COLUMN | N     |         | Y           | N          |       |            |
customer_id   | NUMBER(38,0)  | COLUMN | N     |         | N           | N          |       |            |
order_date    | TIMESTAMP_NTZ | COLUMN | Y     |         | N           | N          |       |            |
total_amount  | FLOAT         | COLUMN | Y     |         | N           | N          |       |            |
status        | VARCHAR(50)   | COLUMN | Y     | 'PENDING'| N          | N          |       |            |

Example 2: DESCRIBE VIEW

Inspect the output columns of a view without seeing the underlying SQL:

DESC VIEW mydb.analytics.monthly_revenue;

Output shows the columns the view exposes:

name           | type          | kind   | null?
---------------+---------------+--------+------
month          | DATE          | COLUMN | Y
revenue        | FLOAT         | COLUMN | Y
order_count    | NUMBER(38,0)  | COLUMN | Y
avg_order_value| FLOAT         | COLUMN | Y

Example 3: DESCRIBE SCHEMA

List all objects in a schema to get a quick inventory:

DESC SCHEMA mydb.public;

Returns all tables, views, sequences, stages, and file formats in the schema with their creation timestamps and owners.

Frequently Asked Questions

The DESCRIBE command in Snowflake (also written as DESC) displays metadata about database objects including tables, views, schemas, stages, and file formats. It shows column names, data types, nullability, default values, and other object properties.

To describe a table in Snowflake, use: DESCRIBE TABLE table_name; or DESC TABLE table_name; This returns column names, data types, kind (COLUMN), null constraints, default values, primary keys, unique keys, and comments for each column.

DESCRIBE shows detailed metadata about a specific object's structure (columns, data types, constraints), while SHOW lists multiple objects of a type (e.g., SHOW TABLES lists all tables). Use DESCRIBE for object details, SHOW for object discovery.

Yes, DESC is an alias for DESCRIBE in Snowflake. Both commands work identically: DESC TABLE my_table; and DESCRIBE TABLE my_table; produce the same results. DESC is shorter and commonly used.