Shelf · AWS · Chapter 7

Chapter 7: Data engineering on AWS

Your Jenkins ETL scripts already do extract, transform, load by hand. This chapter hands those same jobs to managed AWS data services—Glue, Athena, Kinesis, Redshift Serverless, MWAA—and drills the one thing none of them give you for free: proof that a successful run actually moved the right data.

Difficulty
Priority
aws-07-01Card 1 of 6

Glue catalogue, crawlers and managed Spark

HardP3 · Role-dependentTalk about it15 min
gluedata-catalogcrawlersparkbookmarksquality-gates
Needs: nothing. Start here.

Why you care

Every Jenkins job you have written assumes a fixed set of columns because you wrote the schema into the script. Glue flips that: the Glue Data Catalog is a shared schema definition that Athena, Spark, Redshift Spectrum and every Glue job read from, so one table definition drives every downstream query. The catch is the crawler that builds that definition infers schema from a sample of files, and it will happily overwrite a column's type the moment the data drifts—silently, with no error, no code change on your side. Interviewers ask about Glue not to test trivia but to see whether you reach for a schema contract the moment a table has real consumers, instead of trusting inference forever.

Explain like I'm 5

The Glue Data Catalog is a shared library's index-card system. The crawler is a librarian who walks the shelves (S3) on a schedule and updates each index card with whatever title, author and genre it currently sees on the book's cover. Every patron—Athena, Spark, Redshift—trusts whatever the card says the next time they go looking. If one day a book's cover has a sticker over the author's name, the librarian updates the card to match. A drifting shelf becomes a drifting card, and every patron's search is now reading a definition nobody reviewed.

Core concept

  • The Glue Data Catalog is a managed, Hive-compatible metastore: databases → tables → partitions, shared across Athena, Redshift Spectrum, EMR and Glue jobs. One definition, many readers.
  • A crawler scans a data source (an S3 prefix, a JDBC connection) on a schedule or on demand, infers column names and types from a sample of files, and writes or updates the table definition and partitions in the catalog.
  • Crawler inference is probabilistic, not authoritative: it looks at a sample, not every file. One unusual batch can shift the inferred schema for the whole table on the next crawl.
  • A schema contract is the opposite approach: you (or an upstream producer) define the table explicitly—via the Glue API, Terraform or CloudFormation—and configure the crawler so it cannot silently rewrite existing columns, or stop crawling that table at all once the schema is set.
  • Glue's managed Spark ETL runs PySpark or Scala on Spark clusters AWS provisions for the duration of the job; you write against the schema-flexible DynamicFrame API (handles mismatched or "choice" types) or a plain Spark DataFrame once the schema is fixed and trusted.
  • Job bookmarks track which input files or partitions a specific job has already processed—by S3 path/timestamp or a JDBC bookmark key—so a rerun only picks up new data. This is Glue's version of an incremental watermark, scoped to one job, not a global ledger.
  • Bad-record handling: a DynamicFrame read lets you resolve type mismatches explicitly (resolveChoice) or filter unexpected rows to a separate sink instead of crashing the whole job or silently nulling values out.

Use a crawler to bootstrap an unknown dataset's schema, or when the data's shape is genuinely expected to keep changing. Lock the schema—explicit DDL, and a crawler restricted to adding partitions or new columns only—the moment the table has downstream consumers who assumed stability, and route anything that doesn't match to quarantine rather than let the catalog silently redefine the table underneath them.

Under the hood

Glue Spark jobs are billed by worker-hour (or DPU-hour, depending on how you provision workers) for however long the managed Spark cluster is up, not just the time your code actively processes rows—cold start to provision Spark before your script's first line runs can be tens of seconds to a couple of minutes, which matters for small, frequent jobs more than large, infrequent ones. Job bookmarks are per-job state, not per-table: Glue records which S3 objects (by path and last-modified timestamp) or JDBC bookmark keys a specific job has already seen across its previous successful runs. That is why a backfill run under a renamed or newly created job, or files landing out of order relative to their timestamps, can both reprocess data and miss data—the bookmark compares against what this job has seen, not a universal "has this row been loaded" ledger. Current Glue major versions pin specific Spark and Python runtimes (for example, Glue 4.0 ships Spark 3.3/Python 3.10, Glue 5.0 ships Spark 3.5/Python 3.11, Glue 6.0 ships Spark 4.1/Python 3.13)—check the current Glue version table before writing a new job, since library availability and behavior change across major versions.

Minimal example Crawler inference drifts; a schema contract catches it

PythonCrawler-style inference drifts across batches; a contract flags the driftruns
def infer_schema(rows):
    types = {}
    for row in rows:
        for k, v in row.items():
            types.setdefault(k, set()).add(type(v).__name__)
    return {k: sorted(v) for k, v in types.items()}

batch1 = [{"amount": 499.0}, {"amount": 250.0}]
batch2 = batch1 + [{"amount": "499.00"}]   # one bad batch mixes in a string

print("crawl 1:", infer_schema(batch1))
print("crawl 2:", infer_schema(batch2))
print("contract violation:", "str" in infer_schema(batch2)["amount"])
Output
crawl 1: {'amount': ['float']}
crawl 2: {'amount': ['float', 'str']}
contract violation: True

In production Job bookmark skips processed files; bad rows go to quarantine

PythonGlue-style job bookmark skips processed files; bad rows go to quarantineruns
import json
from pathlib import Path

raw = Path("raw/orders")
raw.mkdir(parents=True)
(raw / "part-001.json").write_text(json.dumps({"order_id": 1, "amount": 499.0}))
(raw / "part-002.json").write_text(
    "\n".join([json.dumps({"order_id": 2, "amount": 120.0}),
               "{not valid json",
               json.dumps({"order_id": 3, "amount": -5.0})])
)

seen = set()   # bookmark: S3 keys this job has already processed

def run_job(source_dir, quarantine):
    todo = [f for f in sorted(source_dir.glob("*.json")) if str(f) not in seen]
    loaded = 0
    for f in todo:
        for line in f.read_text().splitlines():
            try:
                row = json.loads(line)
            except json.JSONDecodeError as e:
                quarantine.append((f.name, f"bad-json: {e.msg}")); continue
            if row.get("amount", 0) < 0:
                quarantine.append((f.name, "bad-value: negative amount")); continue
            loaded += 1
        seen.add(str(f))
    return loaded, [f.name for f in todo]

quarantine = []
loaded1, files1 = run_job(raw, quarantine)
print(f"run 1: loaded={loaded1} files={files1} quarantine={len(quarantine)}")

(raw / "part-003.json").write_text(json.dumps({"order_id": 4, "amount": 75.0}))
loaded2, files2 = run_job(raw, quarantine)
print(f"run 2 (bookmark skips old files, picks up new one): loaded={loaded2} files={files2}")

for fname, reason in quarantine:
    print(f"quarantined [{fname}]: {reason}")
Output
run 1: loaded=2 files=['part-001.json', 'part-002.json'] quarantine=2
run 2 (bookmark skips old files, picks up new one): loaded=1 files=['part-003.json']
quarantined [part-002.json]: bad-json: Expecting property name enclosed in double quotes
quarantined [part-002.json]: bad-value: negative amount

See it happen Crawler, catalog and job in one pipeline

scan samplewrite/update schemaschema contractread new filescheck/update statewrite good rowsbad rowsquery same tableregister partitionsRaw S3CrawlerGlueData CatalogAthena /SpectrumSpark ETLjobJobbookmarkCuratedParquetQuarantine
  1. Crawler scans a sample of the raw S3 prefix on its schedule.
  2. Crawler writes or updates the inferred schema in the Glue Data Catalog.
  3. The Spark job reads the catalog's current schema as its contract before touching new files.
  4. The job checks job-bookmark state so a rerun only picks up files it has not already processed.
  5. Rows that fail validation go to quarantine; everything else lands in curated Parquet.
  6. New partitions in curated get registered back into the same catalog table.
  7. Athena, Spark and Redshift Spectrum all query the one schema the crawler and the job agree on.
Notice: the catalog is the single schema every engine trusts; the moment the crawler and a hand-maintained contract disagree, every downstream query reads a definition nobody double-checked.
Scenario A

In your world

The situation

Your Jenkins job currently loads S3 CSV order exports into Postgres via pandas with a hardcoded column list. Your team now wants Athena access to the same raw data for analysts, so you point a Glue crawler at the same S3 prefix on a nightly schedule and let it build and maintain the Glue Data Catalog table, with the crawler free to update the table definition on every run.

What actually breaks

A supplier's export starts including a discount_pct column that is usually blank or a plain number, but for one bad batch arrives as the literal string "10%". The crawler re-samples on its next run, infers discount_pct as string because of that one batch, and overwrites the table definition that previously had it as double. Every Athena query doing discount_pct * amount for the revenue report starts failing with a cast error the next morning. The Jenkins pandas job, which never touched the catalog, keeps running green the whole time—so the first sign of trouble is a broken analyst dashboard, not an alert.

What you'd do on Monday

Stop letting the crawler own a column's type once a table has real consumers. Define the schema once as an explicit contract, and restrict the crawler so it can only add new columns, never change existing ones. At the file level, add validation before anything reaches the curated table: a numeric column containing "10%" gets caught and quarantined, not passed through to a table whose type nobody reviewed.

PythonLock a catalog table's schema; restrict the crawler to new columns only (needs boto3)
import boto3

glue = boto3.client("glue", region_name="ap-south-1")

# One-time: define the contract explicitly instead of trusting inference.
glue.create_table(
    DatabaseName="orders_raw",
    TableInput={
        "Name": "orders",
        "StorageDescriptor": {
            "Columns": [
                {"Name": "order_id", "Type": "bigint"},
                {"Name": "amount", "Type": "double"},
                {"Name": "discount_pct", "Type": "double"},
            ],
            "Location": "s3://etl-raw/orders/",
        },
        "PartitionKeys": [{"Name": "day", "Type": "string"}],
    },
)

# Re-point the crawler so it can only add new columns, never overwrite
# the type of a column that already exists.
glue.update_crawler(
    Name="orders-crawler",
    SchemaChangePolicy={"UpdateBehavior": "LOG", "DeleteBehavior": "LOG"},
    Configuration='{"Version":1.0,"CrawlerOutput":'
                  '{"Tables":{"AddOrUpdateBehavior":"MergeNewColumns"}}}',
)
Scenario B

Imagine you work at Flipkart

1,200 supplier feed prefixes crawled nightly

The numbers

Illustrative: 1,200 supplier feed prefixes, averaging 25 GB each, land roughly 30 TB in raw S3 every night. One crawler configured across all 1,200 prefixes samples files from each to infer schema; even at a modest 2 seconds of listing-and-sampling overhead per prefix, that is 1,200 × 2s = 2,400s (40 minutes) of crawler time before a single Spark job starts.

What the Scenario-A answer does here

The Scenario A fix—lock the schema, restrict the crawler to MergeNewColumns—still works per table, but at 1,200 tables nobody is reviewing 1,200 crawler change logs by hand every morning.

What flips at this scale

At this scale the flip is making schema-locking a deploy-time step for every new supplier table (columns defined in code, not clicked once in a console), and alerting on any crawler run that reports a nonzero TablesUpdated for a table that was supposed to be locked—that becomes a signal of a bug or an unreviewed change, not routine behavior.

The trade-off you'd defend

I would defend IaC-defined, locked schemas for every supplier table over "let the crawler figure it out and review changes," accepting the upfront cost of defining 1,200 schemas in code in exchange for catching exactly one failure mode automatically instead of relying on 1,200 manual reviews.

Illustrative arithmetic; not Flipkart-published figures.

Gotchas

Crawler overwrites a manually-fixed schema

Symptom

A column that cast correctly yesterday throws a type error in Athena or a Glue job today, with no code change on your side.

Cause

The crawler re-ran, re-sampled a different batch, inferred a different type, and the default UpdateBehavior lets it overwrite the existing column definition.

Fix

Set SchemaChangePolicy.UpdateBehavior to LOG and configure CrawlerOutput.Tables.AddOrUpdateBehavior to MergeNewColumns, or stop crawling tables that already have established consumers.

Job bookmark state is per-job, not global

Symptom

A backfill run (a cloned or renamed job) reprocesses files that were already loaded, duplicating rows downstream.

Cause

Bookmarks track what this specific job has seen, keyed by job identity; a new or renamed job starts with no history at all.

Fix

Reuse the original job for backfills instead of cloning it, or explicitly manage bookmark state via the API/console—and make the load idempotent regardless, so a reprocessed file can never corrupt the destination.

DynamicFrame silently nulls mismatched values

Symptom

A column that should always have a value shows unexpected nulls after a run, with no error raised.

Cause

DynamicFrame's flexible "choice" typing can resolve a type mismatch by picking one type and nulling out rows that don't fit, unless you explicitly resolve the choice.

Fix

Call resolveChoice (or an explicit filter) and route anything that doesn't match the expected type to a quarantine sink instead of letting it become a silent null.

Tricky questions

Does a crawler always correctly detect your table's "real" schema if you let it run?
Most people say

Yes—it samples the data, so it reflects what's really there.

Actually

It samples a subset of files and infers from that sample. A non-representative sample—one bad batch, a one-off format—can shift the inferred schema for the whole table. The crawler reflects what it saw, not necessarily what every row that will ever land in that prefix looks like.

Is a Glue job bookmark the same as the watermark you'd maintain in Postgres?
Most people say

Yes, it's the same incremental-processing idea, just AWS's version of it.

Actually

It is Glue's own job-scoped state, tracked by file path/timestamp or a JDBC bookmark key, entirely separate from any watermark your own code maintains. The two can disagree, especially after a backfill or a renamed job; owning the full watermark/CDC story end to end is Incremental extraction: watermarks and CDC's job, not the bookmark's.

Scenario question

You're handed a Glue table an analyst says started returning wrong revenue numbers this morning. The crawler ran at its usual 2 a.m. schedule. What do you check first, and how do you prevent a repeat?

Model answer

I would check the catalog table's schema history first—specifically whether a numeric column's type changed after last night's crawl—because a silent type change is the most common cause of "numbers went wrong with no code change." If that's it, I'd compare the current schema against what the downstream query expects, fix the immediate query or revert the type, and then stop the bleeding structurally: lock that table's schema with an explicit contract and restrict the crawler to adding columns only. The trade-off is that the table stops auto-adapting to upstream changes, so a genuinely new column needs someone to notice and add it deliberately—which is exactly the trade I want once a table has real consumers.

Interview corner

CommonWhat is the Glue Data Catalog and why does it matter for an ETL pipeline?
“I'd describe it as a shared, Hive-compatible metastore—tables, columns, partitions—that Athena, Spark, Redshift Spectrum and Glue jobs all read from. It matters because once it's wrong, every one of those engines is wrong in the same way, silently.”
TrickA crawler infers a column as string when it used to be double. Is the crawler broken?
“I'd say no, that's the crawler doing exactly what it's designed to do—infer from a sample. The real issue is leaving a crawler with write access to a table's schema once that table has consumers who assumed a stable contract. I'd lock the schema, not blame the crawler.”
Experience checkHow have you handled a schema that changed under you in an ETL job?
“In my Jenkins/pandas setup today, I've hardcoded column lists, which fails loudly—a KeyError—when a column disappears, but says nothing when a new one silently appears with a different type. Moving to a Glue job with a bookmark and an explicit schema contract gives me both: fail loudly on a real mismatch, and never silently accept a type change nobody reviewed.”
DesignHow would you add schema evolution support to a catalogued table without breaking existing Athena queries?
“I'd allow additive changes—new columns—automatically through the crawler's MergeNewColumns setting, but require any type change or column removal to go through an explicit review, the same discipline I'd apply to a breaking REST API change. If a change is genuinely incompatible, I'd version the curated path so old queries keep working against the old shape while new ones move to the new one.”

Memory hook

The catalog is the index card every engine trusts; a crawler that keeps rewriting it is a librarian who never asks before changing the card.

If you remember only one thing:

Lock a table's schema the moment it has consumers; let the crawler only add partitions or new columns from then on.

Self-check

  1. What is the practical difference between letting a crawler infer a schema and defining a schema contract?

    A crawler infers from a sample of files and can silently change column types or names on its next run; a schema contract is explicitly defined and does not change until someone deliberately updates it.

  2. Why can a backfill job under a new Glue job name reprocess data that was already loaded?

    Job bookmarks are tracked per job, based on job identity; a new or renamed job has no bookmark history, so it treats every file as new.

  3. What does DynamicFrame's resolveChoice step protect against?

    Rows whose field types don't match the expected schema being silently coerced to null instead of being explicitly handled or routed to quarantine.

Prove it

Extend the production example: add a fourth file where amount arrives as the string "not-a-number" instead of a float. Update run_job to catch that as a third quarantine reason (bad-value: non-numeric amount) using a try/except (TypeError, ValueError) around a numeric comparison, and print the final quarantine list showing all three distinct reasons.

aws-07-02Card 2 of 6

Athena, Parquet and partition economics

HardP3 · Role-dependentTalk about it12 min
athenaparquetpartitioningcompactioncost

Why you care

Athena bills per byte scanned and has no index of its own, so a query against a non-partitioned CSV table reads every byte of every file regardless of what the WHERE clause says. Partitioning plus a columnar format like Parquet is the only lever you have to make Athena cheap and fast, and it does nothing automatically—you have to lay the data out correctly and write queries that actually use the layout. Interviewers ask about this because it reveals whether you understand that for a serverless query engine with no traditional index, the data layout is the index.

Explain like I'm 5

Imagine a huge filing cabinet. CSV or JSON data is one giant, unsorted folder—finding "October invoices" means reading every single page in the entire cabinet. Partitioning is adding labeled drawers (year/month/day), so Athena only opens the "2024/10" drawer instead of the whole cabinet. Switching to Parquet on top of that is replacing loose paper with pre-sorted index cards listing only the fields you asked for, so even inside the opened drawer, you read just the "amount" cards, not the whole page per row.

Core concept

  • Athena has no native storage; performance and cost are determined almost entirely by how data is laid out in S3, not by anything Athena itself does to the query.
  • Billing (check the current pricing page) is per terabyte of data scanned per query. A bad layout means paying to scan bytes the query never needed to answer the question.
  • Partition pruning: directories like s3://bucket/orders/year=2024/month=10/day=15/, registered as partition keys in the Glue Data Catalog, tell Athena which prefixes to skip entirely for a query filtering on those keys. A table with partitions not registered in the catalog, or a query filtering on something other than the partition key, scans every partition.
  • Columnar format: Parquet and ORC store data column-by-column with embedded per-block statistics (min/max, row counts). Athena can skip columns the query doesn't select, and skip blocks whose min/max can't satisfy a filter. CSV and JSON are row-oriented—every column of every row has to be read even if the query selects one column.
  • Compression: Parquet files are typically compressed (snappy, gzip), cutting bytes scanned and transferred further, at some CPU cost on write.
  • Small-file problem: many small Parquet files per partition (one file per minute of streaming writes, say) multiply per-file open/listing overhead and lose the benefit of large, well-organized column chunks. Periodic compaction—merging small files into fewer, larger ones—fixes this.
  • Partition explosion: partitioning by a high-cardinality key (user ID) or too many nested levels creates millions of partitions, making catalog-side query planning the bottleneck before a byte is read. Partition projection lets Athena compute partition values from a pattern instead of listing and storing every one—useful once partition count gets large (check current docs for exact configuration).

Partition by the columns your queries actually filter on (usually a date), keep partition counts in the thousands, not millions, store curated data as Parquet, and treat the small-file problem as a standing item on every streaming-to-lake path, not an edge case you'll get to later.

Under the hood

Athena's planner consults the Glue Data Catalog's partition metadata before executing anything, to decide which S3 prefixes to list and scan. If a new partition's data has landed in S3 but was never registered—no crawler run, no ADD PARTITION, no MSCK REPAIR TABLE since—Athena's planner has no idea that prefix exists and simply returns no rows from it, with no error at all. Parquet's internal layout uses row groups with per-column statistics stored in a footer; the query engine reads that footer first to decide which row groups can be skipped for a given predicate, which is why a Parquet file sorted by the filtered column lets the engine skip row groups independent of partitioning. The number Athena reports as "data scanned" for a query is the actual billing driver and the one honest way to measure whether a layout change helped—measure it directly rather than trusting an estimated benchmark.

Minimal example Partition pruning plus column pruning cuts bytes scanned

PythonBytes scanned: unpartitioned CSV vs partitioned, column-pruned Parquetruns
PARTITIONS = {
    "2024-10-14": {"bytes": 48_000_000, "amount_fraction": 0.10},
    "2024-10-15": {"bytes": 55_000_000, "amount_fraction": 0.10},
}

# SELECT amount FROM orders WHERE day = '2024-10-15'
csv_full_scan = sum(p["bytes"] for p in PARTITIONS.values())
target = PARTITIONS["2024-10-15"]
parquet_pruned_scan = int(target["bytes"] * target["amount_fraction"])

print(f"CSV, unpartitioned:        {csv_full_scan:>11,} bytes scanned")
print(f"Parquet, partition+column: {parquet_pruned_scan:>11,} bytes scanned")
print(f"reduction: {csv_full_scan / parquet_pruned_scan:.1f}x fewer bytes scanned")
Output
CSV, unpartitioned:        103,000,000 bytes scanned
Parquet, partition+column:   5,500,000 bytes scanned
reduction: 18.7x fewer bytes scanned

In production Small-file overhead and partition-count explosion

PythonRequest overhead from small files; catalog cost of a bad partition keyruns
def plan_scan(num_files, total_bytes, request_overhead_bytes=8_000):
    """A rough model of what a partition scan costs structurally: every
    file Athena opens incurs a fixed S3 GET + footer-read overhead, on
    top of the actual data bytes it needs from that file."""
    return {
        "files": num_files,
        "data_bytes": total_bytes,
        "overhead_bytes": num_files * request_overhead_bytes,
        "s3_get_requests": num_files,
    }

PARTITION_DAY = "2024-10-15"
TOTAL_PARTITION_BYTES = 50_000_000

# Streaming writer flushes one small Parquet file every minute: 1,440 files/day
before = plan_scan(num_files=1_440, total_bytes=TOTAL_PARTITION_BYTES)
# Nightly compaction job merges those into 4 well-sized files
after = plan_scan(num_files=4, total_bytes=TOTAL_PARTITION_BYTES)

for label, plan in [("before compaction", before), ("after compaction", after)]:
    print(f"{label:<18} files={plan['files']:>5}  "
          f"s3_get_requests={plan['s3_get_requests']:>5}  "
          f"overhead_bytes={plan['overhead_bytes']:>10,}")

print(f"S3 GET requests cut by {before['s3_get_requests'] / after['s3_get_requests']:.0f}x; "
      f"data bytes scanned for this partition is unchanged at {TOTAL_PARTITION_BYTES:,} "
      f"because the query still reads the same {PARTITION_DAY} partition either way.")

# A high-cardinality partition key makes the catalog itself the bottleneck.
partitions_by_day = 365
partitions_by_user = 2_000_000
print(f"partitions by day:  {partitions_by_day:,}")
print(f"partitions by user: {partitions_by_user:,}  "
      f"({partitions_by_user // partitions_by_day:,}x more catalog entries to plan against)")
Output
before compaction  files= 1440  s3_get_requests= 1440  overhead_bytes=11,520,000
after compaction   files=    4  s3_get_requests=    4  overhead_bytes=    32,000
S3 GET requests cut by 360x; data bytes scanned for this partition is unchanged at 50,000,000 because the query still reads the same 2024-10-15 partition either way.
partitions by day:  365
partitions by user: 2,000,000  (5,479x more catalog entries to plan against)
Scenario A

In your world

The situation

Analysts run a daily Athena query against orders_raw, a table pointing at CSV files your Jenkins job has been landing for 18 months in one flat, unpartitioned S3 prefix—around 540 GB of history.

What actually breaks

A simple WHERE order_date = CURRENT_DATE - 1 query scans the full 540 GB every run, because there is no partition key to prune against and CSV has no column-level skip. At roughly a few dollars per terabyte scanned (check current pricing before quoting a figure to anyone), that single query costs real money per run; with a dozen analysts running similar queries several times a day, the monthly Athena bill climbs steadily and nobody connects it to one unpartitioned table.

What you'd do on Monday

Partition the curated copy by day (register day=YYYY-MM-DD partitions) and convert it to Parquet via a Glue Spark job. The same query now prunes to one day's partition and reads only the columns it selects, instead of every column of every row ever landed.

SQL
ALTER TABLE orders_curated
ADD PARTITION (day='2024-10-15')
LOCATION 's3://etl-curated/orders/day=2024-10-15/';
Scenario B

Imagine you work at MakeMyTrip

2 TB of search/booking logs landing daily

The numbers

Illustrative: 2 TB/day of search and booking events land as JSON, queried by around 40 analysts via Athena for date-filtered questions like "yesterday's conversion by route."

What the Scenario-A answer does here

The Scenario A fix—partition by day, convert to Parquet—handles this pattern well, cutting scanned bytes for a single-column, single-day query by roughly the same multiple as the minimal example, scaled up.

What flips at this scale

At this volume the flip is writing Parquet directly at ingestion time rather than landing JSON and converting it in a nightly batch, because a daily "convert yesterday's 2 TB" job becomes its own large Spark job competing for the same compute budget as everything else. You also start compacting small files from day one, since a day's worth of streaming writes can produce thousands of tiny files before any later cleanup job gets to them.

The trade-off you'd defend

I would defend writing Parquet at ingestion (via a streaming job with a sensibly sized buffer) over "land JSON, convert nightly," accepting more complexity in the ingestion path in exchange for a query layer that is fast and cheap from the first hour of the day, not just after the nightly batch catches up.

Illustrative figures; not MakeMyTrip-published numbers.

Gotchas

Query filters on a non-partition column

Symptom

Athena's reported data scanned stays huge even after partitioning the table.

Cause

Partition pruning only works when the WHERE clause filters on the actual partition key; filtering on something else—say customer_id on a table partitioned by day—forces a full scan across every partition. Broad scans or ineffective partition pruning increase work without improving the answer.

Fix

Make sure hot query patterns filter on the partition key, or add a matching dimension. Verify by checking the query's actual data-scanned figure, not by assuming partitioning "fixed" it.

New partitions never registered

Symptom

A query for "yesterday" returns zero rows even though the file is sitting at the expected S3 path.

Cause

New partitions written to S3 are not automatically visible to Athena until registered in the catalog—by a crawler run, MSCK REPAIR TABLE, or an explicit ADD PARTITION.

Fix

Add partition registration as an explicit pipeline step immediately after writing new partition data, rather than depending on the next scheduled crawler run.

Tiny streaming files erode the Parquet benefit

Symptom

A partition scan is slower, and the per-query S3 request count is unexpectedly high even though total bytes scanned looks reasonable.

Cause

Many small files per partition multiply per-file open and footer-read overhead.

Fix

Run a periodic compaction job that merges small files in each partition into a handful of larger ones.

Tricky questions

Does converting a table to Parquet guarantee lower Athena cost regardless of the query?
Most people say

Yes, Parquet is always cheaper because it's columnar.

Actually

Parquet only helps the columns and row groups a query actually needs. A SELECT * with no filters on a Parquet table can scan close to the same bytes as the CSV equivalent—the saving comes from pruning being possible, not automatic.

Is more partitions always better for query performance?
Most people say

Yes, finer partitioning always lets Athena skip more data.

Actually

Partitioning by a high-cardinality key creates so many small partitions that catalog-side planning becomes the bottleneck before a single byte is read. Partition count has a practical ceiling, and partition projection exists specifically to avoid storing and listing millions of them.

Scenario question

You're handed an Athena table backed by daily-arriving JSON files, partitioned by day, feeding a weekly report that joins it against a dimension table. The report has gotten slower every month for the past year, with no change to the query itself. What do you check, and what do you fix?

Model answer

I would first pull the query's actual data-scanned number over the last few runs and compare it against partition count and average partition size over the same period—if both have been growing steadily with no cleanup, I have my answer: a year of accumulating, uncompacted JSON partitions with growing per-partition file counts. I'd convert the table to partitioned Parquet and add a periodic compaction job for the small-file problem. The trade-off is that compaction itself is a recurring job I now have to run and monitor, but it is far cheaper than every weekly report scanning a steadily growing pile of row-oriented, uncompacted files.

Interview corner

CommonWhy does partitioning matter for Athena cost and performance?
“I'd say because Athena bills per byte scanned and has no index of its own—partitioning is effectively the only index you get, letting it skip whole prefixes of S3 that can't match a query's filter.”
TrickIf I convert my CSV table to Parquet, will all my existing queries get cheaper automatically?
“I'd say only the ones that already filter on columns or the partition key benefit. A SELECT * with no WHERE clause reads close to the same bytes either way—Parquet makes pruning possible, it doesn't force it to happen.”
Experience checkTell me about a time query performance degraded slowly over months.
“I haven't hit this on Athena specifically yet, but I've seen the Postgres equivalent—an unindexed table growing until a formerly-instant query started timing out. The fix pattern is the same: find what changed in data volume, not in the query, and add the structural fix—index there, partition and compact here.”
DesignHow would you design the layout for a table queried both for 'last 24 hours' dashboards and occasional 'last 2 years' audits?
“I'd partition by day regardless, since that serves both patterns—the dashboard prunes to one partition, the audit scans many but still only the ones in range. I'd keep recent partitions compacted aggressively and accept that the rare audit query can afford to be slower than the dashboard query run constantly.”

Memory hook

Partitioning and Parquet are the only index Athena has; skip them and every query reads the whole shelf.

If you remember only one thing:

A query only gets cheap if it filters on the partition key and needs only a few columns—verify with the actual bytes-scanned number, not a guess.

Self-check

  1. Why doesn't converting a table to Parquet automatically make every query cheaper?

    Parquet only enables column and row-group pruning for queries that actually filter on relevant predicates; a query with no filters still reads effectively everything.

  2. What causes partition pruning to fail even on a partitioned table?

    The query filters on a column other than the partition key, so Athena has no partition-level signal to skip prefixes.

  3. Why is a very high-cardinality partition key a problem even though finer-grained pruning sounds like it should help?

    It creates so many catalog partition entries that query planning—matching partitions before any data is read—becomes the bottleneck.

Prove it

Extend the minimal example's PARTITIONS dict to 30 days of entries. Write a function that computes total bytes scanned for a query with no WHERE clause versus one filtered to a single day, print both totals and the ratio. Then add a second col_fraction entry for a different column and show the scanned bytes for a query selecting two columns instead of one.

aws-07-03Card 3 of 6

Kinesis versus Firehose and queues

HardP3 · Role-dependentTalk about it13 min
kinesisfirehosestreamingreplayordering

Why you care

ETL developers default to "a queue" for everything, but Kinesis Data Streams and Amazon Data Firehose solve different problems than SQS and than each other. Streams give you an ordered, replayable log that multiple independent consumers can each read at their own pace; Firehose is a managed, buffered pipe from a source to one fixed destination, with no replay and no custom consumer logic. Reaching for Firehose when you actually need replay, or hand-building a Streams consumer when Firehose would do the whole job, is a design smell interviewers listen for.

Explain like I'm 5

A queue (SQS) is a ticket counter: one ticket, one person serves it, then it's gone. Kinesis Data Streams is a security camera's recorded tape split into labeled reels (shards): several people can rewind to any point on their own reel and watch independently, and the tape keeps recording for a retention window even if nobody is watching right now. Amazon Data Firehose is a conveyor belt straight from the camera into a storage room: it batches up a few minutes of footage into neat boxes and drops them off—no rewinding, no "give me yesterday's footage again."

Core concept

  • SQS is point-to-point (or fan-out via SNS): a message is gone once a consumer deletes it, with no native replay of already-deleted messages.
  • Kinesis Data Streams is an ordered, append-only log split into shards. Each shard retains records for a configurable window—24 hours by default, extendable up to 8,760 hours (365 days). Multiple consumers can each independently track their own position (a checkpoint derived from a shard iterator) and re-read from any retained point. This is what you reach for when you need replay, or when several independent teams need the same ordered event history.
  • Ordering: preserved within a shard, not across shards. A partition key (order ID, say) determines which shard a record lands on; all records sharing a key land on the same shard, in order relative to each other.
  • Amazon Data Firehose (renamed from Kinesis Data Firehose in February 2024—same service, new name across console and docs) is a managed delivery pipe: it buffers incoming records by size or time (whichever threshold is hit first) and writes a batch to a fixed destination—S3, Redshift, OpenSearch, Splunk or an HTTP endpoint—optionally transforming records via Lambda first. There is no consumer reading at its own pace, no replay, no "give me position X."
  • Firehose can read directly from a Kinesis Data Stream as its source—the common pairing: Streams as the durable, replayable, multi-consumer log; Firehose as one of several consumers that reliably lands a copy in S3 or Redshift without a hand-written consumer app.

Use SQS for point-to-point task distribution where one worker should process one message. Use Kinesis Data Streams when you need ordered replay and/or multiple independent consumer applications over the same event stream. Use Firehose when you need a reliable, mostly code-free pipe from a stream (or compatible source) into storage or analytics, and replay or custom per-record consumer logic isn't a requirement.

Under the hood

Kinesis Data Streams consumers using the standard GetRecords API (not "enhanced fan-out") share each shard's fixed read throughput across every consumer polling that shard, which is why a slow or buggy consumer can starve a fast one on the same shard; high-fan-out designs add enhanced fan-out (a dedicated throughput allocation per consumer per shard) or put a single consumer—often Firehose or a Lambda—directly on the stream and fan out further downstream instead of attaching many independent Streams consumer apps. Firehose's buffering is a deliberate trade of latency for batch efficiency: it will not deliver a record until its buffer hits either the configured size or time threshold, so a buffer configured for a multi-minute window guarantees records can sit that long before landing in the destination, even under light traffic—if a downstream job assumes near-real-time delivery, that assumption breaks silently, not with an error.

Minimal example Partition key determines shard; order holds only within a shard

PythonPartition-key routing: order preserved per key, not across keysruns
def shard_for_key(key, num_shards):
    digest = sum(ord(c) * (i + 1) for i, c in enumerate(key))
    return digest % num_shards

EVENTS = [("order-1", "CREATED"), ("order-1", "PAID"),
          ("order-2", "CREATED"), ("order-1", "SHIPPED")]
target_shard = shard_for_key("order-1", 3)
same_shard = [(k, s) for k, s in EVENTS if shard_for_key(k, 3) == target_shard]

print(f"shard {target_shard} (order-1's shard):", same_shard)
print("replay from checkpoint 1:", same_shard[1:])
Output
shard 0 (order-1's shard): [('order-1', 'CREATED'), ('order-1', 'PAID'), ('order-1', 'SHIPPED')]
replay from checkpoint 1: [('order-1', 'PAID'), ('order-1', 'SHIPPED')]

In production Firehose buffer flush timing vs a replayable stream log

PythonFirehose time-threshold flush vs independent-checkpoint stream replayruns
class FirehoseBuffer:
    def __init__(self, size_threshold, time_threshold):
        self.size_threshold, self.time_threshold = size_threshold, time_threshold
        self.buf, self.start, self.deliveries = [], None, []

    def put(self, record_bytes, now):
        self.start = self.start if self.start is not None else now
        self.buf.append(record_bytes)
        if sum(self.buf) >= self.size_threshold or now - self.start >= self.time_threshold:
            self.deliveries.append((now, len(self.buf), sum(self.buf)))
            self.buf, self.start = [], None

fh = FirehoseBuffer(size_threshold=5_000_000, time_threshold=60)
for t in range(0, 181, 30):
    fh.put(record_bytes=2_000, now=t)
for at, n, total_bytes in fh.deliveries:
    print(f"Firehose delivery at t={at}s: {n} records, {total_bytes:,} bytes (time-threshold flush)")

# A stream never deletes on read: each consumer tracks its own checkpoint.
stream = ["evt-1", "evt-2", "evt-3", "evt-4"]
print("consumer A from checkpoint 0:", stream[0:])
print("consumer B from checkpoint 2:", stream[2:])
print("consumer A replays from checkpoint 1 after a bug fix:", stream[1:])
Output
Firehose delivery at t=60s: 3 records, 6,000 bytes (time-threshold flush)
Firehose delivery at t=150s: 3 records, 6,000 bytes (time-threshold flush)
consumer A from checkpoint 0: ['evt-1', 'evt-2', 'evt-3', 'evt-4']
consumer B from checkpoint 2: ['evt-3', 'evt-4']
consumer A replays from checkpoint 1 after a bug fix: ['evt-2', 'evt-3', 'evt-4']

See it happen Independent consumers on one stream vs a queue

PutRecordGetRecords (own checkpoint)GetRecords (own checkpoint)GetRecordsbuffered batchProducerKinesisData Streamreplaying from earlier checkpointDashboard(checkpoint B)FirehoseS3
  1. Producer writes once to the stream; nothing downstream is addressed directly.
  2. Loader reads from its own checkpoint and loads Postgres independently.
  3. Dashboard reads the same stream from a different checkpoint, unaffected by the loader's pace.
  4. Firehose attaches as a third, ordinary consumer and buffers before writing to S3.
  5. A bug is found in the loader; it rewinds its own checkpoint and replays without touching the dashboard or Firehose.
Notice: the stream never deletes a record on read, so each consumer's position is independent; replay is just rewinding one consumer's own checkpoint, something no consumer could ever do if this were an SQS queue instead.
Scenario A

In your world

The situation

Your Jenkins job currently polls an SQS queue of clickstream events once a minute via a cron-triggered script and loads batches into Postgres. Product wants near-real-time analytics on the same events via a live dashboard, and the data team wants to replay the last hour whenever they ship a buggy aggregation.

What actually breaks

SQS deletes each message once your worker processes it, so when the data team's bug produces wrong aggregates and they ask to "just rerun last hour," there is nothing to rerun from—the messages are gone. You end up asking the producer to resend events, which it cannot do either, because it never stored them.

What you'd do on Monday

Replace the SQS hand-off with a Kinesis Data Stream as the durable, replayable log the producer writes to once. Your Postgres loader becomes one consumer tracking its own checkpoint; the dashboard team becomes a second, independent consumer. For "replay the last hour," any consumer just re-reads from a checkpoint behind, bounded by the stream's retention window.

PythonReplay the last hour using a Kinesis shard iterator (needs boto3)
import boto3
from datetime import datetime, timedelta

kinesis = boto3.client("kinesis", region_name="ap-south-1")
one_hour_ago = datetime.utcnow() - timedelta(hours=1)

iterator = kinesis.get_shard_iterator(
    StreamName="clickstream-events",
    ShardId="shardId-000000000000",
    ShardIteratorType="AT_TIMESTAMP",
    Timestamp=one_hour_ago,
)["ShardIterator"]

response = kinesis.get_records(ShardIterator=iterator, Limit=1000)
for record in response["Records"]:
    pass  # reprocess with the fixed aggregation logic
Scenario B

Imagine you work at Blinkit

50,000 click events per minute

The numbers

Illustrative: 50,000 events/min ≈ 833/s. A single Kinesis shard caps at roughly 1,000 records/s or about 1 MB/s of writes (check the current per-shard quota)—833 small events/s fits comfortably by record count, but a single hot partition key (one viral SKU driving a disproportionate share of events) can still overload one shard even while others sit idle.

What the Scenario-A answer does here

The Scenario A fix—a Data Stream with independent consumers—handles replay correctly, but a skewed partition key means growth doesn't distribute evenly just because total throughput looks fine on average.

What flips at this scale

At this scale the flip is choosing a partition key that spreads load evenly—not something with few distinct values like page_type, but a higher-cardinality key like a hashed session ID—and provisioning shards for peak, not average, throughput. Firehose, as one of the stream's consumers, then handles "just land it in S3 for batch analytics" instead of a hand-written consumer built for that purpose.

The trade-off you'd defend

I would defend a Kinesis Data Stream with Firehose as a downstream consumer over wiring Firehose directly to the clickstream source, accepting the extra moving part in exchange for replay and multiple independent consumers—dashboard, loader, a future fraud-detection consumer—that Firehose alone cannot give.

Illustrative event volume; not Blinkit-published figures.

Gotchas

Hot shard throttling

Symptom

ProvisionedThroughputExceededException on writes for one specific key, while overall stream throughput looks healthy.

Cause

A low-cardinality or skewed partition key sends a disproportionate share of records to one shard, which has its own fixed limit regardless of how idle other shards are.

Fix

Choose a higher-cardinality, more evenly distributed partition key, or add a bounded random suffix for keys that don't need strict ordering against each other.

Firehose "it will arrive eventually" mistaken for "it arrived now"

Symptom

A downstream job reading from the S3 destination finds no new file yet, even though the source sent data minutes ago.

Cause

Firehose buffers before delivering; the configured size/time threshold—whichever is hit first—delays delivery.

Fix

Size the buffer window to match the downstream job's actual freshness requirement, and never assume delivery is near-instant without checking the configured buffering settings.

SQS reused where replay was the actual requirement

Symptom

A "redo the last hour" request can't be fulfilled because the messages were deleted on first successful consumption.

Cause

SQS has no replay of already-deleted messages—it was the wrong primitive for a requirement that needed a durable, re-readable log.

Fix

Move the event source to a Kinesis Data Stream when replay is a real requirement, not an afterthought; an SQS DLQ redrive only covers messages that failed, not ones already processed successfully.

Tricky questions

Is Amazon Data Firehose just a managed SQS consumer that writes to S3?
Most people say

Yes, it consumes a queue and writes batches to S3.

Actually

Firehose is a delivery pipe, not a queue consumer—its source is typically a Kinesis Data Stream, direct PutRecord calls, or specific AWS service integrations, and its job is strictly moving data from that source into a fixed destination with buffering and optional transformation. It has no concept of a backlog a second independent consumer could read differently.

If I need ordering, does Kinesis guarantee my events arrive at the consumer in the order they happened?
Most people say

Yes, Kinesis Data Streams is fully ordered.

Actually

Order is only guaranteed within a single shard for records sharing a partition key. Across shards—and so, in general, across different keys—there is no ordering guarantee at all.

Scenario question

You're handed a stream of payment events currently flowing through SQS. The fraud team wants to attach a new, independent consumer that scores every event, without touching the existing loader's code or its queue. How do you add this without a redesign of the existing pipeline?

Model answer

I would first ask whether the fraud team needs replay—for backtesting a model against historical events—because that decides the whole approach. If they don't, an SNS topic in front with the existing loader and a new SQS subscription for the fraud consumer is the smallest change. If they do need replay, which is the common case for model development, I would migrate the source to a Kinesis Data Stream with the existing loader as one checkpointed consumer and the fraud scorer as a second, independent one. The trade-off is operational surface: SNS fan-out is simpler to run, but a Kinesis migration is what actually satisfies "backtest against last month's events," which a queue-based design cannot do once messages are gone.

Interview corner

CommonWhen would you choose Kinesis Data Streams over SQS?
“I'd choose Streams when I need either replay—reprocessing a window of events after a bug—or more than one independent consumer reading the same event history at its own pace. SQS is right when it's genuinely one producer handing off one unit of work to one consumer.”
TrickDoes Amazon Data Firehose give me the same multi-consumer flexibility as Kinesis Data Streams?
“No—Firehose is a one-way pipe to a fixed destination, not a log multiple consumers can read independently. If I need Streams' flexibility plus a hands-off way to also land a copy in S3, I'd put Firehose downstream of a Data Stream, not use Firehose as the only layer.”
Experience checkHave you dealt with ordering guarantees in a distributed system?
“Only informally—making sure a worker processes SQS messages for the same order sequentially by locking on order ID in Postgres. Kinesis gives me that per-shard ordering natively if I pick the partition key correctly, which is less code than hand-rolling a lock.”
DesignHow would you design partition keys for a clickstream with a few extremely popular pages?
“I'd avoid keying directly on page ID, since a few hot pages would overload a few shards while others sit idle. I'd use a higher-cardinality key like a hashed session ID, and if I truly needed per-page ordering for some pages, I'd add a bounded random suffix to spread a hot page's events across multiple shards while accepting weaker ordering within that page.”

Memory hook

A queue is a ticket counter; a stream is a rewindable tape; Firehose is the conveyor belt that only moves forward.

If you remember only one thing:

Reach for Kinesis Data Streams the moment you need replay or more than one independent consumer; reach for Firehose only when you need a hands-off pipe to a fixed destination.

Self-check

  1. What does "ordering" actually mean in a Kinesis Data Stream?

    Order is preserved only within a single shard, for records sharing a partition key; there's no ordering guarantee across shards.

  2. Why can't you ask SQS to replay a message you already deleted?

    SQS has no durable log of delivered-and-deleted messages; deletion removes it from the queue permanently, with no retained history to replay from.

  3. What is the one thing Amazon Data Firehose cannot give you that Kinesis Data Streams can?

    Multiple independent consumers reading the same event history at their own pace, including replay from an earlier point.

Prove it

Extend the minimal shard-routing example to 20 distinct order keys across 3 shards. Print the record count per shard and confirm no shard holds more than 50% of total records with your chosen hash function. If one does, change the hash input (for example, hash a generated UUID instead of the order key directly) and show the improved distribution.

aws-07-04Card 4 of 6

Redshift Serverless and lakehouse boundaries

HardP3 · Role-dependentTalk about it13 min
redshift-serverlesslakehousespectrumrpucost-controls

Why you care

Product-company interviews increasingly ask "would you put this in a warehouse or query it in place from the lake" instead of "write me a JOIN," because the wrong choice either balloons warehouse cost loading data nobody repeatedly queries, or kills dashboard latency by querying the lake for a pattern that needs sub-second response. Redshift Serverless removes the "pick a cluster size" decision but still needs you to reason about workload shape—steady versus spiky—and about when querying the lake in place beats loading everything in.

Explain like I'm 5

A warehouse (Redshift) is your personal kitchen: ingredients already chopped, labeled and stored in matched containers—fast, structured, but costs rent to keep stocked. The data lake (S3 queried via Athena or Redshift's own lake integration) is a big shared pantry with raw sacks of ingredients—cheap to keep enormous amounts, slower to prep from each time. Redshift Serverless is a kitchen you don't rent in advance: you pay only while actually cooking, and the stove size scales automatically. But leave the stove running at dinner-party scale all day, and serverless billing stops being the cheap option.

Core concept

  • Redshift Serverless removes capacity planning: instead of choosing node types/counts, you set a base and max RPU (Redshift Processing Unit) range, and compute scales within that range automatically, billed per RPU-hour actually used rather than for an always-on provisioned cluster.
  • Workgroups hold the compute configuration (RPU range, network and security settings); namespaces hold the data (databases, schemas, snapshots, permissions). One namespace can be reached by more than one workgroup.
  • Redshift's native S3/lake integration (Spectrum-style external tables registered in the Glue Data Catalog) lets Redshift query data directly in S3 without loading it in—the lakehouse seam: you can JOIN a Redshift-native fact table against a huge, rarely-queried external dimension or history table without paying to store and load that history in Redshift's own managed storage.
  • Decision: load natively if data is queried frequently, needs the lowest latency, or benefits from Redshift's own storage/sort/distribution optimizations. Query in place if data is large, infrequently queried, or needs to stay a single source of truth shared with other engines (Athena, EMR, Glue) without duplicating storage and cost.
  • Serverless vs provisioned: Serverless suits unpredictable or intermittent workloads—ad hoc analytics, dev/test, spiky BI—that would otherwise leave a provisioned cluster idle most of the day. A genuinely steady, 24/7, high-throughput workload can be cheaper on a correctly sized provisioned (or Reserved Instance) cluster, because serverless's flexibility is priced in.
  • Usage limits: you configure RPU-hour budgets per period with an action—log, alert, or turn off further compute—so a runaway query or a dashboard refresh storm can't surprise-bill you. This is the serverless equivalent of a budget alarm.

Default new, unpredictable or dev workloads to Serverless. Move a workload to provisioned once its usage pattern becomes steady and a correctly sized provisioned cluster is proven cheaper over a month of actual billing, not a guess.

Under the hood

Redshift Serverless's "scale within seconds" claim rests on a pool of pre-warmed compute it keeps attached to your workgroup; the base RPU you configure is the floor it keeps ready, and burst above that floor still has to provision additional compute, which is why a sudden, very large query spike on a workgroup with a low base RPU can show higher latency on the first burst than a sustained high-RPU workload would—base RPU is a pre-warming knob, not purely a cost cap. External/lake-queried tables push predicate and partition pruning down to the S3 scan the same way Athena does, sharing the same underlying Glue Catalog partition metadata, but every row that survives pruning still has to cross the network from S3 into Redshift's compute before it can join against native tables—this is why a lake-side JOIN against a huge, unpartitioned external table can be dramatically slower than the equivalent join against a Redshift-native table, even though both "work."

Minimal example Spiky workload utilization vs an always-on cluster

PythonUtilization: spiky dashboard load vs a 24/7 provisioned clusterruns
HOURS_IN_DAY = 24
ACTIVE_HOURS = [h for h in range(9, 19) if h % 3 != 0]   # bursty, not continuous
QUERY_SECONDS_PER_ACTIVE_HOUR = 240                       # 4 minutes of real compute

active_compute_seconds = len(ACTIVE_HOURS) * QUERY_SECONDS_PER_ACTIVE_HOUR
total_seconds_in_day = HOURS_IN_DAY * 3600
utilization_if_always_on = active_compute_seconds / total_seconds_in_day * 100

print(f"active hours: {len(ACTIVE_HOURS)} of {HOURS_IN_DAY}")
print(f"actual compute: {active_compute_seconds:,}s "
      f"({active_compute_seconds / 60:.0f} minutes) out of {total_seconds_in_day:,}s in the day")
print(f"a 24/7 provisioned cluster sized for this workload runs idle "
      f"{100 - utilization_if_always_on:.1f}% of the time")
print(f"Serverless bills roughly for the {active_compute_seconds:,}s actually used "
      f"(check current RPU-second pricing), not for the idle {total_seconds_in_day - active_compute_seconds:,}s")
Output
active hours: 6 of 24
actual compute: 1,440s (24 minutes) out of 86,400s in the day
a 24/7 provisioned cluster sized for this workload runs idle 98.3% of the time
Serverless bills roughly for the 1,440s actually used (check current RPU-second pricing), not for the idle 84,960s

In production Load-vs-query-in-place placement, and an RPU usage guard

PythonPlacement heuristic across tables; RPU budget guard stops a runaway queryruns
TABLES = [
    {"name": "orders_fact",        "size_gb": 40,   "queries_per_day": 5_000},
    {"name": "customers_dim",      "size_gb": 2,    "queries_per_day": 5_000},
    {"name": "clickstream_raw",    "size_gb": 8_000, "queries_per_day": 3},
    {"name": "returns_history_5y", "size_gb": 1_200, "queries_per_day": 12},
]

def placement(table, hot_queries_per_day=50, hot_size_gb=500):
    """Toy heuristic: frequently-queried, modestly sized data earns a
    Redshift-native load; large, rarely-queried data stays queried in
    place against the Glue Catalog."""
    frequent = table["queries_per_day"] >= hot_queries_per_day
    small_enough = table["size_gb"] <= hot_size_gb
    if frequent and small_enough:
        return "load_to_redshift"
    if frequent and not small_enough:
        return "load_hot_slice_only"
    return "query_in_place_spectrum"

for t in TABLES:
    print(f"{t['name']:<18} {t['size_gb']:>6} GB  "
          f"{t['queries_per_day']:>6}/day -> {placement(t)}")


class RPUBudgetGuard:
    """Serverless usage limit: log, alert, or stop further compute once
    the configured RPU-hour budget for the period is exceeded."""
    def __init__(self, budget_rpu_hours, action="stop"):
        self.budget, self.action, self.used = budget_rpu_hours, action, 0.0

    def record_query(self, rpu, duration_hours):
        if self.used >= self.budget and self.action == "stop":
            raise RuntimeError(
                f"RPU budget exhausted: {self.used:.1f}/{self.budget:.1f} RPU-hours; "
                f"further compute is blocked until the next period"
            )
        self.used += rpu * duration_hours


guard = RPUBudgetGuard(budget_rpu_hours=10.0, action="stop")
for i, (rpu, hrs) in enumerate([(32, 0.05), (64, 0.08), (128, 0.03), (256, 0.02)], 1):
    try:
        guard.record_query(rpu, hrs)
        print(f"query {i}: rpu={rpu} duration_h={hrs} -> used={guard.used:.2f}/{guard.budget:.1f} RPU-hours")
    except RuntimeError as e:
        print(f"query {i}: RuntimeError: {e}")
Output
orders_fact            40 GB    5000/day -> load_to_redshift
customers_dim           2 GB    5000/day -> load_to_redshift
clickstream_raw      8000 GB       3/day -> query_in_place_spectrum
returns_history_5y   1200 GB      12/day -> query_in_place_spectrum
query 1: rpu=32 duration_h=0.05 -> used=1.60/10.0 RPU-hours
query 2: rpu=64 duration_h=0.08 -> used=6.72/10.0 RPU-hours
query 3: rpu=128 duration_h=0.03 -> used=10.56/10.0 RPU-hours
query 4: RuntimeError: RPU budget exhausted: 10.6/10.0 RPU-hours; further compute is blocked until the next period
Scenario A

In your world

The situation

Your team's nightly Jenkins job loads a returns table—5 years of history, 400 GB—in full into a small, always-on provisioned Redshift cluster so BI dashboards can query it, even though 95% of dashboard queries only ever look at the last 90 days.

What actually breaks

The cluster is sized for peak month-end BI load, so most of the day it idles under 10% CPU while still being billed for every hour. Storage keeps growing because nobody prunes the 5-year table, and a single quarter's board-reporting query against the full history occasionally pushes the small cluster into disk-spill territory, slowing every other dashboard on the same cluster for the 20 minutes it runs.

What you'd do on Monday

Split the data by access pattern. Keep the hot 90-day slice loaded natively in a Redshift Serverless workgroup—serverless because BI load is spiky, not steady—and move the 5-year history to Parquet in S3, queried through Redshift's native lake integration against the Glue Catalog only when the rare full-history query actually runs.

SQL
CREATE EXTERNAL SCHEMA returns_lake
FROM DATA CATALOG
DATABASE 'returns_history'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift-spectrum-role';

-- Hot slice stays native; cold history is queried in place
SELECT r.*
FROM returns r                         -- native, last 90 days
UNION ALL
SELECT h.*
FROM returns_lake.returns_history h    -- external, Parquet in S3
WHERE h.return_date < CURRENT_DATE - INTERVAL '90 days';
Scenario B

Imagine you work at Policybazaar

300 GB hot quotes, 4 TB cold quote history

The numbers

Illustrative: 300 GB of actively-queried current-quarter quotes, plus 4 TB of historical quote data rarely touched after 90 days. Loading all 4.3 TB natively means paying for storage and compute sized to data nobody queries 95% of the time.

What the Scenario-A answer does here

The Scenario A hot/cold split works, but at 4 TB of cold history, every external query still has to cross the network from S3 into Redshift compute after pruning, so a careless SELECT * with a weak filter on the external side can be dramatically slower than the equivalent native query, even after pruning.

What flips at this scale

At this scale the flip is treating the cold, lake-queried history with the same partitioning discipline as an Athena table—partition by date and region so lake-side scans prune aggressively—rather than assuming "it's in S3 now, Redshift will handle it."

The trade-off you'd defend

I would defend the hot/cold split with disciplined partitioning on the cold side over keeping everything native "to be safe," accepting that the rare full-history query gets slower in exchange for most queries, most of the time, running cheaper and faster against a small native table.

Illustrative figures; not Policybazaar-published numbers.

Gotchas

Serverless billed even near idle

Symptom

A Redshift Serverless workgroup shows nonzero cost on days with near-zero dashboard usage.

Cause

Depending on configuration, a workgroup can stay warm at its base RPU rather than scaling fully to zero; check current documentation for exactly when compute counts as idle versus billed.

Fix

Set usage limits with a defined action, and review actual RPU-hour billing weekly against expected usage rather than assuming "serverless" means zero cost when unused.

Lake-side query without partition filters scans everything

Symptom

A query against the external history table takes far longer and transfers far more data than expected—mirroring the Athena "forgot to filter on the partition key" failure.

Cause

The external table honours the same catalog partition metadata Athena uses; a query that doesn't filter on the partition key gets no pruning benefit.

Fix

Filter explicitly on the partition key, and verify via Redshift's own query plan which partitions were actually scanned.

Mixing hot and cold data in one query without checking row counts

Symptom

A UNION ALL dashboard query that was fast for months suddenly times out.

Cause

The cold side's row count grew, or a filter was dropped during a dashboard edit, silently turning a small external scan into a full one.

Fix

Monitor the external table's scanned-bytes metric per dashboard query, the same discipline as watching Athena's bytes scanned.

Tricky questions

Does "Serverless" mean Redshift automatically picks the cheapest option for any workload?
Most people say

Yes, serverless always costs less than a provisioned cluster.

Actually

Serverless trades a flexibility premium for not having to size and manage a cluster. A steady, 24/7, high-throughput workload can be cheaper on a correctly sized provisioned cluster or Reserved Instance pricing; serverless wins for spiky, unpredictable, or dev/test workloads where a provisioned cluster would otherwise sit mostly idle.

If data lives in S3 and is registered in the Glue Catalog, is querying it from Redshift the same experience as querying a native table?
Most people say

Yes, once it's "in the lakehouse" it behaves the same either way.

Actually

An external query still crosses the network from S3 into Redshift compute for every row that survives pruning, and doesn't benefit from Redshift's own storage optimizations—sort keys, distribution styles, compression tuned to the native engine. It's a deliberate trade of some performance for not duplicating storage and cost.

Scenario question

You're handed a Redshift Serverless workgroup with no usage limits configured. A new analyst's dashboard query, written without a date filter, scans the full 5-year history table during a demo for leadership. How do you prevent this from becoming an expensive habit without blocking legitimate full-history analysis?

Model answer

I would set a usage limit with an alert action first, not a hard stop, so I get visibility into how often this actually happens before deciding whether to block it outright. In parallel, I'd add a lightweight query-review habit—a dashboard linter or a simple reminder in the BI tool—that flags queries against the history table with no date filter before they run. The trade-off is that an alert-only limit doesn't prevent the next accidental full scan immediately, but a hard stop risks blocking a legitimate quarterly audit query at the worst possible moment; I'd only move to a hard stop once I have evidence of repeated, careless usage rather than one incident.

Interview corner

CommonWhat's the difference between loading data into Redshift and querying it in place via the lake integration?
“I'd say loading puts data into Redshift's own managed storage, with optimizations like sort keys and distribution tuned to the engine, and the lowest latency. Querying in place reads directly from S3 through the Glue Catalog, saving storage and duplication but paying a network and pruning cost on every query.”
TrickIs Redshift Serverless always cheaper than a provisioned cluster?
“I'd say not for a steady, 24/7, predictable workload—that's exactly where a correctly sized provisioned cluster, or Reserved Instance pricing, usually wins. Serverless earns its premium back on spiky or unpredictable workloads where a provisioned cluster would otherwise sit idle most of the day.”
Experience checkHow would you decide whether a table belongs natively in Redshift or stays queried from the lake?
“I'd look at query frequency and size together, like the heuristic I'd use for any hot/cold data decision—frequently queried and reasonably sized goes native, rarely queried or very large stays in S3 queried in place. I'd revisit that placement if usage patterns change, not treat it as a one-time decision.”
DesignHow would you prevent one expensive ad hoc query from degrading every other dashboard on a shared workgroup?
“I'd set usage limits with an alert or stop action so a runaway query can't silently burn the whole period's budget, and I'd separate genuinely different workloads—steady BI dashboards versus occasional full-history analysis—into different workgroups against the same namespace, so one doesn't starve the other.”

Memory hook

A warehouse is a stocked kitchen; a lake is the pantry; Serverless is a kitchen you don't have to rent in advance, but leaving the stove on all day erases that advantage.

If you remember only one thing:

Load natively what's queried often and fits comfortably; leave the rest in the lake, queried in place with the same partition discipline as Athena.

Self-check

  1. What is the difference between a Redshift Serverless workgroup and a namespace?

    The workgroup holds compute configuration (RPU range, network settings); the namespace holds the data (databases, schemas, permissions). A namespace can be reached by more than one workgroup.

  2. Why might a steady, predictable workload be cheaper on a provisioned cluster than on Redshift Serverless?

    Serverless's always-available flexibility is priced in; a workload that never needs that flexibility can be sized once on a provisioned or Reserved Instance cluster for less.

  3. What's the cost of querying cold history via an external table instead of loading it natively?

    Every row surviving partition and predicate pruning still has to cross the network from S3 into Redshift compute, and the data doesn't benefit from Redshift's native storage optimizations.

Prove it

Using the placement() heuristic from the production example, add a fifth table with your own size and frequency numbers, run it through the function, and justify in one sentence whether the heuristic's thresholds (50 queries/day, 500 GB) would hold for a real BI team you've seen. Adjust one threshold and show how the placement decision changes for at least one table.

aws-07-05Card 5 of 6

MWAA versus Step Functions

HardP3 · Role-dependentTalk about it13 min
mwaaairflowstep-functionsorchestrationbackfill

Why you care

Both MWAA and Step Functions schedule and run multi-step workflows, so engineers moving off Jenkins cron often default to whichever name they've heard of, without weighing the operator ecosystem, backfill semantics or who owns failures. Interviewers probe this because the decision reveals whether you understand that orchestration tools are not interchangeable just because both draw a DAG—picking the wrong one means re-implementing, by hand, exactly what the other one already gives you.

Explain like I'm 5

MWAA is a factory floor manager who already has a thick binder of pre-written procedures (Airflow's operators and providers) for talking to practically every vendor's machine—Snowflake, dbt, Spark, S3—and who is comfortable improvising a one-off procedure in Python when the binder doesn't have one. Step Functions is a factory floor with a strict wiring diagram bolted into the wall: every connection (state) is declared upfront, rock solid, maintained by AWS—but adding a new kind of machine means you wire a new connection yourself with AWS-native parts, not a drop-in procedure from the binder.

Core concept

  • Apache Airflow (run on MWAA) models workflows as DAGs written in Python; each task typically uses a pre-built operator or provider—hundreds exist for databases, warehouses, cloud services, SaaS tools—or a PythonOperator for custom logic. The operator ecosystem is why Airflow dominates third-party data tooling.
  • MWAA runs that Airflow as a managed service: you don't run the webserver, scheduler or workers yourself, but you do manage DAG code, Python dependencies (requirements.txt) and plugins as deployables. MWAA supports a rolling set of Airflow versions with scheduled end-of-support dates—check the current version table before building, since both supported versions and their support windows change over time.
  • Step Functions models workflows as a declarative state machine (Amazon States Language, JSON) with native AWS service integrations (see Step Functions orchestration); no third-party operator ecosystem, but direct, no-Lambda-needed calls into most AWS services, plus a console execution history that gives you a visual, run-by-run audit trail for free.
  • Backfills: Airflow has first-class backfill semantics built around its scheduling model—a data_interval per DAG run, and tooling to trigger runs for a date range retroactively. Step Functions has no native backfill concept; you build one yourself, typically a script or Map state starting executions in a loop over the date range.
  • Dependencies and retries: Airflow task dependencies are expressed in Python (>>), with per-task retry/timeout config and a scheduler re-evaluating DAG state continuously. Step Functions dependencies are the explicit Next/Catch/Parallel wiring of the state machine, with Retry/Catch per state (see Step Functions orchestration)—nothing is inferred, every edge is drawn by you.
  • Ownership: Airflow DAGs are a shared Python codebase, usually owned by a data engineering team thinking in Python and pandas/Spark. Step Functions state machines are infrastructure-as-code artifacts, often owned alongside the surrounding AWS wiring—IAM roles, Lambdas—by whoever owns that architecture.

Choose MWAA/Airflow when you need the third-party operator ecosystem, Python-native custom logic, or Airflow's backfill/scheduling model—classic batch ETL/ELT, dbt orchestration, cross-system pipelines. Choose Step Functions when the workflow is primarily AWS-service-to-AWS-service, you want a managed state machine with no cluster of your own, and you're comfortable modelling retries and backfills explicitly rather than inheriting Airflow's scheduler semantics.

Under the hood

MWAA provisions an actual Airflow deployment behind the scenes—a scheduler, a worker fleet that autoscales within min/max bounds you configure, and a metadata database—which is why creating or updating an MWAA environment typically takes several minutes, not seconds, and why there is a baseline cost even with no DAGs running. Step Functions has no "environment" to keep warm at all; you pay per state transition (Standard) or per duration (Express) only when an execution actually runs. Airflow's backfill and data_interval model assumes a DAG run represents one logical time period processed once; running a true backfill concurrently with the live schedule on the same DAG is a known source of task collisions unless you explicitly cap concurrency with max_active_runs—a failure mode that is Airflow-specific, because Step Functions executions have no built-in notion of a "logical date" at all; you supply and track that yourself in the input payload.

Minimal example Step Functions only retries where Retry is explicitly declared

PythonA Task with no Retry block fails immediately; one with Retry survivesruns
STATES = {
    "Extract":   {"max_attempts": 3, "needs": 1},
    "Transform": {"max_attempts": 1, "needs": 3},  # no Retry declared
}

for name, cfg in STATES.items():
    for attempt in range(1, cfg["max_attempts"] + 1):
        if attempt >= cfg["needs"]:
            print(f"{name}: succeeded on attempt {attempt}")
            break
        print(f"{name}: attempt {attempt} failed, retrying")
    else:
        print(f"{name}: FAILED after {cfg['max_attempts']} attempt(s), no Retry -> Catch fires")
Output
Extract: succeeded on attempt 1
Transform: attempt 1 failed, retrying
Transform: FAILED after 1 attempt(s), no Retry -> Catch fires

In production Backfill concurrency—Airflow's native cap vs a naive loop

Pythonmax_active_runs caps backfill concurrency; a hand-rolled loop doesn'truns
BACKFILL_DATES = [f"2024-09-{d:02d}" for d in range(1, 11)]   # 10 days
DOWNSTREAM_DB_CONNECTIONS = 3                                  # a hard limit the Load step shares
RUN_DURATION_TICKS = 2

def airflow_backfill(dates, max_active_runs, duration):
    """Airflow enforces max_active_runs across DAG runs: a new run only
    starts once an earlier one finishes and frees a slot."""
    active_until, log, peak, tick = [], [], 0, 0
    for d in dates:
        active_until = [t for t in active_until if t > tick]
        while len(active_until) >= max_active_runs:
            tick += 1
            active_until = [t for t in active_until if t > tick]
        active_until.append(tick + duration)
        peak = max(peak, len(active_until))
        log.append(f"data_interval={d}: starts at tick={tick}, concurrency={len(active_until)}")
        tick += 1
    return log, peak

def naive_step_functions_backfill(dates):
    """A hand-rolled loop fires StartExecution for every date up front;
    nothing in Step Functions itself caps how many run at once."""
    return [f"StartExecution(date={d}): launched immediately" for d in dates], len(dates)

af_log, af_peak = airflow_backfill(BACKFILL_DATES, DOWNSTREAM_DB_CONNECTIONS, RUN_DURATION_TICKS)
for line in af_log:
    print(f"  [airflow] {line}")
print(f"airflow peak concurrency: {af_peak} (capped at max_active_runs={DOWNSTREAM_DB_CONNECTIONS})")

sf_log, sf_peak = naive_step_functions_backfill(BACKFILL_DATES)
print(f"  [states]  {sf_log[0]} ... {sf_log[-1]}")
print(f"naive Step Functions backfill peak concurrency: {sf_peak} "
      f"({'exceeds' if sf_peak > DOWNSTREAM_DB_CONNECTIONS else 'within'} "
      f"the {DOWNSTREAM_DB_CONNECTIONS}-connection downstream limit)")
Output
  [airflow] data_interval=2024-09-01: starts at tick=0, concurrency=1
  [airflow] data_interval=2024-09-02: starts at tick=1, concurrency=2
  [airflow] data_interval=2024-09-03: starts at tick=2, concurrency=2
  [airflow] data_interval=2024-09-04: starts at tick=3, concurrency=2
  [airflow] data_interval=2024-09-05: starts at tick=4, concurrency=2
  [airflow] data_interval=2024-09-06: starts at tick=5, concurrency=2
  [airflow] data_interval=2024-09-07: starts at tick=6, concurrency=2
  [airflow] data_interval=2024-09-08: starts at tick=7, concurrency=2
  [airflow] data_interval=2024-09-09: starts at tick=8, concurrency=2
  [airflow] data_interval=2024-09-10: starts at tick=9, concurrency=2
airflow peak concurrency: 2 (capped at max_active_runs=3)
  [states]  StartExecution(date=2024-09-01): launched immediately ... StartExecution(date=2024-09-10): launched immediately
naive Step Functions backfill peak concurrency: 10 (exceeds the 3-connection downstream limit)

See it happen Where the wiring lives

>>>>NextNextCatchextract(HttpOperator)transform(GlueJobOperator)load(PythonOperator)StartExecution loopTransform(Task)Load(Task)Catch ->Notify
  1. Airflow dependencies are declared in Python with >>; retries live as config on each task, inherited from provider defaults or your override.
  2. Step Functions dependencies are the explicit Next wiring of the state machine; nothing is inferred.
  3. A Task with no Retry block declared fails immediately on its first error; Catch is a separate, explicit edge you must draw.
  4. Airflow's scheduler re-evaluates DAG state continuously and supports backfilling a date range natively via data_interval.
  5. A Step Functions backfill is a script starting executions in a loop, with no built-in concurrency cap unless you add one yourself.
Notice: both diagrams draw the same three-step pipeline; the difference is where the wiring lives—in reusable Python operators and scheduler semantics for Airflow, or in explicit, hand-drawn Next/Retry/Catch edges for Step Functions.
Scenario A

In your world

The situation

Your nightly ETL is three Jenkins-cron-triggered Python scripts (extract, transform, load) chained by "if this one succeeds, the next cron fires 30 minutes later." The team is moving off Jenkins and must choose MWAA or Step Functions for the replacement.

What actually breaks

Someone picks Step Functions because "it's AWS-native and simpler," without checking that the transform step depends on three third-party connectors—a vendor's REST API client, a dbt-style SQL templating step, and Spark job submission—that already have mature, tested Airflow operators. Rebuilding each as a bespoke Lambda, with retry and timeout handling hand-coded inside the Lambda instead of inherited from a state's declarative Retry/Catch, roughly triples the actual engineering effort compared to using the existing Airflow providers almost as-is.

What you'd do on Monday

Reassess the decision by what the task actually touches, not by which service sounds more "native." Given dependence on an existing operator ecosystem—dbt, vendor SDKs, Spark submission—MWAA is the better fit here. Step Functions would have been the better fit for a handful of direct AWS-service calls (Lambda, Glue, SNS) with no third-party integration to inherit.

PythonAirflow DAG using existing provider operators instead of hand-rolled Lambdas (needs apache-airflow)
from datetime import datetime
from airflow import DAG
from airflow.providers.amazon.aws.operators.glue import GlueJobOperator
from airflow.providers.http.operators.http import SimpleHttpOperator

with DAG("nightly_orders_etl", schedule="0 2 * * *",
         start_date=datetime(2024, 1, 1), catchup=False) as dag:
    extract = SimpleHttpOperator(task_id="extract_vendor_feed",
                                  http_conn_id="vendor_api", endpoint="/orders/export")
    transform = GlueJobOperator(task_id="transform_spark",
                                 job_name="orders-transform", retry_limit=2)
    extract >> transform
Scenario B

Imagine you work at Udaan

40 supplier integrations feeding one nightly sync

The numbers

Illustrative: 40 distinct supplier extract steps, each with its own auth and connector quirks, feeding one nightly transform-and-load. At roughly 3 minutes average runtime per extract, running all 40 sequentially would take about 120 minutes; Airflow's configurable task concurrency can run many simultaneously, bounded by downstream connection limits.

What the Scenario-A answer does here

The Scenario A choice of MWAA for the operator ecosystem holds, but at 40 integrations nobody can eyeball-babysit 40 tasks' worth of logs on a bad night; the question becomes how fast you can tell "integrations are failing" from "the whole DAG is stuck."

What flips at this scale

At this scale the flip is instrumenting DAG-level SLA alerting and per-task timeouts rather than relying on someone noticing a red box in the Airflow UI, and capping max_active_tasks deliberately against the number of concurrent connections your extract targets and your own downstream DB can actually tolerate—the same cap-the-concurrency lesson as the backfill example.

The trade-off you'd defend

I would defend per-task SLA alerting plus a concurrency cap tied to a real downstream limit over "watch the UI and raise parallelism until something breaks," accepting upfront tuning work in exchange for predictable nightly completion instead of occasional silent overnight stalls.

Illustrative integration count; not Udaan-published figures.

Gotchas

DAG stuck because a single task never completes

Symptom

The nightly DAG run shows "running" well past its expected window, with no obvious failure, blocking every downstream task.

Cause

A task has no timeout configured, so a hung connector call (network issue, auth stall) blocks the task indefinitely instead of failing and triggering retries.

Fix

Set an explicit execution_timeout on every task that calls an external system, so a hang becomes a failure the retry and alerting machinery can act on.

Backfill collides with the live schedule

Symptom

Duplicate or conflicting writes appear in the destination table on days a manual backfill was run.

Cause

A backfill run and the regularly scheduled run for a nearby date executed concurrently against the same destination without a concurrency cap.

Fix

Set max_active_runs on the DAG, or run backfills in a separate environment, so overlapping runs are capped intentionally, not accidentally.

Step Functions execution appears "stuck" with no error

Symptom

An execution shows RUNNING far longer than normal, with no CloudWatch alarm firing.

Cause

A Task state has no TimeoutSeconds set, so a hung downstream call blocks the state indefinitely.

Fix

Set TimeoutSeconds on every Task state calling an external or long-running resource, and alarm on execution duration exceeding your expected SLA.

Tricky questions

Is Step Functions always the "AWS-native, so simpler" choice for AWS-centric pipelines?
Most people say

Yes, if everything is already on AWS, Step Functions is the natural default.

Actually

"AWS-native" describes the integration surface, not the operator ecosystem. A pipeline that's AWS-native in infrastructure but depends heavily on third-party connectors often ends up re-implementing what Airflow's providers already give for free, inside hand-written Lambda code.

Does MWAA's "managed" label mean you don't need to think about scaling?
Most people say

Yes, it's managed, so it scales itself.

Actually

MWAA autoscales workers within the min/max bounds you configure, but you still choose those bounds, size the environment class, and manage DAG-level concurrency settings yourself; an under-sized environment queues tasks exactly like an under-provisioned downstream dependency would in Step Functions.

Scenario question

You're handed a Step Functions state machine for a nightly ETL that has grown to 15 states as the business added more downstream notifications and conditional branches. Changes now require careful ASL JSON edits, and the team is considering migrating to MWAA. What do you check before recommending a migration?

Model answer

I would check whether the growth is really about needing Airflow's operator ecosystem or Python flexibility—new third-party integrations, custom branching logic that's painful in ASL—or whether it's actually a process problem: ASL changes with no review discipline or tests. If it's the former, I'd recommend the migration. If it's the latter, migrating just moves the same undisciplined-change problem into Python DAG code, which is easier to write carelessly, not harder. The trade-off is real either way: migrating costs new infrastructure and a cutover risk; staying costs continued ASL complexity unless the team adds the review and testing discipline the state machine actually needs.

Interview corner

CommonWhen would you pick MWAA over Step Functions for a new pipeline?
“I'd pick MWAA when the pipeline leans on an existing ecosystem of connectors—dbt, Spark submission, vendor SDKs—that Airflow already has mature providers for. Rebuilding all of that as hand-written Lambdas just to stay 'AWS-native' usually costs more than it saves.”
TrickDoes Step Functions guarantee my workflow won't need a backfill strategy?
“No—Step Functions has no native backfill concept at all. I'd have to build the retroactive-run loop myself, with my own concurrency cap, where Airflow gives me data-interval-aware backfills and max_active_runs out of the box.”
Experience checkDescribe the closest thing to orchestration you've built without a dedicated orchestrator.
“I'd point to my Jenkins chain as an implicit, fragile state machine—if this job's exit code is 0, the next cron fires 30 minutes later, with retry logic duplicated by hand in each script. Moving that into Step Functions or Airflow makes the dependencies and retries explicit and inspectable instead of inferred from cron timing.”
DesignHow would you design alerting so a stuck DAG or stuck state machine execution is caught quickly?
“I'd set an explicit timeout on every task or state that calls something external, so a hang becomes a failure rather than an indefinite running status, and I'd alarm on execution duration exceeding the expected SLA rather than relying on someone noticing a stale run in a console.”

Memory hook

MWAA hands you a binder of pre-written procedures; Step Functions hands you a solid wiring diagram you build yourself, wire by wire.

If you remember only one thing:

Choose based on what the pipeline actually touches—a rich operator ecosystem or AWS-native service calls—not on which name sounds more "native."

Self-check

  1. What does Airflow give you natively that a hand-rolled Step Functions backfill loop does not?

    A data-interval-aware scheduling model and native concurrency controls, like max_active_runs, for retroactive runs, instead of a loop you must cap yourself.

  2. Why might choosing Step Functions for "AWS-native simplicity" backfire on a pipeline with heavy third-party integrations?

    You lose Airflow's mature operator/provider ecosystem for those integrations and end up re-implementing their retry and connection logic by hand inside Lambdas.

  3. What happens to a Task state or Airflow task with no timeout configured, if the external call it makes hangs?

    It can block indefinitely instead of failing, which prevents retries and alarms from ever triggering, because nothing registers as a failure.

Prove it

Extend the backfill simulation to 20 dates with max_active_runs=5 and RUN_DURATION_TICKS=3. Print peak concurrency and confirm it never exceeds 5. Then run the same 20 dates through naive_step_functions_backfill and print both peak concurrency numbers side by side to show the gap.

aws-07-06Card 6 of 6

Operate and prove an incremental pipeline

HardP3 · Role-dependentTalk about it14 min
pipelinereconciliationquarantinelineageiamunit-cost

Why you care

Candidates can usually explain Glue or Athena in isolation but stumble when asked to walk a single pipeline end to end with evidence that it worked—a reconciled row count, bad records visibly quarantined, an alarm that would have caught the last incident, and an IAM role scoped to only what the job needs. This card is the "operate it, prove it" synthesis interviewers use to separate someone who read about Glue from someone who has actually run a pipeline and would be the one paged for it at 2 a.m.

Explain like I'm 5

Think of the full raw-to-reportable pipeline as a parcel's journey through customs. It arrives (raw S3); someone inspects it and files paperwork so it's searchable later (curated Parquet plus catalogue); the inspector keeps a running tally so nothing goes missing (reconciliation); damaged parcels get set aside instead of shipped onward blindly (quarantine); every parcel has a tracking label so you can trace which truck and shift handled it (lineage); there's a siren if the loading dock stops working (alarm); and only the warehouse staff who need to touch a given shelf have the key (IAM least privilege). You're not done when the parcel physically moved—you're done when you can prove it moved correctly.

Core concept

  • The reference shape: source → raw S3 (as-landed, immutable) → curated S3 (cleaned, Parquet, partitioned, catalogued via Glue) → query layer (Athena ad hoc, Redshift for repeat BI).
  • Reconciliation: compare a count or sum derived independently at two points—for example, rows in the source versus rows successfully loaded plus rows quarantined—and alert on any mismatch, every run, not just when something "feels wrong." A green job with a silent row-count mismatch is the single most common quiet failure in ETL.
  • Quarantine: records failing validation go to a separate, inspectable location with the reason, not dropped and not force-loaded. Reconciliation treats quarantined rows as accounted for, not missing.
  • Lineage: minimal viable lineage tags every curated row or partition with its source file key (or batch ID) and job run ID, so a bad number in a report traces back to the exact input file and run, not "sometime last week." (Owning watermark/CDC extraction and replay/late-arrival/schema-change handling in depth belongs to Incremental extraction: watermarks and CDC and Backfill and replay with late data; this card treats them as a given upstream contract.)
  • Alarms: a pipeline needs at least one alarm that would have caught the last incident you can think of—zero rows loaded, a reconciliation mismatch past a threshold, a quarantine count spiking, or a scheduled trigger that never fired. An alarm nobody would have paged on is decoration.
  • IAM: the job's execution role should be scoped to the specific S3 prefixes, catalog tables and (if applicable) warehouse/secrets resources it needs—not a wildcard on the whole bucket "to make it work," which is the single most common thing a security review flags in a first portfolio pipeline.
  • Unit cost: express cost per unit of real work—per GB processed, per successful run, per 1,000 rows loaded—using actual measured numbers (bytes scanned from a query engine's own stats, compute-hours from a job's own run metrics), not an estimate.

You are not done building a pipeline when data lands in the destination table. You are done when you can show, for the last run: the reconciled count, the quarantine contents and reason, the lineage back to source, the alarm that would fire on failure, the IAM role's actual permissions, and the measured unit cost—in that order, because each answers a different question an on-call shift or a security review will actually ask.

Under the hood

Reconciliation only works if the "expected" count is derived independently of the load path. If you compute "rows expected" by re-reading the same file the loader already parsed, a parsing bug can produce matching-but-wrong numbers on both sides, and the check passes while being meaningless. The expected count should come from something structurally independent—the source system's own row count, an S3 inventory-reported object count, or a checksum the upstream producer emitted. Lineage tags are cheap to add (a column, a few bytes per row) and nearly impossible to retrofit once historical data has already been loaded without them, because you cannot reconstruct "which source file produced this row" after the fact unless you kept it. That asymmetry—cheap now, expensive or impossible later—is why lineage columns belong in the very first version of a curated table's schema, not a "we'll add it later" backlog item.

Minimal example Reconciliation that actually alarms on a mismatch

PythonReconciliation: fully accounted-for run vs one with vanished rowsruns
def reconcile(source_count, loaded_count, quarantined_count, mismatch_tolerance=0):
    accounted_for = loaded_count + quarantined_count
    mismatch = source_count - accounted_for
    return {
        "source_count": source_count,
        "accounted_for": accounted_for,
        "mismatch": mismatch,
        "alarm_fires": abs(mismatch) > mismatch_tolerance,
    }

RUNS = [
    {"run_id": "r1", "source_count": 1000, "loaded": 955, "quarantined": 45},   # fully accounted for
    {"run_id": "r2", "source_count": 1000, "loaded": 950, "quarantined": 45},   # 5 rows vanished
]

for r in RUNS:
    result = reconcile(r["source_count"], r["loaded"], r["quarantined"])
    status = "ALARM" if result["alarm_fires"] else "ok"
    print(f"{r['run_id']}: source={result['source_count']} "
          f"accounted_for={result['accounted_for']} mismatch={result['mismatch']} -> {status}")
Output
r1: source=1000 accounted_for=1000 mismatch=0 -> ok
r2: source=1000 accounted_for=995 mismatch=5 -> ALARM

In production Full run with lineage, reconciliation, unit cost and IAM scope

PythonOne pipeline run: quarantine, lineage, reconciliation, unit cost, IAM checkruns
import json
from pathlib import Path

raw = Path("raw/orders")
raw.mkdir(parents=True)
RUN_ID, SOURCE_FILE = "run-2024-10-15-01", "part-001.json"

lines = [
    json.dumps({"order_id": 1, "amount": 499.0}),
    json.dumps({"order_id": 2, "amount": 250.0}),
    "{not valid json",
    json.dumps({"order_id": 4, "amount": -5.0}),
    json.dumps({"order_id": 5, "amount": 120.0}),
]
(raw / SOURCE_FILE).write_text("\n".join(lines))

# Independent count: lines in the file, computed without touching the
# JSON parser, so a parser bug can't make this check lie to you.
source_line_count = len((raw / SOURCE_FILE).read_text().splitlines())

curated, quarantine = [], []
for line in (raw / SOURCE_FILE).read_text().splitlines():
    try:
        row = json.loads(line)
    except json.JSONDecodeError as e:
        quarantine.append({"source_file": SOURCE_FILE, "run_id": RUN_ID, "reason": f"bad-json: {e.msg}"})
        continue
    if row.get("amount", 0) < 0:
        quarantine.append({"source_file": SOURCE_FILE, "run_id": RUN_ID, "reason": "bad-value: negative amount"})
        continue
    curated.append({**row, "_source_file": SOURCE_FILE, "_run_id": RUN_ID})

mismatch = source_line_count - (len(curated) + len(quarantine))
print(f"reconciliation: source_lines={source_line_count} loaded={len(curated)} "
      f"quarantined={len(quarantine)} mismatch={mismatch} -> {'ALARM' if mismatch != 0 else 'ok'}")
for q in quarantine:
    print(f"  quarantined: {q}")
for c in curated:
    print(f"  curated row with lineage: {c}")

bytes_processed = (raw / SOURCE_FILE).stat().st_size
illustrative_cost_per_gb = 0.02   # placeholder rate; check current pricing before quoting a real figure
cost_this_run = bytes_processed / (1024 ** 3) * illustrative_cost_per_gb
cost_per_1k_rows_loaded = (cost_this_run / len(curated)) * 1000 if curated else None
print(f"unit cost: bytes_processed={bytes_processed} "
      f"cost_per_1000_rows_loaded=${cost_per_1k_rows_loaded:.6f} (illustrative rate, not an AWS-published price)")

role_allowed_prefixes = ["raw/orders/", "curated/orders/"]
resources_touched = [f"raw/orders/{SOURCE_FILE}", "curated/orders/part-001.parquet"]
over_scoped = [r for r in resources_touched if not any(r.startswith(p) for p in role_allowed_prefixes)]
print(f"IAM scope check: role_allowed={role_allowed_prefixes} "
      f"touched={resources_touched} over_scoped={over_scoped or 'none'}")
Output
reconciliation: source_lines=5 loaded=3 quarantined=2 mismatch=0 -> ok
  quarantined: {'source_file': 'part-001.json', 'run_id': 'run-2024-10-15-01', 'reason': 'bad-json: Expecting property name enclosed in double quotes'}
  quarantined: {'source_file': 'part-001.json', 'run_id': 'run-2024-10-15-01', 'reason': 'bad-value: negative amount'}
  curated row with lineage: {'order_id': 1, 'amount': 499.0, '_source_file': 'part-001.json', '_run_id': 'run-2024-10-15-01'}
  curated row with lineage: {'order_id': 2, 'amount': 250.0, '_source_file': 'part-001.json', '_run_id': 'run-2024-10-15-01'}
  curated row with lineage: {'order_id': 5, 'amount': 120.0, '_source_file': 'part-001.json', '_run_id': 'run-2024-10-15-01'}
unit cost: bytes_processed=150 cost_per_1000_rows_loaded=$0.000001 (illustrative rate, not an AWS-published price)
IAM scope check: role_allowed=['raw/orders/', 'curated/orders/'] touched=['raw/orders/part-001.json', 'curated/orders/part-001.parquet'] over_scoped=none

See it happen Evidence attached to every stage, not just at the end

landreadbad rowsgood rows + lineage tagsregister partitionshared schemaloaded+quarantined countsmismatch > 0SourceRaw S3Extract/Transform jobQuarantineCuratedParquetGlueCatalogAthena /RedshiftReconciliationcheckAlarm
  1. Source data lands in raw S3, untouched.
  2. The job reads raw files and splits rows: bad ones go to quarantine with a reason, good ones get lineage tags.
  3. Curated partitions register into the same catalog every query engine shares.
  4. Athena and Redshift query the one schema the catalog and the job agree on.
  5. Reconciliation compares the source's independent count against loaded plus quarantined for this run.
  6. Any nonzero mismatch fires an alarm immediately, not after someone notices a wrong number downstream.
Notice: the pipeline isn't done when curated data lands in the catalog; it's done when the reconciliation step has confirmed every source row is accounted for, one way or another.
Scenario A

In your world

The situation

You've built the pieces separately—a Glue job with bookmarks, a curated Parquet table queried via Athena, an SQS-fed loader. Your manager asks you to walk through, end to end, what happens on a bad night and how you'd know.

What actually breaks

Last Tuesday's run shows green in Jenkins and in the Glue console. Finance's dashboard shows revenue down 8% for the day. Nobody can say, right now, whether 8% of orders failed to load, 8% of orders genuinely didn't happen, or the dashboard query itself is wrong—because there is no reconciled count anywhere in the pipeline, just "the job finished."

What you'd do on Monday

Add reconciliation as a non-optional last stage of every run, not a side investigation: compare an independently-sourced expected count against loaded plus quarantined, alarm on any nonzero mismatch, and keep lineage tags so the moment a mismatch alarm fires, you already know which source file and run to investigate—turning Tuesday's hour-long forensic exercise into a five-minute lookup.

PythonPublish a reconciliation mismatch as a CloudWatch metric and alarm on it (needs boto3)
import boto3

cloudwatch = boto3.client("cloudwatch", region_name="ap-south-1")

def publish_reconciliation(run_id, mismatch):
    cloudwatch.put_metric_data(
        Namespace="ETL/Reconciliation",
        MetricData=[{
            "MetricName": "RowCountMismatch",
            "Dimensions": [{"Name": "RunId", "Value": run_id}],
            "Value": abs(mismatch),
            "Unit": "Count",
        }],
    )

cloudwatch.put_metric_alarm(
    AlarmName="orders-etl-reconciliation-mismatch",
    Namespace="ETL/Reconciliation",
    MetricName="RowCountMismatch",
    Statistic="Maximum",
    Period=86400,
    EvaluationPeriods=1,
    Threshold=0,
    ComparisonOperator="GreaterThanThreshold",
    AlarmActions=["arn:aws:sns:ap-south-1:123456789012:etl-alerts"],
)
Scenario B

Imagine you work at Groww

2 million trade/order confirmation events reconciled daily

The numbers

Illustrative: 2M events/day across multiple source feeds, reconciled per run. At even a 0.01% unexplained mismatch rate (200 rows/day), a per-run reconciliation check catches it the same day; without one, 200 rows/day compounds to roughly 73,000 unreconciled rows over a year before anyone notices via a side effect—a user complaint, an audit.

What the Scenario-A answer does here

The Scenario A fix—reconcile every run, alarm on any nonzero mismatch—is correct at any scale, but across, say, 15 source feeds, reconciliation needs to run per feed, not as one aggregate number, or a 0.01% mismatch in one small feed gets averaged away into "within tolerance" against the other 14.

What flips at this scale

At this scale the flip is per-source-feed reconciliation with its own alarm, plus a daily rollup dashboard, rather than one global number—the global number told you "something, somewhere" was wrong; the per-feed number tells you where to look without any forensic work at all.

The trade-off you'd defend

I would defend per-feed reconciliation and alarms over one aggregate check, accepting more alarms to configure and maintain in exchange for a mismatch alert that already names the broken feed instead of starting a 15-feed investigation.

Illustrative event volume; not Groww-published figures.

Gotchas

Reconciliation count derived from the same buggy parse it's supposed to catch

Symptom

Reconciliation always shows mismatch=0 even on a day with a known parsing bug.

Cause

The "expected" count was computed by re-running the same parser being validated, so a parsing bug produces matching-but-wrong numbers on both sides.

Fix

Source the expected count independently—a line count on the raw file, a count the upstream system itself reports, or an S3 inventory or manifest count.

Retries double-count a reconciliation baseline

Symptom

Reconciliation mismatch goes negative—more rows loaded than source rows reported—after a job that had to retry a failed step.

Cause

A retried step reprocessed and re-added already-loaded rows because the retry path wasn't idempotent; retries or late records can duplicate or corrupt aggregates unless replay boundaries are explicit.

Fix

Make the load path idempotent (see Idempotent consumers under failure) and compute reconciliation against a run-scoped count, not a cumulative one, so a retry's effect is visible rather than absorbed into a growing total.

Lineage tags added after the fact can't explain old data

Symptom

A mismatch investigation on data loaded six months ago has no source-file or run-id trail to follow.

Cause

Lineage columns were added to the schema only recently; historical rows predate them and were never backfilled.

Fix

Treat lineage columns as part of the first version of any curated table's schema, since backfilling them accurately after the fact is often impossible.

Tricky questions

Does a quarantine table mean no data is ever lost?
Most people say

Yes, if it's quarantined instead of dropped, nothing is lost.

Actually

Quarantine prevents silent data loss only if someone, or an alarm, actually looks at it. An unmonitored, ever-growing quarantine table with no owner and no alarm is functionally the same as dropping the rows, just with better optics in an audit.

If Jenkins or Glue shows the job succeeded, does that mean the data is correct?
Most people say

Yes, a successful job run means the data loaded is correct.

Actually

Job success means the code ran to completion without raising an unhandled exception. It says nothing about whether the row count matches the source, whether quarantined rows are being silently ignored, or whether the resulting business numbers are sensible. Reconciliation is the separate check that answers "is it correct," not "did it run."

Scenario question

You're handed a pipeline—Glue extract/transform, Athena-queried curated table—that has run successfully every night for six months with zero alarms. Your manager asks, "how do we know it's actually correct, not just quiet?" What do you show them?

Model answer

I would walk through the evidence checklist rather than the green build: the reconciled count trend across those six months, the quarantine table's contents and whether anyone has reviewed them, a lineage trace from a sample output row back to its source file and run, the specific alarm that would have caught the worst incident I can imagine for this pipeline, the execution role's actual permissions versus what it needs, and a measured unit cost over time. The trade-off I'd name is that building this retroactively takes real effort and still can't answer every question about historical data—lineage I didn't capture six months ago can't be reconstructed now—but it answers "is it correct" for every run going forward, which "it's been quiet" never actually did.

Interview corner

CommonWhat does it mean to "prove" a pipeline is correct, beyond it finishing successfully?
“I'd show a reconciled count for the run, the contents of anything quarantined and why, a lineage trace from a sample output row back to its source file, the alarm that would have caught the last real incident, and the IAM role's actual scope—a green build alone tells me the code ran, not that the data is right.”
TrickIf nothing has alarmed in six months, does that mean the pipeline has been correct for six months?
“Not necessarily—it might mean nothing has gone wrong, or it might mean the alarms don't cover the failure mode that would actually occur. I'd check whether the alarms I have would have caught the worst past incident I know about before trusting the silence.”
Experience checkTell me about a "quiet" failure you've seen or can imagine in your own ETL work.
“My Jenkins jobs log a row count and go green even when that count is zero, because nothing checks it against an independent expectation. The fix I'd apply is exactly the reconciliation pattern here—compare against a source-side count, not just confirm the job didn't crash.”
DesignHow would you retrofit reconciliation and lineage onto a pipeline already running in production without a big-bang rewrite?
“I'd add reconciliation and lineage tagging to new data going forward immediately, since that's cheap, and separately decide whether backfilling lineage for historical data is even possible—often it isn't, so I'd be upfront that some forensic questions about old data will stay unanswerable, rather than pretend a backfill fixes everything.”

Memory hook

A green build tells you the code ran; a reconciled count, a visible quarantine and a working alarm are what tell you the data is right.

If you remember only one thing:

Reconcile every run against an independently-sourced count, and treat a mismatch of any size as an alarm, not a rounding error.

Self-check

  1. Why is a reconciliation count derived from the same parser being validated not trustworthy?

    A bug in that parser can produce matching-but-wrong numbers on both the "expected" and "actual" side, so the check passes while being meaningless.

  2. What's the risk of adding lineage columns to a curated table "later" instead of from the first version of its schema?

    Historical rows loaded before the columns existed usually cannot be backfilled accurately, since the source-file or run information was never captured for them.

  3. Does a quarantine table by itself guarantee no data is silently lost?

    No—only if someone or an alarm actually monitors it; an unwatched, ever-growing quarantine table is functionally equivalent to dropping the rows.

Prove it

Extend the production example with a second run that simulates a non-idempotent retry: reprocess part-001.json a second time without checking for already-loaded rows, and show the reconciliation mismatch go negative. Then make the retry idempotent (skip rows already present in curated by order_id) and show the mismatch return to 0.