Load — Writing to Warehouses or Downstream Files · Data Engineering Quest

The load stage is where idempotency matters most

Extract and transform can be retried freely — the inputs are read-only and the outputs are in-memory. Load writes to a destination that other people read from, which means a buggy load can corrupt downstream reports, dashboards, and ML training data. Get this stage wrong and the consequences leave your machine.

The four canonical write patterns

Replace — overwrite the entire target. Safest semantics; only viable for small datasets.
Append — add new rows. Simplest, but rerunning doubles rows. Forbidden in pipelines unless you have other dedup logic downstream.
Partition replace — replace a single partition (e.g. date=2026-04-30) as one unit. The default for time-partitioned warehouses.
Upsert — merge by primary key. Insert if new, update if exists. Cleanest semantics, requires destination support (Postgres ON CONFLICT, MERGE in warehouses).

"Atomic" is a word to spend carefully

Partition replace is usually described as an atomic swap. On a plain filesystem it is not. POSIX hands you exactly one atomic primitive here — renaming a single path — and it does not extend to a directory that already has contents. Try it and you get OSError with ENOTEMPTY — errno 66 on macOS and the BSDs, 39 on Linux, which is exactly why you check errno.ENOTEMPTY and never the bare number. So swapping a partition directory is always two operations. The only real question is which two.

Delete-then-rename is the version almost everyone writes first, and it is the worst ordering available: between those two lines the partition does not exist at all, and a process that dies in that gap has destroyed the old data without installing the new. Rename-aside-then-rename-in has a window of exactly the same length, but a crash inside it leaves the old partition sitting under a temp name where you can put it back. Same cost, recoverable failure. That is the whole trade, and it is worth taking every time.

It is also why the table formats exist. Iceberg, Delta Lake and Hudi do not try to swap directories. They write the new data files first, then commit by writing one small metadata file and moving a single pointer — because a single-path swap really is atomic. When your partitions get big enough that the window starts to matter, that is the upgrade. Not a cleverer rename.

Code

Partition replace — write to staging, then swap in the recoverable order·python

from pathlib import Path
import shutil
import pandas as pd

def load_partition(df: pd.DataFrame, root: Path, partition: str) -> Path:
    target = root / f'date={partition}'
    staging = root / f'.staging_{partition}'
    retired = root / f'.retired_{partition}'

    if staging.exists():
        shutil.rmtree(staging)
    staging.mkdir(parents=True)
    df.to_parquet(staging / 'part-0.parquet', index=False, compression='zstd')

    # Two renames, never a delete-then-rename. Between these two lines the
    # partition is missing either way — but here the old copy still exists
    # under .retired_*, so a crash is a recovery and not a data loss.
    if target.exists():
        if retired.exists():
            shutil.rmtree(retired)
        target.rename(retired)
    staging.rename(target)

    if retired.exists():
        shutil.rmtree(retired)
    return target

load_partition(df, Path('warehouse/orders'), '2026-04-30')

Upsert into Postgres with ON CONFLICT·python

import psycopg

def upsert_orders(rows: list[dict]) -> None:
    sql = '''
        INSERT INTO orders (order_id, customer_id, amount_usd, order_date, updated_at)
        VALUES (%(order_id)s, %(customer_id)s, %(amount_usd)s, %(order_date)s, NOW())
        ON CONFLICT (order_id) DO UPDATE
        SET customer_id = EXCLUDED.customer_id,
            amount_usd  = EXCLUDED.amount_usd,
            order_date  = EXCLUDED.order_date,
            updated_at  = NOW()
    '''
    with psycopg.connect('postgresql://...') as conn:
        with conn.cursor() as cur:
            cur.executemany(sql, rows)

Load — Writing to Warehouses or Downstream Files

The load stage is where idempotency matters most

The four canonical write patterns

"Atomic" is a word to spend carefully

Code

External links

Exercise

Progress

Comments 0