ADR 029 — S3 Lifecycle Management: Archive and Errored Routing¶

Status: Proposed Date: 2026-06-01

Context¶

The S3 pipeline writes every file twice into a single bucket (S3_BASE_BUCKET) under two prefixes (referred to as "buckets" in config, though they are prefix strings within one physical S3 bucket):

• ingest-bucket/{domain}/{report}/{mode}/{date}/ — raw bytes from the source, original column names.
• stage-bucket/{domain}/{report}/{mode}/{date}/ — a header-sanitized copy, produced deterministically from the ingest file by standardize_headers() (stage_tasks.py:199-220), read by Snowflake COPY INTO.

Two problems have emerged:

1. Both prefixes grow unbounded. There is no delete_object, lifecycle rule, or retention logic anywhere in the codebase. ingest-bucket/ and stage-bucket/ accumulate indefinitely and are starting to get large.
2. No outcome routing after load. When COPY INTO succeeds or fails, the ingest file stays in ingest-bucket/ with no signal about what happened to it. There is no way to tell which raw files made it into Bronze and which did not.

An archive-bucket/ prefix already exists in the bucket for this purpose but is not yet wired into the pipeline.

Decision¶

Route the ingest file to one of two outcome prefixes after COPY INTO completes, making ingest-bucket/ a short-lived transit zone rather than permanent storage.

The invariant: a file in archive-bucket/ means it loaded into Bronze successfully. A file in errored-bucket/ means the load failed. A file still in ingest-bucket/ means the pipeline has not completed (or the move itself failed — cleaned by the 7-day lifecycle).

How archive-bucket/ and errored-bucket/ map to the existing prefix naming pattern¶

The existing prefixes (ingest-bucket, stage-bucket) use a {name}-bucket suffix convention in the config schema (dag_config_v2.py:148-149) and path_builder.py. The two new prefixes follow the same convention: archive-bucket and errored-bucket.

Code changes¶

ingest_keys are currently available in stage_result (produced by stage_tasks.py:118) but are not threaded into snowflake_data by build_copy_parameters(). Two changes are required:

1. build_copy_parameters() (snowflake_tasks.py:346): pass ingest_keys from stage_result through into the returned dict so copy_to_snowflake has access to them.

2. copy_to_snowflake() (snowflake_tasks.py:428):

3. On success: move (copy + delete) ingest file(s) to archive-bucket/

4. On failure (existing handler 855-872): move ingest file(s) to errored-bucket/, then rollback, then re-raise

5. path_builder.py: add build_archive_path() and build_errored_path() alongside the existing build_ingest_path() / build_stage_path(), using bucket_prefix="archive-bucket" and bucket_prefix="errored-bucket" respectively. The errored-bucket/ layout drops the {mode} segment — quarantine is for inspection, not reprocessing.

Move semantics: copy then delete¶

S3 has no atomic rename. The move is implemented as copy + delete: - If the copy succeeds and delete fails, the file exists in both places. The 7-day ingest-bucket/ lifecycle cleans the duplicate automatically — no data loss. - Both moves (archive and errored) are best-effort: a move error is logged as a warning but does not replace the original load error or success. The task outcome is never changed by a move failure.

S3 lifecycle rules (declarative, no application code)¶

Configured on S3_BASE_BUCKET per prefix (AWS Console or CLI):

ingest-bucket/   → Expiration: 7 days
stage-bucket/    → Expiration: 14 days
archive-bucket/  → Transition to GLACIER: 90 days; Expiration: 730 days
errored-bucket/  → Expiration: 30 days

Consequences¶

Positive¶

• Clear outcome signal: archive-bucket/ = loaded, errored-bucket/ = failed, nothing ambiguous.
• ingest-bucket/ and stage-bucket/ stop growing unbounded.
• archive-bucket/ enables replay of raw source data for up to 2 years after initial load.
• Failed loads preserve an inspectable artifact instead of vanishing when stage-bucket/ expires.
• The 7-day ingest-bucket/ safety net handles any move failure without manual intervention.

Negative / trade-offs¶

• A Bronze table rebuild from data older than 2 years can no longer replay from S3 (acceptable — Bronze retains history via batch_replace, ADR 011).
• build_copy_parameters() must thread ingest_keys through — a small but required interface change.
• The copy + delete on the success path adds two S3 API calls per file on every successful load.
• Lifecycle rules live outside the repo (AWS Console / infra layer) and must be kept in sync with this ADR.

Options Considered¶

1. ingest-bucket/ as long-term archive (original design) — rejected after discovering archive-bucket/ already exists and is semantically cleaner. Having a prefix explicitly named "archive" for successfully-loaded files is more meaningful than retaining everything in "ingest."
2. Lifecycle only, no archive routing — rejected. Lifecycle stops the growth but does not distinguish successful from failed loads; ingest-bucket/ would just silently expire for both outcomes.
3. Quarantine from stage-bucket/ instead of ingest-bucket/ — rejected. stage-bucket/ is derived (standardized headers), so it's a less faithful representation of the source for debugging. Moving the original ingest file is more useful for incident investigation.
4. Separate physical S3 buckets per prefix — rejected. Per-prefix lifecycle rules on one bucket serve the same purpose with less IAM and config surface.
5. In-code deletion of stage-bucket/ after successful load — not needed. The 14-day lifecycle handles it without coupling cleanup to the happy path.

See also: ADR 009 (S3 path conventions) | ADR 011 (Bronze retains history via batch_replace) | path_builder.py | stage_tasks.py | snowflake_tasks.py