Archiving Processed CSV Files Automatically After Import

A job that loads a CSV has not finished until it has decided what to do with the file. The three bad answers are all common. Leave it in the inbox, and the next run picks it up again. Delete it, and the only evidence of what arrived is gone the first time someone asks why a number changed. Move it before the load commits, and a failed load leaves neither the rows nor the file. The good answer is a short routine, run after the commit, that stores the original bytes somewhere cheap and orderly, proves the copy is sound, and lets old ones expire. It is the last stage of the importer in automating CSV imports into a database.

Order matters

The sequence has to be: load, commit, archive, delete the source. Consider the failures at each gap. If the archive step runs before the commit and the commit fails, the file is in the archive and the database has nothing, so a rerun has no input. If the source is deleted before the archive copy is verified and the copy is bad, the data is gone. If the archive step fails after a commit, the rows are loaded and the file remains in the inbox, and the next run sees a known hash in the ledger, skips the load and retries the archive. That last case is harmless, which is why archiving goes after the commit, and why the ledger keys on content.

The rule that falls out is simple: the original is deleted last, and only after something verified has taken its place.

A small archiver

Here is a function that does the steps in order: compress into a dated folder under a temporary name, verify, rename, delete the source.

import gzip, hashlib, shutil, sys
from datetime import date
from pathlib import Path

def archive(src: Path, root: Path, loaded_on: date) -> Path:
    """Compress a processed file into archive/YYYY/MM/ and verify the copy."""
    dest_dir = root / f"{loaded_on:%Y}" / f"{loaded_on:%m}"
    dest_dir.mkdir(parents=True, exist_ok=True)
    dest = dest_dir / (src.name + ".gz")
    if dest.exists():
        dest = dest_dir / f"{src.stem}-{loaded_on:%H%M%S}{src.suffix}.gz"
    tmp = dest.with_suffix(dest.suffix + ".part")
    with open(src, "rb") as fi, gzip.open(tmp, "wb") as fo:
        shutil.copyfileobj(fi, fo)
    if sha(src) != sha_gz(tmp):
        tmp.unlink()
        raise RuntimeError(f"archive of {src.name} does not match the original")
    tmp.rename(dest)
    src.unlink()
    return dest

def sha(p):
    return hashlib.sha256(Path(p).read_bytes()).hexdigest()

def sha_gz(p):
    with gzip.open(p, "rb") as f:
        return hashlib.sha256(f.read()).hexdigest()

if __name__ == "__main__":
    out = archive(Path(sys.argv[1]), Path("archive"), date(2026, 10, 2))
    print("archived to", out)

Run on a small file:

python3 archive.py orders-2026-10-02.csv
find archive -type f
gzip -dc archive/2026/10/orders-2026-10-02.csv.gz

archived to archive/2026/10/orders-2026-10-02.csv.gz
archive/2026/10/orders-2026-10-02.csv.gz
order_id,customer
1,Acme
2,Beta

After the run, the source file is gone and the archive holds a compressed, verified copy. A few choices in that code are worth defending.

**The .part suffix.** The copy is written as orders-2026-10-02.csv.gz.part and renamed only after verification. A rename within one file system is atomic, so anything named .gz in the archive is complete. A crash mid-copy leaves a .part file that a cleanup job can remove and nobody mistakes for data.

The verification reads the whole copy. For large files the hash of the decompressed archive is a second full read, and it is the only way to be sure. If files are many gigabytes and time matters, compare sizes and gzip's own CRC (which gzip -t checks) as a cheaper floor, and keep the full hash for files that matter. For files that arrive with a checksum from the producer, store that value beside the archive.

A collision gets a new name. If a file with the same name was archived the same month (a correction re-sent under the same name), the second one gets a time suffix, so neither overwrites the other. Overwriting an archive is the one thing the archive must never do.

Dates come from the load, not the file. The folder is the month the file was loaded, which is the fact you control. A file's own date may be missing or wrong, and the ledger links the two.

Compression and format

Plain CSV compresses well, typically to a fifth or a tenth of its size, so .gz is nearly free and saves real money on a feed that runs for years. gzip is the safe default because every tool reads it, and zstd compresses better and faster where it is available (compressing CSV files and reading them anyway compares them on a real file). For analysis of the archive, keep the compressed CSV readable in place: DuckDB reads .csv.gz directly, so read_csv('archive/2026/10/*.csv.gz') queries a month of history without unpacking anything.

If the archive will be queried often, convert to Parquet once and keep the CSV as the original of record (converting CSV to Parquet). The CSV is the evidence of what arrived; the Parquet is the working copy.

Retention that you can defend

An archive that grows forever is a liability: it costs money, it holds personal data longer than needed, and nobody will find anything in it. Decide how long you need originals for. Three honest reasons exist: to reload after a bug (weeks to months), to answer a dispute with the provider (the contract period) and to meet a regulation (a number set by law). Pick the longest that applies and write it in the runbook.

Expiry can be one line in cron:

find /srv/archive -type f -name '*.csv.gz' -mtime +90 -print -delete

Run it with -print and without -delete first, so you see what it will remove. -mtime +90 is based on the modification time, which the archiver sets when it writes the copy, and a restore from backup can reset it, so for important archives use the dated folders instead and remove whole months. In S3 the same rule is a lifecycle configuration on the prefix, with expiration after 90 days and a transition to a cheaper storage class before that (the lifecycle documentation has the rules and their limits).

Retention applies to personal data in files as much as in tables. A file kept for years because "storage is cheap" is a file that has to be found and deleted when someone asks. Handling CSV files full of customer data under GDPR covers the register that makes that possible.

Keep the small things longer

Three artifacts explain a file's history, and they are tiny compared to the file. The ledger row (name, hash, row counts, load time) should live as long as the table it describes. The rejects file lists rows that did not load and why, and it is the document you want when a provider disputes a count (logging rejected rows from a CSV import for review). The job's log line for the run gives the sequence. Keep those for years, and keep the bulky original for the retention window. If a reject file holds personal data, apply the same retention to it as to the file.

Make the archive observable

Log each move with the source, destination, sizes and hash, and alert on any archive failure, since a silent failure here is the one that fills the inbox. A daily check that the inbox is empty after the run (or that its files are all younger than the schedule interval) catches the case where the job ran and the archiver did not. And check free space on the archive volume, because a full disk is the common reason an archive step fails.

Permissions deserve a thought: the job account writes the archive and cannot delete from it, with a separate cleanup account that can, so a bug in the importer cannot erase history. For object storage the equivalent is write-only access plus versioning, or object lock on the prefix.

When you should keep nothing

Some files should not be archived. If the file is a pure derivative of data that you hold in a table and can regenerate from the source, and it carries personal data, keeping a copy is risk without benefit. If the provider keeps the originals under a contract and a way to fetch them again, your copy is redundant. And if the data is sensitive enough that the storage requirements (encryption, access control, audit) would cost more than the value of the copy, deleting after verified load and keeping the ledger is a sound policy.

In those cases keep the proof and drop the payload: the hash, the row count, the time and the checks that passed. That is enough to say "we loaded this file" without being able to leak it. The format itself dictates nothing here; RFC 4180 covers the bytes, and the Python modules used above are documented in the gzip and pathlib references.