Your Python Script Works on 10K Rows. What Happens at 10 Million?

Python script can look perfectly efficient when you test it with 10,000 records.

Then production sends 10 million.

Suddenly:

memory consumption increases dramatically
processing becomes slower
one bad record can interrupt an entire run
database and API calls become bottlenecks
code that looked harmless during development starts behaving very differently
This is an important transition for Data Engineers.

The question is no longer:

Does the Python code work?

The better question becomes:

Will the same design still work when the data becomes much larger?

Let's look at five Python patterns that become increasingly important as data volume grows.

1. Be Careful About Loading Everything Into Memory Consider this simple transformation:

processed_records = [
transform(record)
for record in records
]
There is nothing inherently wrong with this code.

For a relatively small dataset, it may be exactly what you need.

The problem appears when records becomes very large.

A list comprehension builds the resulting list in memory. If millions of transformed objects are produced, memory usage can increase quickly.

One alternative is a generator expression:

processed_records = (
transform(record)
for record in records
)
The difference looks tiny.

The execution model is not.

A generator produces values lazily instead of constructing the complete result immediately.

For example:

def transformed_records(records):
for record in records:
yield transform(record)
Now we can process records progressively:

for record in transformed_records(records):
write_to_destination(record)
This leads to an important Data Engineering principle:

Don't keep data in memory longer than necessary.

Generators are not automatically faster, and they are not the solution to every performance problem.

But when the workflow is naturally sequential, lazy evaluation can significantly reduce memory pressure.

1. Don't Read a Large File All at Once Unless You Need To This pattern is convenient:

with open("customers.csv") as file:
rows = file.readlines()
But readlines() loads the entire file into memory.

For a small file, nobody cares.

For a multi-gigabyte file, the decision becomes much more important.

Python file objects are iterable, so we can process a file progressively:

with open("customers.csv") as file:
for row in file:
process(row)
Now the application doesn't need the entire file available in memory simultaneously.

A slightly more realistic version might be:

import csv

with open("customers.csv", newline="") as file:
reader = csv.DictReader(file)

for record in reader:
    transformed = transform(record)
    write_to_destination(transformed)

This pattern works particularly well when the pipeline is:

Read → Transform → Write

and each record can be processed independently.

The important lesson is not simply:

"readlines() is bad."

It isn't.

The real lesson is:

Understand the memory implications of the operation you're choosing.

1. Sometimes Batch Processing Is Better Than Row-by-Row Processing Processing one record at a time reduces memory usage.

But it can create another problem.

Imagine inserting one million records into a database individually.

for record in records:
insert_into_database(record)
Even if the Python processing is efficient, one million database round trips can make the pipeline painfully slow.

This is where batching becomes useful.

A simple batch generator could look like this:

def create_batches(items, batch_size):
batch = []

for item in items:
    batch.append(item)

    if len(batch) == batch_size:
        yield batch
        batch = []

if batch:
    yield batch

Now:

for batch in create_batches(records, 1000):
insert_batch(batch)
Instead of:

1,000,000 individual writes

we potentially perform:

1,000 writes containing 1,000 records each.

The exact numbers depend on the system, but the principle is extremely useful.

Batching is common when working with:

database inserts
REST APIs
message queues
object storage
ETL transformations
external services
However, larger batches are not automatically better.

Very large batches may:

consume more memory
increase transaction size
increase retry cost
cause API payload limits
create longer-running database locks
Good Data Engineering often means finding the correct balance.

1. At Scale, Failure Handling Becomes Part of the Design Suppose we process 10 million records and record number 7,452,819 contains invalid data.

Should the entire pipeline fail?

Sometimes yes.

Sometimes absolutely not.

This code is dangerous:

for record in records:
process(record)
because one unexpected exception can terminate the complete loop.

A better design might be:

import logging

logger = logging.getLogger(name)

for record in records:
try:
process(record)

except ValueError as error:
    logger.warning(
        "Invalid record %s: %s",
        record.get("id"),
        error,
    )

But even this is only the beginning.

Production pipelines need a clear failure strategy.

For every failure, ask:

Should we retry it?
Useful for temporary network or service failures.

Should we skip it?
Possibly appropriate for non-critical malformed records.

Should we quarantine it?
Store invalid records separately so they can be investigated later.

Should the pipeline fail completely?
Sometimes data integrity is more important than availability.

For example, silently skipping invalid financial transactions could be much worse than stopping the pipeline.

So good exception handling is not:

except Exception:
pass
That hides problems.

Instead, failures should provide enough context to understand:

what failed → why it failed → what happened to the data → whether processing continued

1. Know When Plain Python Is No Longer the Right Tool This is probably the most important point.

Suppose you've already improved:

memory usage
file processing
batching
database operations
error handling
But the workload continues growing.

10 million rows becomes 100 million.

Then 500 million.

Eventually the question changes.

It is no longer:

How can I optimize this Python loop?

It becomes:

Should this workload still run inside one Python process on one machine?

That is where distributed processing systems become relevant.

A tool such as PySpark can divide a dataset into partitions and process those partitions across multiple executors.

Conceptually:

Large Dataset

    ↓

Partition 1 → Executor 1
Partition 2 → Executor 2
Partition 3 → Executor 3
Partition 4 → Executor 4

    ↓

Combined Result
Instead of asking one process to do all the work, we distribute the workload.

But this does not mean:

Big dataset = always use Spark.

Distributed computing introduces its own costs:

cluster infrastructure
serialization
network communication
data shuffling
partition management
scheduling overhead
operational complexity
For some workloads, well-written Python running on one machine is more than sufficient.

For others, distributed processing becomes necessary.

Knowing when to make that transition is an important Data Engineering skill.

A Simple Experiment You Can Try
Instead of only reading about these patterns, test them.

Generate a large dataset:

records = range(10_000_000)
Then compare several approaches.

Approach 1 — Build a List
results = [
value * 2
for value in records
]
Approach 2 — Use a Generator
results = (
value * 2
for value in records
)
Then consume it:

for value in results:
pass
Observe:

memory consumption
execution time
when computation occurs
how iteration behaves
Next, experiment with batching.

for batch in create_batches(records, 1000):
process_batch(batch)
Try:

100
1,000
10,000
100,000
as different batch sizes.

Don't just ask which one is fastest.

Ask:

Why does the behaviour change?

That question usually teaches more than the benchmark itself.

Scaling Is Usually About Trade-offs
One of the biggest lessons I've learned from Data Engineering is that performance problems rarely have one universal solution.

You might optimize memory and increase CPU usage.

You might increase batch size and increase retry cost.

You might introduce parallelism and increase complexity.

You might move to Spark and discover that the workload was too small to justify a distributed engine.

Engineering is about understanding those trade-offs.

The same code can be:

perfectly acceptable for 10K rows

and

completely inappropriate for 100 million rows.

Context matters.

The Mental Model I Use
When a Python data-processing workload starts growing, I think about it in roughly this order:

Can I avoid unnecessary work?

    ↓

Can I process data lazily?

    ↓

Can I stream instead of loading everything?

    ↓

Can I batch expensive operations?

    ↓

Can I reduce I/O or database round trips?

    ↓

Can I parallelize safely?

    ↓

Does the workload now justify
distributed processing?
Jumping directly to Spark isn't always the solution.

But trying to force every workload through a single Python process isn't the solution either.

The important skill is recognizing where that boundary lies.

Final Thought
Writing Python that produces the correct output is the first step.

Writing Python that remains reliable when:

data grows, failures occur, memory becomes constrained, external systems slow down, and requirements change

is a different level of engineering.

So the next time a Python pipeline works perfectly with 10,000 records, try asking:

What happens if tomorrow this becomes 10 million?

That one question can completely change the way you design the solution.

Challenge
Take one of your existing Python data-processing scripts and answer these five questions:

Does it load the complete dataset into memory?
Could any operation be processed lazily?
Are database/API operations executed individually when they could be batched?
What happens when one record fails?
At what data volume would you consider moving the workload to a distributed processing engine?
I'd be interested to hear how you approach the Python → distributed processing transition in real Data Engineering projects.