Making file loads traceable and safe to rerun

A file becomes governed rows

The raw object and each processing run remain distinct, so a load can be traced and retried.

The raw object and each processing run remain distinct, so a load can be traced and retried.

The VPC and audit controls surround the path; they do not change the pipeline sequence.

• Ingestion and load: Object arrives: S3 emits an object-created event to EventBridge. → Workflow starts: A matching rule starts Step Functions with the bucket and key. → Validate and stage: Lambda stages normalize, validate and quarantine; valid records are written to Parquet under the run identifier. → Commit by dependency: Ordered database stages load in transactions, with idempotent writes and history.
• File arrival: An S3 object-created event starts a state machine for the matching landing prefix. Amazon S3, Amazon EventBridge
• Ordered ETL: Step Functions passes one run identifier through four Lambda stages with retries for transient errors. AWS Step Functions, AWS Lambda, Run ID, Backoff and jitter
• Validation and staging: Inputs are preserved, normalized and checked; rejects are quarantined and typed intermediate data is staged by run. Raw landing object, Required-field checks, Quarantine, Parquet staging
• Transactional load: Dependency-ordered stages write idempotently, preserve SCD Type 2 history and record field-level changes. Amazon RDS for PostgreSQL, Transactions, SCD Type 2, Field audit
• Security and operations: Private subnets, scoped roles, encrypted services, alerts and account audit support the data path. VPC endpoints, OIDC deploy role, CloudWatch, SNS, CloudTrail, Security Hub
• What happens when a load is retried? A partially committed load can duplicate or corrupt a rerun. Each load stage uses one transaction and idempotent writes, with a run identifier carried through the workflow. A stage either commits as a unit or can be retried against the same traceable input.
• How are malformed records handled? Dropping invalid rows silently makes the final table look complete when it is not. Validate required fields, quarantine rejected rows and retain the raw landing object unchanged. Operators can inspect rejects and trace valid rows back to the exact source file.
• How is sensitive data kept out of audit trails? Logs that copy record contents become another sensitive data store. Keep Step Functions execution data and pgAudit bound values out of logs; record metadata and field-level changes separately. The audit trail can answer who, when and what changed without intentionally duplicating payload values.

The integrity and audit problems the foundation addresses.

Lambda stages keep a bounded file pipeline simple, but Lambda has execution and resource limits; larger files can move the heavier stages to Glue or ECS. Per-stage staging adds storage and writes in exchange for traceability and reruns. A single transaction per stage limits throughput but avoids partial stage commits.

• Rerun safety: Make retries deliberate, not duplicative. A stable run identifier, ordered stages, idempotent writes and one transaction per load stage make a rerun inspectable while avoiding partial stage commits.
• Data integrity: Keep rejected records visible. Required-field validation and normalization happen before load. Rejected rows are quarantined, while the original landing object remains unchanged.
• Audit boundary: Trace changes without copying sensitive values into logs. Run history, field-level audit, database audit and cloud audit cover different questions. Execution payloads and bound values stay out of the workflow and database audit logs.
• S3 object-created events route through an EventBridge prefix rule into Step Functions.
• Four ordered Lambda stages use retries with backoff and jitter for transient failures, and pass a run identifier throughout.
• Raw inputs remain unchanged; validation, quarantine, Parquet staging, transactional loading, idempotence, SCD Type 2 and field-level audit are part of the delivered foundation.
• Lambdas run in private subnets, RDS is isolated, the VPC includes an S3 gateway endpoint, and VPC flow logs are enabled.

How the system is intended to work.

An AWS ETL foundation that keeps raw files, validates and quarantines bad rows, stages each run, and loads relational data transactionally.

• Event trigger: S3 object-created events route through an EventBridge prefix rule into Step Functions.
• Repeatable stages: Four ordered Lambda stages use retries with backoff and jitter for transient failures, and pass a run identifier throughout.
• Integrity controls: Raw inputs remain unchanged; validation, quarantine, Parquet staging, transactional loading, idempotence, SCD Type 2 and field-level audit are part of the delivered foundation.
• Private operations: Lambdas run in private subnets, RDS is isolated, the VPC includes an S3 gateway endpoint, and VPC flow logs are enabled.
• File arrival: An S3 object-created event starts a state machine for the matching landing prefix. Amazon S3 Amazon EventBridge
• Ordered ETL: Step Functions passes one run identifier through four Lambda stages with retries for transient errors. AWS Step Functions AWS Lambda Run ID Backoff and jitter
• Validation and staging: Inputs are preserved, normalized and checked; rejects are quarantined and typed intermediate data is staged by run. Raw landing object Required-field checks Quarantine Parquet staging
• Transactional load: Dependency-ordered stages write idempotently, preserve SCD Type 2 history and record field-level changes. Amazon RDS for PostgreSQL Transactions SCD Type 2 Field audit
• Security and operations: Private subnets, scoped roles, encrypted services, alerts and account audit support the data path. VPC endpoints OIDC deploy role CloudWatch SNS CloudTrail Security Hub

Keep the delivered foundation separate from the next hardening steps.

The source distinguishes implemented Terraform and Python from design changes proposed on top.

• Event trigger: S3 object-created events route through an EventBridge prefix rule into Step Functions.
• Repeatable stages: Four ordered Lambda stages use retries with backoff and jitter for transient failures, and pass a run identifier throughout.
• Integrity controls: Raw inputs remain unchanged; validation, quarantine, Parquet staging, transactional loading, idempotence, SCD Type 2 and field-level audit are part of the delivered foundation.
• Private operations: Lambdas run in private subnets, RDS is isolated, the VPC includes an S3 gateway endpoint, and VPC flow logs are enabled.
• Route explicit workflow-stage failures to alerting states and invoke published Lambda aliases.: Exercise each failure path and verify the run status and alert.
• Remove the NAT dependency by adding interface endpoints for required AWS APIs.: Verify required service access and deny unintended outbound destinations.
• Enable Multi-AZ for the PostgreSQL deployment.: Exercise database failover and verify client reconnection behavior.
• Add Macie findings to the existing security-finding path.: Use approved sample data and confirm a finding reaches Security Hub.
• No measured performance, cost, recovery or compliance outcome is published for this project.

A pipeline foundation built for traceability and controlled reruns.

The delivered scope establishes a traceable, validated and re-runnable ingestion path. The proposed additions do not change what is claimed as delivered. No measured performance, cost, recovery or compliance outcome is published for this project.

• Preserve traceability: Raw inputs, run identifiers, staging outputs and field-level history make a data change easier to investigate.
• Separate implemented work from proposals: The foundation is described independently from additional alerting, private endpoints, Multi-AZ and Macie hardening.

Extend the foundation with a scoped hardening review.

Prioritize the proposed additions against data sensitivity, recovery requirements and the network’s operating constraints.