In Databricks, data engineers and analysts often need to inspect the structure and metadata of tables. Whether debugging, auditing, or simply trying to understand the schema, the DESCRIBE command in SQL is an essential tool.
Did you know that there are several levels of DESCRIBE in Databricks? We will explore the differences DESCRIBE, DESCRIBE EXTENDED, and DESCRIBE DETAIL using SQL.
- 🧱 1. DESCRIBE table_name
- 🛠 2. DESCRIBE EXTENDED table_name
- 🔍 3. DESCRIBE DETAIL table_name
- 🎯 When to Use What?
- ✅ Final Thoughts
🧱 1. DESCRIBE table_name
This is the most basic version and is used to view column-level details of a table:
%sql
DESCRIBE student;
🔍 Output:
| col_name | data_type | comment |
|---|---|---|
| student_id | int | |
| student_name | string | |
| teacher_id | int |
Use this when you simply want to understand the schema or preview column names and types.
🛠 2. DESCRIBE EXTENDED table_name
This command gives both column definitions and table-level metadata, such as location, file format, and properties.
%sql
DESCRIBE EXTENDED student;
You’ll see the same column schema first, and then a block of detailed table information like this:
# Detailed Table Information
Database: default
Table: student
Owner: your_user
Type: MANAGED
Provider: delta
Location: dbfs:/user/hive/warehouse/student
This is especially useful to confirm whether the table is stored in Delta Lake, its storage location, and table type (managed vs. external).
🔍 3. DESCRIBE DETAIL table_name
This is the most comprehensive version and returns a single row with JSON-style fields, ideal for automation or programmatic inspection.
%sql
DESCRIBE DETAIL student;
✅ Sample Output:
| name | format | location | createdAt | lastModifiedAt | properties |
|---|---|---|---|---|---|
| student | delta | dbfs:/user/hive/warehouse/student | 2024-01-01 12:34:00 | 2024-01-05 10:22:15 | {…} |
Key fields include:
format: Tells you if the table is Delta, Parquet, etc.location: The DBFS or external storage path.createdAt,lastModifiedAt: Helpful for audits.properties: Any custom table metadata.
🎯 When to Use What?
| Command | Use For |
|---|---|
DESCRIBE | Quick schema check |
DESCRIBE EXTENDED | View storage format and location |
DESCRIBE DETAIL | Full table metadata for deeper insights or scripting |
✅ Final Thoughts
The DESCRIBE suite in Databricks is more than just a tool for looking at columns — it’s a gateway to understanding how your data is structured, stored, and governed. Whether you’re building pipelines, auditing data, or exploring unfamiliar tables, mastering DESCRIBE commands helps you work smarter and faster in Databricks.
Srinimf 