10,000 clients, one migration: Idempotent ETL taught me a lot

I've been building and scaling software for a while. One of the biggest challenges I ever took on was migrating [NUMBER] clients, with around 10,000 active ones, from an old system to a new platform. This wasn't just a simple data dump. It involved complex data transformations, syncing across multiple services, and ensuring zero downtime for our users.

That migration taught me a critical lesson: Idempotent ETL pipelines are non-negotiable. More than that, the ability to retry operations needs to be a core design decision, not an afterthought you tack on at the end.

The 10,000-Client Headache

Our old platform was showing its age. We needed to move client data, user accounts, historical transactions, and configuration settings to a new, more scalable architecture. This meant extracting data from an old SQL database, transforming it, and loading it into a mix of new SQL and NoSQL databases, along with several microservices.

We had to process millions of records for thousands of clients. Initially, we thought we could just write a script, run it, and fix issues as they came up. This quickly became a nightmare. Network glitches, database timeouts, and API rate limits were constant problems. When a job failed halfway through a client's migration, we had to figure out what had been processed, what hadn't, and how to restart without creating duplicates or corrupting data.

Manual fixes were time-consuming and error-prone. We spent more time cleaning up after failed runs than actually migrating data. It was clear we needed a better approach, and fast.

Idempotency is Your Best Friend

Idempotency means that an operation, when applied multiple times, produces the same result as applying it once. Think of setting a value: SET x = 5. If you run it ten times, x is still 5. That's idempotent. Adding to a value, like x = x + 1, is not. Running it ten times gives a different result than running it once.

For ETL, especially during a large migration, idempotency is vital. It means you can safely retry a failed step without worrying about creating duplicate records, incorrect states, or unintended side effects. If a client's migration fails at step three, you should be able to restart step three, or even the whole client's process, knowing it won't mess up what was successfully done before.

Without idempotency, every retry attempt carries the risk of data corruption. This makes your migration fragile and incredibly stressful to manage at scale.

Retries are a Design Choice, Not an Afterthought

Many engineers, myself included in the past, think about retries like this: "Oh, if it fails, I'll just put a try-except block around it and call the function again." This is a dangerous oversimplification. Just retrying without an idempotent design leads to problems.

Imagine you're inserting a new user record. If the insert fails, and you retry, you might end up with two identical user records if the first insert actually succeeded but the acknowledgment failed. This creates data consistency issues that are hard to debug and fix later.

Designing for retries means building your system so that operations can safely be repeated. This impacts how you define your data models, how you interact with databases, and how you design your API endpoints. It is a fundamental architectural decision, not an operational band-aid.

If you don't design for it, retries can lead to:

• Data corruption: Duplicates, partial updates, or incorrect states.
• Performance overhead: Unnecessary retries that consume resources without providing value.
• Debugging nightmares: It becomes impossible to trace why data looks wrong or why a process is stuck.
• System instability: Cascading failures if retries overwhelm dependent services.

Building Idempotent Pipelines

To make our migration robust, we had to rethink how we processed each client and each record. Here's what we focused on.

Unique Operation IDs

Every migration step, for every client and every specific data record, needed a unique identifier. This operation_id could be a combination of client_id and record_id. Before performing any action, we would check a central state store to see if this operation_id had already completed successfully.

If it had, we would skip the operation entirely or return the previously successful result. If it hadn't, we would proceed. This check-then-act pattern is fundamental to idempotency.

Atomic Operations

We ensured that each logical step of the migration was atomic. For database operations, this meant using transactions. Either all changes within a transaction committed successfully, or none did. If a transaction failed, we would roll back everything, leaving the system in a consistent state, ready for a retry.

This is crucial. You do not want to update half a record and then fail. That leaves your data in an inconsistent state, which is very hard to recover from.

Handling External Side Effects

When our ETL process called external APIs, we had to consider their idempotency too. If an external API was not idempotent, we had to wrap its calls with our own idempotent layer. This might involve tracking the success of the external call in our own database before marking our operation_id as complete.

Sometimes, this meant using a transactional outbox pattern. We would record the intention to call an external service in our database within our transaction. Then, a separate process would pick up these intentions and make the actual external calls, marking them as completed only after the external service confirmed success.

An Idempotent ETL Endpoint Example

Here's a simplified FastAPI example showing how you might design an endpoint for processing a migration item idempotently. The key is using a unique identifier for the operation and checking its status before proceeding.

from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
import time
import random
 
app = FastAPI()
 
# In a real application, this would be a database table or a distributed cache.
# It stores the status and result of processed migration items.
processed_items_status = {}
 
class MigrationItem(BaseModel):
    client_id: str
    item_id: str # Unique identifier for the item within a client
    payload: dict
 
@app.post("/process-migration-item")
async def process_migration_item(item: MigrationItem):
    # Create a unique key for this specific operation.
    # This key ensures idempotency across retries.
    operation_key = f"{item.client_id}-{item.item_id}"
 
    # Check if this operation has already been successfully processed.
    if operation_key in processed_items_status and processed_items_status[operation_key]['status'] == 'completed':
        print(f"Skipping already completed item: {operation_key}")
        return {
            "status": "success",
            "message": "Item already processed",
            "data": processed_items_status[operation_key]['result']
        }
 
    print(f"Attempting to process item: {operation_key}")
    # Mark the item as 'in_progress' to prevent concurrent processing issues if using a distributed lock.
    # For this simple example, we'll assume a single processor or rely on the final 'completed' state.
    processed_items_status[operation_key] = {'status': 'in_progress'}
 
    try:
        # Simulate a potential transient failure (e.g., network error, DB timeout).
        # A client retrying this will hit the 'in_progress' or retry the actual processing.
        if random.random() < 0.3: # 30% chance of failure
            raise RuntimeError("Simulated processing error.")
 
        # Simulate actual work being done, e.g., database writes, API calls.
        time.sleep(random.uniform(0.1, 0.5))
 
        # This is where your actual ETL logic goes.
        # For example, save item.payload to a database, call another service.
        result_data = {"processed_payload": item.payload, "timestamp": time.time()}
 
        # Crucial: Only mark as 'completed' AFTER all operations are successful.
        processed_items_status[operation_key] = {'status': 'completed', 'result': result_data}
 
        return {"status": "success", "message": "Item processed successfully", "data": result_data}
 
    except Exception as e:
        print(f"Error processing item {operation_key}: {e}")
        # If processing fails, do NOT mark as completed. This allows retries.
        # You might set a 'failed' status or remove 'in_progress' depending on retry strategy.
        if operation_key in processed_items_status and processed_items_status[operation_key]['status'] == 'in_progress':
            del processed_items_status[operation_key] # Or mark as 'failed' for dead-letter queuing
        raise HTTPException(status_code=500, detail=f"Failed to process item: {e}")

In this example, the operation_key and processed_items_status dictionary are key. A real system would use a database to store processed_items_status to persist across restarts and provide distributed consistency. When a client calls this endpoint, if it gets an error, it knows it can retry with the same client_id and item_id. The server's idempotent design ensures that successful operations are not duplicated.

On the client side, you would implement a retry mechanism, typically with exponential backoff and jitter. This means waiting a little longer after each failure, with some random variance, to avoid overwhelming the server and to give transient issues time to resolve.

Beyond the Code: Operational Wisdom

Building idempotent pipelines also means thinking about how you operate them. We learned that:

• Monitoring is essential: Track not just failures, but also successful retries. This gives you insight into the resilience of your system and helps identify underlying intermittent issues.
• Alerting: Set up alerts for high retry rates or when the maximum number of retries is exhausted for a particular item. This tells you when a transient error has become a persistent problem.
• Dead-Letter Queues: For items that consistently fail even after multiple retries, move them to a dead-letter queue. This prevents them from blocking the main pipeline and allows for manual inspection or separate processing later.

The Big Takeaway

The 10,000-client migration was a trial by fire. It forced me to confront the realities of building robust data pipelines at scale. The biggest lesson was that idempotency and retries are not optional features. They are fundamental design principles that must be baked into your architecture from the start.

Thinking about how your system will recover from failure, rather than just how it will succeed, makes all the difference. It saves countless hours of debugging, prevents data integrity issues, and ultimately builds a more reliable and trustworthy system.

Building for failure, especially for large-scale data movements, is the only way to ensure your migration, and any ETL process, runs smoothly and predictably.