Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

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.

advanced⏱ 6 h 🟡 Low costDEA-C01SAA-C03SCS-C03

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:

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:

ItemApproximate cost (us-east-1, README estimate checked 2026-11-18 ⚠ VERIFY)
Glue Spark job, 2 × G.1X, about 2 minutes billed per runAbout USD 0.03 per run at USD 0.44 per DPU-hour
Glue Data Quality inside the jobAdds DPU time to the same run
KMS customer managed keyUSD 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 APIPennies at lab volume (free tier may apply)
AthenaUSD 5 per TB scanned, 10 MB minimum per query
S3Negligible 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 overviewWhere it is done
1Receive records through an API and an SFTP dropIngestFunction.cs (HTTP API, IAM auth); local atmoz/sftp container
2Store raw data unchanged, encrypted and versionedDataLakeStack.cs: raw bucket SSE-KMS, versioning, TLS-only
3Trigger processing from the arrival eventEventBridge rule on Object Created under incoming/
4Validate and transform, quarantine with a reason codetransactions_etl.py using harbourline_etl
5Store processed data as partitioned ParquetCurated zone year=/month=/day=
6Query in Athena through the Glue catalogsql/athena/02_project_tables.sql with partition projection
7Daily summary, reconciliation report, suspicious-activity signalsGold tables and 03_project_reports.sql views
8Execution logs and an audit record for every runStep 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:cacheprovider

Expected: 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:

FileRowsAcceptedQuarantinedFraud signalsDaily-summary groups
transactions_messy.csv1,036969671 (HIGH_AMOUNT)225
transactions.csv (clean)1,0121,01201 (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.sql

02_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 with reason_codes, issues, row_number and the raw record. It contains raw personal data: treat it like raw.
  • Gold: daily_summary/, reconciliation/ and fraud_signals/ partitioned by run id.
  • Audit: audit/ingest_run_id=run-.../audit.json with event_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 and reconciliation_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.json

The 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.00

When 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)

LimitWhy it existsWhat 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 totalsThe run id includes the version id by design; no cross-file key storeUse 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 deleteKeeps the job simpleIceberg tables for MERGE, corrections and erasure handling
Fraud signals in batch use a fixed ten-minute windowSame as the streaming rules, deliberatelySliding window or a stream processor
Late-arriving rows are flagged, not re-aggregatedDaily summary is per runRe-run affected dates; read is_late_arriving
The token key is a single secret, rotated by re-tokenisingDeterministic tokens must stay joinableVersioned token prefix and a re-tokenise runbook (Secure pipeline design)
Gold partitions need MSCK REPAIRGold is partitioned by run idPartition 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 freeRead 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 PutObject under incoming/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 111122223333 in the SQL → tables point at buckets that do not exist and queries return nothing.
  • Forgetting MSCK REPAIR TABLE for 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 MATCHED status 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

SymptomLikely causeFix
Workflow never starts after uploadEventBridge 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 KMSRole lacks kms:Decrypt or GenerateDataKey, or the Logs statement was removed from the key policyThe 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 totalsA 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 curatedWrong account id in storage.location.template, or a wrong date filterReplace 111122223333 everywhere; check partitions with year, month, day filters
Gold views are emptyPartitions not loadedRun the MSCK REPAIR TABLE statements
ProvisionedThroughputExceeded in the streaming producerOne shard allows 1 MB/s or 1,000 records/sThe producer retries with back-off; lower --limit
.NET tests: Sample file ... not foundTests copied without data/samplesKeep the data/ folder next to tests/
cdk deploy cannot find dotnetCDK runs dotnet via cdk.jsonInstall 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_key was accepted by an earlier file, using DynamoDB with a conditional put, and describe how v_daily_summary should 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 is MATCHED unhelpful 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

Knowledge check

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

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 MATCHED and one BREAK run 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

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