PySpark in AWS Glue for beginners: DataFrames for C# developers
Learn just enough Python and Apache Spark to read, validate, transform and write Harbourline's transactions in an AWS Glue job, using LINQ as your mental model, and run the same job free on your laptop before paying for Glue.
Assumes: AWS Glue 5.0 (Spark 3.5.4, Python 3.11) · Glue 6.0 noted (Spark 4.1.1, Python 3.13) · PySpark 3.5.5 locally · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Read and write the small subset of Python a Glue job needs, mapped to C# concepts you already know.
- Explain Spark's model: driver and executors, partitions, lazy transformations and actions, using LINQ and
IQueryableas the bridge. - Write a PySpark job that reads raw CSV as strings, validates with a reason code, de-duplicates with a window function, types and masks columns, and writes partitioned Parquet idempotently.
- Explain what Glue adds on top of Spark: GlueContext, DynamicFrame, job bookmarks, worker types, Glue versions, Glue Studio and Flex execution.
- Run the job for free in Docker, then deploy it to Glue with CDK (C#) or Terraform and run it once for a few cents.
Prerequisites
- Data engineering for .NET developers (reason codes, control totals, idempotency)
- File formats and Glue Data Catalog and crawlers
- Tools: Docker Desktop (Linux containers); AWS CLI v2 (
learn-sandbox) for the optional cloud run - No Python experience needed
Why this matters in banking
Harbourline's 1,000-row sample fits in a C# List<T>. The large dataset has more than a million transactions, and a real bank's end-of-day extract can have tens of millions. One machine reading everything into memory is slow and fragile; when it fails at row 9 million at 02:00, the morning reports are late.
Apache Spark splits the work across many machines, retries failed pieces, and streams data through instead of loading it all. AWS Glue runs Spark for you: no clusters to patch, billed per second. It is the default heavy-lifting engine for the DEA-C01 exam and for most AWS data platforms, and its main language is Python. A .NET developer who can read and write a Glue job is immediately useful on a data team.
The idea in plain language
Spark is a distributed query engine. You describe what you want (filter these rows, compute these columns, group by that) and Spark works out how to run it in parallel.
- The driver is the process running your script. It builds a plan.
- Executors are worker processes on other machines. They run tasks.
- Data is split into partitions (chunks of rows, nothing to do with S3 partitions). Each task processes one partition.
- Transformations (
filter,select,withColumn,groupBy,join) only add steps to a plan. Nothing runs yet. - Actions (
count,collect,write,show) make Spark optimise the plan and execute it.
You already know this pattern. A LINQ query over an IQueryable builds an expression tree; nothing hits the database until you call ToList() or Count(). Spark's DataFrame API is the same idea, with a cluster instead of SQL Server behind it.
Analogy: Writing a Spark job is like the branch operations manager writing a work instruction for the overnight processing team. The instruction ("take today's paper, discard duplicates, total by currency") is written once by the manager (driver). The work is split into bundles (partitions) and handed to clerks (executors), who each process their bundle. Nothing happens until someone says "go and give me the total" (action). If a clerk goes home sick, their bundle is handed to another clerk.
What problem the AWS service solves
AWS Glue for Spark
Glue gives you a serverless Spark environment:
- You upload a script (Python or Scala) and set a Glue version, worker type and number of workers.
- Glue starts the cluster (usually within seconds to a couple of minutes), runs the job, streams logs to CloudWatch, and shuts down.
- It integrates with the Data Catalog (read and write tables), job bookmarks (process only new data), Glue Data Quality (rules in DQDL), Lake Formation (fine-grained access), and connections to JDBC sources such as SQL Server.
Current Glue versions (checked 2026-11-18):
| Glue version | Spark | Python | Notes |
|---|---|---|---|
| 6.0 | 4.1.1 | 3.13 | Default for new jobs that do not specify a version; Iceberg 1.11, Spark Connect for interactive sessions; S3A connector only |
| 5.1 | 3.5.6 | 3.11 | Iceberg 1.10, Iceberg format v3 |
| 5.0 | 3.5.4 | 3.11 | requirements.txt support; Lake Formation Spark-native fine-grained access control; S3 Tables support |
| 4.0 | 3.3.0 | 3.10 | Older; plan upgrades |
| 0.9, 1.0, 2.0 | 2.x | — | End of life since 2026-04-01 |
This module uses Glue 5.0 because AWS publishes a matching local Docker image (public.ecr.aws/glue/aws-glue-libs:5), and the lab's local Spark image mirrors its versions. Spark 4 (Glue 6.0) turns on ANSI SQL mode by default, so invalid casts and dates raise errors instead of returning null; code written for 5.0 should be tested before moving to 6.0.
Capacity and pricing:
| Worker type | DPUs per worker | Resources per worker | Use |
|---|---|---|---|
| G.1X | 1 | 4 vCPU, 16 GB | Default choice for most ETL |
| G.2X | 2 | 8 vCPU, 32 GB | Memory-heavy joins, ML transforms |
| G.4X, G.8X | 4, 8 | Larger | Very large shuffles |
| G.025X | 0.25 | 2 vCPU, 4 GB | Low-volume streaming jobs |
- Price: USD 0.44 per DPU-hour for standard Spark jobs (Glue 2.0 to 5.1), billed per second with a 1-minute minimum per run. Flex execution (spare capacity, for non-urgent jobs) is USD 0.29 per DPU-hour. Glue 6.0 pricing may differ ⚠ VERIFY on the pricing page.
- Minimum 2 workers for Spark jobs. A job with 2 × G.1X workers running 3 minutes costs 2 DPU × 0.05 h × 0.44 ≈ USD 0.044.
- Auto Scaling can add and remove workers during a run up to the maximum you set.
Limitations to be honest about:
- Start-up time: tens of seconds to a few minutes. For a 2 KB file, Lambda is faster and cheaper (see Lambda transforms).
- Python on executors is slower than built-in functions. Python UDFs serialise each row between the JVM and Python. Prefer built-in
pyspark.sql.functions; use pandas/Arrow UDFs or the plain-Python approach in the lab when you must. - Debugging is remote. Develop locally first; use CloudWatch logs and the Spark UI (event logs to S3) in Glue.
DataFrame vs DynamicFrame
| Spark DataFrame | Glue DynamicFrame | |
|---|---|---|
| Schema | Fixed per DataFrame | Self-describing per record; a column can be "choice" of types (e.g. int or string) |
| API | Full Spark SQL and functions | Glue transforms: ApplyMapping, ResolveChoice, DropNullFields, Relationalize |
| Bookmarks | Not directly | Yes, via transformation_ctx on sources |
| Catalog integration | spark.table(...) with Glue as metastore | create_dynamic_frame.from_catalog(...) |
| When to use | Most transformation logic | Reading with bookmarks; messy semi-structured JSON; Glue Studio-generated code |
Convert freely: dyf.toDF() and DynamicFrame.fromDF(df, glue_context, "name"). Most production jobs read with a DynamicFrame (for bookmarks), convert to a DataFrame for logic, and write with either.
Job bookmarks
A job bookmark is Glue's built-in high-water mark. For S3 sources it remembers which files (by path and modification time) a source with a given transformation_ctx has already read. On the next run, only new files are read.
- Enable with the job parameter
--job-bookmark-option job-bookmark-enable. - Every bookmarked source needs a unique
transformation_ctx. - The bookmark is saved only when the script calls
job.commit()at the end. If the job fails before that, the next run re-reads the same files (so writes must be idempotent). - Reset with
aws glue reset-job-bookmarkto reprocess everything. - Bookmarks are per job, not per file event; for event-driven, one-file-per-run designs (like the module project's main job, triggered by Step Functions), the file key is passed as a parameter and idempotency is handled with an audit record instead.
Glue Studio
Glue Studio is the visual editor (Console → AWS Glue → ETL jobs → Visual ETL). You drag sources, transforms and targets onto a canvas and it generates a PySpark script. Useful to learn the Glue API, to build simple copy-and-convert jobs, and for teams that prefer low-code. For bank pipelines, keep the generated script in version control and review it like any code; do not let console edits become the only copy.
Microsoft / Azure comparison
| You know (C#/.NET, Microsoft/Azure) | PySpark / Glue | What's different |
|---|---|---|
IQueryable<T> + LINQ | DataFrame transformations | Lazy plan, executed on a cluster |
.ToList(), .Count() | .collect(), .count() | collect() pulls all rows to the driver: only for small results |
.Where(x => x.Amount > 0) | .filter(F.col("amount") > 0) | Expressions are column objects, not lambdas |
.Select(x => new { x.Id, Amt = x.Amount * 2 }) | .select("id", (F.col("amount") * 2).alias("amt")) | |
.GroupBy(x => x.Currency).Select(g => new { g.Key, Total = g.Sum(x => x.Amount) }) | .groupBy("currency").agg(F.sum("amount").alias("total")) | |
.Join(...) | .join(other, on="account_id", how="left") | Large joins shuffle data across the network |
.Distinct() / DistinctBy | .dropDuplicates(["transaction_id"]) | Window functions give control over which duplicate wins |
SQL ROW_NUMBER() OVER (PARTITION BY ...) | F.row_number().over(Window.partitionBy(...).orderBy(...)) | Same semantics |
| SSIS data flow | Glue job | Code-first; Glue Studio for visual |
| Azure Databricks / Synapse Spark pools | AWS Glue (serverless) or Amazon EMR (managed clusters) | Glue bills per DPU-second; no cluster to keep warm |
| ADF "Copy" incremental watermark | Glue job bookmark | Managed by Glue for S3 and JDBC sources |
Python for C# developers (only what a Glue job needs)
| Concept | C# | Python |
|---|---|---|
| Blocks | { } | Indentation (4 spaces) after a : |
| Variable | var total = 0m; | total = Decimal("0") (no declaration keyword) |
| Null | null | None |
| String interpolation | $"rows={n}" | f"rows={n}" |
| Raw regex string | @"^\d{4}$" | r"^\d{4}$" |
| Method | static int Add(int a, int b) => a + b; | def add(a: int, b: int) -> int: return a + b |
| Type hints | Enforced by compiler | Not enforced; documentation and tooling only |
using / namespaces | using System.Linq; | from pyspark.sql import functions as F |
| List / dictionary | new List<string> { "a" }, new Dictionary<string,int>() | ["a"], {"a": 1} |
| LINQ over a list | names.Where(n => n.StartsWith("A")).ToList() | [n for n in names if n.startswith("A")] |
| Exceptions | try { } catch (IOException ex) { } | try: ... except OSError as ex: ... |
| Entry point | static void Main(string[] args) | if __name__ == "__main__": main(sys.argv) |
| Package manager | NuGet, .csproj | pip, requirements.txt |
| Money | decimal | decimal.Decimal in Python; DecimalType(18,2) in Spark |
Two Python traps for C# developers: F.col("a") & F.col("b") (not and), and parentheses around comparisons in boolean expressions ((F.col("x") > 0) & (F.col("y") < 5)), because & binds tighter than > in Python.
Architecture
Security-boundary view: the job runs as an IAM role (hl-m07-glue-job) that can read raw, write curated and quarantine, use the two KMS keys, read its script from an assets bucket, and write logs. It has no human users. In production it runs inside a VPC through a Glue connection with S3 reached via a gateway endpoint, and its security configuration encrypts logs, bookmarks and job temporary data.
Step-by-step implementation
Get a local Spark that matches Glue 5.0
The module lab ships a small image with Python 3.11, Spark 3.5 and Java 17 (no AWS libraries). From the repository root:
docker build -f labs/m07-transaction-pipeline/local/spark.Dockerfile -t hl-m07-spark labs/m07-transaction-pipeline/localAlternatively, pull the official image with the Glue libraries (about 7 GB of disk): docker pull public.ecr.aws/glue/aws-glue-libs:5.
Save the job
Save the script in Complete code as jobs/learn_transactions_job.py in a working folder. It detects whether it is running on Glue (awsglue importable) or locally, so the same file runs in both places.
Run it locally on the messy sample (free)
MSYS_NO_PATHCONV=1 docker run --rm --memory 2g \
-v "$PWD/jobs:/job" -v "$PWD/datasets/samples:/data:ro" hl-m07-spark \
spark-submit --master "local[2]" /job/learn_transactions_job.py \
--raw_path /data/csv/transactions_messy.csv \
--curated_path /tmp/out/curated/transactions \
--quarantine_path /tmp/out/quarantine/transactions \
--run_id run-local-001Expected output (verified with the committed sample):
input=1036 curated=983 quarantined=53
AMOUNT_SIGN 8
DUPLICATE 23
INVALID_CURRENCY 12
INVALID_DATE 10Explore interactively
Start a PySpark shell in the same image (docker run -it --rm -v ... hl-m07-spark pyspark) and try the LINQ-style operations from the comparison table on spark.read.option("header", True).csv("/data/csv/transactions.csv"). Use .explain() on a DataFrame to see the physical plan: the column pruning and filter pushdown you learned in the file-formats lesson are visible there.
Deploy to Glue and run once (credits)
Upload the script, create the job with the IaC below (Glue 5.0, 2 × G.1X, 10-minute timeout, bookmarks disabled for this one-off job), copy the messy CSV to raw, and start a run with the CLI. Expect a 1–3 minute run costing about USD 0.02–0.05.
Try a bookmark
Deploy the small bookmarked variant (bookmark_demo.py below), run it twice: the second run reads zero new files. Add a file and run again: only the new file is read.
AWS Console instructions
- Console → AWS Glue → ETL jobs → Script editor → Spark → Upload script: choose
learn_transactions_job.py. - Job details tab: name
hl-m07-learn-pyspark; IAM rolehl-m07-glue-job; Glue version Glue 5.0; language Python 3; worker type G.1X; requested workers 2; job timeout 10 minutes; job bookmark Disable; flex execution off; number of retries 0 (for learning; production uses retries plus idempotent writes); security configurationhl-m07-glue-security. - Advanced properties → Job parameters:
--raw_path=s3://hl-m07-raw-<account-id>/transactions/source=core/,--curated_path=s3://hl-m07-curated-<account-id>/learn/transactions/,--quarantine_path=s3://hl-m07-quarantine-<account-id>/learn/transactions/,--run_id=run-console-001. Spark UI on, with logs tos3://hl-m07-athena-results-<account-id>/spark-logs/(or a dedicated logs bucket). - Add the four tags, Save, then Run.
- Runs tab: watch status, duration and DPU hours; Output logs opens CloudWatch with the
printoutput (the control totals). - Visual ETL (optional): create a job with source S3 (CSV), transform "Change Schema", target S3 (Parquet), and look at the generated Script tab to see
DynamicFramecode.
AWS CLI
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
ASSETS=hl-m07-athena-results-$ACCOUNT # reuse a lab bucket for scripts in this lesson
# Upload the script and the input
aws s3 cp jobs/learn_transactions_job.py "s3://$ASSETS/glue-scripts/learn_transactions_job.py"
aws s3 cp datasets/samples/csv/transactions_messy.csv \
"s3://hl-m07-raw-$ACCOUNT/transactions/source=core/ingest_date=2026-11-16/transactions_messy.csv"
# Create the job (if not using CDK/Terraform)
aws glue create-job --name hl-m07-learn-pyspark \
--role "arn:aws:iam::$ACCOUNT:role/hl-m07-glue-job" \
--glue-version 5.0 --worker-type G.1X --number-of-workers 2 --timeout 10 --max-retries 0 \
--command "Name=glueetl,ScriptLocation=s3://$ASSETS/glue-scripts/learn_transactions_job.py,PythonVersion=3" \
--default-arguments "{\"--job-bookmark-option\":\"job-bookmark-disable\",\"--enable-metrics\":\"true\",\"--enable-continuous-cloudwatch-log\":\"true\"}" \
--security-configuration hl-m07-glue-security \
--tags Project=cea-lab,Lab=m07-transaction-pipeline,Environment=learn,Owner=your-name
# Start a run with this run's parameters
RUN=$(aws glue start-job-run --job-name hl-m07-learn-pyspark --arguments "{
\"--raw_path\":\"s3://hl-m07-raw-$ACCOUNT/transactions/source=core/\",
\"--curated_path\":\"s3://hl-m07-curated-$ACCOUNT/learn/transactions/\",
\"--quarantine_path\":\"s3://hl-m07-quarantine-$ACCOUNT/learn/transactions/\",
\"--run_id\":\"run-cli-001\"}" --query JobRunId --output text)
# Poll status, duration and DPU-seconds
aws glue get-job-run --job-name hl-m07-learn-pyspark --run-id "$RUN" \
--query 'JobRun.{State:JobRunState,Seconds:ExecutionTime,DPUSeconds:DPUSeconds,Error:ErrorMessage}'
# Read the job's printed control totals from CloudWatch
aws logs filter-log-events --log-group-name /aws-glue/jobs/output \
--log-stream-name-prefix "$RUN" --filter-pattern '"input="' --query 'events[].message'
# Check the output
aws s3 ls "s3://hl-m07-curated-$ACCOUNT/learn/transactions/" --recursive | headDPUSeconds is reported for jobs using auto scaling or Flex; otherwise compute DPU-hours as workers × DPUs per worker × run time.
Infrastructure as Code
using System.Collections.Generic;
using Amazon.CDK;
using Amazon.CDK.AWS.Glue;
using Amazon.CDK.AWS.IAM;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.S3.Assets;
using Constructs;
namespace Harbourline.DataLake.Infra;
public sealed class GlueJobStack : Stack
{
public GlueJobStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
var account = Aws.ACCOUNT_ID;
string Bucket(string zone) => $"arn:aws:s3:::hl-m07-{zone}-{account}";
// The script is uploaded by CDK to the bootstrap assets bucket.
var script = new Asset(this, "Script", new AssetProps { Path = "../jobs/learn_transactions_job.py" });
var role = new Role(this, "JobRole", new RoleProps
{
RoleName = "hl-m07-glue-job",
AssumedBy = new ServicePrincipal("glue.amazonaws.com"),
ManagedPolicies = new[] { ManagedPolicy.FromAwsManagedPolicyName("service-role/AWSGlueServiceRole") },
});
script.GrantRead(role);
role.AddToPolicy(new PolicyStatement(new PolicyStatementProps
{
Actions = new[] { "s3:GetObject", "s3:ListBucket" },
Resources = new[] { Bucket("raw"), $"{Bucket("raw")}/transactions/*" },
}));
role.AddToPolicy(new PolicyStatement(new PolicyStatementProps
{
Actions = new[] { "s3:GetObject", "s3:PutObject", "s3:DeleteObject", "s3:ListBucket" },
Resources = new[]
{
Bucket("curated"), $"{Bucket("curated")}/learn/*",
Bucket("quarantine"), $"{Bucket("quarantine")}/learn/*",
},
}));
Key.FromLookup(this, "RawKey", new KeyLookupOptions { AliasName = "alias/hl-m07-raw" }).GrantEncryptDecrypt(role);
Key.FromLookup(this, "CuratedKey", new KeyLookupOptions { AliasName = "alias/hl-m07-curated" }).GrantEncryptDecrypt(role);
_ = new CfnJob(this, "LearnJob", new CfnJobProps
{
Name = "hl-m07-learn-pyspark",
Role = role.RoleArn,
GlueVersion = "5.0",
WorkerType = "G.1X",
NumberOfWorkers = 2,
Timeout = 10, // minutes: a runaway job cannot bill for hours
MaxRetries = 0,
SecurityConfiguration = "hl-m07-glue-security",
Command = new CfnJob.JobCommandProperty
{
Name = "glueetl",
PythonVersion = "3",
ScriptLocation = script.S3ObjectUrl,
},
DefaultArguments = new Dictionary<string, string>
{
["--job-bookmark-option"] = "job-bookmark-disable",
["--enable-metrics"] = "true",
["--enable-continuous-cloudwatch-log"] = "true",
["--raw_path"] = $"s3://hl-m07-raw-{account}/transactions/source=core/",
["--curated_path"] = $"s3://hl-m07-curated-{account}/learn/transactions/",
["--quarantine_path"] = $"s3://hl-m07-quarantine-{account}/learn/transactions/",
["--run_id"] = "run-default",
},
Tags = new Dictionary<string, string>
{
["Project"] = "cea-lab", ["Lab"] = "m07-transaction-pipeline",
["Environment"] = "learn", ["Owner"] = "your-name",
},
});
}
}The module lab's CDK app (labs/m07-transaction-pipeline/infra/cdk/) defines the real pipeline jobs the same way, plus the Python package as a zipped asset passed with --extra-py-files.
Complete code
The teaching job
"""Teaching Glue job for Module 7: validate raw Harbourline transactions with the DataFrame API.
Runs in two places without changes:
* AWS Glue 5.0 (Spark 3.5, Python 3.11): awsglue is importable, arguments come from getResolvedOptions.
* Plain PySpark on your laptop (Docker): awsglue is missing, arguments come from argparse.
Fictional data only.
"""
import argparse
import sys
from pyspark.sql import DataFrame, SparkSession, Window
from pyspark.sql import functions as F
from pyspark.sql import types as T
try: # AWS Glue runtime
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
ON_GLUE = True
except ImportError: # local PySpark
ON_GLUE = False
RAW_COLUMNS = [
"transaction_id", "account_id", "booking_date", "value_date", "amount", "currency",
"type", "channel", "counterparty_name", "counterparty_iban", "merchant_category_code",
"reference", "status", "idempotency_key", "created_at",
]
# Raw landing shape: every column is a string. Types are applied only after validation.
RAW_SCHEMA = T.StructType([T.StructField(c, T.StringType(), True) for c in RAW_COLUMNS])
CREDIT_TYPES = ["transfer_in"]
DEBIT_TYPES = ["card_payment", "transfer_out", "direct_debit", "standing_order", "atm_withdrawal", "fee"]
def parse_args(argv: list[str]) -> dict[str, str]:
names = ["raw_path", "curated_path", "quarantine_path", "run_id"]
if ON_GLUE:
return getResolvedOptions(argv, ["JOB_NAME", *names])
parser = argparse.ArgumentParser()
for name in names:
parser.add_argument(f"--{name}", required=True)
return vars(parser.parse_args(argv[1:]))
def read_raw(spark: SparkSession, path: str) -> DataFrame:
"""Read CSV as all-strings; never let Spark guess types for money or codes."""
return (spark.read
.option("header", True)
.option("mode", "PERMISSIVE") # keep malformed lines instead of failing the job
.option("encoding", "UTF-8")
.schema(RAW_SCHEMA)
.csv(path)
.withColumn("source_file", F.input_file_name()))
def add_reason_code(df: DataFrame) -> DataFrame:
"""Add reason_code = the first rule a row breaks (null when the row is valid)."""
amount = F.col("amount")
booking = F.to_date("booking_date", "yyyy-MM-dd") # null for bad or impossible dates (Spark 3.5)
created = F.to_timestamp("created_at", "yyyy-MM-dd'T'HH:mm:ss'Z'")
amount_ok = amount.rlike(r"^-?\d{1,16}(\.\d{1,2})?$")
amount_dec = amount.cast(T.DecimalType(18, 2))
# Duplicates: the 2nd, 3rd ... row for the same transaction_id or idempotency_key.
by_id = Window.partitionBy("transaction_id").orderBy("created_at", "source_file")
by_key = Window.partitionBy("idempotency_key").orderBy("created_at", "source_file")
df = (df.withColumn("_n_id", F.row_number().over(by_id))
.withColumn("_n_key", F.row_number().over(by_key)))
reason = (
F.when(F.col("transaction_id").isNull() | F.col("account_id").isNull(), "MISSING_REQUIRED")
.when(~F.col("account_id").rlike(r"^ACC-\d{10}$"), "INVALID_ID")
.when(~F.col("booking_date").rlike(r"^\d{4}-\d{2}-\d{2}$") | booking.isNull(), "INVALID_DATE")
.when(created.isNull(), "INVALID_TIMESTAMP")
.when(~amount_ok, "INVALID_AMOUNT")
.when(~F.col("currency").isin("GBP", "EUR"), "INVALID_CURRENCY")
.when(F.col("type").isin(CREDIT_TYPES) & (amount_dec <= 0), "AMOUNT_SIGN")
.when(F.col("type").isin(DEBIT_TYPES) & (amount_dec >= 0), "AMOUNT_SIGN")
.when((F.col("_n_id") > 1) | (F.col("_n_key") > 1), "DUPLICATE")
)
return df.withColumn("reason_code", reason).drop("_n_id", "_n_key")
def to_curated(valid: DataFrame, run_id: str) -> DataFrame:
"""Typed columns, IBAN masked for display, lineage columns, partition keys."""
booking = F.to_date("booking_date", "yyyy-MM-dd")
iban = F.col("counterparty_iban")
masked = F.when(iban.isNull(), None).otherwise(
F.concat(F.substring(iban, 1, 4), F.expr("repeat('*', length(counterparty_iban) - 8)"),
F.expr("right(counterparty_iban, 4)")))
return valid.select(
"transaction_id", "account_id",
booking.alias("booking_date"),
F.to_date("value_date", "yyyy-MM-dd").alias("value_date"),
F.col("amount").cast(T.DecimalType(18, 2)).alias("amount"),
"currency", "type", "channel", "counterparty_name",
masked.alias("counterparty_iban_masked"), # the lab also adds an HMAC token column
"merchant_category_code", "reference", "status", "idempotency_key",
F.to_timestamp("created_at", "yyyy-MM-dd'T'HH:mm:ss'Z'").alias("created_at"),
"source_file",
F.lit(run_id).alias("ingest_run_id"),
F.date_format(booking, "yyyy").alias("year"),
F.date_format(booking, "MM").alias("month"),
F.date_format(booking, "dd").alias("day"),
)
def main(argv: list[str]) -> None:
args = parse_args(argv)
if ON_GLUE:
glue_context = GlueContext(SparkSession.builder.getOrCreate().sparkContext)
spark = glue_context.spark_session
job = Job(glue_context)
job.init(args["JOB_NAME"], args)
else:
spark = SparkSession.builder.appName("hl-m07-learn-pyspark").getOrCreate()
# Overwrite only the partitions this run writes (idempotent re-runs), not the whole table.
spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")
raw = read_raw(spark, args["raw_path"]).cache()
checked = add_reason_code(raw).cache()
valid = checked.filter(F.col("reason_code").isNull())
rejected = checked.filter(F.col("reason_code").isNotNull())
curated = to_curated(valid, args["run_id"])
(curated.repartition("year", "month", "day") # one task (and file) per day
.write.mode("overwrite")
.partitionBy("year", "month", "day")
.option("compression", "zstd")
.parquet(args["curated_path"]))
(rejected.withColumn("run_id", F.lit(args["run_id"]))
.coalesce(1)
.write.mode("overwrite")
.json(f"{args['quarantine_path'].rstrip('/')}/run_id={args['run_id']}"))
# Control totals: every input row is either curated or quarantined.
n_in, n_ok, n_bad = raw.count(), valid.count(), rejected.count()
print(f"input={n_in} curated={n_ok} quarantined={n_bad}")
for row in rejected.groupBy("reason_code").count().orderBy("reason_code").collect():
print(f" {row['reason_code']:<18} {row['count']}")
if n_ok + n_bad != n_in:
raise RuntimeError("Control total mismatch: rows were lost")
if ON_GLUE:
job.commit()
if __name__ == "__main__":
main(sys.argv)A minimal bookmark demo
"""Reads only NEW CSV files under --raw_path on each run (requires --job-bookmark-option job-bookmark-enable)."""
import sys
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
args = getResolvedOptions(sys.argv, ["JOB_NAME", "raw_path"])
glue_context = GlueContext(SparkContext.getOrCreate())
job = Job(glue_context)
job.init(args["JOB_NAME"], args) # loads this job's bookmark state
new_files = glue_context.create_dynamic_frame.from_options(
connection_type="s3",
connection_options={"paths": [args["raw_path"]], "recurse": True},
format="csv",
format_options={"withHeader": True},
transformation_ctx="raw_csv", # the bookmark key for this source
)
print(f"new rows this run: {new_files.count()}")
job.commit() # saves the bookmark; skip this and files are re-readA pytest for the rules (runs in the local image)
from pyspark.sql import SparkSession
from learn_transactions_job import RAW_COLUMNS, add_reason_code
def test_reason_codes():
spark = SparkSession.builder.master("local[1]").getOrCreate()
base = dict.fromkeys(RAW_COLUMNS, None) | {
"transaction_id": "01a172d5-d5f8-75f5-b05e-314cfea1cd18", "account_id": "ACC-1000000001",
"booking_date": "2026-10-25", "value_date": "2026-10-25", "amount": "-2.13", "currency": "GBP",
"type": "card_payment", "channel": "card", "status": "booked",
"idempotency_key": "07dcf311-0260-41bf-b9b4-997342959661", "created_at": "2026-10-25T11:32:59Z",
}
rows = [
base,
base | {"transaction_id": "t2", "idempotency_key": "k2", "currency": "GPB"},
base | {"transaction_id": "t3", "idempotency_key": "k3", "booking_date": "2026-02-30"},
base | {"transaction_id": "t4", "idempotency_key": "k4", "amount": "2.13"},
base, # exact duplicate of the first row
]
df = spark.createDataFrame(rows, schema=", ".join(f"{c} string" for c in RAW_COLUMNS))
df = df.withColumn("source_file", df.transaction_id) # any value; used only for ordering
codes = sorted((r["reason_code"] or "OK") for r in add_reason_code(df).collect())
assert codes == ["AMOUNT_SIGN", "DUPLICATE", "INVALID_CURRENCY", "INVALID_DATE", "OK"]Run with docker run --rm -v "$PWD/jobs:/job" -v "$PWD/tests:/tests" -e PYTHONPATH=/job hl-m07-spark python -m pytest /tests -q.
Code explained line by line
try: from awsglue... except ImportError— one script, two runtimes. Business logic never depends on Glue-only APIs, so it is testable locally. The module lab goes further: its rules live in a plain-Python package (harbourline_etl) with no Spark at all.getResolvedOptions(argv, [...])— Glue passes job parameters as--name valuearguments; this helper parses them. Locally,argparseaccepts the same flags.RAW_SCHEMAwith every column asStringType— raw is text. If Spark inferred types, a single bad amount would turn the whole column into strings or nulls silently..option("mode", "PERMISSIVE")— malformed lines become rows with nulls rather than failing the job; the rules then quarantine them.F.input_file_name()— lineage: which S3 object each row came from.F.to_date("booking_date", "yyyy-MM-dd")— returns null for invalid dates such as2026-02-30in Spark 3.5. In Spark 4 (Glue 6.0) ANSI mode raises instead; usetry_to_date-style functions or disable ANSI deliberately when you migrate ⚠ VERIFY the exact function set for your Spark version.Window.partitionBy("transaction_id").orderBy(...)+F.row_number()— SQL'sROW_NUMBER() OVER (...). The first occurrence gets 1 and is kept; later ones are duplicates. Ordering makes the choice deterministic.F.when(...).when(...)— a SQLCASE WHENchain; first match wins, like the C#else ifchain in the first lesson. Every rule is a column expression evaluated inside the JVM: no Python per row, so it is fast.(F.col("_n_id") > 1) | (F.col("_n_key") > 1)— parentheses are required in Python because|binds tighter than>.masked— keeps the first 4 and last 4 characters of the IBAN. The lab additionally writes an HMAC token (from a key in Secrets Manager) so analysts can join on counterparties without seeing them.F.col("amount").cast(T.DecimalType(18, 2))— exact money type in curated Parquet.F.date_format(booking, "MM")— zero-padded partition values (month=06), matching the lake layout and projection settings.spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic")— withmode("overwrite"), only partitions present in this run's output are replaced. Without it,overwritewould delete every existing day in the table: a classic data-loss bug..repartition("year", "month", "day")— groups each day's rows into one task, so each partition gets one file instead of one file per task (the small-files problem)..coalesce(1)on quarantine — rejected rows are few; one file is easiest to read..cache()onrawandchecked— they are used by several actions (two writes and three counts); caching avoids re-reading S3 each time.- Control totals and
raise RuntimeError— a failed assertion fails the Glue run, which the orchestrator sees and alerts on. job.commit()— saves bookmark state (harmless when bookmarks are disabled). Always the last line of a Glue job.
Security considerations
⚠ Learning shortcut — not for production
The job role uses the AWS managed AWSGlueServiceRole policy, runs outside a VPC, writes the masked IBAN but no token, and stores the script in a shared lab bucket. In production: a customer managed policy scoped to exact prefixes and keys; a Glue connection in private subnets with an S3 gateway endpoint; the tokenisation key read from Secrets Manager at run time (harbourline_etl.masking.tokenise); scripts deployed from CI to a dedicated, versioned assets bucket; and job parameters that cannot be overridden to point at arbitrary buckets.
- Never print rows.
df.show()andprint(row)send data to CloudWatch logs, which many more people can read than the data lake. Print counts and reason codes only. - Security configuration. Encrypt CloudWatch logs, job bookmarks and S3 temporary data with KMS (
hl-m07-glue-securityfrom the previous lesson). - Spark UI logs contain query plans and sometimes literal values; store them in an encrypted, restricted bucket with expiry.
- Dependencies.
--additional-python-modulesandrequirements.txtdownload from PyPI at start-up unless you point to a private repository; for banks, mirror approved packages (CodeArtifact) and pin versions. - Least privilege on writes. The job may write only under its output prefixes; the dynamic-overwrite bug above becomes much less dangerous if the role cannot delete outside them.
Banking use case
Harbourline's module project uses two Glue jobs (see labs/m07-transaction-pipeline/glue/jobs/ and Lab 7.4):
hl-m07-transactions-etl— started by Step Functions for one raw file per run. It reads the file as bytes, applies theharbourline_etlrules (the same code the pytest suite runs locally), writes curated Parquet and quarantine JSON, evaluates a Glue Data Quality ruleset, writes gold outputs and finally an audit record. Idempotency comes from the audit record keyed on the file's bucket, key and version.hl-m07-raw-dq-bookmarked— a scheduled job using a bookmark to process any new files under an incoming prefix, with a DQ ruleset that routes failing rows to quarantine.
Both run on Glue 5.0 with 2 × G.1X workers and a 10-minute timeout; at the small dataset size a run costs a few cents.
Common mistakes
- Letting Spark infer the schema → doubles for money, integers for codes → explicit schema, strings in raw.
mode("overwrite")without dynamic partition overwrite → yesterday's partitions deleted → setpartitionOverwriteMode=dynamicor write to run-specific prefixes.collect()on large DataFrames → driver out of memory → aggregate first;collect()only small results.- Python UDFs for simple logic → slow jobs → use built-in functions; UDFs only when unavoidable.
- Forgetting
job.commit()→ bookmarks never advance; the same files are reprocessed every run. - Same
transformation_ctxfor two sources → bookmark confusion → unique names per source. - No timeout → a stuck job bills for the default timeout (48 hours ⚠ VERIFY current default) → set a timeout on every job.
- Developing only in the cloud → every test costs a minute of DPUs and a cold start → develop and unit-test locally.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
AnalysisException: Path does not exist | Wrong --raw_path or missing s3:// | Check parameters in the run details |
AccessDenied reading S3 | Role lacks s3:GetObject or kms:Decrypt | Add to the job role; check the key policy |
| Job succeeds but curated has one file per row group / many tiny files | Too many tasks writing each partition | repartition by partition columns before writing; compact |
| All previous days disappeared from curated | Static overwrite mode | Restore from S3 versions; set dynamic overwrite |
Py4JJavaError ... OutOfMemoryError on the driver | collect() or toPandas() on big data | Remove; aggregate; increase worker type only after fixing code |
| Bookmark job re-reads all files | Missing job.commit(), bookmarks disabled, or transformation_ctx missing | Enable bookmarks, add the context and the commit |
ModuleNotFoundError: awsglue locally | Running plain PySpark | Expected; the script falls back. Use the official Glue image if you need Glue APIs |
Spark 4 (Glue 6.0) job fails with CAST_INVALID_INPUT | ANSI mode on by default | Validate before casting or use try_ functions |
| Very slow start (minutes) | Large --additional-python-modules installs | Pre-package dependencies as a wheel/zip; use --extra-py-files |
Cost and cleanup
Expected cost: local runs are free. One Glue run: 2 workers × 1 DPU × about 2 minutes ≈ 0.067 DPU-hours × USD 0.44 ≈ USD 0.03; with the 1-minute minimum, a very short run still bills 2 DPU-minutes (≈ USD 0.015). The bookmark demo (two or three runs) adds about USD 0.05. CloudWatch logs for a few KB: effectively free. Total: under USD 0.15.
export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
aws glue delete-job --job-name hl-m07-learn-pyspark
aws glue delete-job --job-name hl-m07-bookmark-demo 2>/dev/null
aws s3 rm "s3://hl-m07-curated-$ACCOUNT/learn/" --recursive
aws s3 rm "s3://hl-m07-quarantine-$ACCOUNT/learn/" --recursive
aws s3 rm "s3://hl-m07-athena-results-$ACCOUNT/glue-scripts/" --recursive
# Job role (or cdk destroy / terraform destroy)
aws iam delete-role-policy --role-name hl-m07-glue-job --policy-name data-access 2>/dev/null
aws iam detach-role-policy --role-name hl-m07-glue-job --policy-arn arn:aws:iam::aws:policy/service-role/AWSGlueServiceRole
aws iam delete-role --role-name hl-m07-glue-job
# Glue job log groups keep logs until deleted; set retention or delete
aws logs put-retention-policy --log-group-name /aws-glue/jobs/output --retention-in-days 7
aws logs put-retention-policy --log-group-name /aws-glue/jobs/error --retention-in-days 7
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[].ResourceARN"The CDK-created role has inline policies with generated names; cdk destroy removes them.
Exercises
- 🟢 Guided: Run the job locally on
transactions_messy.csv, then ontransactions.csv. Explain why the clean file still quarantines nothing, and list the curated partitions written. - 🟡 Challenge: Add an
INVALID_IBANrule. Built-in functions cannot do mod-97 easily, so importis_valid_ibanfrom the lab'sharbourline_etl.ibanmodule and wrap it in a pandas UDF (@F.pandas_udf("boolean")). Measure the run time with and without the UDF on themediumdataset. - 🔴 Live problem: A Glue job that ran in 6 minutes for a year now takes 70 minutes and occasionally fails with executor out-of-memory errors. Nothing in the code changed; the data volume grew about 20%. The job joins transactions to accounts and writes partitioned Parquet. Describe how you would investigate (which metrics, which Spark UI pages) and three likely fixes.
Assignment
Create a small repository with the teaching job, the pytest file, a requirements.txt (pyspark==3.5.5, pytest), the Docker commands, and the CDK or Terraform job definition. Add a README section "From LINQ to PySpark" with ten of your own translations from C# you have written at work.
Solutions
The clean file passes every rule: no bad dates, currencies, signs or duplicates (it is generated to be valid), so quarantined=0 and curated=1012. Partitions run from year=2026/month=09/day=30 (the "balance brought forward" rows dated the day before the period) to year=2026/month=11/day=16. On the messy file the job finds 53 rejects. The answer key lists more defect rows (for example 9 invalid IBANs, 16 bad dates, 22 encoding issues and 7 late-arriving rows) because this teaching job checks only booking_date, has no IBAN rule, and treats encoding and lateness as non-blocking. Counts can also differ by one or two where a row has two defects: the first failing rule wins.
import pandas as pd
from harbourline_etl.iban import is_valid_iban
@F.pandas_udf("boolean")
def iban_ok(ibans: pd.Series) -> pd.Series:
return ibans.map(lambda v: True if v is None else is_valid_iban(v))
# in add_reason_code, before DUPLICATE:
# .when(~iban_ok(F.col("counterparty_iban")), "INVALID_IBAN")Ship the package with --extra-py-files s3://.../harbourline_etl.zip on Glue, or PYTHONPATH locally. Pandas UDFs move data in Arrow batches rather than row by row, so they are much faster than plain Python UDFs, but still slower than built-ins; you will see a measurable but modest increase on medium. Why: reuse the tested rule rather than re-implementing mod-97 in Spark SQL, and accept a small cost for correctness.
Investigate: Glue job metrics (executor memory, shuffle bytes, number of active executors), the Spark UI (enable event logs) Stages page for a stage with very uneven task durations (skew), and the SQL page for the join strategy. Likely causes and fixes: (1) the accounts table grew past the broadcast threshold, so a cheap broadcast join became a shuffle join: broadcast it explicitly (F.broadcast(accounts)) if it still fits in memory, or filter it to needed columns first; (2) data skew: a few business accounts with huge volumes create giant tasks: salt the key or enable adaptive query execution's skew handling (on by default in Spark 3.5, check settings); (3) small files in input from more frequent deliveries: compact upstream or read with grouping options; (4) only then scale: G.2X workers or auto scaling. Also check for an accidental collect() or a UDF added to a common path.
Knowledge check
Answer: Transformations (filter, select, join) only build a lazy plan. Actions (count, collect, write) trigger optimisation and execution. It mirrors IQueryable operators versus ToList().
Answer: For each source with a transformation_ctx, which data (S3 files or JDBC keys) has been processed. It is saved when the script calls job.commit().
Answer: So mode("overwrite") replaces only the partitions present in the new output. In static mode it deletes the whole target table location first.
Answer: For reading with job bookmarks, for semi-structured data with inconsistent types (choice types and ResolveChoice), and when using Glue-specific transforms or Glue Studio-generated code.
Answer: 2 DPU × (4/60) h × USD 0.44 ≈ USD 0.059.
Interview questions
- "How do you test Glue jobs?" — Keep business rules in plain Python (or pure DataFrame functions) and unit-test with pytest locally; run the full job in a local Spark or the official Glue Docker image on sample data; integration-test in a sandbox account with tiny data; assert control totals in the job itself.
- "Your Glue job is slow; what do you check?" — Input file sizes and counts, partition pruning, join strategy and skew in the Spark UI, Python UDFs, unnecessary
collect/cache, output small files; scale workers last. - "Glue or EMR?" — Glue for serverless, per-second, standard ETL with catalogue and bookmark integration; EMR (or EMR Serverless) when you need cluster-level control, custom frameworks, long-running clusters or specific Spark configurations.
Certification practice
A Glue job runs hourly over s3://bucket/landing/ and reprocesses every file each time, creating duplicates downstream. What is the simplest fix?
A. Delete files from landing/ after each run.
B. Enable job bookmarks, give the S3 source a transformation_ctx, and call job.commit() at the end.
C. Add a crawler before the job.
D. Increase the job's worker count.
Answer: B. Bookmarks track processed files per source. A destroys raw evidence and races with arriving files. C catalogues files but does not track processing. D does nothing for duplicates.
After a Glue job re-run, a partitioned Parquet table in S3 contains only the most recent day; all earlier days are gone. The job writes with mode("overwrite").partitionBy("dt"). What is the cause and fix?
A. S3 lifecycle expired the data; disable lifecycle.
B. Static partition overwrite deleted the table location; set spark.sql.sources.partitionOverwriteMode to dynamic.
C. The crawler deleted the partitions; set DeleteBehavior to LOG.
D. Job bookmarks removed old files; disable bookmarks.
Answer: B. Static overwrite replaces the whole output path. A, C and D do not delete data files in this way (a crawler only changes catalogue entries).
A nightly Glue Spark job has no strict completion deadline other than "before 07:00", and starts at 01:00. Which option reduces cost with no code change?
A. Switch to G.2X workers. B. Use the Flex execution class for the job. C. Convert the job to a Python shell job. D. Run the job every hour instead.
Answer: B. Flex runs on spare capacity at a lower DPU-hour rate for non-urgent jobs. A increases cost. C requires a rewrite and cannot run Spark. D multiplies runs and cost.
Completion checklist
- I can translate five LINQ operators into PySpark
- I ran the teaching job locally and matched the expected control totals
- I can explain dynamic partition overwrite and why it prevents data loss
- I ran the job once on Glue (or chose to stay local) and read its logs and DPU usage
- I deleted the job, its role, outputs and scripts, and set log retention
Key takeaways
- PySpark DataFrames are LINQ over
IQueryable, executed lazily across a cluster. - Read raw as strings, validate with reason codes, then cast to exact types; never let Spark guess money.
- Use built-in functions, window functions for de-duplication, dynamic partition overwrite for idempotent writes, and control totals in the job.
- Glue adds catalogue integration, bookmarks, Data Quality and serverless capacity at USD 0.44 per DPU-hour (Flex cheaper), with a 1-minute minimum.
- Develop and test locally for free; run in Glue only when the logic is right.
Further reading
- AWS Glue versions
- Develop and test AWS Glue jobs locally using a Docker image
- Tracking processed data using job bookmarks
- AWS Glue pricing
- PySpark API reference (Apache Spark 3.5)
- Hands-on: Lab 7.2 — CSV to partitioned Parquet and Lab 7.4 — Glue job with bookmarks and Data Quality
- Next: Lightweight transforms with Lambda
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.
AWS Glue Data Catalog and crawlers: tables, partitions and schema drift
Catalogue Harbourline's raw and curated data in the AWS Glue Data Catalog, run a crawler safely with the right schema-change and recrawl policies, and know when to define schemas yourself, register partitions explicitly or use Athena partition projection instead.
Lightweight transforms with Lambda: C# and Python functions for small files
Validate Harbourline's partner SFTP payment batches with an S3-triggered Lambda function in C# (.NET 10) or Python, including trailer control totals, IBAN checks and quarantine, and learn exactly where Lambda's limits make Glue the better tool.