Module project: banking transaction processing pipeline
Deploy, run, break and audit a complete batch pipeline: raw files in an encrypted S3 zone, an event-driven Step Functions and Glue workflow that validates, tokenises and reconciles, quarantine with reason codes, partitioned Parquet, Athena reports and one audit record per run, with 27 .NET and 18 Python tests that need no AWS account.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 · AWS Glue 5.0 (Spark 3.5, Python 3) · AWS CDK v2 (C#, Amazon.CDK.Lib 2.277.0, cdk-nag) · Step Functions Standard · Athena engine v3 · AWS CLI v2 · Region us-east-1 · labs/m07-transaction-pipeline. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this project you can:
- Run both test suites offline (27 .NET tests, 18 Python tests) and explain what each one pins down.
- Deploy a pipeline whose zones, key, roles and workflow are defined in C# CDK with cdk-nag, and read its change summary critically.
- Trace one file from
incoming/through EventBridge, Step Functions and Glue to curated Parquet, quarantine, gold reports and an audit record. - Prove reconciliation, idempotency, masking and quarantine from your own runs, including a deliberate break.
- Query the results in Athena with the project's SQL and views, and explain the known limits of the design.
- Tear everything down, verify nothing billable remains, and write the project up for a portfolio.
Prerequisites
This project uses the whole module. Finish the lessons and labs first, or at least skim them:
- Lessons: S3 data lake design, Glue Data Catalog, PySpark in Glue, Orchestration, Validation and data quality, Secure pipeline design, Governance with Lake Formation, Reporting with Quick Sight
- Labs: 7.1 generate data, 7.2 CSV to Parquet, 7.3 Athena, 7.4 Glue and data quality; optional 7.5 Lake Formation and 7.6 streaming
- Tools: Docker Desktop (the offline parts need nothing else). For the AWS part: AWS CLI v2 with
aws sso login --profile learn-sandbox, the .NET 10 SDK (CDK runsdotnet), Node.js LTS with the AWS CDK CLI v2, an account bootstrapped withcdk bootstrap, and a budget alarm (cost guardrails)
The code lives in labs/m07-transaction-pipeline/, with its own README.md. This page is the guided walkthrough.
Cost label
🟡 Credits for the AWS deploy; 🟢 free for everything offline. A full run costs well under USD 0.50 if destroyed the same day:
| Item | Approximate cost (us-east-1, README estimate checked 2026-11-18 ⚠ VERIFY) |
|---|---|
| Glue Spark job, 2 × G.1X, about 2 minutes billed per run | About USD 0.03 per run at USD 0.44 per DPU-hour |
| Glue Data Quality inside the job | Adds DPU time to the same run |
| KMS customer managed key | USD 1 per month, prorated; deleted after the 7-day waiting period |
| Secrets Manager secret (tokenisation key) | About USD 0.40 per month, prorated ⚠ VERIFY |
| Lambda, Step Functions Standard, SNS, API Gateway HTTP API | Pennies at lab volume (free tier may apply) |
| Athena | USD 5 per TB scanned, 10 MB minimum per query |
| S3 | Negligible for megabytes; lifecycle rules expire objects after 7 to 30 days |
Free-first path: run both test suites and hl-pipeline validate, read the Athena SQL and the Glue job, and answer the review questions at the end. You miss the AWS integration but keep the engineering.
Scenario
Harbourline receives transaction files from partners through an API and an SFTP drop. Finance needs, every morning, a trustworthy answer to: what arrived, what was rejected and why, do the control totals match, and what is suspicious? Compliance needs to know that the raw IBAN never reaches analysts and that every run leaves an audit record. You are the engineer who builds and proves it, using only fictional data.
Architecture
The streaming variant (Lab 7.6) applies the same two fraud rules to a Kinesis stream.
What the project builds, against the eight requirements
| # | Requirement from the module overview | Where it is done |
|---|---|---|
| 1 | Receive records through an API and an SFTP drop | IngestFunction.cs (HTTP API, IAM auth); local atmoz/sftp container |
| 2 | Store raw data unchanged, encrypted and versioned | DataLakeStack.cs: raw bucket SSE-KMS, versioning, TLS-only |
| 3 | Trigger processing from the arrival event | EventBridge rule on Object Created under incoming/ |
| 4 | Validate and transform, quarantine with a reason code | transactions_etl.py using harbourline_etl |
| 5 | Store processed data as partitioned Parquet | Curated zone year=/month=/day= |
| 6 | Query in Athena through the Glue catalog | sql/athena/02_project_tables.sql with partition projection |
| 7 | Daily summary, reconciliation report, suspicious-activity signals | Gold tables and 03_project_reports.sql views |
| 8 | Execution logs and an audit record for every run | Step Functions history, CloudWatch, gold/audit/ record |
Part 1: prove it offline (free)
Run the .NET suite
cd labs/m07-transaction-pipeline
MSYS_NO_PATHCONV=1 docker run --rm --memory 3g -v "$PWD:/src" -v cea-nuget-m07:/root/.nuget/packages -w /src \
mcr.microsoft.com/dotnet/sdk:10.0 sh -c \
"dotnet test --solution TransactionPipeline.slnx; find . -type d \( -name bin -o -name obj -o -name TestResults \) -prune -exec rm -rf {} +"Expected (author's run): Test run summary: Passed! total: 27 failed: 0 succeeded: 27. The tests cover the validator against the answer key, control totals, the clean file and the IBAN protection, the Latin-1 repair, IBAN mod-97, reconciliation and privacy, the ingest Lambda (conditional write, replay, bad requests), and the fraud rules, the DynamoDB store and the Kinesis producer.
Run the Python suite
docker compose -f local/docker-compose.yml run --rm spark python -m pytest glue/tests -q -p no:cacheproviderExpected: 18 passed in about 19s (17 pure-Python tests plus one Spark test). Both suites grade the validator against data/samples/transactions_messy_defects.csv and assert the same control totals (1,036 rows read, 969 accepted, 67 quarantined; GBP 124,187.38 and EUR 5,040.74 accepted) and the same token for the same IBAN, so the C# and Python rules cannot drift apart silently.
Predict the AWS results from the offline engine
I ran the Python rules over the two sample files in the Spark container, so you know what the cloud run should give:
| File | Rows | Accepted | Quarantined | Fraud signals | Daily-summary groups |
|---|---|---|---|---|---|
transactions_messy.csv | 1,036 | 969 | 67 | 1 (HIGH_AMOUNT) | 225 |
transactions.csv (clean) | 1,012 | 1,012 | 0 | 1 (HIGH_AMOUNT) | 228 |
The 67 quarantined rows break down by reason code as: DUPLICATE 23, INVALID_DATE 16, INVALID_CURRENCY 12, INVALID_IBAN 8, AMOUNT_SIGN 6, AMOUNT_SIGN,DUPLICATE 1 and AMOUNT_SIGN,INVALID_IBAN 1. Write these down. If your Athena queries disagree, the pipeline, not the data, is what you must debug.
Part 2: deploy
Build the Lambda package
MSYS_NO_PATHCONV=1 docker run --rm -v "$PWD:/src" -v cea-nuget-m07:/root/.nuget/packages -w /src mcr.microsoft.com/dotnet/sdk:10.0 \
sh -c "dotnet publish src/Harbourline.DataPipeline.Functions -c Release -r linux-arm64 --self-contained false -o publish/functions"The existence of publish/functions/ makes the CDK app include the ingest API and the streaming stack. The Functions project compiled in the author's checks.
Deploy the data lake
cd infra/cdk
aws sso login --profile learn-sandbox
cdk deploy HlM07-DataLake --profile learn-sandbox -c owner=<your-name>Before you confirm, read the summary. You should recognise: one KMS key and alias, six S3 buckets (raw, curated, quarantine, gold, Athena results and access logs), a Secrets Manager secret, two Glue databases, a Glue security configuration and two jobs, an IAM role for the jobs, an SNS topic, a Step Functions state machine, an EventBridge rule, an Athena workgroup, and the ingest Lambda with an HTTP API. cdk-nag (AWS Solutions checks) runs at synth time: unsuppressed findings fail the deploy, and each suppression in the stack carries a written reason. Read two of them and decide whether you agree.
Then set up the shell:
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1 MSYS_NO_PATHCONV=1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
RAW="hl-m07-raw-${ACCOUNT}"; CUR="hl-m07-curated-${ACCOUNT}"; QUA="hl-m07-quarantine-${ACCOUNT}"; GOLD="hl-m07-gold-${ACCOUNT}"
SM=$(aws cloudformation describe-stacks --stack-name HlM07-DataLake --query "Stacks[0].Outputs[?OutputKey=='WorkflowArn'].OutputValue" --output text)
cd ../..If you disabled the EventBridge rule for Lab 7.4, enable it: aws events enable-rule --name hl-m07-raw-object-created.
Create the Athena tables and views
Copy the two SQL files, replace the placeholder account id, and run each statement in workgroup hl-m07-analysts, one at a time (the helper q from Lab 7.3 works):
sed "s/111122223333/${ACCOUNT}/g" sql/athena/02_project_tables.sql > /tmp/02.sql
sed "s/111122223333/${ACCOUNT}/g" sql/athena/03_project_reports.sql > /tmp/03.sql02_project_tables.sql creates hl_m07_curated.transactions and quarantine and the gold tables daily_summary, reconciliation, fraud_signals and audit. The curated table uses partition projection (year 2025 to 2030). The gold tables are partitioned by ingest_run_id, so MSCK REPAIR TABLE (last lines of the file) loads new run partitions after each run. 03_project_reports.sql creates the views v_daily_summary (summed across runs) and v_reconciliation (every run, joined to its audit start time).
Part 3: run the pipeline
Path A: the SFTP drop
Partners deliver files by SFTP. The module's real design uses AWS Transfer Family, which bills per endpoint-hour, so the project uses a free stand-in and a copy step:
docker compose -f local/docker-compose.yml up -d sftp
cp data/samples/transactions_messy.csv local/sftp-drop/
sftp -P 2222 partner0042@localhost # password: change-me-locally (a learning shortcut)The container plays the partner gateway: user partner0042, folder upload. A small copy job (you, here) moves the file to S3; in production Transfer Family writes straight into the raw bucket.
aws s3 cp local/sftp-drop/transactions_messy.csv "s3://${RAW}/incoming/sftp/transactions_messy.csv"The upload creates an Object Created event under incoming/, which starts the workflow.
Watch the workflow and the job
aws stepfunctions list-executions --state-machine-arn "$SM" --max-items 3 --query "executions[].{name:name,status:status,start:startDate}" --output table
aws glue get-job-runs --job-name hl-m07-transactions-etl --max-items 1 --query "JobRuns[0].{state:JobRunState,secs:ExecutionTime}"The Standard workflow has one task (ValidateAndTransform, a Glue .sync task that waits for the job to end), a retry for Glue.ConcurrentRunsExceededException and Glue.AWSGlueException (30 s, back-off 2, three attempts) and a catch that publishes to hl-m07-pipeline-alerts and ends in Fail. Expect a minute or two for Spark to start; the final status is SUCCEEDED. In the console, Step Functions → State machines → hl-m07-transaction-pipeline → Executions shows the graph with the task highlighted, and its event history is part of your audit trail (execution data is excluded from the logs on purpose).
Inspect what each zone received
aws s3 ls "s3://${CUR}/transactions/" --recursive | head -5
aws s3 ls "s3://${QUA}/rows/" --recursive
aws s3 ls "s3://${GOLD}/" --recursive | head -20
aws s3 cp "s3://${GOLD}/audit/" - --recursive 2>/dev/null | head -c 1500- Curated: Parquet under
transactions/year=2026/month=10/day=20/.... - Quarantine:
rows/ingest_run_id=run-<16 hex>/rejects.jsonl: one JSON object per rejected row withreason_codes,issues,row_numberand the raw record. It contains raw personal data: treat it like raw. - Gold:
daily_summary/,reconciliation/andfraud_signals/partitioned by run id. - Audit:
audit/ingest_run_id=run-.../audit.jsonwithevent_id,occurred_at,started_at,actor(the Glue role's caller identity),action(pipeline:ProcessTransactionFile),resource(the source file with its version id),outcome,job_name, statistics andreconciliation_status. It is written last.
Ask Athena
After running the MSCK REPAIR TABLE statements for the gold tables and the quarantine table, run:
q "SELECT * FROM hl_m07_gold.v_reconciliation ORDER BY status DESC, started_at DESC LIMIT 50"
q "SELECT reason_codes, count(*) AS rows_rejected FROM hl_m07_curated.quarantine GROUP BY reason_codes ORDER BY rows_rejected DESC"
q "SELECT rule, count(*) AS signals, sum(abs(amount)) AS value FROM hl_m07_gold.fraud_signals GROUP BY rule"
q "SELECT currency, sum(amount) AS curated_total FROM hl_m07_curated.transactions WHERE year = '2026' AND month = '11' GROUP BY currency"
q "SELECT ingest_run_id, outcome, reconciliation_status, statistics.rows_read, statistics.rows_accepted, statistics.rows_quarantined FROM hl_m07_gold.audit"Compare with the table from Part 1. For the messy file you should see two MATCHED lines (GBP and EUR) with 1,036 source rows, 969 accepted and 67 quarantined, the reason-code counts above, one HIGH_AMOUNT signal and an audit row with rows_read 1,036. A file produced without a control record is reconciled against figures recomputed from its own raw rows: that proves nothing was lost inside the job, not that the sender sent everything. Next you add a real control record.
Part 4: the API path, idempotency and a deliberate break
Send transactions through the API
The ingest Lambda stores exactly what it receives, with control totals as S3 object metadata, under a key made from the Idempotency-Key header. The route uses IAM authorisation, so the request must be signed with SigV4. With a recent curl:
URL=$(aws cloudformation describe-stacks --stack-name HlM07-DataLake --query "Stacks[0].Outputs[?OutputKey=='IngestApiUrl'].OutputValue" --output text)
eval "$(aws configure export-credentials --profile learn-sandbox --format env)"
( echo '['; head -100 data/samples/transactions.jsonl | sed '$!s/$/,/'; echo ']' ) > /tmp/batch.json
curl -sS -X POST "$URL" --aws-sigv4 "aws:amz:${AWS_REGION}:execute-api" \
--user "${AWS_ACCESS_KEY_ID}:${AWS_SECRET_ACCESS_KEY}" -H "x-amz-security-token: ${AWS_SESSION_TOKEN}" \
-H "Idempotency-Key: 4b1f0c3e-7d55-4a43-9a8e-0d2f3c1a9b10" -H "Content-Type: application/json" --data @/tmp/batch.jsonThe flags for SigV4 with session credentials should be checked against your curl version ⚠ VERIFY. Expected: {"status":"accepted","records":100,"rawKey":"incoming/api/2026/11/18/4b1f0c3e-....jsonl","replay":false} with HTTP 202 (the date is today's, UTC).
Run the same command again. The response is the same except "replay":true, and nothing new is stored: the Lambda writes with an S3 conditional write (If-None-Match: *), and the second attempt gets 412 Precondition Failed, which it reports as a replay. That is idempotency at the door. Try an empty body or a missing header and read the RFC 9457 problem response.
The object triggers the workflow like any other. The JSON Lines file goes through the same rules as CSV (the test test_jsonl_api_path_gives_same_result_as_csv proves equivalence), and because the Lambda wrote record-count and control-sum-gbp/eur metadata, this time the reconciliation compares against what the sender declared.
Break the reconciliation on purpose
Upload the messy file again with control totals that are wrong:
aws s3 cp data/samples/transactions_messy.csv "s3://${RAW}/incoming/sftp/transactions_messy_wrong_totals.csv" \
--metadata record-count=1100,control-sum-gbp=150000.00,control-sum-eur=6000.00When the run finishes, v_reconciliation shows BREAK for both currencies: the sender says 1,100 rows and the pipeline saw 1,036, and the amount differences are non-zero. The audit record's reconciliation_status is BREAK too. A break does not stop the load, because accepted rows are still valid; it stops sign-off. Someone must explain it before the numbers go to the ledger. This is the output that the report gate and the Quick Sight dashboard in the reporting lesson are built to surface.
Send a file that cannot be read at all
The sample SFTP payments file uses a different layout (header, detail and trailer records), not the transactions header:
aws s3 cp data/samples/HLPG_PTN0042_PAYMENTS_20261116_001.csv "s3://${RAW}/incoming/sftp/HLPG_PTN0042_PAYMENTS_20261116_001.csv"The job raises SCHEMA_REJECTED run-...: unexpected header, copies the whole file to quarantine/files/<run id>/, and fails. The state machine's catch publishes to hl-m07-pipeline-alerts (subscribe an e-mail to see it) and the execution ends Failed. Row-level problems quarantine a row; a wrong header quarantines the file, because every column would be misread. Confirm both: aws s3 ls "s3://${QUA}/files/" --recursive and aws stepfunctions list-executions --state-machine-arn "$SM" --status-filter FAILED.
Test idempotency of the workflow
The job derives its run id from bucket/key#versionId and checks first whether an audit record for that run already exists. Start the workflow again with the same object and no new upload:
aws stepfunctions start-execution --state-machine-arn "$SM" \
--input "{\"detail\":{\"bucket\":{\"name\":\"${RAW}\"},\"object\":{\"key\":\"incoming/sftp/transactions_messy.csv\"}}}"The job logs already processed; nothing to do and succeeds without writing. This protects against duplicate event deliveries and Step Functions retries.
Part 5: the design, honestly
What makes this pipeline repeatable
- One file per run, run id from the object version. The same bytes at the same version always map to the same run.
- The audit record is written last. A crash before it leaves no audit record, so the re-run starts clean instead of skipping a half-written run.
- Raw is never rewritten. The job role has read-only access to raw, and raw is versioned.
- Rules live in one reviewable module with a C# twin, and both are tested against the same answer key.
Known limits (write these in your portfolio)
| Limit | Why it exists | What you would do next |
|---|---|---|
Re-uploading the same key makes a new version, hence a new run, and curated gets the rows twice. Duplicates are caught within a file, not across files. The v_daily_summary view sums all runs, so a re-sent file doubles the totals | The run id includes the version id by design; no cross-file key store | Use idempotency_key against a store (DynamoDB or an Iceberg MERGE) to reject cross-file duplicates; make the summary read a de-duplicated view |
| Curated is append-only Parquet; there is no update or delete | Keeps the job simple | Iceberg tables for MERGE, corrections and erasure handling |
| Fraud signals in batch use a fixed ten-minute window | Same as the streaming rules, deliberately | Sliding window or a stream processor |
| Late-arriving rows are flagged, not re-aggregated | Daily summary is per run | Re-run affected dates; read is_late_arriving |
| The token key is a single secret, rotated by re-tokenising | Deterministic tokens must stay joinable | Versioned token prefix and a re-tokenise runbook (Secure pipeline design) |
Gold partitions need MSCK REPAIR | Gold is partitioned by run id | Partition projection with an injected run id, a crawler, or a catalog update from the job |
Redshift is design-only (sql/redshift/01_load_curated.sql) | Redshift Serverless is not free | Read the COPY and distribution choices; run them only with a budget |
Security considerations
⚠ Learning shortcut — not for production
Buckets use RemovalPolicy.DESTROY and autoDeleteObjects so cdk destroy leaves nothing behind; one KMS key protects every zone; the SFTP container uses a password on the command line; the analysts' workgroup is shared; the API trusts any IAM principal in the account that may invoke it; and the tokenisation key is one secret. Production keeps the raw and quarantine buckets (retained, with Object Lock where policy requires), splits keys by domain, uses SSH keys and Transfer Family, restricts the API with a resource policy and a partner role, and reviews every suppression in cdk-nag.
- Least privilege per component: the ingest Lambda can only
PutObjectunderincoming/api/*and use the key to encrypt; the Glue role reads raw, writes the other zones, reads one secret and uses the key. - PII: the raw IBAN exists only in raw and quarantine; curated holds a keyed token and a masked form; the Step Functions logs exclude execution data; the jobs log counts and ids.
- Encryption and TLS everywhere: SSE-KMS with Bucket Keys, an enforced SSL bucket policy, encrypted logs, topics and the Glue bookmark.
- Audit: Step Functions Standard history, CloudWatch, the audit record and CloudTrail. Add CloudTrail data events for raw and quarantine if you need object-level evidence.
- Quarantine is sensitive. It holds the original values of bad rows. Keep its retention short (14 days here) and restrict access to the pipeline and a break-glass role.
- Analysts' access: do Lab 7.5 so analysts see tokens, not names, through Lake Formation filters, and see only gold aggregates in reports.
Compliance review required
This project makes no claim of compliance with UK GDPR, GDPR or PCI DSS. Masking and tokenisation choices, retention of raw and quarantine data, erasure handling, who may read quarantine, and the lawful basis for monitoring are decisions for your data-protection officer, security and compliance teams.
Common mistakes
- Uploading test files under
incoming/while running a lab that has its own Glue job → both start. Use a separate prefix or disable the rule. - Forgetting to replace
111122223333in the SQL → tables point at buckets that do not exist and queries return nothing. - Forgetting
MSCK REPAIR TABLEfor the gold tables → the reports are empty even though the files exist. - Re-uploading a file "to test again" → a second run, doubled curated rows and a doubled summary (see the limits table).
- Reading a
MATCHEDstatus as proof of completeness without a control record → it only proves nothing was lost inside the job. - Leaving the stack running overnight → the standing costs are small, but the key and secret bill by the month.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Workflow never starts after upload | EventBridge notifications off, rule disabled, or key not under incoming/ | CDK enables EventBridge on raw; aws events enable-rule --name hl-m07-raw-object-created; check the key prefix |
Glue job AccessDenied on KMS | Role lacks kms:Decrypt or GenerateDataKey, or the Logs statement was removed from the key policy | The stack grants both; re-add the logs.us-east-1.amazonaws.com statement if you edited the key policy |
DQ_FAILED run-... | A curated rule failed (for example duplicate transaction_id across the file) | Read Glue console → Data quality; fix the source and re-upload as a new object |
SCHEMA_REJECTED ... | Wrong header (for example the payments file) | Expected for that file; fix the source layout |
| A second upload of the same file runs again and doubles the totals | A new object version is a new run; the audit check only skips the same version (a re-delivered event) | See the limits table; de-duplicate across files if needed |
| Athena returns zero rows from curated | Wrong account id in storage.location.template, or a wrong date filter | Replace 111122223333 everywhere; check partitions with year, month, day filters |
| Gold views are empty | Partitions not loaded | Run the MSCK REPAIR TABLE statements |
ProvisionedThroughputExceeded in the streaming producer | One shard allows 1 MB/s or 1,000 records/s | The producer retries with back-off; lower --limit |
.NET tests: Sample file ... not found | Tests copied without data/samples | Keep the data/ folder next to tests/ |
cdk deploy cannot find dotnet | CDK runs dotnet via cdk.json | Install the .NET 10 SDK locally for the AWS part |
Cost and cleanup
# 1. Stop the local SFTP container and remove local files.
docker compose -f local/docker-compose.yml down --remove-orphans
rm -rf local/sftp-drop local/out publish /tmp/batch.json /tmp/02.sql /tmp/03.sql
# 2. If you ran Lab 7.5 or 7.6, undo them first.
./infra/cli/lab75-lake-formation.sh teardown # only if you ran Lab 7.5
# 3. Drop the Athena tables created outside CDK, then destroy the stacks.
q "DROP DATABASE IF EXISTS hl_m07_lab CASCADE" # if you ran Lab 7.3
cd infra/cdk
cdk destroy HlM07-Streaming HlM07-DataLake --profile learn-sandbox
# 4. Check nothing tagged for the lab remains (empty list expected).
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[?contains(ResourceARN, 'hl-m07')].ResourceARN"The KMS key enters its seven-day pending-deletion window (the minimum) and stops costing once scheduled for deletion ⚠ VERIFY. The buckets use autoDeleteObjects, a learning shortcut, so cdk destroy can remove them; production buckets are retained. Remove any hl-analyst-uk profile you added.
Exercises
- 🟢 Guided: Run Part 1 and Part 3 with the messy file and fill in a table of your own results next to the prediction table: rows read, accepted, quarantined, reason-code counts, signals and the audit record. Explain every difference, if any.
- 🟡 Challenge: Close the cross-file duplicate gap. Design (and, if you wish, code against the in-memory stores first) a check that rejects a row whose
idempotency_keywas accepted by an earlier file, using DynamoDB with a conditional put, and describe howv_daily_summaryshould change so a re-sent file cannot double-count. Add a unit test in C# and Python. - 🔴 Live problem: On Monday the head of payments reports that GBP totals in the daily summary are exactly double on Friday 13 November, and the reconciliation shows
MATCHED. Investigate: what do you check first, why isMATCHEDunhelpful here, what in the audit records proves your theory, and what do you change so it cannot happen again?
Assignment
Create a portfolio repository folder m07-transaction-pipeline/ with: the three prediction tables from this project filled with your own results, screenshots-free evidence (CLI output, query results) of a MATCHED run and a BREAK run, an ARCHITECTURE.md with the diagram and the known-limits table, a SECURITY.md with your threat table, and a RUNBOOK.md for "a reconciliation shows BREAK" and "the pipeline-alerts topic fired". Link to your fork of the lab code and include the teardown evidence.
Solutions
Your numbers should match the prediction table: 1,036 / 969 / 67 for the messy file, one HIGH_AMOUNT signal, and the reason-code counts listed. Differences usually mean (a) the file was uploaded twice (counts double), (b) a different sample file was used, or (c) MSCK REPAIR was not run for a table. The audit row proves which: it records the source file with its version id and the counts for that run.
Before the file is processed, for each accepted row do a conditional put of IDEM#<idempotency_key> into a DynamoDB table with a TTL longer than the retention window; if the condition fails, reject the row with a new reason code such as DUPLICATE_ACROSS_FILES (and record which run first accepted it). In Spark the lookup should be a batch operation, not row by row, or the store becomes the bottleneck. For the reports, either read only rows whose ingest_run_id is the first run for each idempotency_key (a window function over curated) or switch curated to an Iceberg table with MERGE ON idempotency_key. Tests: feed the same file twice and assert the second run quarantines every row with the new code in both the C# and Python suites. The cost is an extra store and a consistency decision about what "first" means under concurrent runs.
Suspect a re-sent file: an operator or partner uploaded Friday's file twice. Each upload is a new object version, hence a new run id, so curated holds the rows twice and v_daily_summary (a sum over all runs) doubles them. Each run reconciles in isolation, so each is MATCHED: the reconciliation compares a run's accepted plus quarantined with that run's input, not the ledger or earlier runs. Prove it from the audit table: two runs with the same resource key but different version ids, the same row counts and nearby start times. Fix: de-duplicate by idempotency_key across files (see the challenge), reject or flag a file whose content hash was already processed, and add a daily cross-check of curated totals against the source system's ledger total, which is the reconciliation that catches this.
Knowledge check
Answer: Its presence means the file is fully processed. A crash earlier leaves no record, so a re-run starts clean instead of skipping a half-finished run.
Answer: Raw is the evidence. The pipeline must never rewrite or delete it, so a bug or a stolen job role cannot destroy the source of truth. Versioning protects it further.
Answer: Without a control record from the sender, the pipeline reconciles against its own input, so it proves only that nothing was lost inside the job. A missing file never reaches the pipeline, so there is nothing to mismatch. Control records and expected-file checks catch that.
Answer: A row-level reject quarantines the row with reason codes and continues. A wrong header means every column would be misread, so the whole file is copied to quarantine and the run fails and alerts.
Answer: The keyed token lets analysts count and join on a counterparty; the masked form lets a human recognise an account; neither reveals the IBAN. Dropping the raw column removes the personal data from the zone analysts use.
Interview questions
- "Walk me through a pipeline you built." — Use this one: arrival event, workflow, validation with reason codes, quarantine, tokenised curated Parquet, gold reports, audit record, control totals, tests that run offline.
- "How do you make a data pipeline idempotent?" — Derive a run id from the input identity (object and version), check for a completion record before doing work, write the completion record last, and make writes safe to repeat. Be honest about cross-file duplicates.
- "What does reconciliation prove and not prove?" — Within-run reconciliation proves no rows or amounts were lost inside the job. It does not prove the sender sent everything or that totals agree with the ledger; those need control records and cross-system checks.
- "How would you test a Glue job?" — Keep the logic in plain Python and test it with pytest against seeded messy data and an answer key; test the thin Glue wrapper in a sandbox with a small run.
Certification practice
A pipeline starts a Glue job from Step Functions. Occasionally the Glue job cannot start because the maximum concurrent runs is reached. The workflow should wait and try again without failing immediately, and alert if it never succeeds. What should the engineer configure?
A. Increase the Glue timeout.
B. A Retry on the Glue task for Glue.ConcurrentRunsExceededException with an interval and back-off, and a Catch that publishes to SNS.
C. Run the job on a schedule instead.
D. Remove the concurrency limit.
Answer: B. Retry handles the transient condition and the catch alerts after attempts are exhausted. A does not address concurrency, C loses event-driven behaviour, D removes a guard rail.
A pipeline's curated zone must support joining on a customer's IBAN while ensuring analysts cannot recover the IBAN. Which approach is the best fit?
A. Store the SHA-256 hash of the IBAN. B. Store an HMAC of the IBAN using a secret key held in Secrets Manager, and drop the raw IBAN column. C. Store the IBAN encrypted with the bucket's SSE-S3 key. D. Store the IBAN in a separate bucket with a different name.
Answer: B. A keyed token is joinable and not guessable without the key. A is reversible by guessing (IBANs are structured), C is transparent to any reader with S3 access, D only moves the data.
A security team wants audit evidence for pipeline runs without copying personal data into logs. Which design is best?
A. Log every row at INFO level. B. Write one structured audit record per run with the actor, source object and version, counts and status, and exclude execution data from workflow logs. C. Disable logging. D. Log only to the console.
Answer: B. It gives evidence without row content. A leaks personal data into logs, C removes evidence, D is not durable.
Completion checklist
- I ran both test suites offline (27 and 18 passed in the author's run) and filled in the prediction table
- I deployed, ran the messy file through the pipeline and matched my results to the prediction
- I produced one
MATCHEDand oneBREAKrun and found the failed-file run in Step Functions - I demonstrated API idempotency and workflow idempotency, and I can explain the cross-file duplicate gap
- I queried the three reports and the audit table in Athena
- I destroyed the stacks and the tag query returned nothing
- I wrote the architecture, security and runbook notes for my portfolio
Key takeaways
- A trustworthy pipeline is repeatable (run id from the input), validated (reason codes), masked (token and mask, no raw IBAN downstream) and auditable (one record per run, written last).
- Row problems quarantine rows; a broken header quarantines the file.
- Reconciliation against a sender's control record is stronger than against your own input, and neither catches a missing or re-sent file without more checks.
- Offline tests on seeded messy data with an answer key make a cloud pipeline safe to change.
- Write the known limits down; they are the next sprint.
Further reading
labs/m07-transaction-pipeline/README.mdand the code inglue/,src/,infra/cdk/andsql/- Secure pipeline design and Reporting with Quick Sight
- AWS Glue best practices: building a secure and reliable data pipeline
- Back to the Module 7 overview and the cheat sheet
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.
Lab 7.6: near-real-time fraud signals with Kinesis, a .NET Lambda and SNS
Run the velocity and high-amount rules locally first, then deploy a one-shard Kinesis stream, a .NET 10 Lambda and an SNS topic, send a burst of transactions, receive exactly one velocity signal, and destroy everything inside the hour.
Module 7 cheat sheet
One-page revision summary for Data Engineering & ETL Pipelines: services, Azure mapping, CLI commands, limits, decision rules, exam traps and cost traps.