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

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.

beginner⏱ 2 h 30 min🟡 Low costDEA-C01SAA-C03

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 IQueryable as 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

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 versionSparkPythonNotes
6.04.1.13.13Default for new jobs that do not specify a version; Iceberg 1.11, Spark Connect for interactive sessions; S3A connector only
5.13.5.63.11Iceberg 1.10, Iceberg format v3
5.03.5.43.11requirements.txt support; Lake Formation Spark-native fine-grained access control; S3 Tables support
4.03.3.03.10Older; plan upgrades
0.9, 1.0, 2.02.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 typeDPUs per workerResources per workerUse
G.1X14 vCPU, 16 GBDefault choice for most ETL
G.2X28 vCPU, 32 GBMemory-heavy joins, ML transforms
G.4X, G.8X4, 8LargerVery large shuffles
G.025X0.252 vCPU, 4 GBLow-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 DataFrameGlue DynamicFrame
SchemaFixed per DataFrameSelf-describing per record; a column can be "choice" of types (e.g. int or string)
APIFull Spark SQL and functionsGlue transforms: ApplyMapping, ResolveChoice, DropNullFields, Relationalize
BookmarksNot directlyYes, via transformation_ctx on sources
Catalog integrationspark.table(...) with Glue as metastorecreate_dynamic_frame.from_catalog(...)
When to useMost transformation logicReading 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-bookmark to 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 / GlueWhat's different
IQueryable<T> + LINQDataFrame transformationsLazy 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 flowGlue jobCode-first; Glue Studio for visual
Azure Databricks / Synapse Spark poolsAWS Glue (serverless) or Amazon EMR (managed clusters)Glue bills per DPU-second; no cluster to keep warm
ADF "Copy" incremental watermarkGlue job bookmarkManaged by Glue for S3 and JDBC sources

Python for C# developers (only what a Glue job needs)

ConceptC#Python
Blocks{ }Indentation (4 spaces) after a :
Variablevar total = 0m;total = Decimal("0") (no declaration keyword)
NullnullNone
String interpolation$"rows={n}"f"rows={n}"
Raw regex string@"^\d{4}$"r"^\d{4}$"
Methodstatic int Add(int a, int b) => a + b;def add(a: int, b: int) -> int: return a + b
Type hintsEnforced by compilerNot enforced; documentation and tooling only
using / namespacesusing System.Linq;from pyspark.sql import functions as F
List / dictionarynew List<string> { "a" }, new Dictionary<string,int>()["a"], {"a": 1}
LINQ over a listnames.Where(n => n.StartsWith("A")).ToList()[n for n in names if n.startswith("A")]
Exceptionstry { } catch (IOException ex) { }try: ... except OSError as ex: ...
Entry pointstatic void Main(string[] args)if __name__ == "__main__": main(sys.argv)
Package managerNuGet, .csprojpip, requirements.txt
Moneydecimaldecimal.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/local

Alternatively, 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-001

Expected output (verified with the committed sample):

input=1036 curated=983 quarantined=53
  AMOUNT_SIGN        8
  DUPLICATE          23
  INVALID_CURRENCY   12
  INVALID_DATE       10

Explore 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

  1. Console → AWS Glue → ETL jobs → Script editor → Spark → Upload script: choose learn_transactions_job.py.
  2. Job details tab: name hl-m07-learn-pyspark; IAM role hl-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 configuration hl-m07-glue-security.
  3. 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 to s3://hl-m07-athena-results-<account-id>/spark-logs/ (or a dedicated logs bucket).
  4. Add the four tags, Save, then Run.
  5. Runs tab: watch status, duration and DPU hours; Output logs opens CloudWatch with the print output (the control totals).
  6. 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 DynamicFrame code.

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 | head

DPUSeconds is reported for jobs using auto scaling or Flex; otherwise compute DPU-hours as workers × DPUs per worker × run time.

Infrastructure as Code

infra/GlueJobStack.cs
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

jobs/learn_transactions_job.py
"""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

jobs/bookmark_demo.py
"""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-read

A pytest for the rules (runs in the local image)

tests/test_learn_job.py
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

  1. 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.
  2. getResolvedOptions(argv, [...]) — Glue passes job parameters as --name value arguments; this helper parses them. Locally, argparse accepts the same flags.
  3. RAW_SCHEMA with every column as StringType — raw is text. If Spark inferred types, a single bad amount would turn the whole column into strings or nulls silently.
  4. .option("mode", "PERMISSIVE") — malformed lines become rows with nulls rather than failing the job; the rules then quarantine them.
  5. F.input_file_name() — lineage: which S3 object each row came from.
  6. F.to_date("booking_date", "yyyy-MM-dd") — returns null for invalid dates such as 2026-02-30 in Spark 3.5. In Spark 4 (Glue 6.0) ANSI mode raises instead; use try_to_date-style functions or disable ANSI deliberately when you migrate ⚠ VERIFY the exact function set for your Spark version.
  7. Window.partitionBy("transaction_id").orderBy(...) + F.row_number() — SQL's ROW_NUMBER() OVER (...). The first occurrence gets 1 and is kept; later ones are duplicates. Ordering makes the choice deterministic.
  8. F.when(...).when(...) — a SQL CASE WHEN chain; first match wins, like the C# else if chain in the first lesson. Every rule is a column expression evaluated inside the JVM: no Python per row, so it is fast.
  9. (F.col("_n_id") > 1) | (F.col("_n_key") > 1) — parentheses are required in Python because | binds tighter than >.
  10. 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.
  11. F.col("amount").cast(T.DecimalType(18, 2)) — exact money type in curated Parquet.
  12. F.date_format(booking, "MM") — zero-padded partition values (month=06), matching the lake layout and projection settings.
  13. spark.conf.set("spark.sql.sources.partitionOverwriteMode", "dynamic") — with mode("overwrite"), only partitions present in this run's output are replaced. Without it, overwrite would delete every existing day in the table: a classic data-loss bug.
  14. .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).
  15. .coalesce(1) on quarantine — rejected rows are few; one file is easiest to read.
  16. .cache() on raw and checked — they are used by several actions (two writes and three counts); caching avoids re-reading S3 each time.
  17. Control totals and raise RuntimeError — a failed assertion fails the Glue run, which the orchestrator sees and alerts on.
  18. 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() and print(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-security from 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-modules and requirements.txt download 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 the harbourline_etl rules (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 → set partitionOverwriteMode=dynamic or 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_ctx for 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

SymptomLikely causeFix
AnalysisException: Path does not existWrong --raw_path or missing s3://Check parameters in the run details
AccessDenied reading S3Role lacks s3:GetObject or kms:DecryptAdd to the job role; check the key policy
Job succeeds but curated has one file per row group / many tiny filesToo many tasks writing each partitionrepartition by partition columns before writing; compact
All previous days disappeared from curatedStatic overwrite modeRestore from S3 versions; set dynamic overwrite
Py4JJavaError ... OutOfMemoryError on the drivercollect() or toPandas() on big dataRemove; aggregate; increase worker type only after fixing code
Bookmark job re-reads all filesMissing job.commit(), bookmarks disabled, or transformation_ctx missingEnable bookmarks, add the context and the commit
ModuleNotFoundError: awsglue locallyRunning plain PySparkExpected; the script falls back. Use the official Glue image if you need Glue APIs
Spark 4 (Glue 6.0) job fails with CAST_INVALID_INPUTANSI mode on by defaultValidate before casting or use try_ functions
Very slow start (minutes)Large --additional-python-modules installsPre-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 on transactions.csv. Explain why the clean file still quarantines nothing, and list the curated partitions written.
  • 🟡 Challenge: Add an INVALID_IBAN rule. Built-in functions cannot do mod-97 easily, so import is_valid_iban from the lab's harbourline_etl.iban module and wrap it in a pandas UDF (@F.pandas_udf("boolean")). Measure the run time with and without the UDF on the medium dataset.
  • 🔴 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

Knowledge check

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

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

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