SAP-C03 Data, Storage and Database Architecture: Iceberg Lakehouses, Streaming, Global Databases, Caching and Data Sharing

Design a lakehouse by storing the data once in Amazon S3 as Apache Iceberg tables, cataloging them in the AWS Glue Data Catalog, and letting several engines (Amazon Athena, Amazon EMR, AWS Glue, Amazon Redshift) read and write the same tables. Plain Parquet files partitioned in Hive style give you cheap storage and fast scans, but no transactions: a change means rewriting whole partitions by hand, and readers can see a half-written partition. Iceberg adds a table layer on top of the files that fixes this.

• Atomic commits. Every write produces a new snapshot that becomes visible all at once, so readers never see a partial change.
• Row-level DELETE, UPDATE and MERGE. A privacy request to erase or correct one customer becomes a SQL statement from Athena or Spark instead of a partition rewrite.
• Time travel. Queries can read the table as it stood at an earlier snapshot or timestamp, which auditors use to see past states.
• Schema and partition evolution. Columns can be added, dropped, renamed and widened, and the partition layout can change, without rewriting existing data.

S3 Versioning is not a substitute: it keeps old object versions but offers no consistent table view and no "as of" query.

Table maintenance: who runs it?

Iceberg tables need ongoing maintenance. Frequent small writes (a streaming source committing every minute, for example) create millions of small files and thousands of snapshots; queries slow down because each one opens far more files and metadata, and S3 request charges climb. Three jobs fix this: compaction (rewrite small files into large ones), snapshot expiry (drop old snapshots beyond the time-travel window) and orphan file removal (delete files no snapshot references). The design decision is who performs them.

Raising the ingestion buffer (for example a longer Amazon Data Firehose buffering interval) writes fewer, larger files from now on, but it does nothing for the files and snapshots that already exist, so it complements maintenance rather than replacing it.

Partition evolution

Iceberg uses hidden partitioning: the partition spec is a transform of a source column (such as day(event_ts) or hour(event_ts)), and queries filter on the source column itself. Changing the spec is a metadata operation: new data is written with the new layout, existing files keep the old one, and a filter on the timestamp prunes files under both specs. Nothing is rewritten and the queries do not change. Athena's Iceberg DDL can add, drop, rename and reorder columns and promote types, but it cannot change the partition spec; that is done with Spark SQL (ALTER TABLE … REPLACE PARTITION FIELD), for example on Amazon EMR. Athena partition projection is a Hive-table feature that computes partition locations from a pattern; it does not apply to Iceberg, which tracks partitions in its own metadata.

Querying the lake from Redshift

Amazon Redshift Spectrum queries S3 data in place: you create an external schema in Redshift that points at a Glue Data Catalog database, and its tables can be joined with local warehouse tables in one SQL statement, with nothing loaded. It suits cold history that is queried occasionally while the warehouse keeps the hot data. Spectrum is billed by the bytes it scans in S3, so cost and speed come from the file layout, not from cluster size: a columnar format such as Parquet lets it read only the referenced columns, and partitions on the filtered column let it skip whole partitions. Row-based CSV is read in full whatever the query selects, and a view or more cluster nodes does not change what is scanned. Loading the history with COPY defeats the purpose, and concurrency scaling adds query capacity for warehouse bursts rather than making S3 files queryable.

AWS Lake Formation enforces fine-grained permissions on Glue Data Catalog tables, so one copy of the data can serve audiences that may see different columns and rows. You register the S3 location with Lake Formation, and integrated engines such as Athena, Redshift Spectrum, EMR and AWS Glue then check Lake Formation permissions on every query, wherever the query runs.

Tag-based access control at scale

Named-resource grants (one grant per table) grow with the table count, and any automation that creates them per table leaves new tables without access until it runs. LF-Tags invert this. You define tags such as domain and sensitivity, assign them to databases, and tables and columns inherit the database's tags at creation; you override a tag at a lower level, for example marking a personal data column sensitivity=high inside a low-sensitivity table. Principals are then granted permissions on tag expressions (domain=payments AND sensitivity=low), which match every resource carrying those tags, including resources created later. The number of grants scales with roles and tag values, not with tables. LF-Tags work at database, table and column level only; restricting rows still needs data filters.

What does not do the job

• Engine views (Athena views per group) exist only in the engine that defines them, must be rebuilt for every other engine, and do not stop a principal with access to the base table or its S3 objects.
• IAM resource tags and aws:ResourceTag conditions on Glue actions control catalog API calls, not the column- and row-level data access that Lake Formation enforces in the engines.
• Separate KMS keys control who can decrypt objects, not which columns or rows a query returns.
• Extra copies per audience (written by Glue jobs or to separate prefixes) work but must be kept in sync, which breaks any "single copy" requirement.

A real-time pipeline is chosen by four questions: which protocol the producers already speak, whether several consumers must read independently and replay history, whether the processing needs state across time windows, and where the data must land. The building blocks divide the work as follows.

Kafka workloads: MSK, not a rewrite

When a company runs self-managed Kafka and wants to stop patching, scaling and managing broker storage without touching application code, the target is Amazon MSK: it runs Apache Kafka itself, so clients only need new bootstrap brokers. Topics move with MirrorMaker 2, and existing connectors move to MSK Connect. Moving to Kinesis means switching to the Kinesis libraries, and rebuilding connectors as Glue or Spark jobs means new code. Amazon MQ does not speak the Kafka protocol either: Amazon MQ for ActiveMQ supports OpenWire, AMQP 1.0, STOMP, MQTT and WebSocket, and Amazon MQ for RabbitMQ supports AMQP 0-9-1.

Kinesis capacity and fan-out

Two separate limits catch architects out. Write and shard capacity: in provisioned mode you size and reshard the stream yourself; on-demand mode scales capacity with traffic and can be switched on for an existing stream in place, without recreating it. Read capacity: consumers that call GetRecords share each shard's read throughput, so adding consumers throttles all of them even when writes are well within limits. Enhanced fan-out gives each registered consumer its own dedicated read throughput per shard, pushed over SubscribeToShard. Adding shards adds shared capacity but keeps the manual resharding; longer retention keeps unread records available but adds no read throughput. Write and read capacity are independent problems, so a stream that both throttles producers during spikes and starves several consumers can need a change on each side.

A combined pattern

Because a durable stream keeps records for its whole retention period and lets each reader track its own position, one stream can serve several requirements at once: a stateful processor for windowed detection, a delivery service writing to the lake, and later readers that replay history. The design rule is to set retention from the longest replay requirement (the 24-hour default is often too short) and to give each requirement its own reader, rather than chaining services or copying the data into a second stream.

Batch analytics on AWS runs Spark, Hive and similar frameworks on Amazon EMR, and the deployment option is chosen by utilization and how much infrastructure the team wants to own. Data that already lives in an AWS database or a SaaS application should usually arrive through a managed integration rather than a hand-built pipeline.

Cost and reliability on EMR on EC2

An EMR cluster has a primary node (cluster coordination), core nodes (run tasks and store HDFS blocks) and task nodes (run tasks, store no HDFS data). Spot reclamation of a core node can destroy HDFS blocks and fail jobs, so the safe split is primary and core nodes On-Demand and task nodes on Spot. Instance fleets that list several instance types let EMR draw Spot capacity from more pools, which lowers the chance of interruption compared with one type. Pair the fleet with a capacity-aware Spot allocation strategy such as price-capacity-optimized, which launches from the pools with the most spare capacity at a low price instead of simply the cheapest pool (lowest-price draws from the pools most likely to be reclaimed). EMR managed scaling resizes the cluster from YARN metrics and can be limited so growth happens on task nodes, so you stop paying for peak capacity in quiet hours.

Zero-ETL and SaaS ingestion

• Zero-ETL integrations into Amazon Redshift replicate data without pipeline code from sources such as Aurora MySQL, Aurora PostgreSQL, RDS for MySQL, RDS for PostgreSQL, RDS for Oracle and DynamoDB, and AWS Glue zero-ETL integrations bring SaaS applications such as Salesforce, SAP, ServiceNow and Zendesk into Redshift or a SageMaker lakehouse. The DynamoDB integration is built on table exports (point-in-time recovery must be on), so it consumes no read capacity on the source table; it replicates on an interval of minutes (AWS documents roughly 15 to 30), so it fits analytics that can be minutes behind, not a per-event leaderboard. A Lambda function on DynamoDB Streams or a scheduled Glue scan is code to own, and a scan consumes read capacity.
• Amazon AppFlow is the managed flow service for SaaS applications such as Salesforce, Zendesk, ServiceNow and Slack. Flows run on a schedule, on an event or on demand, map and filter fields, encrypt with an AWS KMS key, and write to S3, Redshift and other destinations. Glue zero-ETL is the other no-code path for supported SaaS sources when the goal is continuous replication into Redshift or a lakehouse rather than scheduled, field-mapped flows. AWS DMS migrates databases and has no Salesforce or Zendesk source, AWS DataSync moves files and objects between storage systems, and a Glue crawler only infers schemas into the Data Catalog.

Select the database from the access pattern first, then confirm consistency and scale: key lookups at very high rates go to DynamoDB, multi-table transactions and ad-hoc joins go to Aurora or RDS, large analytical scans go to Redshift, and rankings or counters that change constantly go to an in-memory sorted set.

Scaling traps in DynamoDB

• Hot partitions. A global secondary index whose partition key is a constant value sends every update to one partition, which throttles at high update rates. Rankings across all players belong in a sorted set (ZADD to update, range and rank commands to read), not in a constant-key index.
• Sudden spikes in on-demand mode. On-demand tables instantly serve up to double their previous peak; a known launch that jumps far beyond that should raise the table's warm throughput beforehand so it is ready for the spike.
• Reports without affecting the table. Exporting the table to S3 (point-in-time export) and querying it with Athena consumes no read capacity.

Mixed workloads usually mean several stores, each matched to one access pattern: a key-value store for high-rate item writes (using conditional writes where an update must not overwrite a concurrent one), an in-memory structure for constantly changing rankings, and an offline copy such as an S3 export for periodic analytical reporting.

Multi-AZ deployments provide high availability with automatic failover, read replicas provide read scaling, and only the Multi-AZ DB cluster does both at once. Mixing these up is the most common error in database availability scenarios.

The rule follows from the table: availability and read offload are separate goals on a Multi-AZ DB instance, so a design that needs both on that deployment type needs both a standby and a read replica, because reports cannot run on the standby. A Multi-AZ DB cluster meets both goals in one deployment and keeps the RDS engine; Aurora offers similar benefits but is a different engine, which matters whenever a requirement fixes the engine. RDS Proxy pools connections and shortens reconnect time after a failover, but it creates no standby, so it adds nothing to a Single-AZ instance.

For relational data across Regions use Amazon Aurora Global Database; for key-value data use Amazon DynamoDB global tables, choosing between eventual and strong consistency modes. Both are managed replication that a self-built logical or binlog replica cannot match for writer impact and recovery speed.

Aurora Global Database

• Storage-level replication. Data is replicated by the storage layer, typically with sub-second lag and no binary logging or replication work on the primary writer. A cross-Region binlog replica, by contrast, needs binary logging on the writer and lags more as write volume grows; bidirectional logical replication adds load to each writer and risks write conflicts.
• Read locally, promote on failure. Secondary clusters serve local, low-latency reads and can be promoted if the primary Region fails. An existing cluster is converted by adding a secondary Region.
• Write forwarding. With write forwarding on, a secondary cluster accepts occasional writes and forwards them to the primary, so the application needs no code that routes writes to the primary Region.
• Switchover versus failover. A switchover is the planned operation: Aurora waits until the secondary is fully synchronized before swapping roles, so no data is lost; use it for drills and Region rotation. A failover is for an unplanned outage: it promotes a secondary immediately, and any data not yet replicated (the replication lag) is lost.
• Bounding RPO on Aurora PostgreSQL. The rds.global_db_rpo parameter, set in the primary cluster's DB cluster parameter group (minimum 20 seconds), makes Aurora block commits on the primary whenever every secondary is further behind than the limit; commits resume as soon as one secondary catches up. This guarantees the maximum data loss of a failover while adding no latency to normal writes. An alarm on lag only reacts after the fact, and synchronous cross-Region commit adds a round trip to every write.

Snapshot copies to another Region give an RPO of minutes or more and a slow restore, with no live cluster to accept writes. ElastiCache Global Datastore copies a cache, not the database, and cannot replace a database cluster.

DynamoDB global tables: MREC versus MRSC

The consequence for migrations: because the mode is fixed at creation, data that needs strong reads in every Region and zero RPO goes into a newly created MRSC table that is loaded afterwards, never an existing populated table switched over; and routing all writes to one Region on MREC still leaves reads in the other Regions eventually consistent. Replicated writes from other Regions are applied to the table directly, bypassing any DAX cluster in front of the local replica, so that cache keeps old values until its TTL expires.

Choose storage by protocol first (object, block, NFS, SMB, parallel file system) and then by performance and data-protection needs. The near-twins below are where most designs go wrong.

Mountpoint for Amazon S3 gives high-throughput file access to a bucket, but it does not provide full POSIX semantics (for example, modifying existing files in place), so it is not a shared scratch file system. Copying data between EFS and S3 with DataSync before and after every run adds time that a linked FSx for Lustre file system avoids.

Use Amazon ElastiCache (Valkey, Redis OSS or Memcached) as a general-purpose cache in front of any data source, and DynamoDB Accelerator (DAX) only in front of DynamoDB. The design work is choosing how entries get into the cache, how they stay fresh, and what bypasses the cache.

ElastiCache strategies

The usual answer combines them: lazy loading with a TTL for reads, plus updating (or deleting) the cached entry whenever the application writes a change, so changes are visible within seconds and memory still holds only what is read. For cross-Region caches, ElastiCache Global Datastore (Valkey and Redis OSS) replicates a primary cluster to read-only secondary clusters in other Regions for local reads, and a secondary can be promoted if the primary Region fails. Replica nodes in a replication group stay in the primary's Region, and ElastiCache Serverless is single-Region.

How DAX actually caches

DAX keeps two separate caches and is write-through for writes made through the DAX client.

• Item cache serves GetItem and BatchGetItem. A write through DAX updates it, so single-item reads see the change at once.
• Query cache stores result sets of Query and Scan. Writes through DAX do not update or invalidate it; a cached result set stays until its TTL expires (both caches default to five minutes). Lowering the query cache TTL bounds how stale list pages can be.
• Strongly consistent and transactional reads pass through. With ConsistentRead set to true, DAX forwards the request to DynamoDB and caches nothing, so latency and consumed read capacity do not improve. Switch to eventually consistent reads where short staleness is acceptable, or use strongly consistent reads deliberately on the one path that must always be current.
• No invalidation API. You cannot delete individual DAX entries from a stream consumer; freshness comes from TTLs, writes through DAX and eviction.
• Writes that bypass DAX, including global table replication from other Regions and writes made directly to DynamoDB, leave the DAX cache stale until the TTL ends.

Adding DAX nodes adds capacity and availability, and adding table read capacity raises throughput, but neither makes pass-through reads use the cache.

Catalog data once in the AWS Glue Data Catalog and share it in place: Lake Formation cross-account grants for lake tables, Redshift data sharing for warehouse tables, and AWS Clean Rooms when partners must analyse combined data without seeing each other's raw records. None of these copies the data.

Lake Formation cross-account sharing

1. In the producer (central) account, grant Lake Formation permissions, such as SELECT with column restrictions or through a data filter, to the consumer's AWS account ID (or organization). Lake Formation shares the resources through AWS RAM.
2. In the consumer account, the data lake administrator accepts the share if needed, creates resource links to the shared databases or tables in the local catalog so Athena and other engines can find them, and grants local analysts permissions on the links and the shared tables.

The producer's column and row restrictions stay enforced wherever the tables are queried. Replicating the bucket, or crawling the central bucket from the consumer account, creates copies or separate table definitions that bypass the central grants.

Amazon Redshift data sharing

A producer Redshift warehouse (RA3 provisioned or Serverless) creates a datashare, and authorizes another account; an administrator in the consumer account associates the datashare with the account (or specific namespaces), and the consumer then creates a database from it. Consumers read live, current data with no extract jobs or copies, and their queries run on their own compute, so they do not slow the producer. UNLOAD-plus-Spectrum is an extract job with stale data, restored snapshots are stale copies, and querying the producer through the Data API uses the producer's compute. Datashares work between Redshift warehouses; they do not expose Glue Data Catalog tables to Athena in another account.

AWS Clean Rooms

In a Clean Rooms collaboration, each member adds configured tables that point at data in its own account, and an analysis rule on each table decides what queries may return.

Aggregation thresholds protect each result on its own, but not a sequence of results: if collaborators may run free-form SQL that is not pre-approved, the difference between two otherwise identical aggregates that differ by a single record can expose that record (a differencing attack). AWS Clean Rooms Differential Privacy, turned on in a custom analysis rule and governed by the collaboration's differential privacy policy (a privacy budget and the noise added per query), limits what repeated queries can reveal. Query logging records what ran but prevents nothing. Lake Formation filtered shares, S3 access points and copied lists all hand the other party customer-level records, which Clean Rooms exists to avoid.