Partition overwrite mode for Spark & Data Eng developers

The problem with full partition overwrites

Imagine you're running a daily ETL job that processes a month's worth of data and writes the results to a partitioned table. If your job reruns—whether due to a bug fix, schema change, or upstream delay—you're forced to overwrite the entire month, even though you only needed to recompute yesterday's data. That's wasteful, slow, and risky. Partition overwrite mode for Spark & Data Eng developers solves this by letting you rewrite only the partitions that actually changed, not the entire dataset.

In this article, we'll walk through how dynamic partition overwrite works, why it matters for production jobs, and how to combine it with idempotent design patterns to build safe, repeatable pipelines.

Understanding spark.sql.sources.partitionOverwriteMode=dynamic

By default, Spark uses STATIC partition overwrite mode. When you write to a partitioned table, Spark replaces all partitions that your write operation touches—regardless of what data you actually produced. This is the safest default for one-off queries, but it's inefficient for incremental jobs.

DYNAMIC mode flips the logic: Spark only overwrites the partitions that appear in your output data. If your job computes data for three days but writes to a monthly table, only those three day-partitions get replaced. The rest of the table remains untouched.

Enable it with a single configuration:

spark.sql.sources.partitionOverwriteMode=dynamic

Or set it in your SparkSession:

spark = SparkSession.builder \
    .config("spark.sql.sources.partitionOverwriteMode", "dynamic") \
    .getOrCreate()

This setting applies to all DataFrame writes that use the Parquet format (and some other formats supporting partitioned writes). Hive-syntax INSERT OVERWRITE also respects this mode.

Overwrite affected partitions only

The real value emerges when you rerun jobs. Let's walk through a concrete example.

You have a sales fact table partitioned by date_id. Your daily job processes the previous day's orders and writes results for that single day. In STATIC mode, Spark would overwrite all partitions your write touches—which, if your schema includes multiple partition columns or if your filtering logic is loose, could be many more partitions than intended.

With DYNAMIC mode enabled, Spark inspects your output data, identifies which partition values it contains, and overwrites only those. If your job produces data for 2024-01-15 only, only that partition gets replaced.

Here's a practical scenario:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, current_date, date_sub
import datetime

spark = SparkSession.builder \
    .config("spark.sql.sources.partitionOverwriteMode", "dynamic") \
    .appName("daily-sales-job") \
    .getOrCreate()

# Read raw orders from the previous day
yesterday = datetime.date.today() - datetime.timedelta(days=1)
orders_df = spark.read.parquet(f"s3://raw-data/orders/date_id={yesterday}")

# Transform and aggregate
sales_by_region = orders_df \
    .groupBy("region", "product_category") \
    .agg({"amount": "sum", "quantity": "sum"}) \
    .withColumn("date_id", col(lit(yesterday.strftime("%Y%m%d"))).cast("string"))

# Write with dynamic mode enabled
sales_by_region.write \
    .mode("overwrite") \
    .partitionBy("date_id") \
    .parquet("s3://warehouse/sales_by_region")

Even if the job runs twice in the same day, the second run produces the same date_id partition and overwrites only that partition. The historical data remains intact and unchanged.

Safe for daily reruns

Production jobs fail. Networks hiccup. Upstream data arrives late. When they do, you need to rerun your pipeline without cascading damage down the line.

With dynamic overwrite mode, daily reruns become much safer. Each run targets its own partition, so rerunning Wednesday's job doesn't accidentally blow away Thursday's or Tuesday's data. This is especially critical in organizations with dozens of interdependent jobs.

Consider a data quality scenario: your 2024-01-15 job runs successfully, but you later discover a bug in the transformation logic. You fix it and rerun the job. In static mode, you'd have to be very careful about which partitions you're targeting—a misconfiguration could overwrite January 14 or 16. In dynamic mode, the fix is straightforward: just rerun with the same date filter, and the correct partition gets updated.

This safety extends to schema changes too. If you add a new column to your output, dynamic mode still works: it overwrites only the partitions your current job produces, leaving older partitions with the old schema intact (assuming your read layer can handle schema evolution).

However, safety requires discipline. If your job logic drifts and starts producing data for multiple dates unexpectedly, dynamic mode will dutifully overwrite all of them. This is why idempotent design is crucial—not just a nice-to-have.

Combine with idempotent jobs

Idempotence means running the same job twice produces the same result as running it once. Dynamic partition overwrite mode amplifies the benefits of idempotent design.

To make a job idempotent, ensure that:

• Your input filter is explicit and deterministic (e.g., always process a specific date range, not "last 7 days running from job start time")
• Your output contains only the partition values you intend to write
• Your transformations are deterministic (no randomness, no calls to external APIs with side effects)
• You don't rely on incremental state or previous output (e.g., "add new rows to what's already there")

Here's an improved version of the earlier example:

def process_sales(spark, target_date):
    """
    Idempotent job: always processes exactly one date,
    produces deterministic output, overwrites its partition only.
    """
    # Explicit input: read exactly the date we're processing
    orders_df = spark.read.parquet(f"s3://raw-data/orders/date_id={target_date}")
    
    # Transform deterministically
    sales = orders_df \
        .filter(col("amount") > 0) \
        .groupBy("region", "product_category") \
        .agg({
            "amount": "sum",
            "quantity": "sum",
            "order_id": "count"
        }) \
        .withColumn("date_id", lit(target_date))
    
    # Write: dynamic mode ensures only this partition is overwritten
    sales.write \
        .mode("overwrite") \
        .partitionBy("date_id") \
        .parquet("s3://warehouse/sales_by_region")
    
    return sales.count()

# Run it: same call, same result, regardless of how many times
target = "20240115"
process_sales(spark, target)

The combination is powerful: idempotence guarantees the job is safe to rerun, and dynamic overwrite mode ensures reruns don't accidentally touch other partitions. Together, they let you build pipelines that are robust to failure and easy to recover from.

Handling multi-column partitioning

If your table is partitioned by multiple columns (e.g., year, month, day), dynamic mode still works correctly. Spark will overwrite only the combinations of partition values that appear in your output.

For example, if your table is partitioned by year, month, day and your job produces data for (2024, 01, 15), only that specific combination gets overwritten. Data for (2024, 01, 14) is untouched.

sales_df.write \
    .mode("overwrite") \
    .partitionBy("year", "month", "day") \
    .parquet("s3://warehouse/sales")

One caveat: if your output data contains partition values across a wide range (e.g., multiple months), dynamic mode will overwrite all of them. Make sure your input filtering is tight enough that you're not inadvertently producing data for unintended partitions.

Common mistakes

Forgetting to enable dynamic mode globally: If you're using a shared cluster or Databricks workspace, dynamic mode might not be the default. Always set it explicitly in your job configuration, or document that it must be set cluster-wide.

Loose input filters: If your job reads "all data newer than 30 days ago" and that time window shifts every run, you'll overwrite different partitions each time. Instead, parameterize your date range and pass it in explicitly.

Mixing static and dynamic writes: If some of your jobs use static mode and others use dynamic, it's easy to get confused about which partitions are being overwritten. Standardize on one approach (dynamic is almost always better for production).

Not testing idempotence: Just because dynamic mode is enabled doesn't mean your job is idempotent. Always test by running your job twice with the same inputs and verifying the output is identical.

Ignoring downstream dependencies: If your job feeds into other jobs, make sure they can handle a partition being rewritten. If they cache data in memory or rely on append-only semantics, a rewrite could cause issues.

Key takeaways

• Enable spark.sql.sources.partitionOverwriteMode=dynamic to rewrite only the partitions your job actually produces, not all partitions it touches.
• Dynamic mode makes daily reruns safer: if your job fails and you rerun it, only that day's partition gets updated, leaving historical data intact.
• Combine dynamic mode with idempotent job design: explicit input filters, deterministic transformations, and no reliance on external state.
• Always test idempotence by running your job twice with the same inputs and verifying the output doesn't change on the second run.
• For multi-partition tables, dynamic mode overwrites only the exact partition combinations present in your output, giving you precise control over what changes.
• Document and standardize on dynamic mode across your team; mixing static and dynamic approaches invites confusion and bugs.