Data engineering for .NET developers
Learn the vocabulary and the core trade-offs of data engineering (batch vs stream, ETL vs ELT, idempotent loads, lineage) by building a tiny end-of-day pipeline in C# over Harbourline's fictional transactions.
Assumes: .NET 10 LTS (file-based apps) · DuckDB CLI 1.x (optional) · AWS CLI v2 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain what a data engineer does, and how it differs from the application development you already do.
- Choose between batch and stream processing for a banking feed, and justify the choice with latency, cost and correctness arguments.
- Explain ETL and ELT, and say where each one runs on AWS (Glue, Lambda, Athena, Redshift).
- Use the DEA-C01 vocabulary correctly: data lake, zone, partition, schema-on-read, idempotent load, watermark, late-arriving data, lineage, data contract.
- Build and run a tiny, idempotent end-of-day pipeline in C# over the Harbourline sample data, and reproduce the same result with SQL (ELT style).
Prerequisites
- Module 1: AWS fundamentals, especially S3 and IAM roles.
- Module 6: Databases, for the OLTP side of the story.
- Tools: .NET 10 SDK (or Docker), a text editor. Optional: the DuckDB CLI for the SQL version. No AWS account is needed for this lesson.
- The repository's sample data in
datasets/samples/(generated with seed20261117; seedatasets/README.md).
Why this matters in banking
At 23:30 every night, Harbourline's core banking system produces an end-of-day extract. By 07:00, finance expects a reconciliation report, the fraud team expects yesterday's card spend by merchant category, and the regulatory reporting team expects totals per product. None of those consumers wants to query the core banking database directly: it is busy, it is critical, and its schema was designed for posting transactions, not for analysis.
Things go wrong in very ordinary ways:
- The job runs twice after a retry and double-counts a day, so the reconciliation is out by £1.4 million and nobody knows why until Tuesday.
- A partner's file arrives late, after the report ran. The totals are "right" for the data that existed at 07:00, but wrong for the business day. Nobody wrote down which.
- Someone copies the raw extract, with names and IBANs, into a shared analytics folder "just to have a look". That is now a data-protection question for the DPO.
Data engineering is the discipline that makes these flows repeatable, explainable and safe. You already have most of the skills. What changes is the scale, the tools and a few new failure modes.
The idea in plain language
As an application developer you mostly build OLTP systems (online transaction processing): many small reads and writes, one customer at a time, with strict consistency. An Accounts API reads one account, a Payments Service writes one payment.
A data engineer builds systems that move and reshape data in bulk so that other people can analyse it. Think OLAP (online analytical processing): few, large queries that scan millions of rows, such as "total card spend by merchant category for October".
The work splits into a few repeated steps:
- Ingest: get data out of the source (files, APIs, database change streams, event streams) and land it somewhere durable, unchanged.
- Store: keep it in a layout that is cheap, secure and fast to read.
- Transform: clean, validate, de-duplicate, mask personal data, join and aggregate.
- Serve: make it queryable (SQL, dashboards, extracts for regulators).
- Operate: schedule, monitor, retry, alert, audit and prove what happened.
Analogy: The core banking system is the branch counter: fast, one customer at a time, everything must balance immediately. The data platform is the back office that receives the day's paperwork in boxes, files the originals untouched in the archive (raw), produces cleaned and checked copies (curated), and writes the summary that goes to head office (consumption). If a box is delivered twice, the back office must notice and not file it twice.
What problem the AWS services solve
On AWS, data engineering is a set of building blocks rather than one product. The module covers each in depth; here is the map so the vocabulary makes sense.
| Job | AWS service(s) | One-line role |
|---|---|---|
| Durable storage for files of any size | Amazon S3 | The data lake: cheap, durable object storage organised into zones |
| A shared table catalogue | AWS Glue Data Catalog | A Hive-compatible metastore: databases, tables, columns, partitions |
| Discover schemas | Glue crawlers | Scan S3 and propose table definitions |
| Large transforms | AWS Glue (Spark) jobs | Managed Apache Spark; you write PySpark or Scala |
| Small transforms | AWS Lambda | Event-driven functions, C# or Python, up to 15 minutes |
| SQL over files in S3 | Amazon Athena | Serverless SQL (Trino-based), pay per data scanned |
| Data warehouse | Amazon Redshift / Redshift Serverless | Columnar MPP warehouse for heavy, repeated BI |
| Streaming ingestion | Kinesis Data Streams, Amazon Data Firehose, Amazon MSK | Ordered event streams and managed delivery to S3 |
| Orchestration | Step Functions, EventBridge, EventBridge Scheduler, MWAA | Run steps in order, on a schedule or on events |
| Database change capture | AWS DMS | Full load plus CDC from SQL Server into S3 or Kinesis |
| Fine-grained access | AWS Lake Formation | Table, column, row and tag permissions on the catalogue |
| Data quality | Glue Data Quality | Declarative rules (DQDL) evaluated inside jobs or on tables |
| Dashboards | Amazon Quick Sight (part of AWS Quick) | BI with row-level security |
The honest limitation: there are many services, they overlap, and each has its own pricing model (per GB stored, per TB scanned, per DPU-hour, per GB-second, per shard-hour). Most of this module is about choosing the smallest set that meets the requirement.
Batch vs stream
| Batch | Stream | |
|---|---|---|
| Unit of work | A bounded set: a file, a day, a partition | An unbounded sequence of events |
| Latency | Minutes to hours | Milliseconds to seconds |
| Typical AWS shape | S3 + Glue/Lambda + Athena, orchestrated by Step Functions | Kinesis Data Streams or MSK + Lambda/Flink, often also landing in S3 via Firehose |
| Correctness | Easy to reason about: the input is complete when you start | Harder: events arrive late and out of order; you need windows and watermarks |
| Cost profile | Pay while the job runs | Pay while the stream exists (shards, brokers), even when idle |
| Banking examples | End-of-day reconciliation, regulatory returns, statement production, monthly interest | Card fraud signals, real-time balance notifications, AML velocity rules |
Most banks run both: a stream for decisions that must happen now (block a card), and a batch for numbers that must be right (the ledger reconciliation). A common mistake is to build everything as a stream "because it is modern". Streams cost money when idle and are harder to make exactly correct. Start with batch unless a business requirement needs seconds.
Micro-batch sits in between: small batches every minute or so (Spark Structured Streaming, Firehose buffering). It is often the pragmatic answer for "near real time".
ETL vs ELT
- ETL (extract, transform, load): transform the data before it reaches the analytical store. You know this from SSIS: a package reads from SQL Server, applies data-flow transforms in memory, and writes to the warehouse. On AWS: a Glue Spark job or a Lambda function reads raw files, validates and masks, and writes curated Parquet.
- ELT (extract, load, transform): load the raw data first, then transform it inside the analytical engine with SQL. On AWS: land raw files in S3, then run Athena
CREATE TABLE AS SELECT(CTAS) orINSERT INTO, or load into Redshift and transform with SQL (often with dbt).
| Question | Leans ETL | Leans ELT |
|---|---|---|
| Must personal data be masked before anyone can query it? | Yes: mask in the job, never land unmasked data in a queryable zone | Possible only if raw is locked down tightly |
| Are transforms complex (parsing, custom validation, IBAN checks, ML)? | Yes: code in Spark/Python/C# | SQL becomes painful |
| Is the team SQL-first? | Yes | |
| Do you want to re-derive history when rules change? | Keep raw and re-run | Natural: raw is already loaded |
In practice Harbourline does both: ETL from raw to curated (validation, masking, quarantine) and ELT from curated to consumption (SQL aggregates for reports). This is the shape of the module project in labs/m07-transaction-pipeline.
The vocabulary you will meet on DEA-C01
| Term | Plain meaning | .NET mental hook |
|---|---|---|
| Data lake | Files in object storage, organised by convention, queried in place | A very large, well-organised file share |
| Zone (raw/bronze, curated/silver, consumption/gold) | Stages of refinement, each with its own access rules | Staging tables → clean tables → reporting views |
| Partition | A folder-like key prefix such as year=2026/month=11/day=16/ so engines can skip data | A partitioned table in SQL Server, but enforced by naming |
| Schema-on-read | The file has no enforced schema; the reader applies one when querying | Deserialising JSON into a DTO at read time |
| Schema-on-write | Data must match a schema to be stored | INSERT into a typed table |
| Columnar format (Parquet, ORC) | Stores each column together, compressed, with statistics | A clustered columnstore index |
| Idempotent load | Running the same load twice gives the same result | An idempotency key on a POST |
| Watermark | "We have processed everything up to time T" | A high-water mark column in an incremental SSIS load |
| Job bookmark | Glue's built-in watermark for files already processed | Same idea, managed for you |
| Late-arriving data | Records that arrive after their business period was processed | A back-dated posting |
| CDC (change data capture) | A stream of inserts, updates and deletes from a database log | SQL Server CDC tables |
| Lineage | Which inputs and code produced an output | Tracing a report figure back to its source rows |
| Data contract | The agreed schema and quality rules between producer and consumer | A versioned API contract (OpenAPI) for data |
| Quarantine | A place for rejected rows, with a reason code | A dead-letter queue for rows |
| SCD (slowly changing dimension) | Keeping history of attributes that change, such as a customer's address | Temporal tables |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SSIS packages | AWS Glue jobs (Spark) or Lambda | Code-first (PySpark/C#) rather than a visual designer; Glue Studio offers a visual editor |
| Azure Data Factory pipelines | Step Functions + EventBridge (or MWAA) | Orchestration and compute are separate services |
| ADLS Gen2 | Amazon S3 | No real directories; "folders" are key prefixes. No hierarchical namespace semantics such as atomic folder rename |
| Synapse serverless SQL | Amazon Athena | Both charge per data processed; Athena bills per TB scanned with a 10 MB minimum per query |
| Synapse dedicated SQL pool | Amazon Redshift | Distribution and sort keys instead of distribution and columnstore choices |
| Microsoft Fabric lakehouse / Delta tables | S3 + Apache Iceberg tables (or S3 Tables) | Iceberg is the format AWS services support most broadly |
| Microsoft Purview | Glue Data Catalog + Lake Formation (+ Amazon DataZone / SageMaker Catalog for business catalogue) | Permissions are enforced by Lake Formation when engines integrate with it |
| SQL Server CDC → ADF | AWS DMS full load + CDC | DMS writes CDC rows into S3 or Kinesis |
| Event Hubs | Kinesis Data Streams / Amazon MSK | Shards (Kinesis) or Kafka partitions (MSK) |
Architecture
The module project, at a glance. Each box is covered by a later lesson.
Security-boundary view. Three trust levels matter even in a tiny pipeline:
Only the pipeline's own role can read raw. Analysts read curated and gold. You will build these boundaries with bucket policies, KMS keys and Lake Formation later in the module.
Step-by-step implementation
You will build a miniature version of the whole idea on your laptop: a C# end-of-day job that reads the raw extract, validates, de-duplicates, writes a curated file plus a quarantine file, and writes a daily summary. Then you will reproduce the summary with SQL to feel the ETL/ELT difference.
Look at the raw data first
Data engineering starts by profiling the input. Open datasets/samples/csv/transactions.csv and transactions_messy.csv. From Git Bash in the repository root:
head -3 datasets/samples/csv/transactions.csv
wc -l datasets/samples/csv/transactions.csv datasets/samples/csv/transactions_messy.csv
head -5 datasets/samples/csv/transactions_messy_defects.csvThe clean file has 1,012 data rows plus a header. The messy copy has 1,036 rows because duplicates and late-arriving rows were injected. The _defects.csv file is the answer key: every defect, its row number and column. Real sources never give you an answer key; here it lets you grade your pipeline.
Decide the contract
Write down, before coding, what "valid" means. For Harbourline transactions (CONVENTIONS.md §2):
transaction_idis a UUID;account_idmatchesACC-plus 10 digits.booking_dateandvalue_dateare ISOyyyy-MM-dd.amountis a decimal with at most 2 decimal places; credits positive, debits negative.currencyisGBPorEUR.- A row whose
transaction_id(oridempotency_key) was already seen is a duplicate.
This list is your data contract. In the full lab it lives in labs/m07-transaction-pipeline/glue/src/harbourline_etl/transactions.py (the ValidationRules class), so the same rules run in tests and in Glue.
Write the job as a .NET 10 file-based app
.NET 10 can run a single .cs file with dotnet run eod.cs, no project file needed. Save the code from Complete code as eod.cs in a scratch folder.
Run it twice and compare
dotnet run eod.cs -- datasets/samples/csv/transactions_messy.csv out/
dotnet run eod.cs -- datasets/samples/csv/transactions_messy.csv out/The second run must produce byte-identical output. If it appended instead of replacing, your job is not idempotent and would double-count on a retry.
Reproduce the summary in SQL (ELT)
Load the curated CSV into an engine and aggregate with SQL. Locally, DuckDB is a free single-binary engine that behaves much like Athena for this purpose:
SELECT booking_date, currency,
COUNT(*) AS txn_count,
SUM(amount) AS net_amount
FROM read_csv('out/curated/transactions.csv', header = true)
WHERE status = 'booked'
GROUP BY booking_date, currency
ORDER BY booking_date, currency;Compare with out/gold/daily_summary.csv. Same numbers, different place where the transform ran. That is the ETL/ELT distinction.
AWS Console instructions
This lesson runs locally and creates nothing in AWS. To orient yourself before the next lessons, you can browse (read-only) in the sandbox account with the learn-sandbox SSO profile:
- Console → S3 — where the lake's buckets will live.
- Console → AWS Glue → Data Catalog → Databases — where table definitions will live.
- Console → Athena → Query editor — where you will query the lake. Do not run queries yet; the workgroup with a scan limit comes in the Athena lesson.
- Console → AWS Glue → ETL jobs — Glue Studio, the visual job editor.
AWS CLI
Read-only commands to confirm your CLI works and see what (if anything) exists:
# Who am I? (SSO profile from Module 1)
aws sts get-caller-identity --profile learn-sandbox
# Any Glue databases yet? (expect only "default" or nothing)
aws glue get-databases --query "DatabaseList[].Name" --profile learn-sandbox --region us-east-1
# Any Athena workgroups? ("primary" always exists)
aws athena list-work-groups --query "WorkGroups[].Name" --profile learn-sandbox --region us-east-1None of these calls cost anything.
Infrastructure as Code
Not needed in this lesson: nothing is deployed. The lake buckets are defined in CDK (C#) and Terraform in the next lesson, S3 data lake design.
Complete code
A complete, runnable end-of-day job. It deliberately uses only the base class library so you can see every step.
// Run: dotnet run eod.cs -- <input.csv> <outDir>
// A miniature batch ETL job over Harbourline's fictional transactions.
using System.Globalization;
using System.Text;
using System.Text.RegularExpressions;
if (args.Length != 2)
{
Console.Error.WriteLine("usage: dotnet run eod.cs -- <input.csv> <outDir>");
return 2;
}
var input = args[0];
var outDir = args[1];
var runId = $"run-{DateTime.UtcNow:yyyyMMddTHHmmssZ}";
var accountId = new Regex(@"^ACC-\d{10}$", RegexOptions.Compiled);
var isoDate = new Regex(@"^\d{4}-\d{2}-\d{2}$", RegexOptions.Compiled);
var amountPattern = new Regex(@"^-?\d{1,16}(\.\d{1,2})?$", RegexOptions.Compiled);
var currencies = new HashSet<string> { "GBP", "EUR" };
var lines = File.ReadAllLines(input, Encoding.UTF8);
var header = Csv.Split(lines[0]);
int Col(string name) => Array.IndexOf(header, name);
int iId = Col("transaction_id"), iAcc = Col("account_id"), iBook = Col("booking_date"),
iAmt = Col("amount"), iCur = Col("currency"), iStatus = Col("status"), iKey = Col("idempotency_key");
var seenIds = new HashSet<string>();
var seenKeys = new HashSet<string>();
var curated = new List<string> { lines[0] };
var quarantine = new List<string> { "row_number,reason_code,raw_line" };
var totals = new SortedDictionary<(string Date, string Ccy), (int Count, decimal Net)>();
for (var n = 1; n < lines.Length; n++)
{
var line = lines[n];
if (string.IsNullOrWhiteSpace(line)) continue;
var f = Csv.Split(line);
string? reason = null;
if (f.Length != header.Length) reason = "SCHEMA_COLUMN_COUNT";
else if (!accountId.IsMatch(f[iAcc])) reason = "INVALID_ID";
else if (!isoDate.IsMatch(f[iBook]) ||
!DateOnly.TryParseExact(f[iBook], "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out _))
reason = "INVALID_DATE";
else if (!amountPattern.IsMatch(f[iAmt])) reason = "INVALID_AMOUNT";
else if (!currencies.Contains(f[iCur])) reason = "INVALID_CURRENCY";
else if (!seenIds.Add(f[iId]) || !seenKeys.Add(f[iKey])) reason = "DUPLICATE";
if (reason is not null)
{
quarantine.Add($"{n},{reason},{Csv.Quote(line)}");
continue;
}
curated.Add(line);
if (f[iStatus] == "booked")
{
var amount = decimal.Parse(f[iAmt], CultureInfo.InvariantCulture);
var key = (f[iBook], f[iCur]);
totals.TryGetValue(key, out var t);
totals[key] = (t.Count + 1, t.Net + amount);
}
}
// Idempotent output: every run REPLACES its outputs instead of appending.
Directory.CreateDirectory(Path.Combine(outDir, "curated"));
Directory.CreateDirectory(Path.Combine(outDir, "quarantine"));
Directory.CreateDirectory(Path.Combine(outDir, "gold"));
File.WriteAllLines(Path.Combine(outDir, "curated", "transactions.csv"), curated);
File.WriteAllLines(Path.Combine(outDir, "quarantine", "transactions_rejected.csv"), quarantine);
var gold = new List<string> { "booking_date,currency,txn_count,net_amount" };
gold.AddRange(totals.Select(kv =>
$"{kv.Key.Date},{kv.Key.Ccy},{kv.Value.Count},{kv.Value.Net.ToString("0.00", CultureInfo.InvariantCulture)}"));
File.WriteAllLines(Path.Combine(outDir, "gold", "daily_summary.csv"), gold);
// The audit record: what came in, what went where. Control totals let finance reconcile.
var inputRows = lines.Length - 1;
Console.WriteLine($"{runId}: input={inputRows} curated={curated.Count - 1} quarantined={quarantine.Count - 1}");
if (curated.Count - 1 + quarantine.Count - 1 != inputRows)
{
Console.Error.WriteLine("Row count mismatch: some rows were lost. Failing the run.");
return 1;
}
return 0;
static class Csv
{
// Minimal RFC 4180 splitter: handles quoted fields and doubled quotes.
public static string[] Split(string line)
{
var fields = new List<string>();
var sb = new StringBuilder();
var inQuotes = false;
for (var i = 0; i < line.Length; i++)
{
var c = line[i];
if (inQuotes)
{
if (c == '"' && i + 1 < line.Length && line[i + 1] == '"') { sb.Append('"'); i++; }
else if (c == '"') inQuotes = false;
else sb.Append(c);
}
else if (c == '"') inQuotes = true;
else if (c == ',') { fields.Add(sb.ToString()); sb.Clear(); }
else sb.Append(c);
}
fields.Add(sb.ToString());
return fields.ToArray();
}
public static string Quote(string value) => "\"" + value.Replace("\"", "\"\"") + "\"";
}No .NET SDK on the machine? Run it in the official SDK container from the repository root (Git Bash):
MSYS_NO_PATHCONV=1 docker run --rm -v "$PWD:/w" -w /w mcr.microsoft.com/dotnet/sdk:10.0 \
dotnet run eod.cs -- datasets/samples/csv/transactions_messy.csv out/(Put eod.cs in the repository root for this command, and delete it and out/ afterwards; out/ is scratch output.)
Code explained line by line
if (args.Length != 2)— a batch job is a command-line program with explicit inputs and outputs. No hidden state, no "current directory" assumptions.var runId = ...— every run gets an identifier. In AWS this becomes the Step Functions execution name or GlueJobRunId, and it is written into every output row and audit record (lineage).- The
Regexfields — the data contract as code. They mirror the Python rules in the lab so both implementations agree. File.ReadAllLines(input, Encoding.UTF8)— fine for a 1,000-row sample. For 1M rows you would stream line by line, and in AWS you would hand the job to Spark. Knowing when in-memory processing stops being acceptable is part of the job (see Lambda transforms).headerandCol(...)— look columns up by name, never by position. Producers add columns; position-based code silently reads the wrong field.seenIdsandseenKeys— de-duplication on both the business key and the idempotency key. The messy data contains "retries" with a new id but the same idempotency key, exactly as a real client retry would.- The
reasonchain — first failing rule wins and becomes a reason code. Quarantined rows keep their original text so nothing is lost and an operator can replay them after a fix. if (f[iStatus] == "booked")— the ledger rule fromdatasets/README.md: only booked rows count towards balances. Pending, reversed and rejected rows are kept in curated but excluded from totals.decimal.Parse(..., CultureInfo.InvariantCulture)— money isdecimal, and parsing is culture-invariant. On a machine set tode-DE,403.41would otherwise parse as 40341.File.WriteAllLines(...)— overwrite, not append: the simplest form of idempotency. In S3 the equivalent is writing a partition with "overwrite" semantics, or writing to a run-specific prefix and switching a pointer.- The final row-count check — a control total. Input rows must equal curated plus quarantined. If not, fail loudly. Banks reconcile counts and sums at every hop.
static class Csv— a minimal RFC 4180 parser. Counterparty names and references can contain commas inside quotes, soline.Split(',')would be a bug.
Security considerations
⚠ Learning shortcut — not for production
This local job writes curated rows that still contain counterparty names and full IBANs, and keeps raw lines in the quarantine file. That is acceptable for fictional sample data on your laptop. In production the curated zone holds tokenised IBANs (keyed HMAC, key in Secrets Manager or KMS-derived), quarantine is as restricted as raw, and both are encrypted with a customer managed KMS key. See harbourline_etl/masking.py in the lab and the secure pipeline lesson listed in the module overview.
Other habits to start now:
- Never land real data in a learning environment. The generator exists so you never need to.
- Classify every zone. Raw is the most sensitive because it is unchanged. Gold is usually the least sensitive because it is aggregated, but small aggregates can still identify people (a "count of 1" row).
- Keep the raw copy unchanged. It is your evidence. Validation and masking produce new data; they never edit raw in place.
- Log counts, not contents. Audit records hold row counts, sums and file names, never row data.
Banking use case
Harbourline's finance operations team owns the daily ledger reconciliation. The control is simple to state: for each account and day, the sum of booked transactions in the data platform equals the movement in the core banking ledger. The pipeline therefore must:
- Ingest the end-of-day extract unchanged (evidence for audit).
- Reject and explain every bad row instead of silently fixing or dropping it.
- Be idempotent, so the operations team can re-run a day after fixing a source problem without double-counting.
- Produce control totals (row counts and sums by currency) at each hop, written to an audit table.
- Record late-arriving rows separately, so a reconciliation break caused by a late partner file can be explained in one query.
You will build exactly this in labs/m07-transaction-pipeline over the module.
Common mistakes
- Treating the data lake like a database → expecting updates in place and transactions → design for immutable files plus overwrite-by-partition, or use Iceberg tables when you truly need row-level updates (see Lake vs warehouse vs lakehouse).
- Appending on retry → doubled totals after an automatic retry → make every write idempotent (overwrite a partition, or de-duplicate on a key).
- Fixing data silently → "the numbers changed and nobody knows why" → quarantine with a reason code; fix at source or with a documented rule.
- Using
doublefor money → rounding differences in reconciliations →decimalin C#,DECIMAL(18,2)in Parquet and SQL. - Streaming by default → idle cost and late-data complexity with no business benefit → batch unless the requirement is seconds.
- No control totals → lost rows go unnoticed → count and sum at each hop and fail the run on mismatch.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
dotnet run eod.cs says the command is not recognised | .NET SDK older than 10 | Install .NET 10 SDK, or use the Docker command shown above |
Every row is quarantined as SCHEMA_COLUMN_COUNT | Splitting on commas without honouring quotes | Use the RFC 4180 splitter in the sample |
| Totals differ from DuckDB by a few pence | Parsing amounts as double somewhere | Use decimal everywhere; cast to DECIMAL(18,2) in SQL |
| Second run gives different output | Appending instead of overwriting, or using DateTime.Now inside the data | Overwrite outputs; keep run time only in the audit record |
| Amounts look 100 times too large on one machine | Culture-specific parsing | CultureInfo.InvariantCulture |
DuckDB reads booking_date as text | Auto-detection saw bad values in the messy file | Read the curated output, not the messy input; or pass explicit column types |
Cost and cleanup
Cost: nothing. Everything runs locally. The read-only CLI calls are free.
Cleanup:
# from the folder where you ran the job
rm -rf out/ eod.csExercises
- 🟢 Guided: Run the job on
transactions_messy.csv. Count the quarantined rows by reason code and compare them with the defect types intransactions_messy_defects.csv. Which defect types does the job not catch yet? - 🟡 Challenge: Add the rule "a
transfer_inamount must be positive" and an IBAN mod-97 check oncounterparty_iban(when present). Re-grade against the answer key. - 🔴 Live problem: Finance reports that the daily summary for 2026-11-14 changed between Monday's and Tuesday's runs, although "nothing was re-run". The only change was a late partner file that arrived on Monday night. Explain what happened, whether the pipeline is wrong, and what you would change so that this question can be answered from data in under five minutes next time.
Assignment
Write a one-page pipeline design note (Markdown in your portfolio repository) for Harbourline's end-of-day transaction feed: sources, batch or stream with justification, ETL and ELT steps, zones and who can read each, the data contract (field rules), idempotency strategy, control totals, and how late-arriving data is handled. Keep it; you will refine it in each lesson of this module.
Solutions
The sample job catches bad_date in booking_date (but not in value_date or created_at), wrong_currency, both kinds of duplicate, and some malformed IDs. It does not catch invalid_iban (no checksum test), negative_amount (no sign rule per type), mixed_encoding (it reads the whole file as UTF-8, so invalid bytes become replacement characters instead of being flagged) or late_arriving (no rule comparing created_at with value_date). Why it matters: an answer key turns "looks fine" into a measurable detection rate. The lab's Python module covers all seven defect types and its tests assert against the same answer key.
Add before the duplicate check:
else if (f[Col("type")] == "transfer_in" &&
decimal.Parse(f[iAmt], CultureInfo.InvariantCulture) <= 0m) reason = "AMOUNT_SIGN";
else if (f[Col("counterparty_iban")] is { Length: > 0 } iban && !Iban.IsValid(iban)) reason = "INVALID_IBAN";with a mod-97 helper:
static class Iban
{
public static bool IsValid(string value)
{
var s = value.Replace(" ", "").ToUpperInvariant();
if (s.Length is < 15 or > 34) return false;
var rearranged = s[4..] + s[..4];
var remainder = 0;
foreach (var ch in rearranged)
{
var digits = char.IsLetter(ch) ? (ch - 'A' + 10).ToString() : ch.ToString();
foreach (var d in digits) remainder = (remainder * 10 + (d - '0')) % 97;
}
return remainder == 1;
}
}The check must come after the amount format check, otherwise decimal.Parse throws on bad input. Why: rule order matters; validate shape before meaning.
The pipeline recomputed 2026-11-14 because the late partner file contained rows booked on that date. The output is "correct" for the data available at each run, but the report has no notion of as-of time. Changes: (1) stamp every curated row with ingest_run_id and source_file (the lab's CURATED_COLUMNS already include these); (2) flag is_late_arriving rows; (3) keep each daily summary version with its run ID and generation timestamp instead of overwriting history (or use an Iceberg table and time travel); (4) document the reporting cut-off rule with finance ("reports are final at T+1 07:00; later rows appear in an adjustments report"). Then the question becomes one query: rows for 2026-11-14 grouped by ingest_run_id.
Knowledge check
Answer: Stream (Kinesis Data Streams or MSK with a consumer such as Lambda). Batch runs every few minutes at best. The same transactions should still land in S3 for batch reporting and model training.
Answer: ETL transforms before loading into the analytical store; ELT loads raw data first and transforms inside the engine with SQL. ETL suits mandatory masking, because unmasked data never reaches a queryable zone.
Answer: Idempotency: re-running with the same input produces the same output, typically by overwriting the target partition or de-duplicating on a key.
Answer: Files are stored without an enforced schema; the query engine (Athena, Spark) applies a table definition from the catalogue when reading. Bad rows surface at query time, not at write time, which is why validation in the pipeline matters.
Answer: Row counts in and out (curated plus quarantined equals input) and sums of amounts by currency. Hash totals of keys are a third common choice.
Interview questions
- "How is data engineering different from backend development?" — Same engineering discipline, different unit of work: sets of rows instead of single requests. The hard parts are idempotency across retries, late and out-of-order data, schema evolution, cost per byte scanned, and proving lineage for audit.
- "When would you choose streaming over batch?" — When a business decision needs seconds of latency (fraud, notifications). Otherwise batch, because it is cheaper, simpler to make exactly correct, and easier to reconcile. Often both, from the same events.
- "How do you make a pipeline idempotent?" — Deterministic outputs keyed by input (partition overwrite or run-specific prefixes with a pointer switch), de-duplication on business keys and idempotency keys, and an audit table recording which inputs each run consumed.
- "What do you do with bad rows?" — Never drop silently. Quarantine with a reason code, keep raw unchanged, report counts, fix at source when possible, and replay quarantined rows through the same code after a fix.
Certification practice
A bank receives card authorisations continuously. The fraud team must score each authorisation within 1 second. Finance needs a daily total of authorised amounts by merchant category. Which design meets both needs with the LEAST operational overhead?
A. Write authorisations to S3 every hour and run an Athena query for both use cases. B. Send authorisations to Amazon Kinesis Data Streams; a Lambda consumer scores each event; Amazon Data Firehose delivers the same stream to S3 for a daily Athena query. C. Insert authorisations into an Amazon RDS database and run the fraud check with a trigger and the finance report with a nightly SQL job. D. Send authorisations to Amazon SQS and poll the queue once a day for both use cases.
Answer: B. One stream serves the low-latency consumer and, via Firehose, lands data for cheap batch analysis. A cannot meet 1-second scoring. C couples analytics to an OLTP database and puts fraud logic in triggers. D: daily polling misses the latency requirement, and SQS is not designed for multiple independent consumers of the same message.
A daily AWS Glue job appends Parquet files to s3://lake/curated/transactions/day=.../. After a transient failure, the orchestrator retried the job and the day's totals doubled. What is the MOST appropriate fix?
A. Disable retries on the job. B. Configure the job to overwrite the target partition (dynamic partition overwrite) so reruns replace that day's data. C. Run a weekly job that deletes duplicate files. D. Increase the number of DPUs so the job does not fail.
Answer: B. Overwriting the affected partition makes reruns idempotent. A trades one failure mode for another (missing data). C leaves wrong numbers for up to a week. D does not remove transient failures and does nothing about duplicates.
A team built a Kinesis Data Streams pipeline with 10 provisioned shards to load a nightly file of 2 GB into S3. The stream is idle 23 hours a day. What change reduces cost the MOST while meeting the requirement?
A. Switch the stream to on-demand capacity mode. B. Upload the nightly file directly to S3 and process it with a scheduled batch job. C. Reduce the stream to 5 shards. D. Add Amazon Data Firehose in front of the stream.
Answer: B. The workload is a batch file; a stream adds cost with no benefit. A and C reduce but do not remove streaming cost. D adds another service.
Completion checklist
- I can explain batch vs stream and ETL vs ELT to a colleague with a banking example
- I ran the C# job twice and confirmed identical output
- I reproduced the daily summary with SQL
- I compared my quarantine reasons with the answer key
- I started my pipeline design note
Key takeaways
- Data engineering moves and reshapes data in bulk; the hard parts are correctness across retries, late data, schema change and cost per byte.
- Default to batch; use streaming when the business needs seconds. Many banks run both from the same events.
- ETL for validation and masking before anyone can query; ELT (SQL) for aggregates once data is clean.
- Keep raw unchanged, quarantine with reason codes, write idempotently, and record control totals for every run.
- AWS gives you building blocks (S3, Glue, Athena, Lambda, Step Functions, Lake Formation); the rest of this module teaches when to use each.
Further reading
- What is AWS Glue?
- What is Amazon Athena?
- AWS Certified Data Engineer – Associate (DEA-C01) exam page
- Module 7 overview and the DEA-C01 study page
- Next in this module: S3 data lake design, then Lab 7.1: generate the transaction datasets. The finished pipeline is the module project.
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.
Module 7: Data Engineering & ETL Pipelines
Design and build secure batch and streaming data pipelines on AWS, from an S3 data lake through Glue, Athena and Redshift to reporting. The module ends with a full banking transaction pipeline that validates, masks, reconciles and audits fictional transaction data, plus a near-real-time fraud-signal variant.
S3 data lake design: zones, partitions, naming, lifecycle and encryption
Design Harbourline's S3 data lake with raw/bronze, curated/silver and consumption/gold zones, Hive-style partitions, predictable naming, lifecycle rules and KMS encryption, then deploy it with CDK (C#) or Terraform and land a file from C#.