AWS Data Lake and Analytics Architecture: S3, Glue, Athena, Redshift and Kinesis

The finance forward arrives before the coffee does: Athena scanned 2.3 PB last month — $11,500 for forty analysts — and the dashboards are still slow. The “lake” bucket holds 240 million objects averaging 400 KB, three teams disagree about which copy of orders is authoritative, and the engineer who understood the Glue jobs has left. Nobody designed this. It accreted — a Firehose here, a crawler there, a CSV export “just for now” — because on AWS the storage is so cheap and the services so easy to bolt together that the architecture is the only hard part. A data lake is not a bucket with data in it; it is a contract: zoned storage on Amazon S3, columnar formats sized for scan engines, one AWS Glue Data Catalog as the single source of schema truth, ingestion that produces query-shaped files, permissions governed above the storage layer, and compute — Athena, Redshift, EMR, QuickSight — that rents the same bytes without copying them.

This article is the blueprint for that contract. You will design the S3 zone layout (raw → curated → consumption) with Hive-style partitioning and Parquet/Iceberg formats that cut scan costs by 90%+; ingest with Kinesis Data Streams, Amazon Data Firehose and DMS so files arrive big and partitioned instead of small and scattered; run Glue crawlers, ETL jobs and workflows without letting the catalog drift; query with Athena using partition projection and workgroup cost caps; decide when a workload belongs in Redshift RA3/Serverless instead of the lake and how Spectrum joins both; govern with Lake Formation LF-tags; upgrade hot tables to an Apache Iceberg lakehouse for upserts, deletes and time travel; and orchestrate it with Step Functions or MWAA into QuickSight — each layer with the exact aws CLI, Athena SQL and Terraform, plus the limits and prices that decide designs.

By the end you will be able to defend every byte on the bill. A $3,000/month platform and a $30,000/month swamp use the same services — they differ only in a dozen decisions: file size, format, partition keys, buffer hints, catalog hygiene, scan caps, and where the warehouse boundary sits. This is a reference for making all of them deliberately.

What problem this solves

Analytics on AWS fails in predictable, expensive ways, and almost never because a service broke — it fails because defaults shipped to production: Firehose’s 5 MB buffer produced millions of tiny files; a crawler rewrote a schema at 03:00; an unpartitioned table made every dashboard scan three years of history; bucket policies multiplied until nobody could say who can read PII. Each is invisible on day one and structural by month six — re-partitioning a petabyte is a quarter of work — which is why lake architecture must be decided before the data arrives.

Who hits this: any team past its first Athena query, hardest at the 1–50 TB/month range — big enough that scan costs and file counts matter, small enough that there is no dedicated platform team. The fix is never “add another service”; it is layout, format and governance discipline applied in the right order.

Learning objectives

By the end of this article you can:

• Design a zoned S3 lake (raw/curated/consumption) with bucket strategy, prefix conventions, Hive-style partition keys and target file sizes, justified against S3’s limits and Athena’s pricing.
• Choose ingestion per source — Kinesis Data Streams vs Firehose vs DMS vs batch — from their real limits, and configure each to emit large Parquet files, not small JSON ones.
• Run the Glue Data Catalog as the single schema store: crawlers that don’t drift, partition indexes, explicit DDL, and ETL jobs sized by worker type with bookmarks and Flex.
• Make Athena cheap and fast: partition projection, CTAS/INSERT mechanics (including the 100-partition limit), workgroup scan caps, and the techniques that cut bytes scanned by 90%+.
• Place the lake/warehouse boundary: when Athena is enough, when Redshift RA3 or Serverless earns its cost, and how Spectrum and zero-ETL keep one copy of truth.
• Govern with Lake Formation: register locations, revoke IAMAllowedPrincipals, grant by LF-tags and row/cell filters instead of bucket policies.
• Upgrade mutable tables to Apache Iceberg — MERGE/UPDATE/DELETE, time travel, compaction, snapshot maintenance — and know when the machinery is not worth it.
• Orchestrate with Glue workflows, Step Functions or MWAA, serve with QuickSight, and produce a defensible monthly cost model in USD and INR.

Prerequisites & where this fits

You should be comfortable with core S3 (buckets, keys, storage classes), IAM roles and policies, basic SQL, and reading Terraform. Everything else is built up from first principles. If any of those are shaky, fix them first:

This article sits at the centre of the Data & Analytics on AWS track: upstream are the storage and account foundations above; downstream sit the specialised deep dives — Apache Iceberg on S3 with Glue for table-format operations, DynamoDB Streams CDC pipelines for NoSQL sources, Kafka as the event backbone on AWS if you outgrow Kinesis, and the enterprise AWS lakehouse reference architecture for the multi-account version. Running Snowflake or BigQuery alongside AWS? The layout principles transfer — compare Snowflake + dbt on AWS and BigQuery’s warehouse model.

Core concepts

Five mental models carry every design decision in this article.

Storage and compute are separated, and the catalog is the hinge. A classic warehouse ships storage, metadata and compute as one product. On AWS they are three independent layers: bytes in S3; schemas, locations and partitions in the Glue Data Catalog (an API-compatible descendant of the Hive metastore); and stateless compute — Athena, Spectrum, Glue Spark, EMR — rented by the query or the hour. Any engine that speaks to the catalog reads the same table without copying it. That is the superpower and the failure mode: if the catalog lies (stale partitions, drifted schema), every engine lies identically.

Files are the database, so file layout is your index. There are no B-trees in a lake. The only “indexes” are: the partition key encoded in the object key (dt=2026-07-01/), which lets engines skip whole directories; the columnar format (Parquet), which lets them skip columns; and Parquet’s internal row-group statistics, which let them skip pages. A lake with the right layout answers a typical BI query by reading 0.1% of the bytes a naive one reads. Nothing you buy later — bigger Redshift, more DPUs — compensates for a wrong layout, because every engine pays the same I/O.

Schema-on-read cuts both ways. Ingestion never fails on schema (raw zone accepts anything), which is why lakes absorb messy sources so well. But it means quality is enforced at read time — by the curated zone’s ETL, not by the storage. The zones exist precisely to move data from “whatever arrived” (raw) to “typed, deduplicated, contracted” (curated) to “modelled for one consumer” (consumption). Skipping the middle zone is how lakes become swamps.

You pay one of two meters: per byte scanned, or per hour provisioned. Athena and Spectrum bill by data touched ($5/TB scanned); Redshift clusters, DMS instances and MWAA bill by wall-clock. The architecture’s job is to put each workload on the cheaper meter: ad-hoc and spiky → scan-metered; hot, repeated, high-concurrency → provisioned. Failed cost models do the opposite — dashboards refreshing every 30 seconds on the $5/TB meter, or a once-a-week query on a 24×7 cluster.

Govern tables, not buckets. IAM and bucket policies see objects and prefixes; they cannot express “analysts may read orders except card_number, and only rows where country = 'IN'”. Lake Formation governs at the database/table/column/row level and is enforced consistently by Athena, Spectrum, Glue and EMR. At more than a handful of teams and tables, bucket-policy governance collapses under its own combinatorics.

The vocabulary in one table

The S3 foundation: zones, layout, formats and lifecycle

Everything downstream — ingestion configs, Glue jobs, Athena bills, Redshift COPY performance — is determined by decisions made here. Get the foundation right and the rest of the platform is configuration; get it wrong and every layer fights it forever.

Zones: the contract levels

Use three primary zones plus two utility areas. Physically, make each zone its own bucket (not a prefix in one bucket): per-bucket encryption defaults, lifecycle rules, replication, access boundaries and cost reporting all become trivial, and a mistaken recursive delete cannot cross zones. Name them with account/region qualifiers since bucket names are global: mycorp-lake-raw-apsouth1-prod.

Two rules make zones real rather than decorative. First, raw is immutable: append-only, byte-for-byte what the source sent — if a bug corrupts curated, you rebuild it from raw; enable S3 Versioning (and Object Lock where regulators care) to protect that undo log. Second, no consumer reads raw: the moment a dashboard points at raw JSON “temporarily”, you have frozen that schema forever and put your most expensive scans on your worst format.

Buckets, prefixes and S3’s real limits

Within a zone bucket, lay out prefixes as source/dataset/partitionkeys.../file:

s3://mycorp-lake-raw-apsouth1-prod/
  pos/orders/ingest_dt=2026-07-06/hr=13/part-0001.gz.parquet
  pos/orders/ingest_dt=2026-07-06/hr=14/...
  clickstream/events/ingest_dt=2026-07-06/hr=13/...
  dms/erp/customers/LOAD00000001.parquet          # DMS full load
  dms/erp/customers/2026/07/06/20260706-131502044.parquet  # DMS CDC

s3://mycorp-lake-curated-apsouth1-prod/
  sales/orders/dt=2026-07-06/part-...-c000.zstd.parquet
  sales/order_items/dt=2026-07-06/...
  customer/profiles_iceberg/data/...              # Iceberg manages its own layout
  customer/profiles_iceberg/metadata/...

The performance numbers that matter: S3 sustains 3,500 PUT/COPY/POST/DELETE and 5,500 GET/HEAD requests per second per partitioned prefix, partitioning automatically as rates grow. Analytics-sized files never hit this; the tiny-file pattern (millions of 200 KB objects) is what turns request rates and per-request prices into a real constraint. Randomised key prefixes have been unnecessary since 2018 — human-readable, sortable layouts like the above are correct.

Three layout rules with teeth: keep one dataset (one schema) per prefix — mixed schemas under one table location break crawlers and confuse every engine; let writers generate unique file names and never overwrite objects in place on Hive-style tables (a reader mid-scan sees torn state — write new files then swap the partition, or use Iceberg, whose commits are atomic); and pair Versioning with a NoncurrentVersionExpiration lifecycle rule, or every compaction rewrite quietly doubles storage.

Partitioning: the coarse index

A partition column turns WHERE dt BETWEEN '2026-07-01' AND '2026-07-07' into “read 7 directories instead of 1,095”. It is the single highest-leverage performance decision in the lake. It also has a failure mode in each direction: too few partitions and every query scans everything; too many (high-cardinality keys, multi-key explosions) and you get the small-files swamp plus slow planning.

Sizing heuristics that survive production: keep each partition ≥ ~100 MB of columnar data (below that, overhead beats pruning — partition by day, not hour, at modest volumes); keep partitions per table in the tens of thousands, not millions (each is a Glue catalog object, and GetPartitions traffic scales with them); and never partition by a high-cardinality column (user_id, order_id, a 1,800-value store_id) — sort order within files plus Parquet statistics do that job.

File formats: where 90% of the money is

Columnar formats win analytics for three mechanical reasons: engines read only referenced columns (column pruning), skip row groups whose min/max statistics exclude the predicate (predicate pushdown), and compress far better because similar values sit together. The same 1 TB of JSON is typically 100–200 GB as SNAPPY Parquet — and a SELECT 4 columns WHERE dt=... might scan 2 GB of it. At Athena’s $5/TB, format choice is the bill.

Compression inside Parquet: SNAPPY (the common default) decompresses fastest; ZSTD is ~20–30% smaller for slightly more CPU and is well supported across Athena/Glue/Firehose — prefer it for storage-heavy tables. GZIP on CSV/JSON is a hidden parallelism killer: non-splittable, one reader per file.

File size matters as much as format. Every object carries fixed costs — a GET request, TLS setup, footer parse, a task-scheduling slot — so 1 TB at 1 MB/file is a million objects and a planning disaster, while the same terabyte at the target 128–512 MB per file is a few thousand objects scanned in parallel. Above ~2 GB is still fine for Parquet (row groups keep it splittable) but bad for gzipped JSON/CSV, which a single reader must decompress end to end.

Storage classes and lifecycle: paying for temperature honestly

Lake data cools fast: yesterday’s partition is hot, last quarter’s is warm, 2023’s exists for the auditor and the once-a-year backfill. S3 prices that curve (us-east-1, indicative; Mumbai ap-south-1 runs ~10% higher on storage):

Design notes that bite: Athena and Spectrum read Standard, IA and Glacier Instant Retrieval directly, but objects in Glacier Flexible/Deep Archive fail or are skipped until restored — never archive a partition analysts might query without a restore runbook. And the tiny-file swamp poisons lifecycle economics too: transitions are billed per object (~$0.01–0.05 per 1,000 depending on target), and the 128 KB minimums mean small objects don’t even get cheaper. Compact first, then tier.

# Lifecycle: raw → IA at 30d → Glacier IR at 90d → Deep Archive at 400d; expire old versions
aws s3api put-bucket-lifecycle-configuration \
  --bucket mycorp-lake-raw-apsouth1-prod \
  --lifecycle-configuration '{
    "Rules": [{
      "ID": "raw-cooling", "Status": "Enabled",
      "Filter": {"Prefix": ""},
      "Transitions": [
        {"Days": 30,  "StorageClass": "STANDARD_IA"},
        {"Days": 90,  "StorageClass": "GLACIER_IR"},
        {"Days": 400, "StorageClass": "DEEP_ARCHIVE"}
      ],
      "NoncurrentVersionExpiration": {"NoncurrentDays": 30},
      "AbortIncompleteMultipartUpload": {"DaysAfterInitiation": 7}
    }]
  }'

resource "aws_s3_bucket" "curated" {
  bucket = "mycorp-lake-curated-apsouth1-prod"
}

resource "aws_s3_bucket_server_side_encryption_configuration" "curated" {
  bucket = aws_s3_bucket.curated.id
  rule {
    apply_server_side_encryption_by_default {
      sse_algorithm     = "aws:kms"
      kms_master_key_id = aws_kms_key.lake.arn
    }
    bucket_key_enabled = true   # cuts KMS request costs ~99% on high-object-count buckets
  }
}

resource "aws_s3_bucket_lifecycle_configuration" "curated" {
  bucket = aws_s3_bucket.curated.id
  rule {
    id     = "curated-cooling"
    status = "Enabled"
    filter {}                                    # entire bucket
    transition { days = 60  storage_class = "INTELLIGENT_TIERING" }
    abort_incomplete_multipart_upload { days_after_initiation = 7 }
  }
}

The bucket_key_enabled line matters: with SSE-KMS and millions of objects, S3 Bucket Keys collapse per-object KMS calls into per-bucket-key calls — the difference between a negligible KMS line and thousands of dollars.

Ingestion: Kinesis Data Streams, Firehose, DMS and batch

Ingestion’s job is not “get data into S3” — anything can do that. Its job is to land data already query-shaped: correctly zoned, partitioned by event time or arrival time, in large files, with failures quarantined instead of dropped. Choose the tool by the shape of the source:

Two cross-cutting rules. Idempotence: every path here is at-least-once, so curated-zone ETL must deduplicate (event ID + event time, or Iceberg MERGE). Event time vs arrival time: raw partitions by arrival (ingest_dt, all ingestion reliably knows); curated re-partitions by event time (dt from the payload, what analysts filter on). Confusing the two produces silently wrong “daily” numbers and lost late events.

Kinesis Data Streams: the replayable front door

Kinesis Data Streams (KDS) is the durable, ordered, replayable transport you put in front of Firehose when more than one consumer needs the events (real-time anomaly detection and lake delivery), or when you need replay. Capacity is either provisioned (you manage shards) or on-demand (AWS manages them; you pay per GB).

The economics flip on utilisation: well-utilised provisioned shards cost several times less per GB than on-demand, so steady volume → provisioned; spiky or unknown → on-demand. The provisioned-mode trap is the hot shard: records route by MD5(partition key), so keying by store_id when one store does 30% of volume throttles one shard while nineteen idle — WriteProvisionedThroughputExceeded fires while average utilisation looks healthy (confirm with enhanced shard-level metrics). Key by a high-cardinality value unless you genuinely need per-key ordering.

# On-demand stream, 7-day retention, CMK encryption
aws kinesis create-stream --stream-name clickstream-prod \
  --stream-mode-details StreamMode=ON_DEMAND
aws kinesis increase-stream-retention-period \
  --stream-name clickstream-prod --retention-period-hours 168
aws kinesis start-stream-encryption --stream-name clickstream-prod \
  --encryption-type KMS --key-id alias/lake-ingest

resource "aws_kinesis_stream" "clickstream" {
  name             = "clickstream-prod"
  retention_period = 168
  encryption_type  = "KMS"
  kms_key_id       = aws_kms_key.lake.arn
  stream_mode_details { stream_mode = "ON_DEMAND" }
}

Amazon Data Firehose: the file-size governor

Firehose (now Amazon Data Firehose) is the zero-administration delivery service: it reads from KDS or direct PutRecord, optionally transforms batches with a Lambda, optionally converts JSON to Parquet/ORC against a Glue table schema, buffers, and writes S3 objects under your prefix pattern, quarantining failures to an error prefix. It is the lake’s file-size knob — every row below is a design decision, not a default to accept:

The buffer maths: at 2 MB/s a 128 MB buffer fills in ~64 s — big files and ~1-minute freshness; at 50 KB/s the 900 s interval fires first → ~45 MB files, still fine. At 5 MB/60 s defaults with dynamic partitioning across a 1,800-value key: thousands of sub-MB objects per hour, forever. That arithmetic is behind most “Athena is slow” tickets.

resource "aws_kinesis_firehose_delivery_stream" "clickstream_to_raw" {
  name        = "clickstream-to-raw"
  destination = "extended_s3"

  kinesis_source_configuration {
    kinesis_stream_arn = aws_kinesis_stream.clickstream.arn
    role_arn           = aws_iam_role.firehose.arn
  }

  extended_s3_configuration {
    role_arn            = aws_iam_role.firehose.arn
    bucket_arn          = aws_s3_bucket.raw.arn
    prefix              = "clickstream/events/dt=!{timestamp:yyyy-MM-dd}/hr=!{timestamp:HH}/"
    error_output_prefix = "errors/clickstream/!{firehose:error-output-type}/dt=!{timestamp:yyyy-MM-dd}/"
    buffering_size      = 128   # MB
    buffering_interval  = 900   # seconds

    data_format_conversion_configuration {
      input_format_configuration  { deserializer { open_x_json_ser_de {} } }
      output_format_configuration { serializer   { parquet_ser_de { compression = "ZSTD" } } }
      schema_configuration {
        database_name = aws_glue_catalog_database.raw.name
        table_name    = "clickstream_events"
        role_arn      = aws_iam_role.firehose.arn
      }
    }
  }
}

DMS: databases into the lake without dump scripts

AWS DMS replicates relational sources into S3 using the engine’s change log — MySQL/Aurora binlog (must be binlog_format=ROW, with retention covering your longest outage), PostgreSQL logical replication, Oracle redo, SQL Server CDC. Of its three task modes — full load (one-time bulk seed, LOAD000... files per table), CDC only (change files carrying an Op column of I/U/D from a checkpoint), and full load + CDC — the last is the default for lake sync; validate row counts after cutover anyway. If the only consumer is Redshift BI, managed zero-ETL (Aurora/RDS→Redshift) skips the lake copy entirely.

The S3 target endpoint settings decide whether DMS emits lake-friendly files or a CSV mess. Set them explicitly:

Downstream, curated must apply the change stream: a periodic Glue job MERGEs the latest change per primary key (ordered by dms_ts) into an Iceberg table, turning a change log into a queryable current-state table — the same pattern as Debezium/Kafka CDC into a warehouse with DMS in Debezium’s role. Size the replication instance by change rate, not table size: a small dms.t3.medium (roughly $60/month) covers modest OLTP change volumes; C/R classes and Multi-AZ for busy sources, or DMS Serverless to stop guessing.

Glue: the catalog and the ETL layer

AWS Glue is three products sharing a console: the Data Catalog (metadata everything depends on), crawlers (schema inference), and ETL jobs (serverless Spark, plus workflows and Data Quality). Trust them differently: the catalog is sacred, jobs are workhorses, crawlers are useful but must be kept on a leash.

The Data Catalog: one schema truth

The catalog holds databases → tables → partitions. Each table stores location, format, SerDe, columns and partition keys; each partition is its own catalog object with its own location and — dangerously — its own copy of the schema. Athena, Redshift Spectrum, EMR, Glue jobs and Lake Formation all resolve tables here. That is the point: change schema once, every engine sees it.

aws glue create-database --database-input '{"Name":"curated_sales","Description":"Curated sales domain"}'

# Partition index on the big table (create alongside the table, not after year 2)
aws glue create-partition-index \
  --database-name curated_sales --table-name orders \
  --partition-index Keys=dt,IndexName=dt_idx

Crawlers: schema inference with a leash

A crawler walks a prefix, samples files, infers schema and writes tables/partitions to the catalog. Stable datasets often don’t need one — explicit CREATE EXTERNAL TABLE plus projection is more deterministic; crawlers earn their keep on unknown or evolving sources (DMS landing areas, third-party drops). The defaults are what cause “the crawler broke prod”:

The classic drift incident: a source starts sending customer_id as a number; the crawler (UPDATE mode) rewrites the column type; older partitions still carry the old type; Athena throws HIVE_PARTITION_SCHEMA_MISMATCH. Prevention: LOG mode plus change-controlled ALTER TABLE on anything consumers query — and Iceberg (schema versioned by column ID) where evolution is routine.

ETL jobs: serverless Spark, sized honestly

Glue jobs run Spark (or a lightweight Python shell) on managed DPUs (1 DPU = 4 vCPU + 16 GB) at $0.44/DPU-hour, billed per second with a 1-minute minimum (Glue 2.0+). Glue 4.0 = Spark 3.3; Glue 5.0 = Spark 3.5 / Python 3.11. Use 4.0+ for native --datalake-formats iceberg,delta,hudi.

The bread-and-butter lake job — compact small raw files into sized, partitioned curated Parquet:

# Glue 5.0 job: raw clickstream → curated Parquet, deduplicated, partitioned by event time
import sys
from awsglue.context import GlueContext
from awsglue.job import Job
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext

args = getResolvedOptions(sys.argv, ["JOB_NAME", "dt"])
gc = GlueContext(SparkContext())
job = Job(gc); job.init(args["JOB_NAME"], args)

src = gc.create_dynamic_frame.from_catalog(
    database="raw", table_name="clickstream_events",
    push_down_predicate=f"dt='{args['dt']}'",              # partition pruning at read
    additional_options={"groupFiles": "inPartition",        # coalesce small files on read
                        "groupSize": "134217728"})           # 128 MB groups

df = src.toDF().dropDuplicates(["event_id"])
(df.repartition("event_dt")
   .sortWithinPartitions("session_id")                       # co-locate values → row-group stats work
   .write.mode("overwrite")
   .option("compression", "zstd")
   .partitionBy("event_dt")
   .parquet("s3://mycorp-lake-curated-apsouth1-prod/clickstream/events/"))
job.commit()

aws glue start-job-run --job-name curate-clickstream \
  --arguments '{"--dt":"2026-07-06"}' \
  --worker-type G.1X --number-of-workers 10

Workflows, triggers and Data Quality

Glue workflows chain crawlers and jobs with triggers — free, and adequate for pipelines living entirely inside Glue; anything spanning services belongs in Step Functions (compared later). Glue Data Quality evaluates DQDL rulesets (RowCount > 1000, Completeness "order_id" > 0.99) against tables or inline in jobs at $0.44/DPU-hr — put a ruleset at the raw→curated boundary so garbage stops at the zone line instead of reaching dashboards.

Athena: the lake query engine

Athena is serverless, Trino-based (engine v3) SQL over catalog tables. No cluster to size; you pay $5 per TB scanned, 10 MB minimum per query (us-east-1). DDL is free; failed queries are free; cancelled queries bill for bytes already scanned. That pricing model is the whole game — every optimisation below cuts bytes scanned, and the same moves make queries faster because scan time tracks scan bytes.

Partition management: from MSCK to projection

Athena prunes only partitions it knows about. The four ways to keep it informed, in ascending production-worthiness for time-series layouts: MSCK REPAIR TABLE (walks the whole prefix tree — slow, dev-only), explicit ALTER TABLE ADD PARTITION from ingestion hooks (instant, free), a scheduled crawler (fine when it exists anyway; 10-minute billing minimum per run), and partition projection — the production default for date/hour/enum layouts. Projection replaces per-partition catalog objects with rules evaluated at plan time: new data is queryable the second the object lands, GetPartitions throttling disappears, and effectively-million-partition tables plan in milliseconds. Configuration lives in TBLPROPERTIES:

CREATE EXTERNAL TABLE curated_sales.orders (
  order_id      string,
  customer_id   string,
  store_id      string,
  amount        decimal(12,2),
  event_ts      timestamp
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://mycorp-lake-curated-apsouth1-prod/sales/orders/'
TBLPROPERTIES (
  'projection.enabled'          = 'true',
  'projection.dt.type'          = 'date',
  'projection.dt.range'         = '2024-01-01,NOW',
  'projection.dt.format'        = 'yyyy-MM-dd',
  'projection.dt.interval'      = '1',
  'projection.dt.interval.unit' = 'DAYS',
  'storage.location.template'   = 's3://mycorp-lake-curated-apsouth1-prod/sales/orders/dt=${dt}/'
);

Two caveats: projection is an Athena-side feature — Redshift Spectrum and Glue Spark still need real catalog partitions (or Iceberg, which removes this problem class entirely); and a query with no filter on a projected column enumerates the entire projected range, so keep ranges honest.

CTAS and INSERT: transformation inside Athena

CTAS (CREATE TABLE AS SELECT) writes query results as a new table — the cheapest format-conversion and repartitioning tool, within one limit: 100 partitions max per CTAS or INSERT INTO statement. A three-year daily backfill is one CTAS for the first 100 days plus a scripted INSERT INTO loop (or a Glue job for very large rewrites).

CREATE TABLE curated_sales.orders_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'ZSTD',
  external_location = 's3://mycorp-lake-curated-apsouth1-prod/sales/orders_v2/',
  partitioned_by = ARRAY['dt'],
  bucketed_by = ARRAY['customer_id'], bucket_count = 16
) AS
SELECT order_id, customer_id, store_id,
       CAST(amount AS decimal(12,2))                          AS amount,
       CAST(from_iso8601_timestamp(ts) AS timestamp)          AS event_ts,
       date_format(from_iso8601_timestamp(ts), '%Y-%m-%d')    AS dt
FROM raw.orders_json
WHERE dt BETWEEN '2026-03-29' AND '2026-07-06';   -- ≤ 100 partition values

Workgroups: the cost circuit-breakers

Workgroups separate teams, result locations and — critically — enforce scan limits. Run one per team/tool with limits on; this converts “someone SELECT *'d three years of events” from a $900 surprise into a cancelled query.

aws athena create-work-group --name analysts \
  --configuration 'ResultConfiguration={OutputLocation=s3://mycorp-lake-scratch-apsouth1-prod/athena/analysts/},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=1099511627776,EngineVersion={SelectedEngineVersion=Athena engine version 3}'

What actually cuts bytes scanned

Redshift: when the lake needs a warehouse

Athena’s per-scan model is unbeatable for ad-hoc and scheduled batch SQL — and wrong for a 200-user BI portal issuing the same joins every 30 seconds with a sub-second SLA. That workload wants pre-loaded, cached, materialized-view-accelerated compute: Redshift. The boundary is a workload decision, not a religion:

The hybrid is the point: one copy of truth in the lake, only hot marts loaded into Redshift, and Redshift Spectrum joining local tables to lake tables through the same Glue catalog for cold history — orders_hot local, older years external, one UNION ALL view over both.

RA3 and Serverless: compute with managed storage

RA3 nodes decouple compute from Redshift Managed Storage (RMS): data lives in S3-backed managed storage (~$0.024/GB-month) with hot blocks cached on local NVMe, so you size nodes for query throughput, not disk. (Legacy DC2 tied storage to nodes; new designs should not start there.)

Serverless changes the economics for part-time warehouses: an 8-RPU workgroup active 6 h/day ≈ 8 × $0.36 × 180 h ≈ $520/month, $0 when idle — versus ~$793/month for the smallest always-on xlplus node; steady 24×7 clusters flip the other way once reserved pricing applies. The operational levers that matter: concurrency scaling (transient clusters absorb queue spikes; one free hour accrues per day), auto WLM with query priorities, and data sharing to expose live tables to other clusters/workgroups without copies.

Spectrum, COPY and modeling: how data reaches and serves

Data reaches Redshift four ways: Spectrum external tables (the cluster queries S3 through the Glue catalog live — $5/TB scanned on provisioned clusters, included in Serverless RPUs — for cold history and rarely-joined dimensions), COPY from S3 (parallel bulk load of hot marts on a batch cadence, costing only cluster time), zero-ETL (managed near-real-time CDC from Aurora/RDS/DynamoDB, no pipeline to build), and streaming ingestion (a materialized view directly over Kinesis/MSK for seconds-fresh operational dashboards).

-- The lake, mounted: external schema over the shared Glue catalog
CREATE EXTERNAL SCHEMA lake
FROM DATA CATALOG DATABASE 'curated_sales'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-lake-read';

-- Hot mart loaded from curated Parquet (COPY reads Parquet natively)
COPY mart.orders_hot
FROM 's3://mycorp-lake-curated-apsouth1-prod/sales/orders/dt=2026-07-06/'
IAM_ROLE 'arn:aws:iam::111122223333:role/redshift-lake-read'
FORMAT AS PARQUET;

-- One seamless view: hot local + cold lake
CREATE VIEW analytics.orders_all AS
SELECT * FROM mart.orders_hot
UNION ALL
SELECT * FROM lake.orders WHERE dt < '2026-01-01'
WITH NO SCHEMA BINDING;

-- Repeated dashboard aggregate → materialized view, auto-maintained
CREATE MATERIALIZED VIEW mart.daily_store_sales
AUTO REFRESH YES AS
SELECT store_id, dt, COUNT(*) AS orders, SUM(amount) AS revenue
FROM mart.orders_hot GROUP BY store_id, dt;

COPY performance rules: point it at a prefix of many similar-sized files (~100–250 MB compressed, count a multiple of the cluster’s slice count — never one giant single-slice file). Modeling on RA3: DISTSTYLE AUTO/SORTKEY AUTO are the right defaults; override with DISTKEY on the dominant join key and a date-led compound sort key only where AUTO hasn’t converged (interleaved sort keys are legacy — avoid). Materialized views with auto refresh, plus Redshift’s automated MVs, routinely take dashboard p95 from seconds to tens of milliseconds — the answer to “we re-aggregate the same table 40,000 times a day”.

Lake Formation governance and the Iceberg lakehouse

Lake Formation: permissions at the table layer

Without Lake Formation (LF), lake access control means IAM policies naming S3 prefixes and Glue APIs — workable for one team, unmanageable at ten. LF moves the grant to where analysts think: database, table, column, row. You register S3 locations, and integrated engines (Athena, Spectrum, Glue, EMR, QuickSight) enforce LF grants by vending scoped temporary credentials — callers never hold S3 permissions at all. LF permissions cost nothing extra.

That last row is the gotcha that defines every LF rollout. For backwards compatibility, new catalogs grant a virtual principal IAMAllowedPrincipals full access to everything — IAM alone still decides, and LF grants are decorative until you revoke it and clear the “use only IAM access control” defaults. Do it database-by-database (hybrid access mode exists precisely for this), in the order that works: register locations → create LF-tags → tag resources → grant roles by tag → revoke IAMAllowedPrincipals on the pilot database → verify with a canary principal → repeat.

# Register the curated bucket under LF (role LF uses to vend credentials)
aws lakeformation register-resource \
  --resource-arn arn:aws:s3:::mycorp-lake-curated-apsouth1-prod \
  --role-arn arn:aws:iam::111122223333:role/lf-register-role

# Tag-based access: define once, tag resources, grant by tag
aws lakeformation create-lf-tag --tag-key domain --tag-values sales,customer,ops
aws lakeformation create-lf-tag --tag-key sensitivity --tag-values public,internal,pii

aws lakeformation add-lf-tags-to-resource \
  --resource '{"Table":{"DatabaseName":"curated_sales","Name":"orders"}}' \
  --lf-tags '[{"TagKey":"domain","TagValues":["sales"]},{"TagKey":"sensitivity","TagValues":["internal"]}]'

# Analysts: SELECT on everything tagged domain=sales AND sensitivity=internal
aws lakeformation grant-permissions \
  --principal DataLakePrincipalIdentifier=arn:aws:iam::111122223333:role/analyst-sales \
  --permissions SELECT \
  --resource '{"LFTagPolicy":{"ResourceType":"TABLE","Expression":[
      {"TagKey":"domain","TagValues":["sales"]},
      {"TagKey":"sensitivity","TagValues":["internal"]}]}}'

LF-tags invert the grant model: instead of role × table (multiplicative growth), you tag tables once and grant role × tag-expression (linear). A new table tagged domain=sales is instantly visible to every principal holding the sales grant. Data filters add row/cell security on top — country = 'IN' with card_number excluded gives a partner team one governed slice of one copy. Design the tag ontology before granting anything:

Iceberg: tables that behave like tables

Plain Hive-style Parquet tables are append-only directories: no transactional updates or deletes, positional schema changes, and concurrent writers can corrupt each other. Apache Iceberg fixes this with a metadata layer — a versioned tree (metadata.json → manifest list → manifests → data files) where every commit atomically produces a new snapshot. Athena engine v3 reads and writes Iceberg; Glue, EMR Spark and Firehose speak it natively; the Glue catalog stores it as a first-class table type.

Adopt Iceberg for CDC-fed current-state tables (MERGE per batch), GDPR/DPDP erasure (DELETE WHERE customer_id = ... instead of rewriting partitions), frequently-corrected facts, concurrent writers, and tables whose schema will evolve. Skip it for immutable append-only event history with a stable schema — plain partitioned Parquet plus projection is simpler and maintenance-free.

-- Iceberg in Athena: create, upsert CDC, time-travel, maintain
CREATE TABLE curated_customer.profiles (
  customer_id string, email string, tier string,
  country string, updated_at timestamp)
PARTITIONED BY (bucket(16, customer_id))
LOCATION 's3://mycorp-lake-curated-apsouth1-prod/customer/profiles/'
TBLPROPERTIES ('table_type'='ICEBERG', 'format'='PARQUET',
               'vacuum_max_snapshot_age_seconds'='432000');

MERGE INTO curated_customer.profiles t
USING staging.profile_changes s ON t.customer_id = s.customer_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED THEN UPDATE SET email = s.email, tier = s.tier,
                             country = s.country, updated_at = s.dms_ts
WHEN NOT MATCHED THEN INSERT (customer_id, email, tier, country, updated_at)
                      VALUES (s.customer_id, s.email, s.tier, s.country, s.dms_ts);

SELECT * FROM curated_customer.profiles
FOR TIMESTAMP AS OF TIMESTAMP '2026-07-01 00:00:00 UTC'
WHERE customer_id = 'C-10442';

Iceberg’s tax is maintenance: every MERGE writes small delta files and a new snapshot; unmaintained tables accumulate thousands of small files and stale snapshots until queries crawl and storage doubles. Schedule two operations per table: daily compaction of hot partitions with OPTIMIZE t REWRITE DATA USING BIN_PACK WHERE dt >= ... (rewrites small and delete-laden files into target-size Parquet), and VACUUM t (expires snapshots past vacuum_max_snapshot_age_seconds and removes unreferenced files — which also ends time travel past that age). If you would rather not own that, the Glue catalog offers automatic compaction for Iceberg tables, and S3 Tables (managed Iceberg buckets) automate compaction, snapshot expiry and GC entirely — trading control for zero ops.

For the full operational treatment — writer tuning, compaction strategies, migration from Hive tables — see Apache Iceberg on S3 with Glue. The zone model with Iceberg curated tables is exactly the lakehouse pattern covered in medallion architecture; the enterprise AWS lakehouse article scales it to multi-account.

Orchestration and consumption: Step Functions, MWAA and QuickSight

Choosing the orchestrator

Pipelines are DAGs: ingest → validate → transform → compact → refresh marts → refresh dashboards. Four reasonable engines run DAGs on AWS; teams go wrong by picking the heaviest one first.

Rule of thumb: start with Step Functions; adopt MWAA only with an Airflow-fluent team and dozens of interdependent DAGs — an idle MWAA environment costs more per month than many small lakes’ entire Athena bill. A Standard state machine chaining a Glue job and an Athena statement with .sync (Step Functions polls completion for you):

{
  "StartAt": "CurateClickstream",
  "States": {
    "CurateClickstream": {
      "Type": "Task",
      "Resource": "arn:aws:states:::glue:startJobRun.sync",
      "Parameters": {"JobName": "curate-clickstream",
                     "Arguments": {"--dt.$": "$.dt"}},
      "Retry": [{"ErrorEquals": ["States.ALL"], "MaxAttempts": 2, "BackoffRate": 2}],
      "Next": "RefreshMart"
    },
    "RefreshMart": {
      "Type": "Task",
      "Resource": "arn:aws:states:::athena:startQueryExecution.sync",
      "Parameters": {
        "QueryString": "INSERT INTO consumption.daily_kpis SELECT ...",
        "WorkGroup": "pipelines"
      },
      "End": true
    }
  }
}

QuickSight: the last mile

QuickSight connects to Athena and Redshift natively and adds SPICE — an in-memory columnar engine that caches datasets so dashboards stop re-querying the lake. That is architecture, not convenience: 500 viewers on SPICE cost zero Athena scans; the same viewers in direct-query mode would issue hundreds of thousands of Athena queries a month.

Architecture at a glance

Read the diagram left to right — it is the article in one picture. Sources (OLTP via DMS CDC, application/clickstream events) enter the ingestion tier, where Kinesis Data Streams is the durable, replayable transport and Firehose is the file-size governor — buffering to 128 MB, converting JSON to Parquet against the Glue schema, writing Hive-partitioned objects into the raw zone. The lake tier is zoned S3 with the Glue Data Catalog as the schema truth every engine resolves, and Lake Formation vending scoped credentials so access is granted by LF-tag at table/column/row level rather than by bucket policy. The query tier reads one copy two ways: Athena for scan-metered ad-hoc SQL with partition projection, Redshift RA3/Serverless for the hot high-concurrency BI slice — Spectrum joining back to the lake so history is never copied. Consumption is QuickSight (SPICE datasets refreshed by the pipeline, so viewers never touch the $5/TB meter) and EMR/Spark on the same Iceberg tables.

The numbered badges mark the six decisions this article keeps returning to: buffer sizing at Firehose (1), partition-or-pay at S3 (2), catalog hygiene at Glue (3), single-point governance at Lake Formation (4), the bytes-scanned meter at Athena (5), and the lake-vs-warehouse boundary at Redshift (6).

Real-world scenario

Meridian Retail, a fictional-but-familiar omnichannel retailer (1,800 stores, one e-commerce platform, ap-south-1), built its lake in a hurry before Diwali: clickstream and POS events (~4,000 events/s, ~415 GB/day of JSON) through Firehose on default buffers, dynamic partitioning by store_id “so store queries would be fast”, a crawler on a 15-minute cron, forty analysts on Athena with no workgroups. It worked in the demo. By week six the 09:00 IST merchandising dashboards took 40+ seconds per visual, the Athena line hit $6,800/month, and the raw bucket was gaining 1.9 million objects a day averaging 218 KB — 1,800 store partitions × 24 hours × a flush every ~60 seconds. The crawler, meanwhile, re-typed coupon_code from string to bigint one night (a day of numeric-only coupons), and every query touching older partitions failed with HIVE_PARTITION_SCHEMA_MISMATCH until an engineer hand-edited the table at 01:30.

The fix took two weeks and no new services. Ingestion: Firehose buffers to 128 MB/900 s, dynamic partitioning removed — partitioning switched to dt=/hr= from event time, store_id demoted to an ordinary column sorted within files so row-group stats still prune store queries. A nightly Glue job (10 × G.1X, ~22 minutes) compacted the swamp into 256–512 MB ZSTD Parquet, hourly incremental compaction kept it that way; object creation fell to ~4,200/day. Catalog: the crawler moved to LOG mode over raw landing only; curated tables became explicit DDL with partition projection, so the 15-minute crawler cron ($21/day of billing minimums) disappeared along with GetPartitions throttling. Governance: LF-tags (domain, sensitivity=pii on card-holder columns) replaced eleven bucket policies; IAMAllowedPrincipals was revoked database-by-database in hybrid mode. Consumption: analysts got workgroups with a 500 GB per-query cutoff; the six merchandising dashboards moved to SPICE refreshed by the pipeline’s final Step Functions state; the two genuinely hot marts (store-day sales, inventory positions) moved into an 8-RPU Redshift Serverless workgroup, Spectrum unioning history from the lake.

Results, month over month: dashboard p50 from 42 s to 1.8 s (SPICE) and ad-hoc Athena p50 from 31 s to 3.4 s; Athena spend from $6,800 to $1,450 (Parquet + pruning + SPICE absorbing repeat queries); total platform cost from ~$9,900 to ~$4,100/month (~₹8.5 lakh → ~₹3.5 lakh) — while ingest volume grew 12%. The postmortem’s one-line lesson went on the team wiki: defaults are for demos; file size, partitions and the catalog are architecture.

Advantages and disadvantages

The honest summary: the lake pattern wins on cost, scale and flexibility and charges for it in architectural discipline; a single-product warehouse (Snowflake+dbt, BigQuery, all-in Redshift) buys simplicity and charges in pricing and lock-in. Below ~1 TB and one team, the warehouse alone is genuinely simpler; past tens of TB and three teams, the lake pattern is usually the only affordable shape.

Hands-on lab

Build the miniature version end to end: zoned bucket, raw JSON, Athena with projection, CTAS to Parquet, an Iceberg table with MERGE, and teardown. Cost: well under $1 (Athena minimums + pennies of S3). Prereqs: AWS CLI v2 with a profile that can create S3/Glue/Athena resources.

# 1. Buckets (use your own suffix; names are global)
export ACC=$(aws sts get-caller-identity --query Account --output text)
aws s3 mb s3://lakelab-raw-$ACC && aws s3 mb s3://lakelab-curated-$ACC && aws s3 mb s3://lakelab-scratch-$ACC

# 2. Generate three days of fake orders as JSON Lines, partitioned by dt
for d in 2026-07-04 2026-07-05 2026-07-06; do
  for i in $(seq 1 2000); do
    echo "{\"order_id\":\"O-$d-$i\",\"customer_id\":\"C-$((RANDOM%500))\",\"store_id\":\"S-$((RANDOM%50))\",\"amount\":$((RANDOM%9000+100)).50,\"ts\":\"${d}T$(printf '%02d' $((RANDOM%24))):15:00Z\"}"
  done > orders-$d.json
  aws s3 cp orders-$d.json s3://lakelab-raw-$ACC/sales/orders/dt=$d/
done

# 3. Glue database + Athena workgroup with a scan cap
aws glue create-database --database-input '{"Name":"lakelab"}'
aws athena create-work-group --name lakelab \
  --configuration "ResultConfiguration={OutputLocation=s3://lakelab-scratch-$ACC/results/},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=10737418240"

-- 4. Raw table with partition projection (run in the lakelab workgroup)
CREATE EXTERNAL TABLE lakelab.orders_raw (
  order_id string, customer_id string, store_id string,
  amount double, ts string)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://lakelab-raw-REPLACE_ACC/sales/orders/'
TBLPROPERTIES (
  'projection.enabled'='true', 'projection.dt.type'='date',
  'projection.dt.range'='2026-07-01,NOW', 'projection.dt.format'='yyyy-MM-dd',
  'storage.location.template'='s3://lakelab-raw-REPLACE_ACC/sales/orders/dt=${dt}/');

-- 5. Query raw; note "Data scanned" in the result footer
SELECT dt, COUNT(*) orders, SUM(amount) revenue
FROM lakelab.orders_raw WHERE dt = '2026-07-05' GROUP BY dt;

-- 6. CTAS to partitioned ZSTD Parquet (the format conversion in one statement)
CREATE TABLE lakelab.orders_parquet
WITH (format='PARQUET', write_compression='ZSTD',
      external_location='s3://lakelab-curated-REPLACE_ACC/sales/orders/',
      partitioned_by=ARRAY['dt']) AS
SELECT order_id, customer_id, store_id, amount,
       CAST(from_iso8601_timestamp(ts) AS timestamp) AS event_ts, dt
FROM lakelab.orders_raw;

-- 7. Re-run step 5 against orders_parquet: same answer, a fraction of the bytes.
--    SELECT two columns and compare "Data scanned" again — that delta is your bill.

-- 8. Iceberg: current-state table + an upsert + time travel
CREATE TABLE lakelab.orders_ice (
  order_id string, customer_id string, store_id string,
  amount double, event_ts timestamp, dt string)
PARTITIONED BY (dt)
LOCATION 's3://lakelab-curated-REPLACE_ACC/sales/orders_ice/'
TBLPROPERTIES ('table_type'='ICEBERG');

INSERT INTO lakelab.orders_ice SELECT * FROM lakelab.orders_parquet;

MERGE INTO lakelab.orders_ice t
USING (SELECT 'O-2026-07-05-7' order_id, 999.99 amount) s
ON t.order_id = s.order_id
WHEN MATCHED THEN UPDATE SET amount = s.amount;

SELECT order_id, amount FROM lakelab.orders_ice WHERE order_id='O-2026-07-05-7';
-- Then look at history:
SELECT * FROM "lakelab"."orders_ice$snapshots" ORDER BY committed_at DESC LIMIT 5;

# 9. Teardown — leave nothing metered behind
aws glue delete-database --name lakelab
aws athena delete-work-group --work-group lakelab --recursive-delete-option
for b in raw curated scratch; do
  aws s3 rb s3://lakelab-$b-$ACC --force
done

Expected observations: step 5 scans ~1–2 MB of JSON (billed at the 10 MB minimum — the minimum exists to make you batch small queries); step 7 scans ~10–20× less for the same answer, and selecting two columns cuts it again; the Iceberg $snapshots metadata table shows one snapshot per write — that is your time-travel history and also the thing VACUUM exists to prune.

Common mistakes & troubleshooting

The playbook. Symptoms are what the on-call sees; confirm before fixing — half of these share symptoms.

The Athena error strings worth memorising, since they name the layer at fault:

Best practices

1. Decide file size at ingestion — 128–512 MB Parquet targets, Firehose buffers at maximum, compaction scheduled from day one. Retrofitting is a hundred times harder.
2. Zone with different buckets and different writers: only ingestion writes raw, only ETL writes curated, consumers write nowhere. Enforce with IAM/LF, not convention.
3. Partition by event date (and hour only above ~50 GB/day); never by high-cardinality keys — sort within files instead so row-group stats do that work.
4. Prefer explicit DDL + partition projection over crawlers for anything consumers query; crawl only landing areas, in LOG mode, with table-level config set.
5. One Glue catalog, one schema truth — no side-car Hive metastores, no per-tool table definitions; share cross-account via Lake Formation/RAM.
6. Cap every human with Athena workgroups (BytesScannedCutoffPerQuery, enforced config) and alarm on per-workgroup DataScannedInBytes at 80% of budget.
7. Adopt Iceberg for mutable tables (CDC state, GDPR-deletable data) and schedule its maintenance (OPTIMIZE/VACUUM) the same day you create the table.
8. Keep the warehouse a cache, not a fork: Redshift holds hot marts; history stays in the lake behind Spectrum; nothing exists only in Redshift.
9. Quarantine, never drop: Firehose ErrorOutputPrefix, DLQs on stream consumers, Glue Data Quality gates between zones — with alarms on all three.
10. Make pipelines idempotent and re-runnable by partition (--dt parameters, deterministic outputs, overwrite-by-partition or MERGE) — backfills and replays are then boring.
11. Tag spend to the byte: cost allocation tags on buckets/jobs/workgroups per domain; publish a monthly $-per-domain report — behaviour changes fast when scans have names.
12. Lifecycle deliberately: Intelligent-Tiering for unpredictable curated data, explicit transitions for raw, and never archive partitions that analysts still query into Glacier FR/DA without a restore runbook.

Security notes

Least privilege has three layers here, and each catches what the previous misses:

Beyond the table: encrypt the streams (KDS/Firehose with the lake CMK) so data is protected before it ever rests; turn on CloudTrail S3 data events for curated/PII buckets and alert on direct-S3 reads that bypass the query engines — that is someone evading LF; run Macie periodically against raw, because PII always leaks into raw eventually; and treat Athena result buckets as sensitive — results contain the data, and teams routinely leave 90-day-old PII extracts readable account-wide. Audit posture pairs with CloudTrail/Config compliance patterns.

Cost & sizing

What drives the bill, per layer — and the lever that moves it:

The Meridian Retail steady state (415 GB/day JSON ingest, ~83 GB/day as Parquet, 12-month hot history, 40 analysts, 6 dashboards, 80 viewers) — us-east-1 list prices, indicative; ap-south-1 runs a few percent higher; ₹ at ~86/USD:

Right-sizing rules that fall out of the model: the ingest pipeline (KDS+Firehose ≈ $950) is the floor — it scales with data, not usage, so fight for it first; Athena at $200 versus its pre-optimisation $6,800 is the proof that format/partition work pays permanent dividends; Redshift Serverless earns its $520 only while dashboards genuinely need sub-second — revisit quarterly; and the QuickSight reader cap makes viewer growth nearly free. For learning, the free corners are Glue’s first million catalog objects, Athena’s pennies-per-lab minimums, QuickSight’s 30-day trial and S3’s 5 GB — the lab above exercises all of them.

Interview & exam questions

Mapped to SAA-C03 (analytics design), DEA-C01 (Data Engineer Associate — this article is essentially its core domain), and SAP-C02 (cost/migration trade-offs).

1. Why does Parquet cut Athena costs so dramatically versus JSON? Columnar layout lets the engine read only referenced columns; per-column compression shrinks bytes 4–8×; row-group min/max statistics skip data pages that can’t match predicates. Together a typical BI query touches 2–10% of the JSON-equivalent bytes, and Athena bills bytes scanned.
2. What is partition projection and when do you need it? Table properties from which Athena computes valid partitions (date ranges, enums, integers) at plan time instead of fetching them from the catalog. Use it for high-partition-count time-series tables: zero GetPartitions calls, no MSCK/crawler lag, new data instantly queryable. Spectrum and Spark ignore it — they need real partitions or Iceberg.
3. Kinesis Data Streams vs Firehose — when each? KDS is durable, ordered, replayable transport for multiple/custom consumers (fan-out, replay, per-key ordering); Firehose is fully-managed delivery — buffer, transform, convert, land in S3/Redshift/OpenSearch — with no replay or custom consumers. Classic lake pattern: KDS in front when real-time consumers exist, Firehose behind it as the S3 writer.
4. Explain the small-files problem and three fixes. Each object costs a request, metadata parse and a scheduling slot, so millions of KB-scale files make planning dominate query time and inflate request costs. Fixes: maximise Firehose buffers (128 MB/900 s), scheduled compaction (Glue job or Iceberg OPTIMIZE ... BIN_PACK), and avoid high-cardinality dynamic partitioning that fragments buffers.
5. Athena vs Redshift Spectrum vs Redshift local — pick per workload. Athena: standalone, serverless, ad-hoc/scheduled SQL over the catalog. Spectrum: same S3 data but joined from Redshift with its local tables — for extending a warehouse to lake history. Local Redshift: hot, high-concurrency, sub-second repeated BI. Hot slice local, cold history external, one view over both.
6. Why RA3 over DC2, in one sentence each? RA3 separates compute from Redshift Managed Storage so you scale nodes for CPU/concurrency while storage grows independently (and cheaper); DC2 welds them so growing disk means buying compute you don’t need.
7. You enabled Lake Formation but permissions don’t seem enforced. Why? The default IAMAllowedPrincipals grant gives every IAM-authorised principal implicit access, making LF grants decorative until you revoke it (per resource or via settings) — after which only explicit LF grants (ideally LF-tags) apply. Hybrid access mode lets you migrate database-by-database.
8. What do Iceberg tables add over Hive-style Parquet, and what do they cost you? ACID commits with snapshots (time travel), MERGE/UPDATE/DELETE, schema evolution by column ID, hidden/evolvable partitioning, manifest-level file pruning, safe concurrent writers. Cost: metadata/maintenance overhead — compaction and snapshot expiry (OPTIMIZE/VACUUM) must be scheduled or the table degrades.
9. How do Glue job bookmarks work? The job persists state about processed input (S3 objects/timestamps; JDBC key bounds) per bookmark name; the next run reads only new data. Disable or rewind for backfills; they make incremental pipelines idempotent-ish but your writes must still be partition-overwrite or MERGE to be safely re-runnable.
10. When is Kinesis on-demand the wrong choice? Steady, predictable, well-utilised throughput — provisioned shards cost several times less per GB at high duty cycle. On-demand wins for spiky, unpredictable or low-volume streams, and as the default until you know the shape.
11. How do you hard-cap Athena spend for a team? A dedicated workgroup: BytesScannedCutoffPerQuery kills runaway queries, EnforceWorkGroupConfiguration stops overrides, per-workgroup result location + lifecycle, and CloudWatch DataScannedInBytes alarms against a monthly budget. Plus SPICE/result-reuse so dashboards stop hitting the meter.
12. A CTAS backfill of 3 years of daily partitions fails immediately — why? CTAS/INSERT INTO write at most 100 partitions per statement. Chunk the backfill: CTAS for the first ≤100 days, then looped INSERT INTO ... WHERE dt BETWEEN ... chunks (or do the rewrite in a Glue job where no such limit applies).

Quick check

1. Your raw zone receives 300 GB/day of JSON and analysts query it directly “temporarily”. Name the two structural problems and the first fix.
2. Which Firehose settings decide output file size, and what are the production values for a lake?
3. A query filters WHERE date(dt) >= date '2026-07-01' on a dt-partitioned table and scans the whole table. Why?
4. When does Redshift Serverless beat a provisioned ra3.xlplus node, numerically?
5. What two maintenance operations keep an Iceberg table healthy, and what happens if you skip them?

Answers

1. Row-format scans (5–10× the bytes of Parquet at $5/TB) and a schema contract you now can’t change because consumers depend on raw. Fix: CTAS/Glue into a curated Parquet table and repoint consumers; keep raw for replay only.
2. Buffer size (1–128 MB) and buffer interval (60–900 s), plus format conversion determining Parquet output; production: 128 MB / 600–900 s with conversion on — and no high-cardinality dynamic partitioning.
3. Wrapping the partition column in a function prevents partition pruning — Athena can’t map date(dt) back to prefix values, so it scans everything. Rewrite the predicate against raw values: dt >= '2026-07-01'.
4. Serverless ≈ RPUs × $0.36 × active hours; xlplus ≈ $793/month always-on. 8 RPU active 6 h/day ≈ $520 wins; the same 8 RPU busy 24×7 ≈ $2,073 loses badly — the crossover is roughly 9–10 active hours/day at that size.
5. Compaction (OPTIMIZE ... REWRITE DATA USING BIN_PACK) and snapshot expiry/orphan cleanup (VACUUM). Skipping them: small delta files accumulate until queries crawl, and storage grows unbounded with stale snapshots (time travel is the feature and the bill).

Glossary

• Data lake: Zoned object storage holding raw through modelled data in open formats, queried in place by multiple engines.
• Lakehouse: Lake + table format (Iceberg/Delta/Hudi) giving warehouse semantics — ACID, mutation, evolution — over open files.
• Zone (raw/curated/consumption): Contract level of data in a bucket: as-arrived → cleaned/typed → modelled for consumers.
• Hive-style partitioning: Encoding partition values in paths (dt=2026-07-06/) so engines prune directories.
• Partition projection: Athena table properties that compute partitions from rules instead of catalog entries.
• Parquet: Columnar, compressed, splittable file format; the lake’s default at rest. Its internal row groups carry min/max stats that let engines skip pages.
• Apache Iceberg: Open table format adding snapshots (time travel), ACID commits, hidden partitioning and safe schema evolution over Parquet.
• Glue Data Catalog: Regional metadata store (databases/tables/partitions) shared by Athena, Spectrum, Glue, EMR.
• DPU / RPU: Billing units of Glue (4 vCPU + 16 GB Spark capacity) and Redshift Serverless (compute slice) respectively.
• Shard: KDS capacity unit — 1 MB/s or 1,000 records/s in, 2 MB/s out (enhanced fan-out adds 2 MB/s per consumer).
• CDC (change data capture): Replicating databases by reading their change logs (binlog/WAL) — DMS’s continuous mode.
• Spectrum: Redshift’s engine for querying catalog/S3 external tables alongside local tables.
• LF-tags: Lake Formation tag-based access control — grant by tag expression instead of per resource.
• SPICE: QuickSight’s in-memory dataset engine; absorbs dashboard queries so the lake isn’t re-scanned.

Next steps

• Go deep on the table format that makes the lakehouse work: Apache Iceberg on S3 with Glue — deployment, compaction, catalog.
• Zoom out to the multi-account, multi-domain version of this design: Enterprise AWS lakehouse reference architecture and its conceptual cousin, medallion architecture for streaming + batch.
• Wire OLTP sources in properly: DynamoDB Streams CDC pipelines and Debezium/Kafka CDC into warehouses.
• Compare the ecosystem alternatives before someone asks in the steering meeting: Snowflake + dbt on AWS, BigQuery as the warehouse, and ADLS Gen2 medallion on Azure.
• Organise team ownership of domains before the lake becomes a bottleneck: data mesh and decentralized data ownership.