Using Sqlite

Using SQLite

Overview

SQLite is the primary database for DataPeeker analysis sessions. This skill provides task-oriented guidance for common SQLite operations during data analysis.

Core principle: Explore schema first, format output for readability, write efficient queries, verify all operations.

When to Use

Use this skill when you need to:

• Explore an unfamiliar database schema
• Query data for analysis or hypothesis testing
• Import CSV files into SQLite tables
• Format query output for readability
• Diagnose slow queries or optimize performance
• Understand which CLI invocation pattern to use

When NOT to use:

• Database-agnostic data profiling (see understanding-data skill for patterns that work across all SQL databases)
• Complex data cleaning logic (delegate to cleaning-data skill + sub-agents)
• Statistical analysis (use Python/pandas for advanced statistics)
• Large-scale transformations (use Python sqlite3 module)

DataPeeker Conventions

Database Path:    data/analytics.db (relative from project root)
Table Naming:     raw_* for imported data, clean_* for cleaned data
Single Database:  All tables in one file per analysis session

Example workflow:

CSV file → raw_sales → clean_sales → Analysis queries

Quick Reference

Task-Oriented Guidance

Exploring Schema

Before writing queries, understand the database structure.

See @./exploring-schema.md for:

• Listing all tables (.tables)
• Viewing table structure (.schema, PRAGMA table_info)
• Understanding column types and constraints
• Checking for indexes

When: Starting analysis, unfamiliar database, before writing joins

Formatting Output

Make query results readable for analysis.

See @./formatting-output.md for:

• Output modes (column, csv, json, markdown)
• Showing/hiding headers (.headers on/off)
• Setting column widths for readability
• Redirecting output to files

When: Query results hard to read, need specific format for export, preparing reports

Writing Queries

SQLite-specific query patterns and conventions.

See @./writing-queries.md for:

• SQLite idioms used in DataPeeker (COUNT(*) - COUNT(col) for NULLs)
• Date handling with STRFTIME
• DataPeeker percentage calculation conventions
• Common verification queries

See also: writing-queries and understanding-data skills for database-agnostic SQL patterns and data profiling approaches. This guidance focuses on SQLite-specific syntax, CLI usage, and optimizations.

When: Need SQLite-specific syntax, DataPeeker query conventions, date formatting with STRFTIME

Importing Data

Load CSV files and verify import success.

See @./importing-data.md for:

• Using .import command
• Verification queries (row counts, sample data)
• When to use CLI vs Python sqlite3
• Transaction handling

When: Loading new data, Phase 4 of importing-data skill, verifying data loaded correctly

Optimizing Performance

Diagnose and fix slow queries.

See @./optimizing-performance.md for:

• EXPLAIN QUERY PLAN analysis
• Creating indexes for common queries
• Using transactions for bulk operations
• PRAGMA optimization

When: Query takes >1 second, loading large datasets, repeated similar queries

Invoking CLI

Choose the right method to run sqlite3 commands.

See @./invoking-cli.md for:

• Interactive mode (for exploration)
• Heredoc pattern (for multi-command scripts)
• File redirect (for SQL files)
• One-liner mode (for quick checks)

When: Starting any SQLite operation, unsure which invocation to use

Common Mistakes

Verification Before Proceeding

After any import operation:

-- 1. Verify row count matches expectation
SELECT COUNT(*) FROM raw_table;

-- 2. Check sample data looks correct
SELECT * FROM raw_table LIMIT 5;

-- 3. Verify no unexpected NULLs
SELECT COUNT(*) - COUNT(critical_column) FROM raw_table;

After writing a complex query:

-- 1. Check query plan
EXPLAIN QUERY PLAN SELECT ...;

-- 2. Time the query
.timer on
SELECT ...;

Real-World Impact

Systematic approach prevents:

• Writing queries against wrong tables (explore schema first)
• Unreadable output (format before analyzing)
• Slow queries (diagnose with EXPLAIN QUERY PLAN)
• Silent data loss (verify imports)
• Incorrect percentages (use float division)

Following this skill:

• Reduces query debugging time by 50%+
• Catches import issues immediately
• Produces readable analysis artifacts
• Ensures reproducible workflows