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.
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
*_messyfiles 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
- Docker Desktop. You do not need a local .NET SDK: every command below runs in the
mcr.microsoft.com/dotnet/sdk:10.0image. - Data engineering for .NET developers and File formats: CSV, JSON, Parquet, so the three formats are familiar.
- A copy of the repository with the
datasets/andlabs/m07-transaction-pipeline/folders.
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
| Path | Role in the lab |
|---|---|
datasets/generator/src/Harbourline.Datasets.Cli/Program.cs | The 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/outputdatasets/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.csvtransaction_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:59ZRead the two rows against the schema:
transaction_idis a UUID v7, time-ordered, so ids sort roughly by creation time.amounthas 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_keyis what a client resends on a retry. Two rows with the same key but different ids are a retry, not two payments.created_atis UTC with a trailingZ; the dates are plain ISOyyyy-MM-dd.
Compare the three formats
ls -l datasets/output/csv/transactions.csv datasets/output/json/transactions.jsonl datasets/output/parquet/transactions.parquet| Format | Bytes (small, 1,012 rows) | Notes |
|---|---|---|
| CSV | 220,622 | Everything is text; header row |
| JSON Lines | 457,294 | Field names repeated on every line, camelCase; the API ingest path uses this shape |
| Parquet | 112,894 | Binary 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| Defect | What it looks like | Why a bank sees it |
|---|---|---|
duplicate | An exact copy, or a retry with a new id and the same idempotency_key | Network retries; a partner re-sends a file |
bad_date | 10/28/2026, 2026-02-30, 20261028, 5 Oct 2026, an Excel serial, N/A, empty | A spreadsheet in the middle of the chain |
invalid_iban | Check digits that fail mod-97 | Typing errors, truncation |
negative_amount | A credit (transfer_in) with a negative sign | Sign conventions differ between systems |
wrong_currency | GPB, EURO, gbp, £, UKP, a trailing space | Free-text entry |
mixed_encoding | Café instead of Café, or a whole line saved as ISO-8859-1 in a UTF-8 file | A legacy system with a different code page |
late_arriving | A row created long after its value date, appended at the end | Back-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) andLATE_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_amountdefects but the validator reports 8AMOUNT_SIGNissues, 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: MATCHEDmeans 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.csvrow_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 fileRows 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.csvExpected: 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.
| Size | Transactions | Period | Measured here |
|---|---|---|---|
small | 1,012 | 2026-10-01 to 2026-11-16 | CSV 220,622 bytes; Parquet 112,894 |
medium | 100,146 | 2026-05-01 to 2026-11-16 | About 17 seconds; transactions.csv 21,838,184 bytes; transactions.parquet 10,379,367 |
large | 1,000,000+ | 2025-12-01 to 2026-11-16 | Not 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:
ExportOptions.cs—CommandLine.Parseturns the flags into anExportOptionsrecord.--seeddefaults to20261117;--formattakes a comma list; an unknown option returns exit code 2 with the usage text.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.Generation/— the seeded simulator. A singleRandomseeded from the option drives customers, accounts and postings, so nothing depends on the clock.Messy/— takes a clean row stream and applies a profile of defects at fixed rates, writing the answer key as it goes.Harbourline.DataPipeline.Core/Validation/TransactionValidator.cs— the rules behindhl-pipeline validate. The Python twin inglue/src/harbourline_etl/transactions.pyimplements 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/.netdomains 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/wintoC:/Program Files/Git/w. Prefix the command or use PowerShell. - Forgetting
--messyand then looking fortransactions_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_REPAIREDrows 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
| Symptom | Likely cause | Fix |
|---|---|---|
Cannot find /w/generator | Path mount is wrong, or you are not in the repository root | Run from the repository root; check the -v "$PWD/datasets:/w" mount |
| NuGet restore fails in the container | No network, or a proxy | Check Docker's network settings and retry; the named volume keeps what was downloaded |
Unknown option '--messey' | Typo | Run with --help for the option list |
| Output folder owned by root and cannot be deleted from Windows | Container wrote as root | Delete from Explorer, or run docker run --rm -v "$PWD/datasets:/w" alpine rm -rf /w/output |
| Different bytes from a colleague's run | Different generator version or seed | Compare manifest.json (seed, size, version) |
| Out of memory in Docker | Memory limit too low for large | Use --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
20261117and then with seed42. For each, record the number of transactions and the first transaction id. Which numbers changed and why? Then runhl-pipeline validateon 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
Seed 20261117 gives 1,012 transactions and first id 01a15a7b-04b0-77ec-a5d2-2e4a1eb2627a. A different seed changes customers, amounts, dates and ids, and may change the row count by a few rows, because the size is a target and the customers' simulated activity decides the exact count. Both clean files validate with 0 rejects, which shows that the validator's rules do not depend on the seed. The difference is that exact-total assertions in the tests only hold for seed 20261117.
Join on row number. The warnings are mixed_encoding (22; the validator repairs the text and keeps the row) and late_arriving (7; the row is accepted and flagged). The remaining defects should all appear as rejects: bad dates, invalid IBANs, wrong currencies, negative credits and duplicates. A defect that appears in the key but is neither rejected nor warned is a hole in your rules; here there are none, which is what the test Every_injected_defect_is_found_and_nothing_else asserts. Two defects can share a row, so count by row, not by line.
Knowledge check
Answer: The simulator draws from a seeded random source and derives ids and timestamps from the simulated clock, never from the wall clock, so the same seed, size and generator version produce the same bytes. Change the generator code and the output changes too.
Answer: The messy tables model a raw landing zone. A value like 10/28/2026 cannot be stored in a DATE column, so raw data stays text until the pipeline validates and types it.
Answer: The validator emits each input row exactly once, either accepted or rejected. A pipeline where these three numbers do not add up has lost or duplicated rows.
Answer: No. It is a warning: the text was repaired and the row was accepted. The warning code is kept in the dq_warnings column.
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
A data engineer must show that a new ingestion job detects malformed records. Which approach gives the strongest, repeatable evidence?
A. Run the job on last week's production file and eyeball the output. B. Generate synthetic data with known injected defects and an answer key, run the job, and assert that each defect is reported and no clean row is rejected. C. Run the job on an empty file. D. Disable validation in test.
Answer: B. It is repeatable and measures both misses and false alarms. A uses production data and has no ground truth, C tests nothing, D removes the thing being tested.
A producer retries a request after a timeout and the same payment is sent twice with different row ids but the same client-supplied key. What should the pipeline use to recognise the duplicate?
A. The row id. B. The idempotency key. C. The file name. D. The ingestion time.
Answer: B. The row id differs and the timing differs. The client-supplied key identifies the logical request.
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 validateon the messy and clean files and explained 1,036 = 969 + 67 - I deleted generated output and checked that no
bin/orobj/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
datasets/README.mdin the repository: the schema, the defect list, the sizes and the regeneration commands- Next: Lab 7.2: CSV to partitioned Parquet
- Module overview: Module 7
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.
Reporting with Amazon Quick Sight: datasets, SPICE and row-level security
Turn the pipeline's gold tables into a reconciliation dashboard: connect Quick Sight to Athena, choose SPICE or direct query, restrict rows per group with a permissions dataset, and know the free alternative when you do not want a paid seat.
Lab 7.2: convert CSV to partitioned Parquet with local Spark
Run PySpark 3.5 in Docker (the same Spark and Python versions as AWS Glue 5.0) to turn transaction CSV into typed, partitioned Parquet, compare five layouts by size, and discover why partitioning 1,000 rows makes the data bigger, not smaller.