Skip to Content
Cloud Engineering Academy
Projects

Capstone 3: Banking data engineering pipeline with SFTP reconciliation

A data lake pipeline (S3, Glue, Athena, Lake Formation) plus an SFTP daily reconciliation flow with AWS Transfer Family, control-total validation, PII tokenising and masking, counterparty reconciliation and a tamper-evident audit trail, built on the Module 7 lab and 22 new offline tests.

advanced⏱ 10 h 🟡 Low costDEA-C01SCS-C03SAA-C03

Assumes: .NET 10 LTS · AWS SDK for .NET v4 · Python 3.11 / Spark 3.5 (Glue 5.0) · xUnit v3 4.0.3 · Terraform 1.10+ with AWS provider 6.x · AWS Transfer Family (SFTP) · Region us-east-1. Verified against official sources on 2026-11-18.

What you are building

Two flows for the fictional Harbourline Bank:

  1. A transaction data pipeline: files and API calls land in S3, a workflow validates and cleans them, Glue writes partitioned Parquet, bad rows go to quarantine with reason codes, Athena and Lake Formation serve analysts, and each run leaves a control-total reconciliation and an audit record.
  2. A daily SFTP reconciliation: a payroll partner drops a payments file on an AWS Transfer Family SFTP server every night. The bank validates the header, details and trailer, masks and tokenises personal data, compares the payments with its own books, reports breaks and writes a hash-chained audit trail of every decision.

All data is fictional, produced by the seeded generator in datasets/. Nothing here makes a system compliant with UK GDPR or any regulation. Masking, retention and access are decisions for your data-protection and compliance teams.

Learning objectives

By the end you can:

  • Design a layered lake (raw, curated, quarantine, gold, masked) and say who may read each layer.
  • Validate a partner file the way a receiving bank does: shape, check digits, duplicates, trailer count and control sum, and reject the whole file on any error.
  • Tokenise and mask personal data inside the worker, so analysts never see raw values, and explain why S3 Object Lambda is not part of this design.
  • Reconcile a validated file against the bank's books and report amount, currency, missing and unexpected breaks.
  • Build an audit trail with a hash chain and Object Lock, and show tamper detection.
  • Deploy the SFTP side, test partner isolation, and delete the server so the hourly charge stops.

The architecture

The repository adds the file-by-file sequence, the states of a file (landed, duplicate, rejected, accepted, masked, reconciled, matched or break) and the security boundaries in projects/p3-data-engineering-pipeline/docs/architecture.md.

Prerequisites and the lessons it builds on

What is in the repository

WhereWhat
datasets/The seeded generator and the committed samples, including the SFTP batch file and its deliberately broken copy with an answer key
labs/m07-transaction-pipelineThe validator (C# and Python twins), PII protector, reconciliation calculator, Glue jobs with data quality, Athena DDL and reports, CDK data-lake stack, Lake Formation script, 27 .NET and 18 Python tests
projects/p3-data-engineering-pipelineThe capstone: src/Harbourline.Capstone.Reconciliation, 22 tests, infra/terraform (Transfer Family, landing and audit buckets, file-ready queue, worker role, alarms), docs/, checklist.md

The reconciliation code, in one table

ClassJob
PartnerBatchParserHeader, detail and trailer checks with finding codes (H001..T003, F001..F003); findings carry the rule and line number, never the value
BatchMaskerKeyed-HMAC tokens for names and IBANs (the m07 PiiProtector), masked IBANs, references with digit runs masked
ReconcilerCompares the file with the expected payments by end-to-end ID: matched, amount mismatch, currency mismatch, unexpected, missing
HashChainedAuditLogAppend-only records in the audit_events shape with a SHA-256 chain; refuses IBAN-shaped and e-mail-like values
ReconciliationWorkflowDedupe by content hash, validate, quarantine or mask and reconcile, audit every step

Build path

Run the lab suites and the capstone tests (free). Read the broken sample's answer key first (datasets/samples/sftp/*_messy_defects.csv): four defects, which the tests expect as six finding codes because a duplicate line also breaks the trailer's count and sum.

Run the Module 7 pipeline locally. The Spark container runs csv_to_parquet.py; the .NET CLI validates the messy transactions and writes a reject report.

Deploy the data lake (credits). cdk deploy HlM07-DataLake, upload transactions_messy.csv to incoming/sftp/, and run the Athena reports. Compare scanned bytes for CSV and Parquet.

Deploy the reconciliation infrastructure. Generate a throwaway SSH key pair, terraform apply, put a random value in the tokenisation secret, and sftp the clean sample into /in. Read the event on the file-ready queue.

Run the worker logic. Host ReconciliationWorkflow as an ECS task or Lambda with the worker role, or run it from a console app with your SSO session. Send the clean file, the broken file and the clean file again.

Try to misbehave as the partner. Write into /out, look for other partners' folders, delete a file, use a wrong key. Everything must fail.

Delete the SFTP server first, then tear down the rest. Stopping does not stop the charge.

What was verified when this page was written

CheckResult
Capstone tests (dotnet test in Docker, .NET SDK 10)22 passed, 0 failed
Lab m07 suites27 .NET and 18 Python tests per the lab README
terraform validate on infra/terraform (Terraform 1.14, AWS provider 6.x)Valid
Not runAn actual deployment of the SFTP infrastructure, the worker as a hosted service, Step Functions orchestration, and the Quick Sight dashboards (design only)

Security controls

The repository's docs/security-controls.md covers partner access, landing and storage, processing, privacy and audit. The ones proven offline:

ControlHow it is proven
Whole-file rejection on a control-total failureThe broken sample is rejected with exactly D002, D003, D004, H002, T002, T003; no masked output is produced
Exactly-once processing of a fileRe-sending identical bytes returns Duplicate and writes no second output
No values in findingsA test asserts the messages contain no IBAN or amount fragment
PrivacyThe masked CSV has no creditor name, no full IBAN and no digit run from the reference
Tamper evidenceEditing or removing an audit record is detected at the right index; IBAN-shaped values are refused

Honest gaps: the lab uses a public SFTP endpoint (production: VPC internet-facing endpoint with Elastic IPs and an allow-list), no PGP or manifest, no malware scan, Object Lock in governance mode for one day (production: compliance mode), and in-memory registries.

Cost and teardown

Transfer Family is the expensive part: about USD 0.30 per hour per protocol while the server exists (checked 2026-11-18, ⚠ VERIFY), roughly USD 216 a month if left. Stopping a server does not stop the charge; deleting it does. The rest of a session (a few Glue runs and Athena queries, KMS, S3, SQS) is cents: plan for roughly USD 0.50 to 1.00. The repository's docs/cost-and-teardown.md has the tables and the ordered teardown, including the Object Lock note: objects in the audit bucket are retained for the retention period, so destroy may need a day or a governance bypass.

Teardown checklist

Run terraform destroy for the capstone first (it deletes the SFTP server), then the Module 7 teardown and cdk destroy, then remove the throwaway key pair, then run the tag query for Project=cea-lab. Check by hand for Transfer Family servers, S3 buckets and log groups.

Acceptance criteria

  • All suites pass on a clean machine (27 + 18 lab, 22 capstone).
  • You can state the order of the reconciliation steps and why: dedupe, validate, mask, reconcile, audit.
  • Partner isolation tested live: write to /out, other prefixes, delete, wrong key all fail.
  • Rejected, duplicate and accepted files each leave the audit records the tests expect.
  • Analysts can query masked data and cannot read landing/.
  • The SFTP server is deleted and nothing tagged Project=cea-lab remains.

Portfolio-ready checklist

The full list is projects/p3-data-engineering-pipeline/checklist.md. The essentials:

  • Five ADRs: lake, Transfer Family, whole-file rejection and hash dedupe, tokenise in the worker, audit chain and Object Lock.
  • Control totals prove that source equals accepted plus quarantined.
  • No real data, no private keys in the repository.
  • You wrote down what a real bank still needs (legal basis, retention, DPIA, partner contract, audit sign-off).
  • Extensions: host the worker, add PGP and a manifest, add malware scanning, orchestrate with Step Functions, write masked Parquet, add a missing-file detector.

Exercises

🟢 Guided. Add a detail-line defect to the sample (a negative amount) and a test that proves it is rejected with D002. Check that the finding does not echo the amount.

🟡 Challenge. Replace the in-memory processed-file set with a DynamoDB conditional put on the SHA-256, and write a Testcontainers test that fires ten concurrent identical files and expects exactly one Accepted.

🔴 Live problem. The partner says they sent the file but yesterday's reconciliation shows no payments. List what you check, in order (SFTP logs, landing object, event, queue, DLQ, worker log, audit chain, partner's clock and time zone), and what evidence would convince both sides.

Interview questions

  1. Why reject the entire file when the trailer's control sum does not match?
  2. What is the difference between tokenising and masking, and what is the risk of deterministic tokens?
  3. Why must Transfer Family partners get a logical home directory and a prefix-scoped role?
  4. What does S3 Object Lock in compliance mode protect against, and what is the operational cost?
  5. A reconciliation shows a break of exactly one payment. Who acts, and why must the software not "fix" it?

Key takeaways

  • Control totals turn "I think we got everything" into proof.
  • Keep what you reject, with reasons, and never put values in findings or audit records.
  • Mask before anyone can read: raw data belongs in one place, briefly.
  • Evidence is designed, not collected afterwards.
  • A per-hour service is a deletion problem: write the teardown first.

Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.

On this page