Where Data Lives — CSV, JSON, APIs, and Databases Explained

What You Will Learn

• The four most common places data lives: files, JSON, APIs, and databases
• What each format looks like on the inside
• When you will encounter each one as an analyst
• How to choose between them when storing your own data

Why This Topic Matters

Every analysis starts with getting the data. Sometimes you receive a file. Sometimes you query a database. Sometimes you call an API. If you do not recognize what you are looking at, you cannot even begin. This lesson is the map of where data lives so you always know where to look.

The Four Homes of Data

1. CSV — The Universal Simplest Format

CSV stands for Comma-Separated Values. It is plain text. Each row is one line, and columns are separated by commas. The first row is usually the header.

Example CSV

order_id,customer,city,amount
001,Anita,Mumbai,500
002,Ravi,Chennai,1200
003,Mira,Kolkata,750

Pros

• Opens in Excel, Google Sheets, Python, R, SQL loaders — everywhere
• Human-readable
• Small file size for simple data

Cons

• Cannot represent nested data (e.g., an order with multiple items)
• Commas inside values break the format unless quoted
• No type information — "500" might be a number or text until you decide

2. JSON — For Nested and Flexible Data

JSON stands for JavaScript Object Notation. It stores data as labeled values, and values can themselves be lists or objects, which lets you represent nested structures.

Example JSON

{
  "order_id": "001",
  "customer": {
    "name": "Anita",
    "city": "Mumbai"
  },
  "items": [
    {"name": "Notebook", "qty": 2, "price": 80},
    {"name": "Pen",      "qty": 5, "price": 20}
  ],
  "total": 260,
  "ordered_at": "2026-08-23T14:30:00Z"
}

Pros

• Handles nested data naturally (an order with many items)
• Universally supported by web APIs and modern databases
• Lightweight and human-readable

Cons

• Harder to read as a flat table — you must flatten it first
• Schemas can drift (one order has 2 items, another has 7)
• More verbose than CSV for simple tabular data

When you see JSON

• Coming from a web API → see below
• Stored in a NoSQL database like MongoDB
• Exported from a SaaS tool (Stripe, Shopify, etc.)

3. REST API — Live Data Over the Web

An API (Application Programming Interface) is a service that gives you data when you ask for it. A REST API works like a website: you send a request to a URL, and instead of getting an HTML page back, you get structured data (usually JSON).

Real example: Open-Meteo weather API

The Open-Meteo service (https://open-meteo.com/) provides free weather data with no API key required. If you visit this URL in a browser:

https://api.open-meteo.com/v1/forecast?latitude=19.07&longitude=72.87¤t=temperature_2m

You get back JSON like:

{
  "current": {
    "temperature_2m": 28.4,
    "time": "2026-08-23T14:30"
  }
}

That is data fetched live from a service. No file to download — you ask, it answers.

How analysts use APIs

• Pull daily metrics from a SaaS tool (Stripe payments, Shopify orders)
• Enrich data (look up a city's weather or coordinates from a postcode)
• Automate reporting (a script runs nightly, fetches fresh data, updates a dashboard)

4. Databases — Structured Storage at Scale

A database is software that stores data and lets you query it efficiently. The most common type for analytics is the relational database, which stores data in tables and lets you query them with SQL.

Why use a database instead of CSV?

Common relational databases

• PostgreSQL — open source, feature-rich, widely used in analytics
• MySQL — open source, common in web apps
• SQLite — a single-file database, perfect for learning and small projects
• Microsoft SQL Server — common in enterprises

You will write SQL against any of these starting in lesson 17 — SQL SELECT. The SQL you learn works on all of them with minor dialect differences.

How Data Moves Between Homes

Data rarely stays in one place. A typical journey:

1. A user places an order on a website → the web app sends the order to a database.
2. A reporting script calls an API on the same server to fetch today's orders as JSON.
3. The script flattens the JSON into a CSV file for analysts.
4. An analyst opens the CSV in Excel or pandas to study it.

Understanding all four homes means you can work with data at any step of that journey.

Common Mistakes

1. Treating JSON like a flat table. Nested objects must be flattened before analysis. See lesson 25.
2. Storing large data in Excel. Excel struggles above ~1 million rows. Use a database.
3. Hard-coding API URLs in scripts. If the API changes, your script breaks. Use configuration files.
4. Assuming CSV types are correct. CSVs have no types. Excel may guess "001" is the number 1, losing leading zeros.
5. Forgetting APIs can change. APIs can rate-limit, change format, or go offline. Always handle errors gracefully.

Practical Exercise (10 minutes)

1. Open a new text file. Type three lines of CSV with columns name,city,amount and three rows of fake data. Save it as test.csv. Open it in Excel — it should appear as a table.
2. Visit the Open-Meteo URL above in your browser. Confirm you get JSON back with a temperature value.
3. If you have a smartphone, open any app's settings and look for "Export data" or "Download my data" — that exported file is almost always CSV or JSON.

Mini Challenge

Find one real CSV file and one real JSON file from your own life (exports from apps work well). Compare them: which is easier for a human to read? Which handles nested information better? Write down your answer in one sentence each. There is no "right" answer — the point is to notice the trade-off.

Key Takeaways