Load — Writing to Warehouses or Downstream Files

~12 min · etl, load, idempotency

Level 0Curious Reader

0 XP0/47 lessons0/11 achievements

0/120 XP to next level120 XP to go0% complete

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) atomically. 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).

Code

Atomic partition replace — write to staging, then swap·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}'

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

    if target.exists():
        shutil.rmtree(target)
    staging.rename(target)
    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)

External links

Exercise

Implement a partition-replace loader that writes to warehouse/<table>/date=<partition>/part-0.parquet via a staging directory. Run it twice for the same partition — the second run should produce identical output and leave nothing extra on disk.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousTransform — Cleaning, Casting, Deriving Next →Polars — When Pandas Hits a Wall

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.