structured-logging

SQLite for Structured Data

Decision Check

Before writing any data analysis code, evaluate:

1. Will the data be queried more than once? -> Use SQLite
2. Are GROUP BY, COUNT, AVG, or JOIN operations needed? -> Use SQLite
3. Is custom Python/jq parsing code about to be written? -> Use SQLite instead
4. Is the dataset >100 records? -> Use SQLite

If the answer to any question above is YES, use SQLite. Do not write custom parsing code.

# Custom code for every query:
cat data.json | jq '.[] | select(.status=="failed")' | jq -r '.error_type' | sort | uniq -c

# SQL does the work:
sqlite3 data.db "SELECT error_type, COUNT(*) FROM errors WHERE status='failed' GROUP BY error_type"

Core Principle

SQLite is just a file -- no server, no setup, zero dependencies. Apply it when custom parsing code would otherwise be written or data would be re-processed for each query.

When to Use SQLite

Apply when ANY of these conditions hold:

• >100 records -- JSON/grep becomes unwieldy
• Multiple aggregations -- GROUP BY, COUNT, AVG needed
• Multiple queries -- Follow-up questions about the same data are expected
• Correlation needed -- Joining data from multiple sources
• State tracking -- Queryable progress/status over time is needed

When NOT to Use SQLite

Skip SQLite when ALL of these are true:

• <50 records total
• Single simple query
• No aggregations needed
• No follow-up questions expected

For tiny datasets with simple access, JSON/grep is fine.

Red Flags -- Use SQLite Instead

STOP and use SQLite when about to:

• Write Python/Node code to parse JSON/CSV for analysis
• Run the same jq/grep command with slight variations
• Write custom aggregation logic (COUNT, AVG, GROUP BY in code)
• Manually correlate data by timestamps or IDs
• Create temp files to store intermediate results
• Process the same data multiple times for different questions

All of these mean: Load into SQLite once, query with SQL.

The Threshold