AWS Data Engineering Services

Data Engineering & Modern Data Platforms · Lesson 54 · Modern Data Platforms and Cloud

Lesson Overview

The platform channel has a new diagram. S3 sits under everything, Glue crawls it, Athena queries it, Redshift serves it, Kinesis feeds it, and EMR, Lake Formation, DMS, and MWAA orbit the edges. The diagram is a product catalog. It does not say which service is allowed to answer "how many learners completed lesson one last week," which service may scan a single day of raw progress, or what happens to the Kafka topics this course already runs.

Modern Data Architecture Overview placed the completion mart in a curated grain and left the raw zone replayable. Lessons on the lake, the lakehouse, Delta, Iceberg, Databricks, and Snowflake fix the table and the engine shapes. This lesson is the AWS service map for the same TechLearningPro workloads, written for a team whose account boundary is already AWS. The account is not a reason to adopt every logo in the account's console.

Four workloads stay constant across this lesson and the Azure and Google Cloud lessons, so the comparison is the workload.

1. The completion mart. Grain (learner_id, lesson_id, attempt_number), definition completion-v4, daily, on the board by 06:00 local. About forty people open it on Monday. Five people open it on Wednesday.
2. Raw exploration. One day of progress, ad hoc, allowed to be slower, never allowed to become the Monday number.
3. The provisional activity tile. Five-minute freshness, labeled provisional, fed by the progress topic from Module 4.
4. Enrollment change. The Debezium topic and the replication slot from CDC with Kafka and Debezium. A second change feed is a second slot on the primary.

The volumes are the course's teaching platform, fixed so lesson 58 can price them: about 80 million progress events a day, on the order of 40 GB a day once compacted to Parquet, an enrollment table of about 8 million rows with about 200,000 changes a day, and about 120,000 purchase lines a day. Payment instruments never land. That prohibition is older than this module.

Amazon MSK is on the map because the Kafka contract may stay. Kinesis is on the map because some producers will never hold a Kafka client. Picking both for the same event is the failure this lesson is here to prevent. Real-Time Data Quality and Monitoring still decides which cursor may page a human. A green Glue job is not that cursor.

Learning Objectives

By the end of this lesson you will be able to:

1. Place S3, the Glue Data Catalog, Athena, Redshift, Kinesis, Firehose, EMR, MSK, and DMS on one TechLearningPro diagram and name the workload each one owns.
2. Explain what S3 strong consistency guarantees for a pipeline and which pipeline problems a successful put leaves untouched.
3. Choose an explicit catalog table over a crawler for a contracted dataset, and say when a crawler is still useful.
4. Describe what a Glue job bookmark tracks, which arguments make it advance, and which file operations make it reread.
5. Estimate an Athena query as bytes scanned after partition pruning, and set a workgroup cutoff that protects the raw prefix.
6. Choose Redshift Serverless or a small provisioned RA3 cluster for the Monday mart, and choose Athena for single-day exploration.
7. Pick a DISTKEY and a sort key for the completion grain and explain how each one changes a join or a date filter.
8. Decide among the existing Kafka topics, MSK, Kinesis Data Streams, and Data Firehose using replay, order, and the consumers you already have.
9. Keep a single enrollment change feed, either the Debezium topic or DMS, with the replication slot still visible on the primary.
10. Write an AWS decision record that the next two lessons can mirror and that the cost lesson can price.

Prerequisites

• Lesson 2 places the completion mart in a curated grain and keeps the raw zone replayable. This lesson assigns AWS services to those boxes.
• Data Formats and Serialization is the file-format contract. Parquet and compression live there. S3 stores the objects.
• ETL and ELT Architecture is the transform once bytes are columnar. Glue Spark is the transform when the raw shape is still messy.
• Data Lineage and Metadata Management names datasets. Glue databases and tables should use those names.
• Kafka Connect and Data Integration, lesson 46, and lesson 47 already land progress, fold enrollment from a slot, and page on freshness.
• The lake, lakehouse, Delta, and Iceberg lessons own layout and table commits. Athena reading an Iceberg table is a query engine on that commit.
• The Databricks and Snowflake lessons own those engines. If either is the chosen engine, AWS is the account boundary and the network path, and this lesson's warehouse section is the alternative.
• /learn/kafka is the broker primer. /learn/postgresql is the operational database.

Detailed Core Concepts

The same four questions, asked of each service

Every box in the catalog collapses to four questions.

What is the contract with the bytes? S3 stores objects. It does not know that an object is a progress event, a partition, or a completion row. The catalog and the table format hold that meaning.

What is the contract with time? A Firehose buffer becomes an object and then drops the record from the hose. A Kinesis stream or an MSK topic can replay inside retention. A Redshift table serves the latest merge. The tile's five minutes and the mart's 06:00 are different clocks, which lesson 47 already separated.

What do you pay when nobody is querying? Object storage bills while it sits. A provisioned Redshift cluster bills while it is running. Athena bills when a query scans. Serverless Redshift bills RPU-hours while work is active, plus the base capacity you configured. Idle is a design choice you will price in lesson 58.

Who is allowed to be wrong? Exploration may scan one partition and return a draft. The Monday mart may not publish two definitions. The provisional tile may lag inside its label. Enrollment CDC may not drop a change and stay green.

S3 is the raw contract's disk

TechLearningPro's raw progress prefix is a bucket and a key layout:

s3://tlp-data-prod/raw/progress/event_date=2026-11-10/part-00017.snappy.parquet

S3 has provided strong consistency for puts, overwrites, deletes, and lists since December 2020. A writer that receives a successful PutObject can hand the key to a reader and expect the reader to see that object. A list that started before the put finished can still miss it, because the list did not wait for the commit. The successful put is the durability point for that key. The successful job, with its manifest of keys, is the commit for the batch. Lake zones and a table commit sit on top of this. Consistency of a single object is not a multi-object transaction. Iceberg and Delta exist because a prefix of Parquet files is not an atomic publication.

Objects are replaced as a whole. Producers land new objects. Compaction writes new objects and deletes the small ones after readers have a commit that points at the replacements. Deleting first is how a day disappears when the write fails.

Prefixes are a naming convention. Query engines prune when the name is a partition column they were told about. A hashed key prefix spreads extreme request rates and defeats pruning. At this platform's landing rate, date prefixes are the layout. S3's request budget per prefix still exists at very high put rates. Eighty million progress events a day, landed as compacted Parquet objects rather than as one put per event, sits far under that ceiling. The producer that puts one tiny object per event will hit request cost and small-file pain long before it hits a theoretical prefix limit. Compaction is the design. Hashing the prefix is the wrong lesson from an older S3 talk.

Storage class is a retention decision. Standard for the days you might replay during an incident. Standard-IA after its minimum storage duration, when the day is cold and you still want millisecond reads. Glacier Instant Retrieval when an audit restore is acceptable and a Monday incident restore is not. Lifecycle rules implement that sentence. They are not a backup. A transition inside the same bucket and region does not save you from a bad delete in the same account. Versioning, a replication destination you have restored in a game day, and Object Lock where a regulator requires immutability, match those threats. Lesson 58 prices the extra copy. Lesson 57 decides who can delete.

Encryption at rest is SSE-S3 or SSE-KMS. SSE-KMS with a customer managed key is the choice when security must be able to deny the key and when audit must show which role used it. S3 Bucket Keys reduce the KMS request count on prefixes with many objects. Pick the key per bucket at creation. A raw prefix that mixes encrypted and unencrypted objects is an accident, and the accident usually arrives through a writer that ignored the bucket default.

The Glue Data Catalog is a shared metastore

Athena, Redshift Spectrum, Glue ETL, and EMR can share the Glue Data Catalog. A database and a table there should be the names from lesson 22: tlp.progress_raw, tlp.completion_attempt. Partitions map to prefixes. The catalog stores the schema, the location, and the partition values. The Parquet rows stay in S3.

A crawler walks a prefix, infers a schema, and adds partitions. It is a discovery tool for a landing zone whose producers you do not fully control. It is a poor author of completion_attempt. Inference widens a column from integer to string because one bad file arrived. It can register a partition you meant to quarantine. It bills crawler DPUs. For a contracted dataset, CREATE TABLE plus partition projection, or an explicit ADD PARTITION, is the contract. Projection fits event_date: the range is a template, and the catalog does not need a row per day stored ahead of the data.

Glue jobs are separate from the catalog. A job can be Spark or a small Python shell. Glue 4.0 runs Spark 3.3 and Python 3.10. Glue 5.0 runs Spark 3.5 and Python 3.11. Pin the Glue version beside the script. A laptop on Spark 3.5 and a job on Glue 4.0 are different runtimes. Confirm the release notes when you pin, because AWS binds the Spark version to the Glue version.

A Glue Spark job is billed in DPUs. One DPU is 4 vCPU and 16 GB of memory. Worker types such as G.1X and G.2X change the packing. Flex execution costs less for a job that can wait. The completion build may use Flex when it still finishes before 06:00 with margin. A job on the critical path uses standard execution and an alarm on duration.

Job bookmarks are incremental state for a Glue job. They advance when the job commits. For an S3 source read as a DynamicFrame with a transformation_ctx, the bookmark remembers objects already processed. Three conditions have to be true together: bookmarks enabled on the job, a stable transformation_ctx, and job.commit() on the success path. A renamed object looks new. A rewritten object with a new modification time looks new. A bookmark is not a database cursor and it is not a Kafka offset. Enrollment CDC keeps the slot and the Connect offset. The bookmark is for files that only arrive.

Athena sells a scan

Athena is a serverless query engine. Engine version 3 is Trino-based. The bill is bytes scanned, with a documented minimum per query, plus the storage of the results prefix. The price per terabyte is regional and public. The lever you control is the number of bytes.

Bytes fall when the format is columnar, compression is real, the query names its columns, and a partition predicate eliminates prefixes. SELECT * over a year of raw JSON is the "before" picture in the cost lesson. The same business question against completion_attempt for seven dates in Parquet is a different system.

Workgroups separate exploration from production. The exploration workgroup writes results to its own prefix, carries a per-query scanned-bytes cutoff (bytes_scanned_cutoff_per_query on the workgroup), and is the workgroup analysts can start. The production workgroup runs publish checks. A cutoff stops the query that forgot event_date. It does not design the table for you.

Athena can query Iceberg tables registered in the Glue catalog. The commit still belongs to the table format. If Monday concurrency outgrows a shared serverless scan, the serving copy moves to Redshift and Athena stays on investigation. Two serving copies of the same mart are two definitions unless one is an explicit replica with a named lag.

Redshift pulls compute off managed storage on RA3

Provisioned Redshift is a leader node plus compute nodes. The leader plans. The slices run the work. RA3 nodes keep a local SSD cache and store the durable data in Redshift Managed Storage. You can change compute without a full reload, and you still pay for managed storage while a cluster is paused. Pause stops compute charges. It does not keep a session warm. Resume takes time. If that time does not fit before 06:00, the cluster that serves the board stays resumed, and some other environment is the one you pause.

Redshift Serverless uses RPUs in a workgroup. You set a base. The meter is RPU-hours while queries run, and the base is what quiet days cost. Confirm the current minimum base before a development workgroup enters a budget, because the floor has moved before. Serverless fits a mart that spikes on Monday if the maximum RPU cap is a number you can stand on that morning and the base is a number you can stand on Wednesday.

Distribution and sort are the physical design you can actually defend.

• DISTSTYLE KEY on learner_id co-locates one learner's rows. A join to another table distributed on learner_id can avoid shuffling the fact. A join only on lesson_id does not receive that gift.
• DISTSTYLE ALL copies a small dimension, such as lesson attributes, onto every node. It fits a small table that joins often. It does not fit the fact.
• DISTSTYLE EVEN, or AUTO after you have evidence, spreads rows when each query joins on a different key.
• A compound sort key of (event_date, lesson_id) lets zone maps skip blocks for the Monday date predicate. Interleaved sort keys are the older answer to unpredictable filters. Prefer the compound key that matches the certified predicate.

Redshift does not enforce a primary key you declare. The grain lives in the merge and in a post-check that fails the publish on duplicates. COPY from Parquet in S3, using an IAM role on the cluster, is the load. A client that inserts row by row will still be loading at 06:00.

Spectrum reads the Glue catalog from the cluster. It reaches the raw prefix without a full load. It still spends cluster capacity. Use it to stage and to investigate. The board reads the curated table.

Concurrency scaling adds transient capacity when WLM queues, on its own meter. Materialized views can hold the Monday aggregate when refresh finishes inside the window. WLM queues keep COPY and the dashboard from sharing one pool of memory. A load that takes every slot at 05:50 makes the board late even when each statement is fast alone.

Streaming ingestion can pull Kinesis or MSK into a materialized view. That can own the provisional tile when the consumer lives in the warehouse. It does not replace the Connect sink that writes the replayable prefix. One path owns the tile. The other owns raw. The decision record says which.

Kinesis, Firehose, and MSK make different promises

Kinesis Data Streams is a sharded log. In provisioned mode a shard takes up to 1 MB/s or 1,000 records/s in, and up to 2 MB/s out shared by consumers reading with GetRecords. Enhanced fan-out gives a consumer its own throughput on SubscribeToShard. On-demand mode lets the service add shards. The partition key still hashes to a shard. A producer that keys every event with the literal progress fills one shard while the stream's average looks bored. lesson_id is a poor key during a live session that packs a cohort into one lesson. learner_id spreads progress on this platform, because one learner cannot fill a shard. Order holds inside a shard. There is no cross-shard transaction.

Retention starts at 24 hours and can extend toward 365 days, at a price. Inside retention a consumer can replay. Consumers are at-least-once. PutRecords can return success for the call while individual records in the batch failed. The producer has to read the per-record results and retry the failures. Treating the HTTP status as proof that the batch landed will drop events.

Data Firehose buffers records and writes them to S3, Redshift, OpenSearch, HTTP endpoints, or an Iceberg table. Buffer size and buffer interval decide the object size. Analytics prefixes want those settings toward the high end of the allowed range, then compaction if objects are still small. Delivery is at-least-once. After the buffer flushes, the hose does not keep an offset you can rewind. Replay is "read the object the destination kept." Firehose is a landing hose.

MSK is managed Kafka. Use it when Connect, the schema registry, transactions, or Kafka Streams are load-bearing and you want the brokers off machines you patch. Glue Schema Registry can store the schemas. Compatibility stays the rule from the schema lesson. MSK is a Kafka broker. Kinesis is a different API. Dual-writing progress to both will diverge the first time one side retries.

DMS can full-load and CDC PostgreSQL into S3, Kinesis, or Redshift. A PostgreSQL source still uses logical replication, so a slot can still pin WAL on the primary. Choose DMS when the sink is an AWS store and no subscriber needs the Debezium envelope. Keep Debezium when the topic already feeds the cohort and the quality checks. Two slots, each believing it is the feed, both retain WAL. The disk page from lesson 47 does not care which product opened the slot.

EMR and EMR Serverless exist for a Spark runtime you must control: a version Glue does not pin, a bootstrap, a long shuffle whose executor shape you already measured. This daily volume does not need a standing cluster. The PySpark you trust from Module 3 runs on Glue when the Spark version matches, and on EMR Serverless when it does not. A visual rewrite of a reviewed script is not a migration plan.

Orchestration stays Airflow when that is how the team ships. MWAA is managed Airflow. Glue workflows can run a short graph. Step Functions can call Glue and Athena. One graph has to be able to say whether the mart published. Two orchestrators that each own half the morning will disagree on the day the board is wrong.

Lake Formation adds catalog and column grants beside IAM. Hybrid mode exists so a migration can honor both. An AccessDenied you cannot explain from the IAM policy alone is often this second layer. Lesson 57 designs the grants. This lesson only requires that the job role and the analyst role are different roles, and that neither is an account administrator.

Selection inside an AWS account

Firehose is the right hose for a new edge producer. It is the wrong second copy of progress. The table is a workload decision.

Architecture and Design Diagrams

The progress topic stays the log. S3 is the raw zone. Glue Spark compacts and projects. The catalog names the tables. Athena reads raw under a cutoff. Redshift serves the mart. One orchestrator publishes.

mermaidCopy
flowchart LR
  subgraph sources [Sources]
    PG["PostgreSQL enrollment"]
    APP["Progress producers"]
  end
  subgraph log [Log already running]
    K["Kafka or MSK"]
  end
  subgraph lake [S3]
    RAW["raw prefixes"]
    CUR["curated Parquet"]
  end
  subgraph meta [Catalog]
    GLUE["Glue Data Catalog"]
  end
  subgraph serve [Serving]
    ATH["Athena exploration"]
    RS["Redshift mart"]
  end
  subgraph clock [Publisher]
    AF["Airflow or MWAA"]
  end
  PG --> K
  APP --> K
  K --> RAW
  RAW --> GLUE
  GLUE --> ATH
  RAW --> AF
  AF --> CUR
  CUR --> GLUE
  CUR --> RS
  RS --> BOARD["Monday board"]
  ATH --> INV["Single-day investigation"]

A producer that cannot use a Kafka client is a side door with its own prefix. It is not a second progress topic.

mermaidCopy
flowchart LR
  EDGE["Edge producer"] --> KS["Kinesis Data Streams"]
  KS --> FH["Data Firehose"]
  FH --> PREFIX["raw/edge/event_date"]
  PREFIX --> COMPACT["Compaction"]
  COMPACT --> CAT["Glue table edge_event"]

Technical Explanation

A successful landing is not a catalog update

The Connect sink writes an object and commits its offsets after the write, with the dead-letter path lesson 47 already watches. S3 can make that object visible immediately. Nothing in that put updates the Glue catalog. A partition the catalog does not know is invisible to a partitioned Athena table. Partition projection removes that miss for a pure date layout, because Athena builds the prefix from the predicate. The location template has to match the keys. A template of event_date=${event_date} and a sink that writes dt= scans nothing and returns success. The check is a row count against the topic or the manifest, not a green query.

Compaction reads the day's small objects, writes larger Parquet, and drops the small objects only after the new objects are the ones the table reads. For a daily prefix, idempotent overwrite is the contract I want: every run rewrites event_date=2026-11-10 completely. Bookmarks fit a continuous folder where objects only arrive and are never replaced. Mixing bookmarks with a cleanup job that renames keys will reprocess or skip, and the symptom appears as a duplicate grain or a short day.

What Athena does with a query

The workgroup checks identity and the bytes cutoff. The planner uses the catalog. A predicate on event_date skips prefixes. A predicate on learner_id does not skip a date partition. It filters after those column chunks are read. Naming columns reads those columns. SELECT * reads all of them. Results land in the workgroup results prefix, which is another copy of the output, sometimes of sensitive aggregates. Expire it. Lesson 57 treats that bucket as a data store.

A query that scans two terabytes of raw JSON and a query that scans a few gigabytes of a week's mart are the same engine. The workgroup history is the evidence the cost lesson needs. Retain the history long enough to review it.

What Redshift does with COPY and MERGE

COPY reads Parquet in parallel with the cluster role. Files should be large enough to feed the slices and small enough to retry. Tens of thousands of 1 MB files spend the load in listing and task overhead. A single multi-hundred-gigabyte object is a poor retry unit. A few hundred files of a few hundred megabytes is the usual shape after compaction. Load staging. Merge into curated.completion_attempt on the grain. Truncate staging after the checks pass.

The merge is idempotent when staging holds one row per grain. Two staging rows for one attempt make the merge fail or apply twice, depending on the statement and the engine version. Deduplicate before COPY, and assert zero duplicate grains. Sort keys help the date filter. They do not invent uniqueness. A dist key that helps a join the board never runs is a shuffle you added for a slide. Read EXPLAIN on the certified query.

Serverless and provisioned differ in the bill and the ceiling. They should not each hold a private definition of completion. One workgroup or one cluster owns curated. The board points at a view that becomes readable after the publish mark.

Failures worth designing for

A Glue job that throws before job.commit() leaves a bookmark unmoved, so the next run rereads. The sink has to tolerate the reread. Partition overwrite does. Append does not.

An Athena query killed by the cutoff returns an error and leaves no complete result. That is the right outcome in exploration. The same error in the production workgroup is an alarm, because scheduled SQL should have a known scan.

A COPY option that skips bad rows and inserts nulls will publish a mart whose completed_at is quietly empty for the broken files. Prefer a failed load and a reject count you can page on, in the same spirit as a dead-letter topic.

Kinesis partial batches need per-record retries. Firehose has the same shape. The Connect producer you already run has its own retry and its own dead letter. A new Kinesis producer is not ready until the partial-batch test exists.

DMS and Debezium harm a stalled primary in the same way. Task status and connector status are not the WAL measurement. The slot query remains the page.

Practical Implementation or Design Walkthrough

Problem

Publish curated.completion_attempt by 06:00. Raw progress already lands as Parquet under event_date. Enrollment changes stay on the existing topic. The board reads Redshift. Investigators read Athena. Email stays out of the mart. Payment fields stay out of the lake.

Schemas

Raw progress, one row per event:

Curated attempt, one row per grain:

Document which timestamp becomes event_date on the curated table. Mixing event time and processing time in that column moves Monday's count between runs.

Catalog table with projection

sqlCopy
CREATE EXTERNAL TABLE tlp.progress_raw (
  event_id string,
  learner_id string,
  lesson_id string,
  event_time timestamp,
  event_type string,
  position_seconds int
)
PARTITIONED BY (event_date string)
STORED AS PARQUET
LOCATION 's3://tlp-data-prod/raw/progress/'
TBLPROPERTIES (
  'parquet.compression' = 'SNAPPY',
  'projection.enabled' = 'true',
  'projection.event_date.type' = 'date',
  'projection.event_date.format' = 'yyyy-MM-dd',
  'projection.event_date.range' = '2024-01-01,NOW',
  'projection.event_date.interval' = '1',
  'projection.event_date.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://tlp-data-prod/raw/progress/event_date=${event_date}/'
);

Idempotent rewrite of one day

Pin the job to a Glue version in the job definition. This script expects Glue 4.0, Spark 3.3, and Python 3.10. It overwrites one partition. Airflow passes --event_date. Bookmarks stay off.

pythonCopy
import sys
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from pyspark.sql import functions as F

args = getResolvedOptions(sys.argv, ["JOB_NAME", "event_date"])
sc = SparkContext()
glue_context = GlueContext(sc)
spark = glue_context.spark_session
job = Job(glue_context)
job.init(args["JOB_NAME"], args)

raw = spark.read.parquet(
    f"s3://tlp-data-prod/raw/progress/event_date={args['event_date']}/"
)
forbidden = [c for c in ("email", "card_token", "full_name") if c in raw.columns]
if forbidden:
    raise RuntimeError(f"forbidden columns in raw progress: {forbidden}")

day = (
    raw.filter(F.col("event_id").isNotNull())
    .dropDuplicates(["event_id"])
    .withColumn("event_date", F.lit(args["event_date"]))
)
# Production file count follows compressed size, about 256 MB per file.
# Eight files is a dev-bucket default, not the 40 GB production number.
(
    day.repartition(8)
    .write.mode("overwrite")
    .parquet(
        f"s3://tlp-data-prod/curated/progress_event/event_date={args['event_date']}/"
    )
)
job.commit()

The business definition completion-v4 stays a reviewed transform from this cleaned day plus enrollment state. The script above is file hygiene and the forbidden-column gate. Folding an unreviewed definition into the same file is how the rate changes on a Friday without a version bump.

Redshift load

sqlCopy
CREATE TABLE curated.completion_attempt (
  learner_id VARCHAR(64) NOT NULL,
  lesson_id VARCHAR(64) NOT NULL,
  attempt_number INTEGER NOT NULL,
  completed_at TIMESTAMPTZ,
  event_date DATE NOT NULL,
  definition_version VARCHAR(32) NOT NULL
)
DISTSTYLE KEY
DISTKEY (learner_id)
COMPOUND SORTKEY (event_date, lesson_id);

CREATE TABLE staging.completion_attempt (LIKE curated.completion_attempt);

COPY staging.completion_attempt
FROM 's3://tlp-data-prod/curated/completion_attempt/event_date=2026-11-10/'
IAM_ROLE 'arn:aws:iam::111122223333:role/tlp-redshift-copy'
FORMAT AS PARQUET;

MERGE INTO curated.completion_attempt
USING staging.completion_attempt AS s
ON curated.completion_attempt.learner_id = s.learner_id
 AND curated.completion_attempt.lesson_id = s.lesson_id
 AND curated.completion_attempt.attempt_number = s.attempt_number
WHEN MATCHED THEN UPDATE SET
  completed_at = s.completed_at,
  event_date = s.event_date,
  definition_version = s.definition_version
WHEN NOT MATCHED THEN INSERT (
  learner_id, lesson_id, attempt_number,
  completed_at, event_date, definition_version
) VALUES (
  s.learner_id, s.lesson_id, s.attempt_number,
  s.completed_at, s.event_date, s.definition_version
);

TRUNCATE staging.completion_attempt;

The account in the role ARN is an example. The role is attached to the cluster, limited to the curated prefix, and is separate from the analyst role. TRUNCATE runs after the duplicate check and the day-count check, in the same DAG, only on success.

Checks the board waits for

sqlCopy
SELECT learner_id, lesson_id, attempt_number, COUNT(*) AS n
FROM curated.completion_attempt
GROUP BY 1, 2, 3
HAVING COUNT(*) > 1;

SELECT COUNT(*) AS attempts
FROM curated.completion_attempt
WHERE event_date = DATE '2026-11-10'
  AND definition_version = 'completion-v4';

Compare attempts to the manifest the Spark job wrote beside the partition. A green merge of an empty prefix is a short day, and the count is how you see it. Investigators use the same date predicate on Athena:

sqlCopy
SELECT lesson_id, COUNT(DISTINCT learner_id) AS learners
FROM tlp.progress_raw
WHERE event_date = '2026-11-10'
GROUP BY 1;

Operations

Airflow orders the morning: compact the day, build the attempt partition, COPY, MERGE, run the checks, then mark the dataset published the way lesson 22 records a run. The board reads published.completion_attempt, which points at the mart only after that mark. Reading the base table during the merge shows a half-applied Monday.

Glue logs go to CloudWatch. Page on duration that eats the margin before 06:00, and on the forbidden-column failure, which goes to the producer owner. Redshift pages on dashboard queue time and on COPY failures. Athena cutoff hits in exploration are coaching. Cutoff hits in production are a design failure.

Enterprise Real-World Scenarios

TechLearningPro keeps the log it already has

The team runs the progress topic, the Connect S3 sink, and Debezium. A proposal adds Kinesis, Firehose, DMS, a crawler, Athena as the board, and a Redshift cluster in one quarter.

The review keeps four additions. S3 layout and the Glue catalog become explicit, with projection on event_date, because the sink prefixes lived only in the connector config. A Glue job rewrites each day to large Parquet and fails on forbidden columns. Athena gets an exploration workgroup with a bytes cutoff. Redshift Serverless gets the mart, loaded by COPY and MERGE, with capacity for the Monday window. Kafka stays. MSK is a later broker migration if patching brokers is the pain, and that project is a Kafka project. Kinesis is refused for progress because it would be a second log. DMS is refused while the topic has subscribers. The crawler is refused for the mart and allowed, for a while, on a vendor drop whose schema still changes without notice. That vendor prefix is not on the board.

The freshness page remains the page. The published view's maximum event_date against the clock is freshness for the mart. Job success is a step.

Northline sellers share an account pattern and not a table grant

Northline is the teaching marketplace. Instructors sell courses to companies. About 500,000 order lines a day land. Each instructor may see their own gross merchandise value. Finance closes the month once.

The shape that fits is order_line in Redshift with row-level security on seller_id, bound to the seller identity, plus a finance role that can read every seller for the close. Athena stays the one-day investigation tool, in a workgroup whose results prefix is locked down, because a raw order can carry a buyer email the mart does not store. The mart keeps buyer_id and seller_id. Row-level security is attached to the table the portal role can select. A filtering view, with the portal role still able to select the base table, does not isolate sellers.

Northline does not need a second bus for orders. The monthly close is a table with close_month, a definition version, and a publish mark. It shares the portal cluster only when WLM can isolate the close from the portal. When it cannot, a second workgroup for finance is the isolation, and lesson 58 gives that split a cost and an owner. Lesson 57 requires a test: a seller role selects another seller's rows and gets zero.

Best Practices

• Write the four-workload table into the decision record before the first production bucket exists. Empty rows are how a second landing hose appears beside a working sink.
• Layout raw and curated as event_date=yyyy-MM-dd/ and teach every engine that predicate.
• Pin the Glue version and the Spark version in the job and in the repository that ships the script.
• Split exploration and publication into different Athena workgroups, result prefixes, and IAM roles.
• Load Redshift through staging and a merge on the grain. Truncate staging only after the checks pass.
• Scope the cluster copy role to the curated prefix. Analysts use their own role.
• Aim compacted Parquet at a few hundred megabytes per object. Rewrite a partition when the job reruns.
• Name the replication slot's product in the runbook, Debezium or DMS, and page on the slot.
• Expire Athena results and Glue temporary prefixes. They hold copies.
• Match Glue table names to the dataset names from lesson 22 so Module 6 inherits one vocabulary.

Common Mistakes and Anti-Patterns

The crawler owns the schema. One malformed object widens a column and the crawler run stays green. Contracted tables are DDL in review. Crawlers belong on unmanaged drops, with a person accepting the new schema.

Athena over raw JSON is the Monday board. The query is short and the bill is not. The deeper defect is two analysts applying two definitions and both looking official. The board reads the published mart at a pinned definition_version.

A provisioned cluster that never pauses, plus a serverless copy of the same mart. You pay twice, and the definitions drift. One serving mart. Pause only capacity whose resume time fits the SLO.

A hot Kinesis key. A constant key, or lesson_id during one live lesson, pins a shard. On-demand mode adds shards for the stream and still hashes that key to one of them. Spread by learner_id. Alarm on iterator age per shard.

Firehose treated as a topic. After the flush there is no offset to rewind. If the design needs rewind, it needs a stream or a Kafka topic, and the destination objects are the landing, not the log.

Bookmarks plus a rename "cleanup." New keys look unprocessed. Idempotent partition overwrite has less hidden state. Use bookmarks on append-only folders only.

DMS beside Debezium. Two slots retain WAL. Each console says the task is healthy until the primary disk is not. One feed.

COPY that nulls bad rows and continues. The publish is green and completed_at is empty where the files were wrong. Fail the load. Count rejects.

An account-admin role on the Glue job. The job can read buckets outside this platform. The role names its prefixes. The hurry that creates the admin role is the incident's first commit. Lesson 57 is the full control design.

Troubleshooting and Design Review

Athena returns zero rows for a day visible in the bucket. Compare keys with storage.location.template. A mismatch is an empty scan of a prefix that is not empty of objects. If you are not using projection, SHOW PARTITIONS answers a different question than a bucket listing.

A bookmarked job rereads a day. Confirm bookmarks are enabled, transformation_ctx is stable, and job.commit() ran. Then look for rewritten keys. If the design is partition overwrite, a reread is the contract. If the design appends, the duplicate-grain check is the alarm.

COPY is slow and slices look idle. Count files. Tiny files spend the load in overhead. Compact, then load. Also confirm the role can read every key. An authorization error on one file fails a strict load and is the outcome you want.

The dashboard queues behind the load. WLM shows one queue. Split the load and the board. On serverless, the workgroup may have hit its RPU maximum and the load took it. Move the load earlier or raise the cap for the window. Queue time is the evidence.

Iterator age grows on one Kinesis shard. Check the partition-key distribution for that hour. A stuck consumer raises age on the shards it owns. A hot key raises one shard. Read those as different incidents.

DMS says healthy and enrollment is late. Query the slot on PostgreSQL. Task status is not retained WAL.

AccessDenied for someone who "has IAM." Read the error for the denying service. Check Lake Formation mode and the table grant, the workgroup, and the bucket policy. Adding a broader IAM allow before you know which layer denied is how the job role becomes an administrator.

Design review for any AWS proposal in this course:

• Which of the four workloads does this service own, and which running component already owns it?
• What is the commit: an object, a catalog partition, a table snapshot, or a published view?
• How do you replay a bad day?
• Which role can read raw, and which role can read only the mart?
• What happens to primary WAL if this ingest stalls?

Interview Questions and Answers

Q1. When would you serve the completion mart from Athena, and when from Redshift?

I serve it from Athena when the certified queries are few, the data is partitioned Parquet or Iceberg, a bytes cutoff is acceptable, and Monday concurrency still meets 06:00. I serve it from Redshift when concurrent sessions, isolation between load and dashboard, and a warehouse SQL surface are the requirement. I keep one serving definition. If Redshift serves, Athena stays on raw exploration. If a month of measurements shows Athena meets the board, the warehouse copy can go. The decision record names that measurement in advance.

Q2. What does a Glue job bookmark remember, and when is overwrite the better contract?

With bookmarks enabled, a stable transformation_ctx, and job.commit(), an S3 DynamicFrame source remembers objects the job already processed. Renamed or rewritten keys look new. A failure before commit should reread, so the sink must allow that. I use bookmarks for an append-only landing folder. For a daily prefix I overwrite event_date and I leave bookmarks off. The partition rewrite is the whole contract, and a rerun does not depend on hidden state.

Q3. Why can Kinesis on-demand still lag on a single shard?

On-demand adds shards for stream-level load. The partition key still hashes to one shard, and that shard still has an ingest ceiling. A constant key or a single lesson_id during a live session hits the ceiling while the stream average looks fine. I key progress by learner_id, alarm on per-shard iterator age, and I retry the failed members of a PutRecords response.

Q4. What do you give up if Firehose is the only progress path?

I give up the retained log. After a flush I can reread only objects the destination kept. I also give up consumers that already use the Kafka client, transactions, or the schema registry. Firehose is how an edge producer with no Kafka client lands into its own prefix. It is not how I retire a topic Module 4 already reads.

Q5. How do you lay out learner_id and event_date in Redshift for this mart?

I distribute the fact on learner_id when the heavy joins are learner-aligned, and I replicate the small lesson dimension. I compound-sort (event_date, lesson_id) because the certified filter is a date range. Redshift will not enforce the grain, so the merge key is the grain and a post-check fails publish on duplicates. If the certified query only aggregates by lesson and date, the sort key matters more, and distribution can be EVEN or AUTO after I read the plan.

Q6. S3 is strongly consistent. Which bugs does that remove?

A successful put, overwrite, or delete is what a later read and list observe. The bug that leaves is a reader that missed an object whose put had already succeeded. What remains is a list that ran too early, a multi-object batch with no manifest or table commit, a missing catalog partition, a projection template that does not match the keys, and a compaction that deleted old files before new ones were durable. Per-object consistency is not a publication transaction.

Q7. Why keep EMR on the diagram beside Glue?

Glue is managed Spark bound to a Glue version, billed in DPUs. EMR and EMR Serverless are how I run a Spark version or a shuffle shape Glue does not match, including PySpark this course already reviewed. This volume does not need a standing cluster. I will not rewrite a reviewed script into a visual job to justify a service, and I will not stand up EMR for a job Glue already runs at the same Spark version.

Q8. How do you keep an exploration query off a year of raw JSON?

The table analysts can query is Parquet, projected or partitioned on event_date. Their workgroup has a scanned-bytes cutoff. Results expire. The JSON landing prefix is not granted to their role. Production uses another workgroup. Cutoff hits in exploration are coaching. Cutoff hits in production page, because scheduled SQL should have a known scan.

Q9. When is DMS the enrollment path if Debezium is already running?

Only as a replacement, after every subscriber has moved, with one slot on the primary. I do not add DMS beside the topic. The WAL risk is the slot, not the product name. If the pain is operating Connect, I look at MSK Connect or at the pager before I open a second decoding slot. Two feeds become two truths the first time one task pauses.

Q10. A design lists Glue, Athena, Redshift, Kinesis, Firehose, DMS, and a crawler. What do you remove first?

I remove the duplicate log and the duplicate change feed. Progress stays on Kafka or MSK with the existing S3 sink. Enrollment stays on one slot. The crawler comes off any table whose schema is in git. What remains is S3, an explicit catalog, compaction, Athena for one-day investigation, and Redshift for the mart when concurrency requires it. Kinesis and Firehose come back only with a producer that cannot speak Kafka, writing a different prefix. The test is one owner per workload.

Hands-On Exercises

Exercise: Map the four workloads and publish one day

Problem statement. You have a development bucket and a sample day of progress Parquet. Fifty thousand rows is enough. You also have a one-page proposal that assigns Kinesis, Firehose, DMS, a crawler, Athena, and Redshift to every box. Produce the decision record and one day of completion_attempt you can defend. If you have no Redshift cluster, simulate the merge in PostgreSQL and still write the DISTKEY and sort key as comments on the real design. Do not dual-write the sample onto a second bus.

Tasks.

1. Assign one owner to each workload. For every service you will not deploy this quarter, write the condition that reopens it.
2. Create the raw external table with partition projection. Show a one-day query and the bytes scanned, or the plan evidence your engine provides.
3. Implement the forbidden-column check and an idempotent rewrite of one date. Rerun it and show that the grain count does not double.
4. Load staging and merge on the grain. Run the duplicate check and the day-count check. Inject a duplicate event_id, fail the check, and keep the evidence.
5. Write the job role and the analyst role as prefixes and actions. No account administration.
6. State the freshness signal the board uses after publish, and how it differs from a successful Glue job.

Expected deliverables. Decision record, DDL, job script, merge, rerun evidence, failed-check evidence, role scope, freshness note.

Evaluation rubric.

Lesson Summary

S3 stores objects and, since late 2020, makes a successful put visible to the next read. It does not publish a multi-file table, hold a schema, or stop an expensive query. The Glue Data Catalog holds names. Crawlers discover unmanaged drops. Contracted tables are DDL, and date prefixes should use projection or an explicit partition. Glue Spark compacts and rejects forbidden columns on a pinned runtime. Bookmarks belong to append-only landings. Athena is the exploration scan, fenced by a workgroup and a bytes cutoff. Redshift, serverless or a small RA3 cluster, is the Monday mart when concurrency and isolation need a warehouse: COPY, merge on the grain, sort by event_date, distribute by the join you actually run. Kafka or MSK remains the progress log. Kinesis is the side door for a producer with no Kafka client. Firehose lands that door and cannot rewind it. DMS replaces Debezium only as the single slot. EMR runs Spark you must control. One orchestrator publishes.

Azure Data Engineering Services asks the same four questions of storage, Data Factory, Synapse, Event Hubs, and Fabric. The Google Cloud lesson asks them of object storage, BigQuery, Dataflow, and Pub/Sub. Security and cost lessons then put identity and the bill on whichever pair you kept. DataOps and Data Platform Engineering is where the decision record becomes a platform you run, and the capstone is where one cloud and one serving engine have to be chosen with the reopen conditions still attached.

Further Reading

• Amazon S3 User Guide, including the consistency model, storage classes, lifecycle configuration, and default encryption.
• AWS Glue Developer Guide, job bookmarks, Glue version release notes, and the Data Catalog.
• Amazon Athena User Guide, workgroups, and partition projection.
• Amazon Redshift Database Developer Guide, distribution styles, sort keys, COPY, MERGE, WLM, and Redshift Serverless.
• Amazon Kinesis Data Streams Developer Guide and the Amazon Data Firehose Developer Guide.
• Amazon MSK Developer Guide and the AWS DMS User Guide for PostgreSQL sources.
• AWS Lake Formation Developer Guide for the catalog grants lesson 57 designs.
• Course lessons on Connect, CDC, real-time quality, and the lakehouse formats this map assumes.