Day 5 - Week 9 Comprehensive Review: Data Architecture as One Picture

Week 9 covered "where data is stored, how it's processed, who sees what, and how it flows in real-time." Memorizing individual services alone won't pass SAP exams. Exams always ask "under these constraints (cost, real-time, operational burden, portability, permission granularity), what is optimal?" and only one of two similar-looking services satisfies all conditions. Today we consolidate the week's pieces into one unified architecture picture and sharpen boundary-drawing through scenario drills.

Data Architecture in One Frame

[Ingest]   Kinesis (KDS/Firehose) / MSK / DMS
   ↓
[Store]    S3 Raw → Curated → Trusted (Parquet + partitions + compression)
   ↓                         │
[Catalog]  Glue Data Catalog (Hive Metastore compatible — hub for all engines)
   ↓
[Process]  Glue (serverless ETL) / EMR (full Spark·Hadoop) / Athena (SQL CTAS)
   ↓
[Load]     Redshift (RA3·Serverless) / OpenSearch / S3 Curated
   ↓
[Analyze]  Athena / Redshift (Spectrum·Federated·Sharing·Zero-ETL) / QuickSight / SageMaker
   │
[Perms]    Lake Formation (rows·columns·cells, LF Tag ABAC, RAM cross-account)
[Orch]     MWAA (Airflow DAG) / Step Functions
[Govern]   DataZone (marketplace) / Glue Data Quality (DQDL)

The core of this picture: Glue Data Catalog is the central hub. The same Parquet file in S3 is seen as the same table by Athena (SQL), EMR Spark (transform), and Redshift Spectrum (join). This separation of unified metadata with swappable processing engines is the essence of the lakehouse.

Boundary Clarification — 7 Pairs of Confusions the Exam Exploits

Each row is roughly one exam question. Two choices both "look right," but a single condition (OpDB load avoidance? Portability? On-demand cost?) breaks the tie.

Cost · Real-Time · Portability — Reframed on Three Axes

Cost Axis: Sporadic, variable workloads always favor serverless/elastic (Glue, Athena, EMR Serverless, Redshift Serverless, MSK Serverless, Step Functions); steady high volume favors reserved/provisioned (RA3 reserved, EMR EC2 Spot Fleet) cheaper. "Zero cost when not running" is the serverless keyword; "24/7 packed normal load" points to reserved.

Real-Time Axis: Batch (Glue, EMR, Athena CTAS, minutes to hours) → Micro-batch (Glue Streaming, Firehose, seconds to minutes) → Real-time (KDS, MSK, milliseconds to seconds). Real-time operational DB analysis: Zero-ETL (seconds-to-tens-of-seconds CDC) freshest without ETL code.

Portability Axis: Open-source standards (MSK=Kafka, MWAA=Airflow, EMR=Spark/Hadoop, Iceberg/Hudi/Delta) ease cross-cloud and on-prem migration; AWS proprietary (Kinesis, Step Functions, Redshift) offer deep integration but vendor lock-in. Multi-cloud strategy tips toward standards.

💡 Related Theory: These three axes' trade-offs connect to CAP/PACELC in distributed systems. PACELC: "Under partition, choose availability vs consistency; in normal operation, choose latency vs consistency." Zero-ETL and CDC replicate introduce intentional delay (eventual consistency) between operational DB and analytical DW to gain availability and isolation; Federated Query sacrifices OpDB isolation for consistency (freshness). "Freshness vs isolation load" is really the data pipeline version of consistency vs availability trade-off.

🔍 Deeper Dive: SAP often layers data topics over multi-account, multi-region contexts. Central data lake in separate data account with departmental accounts sharing via RAM+LF (data mesh pattern), Redshift Data Sharing cross-region/cross-account sharing without replication, Glue Catalog cross-account reference — these are recurring patterns. Organizations, SCP, and Control Tower form the governance backbone. Habit: don't view one data service alone but "how does this share/isolate/control in multi-account organizations" — this habit is key to solving Pro-tier scenarios.

🎯 Scenario: "Global firm operates Redshift in US, Europe, Asia regions. Head BI team wants to unified-analyze sales across three regions without moving/replicating data, can't impact regional cluster performance. Optimal?" — Answer: Redshift Data Sharing (cross-Region/cross-Account). Because data lives in RMS (S3-backed), producer (each region) shares data to consumer (head BI) cluster without copying; independent compute isolates workloads (no performance impact). Centralizing data via ETL/replicate costs, latency, sync burden far exceed this solution; Spectrum is for S3 external data, not internal table sharing mechanism.

Common Pitfalls

⚠️ Pitfall: "Serverless is always cheaper" — Redshift Serverless, EMR Serverless, MSK Serverless all cheaper on variable loads, but provisioned/reserved cheaper on steady heavy load. Strength is not absolute cost per unit but "zero when idle" elasticity.

⚠️ Pitfall: "Federated Query = Zero-ETL" — Both "no ETL, analyze operational DB in Redshift." But Federated Query directly queries OpDB, loading it, Zero-ETL replicates via CDC, no load. One word "avoid OpDB load" breaks the tie.

⚠️ Pitfall: "Spectrum and Athena both query S3, so interchangeable?" — Both SQL S3 directly via Glue Catalog. But joining to Redshift internal tables requires Spectrum; pure S3 favors Athena simplicity. "Already running Redshift + must join S3 and internal" is Spectrum's slot.

⚠️ Pitfall: "Make Master/Core Spot too, cheaper" — EMR Master on Spot: cluster dies on interruption. Core on Spot: HDFS data vanishes. Spot only for Task nodes. Cost optimal: Task=Spot + Instance Fleets multi-type.

Summary

Week 9 one-liner: Aggregate data into S3 in standard format (Parquet+partitions), unify metadata in Glue Catalog, pick processing engines matching workload traits (cost, real-time, portability, permissions). Exams ask "optimal under constraint," not service names. Below 12 scenarios sharpen boundary-drawing hands-on. Next week (Week 10): ML/AI architecture — SageMaker, Bedrock, MLOps.

📝 Practice Questions

Click a choice to reveal the answer and explanation.

Question 1

Athena query cost (billed on the volume of S3 data scanned) is too high. The data currently accumulates as uncompressed CSV under a single path. What reduces scan cost the most?

• AAdd more WHERE clauses
• BConvert to Parquet columnar format + date partitioning + compression
• CSeparate Athena Workgroups
• DEnable query Result Reuse only

Question 2

You already run Redshift RA3, and you need to join five years of historical orders (Parquet) in the S3 data lake with the recent-orders table in Redshift within a single query. You do not want to load the historical data into Redshift.

• ACOPY the S3 data into a Redshift internal table
• BRedshift Spectrum + External Schema
• CQuery separately with Athena and combine the results manually
• DFederated Query

Question 3

You need to analyze Aurora PostgreSQL OLTP data in Redshift in near real time. You do not want to build an ETL pipeline yourself, and you do not want analytic queries putting load on the operational database.

• ARedshift Federated Query
• BAurora PostgreSQL Zero-ETL Integration with Redshift
• CReplicate to S3 with DMS CDC, then run Glue ETL
• DHourly Glue batch ETL

Question 4

For one table in the S3 data lake, an analyst group must see every column except a PII column (national ID number), while users in the Korean branch must at the same time see only rows where region='KR'.

• ARestrict object access with an IAM policy
• BLake Formation column permissions + row filters (data filters)
• CS3 bucket policy
• DSplit the PII out into a separate bucket

Question 5

Petabyte-scale logs are processed daily by a 2-3 hour Spark batch. The data is stored permanently in S3. How do you minimize cost while keeping the job safe from Spot interruptions?

• AKeep a persistent EMR cluster on On-Demand to eliminate job start-up delay and ensure stability
• BA per-job transient EMR cluster with Task nodes on Instance Fleets Spot across multiple types, terminated when the job finishes
• CTo minimize cost, run Master, Core, and Task nodes all on Spot Instances
• DCOPY the data into Redshift RA3 and process it with SQL batches

Question 6

A Spark job runs a few times a day on an irregular schedule. You do not want to size clusters or manage nodes, and you want cost near zero when it is not running.

• AA persistent EMR on EC2 cluster
• BEMR Serverless
• CGlue Crawler
• DRedshift Serverless

Question 7

You are moving a self-managed on-premises Kafka to AWS. You want to reuse the existing producer/consumer code and Debezium connectors nearly as-is, and you also want to keep the option of migrating to another cloud later.

• AKinesis Data Streams
• BAmazon MSK
• CSQS FIFO
• DEventBridge

Question 8

You are building an S3 data lake that requires ACID transactions, schema evolution, and time travel (querying a specific past point in time). Plain Parquet does not guarantee consistency for concurrent writes, updates, and deletes.

• AManage plain Parquet files directly and control concurrent writes with application-level locks
• BA transactional table format such as Apache Iceberg / Hudi / Delta Lake
• CStore as CSV for schema-evolution flexibility and imitate time travel with per-version directories
• DSince ACID is required, move the data to DynamoDB and substitute PITR for time travel

Question 9

You need to orchestrate a data pipeline whose dozens of steps have dependencies. The team already uses Airflow, and multi-cloud portability plus complex Python branching logic are required.

• AStep Functions (ASL)
• BMWAA
• CGlue Workflow
• DA chain of EventBridge rules

Question 10

You are building an event-driven, intermittent workflow in a pure AWS environment. You want no standing cost when the workflow is not running, and you need fine-grained integration with many AWS services.

• ABuild Airflow DAGs on MWAA and trigger the workflow by events
• BStep Functions
• CRun cron on a persistent EC2 instance and execute the workflow by polling
• DInstall Jenkins on EC2 and call AWS services from pipeline jobs

Question 11

Dozens of new tables are added to the data lake every week, and granting permissions manually each time is a burden. How do you get permissions applied to new tables automatically?

• AManually add each new table's ARN to an IAM policy as it is created
• BLake Formation LF Tags (tag-based access control, ABAC)
• CClassify with S3 object tags and control access with tag-condition bucket policies
• DUse a Glue Trigger to run a permission-granting script automatically when a new table is detected

Question 12

Two separate RA3 clusters (a BI team and a data science team) must read the same sales data. How do you avoid data duplication and synchronization cost while isolating each team's queries so they do not affect the other's performance?

• AHave both teams share one cluster
• BRedshift Data Sharing (producer → consumer)
• CHave each team replicate the data with COPY
• DShare via Redshift Spectrum

📌 Week 9 at a Glance

Storage    ──► S3 Raw/Curated/Trusted (Parquet + partitions + compression)
Format     ──► Iceberg/Hudi/Delta (ACID·time-travel·upsert)
Catalog    ──► Glue Data Catalog (Hive Metastore compatible, hub for all engines)
Process    ──► Glue (serverless ETL) / EMR (full, Task=Spot Fleet) / Athena CTAS
DW         ──► Redshift RA3·Serverless (Spectrum·Federated·Sharing·Zero-ETL)
Stream     ──► Kinesis (KDS/Firehose) / MSK (Kafka standard)·Serverless·Connect
Perms      ──► Lake Formation (rows·columns·cells, LF Tag ABAC, RAM cross-account)
Orch       ──► MWAA (Airflow DAG, portability) / Step Functions (ASL, serverless·AWS integration)
Govern     ──► DataZone (marketplace) / Glue Data Quality (DQDL)

Key Single-Line Boundary Drawers: Glue vs EMR = cluster ops, Athena vs Redshift = ad-hoc vs DW, Spectrum vs Federated = S3 vs OpDB, Federated vs Zero-ETL = OpDB load, MSK vs Kinesis = portability vs simplicity, LF vs IAM = row·column granularity, MWAA vs Step Functions = portability vs serverless.

Next week (Week 10): ML/AI Architecture — SageMaker, Bedrock, MLOps.