Case Study: Data Formats in the Wild

The Question

You've now learned to work with three data formats: plain text, CSV, and JSON. But real applications use many more — XML, YAML, TOML, Protocol Buffers, Parquet, SQLite databases, and others. How do professional developers decide which format to use? And when is each one the right choice?

This case study examines how real-world applications make format decisions, and what trade-offs they navigate.

Format Profiles

Plain Text

What it is: Unstructured sequences of characters. No schema, no columns, no nesting — just text.

Real-world examples: - Server log files (Apache, Nginx) - Configuration files in older Unix tools - README files, changelogs - Output from command-line tools

Strengths: - Universal — every tool, every language, every OS can read it - Human-readable with no special tools - Append-friendly (great for logs)

Weaknesses: - No structure — parsing requires custom code - No schema validation - Not suitable for complex data

When to choose it: Logs, simple configuration, human-readable output, anywhere structure is unnecessary.

CSV (Comma-Separated Values)

What it is: Tabular data where each row is a line and columns are separated by a delimiter (usually a comma).

Real-world examples: - Spreadsheet exports (Excel, Google Sheets) - Financial data feeds (bank transaction exports) - Scientific datasets (research data repositories) - Government open data portals (data.gov, NHS Digital)

Strengths: - Excellent spreadsheet compatibility - Human-readable and editable in a text editor - Compact (no overhead for field names repeated per row) - Well-suited for streaming/line-by-line processing

Weaknesses: - Flat structure only — no nesting - No standard schema or type information (everything is a string) - Delimiter collisions (commas inside values require quoting) - No standard for representing missing data ("" vs. NA vs. empty field)

When to choose it: Tabular data with consistent columns, data exchange with spreadsheet tools, large datasets where compactness matters.

JSON (JavaScript Object Notation)

What it is: A text-based format for nested, hierarchical data structures. Looks like Python dictionaries and lists.

Real-world examples: - Web API responses (Twitter/X API, GitHub API, weather APIs) - Configuration files (VS Code settings.json, Node.js package.json) - NoSQL databases (MongoDB stores documents as JSON-like BSON) - Mobile app data (local storage, user preferences)

Strengths: - Supports nesting (objects within objects, arrays within objects) - Type-aware (strings, numbers, booleans, null) - Dominant format for web APIs — if you work with web data, you'll use JSON - Human-readable (with indentation)

Weaknesses: - Verbose for tabular data (field names repeated in every record) - No native date/time type (dates are stored as strings) - No comments allowed in standard JSON - Must load entire file to parse (no streaming by default)

When to choose it: API data, configuration files, nested/hierarchical data, any data that isn't purely tabular.

YAML (YAML Ain't Markup Language)

What it is: A superset of JSON designed for human readability. Uses indentation instead of braces.

Real-world examples: - Docker Compose files (docker-compose.yml) - CI/CD pipelines (GitHub Actions, GitLab CI) - Kubernetes configuration - Ansible playbooks

Strengths: - Extremely human-readable - Supports comments (unlike JSON) - Supports multi-line strings naturally - Superset of JSON — all valid JSON is valid YAML

Weaknesses: - Whitespace-sensitive (indentation errors are hard to spot) - Security concerns (YAML loaders can execute arbitrary code if not configured safely) - Multiple ways to represent the same data (inconsistency)

When to choose it: Configuration files that humans will edit by hand, especially in DevOps tools.

SQLite (Relational Database in a File)

What it is: A full SQL relational database engine stored as a single file on disk. No server required.

Real-world examples: - Mobile apps (iOS and Android use SQLite for local storage) - Web browsers (Firefox and Chrome store history, cookies, and bookmarks in SQLite) - Embedded systems and IoT devices - Python's sqlite3 module is in the standard library

Strengths: - Full SQL query support (SELECT, JOIN, WHERE, GROUP BY) - ACID-compliant transactions (data integrity guaranteed) - Handles millions of rows efficiently - Single file — easy to copy, back up, and distribute

Weaknesses: - Not human-readable (binary format) - Requires SQL knowledge - Not suitable for concurrent writes from multiple processes - Overkill for simple key-value storage

When to choose it: When your data is relational (multiple tables that reference each other), when you need complex queries, or when your data exceeds what CSV/JSON can handle comfortably (roughly 100,000+ records).

Decision Framework

Here's a practical decision tree for choosing a data format:

Is your data tabular (rows and columns, same fields for every record)?
├─ YES: How many records?
│   ├─ Under ~100,000: CSV
│   └─ Over ~100,000, or need complex queries: SQLite
│
└─ NO: Is it nested/hierarchical?
    ├─ YES: Will humans edit it by hand?
    │   ├─ YES: YAML (or JSON with comments via TOML)
    │   └─ NO: JSON
    │
    └─ NO: Is it just a log or notes?
        └─ YES: Plain text

This is a starting point, not a rulebook. Real decisions involve additional factors:

  • Who consumes the data? If the consumer is a web frontend, JSON is mandatory. If it's Excel, CSV wins.
  • How large is the data? CSV and line-by-line text scale well; JSON requires loading the entire structure.
  • How complex is the structure? A list of products with reviews and ratings is natural in JSON, awkward in CSV.
  • Do you need queries? If you're constantly asking "find all X where Y > Z," you need a database.
  • Is it configuration or data? Config files favor human-editability (YAML, TOML). Data files favor machine-parseability (CSV, JSON, SQLite).

Real-World Case Studies

Case 1: A Weather App

A weather app pulls forecast data from a public API. The API returns JSON:

{
  "city": "Portland",
  "forecast": [
    {"day": "Monday", "high": 52, "low": 38, "condition": "Rainy"},
    {"day": "Tuesday", "high": 55, "low": 40, "condition": "Cloudy"}
  ]
}

The app stores user preferences (units, default city) in a local JSON file. Historical temperature logs are stored in a SQLite database so the app can show "average temperature for this week over the past 5 years."

Format choices: JSON (API + preferences), SQLite (historical data). CSV was considered for historical data but rejected because the app needs queries like "average high temperature in January."

Case 2: A University Research Project

A biology lab processes DNA sequence data. Raw sequences come as plain text files (FASTA format). Experimental results are recorded in CSV spreadsheets. Analysis results are stored as JSON for the lab's custom visualization tool.

Format choices: Plain text (raw data — industry standard), CSV (experimental metadata — compatibility with Excel), JSON (analysis results — nested structure with varying fields per experiment). The lab considered using a database but decided the overhead wasn't justified for their scale (a few thousand experiments).

Case 3: A Small Business Inventory System

A small online store tracks products, orders, and customers. Initially, the developer stored everything in JSON files — one for products, one for orders, one for customers.

This worked fine with 50 products and a few orders per day. When the business grew to 5,000 products and hundreds of daily orders, the JSON approach became painfully slow: loading and re-saving a 10 MB JSON file on every order was taking 3-4 seconds.

The developer migrated to SQLite, which handles the load easily. The migration took two days — reading the JSON files, inserting records into SQLite tables, and rewriting the query functions.

Lesson learned: Start simple (JSON is fine for prototyping), but know when to graduate to a database. The threshold is usually when you find yourself loading and re-saving the entire dataset for every operation, or when you need queries that involve multiple tables.

The Format Evolution Pattern

Many applications follow a predictable evolution:

  1. Prototype: JSON or text files. Fast to implement, easy to debug.
  2. Growing pains: The files get too large, queries get too complex, or concurrent access becomes necessary.
  3. Migration to a database: SQLite for single-user apps, PostgreSQL or similar for multi-user systems.
  4. Full maturity: The database handles storage; JSON is used only for API communication and configuration.

Understanding file I/O isn't just about reading and writing files — it's about understanding the first two stages of this evolution, which is where most small-to-medium projects live.

Discussion Questions

  1. A social media app needs to store user profiles (name, bio, list of friends, list of posts). Would you choose CSV, JSON, or SQLite? What if the app has 100 users? What if it has 10 million?

  2. A data science team receives a 500 MB CSV file with 50 columns and 10 million rows. They need to filter it by three different criteria and join it with another dataset. What format would you recommend for their workflow, and why?

  3. Python's json module can't represent dates, sets, or custom objects. How is this a problem in practice, and what workarounds exist?

  4. YAML allows comments but JSON does not. Why do you think JSON's designers made that choice? What are the trade-offs?

Mini-Project

Write a "format converter" program that can: 1. Read a file in any of three formats (detect by extension: .txt, .csv, .json) 2. Convert it to any of the other two formats 3. Handle the limitations of each format (e.g., CSV can't represent nested structures — flatten them; plain text has no structure — use key: value pairs)

Test it with sample data that includes strings, numbers, and at least one nested list.