Amazon SAP-C02: Databases & Analytics — Study Guide
Part of the AWS Solutions Architect Professional SAP-C02 — Study Guide. Practice with verified answers in the Amazon exam hub, or take timed practice tests on ExamRoll.io.
Transactional Databases, Scaling Patterns, and Caching
Choosing between Amazon RDS (MySQL/PostgreSQL/Oracle/SQL Server), Amazon Aurora, and Amazon DynamoDB begins with workload profile: strict relational schema and complex transactions favor RDS/Aurora; massive scale, single-digit-millisecond lookups and flexible schema favor DynamoDB. Aurora offers high throughput with distributed storage, replica autoscaling, fast failover, Global Database for cross-region reads and lower-latency disaster recovery. RDS Multi-AZ provides synchronous replication for availability but not read scaling; read replicas (RDS/Aurora) handle read-heavy workloads. For key-value caching and microsecond latency, ElastiCache (Redis or Memcached) reduces DB load; MemoryDB for Redis adds durability and Redis-compatible persistence where data must be highly available and recoverable. Common traps include underestimating connection limits (MySQL max connections), not using connection pooling (Lambda/containers spawning many connections), hot partitions in DynamoDB from poor key design, and neglecting cache eviction/design leading to data staleness. Decision trade-offs often center on cost versus performance and resilience: provisioned Aurora/large RDS instances cost more but reduce latency and simplify transactions, while DynamoDB with on-demand or autoscaling can lower ops but requires careful schema and capacity planning. Encryption, automated backups, PITR, and cross-region replication patterns should be chosen per RPO/RTO.
Data Lake, ETL, and Analytical Query Engines
S3 is the canonical durable data lake; design around partitioning, columnar formats (Parquet/ORC), compression, and compaction to drive cost-effective queries. AWS Glue and AWS Glue Data Catalog provide serverless ETL, schema discovery, and cataloging; Lake Formation adds centralized access control, fine-grained permissions, and cross-account sharing for governed lakes. For interactive analytics, Amazon Athena queries S3 data directly (serverless, pay-per-query), while Amazon Redshift (RA3/Iceberg support) provides performant, managed MPP warehousing for complex BI and joins. Use Redshift Spectrum to query S3 data from Redshift without ingesting everything. Kinesis Data Firehose is a managed ingest path to land streaming events into S3 or Redshift. Common architect traps include too many small files causing high Athena/Redshift overhead, poorly chosen partition keys that create skews, and failing to compact or convert to columnar formats. Trade-offs are latency versus cost: Athena is low-operational cost for ad-hoc queries; Redshift provides higher sustained performance at higher provisioned cost. Data governance and lineage via Glue/Lake Formation are essential for compliance and multi-team ownership.
Streaming, Real-Time Processing, and Search
Real-time ingestion and processing use Kinesis Data Streams (shard-based throughput and ordering guarantees), Kinesis Data Firehose (managed delivery to sinks), Kinesis Data Analytics (SQL/Apache Flink processing), or Amazon MSK for Kafka-compatible needs. Choose Kinesis for straightforward AWS-native serverless integration; choose MSK when clients rely on Kafka ecosystem tooling. At-least-once delivery semantics, shard limits, consumer parallelism, and provisioning of adequate shards are common operational pitfalls. For downstream fast search and observability, Amazon OpenSearch Service provides indexing, near-real-time search, and built-in Kibana dashboards; index lifecycle management and warm/cold tiers reduce cost for older data. Use Kinesis + Lambda or Kinesis + KDA to enrich/transform events before persisting to OpenSearch or S3. Architect idempotency and deduplication into consumers since retries or replays cause duplicates. Decision criteria balance throughput and latency: Kinesis with many shards supports high throughput but increases cost and management; Firehose removes consumer burden but offers less flexible transformation.
Migration, Replication, Governance, and Operational Resilience
Database Migration Service (AWS DMS) and the Schema Conversion Tool (SCT) are primary migration tools for homogeneous and heterogeneous moves, enabling continuous change data capture (CDC). Migration patterns include rehost (lift-and-shift), replatform (e.g., move to Aurora), and refactor to DynamoDB or serverless where appropriate. Use DMS with pre- and post-migration validation, parallel table copy, and careful LOB/LOBLOB handling. Cross-account and cross-region replication require KMS key access, VPC peering or Transit Gateway, and network bandwidth planning; Global Databases and read replicas are alternatives when low-latency reads across regions are needed. Governance and security must include Lake Formation for data sharing, IAM least privilege, resource policies for S3 and RDS snapshots, and VPC endpoints/PrivateLink to avoid public egress. Operational traps include insufficient monitoring (missed replica lag), not testing failover/runbooks, and hidden egress costs during bulk transfers. Backup, PITR strategy, and automated recovery play into RTO/RPO trade-offs; combine replication for availability with regular backups for longer-term retention and compliance.
Practical Problem: Use-Case Scenario
Scenario: Acme Retail operates an e-commerce platform in a multi-account AWS Organization with production workloads in us-east-1 and europe-west-1. They store clickstreams and transaction events into S3 and operate an on-premises OLTP DB that must be migrated to AWS with minimal downtime.
Challenge: Migrate the OLTP database to a highly available, cross-region read-scalable target while building a governed analytic lake on S3 with real-time ingestion and query capability.
Recommended Approach:
- Use AWS DMS with SCT for schema conversion and set up continuous CDC from the on-premises DB to Amazon Aurora (Global Database) in us-east-1 with an Aurora read replica in europe-west-1.
- Ingest clickstream and transaction events via Amazon Kinesis Data Streams and Firehose; buffer and deliver raw events to S3 in Parquet, partitioned by date and region.
- Catalog S3 data with AWS Glue, enforce access control via Lake Formation, and perform ETL with Glue jobs (or Glue Studio) to produce curated datasets; expose them to analysts via Amazon Athena and Redshift Spectrum.
- Add ElastiCache (Redis) for high-read caching of hot product and session data; instrument end-to-end monitoring with CloudWatch, enable Enhanced Monitoring on Aurora, and validate failover with runbooks.
Rationale: This approach minimizes downtime using DMS CDC, provides low-latency global reads via Aurora Global Database, establishes a governed S3 data lake for analytical agility, and reduces load on OLTP systems with caching and decoupled streaming ingestion in line with enterprise resilience and performance best practices.
← Storage · All domains · Migration →
Practice these questions → · Timed practice on ExamRoll.io →
Pass the whole exam — not just this question
You found this answer. Get every verified question and explanation in one place, and save hours of prep. Free to start.
Pass your exam →