Learn how to efficiently process a large CSV file in daily chunks without re-reading the whole file. We’ll explore simple state tracking, robust database solutions, and cloud-native patterns for stateful, repeatable data processing.

From the Trenches: How to Process a Giant CSV 50 Lines at a Time

I remember it vividly. It was a Tuesday morning, and the PagerDuty alert for “High CPU on prod-web-03” blared. A junior engineer had written a simple Bash script to parse our access logs for marketing metrics. The problem? The log file was 20GB and growing, and their script was re-reading the entire file from the beginning every five minutes. The server was spending all its time just trying to find the last few new lines. We’ve all been there—staring at a seemingly simple task that hides a nasty performance bottleneck. That Reddit thread about processing a CSV 50 lines a day brought that memory right back. It’s a classic “how do I make my script remember what it did yesterday?” problem.

The Root of the Problem: Stateless Thinking

The core issue here is statelessness. By default, a script is like a goldfish; it has no memory of its previous runs. When your cron job kicks off on Tuesday, it has no idea what it processed on Monday. So, its only option is to start from line 1 all over again. This is fine for tiny files, but for a large CSV, it’s incredibly wasteful in terms of I/O, CPU, and time. The goal of any good solution is to introduce state—a memory of the work that has already been completed.

Solution 1: The ‘Get It Done’ Approach: The Humble State File

This is the quick and dirty fix. It’s the digital equivalent of leaving a sticky note on your monitor for your future self. The idea is to use a separate, tiny file to store one piece of information: the last line number you successfully processed.

How It Works

1. Your script starts and looks for a state file, let’s call it .last_processed_line.
2. If the file doesn’t exist, it assumes it’s the first run and starts from line 1.
3. If the file exists, it reads the number from it (e.g., 150). This is its starting point.
4. The script opens the big CSV, skips to line 151, and processes the next 50 lines (up to line 200).
5. Once it’s successfully done, it overwrites the state file with the new last line number: 200.

Here’s a conceptual Python snippet:

# config
STATE_FILE = '.last_processed_line'
CSV_FILE = 'my_giant_data_file.csv'
CHUNK_SIZE = 50

# 1. Read the last processed line
start_line = 0
try:
    with open(STATE_FILE, 'r') as f:
        start_line = int(f.read().strip())
except FileNotFoundError:
    print("State file not found, starting from the beginning.")

# 2. Process the data in chunks
# (Note: This is pseudo-code; for real files, use libraries
# that handle large files efficiently without loading all into memory)
print(f"Starting processing from line {start_line + 1}")
# ... logic to open CSV_FILE, skip to start_line, and read CHUNK_SIZE lines ...
lines_processed = 50 # Assume we processed 50 lines
new_last_line = start_line + lines_processed

# 3. Write the new state back to the file
print(f"Finished processing. Updating state to line {new_last_line}.")
with open(STATE_FILE, 'w') as f:
    f.write(str(new_last_line))

Darian’s Take: I’ve used this method for quick, one-off tasks. It works. But it’s fragile. If the script crashes after processing but before updating the state file, you’ll re-process data. Worse, if two scripts run at once, you’ll get a race condition and things will get messy. Use it, but know its limitations.

Solution 2: The ‘Do It Right’ Method: Ingest and Track

Stop treating a file like a database. A CSV is a data transport format, not a data management system. For any process that’s going to be a permanent part of your infrastructure, the right move is to get that data into a proper database where you can manage state reliably.

The Two-Step Process

Step 1: One-Time Ingestion
Write a simple, one-off script to load the entire CSV into a database table (PostgreSQL, MySQL, even SQLite if it’s a single-server job). Critically, add two columns: an auto-incrementing `id` and a `processed_at` timestamp, which defaults to `NULL`.

CREATE TABLE customer_data (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255),
    -- ... other columns from your CSV ...
    processed_at TIMESTAMP NULL DEFAULT NULL
);

-- Then use a bulk-loading command like COPY in Postgres
-- COPY customer_data(first_name, ...) FROM '/path/to/my_giant_data_file.csv' WITH (FORMAT CSV, HEADER);

Step 2: The Daily Job
Your daily cron job is now dead simple and robust. It no longer touches the CSV. Instead, it queries the database.

-- 1. Select the next batch of unprocessed rows
SELECT id, email FROM customer_data WHERE processed_at IS NULL ORDER BY id LIMIT 50;

-- 2. After your script processes a row with a specific ID (e.g., id=123), it updates it
UPDATE customer_data SET processed_at = NOW() WHERE id = 123;

This approach is transactionally safe. If your script fails halfway through a batch, the rows it didn’t get to will still have `processed_at` as `NULL`, and they’ll be picked up on the next run. No duplicates, no lost data.

Darian’s Take: This is my default answer for 90% of these problems. It’s how production systems are built. Our main database cluster, `prod-db-01`, handles terabytes of this kind of transactional work without breaking a sweat. It’s auditable, scalable, and easy to reason about.

Solution 3: The Cloud Architect’s Answer: Queues and Events

Sometimes, the problem isn’t just one big file. It’s a symptom of a larger architectural issue. If you’re frequently getting large data dumps that need batch processing, it might be time to stop thinking in terms of “files” and “cron jobs” and start thinking in terms of “events” and “pipelines.”

The Event-Driven Mindset

Instead of a monolithic CSV, what if each row was a message?

1. Producer: A one-time script reads your giant CSV and pushes each line as a distinct message into a message queue like AWS SQS, Google Pub/Sub, or RabbitMQ.
2. Queue: The queue holds these messages durably. It doesn’t care when they get processed.
3. Consumer: A worker process (like an AWS Lambda function or a container running on Kubernetes) is configured to pull messages from the queue. You can tell it, “fetch up to 50 messages at a time.” It processes them and, upon success, tells the queue to delete them.

This decouples the “ingestion” of data from the “processing” of data. The “daily” requirement just means you let the consumer run for a while each day or you scale your consumers up and down on a schedule.

Darian’s Take: Is this overkill for a single CSV? Absolutely. But if the marketing team is going to drop a 10GB file on your SFTP server every week, building this kind of event-driven pipeline will save you a world of pain in the long run. It’s a pattern that scales almost infinitely and is incredibly resilient to failure.

Tying It All Together

Choosing the right tool for the job is the hallmark of a senior engineer. There’s no single “best” answer, only the best answer for your specific context—your timeline, your infrastructure, and your future needs.

So next time you’re faced with a giant file, take a step back. Don’t just ask “how do I read 50 lines?” Ask “how do I reliably track the work I’ve done?” The answer will lead you to a much better solution.

Darian Vance

Lead Cloud Architect & DevOps Strategist

With over 12 years in system architecture and automation, Darian specializes in simplifying complex cloud infrastructures. An advocate for open-source solutions, he founded TechResolve to provide engineers with actionable, battle-tested troubleshooting guides and robust software alternatives.

🤖 Frequently Asked Questions