Error Handling, Logging, and Pipeline Restart

Why Error Handling Matters in ETL Pipelines

An ETL pipeline moves data through three stages: Extract (read from a source), Transform (clean and reshape), and Load (write to the target database). Any of these stages can fail—a network drop, a malformed row, a lost database connection. Without deliberate error handling, a failure mid-run can leave the target table in a partial state: some rows written, others missing, and no record of where things went wrong. Recovering from that situation manually is time-consuming and error-prone.

Structured error handling, combined with logging and a checkpoint restart mechanism, solves this problem. You know exactly what happened, where the pipeline stopped, and how to resume without writing duplicates or losing rows.

The Python logging Module

Python's built-in `logging` module lets you record pipeline events at named severity levels. The two levels you will use most in ETL work are:

• INFO — routine progress: pipeline start time, row counts after each stage, pipeline end time.
• ERROR — something went wrong: a rejected row, a caught exception, a failed database operation.

Configure a `FileHandler` that writes to a dated log file (for example, `pipeline_2024-11-15.log`). Set the format to include the timestamp, level, and message. Log row counts after every stage so that when something fails you can see exactly how far the pipeline progressed before the error.

Key practice: always log the start time at the very beginning and the end time only when all stages complete successfully. That gap in the log tells you immediately whether a run finished or not.

try/except Around the Load Stage

The load stage is the most critical to protect because it writes to the database. Wrap the load logic in a `try/except` block that catches `psycopg2.DatabaseError`. This is the base exception class for database-level errors in the psycopg2 adapter, covering connection failures, constraint violations, and server errors.

When the except block fires:

1. Call `connection.rollback()` immediately to undo any partial writes in the current transaction. This is your first data-safety action—never skip it.

2. Log the full exception message at ERROR level.

3. Write a checkpoint file containing the ID of the last batch that was fully committed before the failure. A simple JSON file works well: `{"last_committed_batch": 4, "timestamp": "2024-11-15T14:32:07"}`.

4. Exit the script with a non-zero return code (for example, `sys.exit(1)`). A non-zero exit code signals to any scheduler or calling process that the run failed.

Using database transactions correctly is also a safety concern for data integrity. Think of a transaction as an all-or-nothing unit: either every row in the batch is committed, or none of them are. Rolling back on error keeps the database in the last known good state.

Checkpoint Restart Logic

A checkpoint file stores the progress marker so the pipeline can resume rather than restart from scratch. At pipeline startup, add logic that checks whether a checkpoint file exists:

• If no checkpoint file is found, the pipeline starts from batch 1.
• If a checkpoint file is found, the pipeline reads `last_committed_batch`, skips all batches up to and including that number, and begins loading from the next batch.

After a successful run completes, delete or archive the checkpoint file so that the next scheduled run starts fresh.

This approach is called idempotent restart: running the pipeline again after a failure produces the same final result as a clean run, with no duplicates and no missing rows.

Preventing Duplicate Rows

Checkpoint restart alone does not guarantee no duplicates unless you also design the load stage correctly. Two common strategies:

• Upsert (INSERT … ON CONFLICT DO UPDATE): If a row with the same primary key already exists, update it instead of inserting a second copy. PostgreSQL supports this natively.
• Staging table: Load into a temporary staging table, then merge into the target table in a single SQL statement that handles duplicates.

During the hands-on activities you will confirm the final row count with a `SELECT COUNT(*)` query and check for duplicates with a `GROUP BY` on the primary key, looking for any count greater than one.

Physical and Environmental Safety

Although database work is office-based, standard lab safety applies:

• Keep cables managed and off the floor to prevent trip hazards.
• Follow your school's acceptable-use policy when connecting to shared database servers; do not run destructive commands on shared databases without instructor approval.
• Save your work frequently; an accidental power loss during a lab exercise is recoverable only if you have version-controlled your script.

Connecting to Industry Credentials

Error handling and logging are tested across all four credentials this program prepares you for. Oracle DBA CA and Microsoft DP-300 both address transaction management and recovery strategies. The DAMA CDMP Associate exam covers data quality and pipeline governance, which includes audit logging. EDB PostgreSQL CE tests transaction control and error handling in PostgreSQL specifically. Building these skills now prepares you for both the exams and real production work.