How I Made My Data Pipeline Idempotent

When building my Job Market Intelligence Platform, I encountered a common engineering problem early on: what happens if the Databricks workflow is executed twice in the same day?

The Duplicate Problem

If you simply append API data into a Delta table, rerunning the pipeline will ingest the exact same job listings, ruining the accuracy of downstream analytics. You'll suddenly have double the demand for Python engineers simply because a task ran twice.

The Solution: MERGE-Based Upserts

To make the pipeline idempotent (meaning it can be run multiple times safely without altering the final state beyond the initial run), I implemented MERGE INTO logic in PySpark.

# Conceptual logic used in the Silver layer
deltaTable.alias("target").merge(
    df_updates.alias("updates"),
    "target.job_id = updates.job_id"
).whenMatchedUpdateAll() \
 .whenNotMatchedInsertAll() \
 .execute()

By matching on a unique job_id, the pipeline either updates existing records (if job details changed) or inserts new ones. Safe, reliable reruns are now built directly into the architecture.