Database Interview Guide

The #1 SA interview question: "Which database would you use?" — here's the decision matrix:

The Decision Tree (Interview Framework)

1. Need SQL joins/transactions? → Yes: Aurora. No: continue.
2. Simple key-value access patterns? → Yes: DynamoDB. No: continue.
3. Need caching layer? → Yes: ElastiCache Redis. No: continue.
4. Analytics on large datasets? → Yes: Redshift or Athena (serverless). No: continue.
5. Graph relationships? → Yes: Neptune. No: check remaining options.

Aurora Storage Architecture (Know This)

Aurora stores data in a shared, distributed storage layer: 6 copies across 3 AZs. It can lose 2 copies and still write, lose 3 and still read. Storage auto-heals — bad segments are automatically repaired. This is why Aurora has sub-30-second failover.

Multi-AZ vs Read Replicas

• Multi-AZ: Standby instance for HA (automatic failover). Cannot serve reads. Purpose: availability.
• Read Replica: Separate instance that serves read queries. Can be promoted to primary. Purpose: read scaling.
• Aurora: Every reader is also a failover target. Multi-AZ + read scaling in one.

DynamoDB is THE go-to for serverless and high-scale architectures:

Key Concepts

Capacity Modes

DynamoDB Streams + EventBridge

Pattern: DynamoDB Streams captures every item change (INSERT, MODIFY, DELETE) → triggers Lambda → fan-out via EventBridge to downstream services. This is the CDC (Change Data Capture) pattern for real-time event-driven architectures.

Caching Strategies

DMS Migration Modes

• Full Load — One-time migration of all existing data
• CDC (Change Data Capture) — Continuous replication of changes after full load
• Full Load + CDC — Most common: migrate everything, then keep syncing until cutover

Migration Strategy

1. Assessment: Run SCT to analyze schema compatibility and identify conversion issues
2. Schema conversion: SCT converts schema, stored procedures, triggers
3. Full load: DMS migrates all existing data to the target
4. CDC replication: DMS continuously replicates changes while source is live
5. Cutover: Stop writes to source → verify target → switch application connection string

Database selection and design is a core architect competency. These questions test your ability to choose the right database and handle data at scale.

1. Your application has complex SQL queries with JOINs across 20 tables, needs ACID compliance, and handles 50,000 reads/second with 5,000 writes/second. Would you choose RDS PostgreSQL or Aurora PostgreSQL? Why?
   Answer Guide

   Aurora — 5x throughput over standard PostgreSQL, 6 copies across 3 AZs, auto-scaling storage to 128TB, up to 15 read replicas. The storage architecture (shared distributed volume) eliminates replication lag for read replicas.
2. Design a DynamoDB table for an e-commerce application. You need to query by customer_id (all their orders), by order_id (single order lookup), and by date range (orders in last 30 days). What's your key design?
   Answer Guide

   PK = customer_id, SK = order_date#order_id (enables range queries). GSI1: PK = order_id (single lookup). Consider single-table design vs multiple tables. Discuss hot partition risk if one customer has millions of orders.
3. Your DynamoDB table has a hot partition problem — one partition key receives 80% of all traffic. What are three strategies to fix it?
   Answer Guide

   1) Write sharding — add random suffix to partition key (e.g., customer_123#1, customer_123#2). 2) Composite keys — add a time-based prefix. 3) Caching — DAX in front of DynamoDB for read-heavy patterns. Also: re-evaluate the data model itself.
4. When would you use ElastiCache Redis vs DynamoDB DAX? A team is adding caching to their application and isn't sure which to choose.
   Answer Guide

   DAX is specifically for DynamoDB — microsecond read latency, API-compatible (drop-in). ElastiCache Redis is general-purpose — supports complex data structures (sorted sets, pub/sub, Lua scripting), works with any backend. Use DAX for DynamoDB-only caching, Redis for everything else.
5. You need to migrate a 2TB Oracle database to Aurora PostgreSQL with near-zero downtime. Walk me through the complete migration process.
   Answer Guide

   1) SCT to convert schema. 2) Fix incompatibilities manually (Oracle-specific PL/SQL, sequences, synonyms). 3) DMS Full Load to seed data. 4) DMS CDC for ongoing replication. 5) Validate data consistency. 6) Cut over when replication lag = 0. Test rollback plan before cutover.
6. Explain the caching strategies: lazy loading, write-through, and write-behind. Which would you use for a leaderboard that updates every second and is read by millions of users?
   Answer Guide

   Write-behind (write to cache immediately, async persist to DB) for real-time updates. Redis Sorted Sets are perfect for leaderboards (ZADD, ZRANGE). Lazy loading risks stale data. Write-through adds latency on every write. Discuss TTL as a safety net.
7. Design a multi-region database strategy for a global chat application. Users in US, Europe, and Asia need sub-100ms read and write latency from their region.
   Answer Guide

   DynamoDB Global Tables — active-active in all 3 regions with eventual consistency (typically sub-second replication). For SQL: Aurora Global Database gives low-latency reads in secondary regions, but writes go to one primary region. Trade-off: DynamoDB has eventual consistency, Aurora has single-writer.
8. A Lambda function connecting to an Aurora database is hitting connection limits during traffic spikes (1,000 concurrent Lambda executions). How do you solve this?
   Answer Guide

   RDS Proxy — connection pooling and multiplexing. Lambda creates a new connection per invocation; RDS Proxy pools them (e.g., 1,000 Lambda connections → 100 DB connections). Also supports IAM authentication and automatic failover. Without RDS Proxy, you'll exhaust max_connections.
9. Your team uses DynamoDB with On-Demand capacity mode. The monthly bill is $15,000. Reads are 80% of the cost and traffic is predictable. How would you optimize?
   Answer Guide

   Switch to Provisioned capacity with auto-scaling (20-40% cheaper for predictable traffic). Add DAX for read-heavy patterns (reduces consumed RCUs dramatically). Enable DynamoDB Reserved Capacity for baseline. Consider DynamoDB Infrequent Access table class for cold data.
10. What's the difference between DynamoDB strongly consistent reads and eventually consistent reads? When would you explicitly need strong consistency?
    Answer Guide

    Eventually consistent (default) — may return stale data (replication takes ~ms). Strongly consistent — always returns latest, but 2x the cost and only works against the leader node. Use strong consistency for: financial transactions, inventory counts, anything where reading stale data causes business errors.