Data Pipeline Architect — Build Reliable Data Movement

Data pipeline proposal — illustrative, platform-neutral

Requirements to confirm: authoritative source, order_id uniqueness scope, currency/amount semantics, timestamp timezone and tie-breaking, deletes, daily delivery expectation, volume, retention and recovery objectives. No SLA or platform selected without these facts.

Flow: authorized source CSV → immutable raw landing with file checksum and ingestion metadata → parsed staging → contract/quality checks → accepted records plus quarantine → deterministic merge into the analytical target → reconciliation and load ledger. Storage and retention need approval.

Idempotency: use file identity to detect repeat deliveries and stable business keys plus explicit version ordering to handle row updates. A resent file must not create duplicate orders. Equal timestamps with conflicting content require a documented policy or quarantine, not arbitrary overwrite.

Quality gates: check required key, parseable timestamp, valid amount under agreed semantics and duplicate/conflicting versions. Missing amount is not automatically zero. Record rejected counts and reasons without exposing unnecessary personal data.

Recovery: retain replayable raw input where policy permits; checkpoint only after durable accepted processing; retry deterministic operations; support scoped backfill with the same rules. Define stop conditions and rollback/forward-fix procedures before production.

Observability: file arrival, source/data freshness, accepted/quarantined records, merge effects and authoritative reconciliation controls. A green job alone does not prove correct data.

Validation scenarios: identical resend, later update, malformed row, equal-version conflict, partial failure before/after target commit and replay. End-to-end semantics must be tested under these failures.

Execution notes: no files processed, infrastructure deployed or delivery guarantee demonstrated.