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

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.

beginner⏱ 1 h 15 min🟢 FreeDEA-C01SAA-C03

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 seed 20261117; see datasets/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:

  1. Ingest: get data out of the source (files, APIs, database change streams, event streams) and land it somewhere durable, unchanged.
  2. Store: keep it in a layout that is cheap, secure and fast to read.
  3. Transform: clean, validate, de-duplicate, mask personal data, join and aggregate.
  4. Serve: make it queryable (SQL, dashboards, extracts for regulators).
  5. 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.

JobAWS service(s)One-line role
Durable storage for files of any sizeAmazon S3The data lake: cheap, durable object storage organised into zones
A shared table catalogueAWS Glue Data CatalogA Hive-compatible metastore: databases, tables, columns, partitions
Discover schemasGlue crawlersScan S3 and propose table definitions
Large transformsAWS Glue (Spark) jobsManaged Apache Spark; you write PySpark or Scala
Small transformsAWS LambdaEvent-driven functions, C# or Python, up to 15 minutes
SQL over files in S3Amazon AthenaServerless SQL (Trino-based), pay per data scanned
Data warehouseAmazon Redshift / Redshift ServerlessColumnar MPP warehouse for heavy, repeated BI
Streaming ingestionKinesis Data Streams, Amazon Data Firehose, Amazon MSKOrdered event streams and managed delivery to S3
OrchestrationStep Functions, EventBridge, EventBridge Scheduler, MWAARun steps in order, on a schedule or on events
Database change captureAWS DMSFull load plus CDC from SQL Server into S3 or Kinesis
Fine-grained accessAWS Lake FormationTable, column, row and tag permissions on the catalogue
Data qualityGlue Data QualityDeclarative rules (DQDL) evaluated inside jobs or on tables
DashboardsAmazon 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

BatchStream
Unit of workA bounded set: a file, a day, a partitionAn unbounded sequence of events
LatencyMinutes to hoursMilliseconds to seconds
Typical AWS shapeS3 + Glue/Lambda + Athena, orchestrated by Step FunctionsKinesis Data Streams or MSK + Lambda/Flink, often also landing in S3 via Firehose
CorrectnessEasy to reason about: the input is complete when you startHarder: events arrive late and out of order; you need windows and watermarks
Cost profilePay while the job runsPay while the stream exists (shards, brokers), even when idle
Banking examplesEnd-of-day reconciliation, regulatory returns, statement production, monthly interestCard 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) or INSERT INTO, or load into Redshift and transform with SQL (often with dbt).
QuestionLeans ETLLeans ELT
Must personal data be masked before anyone can query it?Yes: mask in the job, never land unmasked data in a queryable zonePossible 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-runNatural: 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

TermPlain meaning.NET mental hook
Data lakeFiles in object storage, organised by convention, queried in placeA very large, well-organised file share
Zone (raw/bronze, curated/silver, consumption/gold)Stages of refinement, each with its own access rulesStaging tables → clean tables → reporting views
PartitionA folder-like key prefix such as year=2026/month=11/day=16/ so engines can skip dataA partitioned table in SQL Server, but enforced by naming
Schema-on-readThe file has no enforced schema; the reader applies one when queryingDeserialising JSON into a DTO at read time
Schema-on-writeData must match a schema to be storedINSERT into a typed table
Columnar format (Parquet, ORC)Stores each column together, compressed, with statisticsA clustered columnstore index
Idempotent loadRunning the same load twice gives the same resultAn 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 bookmarkGlue's built-in watermark for files already processedSame idea, managed for you
Late-arriving dataRecords that arrive after their business period was processedA back-dated posting
CDC (change data capture)A stream of inserts, updates and deletes from a database logSQL Server CDC tables
LineageWhich inputs and code produced an outputTracing a report figure back to its source rows
Data contractThe agreed schema and quality rules between producer and consumerA versioned API contract (OpenAPI) for data
QuarantineA place for rejected rows, with a reason codeA dead-letter queue for rows
SCD (slowly changing dimension)Keeping history of attributes that change, such as a customer's addressTemporal tables

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
SSIS packagesAWS Glue jobs (Spark) or LambdaCode-first (PySpark/C#) rather than a visual designer; Glue Studio offers a visual editor
Azure Data Factory pipelinesStep Functions + EventBridge (or MWAA)Orchestration and compute are separate services
ADLS Gen2Amazon S3No real directories; "folders" are key prefixes. No hierarchical namespace semantics such as atomic folder rename
Synapse serverless SQLAmazon AthenaBoth charge per data processed; Athena bills per TB scanned with a 10 MB minimum per query
Synapse dedicated SQL poolAmazon RedshiftDistribution and sort keys instead of distribution and columnstore choices
Microsoft Fabric lakehouse / Delta tablesS3 + Apache Iceberg tables (or S3 Tables)Iceberg is the format AWS services support most broadly
Microsoft PurviewGlue 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 → ADFAWS DMS full load + CDCDMS writes CDC rows into S3 or Kinesis
Event HubsKinesis Data Streams / Amazon MSKShards (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.csv

The 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_id is a UUID; account_id matches ACC- plus 10 digits.
  • booking_date and value_date are ISO yyyy-MM-dd.
  • amount is a decimal with at most 2 decimal places; credits positive, debits negative.
  • currency is GBP or EUR.
  • A row whose transaction_id (or idempotency_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-1

None 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.

eod.cs
// 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

  1. if (args.Length != 2) — a batch job is a command-line program with explicit inputs and outputs. No hidden state, no "current directory" assumptions.
  2. var runId = ... — every run gets an identifier. In AWS this becomes the Step Functions execution name or Glue JobRunId, and it is written into every output row and audit record (lineage).
  3. The Regex fields — the data contract as code. They mirror the Python rules in the lab so both implementations agree.
  4. 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).
  5. header and Col(...) — look columns up by name, never by position. Producers add columns; position-based code silently reads the wrong field.
  6. seenIds and seenKeys — 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.
  7. The reason chain — 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.
  8. if (f[iStatus] == "booked") — the ledger rule from datasets/README.md: only booked rows count towards balances. Pending, reversed and rejected rows are kept in curated but excluded from totals.
  9. decimal.Parse(..., CultureInfo.InvariantCulture) — money is decimal, and parsing is culture-invariant. On a machine set to de-DE, 403.41 would otherwise parse as 40341.
  10. 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.
  11. 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.
  12. static class Csv — a minimal RFC 4180 parser. Counterparty names and references can contain commas inside quotes, so line.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 double for money → rounding differences in reconciliations → decimal in 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

SymptomLikely causeFix
dotnet run eod.cs says the command is not recognised.NET SDK older than 10Install .NET 10 SDK, or use the Docker command shown above
Every row is quarantined as SCHEMA_COLUMN_COUNTSplitting on commas without honouring quotesUse the RFC 4180 splitter in the sample
Totals differ from DuckDB by a few penceParsing amounts as double somewhereUse decimal everywhere; cast to DECIMAL(18,2) in SQL
Second run gives different outputAppending instead of overwriting, or using DateTime.Now inside the dataOverwrite outputs; keep run time only in the audit record
Amounts look 100 times too large on one machineCulture-specific parsingCultureInfo.InvariantCulture
DuckDB reads booking_date as textAuto-detection saw bad values in the messy fileRead 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.cs

Exercises

  • 🟢 Guided: Run the job on transactions_messy.csv. Count the quarantined rows by reason code and compare them with the defect types in transactions_messy_defects.csv. Which defect types does the job not catch yet?
  • 🟡 Challenge: Add the rule "a transfer_in amount must be positive" and an IBAN mod-97 check on counterparty_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

Knowledge check

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

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

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