How to Handle Large Datasets

Step 1 — Move it into a database, not a spreadsheet

The single most important move: stop trying to open large data in a spreadsheet. Put it in a database instead. SQLite (via the free DB Browser app) has no practical row limit. The same file that crashed Excel becomes a table you can query in seconds.

This is exactly the create, import, verify workflow from the SQL database guide. The steps are identical for a big file. It just takes longer, which is what the rest of this guide prepares you for.

Step 2 — Expect the download to be zipped (with extras you don't need)

Large public datasets almost never download as a single clean CSV. They arrive as a .zip (often named something generic like archive.zip) that contains several files bundled together. In our example the zip held four files, and we only needed one.

1. Right-click the downloaded zip and choose Extract All.
2. Open the extracted folder and use only the file you actually need (for us, recommendations.csv). Ignore the rest.

Step 3 — Import is slow, and the file grows. That's normal.

A 2 GB CSV does not import instantly. Give it time. Two things surprise people, and both are expected:

• The import takes minutes, not seconds. DB Browser may look frozen while it works. Let it finish.
• Your .db file grows to several gigabytes. A 2 GB CSV can produce a multi-GB database. Make sure you have the free disk space before you start: the original CSV plus the database can easily total 4 to 6 GB.

Import the file exactly as in the SQL guide (name the table something ending in _raw, tick "column names in first line"), then press Ctrl+S to save.

Step 4 — Build an index (the step that makes big queries fast)

An index is a lookup shortcut on a column. Without one, every time you search or join on that column, the database reads all 41 million rows from top to bottom. With one, it jumps straight to the rows it needs. On a small table you never notice. On a big one, it is the difference between instant and unusable.

Build the index once, on the column you will join or filter on most (usually an id):

CREATE INDEX IF NOT EXISTS idx_rec_appid ON recommendations(app_id);

Step 5 — Write your query against a sample first

You do not want to wait on 41 million rows every time you fix a typo in your query. Develop against a small slice, then run the finished query on everything.

• Add LIMIT 1000 while you are writing and testing, so results come back instantly.
• Or keep a small sample CSV (a few thousand rows) as a separate table for building queries.

Once the query is correct on the sample, remove the LIMIT and run it for real.

Step 6 — Aggregate early, and never dump millions of raw rows to the screen

Asking a tool to display 41 million rows is what freezes it. You almost never want the raw rows anyway, you want a summary. Use GROUP BY to collapse the data to one row per thing you care about:

SELECT app_id, COUNT(*) AS num_reviews, AVG(hours) AS avg_hours
FROM recommendations
GROUP BY app_id;

That turns 41 million review rows into one tidy row per game. Reach for COUNT, AVG, SUM, and GROUP BY before you ever reach for SELECT *.