---
name: duckdb-analytics
description: Embedded SQL analytics engine for exploring large datasets without infrastructure overhead. Perfect for JSON, CSV, Parquet analysis when you need GROUP BY, aggregations, and correlations. Uses DuckDB CLI binary directly — no Python dependency.
---

# DuckDB Analytics

Fast SQL-based analysis of large datasets (100MB–10GB) with zero infrastructure overhead.

## When to Use

Use DuckDB for:

- **Data exploration:** Answering questions via SQL (GROUP BY, aggregations, joins)
- **One-off analysis:** No need for persistent databases or servers
- **Log/event analysis:** JSON, CSV, Parquet files with millions of records
- **Correlations:** Finding patterns (e.g., "when X happens, Y follows?")
- **Reproducible analytics:** Queries visible as shell commands (no black-box processing)

**When NOT to use:**

- Real-time transactional workloads (→ PostgreSQL)
- Distributed processing across machines (→ Spark)
- Unstructured text search (→ Elasticsearch)
- Data that doesn't fit in RAM (→ Spark + distributed storage)

## Quick Start (2 minutes)

### Install

```bash
# macOS
brew install duckdb

# Linux
curl https://install.duckdb.org | sh

# Ubuntu/Debian
sudo apt install duckdb
```

### Basic Usage

DuckDB reads files directly — no setup, no schema, no server:

```bash
# Query a CSV in one command
duckdb -c "
    SELECT category, COUNT(*) as count, AVG(value) as avg_value
    FROM read_csv('data.csv')
    GROUP BY category
    ORDER BY count DESC
"
```

Output is a formatted table. Add `-json` for machine-parseable output:

```bash
duckdb -json -c "SELECT category, COUNT(*) as count FROM read_csv('data.csv') GROUP BY category"
```

## Data Sources

DuckDB reads from multiple formats directly — no import step needed:

```bash
# CSV
duckdb -c "SELECT * FROM read_csv('file.csv')"

# Parquet
duckdb -c "SELECT * FROM read_parquet('file.parquet')"

# JSON (one object per line)
duckdb -c "SELECT * FROM read_json_auto('file.jsonl')"

# Multiple files (glob pattern)
duckdb -c "SELECT * FROM read_csv('data/*.csv')"
```

## Single-Query Mode (default)

Each `duckdb -c "..."` invocation runs in **in-memory** mode. For quick one-off
queries this is perfect — no cleanup needed. Just point at a file and query.

For **multi-step workflows** (load → analyze → export), use a persistent database
file or combine statements:

```bash
# Persistent database (keeps tables between calls)
duckdb analysis.db -c "CREATE TABLE events AS SELECT * FROM read_csv('data.csv')"
duckdb analysis.db -json -c "SELECT COUNT(*) FROM events"
duckdb analysis.db -c "COPY events TO 'backup.parquet'"

# Or combine into one call with semicolons
duckdb -c "
    CREATE TABLE events AS SELECT * FROM read_csv('data.csv');
    SELECT event_type, COUNT(*) FROM events GROUP BY event_type;
    COPY (SELECT * FROM events) TO 'backup.parquet';
"
```

## Multi-Statement Mode

For complex workflows, use a heredoc or temp file:

```bash
cat << 'SQL' | duckdb
CREATE TABLE events AS SELECT * FROM read_csv('data.csv');
SELECT event_type, COUNT(*) FROM events GROUP BY event_type;
COPY (SELECT * FROM events) TO 'events.parquet';
SQL
```

Or write to a file for reuse:

```bash
cat > analysis.sql << 'SQL'
CREATE TABLE events AS SELECT * FROM read_csv('data.csv');
SELECT event_type, COUNT(*) FROM events GROUP BY event_type ORDER BY count DESC;
SQL

duckdb < analysis.sql
```

## Common Queries

### 1. Group By with Multiple Aggregations

```sql
SELECT
    category,
    COUNT(*) as count,
    SUM(value) as total,
    AVG(value) as avg,
    MIN(value) as min,
    MAX(value) as max,
    STDDEV_POP(value) as stdev
FROM data
GROUP BY category
ORDER BY count DESC
```

### 2. Correlations (Bucket & Compare)

```sql
SELECT
    CASE
        WHEN metric < 100 THEN '0-100'
        WHEN metric < 1000 THEN '100-1K'
        ELSE '1K+'
    END as bucket,
    COUNT(*) as count,
    ROUND(100.0 * SUM(CASE WHEN error THEN 1 ELSE 0 END) / COUNT(*), 1) as error_rate,
    AVG(duration) as avg_duration
FROM events
GROUP BY bucket
ORDER BY bucket
```

### 3. Time Series (if you have a timestamp column)

```sql
SELECT
    DATE_TRUNC('day', timestamp) as day,
    COUNT(*) as events,
    SUM(CASE WHEN error THEN 1 ELSE 0 END) as errors,
    ROUND(AVG(latency), 2) as avg_latency
FROM events
GROUP BY day
ORDER BY day DESC
LIMIT 30
```

### 4. Top N Within Groups

```sql
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY value DESC) as rank
    FROM data
)
SELECT * FROM ranked WHERE rank <= 10
```

### 5. Join Multiple Tables

```sql
SELECT
    a.id,
    a.name,
    COUNT(b.event_id) as event_count,
    AVG(b.value) as avg_event_value
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
GROUP BY a.id, a.name
ORDER BY event_count DESC
```

## Advanced Features

### Window Functions

```bash
duckdb -c "
    SELECT
        timestamp,
        value,
        AVG(value) OVER (
            ORDER BY timestamp
            ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
        ) as rolling_avg,
        LAG(value) OVER (ORDER BY timestamp) as prev_value,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY value DESC) as rank
    FROM read_csv('data.csv')
    ORDER BY timestamp
"
```

### CTEs (Common Table Expressions)

```bash
duckdb -c "
    WITH filtered_data AS (
        SELECT * FROM read_csv('data.csv') WHERE quality > 0.5
    ),
    aggregated AS (
        SELECT
            category,
            COUNT(*) as count,
            AVG(value) as avg
        FROM filtered_data
        GROUP BY category
    )
    SELECT * FROM aggregated WHERE count > 10
"
```

### JSON Extraction

```bash
duckdb -c "
    SELECT
        json_extract(metadata, '$.user_id') as user_id,
        json_extract(metadata, '$.timestamp') as ts,
        json_array_length(json_extract(metadata, '$.tags')) as tag_count
    FROM read_json_auto('events.jsonl')
    WHERE json_extract(metadata, '$.status') = 'error'
"
```

### Pattern Matching

```bash
# LIKE for simple patterns, regex for complex
duckdb -c "SELECT * FROM read_csv('data.csv') WHERE name LIKE 'user_%'"
duckdb -c "SELECT * FROM read_csv('data.csv') WHERE name ~ 'user_[0-9]+'"
```

## Performance Tips

### 1. Filter Early

```sql
-- SLOW: compute on all data, then filter
SELECT AVG(value) as avg_value FROM read_csv('data.csv') WHERE category = 'X'

-- FAST: filter first in subquery
SELECT AVG(value) FROM (
    SELECT value FROM read_csv('data.csv') WHERE category = 'X'
)
```

### 2. Specify Column Types

When reading CSV, hint types for performance and correctness:

```bash
duckdb -c "
    SELECT * FROM read_csv('data.csv',
        types={
            'timestamp': 'TIMESTAMP',
            'amount': 'DECIMAL(10,2)',
            'count': 'INTEGER'
        }
    )
"
```

### 3. Parquet for Large Files

CSV is flexible; Parquet is fast. For files >100MB, convert once:

```bash
# Convert CSV to Parquet (one-time)
duckdb -c "
    COPY (SELECT * FROM read_csv('huge.csv'))
    TO 'huge.parquet' (FORMAT PARQUET)
"

# Future queries against Parquet (much faster)
duckdb -c "SELECT * FROM read_parquet('huge.parquet')"
```

### 4. Indexes (rarely needed, but available)

```bash
duckdb analysis.db -c "CREATE INDEX idx_category ON data(category)"
```

### 5. Thread Control

```bash
duckdb -c "SET threads = 4; SELECT COUNT(*) FROM read_parquet('big.parquet')"
```

## Output Formats

| Flag        | Format   | Use case                       |
| ----------- | -------- | ------------------------------ |
| _(none)_    | Table    | Human-readable terminal output |
| `-json`     | JSON     | Machine parsing, jq pipelines  |
| `-csv`      | CSV      | Spreadsheet import, piping     |
| `-markdown` | Markdown | Docs, README tables            |

```bash
# JSON — pipe to jq for filtering
duckdb -json -c "SELECT * FROM read_csv('data.csv')" | jq '.[0:5]'

# CSV — pipe to other tools
duckdb -csv -c "SELECT * FROM read_csv('data.csv')" | head -10
```

## Exporting Results

### To CSV

```bash
duckdb -c "COPY (SELECT * FROM read_csv('data.csv')) TO 'output.csv' (FORMAT CSV)"
```

### To Parquet

```bash
duckdb -c "COPY (SELECT * FROM read_csv('data.csv')) TO 'output.parquet' (FORMAT PARQUET)"
```

### To JSON (one object per line)

```bash
duckdb -c "COPY (SELECT * FROM read_csv('data.csv')) TO 'output.jsonl' (FORMAT JSON)"
```

## Compare to Alternatives

| Tool               | Use Case                      | Setup            | Speed          | Learning   |
| ------------------ | ----------------------------- | ---------------- | -------------- | ---------- |
| **DuckDB CLI**     | Exploratory analytics, <10GB  | brew/apt install | ⚡ Fast        | Easy (SQL) |
| **Pandas**         | Data cleaning, transformation | pip install      | 🐢 Slower      | Medium     |
| **Polars**         | Fast data manipulation        | pip install      | ⚡ Very fast   | Medium     |
| **Spark**          | Distributed, petabyte-scale   | Docker+cluster   | 🚀 Distributed | Hard       |
| **SQL (Postgres)** | Transactional, persistent     | Server setup     | ⏱️ Variable    | Easy (SQL) |

## Common Gotchas

### 1. NULL Handling in Division

```sql
-- WRONG: divides by zero, causes error
SELECT error_count / message_count FROM events

-- RIGHT: use NULLIF
SELECT ROUND(100.0 * error_count / NULLIF(message_count, 0), 1) FROM events
```

### 2. Integer Division

```sql
-- WRONG: truncates to 0 or 1
SELECT error_count / message_count

-- RIGHT: cast to float first
SELECT ROUND(100.0 * error_count::float / message_count, 1)
```

### 3. Case Sensitivity

```sql
-- DuckDB is case-insensitive for identifiers
SELECT name FROM table_name;  -- OK
SELECT NAME FROM TABLE_NAME;  -- Also OK

-- But strings ARE case-sensitive
WHERE status = 'active'    -- Matches 'active' only
WHERE LOWER(status) = 'active'  -- Matches 'active', 'ACTIVE', 'Active'
```

### 4. Each CLI invocation is a fresh process

```bash
# WRONG (in-memory only — second call has no events table):
duckdb -c "CREATE TABLE events AS SELECT * FROM read_csv('data.csv')"
duckdb -c "SELECT COUNT(*) FROM events"  # Error: table not found

# RIGHT: use persistent database file:
duckdb analysis.db -c "CREATE TABLE events AS SELECT * FROM read_csv('data.csv')"
duckdb analysis.db -c "SELECT COUNT(*) FROM events"

# ALSO RIGHT: combine in one call:
duckdb -c "
    CREATE TABLE events AS SELECT * FROM read_csv('data.csv');
    SELECT COUNT(*) FROM events;
"
```

### 5. Aggregation Without GROUP BY

```sql
-- WRONG: mixing grouped and ungrouped columns
SELECT category, value FROM data GROUP BY category

-- RIGHT: aggregate or GROUP all non-aggregated columns
SELECT category, AVG(value) FROM data GROUP BY category
```

## Next Steps

1. **Start small:** `duckdb -c "SELECT * FROM read_csv('file.csv') LIMIT 10"`
2. **Build incrementally:** Add WHERE, GROUP BY, ORDER BY
3. **Draft in heredocs:** Write multi-step analyses with `cat << 'SQL' | duckdb`
4. **Export results:** Save to Parquet for reproducibility
5. **Automate:** Save queries in `.sql` files, version control, run with `duckdb < query.sql`
6. **Monitor:** Re-run monthly to track trends, pipe to `jq` for formatting

## Example: Full Analysis Workflow

```bash
# ── 1. Load data into persistent database ──────────────────────────
duckdb analysis.db -c "
    CREATE TABLE events AS
    SELECT * FROM read_json_auto('events.jsonl')
"

# ── 2. Explore ────────────────────────────────────────────────────
duckdb analysis.db -json -c "SELECT COUNT(*) as total_rows FROM events"

# ── 3. Analyze ────────────────────────────────────────────────────
duckdb analysis.db -json -c "
    SELECT
        event_type,
        COUNT(*) as count,
        AVG(duration) as avg_duration,
        ROUND(100.0 * SUM(CASE WHEN error THEN 1 ELSE 0 END) / COUNT(*), 1) as error_rate
    FROM events
    WHERE timestamp > NOW() - INTERVAL 30 DAY
    GROUP BY event_type
    ORDER BY count DESC
"

# ── 4. Export ────────────────────────────────────────────────────
duckdb analysis.db -c "
    COPY (SELECT * FROM events WHERE error = true)
    TO 'error_events.parquet'
"

echo "✓ Analysis saved to error_events.parquet"
```

---

**Remember:** DuckDB is embedded, fast, and requires zero infrastructure. It's perfect for exploring data you own, understanding patterns, and making decisions based on facts. Use it whenever you have data to understand.
