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

Lab 7.1: generate seeded banking transactions, including the messy ones

Use the C# dataset generator to produce 1k, 100k or 1M reproducible Harbourline transactions in CSV, JSON Lines and Parquet, inject deliberate defects with an answer key, and prove with the pipeline's own validator that every defect is found.

beginner⏱ 1 h 🟢 FreeDEA-C01

Assumes: .NET 10 LTS (in Docker) · Bogus 35.6.5 · Parquet.Net 6.1.0 · xUnit v3 4.0.3 · seed 20261117 · datasets/generator and labs/m07-transaction-pipeline. Verified against official sources on 2026-11-18.

Learning objectives

By the end of this lab you can:

  • Generate the small, medium and large Harbourline datasets from a seed and explain why the same seed gives byte-identical files.
  • Read the transactions schema and the ledger rules (balance, signs, statuses) well enough to spot a wrong row by eye.
  • Produce *_messy files with seven kinds of defect and use the answer key (*_messy_defects.csv) to grade a cleaning job.
  • Run the lab's C# validator (hl-pipeline validate) and read its reject report and control totals.
  • Explain why a data pipeline needs messy test data and an answer key, not only clean samples.

Prerequisites

Cost label

Free. Everything runs locally in Docker. No AWS account is used and no data leaves your machine. All data is fictional: Harbourline Bank does not exist, e-mail addresses use example.com, IBANs use the invented bank code HLBK or come from a test generator, and card data is tokens only.

Scenario

Your team is about to build a transaction pipeline, and the first question from the testers is: "What data do we test with?" Real customer data is off limits. Hand-typed samples are too clean: they never contain the duplicate retry, the American-format date or the line saved in the wrong encoding that arrives on a Monday morning. You will generate data that looks like a bank's, with a known number of known defects, so that every later lab can say "the job found 100 percent of the planted defects and nothing else".

Architecture

The generator is a library (Harbourline.Datasets), a command-line tool (harbourline-datasets) and a test project, all under datasets/generator. Customers are simulated one at a time (salary on the 25th, direct debits on fixed days, card spend by merchant category, occasional reversals), rows are streamed to disk, and memory stays flat even for a million transactions.

The files

PathRole in the lab
datasets/generator/src/Harbourline.Datasets.Cli/Program.csThe command line: --size, --seed, --format, --out, --messy, --sftp-batch
datasets/generator/src/Harbourline.Datasets/Messy/Defect injection and the per-table defect profiles
datasets/samples/The committed small sample (seed 20261117), including csv/transactions_messy*.csv
labs/m07-transaction-pipeline/data/samples/Copies of the transaction files used as test fixtures
labs/m07-transaction-pipeline/src/Harbourline.DataPipeline.Cli/hl-pipeline validate, the local validator

Part A: generate the small dataset

All commands are for Git Bash, from the repository root. The MSYS_NO_PATHCONV=1 prefix stops Git Bash rewriting /w into a Windows path.

Run the generator's own tests first

MSYS_NO_PATHCONV=1 docker run --rm --memory 3g -v "$PWD/datasets:/w" -v cea-nuget-m07:/root/.nuget/packages \
  -w /w/generator mcr.microsoft.com/dotnet/sdk:10.0 sh -c \
  "dotnet test --solution Harbourline.Datasets.slnx; find . -type d \( -name bin -o -name obj -o -name TestResults \) -prune -exec rm -rf {} +"

Expected: Test run summary: Passed! total: 31 failed: 0 succeeded: 31. These tests check determinism, IBAN validity, referential integrity, that every messy defect type really appears in the file, balance reconciliation, XML control sums and the SFTP trailer. The first run downloads NuGet packages into the named volume cea-nuget-m07; later runs reuse it.

Generate the small dataset with every format and the messy copies

MSYS_NO_PATHCONV=1 docker run --rm --memory 3g -v "$PWD/datasets:/w" -v cea-nuget-m07:/root/.nuget/packages \
  -w /w/generator mcr.microsoft.com/dotnet/sdk:10.0 sh -c \
  "dotnet run --project src/Harbourline.Datasets.Cli -c Release -- --size small --seed 20261117 --format csv,json,parquet,xml --out /w/output --messy --sftp-batch; \
   find . -type d \( -name bin -o -name obj \) -prune -exec rm -rf {} +"

On the author's machine this finished in about 15 seconds after the image was available, and printed:

Harbourline datasets: size=small seed=20261117 period=2026-10-01..2026-11-16 formats=All messy=True
  accounts                   57 rows
  audit_events              500 rows
  beneficiaries              47 rows
  cards                      51 rows
  customers                  31 rows
  exchange_rates            288 rows
  payments                   76 rows
  transactions            1,012 rows
  accounts_messy             59 rows (with deliberate defects)
  customers_messy            31 rows (with deliberate defects)
  payments_messy             77 rows (with deliberate defects)
  transactions_messy      1,036 rows (with deliberate defects)
Wrote 50 files to /w/output

datasets/output/ is git-ignored, so the files stay on your machine.

Look at the transactions file with your own eyes

head -3 datasets/output/csv/transactions.csv
transaction_id,account_id,booking_date,value_date,amount,currency,type,channel,counterparty_name,counterparty_iban,merchant_category_code,reference,status,idempotency_key,created_at
01a15a7b-04b0-77ec-a5d2-2e4a1eb2627a,ACC-1000000001,2026-10-20,2026-10-20,403.41,GBP,transfer_in,web,Dandre Mitchell,GB45XWCP01710545433821,,INITIAL DEPOSIT,booked,b50a25c2-acfd-4a2d-8a54-3ed9df3fca51,2026-10-20T18:02:54Z
01a172d5-d5f8-75f5-b05e-314cfea1cd18,ACC-1000000001,2026-10-25,2026-10-25,-2.13,GBP,card_payment,card,Riverside Rail,,4111,POS 1507 RIVERSIDE RAIL,booked,07dcf311-0260-41bf-b9b4-997342959661,2026-10-25T11:32:59Z

Read the two rows against the schema:

  • transaction_id is a UUID v7, time-ordered, so ids sort roughly by creation time.
  • amount has exactly two decimals; credits are positive (transfer_in), debits negative (card_payment).
  • A card payment has an MCC (4111) and no counterparty IBAN; a transfer has an IBAN and no MCC. Empty fields mean null.
  • idempotency_key is what a client resends on a retry. Two rows with the same key but different ids are a retry, not two payments.
  • created_at is UTC with a trailing Z; the dates are plain ISO yyyy-MM-dd.

Compare the three formats

ls -l datasets/output/csv/transactions.csv datasets/output/json/transactions.jsonl datasets/output/parquet/transactions.parquet
FormatBytes (small, 1,012 rows)Notes
CSV220,622Everything is text; header row
JSON Lines457,294Field names repeated on every line, camelCase; the API ingest path uses this shape
Parquet112,894Binary and columnar; DECIMAL(18,2), DATE, TIMESTAMP

Even at this size Parquet is about half of CSV and a quarter of JSON Lines. Lab 7.2 shows what happens when you add partitions.

Prove determinism

Run the Part A generator twice, to output and to output2, then compare:

cd datasets && sha256sum output/csv/transactions.csv output2/csv/transactions.csv && cd ..

The two hashes are identical. The generator uses a seeded random source, UUID v7 values derived from the simulated clock and no wall-clock time, so the same seed, size and generator version always produce the same bytes. This is what lets the tests assert exact totals later (for example, accepted GBP total 124,187.38), and it is why changing even one line of generator code changes the output, so you regenerate the committed samples when you change it.

Part B: the messy files and the answer key

Count the planted defects

cut -d, -f3 datasets/output/csv/transactions_messy_defects.csv | sort | uniq -c
     16 bad_date
     24 duplicate
      9 invalid_iban
      7 late_arriving
     22 mixed_encoding
      7 negative_amount
     12 wrong_currency

(plus one header line counted as defect). That is 97 planted defects in a file of 1,036 rows. Each line of the key tells you the row number in the messy file, the key, the defect, the column, the original value and the messy value:

row_number,key,defect,column,original_value,messy_value,note
31,01a1400e-...,bad_date,booking_date,2026-10-15,,unparseable or non-ISO date
42,01a181cd-...,bad_date,value_date,2026-10-28,10/28/2026,unparseable or non-ISO date
56,01a1a37d-...,duplicate,transaction_id,01a1a37d-...,01a1a37d-...,exact duplicate row
DefectWhat it looks likeWhy a bank sees it
duplicateAn exact copy, or a retry with a new id and the same idempotency_keyNetwork retries; a partner re-sends a file
bad_date10/28/2026, 2026-02-30, 20261028, 5 Oct 2026, an Excel serial, N/A, emptyA spreadsheet in the middle of the chain
invalid_ibanCheck digits that fail mod-97Typing errors, truncation
negative_amountA credit (transfer_in) with a negative signSign conventions differ between systems
wrong_currencyGPB, EURO, gbp, £, UKP, a trailing spaceFree-text entry
mixed_encodingCafé instead of Café, or a whole line saved as ISO-8859-1 in a UTF-8 fileA legacy system with a different code page
late_arrivingA row created long after its value date, appended at the endBack-dated postings and delayed feeds

In the messy tables every column is a string, including in Parquet. That is the raw-landing-zone shape: you cannot trust a type before you have validated the value.

Run the lab's validator on the messy file

The validator is a C# twin of the PySpark job's rules. It reads a file, applies the rules, writes a reject report and prints control totals. The lab's sample copy of the file is in labs/m07-transaction-pipeline/data/samples/.

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 run --project src/Harbourline.DataPipeline.Cli -c Release -- validate data/samples/transactions_messy.csv --quarantine rejects.csv; \
   find . -type d \( -name bin -o -name obj \) -prune -exec rm -rf {} +"
{
  "rowsRead": 1036,
  "rowsAccepted": 969,
  "rowsQuarantined": 67,
  "issuesByCode": {
    "AMOUNT_SIGN": 8,
    "DUPLICATE": 24,
    "ENCODING_REPAIRED": 22,
    "INVALID_CURRENCY": 12,
    "INVALID_DATE": 16,
    "INVALID_IBAN": 9,
    "LATE_ARRIVING": 7
  },
  "reconciliation": [
    { "Currency": "EUR", "AcceptedAmountTotal": 5040.74, "QuarantinedAmountTotal": -1112.09, "Status": "MATCHED" },
    { "Currency": "GBP", "AcceptedAmountTotal": 124187.38, "QuarantinedAmountTotal": -26402.47, "Status": "MATCHED" }
  ]
}

Read it like an operator:

  • 1,036 rows read = 969 accepted + 67 quarantined. Every row comes out exactly once.
  • ENCODING_REPAIRED (22) and LATE_ARRIVING (7) are warnings: the row is accepted, repaired or flagged, and the warning is kept in the data. The other codes are rejects.
  • The answer key has 7 negative_amount defects but the validator reports 8 AMOUNT_SIGN issues, because an injected duplicate or date defect can sit on a row that also trips the sign rule. The test suite checks defects per row, not per code, and passes.
  • Status: MATCHED means the control totals agree: the source total equals accepted plus quarantined for each currency.

Read the reject report

head -8 labs/m07-transaction-pipeline/rejects.csv
row_number,reason_codes,messages
31,INVALID_DATE,booking_date: not an ISO date: ''
42,INVALID_DATE,value_date: not an ISO date: '10/28/2026'
56,DUPLICATE,transaction_id: transaction_id seen earlier in this file
62,AMOUNT_SIGN,amount: transfer_in must be a positive credit
69,INVALID_CURRENCY,currency: not an allowed ISO 4217 code: 'EURO'
85,INVALID_CURRENCY,currency: not an allowed ISO 4217 code: 'gbp'
91,DUPLICATE,transaction_id: transaction_id seen earlier in this file

Rows 31, 42, 56 appear in both the answer key and the report. A reject report is a deliverable: the partner needs to know what to fix, and your operations team needs a reason for every row that did not reach the ledger. Delete rejects.csv when you finish; it is a generated file.

Run the clean file for comparison

dotnet run --project src/Harbourline.DataPipeline.Cli -- validate data/samples/transactions.csv

Expected: 1,012 rows read, 1,012 accepted, 0 quarantined, no issues; accepted totals GBP 152,326.64 and EUR 5,857.45, both MATCHED. A pipeline that rejects clean data is as wrong as one that accepts dirty data, so you need both files.

Part C: larger sizes

Only generate these if you need volume for Lab 7.2 or Lab 7.3.

SizeTransactionsPeriodMeasured here
small1,0122026-10-01 to 2026-11-16CSV 220,622 bytes; Parquet 112,894
medium100,1462026-05-01 to 2026-11-16About 17 seconds; transactions.csv 21,838,184 bytes; transactions.parquet 10,379,367
large1,000,000+2025-12-01 to 2026-11-16Not run in this lab; the README estimates about 350 MB CSV and 150 MB Parquet
MSYS_NO_PATHCONV=1 docker run --rm --memory 3g -v "$PWD/datasets:/w" -v cea-nuget-m07:/root/.nuget/packages \
  -w /w/generator mcr.microsoft.com/dotnet/sdk:10.0 sh -c \
  "dotnet run --project src/Harbourline.Datasets.Cli -c Release -- --size medium --format csv,parquet --out /w/output-medium; \
   find . -type d \( -name bin -o -name obj \) -prune -exec rm -rf {} +"

Medium produced 1,499 accounts, 860 customers, 50,000 audit events and 100,146 transactions. Add --messy to roughly double the four affected tables.

Code explained

You do not write code in this lab, but you should know where each behaviour lives:

  1. ExportOptions.cs — CommandLine.Parse turns the flags into an ExportOptions record. --seed defaults to 20261117; --format takes a comma list; an unknown option returns exit code 2 with the usage text.
  2. Program.cs — sets the invariant culture first, so decimals print with a dot on every machine, which is why a German or French Windows locale does not change the bytes. A Ctrl+C cancels cleanly with exit code 130.
  3. Generation/ — the seeded simulator. A single Random seeded from the option drives customers, accounts and postings, so nothing depends on the clock.
  4. Messy/ — takes a clean row stream and applies a profile of defects at fixed rates, writing the answer key as it goes.
  5. Harbourline.DataPipeline.Core/Validation/TransactionValidator.cs — the rules behind hl-pipeline validate. The Python twin in glue/src/harbourline_etl/transactions.py implements the same rules, and both are tested against this answer key.

Security considerations

⚠ Learning shortcut — not for production

Synthetic data is safe to share and commit, which is exactly why it is used here. The shortcut is that it hides how hard the real job is: real data has unknown defects and no answer key. In a bank, test data created from production is a data-protection risk, even if you "scrub" it. Generate, do not copy. If a project insists on production-like volume, ask your data-protection officer first and use masked or tokenised extracts.

  • The generator never produces a card number. Cards are tokens plus the last four digits (pan_last4), because storing a full card number brings you into PCI DSS scope.
  • E-mail addresses are limited to the reserved example.com/.org/.net domains and phone numbers to the Ofcom drama range, so a generated record can never reach a real person.
  • Do not commit datasets/output/. It is git-ignored for a reason: generated volume bloats the repository.
  • The validator's local run uses a throwaway token key (local-dev-key-not-a-secret). Real runs get the key from Secrets Manager; see Secure pipeline design.

Common mistakes

  • Running the generator on Windows without MSYS_NO_PATHCONV=1 → Git Bash turns /w into C:/Program Files/Git/w. Prefix the command or use PowerShell.
  • Forgetting --messy and then looking for transactions_messy.csv → the file only exists when you ask for it.
  • Comparing two runs made with different seeds and concluding the generator is "non-deterministic" → the seed is part of the input.
  • Treating warnings as rejects (or the reverse) → ENCODING_REPAIRED rows are in the accepted count.
  • Editing the committed sample files "to fix a typo" → every test that asserts exact totals will fail; regenerate instead.

Troubleshooting

SymptomLikely causeFix
Cannot find /w/generatorPath mount is wrong, or you are not in the repository rootRun from the repository root; check the -v "$PWD/datasets:/w" mount
NuGet restore fails in the containerNo network, or a proxyCheck Docker's network settings and retry; the named volume keeps what was downloaded
Unknown option '--messey'TypoRun with --help for the option list
Output folder owned by root and cannot be deleted from WindowsContainer wrote as rootDelete from Explorer, or run docker run --rm -v "$PWD/datasets:/w" alpine rm -rf /w/output
Different bytes from a colleague's runDifferent generator version or seedCompare manifest.json (seed, size, version)
Out of memory in DockerMemory limit too low for largeUse --memory 3g or more, or generate medium

Cost and cleanup

Nothing costs money. Remove the generated data and, if you want, the NuGet cache volume.

rm -rf datasets/output datasets/output2 datasets/output-medium labs/m07-transaction-pipeline/rejects.csv
# Only if you no longer need the cached packages (they will be downloaded again next time):
docker volume rm cea-nuget-m07
# bin/ and obj/ folders were already removed by the commands above; check:
find datasets labs/m07-transaction-pipeline -type d \( -name bin -o -name obj -o -name TestResults \)

Exercises

  • 🟢 Guided: Generate the small dataset with seed 20261117 and then with seed 42. For each, record the number of transactions and the first transaction id. Which numbers changed and why? Then run hl-pipeline validate on the clean file from each seed and confirm both are fully accepted.
  • 🟡 Challenge: Using only the answer key and the reject report, write a short script (C#, PowerShell or a spreadsheet) that lists every planted defect the validator did not reject and explains, per defect type, why (warnings versus rejects).
  • 🔴 Live problem: A partner swears they send "perfectly clean" files, yet your finance team finds 2 percent of their rows quarantined. Plan a conversation: which three artefacts do you bring (counts by reason code, a sample of rejected rows with the rule that fired, the control-total reconciliation), and how do you decide whether the partner or your rules are wrong?

Assignment

Add a test-data/README.md to your portfolio repository that explains your seed, your sizes and your defect catalogue, with a table of defect type, the rule that detects it and the reason code. Include the output of the validator on a messy file you generated yourself, and the answer-key counts.

Solutions

Knowledge check

Interview questions

  • "How would you test a pipeline without production data?" — Generate seeded synthetic data with injected, catalogued defects and an answer key, assert exact outcomes, and include a clean file to prove the rules do not over-reject.
  • "What is an idempotency key and why is it in a transactions table?" — A client-supplied id that identifies one logical request across retries; the pipeline uses it to recognise a retry that arrives with a new row id.
  • "Why do you keep control totals?" — They prove that every row and every amount ended up either accepted or quarantined, which is what reconciliation checks.

Certification practice

Completion checklist

  • The generator tests pass (31 passed in the author's run) and I generated the small dataset
  • I proved two runs with the same seed give identical hashes
  • I counted the planted defects by type and read the answer key
  • I ran hl-pipeline validate on the messy and clean files and explained 1,036 = 969 + 67
  • I deleted generated output and checked that no bin/ or obj/ folders remain

Key takeaways

  • Seeded generation makes test data reproducible, so tests can assert exact numbers.
  • Messy data plus an answer key lets you measure a cleaning job for misses and false alarms.
  • Warnings repair or flag a row; rejects quarantine it. Both are counted.
  • Raw data stays text until validated; Parquet types come after.
  • Control totals (read = accepted + quarantined, per currency) are the first reconciliation.

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