Data & Settings Inspection


DuckDB provides two ways to inspect your data: SQL Metadata Functions (which work in any environment like Go or Python) and CLI Dot Commands (exclusive to the terminal).


Inspecting Tables and Views

To see what you are working with, use these commands:

Action

CLI Dot Command

SQL Equivalent

List all tables

.tables

SHOW TABLES;

Show table structure

.schema [table_name]

DESCRIBE [table_name];

Show columns/types

N/A

PRAGMA table_info('table_name');

List all views

.tables (included)

SELECT * FROM duckdb_views();


Pro-Tip: Inspecting "External" Files

One of DuckDB's best features is inspecting a file before you even create a table.

-- See the schema of a Parquet file on your disk
DESCRIBE SELECT * FROM 'data/raw/yellow_taxi.parquet';

-- Peek at the first 5 rows
SELECT * FROM 'data/raw/yellow_taxi.parquet' LIMIT 5;

Deep Schema Inspection

If you need to know more than just column names (like nullability, defaults, or internal storage info), use the Metadata Functions.

  • SUMMARIZE: This is a DuckDB superpower. It gives you a statistical overview of every column (min, max, average, percentage of nulls, unique count).

  • duckdb_columns: Useful if you want to find every table that contains a specific column name (like user_id).

  • .header on: Ensures column names are always visible (usually on by default).


You don't have to leave DuckDB to see your files:

  • .shell [command]: Run any terminal command from inside DuckDB.


Resource & Setting Inspection

Inspect DuckDB environmentarrow-up-right

As a Data Engineer, you need to know how much memory and CPU DuckDB is using.

  • SET: Running SET; (with no arguments) shows every configuration option and its current value.

  • PRAGMA platform: Tells you exactly which architecture and version you are running (useful for extension compatibility).

  • PRAGMA memory_info: Shows current RAM usage and the "spill-to-disk" threshold.



Last updated