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.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 · AWS Glue (Spark) · Python 3 for PySpark · Region us-east-1. Verified against official sources on 2026-11-18.
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.
| Estimated time | About 28 hours (lessons 15 h · labs 7 h · project 6 h) |
| Priority for you | ⭐ Critical — priority 4 (data pipelines) |
| Level | Beginner → advanced (Python introduced from zero) |
| Certifications | DEA-C01 (primary) · SAA-C03 |
| Cost profile | 🟢 S3 + Athena on small data, local Spark · 🟡 Glue jobs, Kinesis deploy-and-destroy · 🔴 MWAA, MSK, Redshift provisioned, Transfer Family |
Why this matters in banking
Banks run on files and feeds: end-of-day transaction extracts, card-scheme settlement files, SWIFT statements, partner SFTP drops and regulator returns. When a pipeline silently drops rows, reconciliation fails, the finance team works the weekend and an auditor asks why the numbers changed. When a pipeline copies raw personal data into a place analysts can browse, you have a data-protection incident. This module teaches pipelines that are repeatable, validated, masked and auditable, so every number in a report can be traced back to the file it came from.
From .NET/Azure to AWS
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SSIS packages / Azure Data Factory | AWS Glue jobs + Step Functions | Glue runs Apache Spark (PySpark or Scala); orchestration is a separate service |
| ADF / Synapse pipelines triggers | EventBridge rules, EventBridge Scheduler, S3 event notifications | Event-driven triggers are first-class, not just schedules |
| Azure Data Lake Storage Gen2 | Amazon S3 data lake with zones | Flat object store; "folders" are key prefixes, partitions are naming conventions |
| Synapse serverless SQL | Amazon Athena | Pay per data scanned — Parquet and partitioning cut cost dramatically |
| Synapse dedicated pool | Amazon Redshift / Redshift Serverless | Columnar warehouse with distribution and sort keys |
| Microsoft Purview catalogue + access | Glue Data Catalog + AWS Lake Formation | Lake Formation adds row, column and tag-based permissions on the catalogue |
| Event Hubs | Kinesis Data Streams / Amazon MSK | Shards (Kinesis) or Kafka partitions (MSK) |
| Event Hubs Capture | Amazon Data Firehose | Managed delivery into S3, Redshift and others, with optional conversion to Parquet |
| Power BI | Amazon Quick Sight (part of AWS Quick) | Per-user pricing; SPICE in-memory engine |
| SQL Server CDC / replication | AWS DMS with change data capture | Streams SQL Server changes into S3 or Kinesis |
Learning objectives
By the end of this module you can:
- Design an S3 data lake with raw/bronze, curated/silver and consumption/gold zones, partitioning and naming conventions.
- Read and write beginner-level PySpark in Glue jobs, use job bookmarks, and know when a C# Lambda transform is enough.
- Catalogue data with crawlers and the Glue Data Catalog, and query it with Athena using partitions and Parquet.
- Apply Lake Formation row, column and tag-based permissions so analysts never see raw personal data.
- Choose between batch and streaming, ETL and ELT, data lake, warehouse and lakehouse (Apache Iceberg).
- Orchestrate pipelines with Step Functions and EventBridge, and explain where MWAA (Airflow) fits.
- Build data-quality rules, quarantine bad rows, mask or tokenise PII inside the pipeline, and keep execution and audit records.
Prerequisites
- Module 1: AWS fundamentals — S3, IAM roles and Regions
- Module 5: Security — KMS, bucket policies and data classification
- Module 6: Databases — SQL Server sources, DMS and Redshift basics
- Helpful: Module 4: APIs for the SFTP and API ingestion paths, and Module 8: Messaging for Kinesis concepts
- No Python experience needed — a short PySpark primer is included
How the pieces fit
Lessons
- Data engineering for .NET developers — batch vs stream, ETL vs ELT, and the vocabulary you will meet on DEA-C01.
- S3 data lake design — raw/bronze → curated/silver → consumption/gold zones, partitioning, naming, lifecycle and encryption.
- File formats — CSV vs JSON vs Parquet, compression, schema evolution and why Parquet makes Athena cheaper.
- Data lake vs warehouse vs lakehouse — Apache Iceberg tables, time travel and when Redshift is still the answer.
- AWS Glue Data Catalog and crawlers — tables, partitions, schema drift and when to define schemas yourself.
- PySpark in Glue for beginners — DataFrames explained against LINQ, Glue Studio, job bookmarks and the C# developer's mental model.
- Lightweight transforms with Lambda — C# and Python Lambda functions for small files, and their limits.
- Amazon Athena — partition pruning, CTAS, workgroups with cost limits and query result encryption.
- Amazon Redshift and Redshift Serverless — loading from S3, distribution and sort keys, Spectrum and the cost model.
- Streaming ingestion — Kinesis Data Streams, Amazon Data Firehose and an Amazon MSK overview for Kafka shops.
- Orchestration — Step Functions for pipelines, EventBridge and EventBridge Scheduler triggers, and an Amazon MWAA (Airflow) overview.
- Change data capture from SQL Server — DMS full-load plus CDC into S3, late-arriving and out-of-order records.
- Validation and data quality — Glue Data Quality rules, Deequ concepts, quarantine zones and reject reports.
- Governance, lineage and cataloguing — Lake Formation row/column and tag-based access control, lineage and data ownership.
- Secure pipeline design — KMS encryption, least-privilege job roles, VPC endpoints, and PII masking or tokenisation inside the pipeline.
- Reporting with Amazon Quick Sight — datasets, SPICE versus direct query, row-level security and a reconciliation dashboard, with a free C# report gate as the alternative.
When you finish, keep the Module 7 cheat sheet handy for revision.
Labs and module project
The code for every lab and for the project lives in one folder, labs/m07-transaction-pipeline/. Its README.md explains the layout, the test commands, and the deploy and teardown steps. The sample data is in datasets/ (datasets/README.md explains the generator, the schema and the deliberately messy files). Everything is fictional.
Each lab has a walkthrough page, and the code and datasets are ready to use.
- Lab 7.1 (🟢) — Generate 1k / 100k / 1M seeded transactions with the C# generator, including intentionally messy rows (duplicates, invalid IBANs, negative amounts, bad dates) and an answer key. Code:
datasets/generator. - Lab 7.2 (🟢) — Convert CSV to partitioned Parquet locally with PySpark in Docker and compare file sizes (including the small-files surprise). Code:
labs/m07-transaction-pipeline/local/. - Lab 7.3 (🟡) — Upload a small dataset to S3, query it with Athena and partition projection; compare bytes scanned for CSV vs Parquet. Code:
labs/m07-transaction-pipeline/sql/athena/. - Lab 7.4 (🟡) — A Glue Spark job with job bookmarks and a Data Quality ruleset that sends bad rows to quarantine. Code:
labs/m07-transaction-pipeline/glue/. - Lab 7.5 (🟡) — Lake Formation: an analyst role that sees only GBP rows and no free-text columns, with the bypass checks. Code:
labs/m07-transaction-pipeline/infra/cli/. - Lab 7.6 (🟡) — Streaming: Kinesis Data Streams → .NET Lambda fraud rules → SNS alert; deploy, test and destroy within an hour. Code:
labs/m07-transaction-pipeline/infra/cdk/(stackHlM07-Streaming).
Module project — banking transaction processing pipeline (code in labs/m07-transaction-pipeline/). Using fictional data only, build a pipeline that:
- Receives transaction records through an API and an SFTP drop.
- Stores the raw data unchanged in S3 (encrypted, versioned).
- Triggers a processing workflow from the arrival event.
- Validates and transforms the data, rejecting bad rows to a quarantine area with a reason code.
- Stores processed data as partitioned Parquet.
- Makes it queryable in Athena through the Glue Data Catalog.
- Produces reporting outputs: a daily summary, a reconciliation report and suspicious-activity signals.
- Keeps execution logs and audit records for every run.
A streaming variant sends the same transactions through Kinesis and a .NET Lambda that applies velocity and amount rules to emit near-real-time fraud signals.
The lab README states that the .NET suite has 27 tests and the Python suite 18, and that both run without an AWS account.
Cost profile
| Part | Label | Free alternative |
|---|---|---|
| Data generators, PySpark, Parquet conversion | 🟢 free | Local Python + Spark in Docker |
| S3 storage and Athena on lab-sized data | 🟢 free / 🟡 | Keep datasets small and partitioned; set an Athena workgroup scan limit |
| Glue crawlers and Spark jobs | 🟡 credits | Billed per DPU-hour with a minimum per run; run few, small jobs, or prototype locally in Spark first |
| Lake Formation permissions | 🟢 free | No charge for Lake Formation itself; you pay for the underlying services |
| Kinesis Data Streams, Data Firehose | 🟡 credits | Shard-hours bill while the stream exists — deploy, test and delete within an hour |
| AWS Transfer Family SFTP endpoint | 🔴 paid | Local SFTP container (for example atmoz/sftp ⚠ VERIFY image) plus an upload to S3; or deploy-and-destroy within an hour |
| Amazon MWAA | 🔴 paid | Design-only; run Apache Airflow locally in Docker |
| Amazon MSK, Redshift provisioned | 🔴 paid | Design-only exercise |
| Redshift Serverless | 🔴/🟡 | Check for a free trial ⚠ VERIFY; otherwise design-only plus Athena |
| Amazon Quick Sight | 🔴 paid | Check current free-trial terms ⚠ VERIFY; otherwise build the report as an Athena query or a local chart |
LocalStack's free Hobby plan does not include Glue or Athena, so local practice uses Spark in Docker and plain SQL instead.
Certification alignment
| Exam | Domains (weight) | What this module covers |
|---|---|---|
| DEA-C01 | Ingestion (34%) · Store (26%) · Ops (22%) · Security & Governance (18%) | Almost the whole exam: Glue, Athena, Kinesis, Firehose, Redshift, Lake Formation, orchestration and data quality |
| SAA-C03 | High-Performing (24%) · Cost-Optimized (20%) | Choosing analytics services, storage classes and cost-aware formats |
The domain labels are short forms; see each exam page for the official wording.
Service availability notes
Services that changed — verified 2026-11-17
Amazon Fraud Detector is closed to new customers (maintenance announced 2025-10-07; new sign-ups ended 2025-11-07). The fraud-signal labs use rules on Kinesis + Lambda instead, with SageMaker or Bedrock covered as options in later modules.
Amazon S3 Object Lambda is closed to new customers (2025-11-07). Do not design PII masking around it; mask in the Glue job or expose masked views through Lake Formation.
AWS Glue Ray jobs are in maintenance (announced 2026-03-31). Use Glue Spark jobs.
Amazon QuickSight is now Quick Sight, a feature inside AWS Quick (introduced as Amazon Quick Suite in October 2025; AWS's current documentation writes "AWS Quick Sight" ⚠ VERIFY the final brand wording). APIs, CLI commands, SDK packages and IAM actions still use the quicksight names. Amazon Q Business is closed to new customers and points to Amazon Quick.
Compliance review required
Masking, tokenisation and retention choices shown here are learning examples. Whether a masked dataset is still personal data under UK GDPR, and how long raw files may be kept, are decisions for your data-protection officer and compliance team.
Key AWS services
Amazon S3 · AWS Glue (Data Catalog, crawlers, Spark ETL, Glue Studio, Data Quality) · AWS Lake Formation · Amazon Athena · Amazon Redshift + Redshift Serverless · Amazon Kinesis Data Streams · Amazon Data Firehose · Amazon MSK · AWS Lambda · AWS Step Functions · Amazon EventBridge + EventBridge Scheduler · Amazon MWAA · AWS DMS · AWS Transfer Family · AWS KMS · Amazon Quick Sight · Amazon CloudWatch
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 6 cheat sheet
One-page revision summary for Databases & Data Architecture: services, Azure mapping, CLI commands, limits, decision rules, exam traps and cost traps.
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.