Choosing a database: SQL vs NoSQL on AWS
Use a repeatable decision framework (access patterns, consistency, scale, operations and cost) to choose between RDS, Aurora, DynamoDB, ElastiCache, DocumentDB, Redshift and S3 for a banking workload, and record the choice in an ADR.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 · AWS CLI v2 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Describe the AWS database family in one sentence each: RDS, Aurora, Aurora DSQL, DynamoDB, ElastiCache, DocumentDB, Redshift, S3 and the specialist stores (Neptune, Timestream, Keyspaces, MemoryDB).
- Explain SQL vs NoSQL in terms of what each optimises for, not in terms of fashion.
- Apply a five-question decision framework: access patterns, consistency, scale, operations, cost.
- Map typical Harbourline Bank workloads (ledger, payment status, sessions, statements, analytics, product catalogue) to a store, and say why the alternatives lose.
- Write an Architecture Decision Record (ADR) that an architect, an auditor and your future self can read.
Prerequisites
- Module 6 overview, for what the module covers and how much it costs
- Module 1: AWS fundamentals, for Regions, Availability Zones and the shared responsibility model
- No AWS resources are created in this lesson. The exercises are design work, plus one optional free CLI command.
Why this matters in banking
Picture Harbourline's first cloud project. A team moves the payments status API to AWS and picks DynamoDB, because "it scales and it's serverless". Six months later, operations asks a simple question: "Show me every payment over £10,000 to a new beneficiary last quarter, grouped by branch." The table was designed for one access pattern, "get payment by ID", so the answer needs a full table scan, an export to S3 and an Athena query. The auditor waits two days.
Down the corridor, another team puts session tokens and rate-limit counters into the same SQL Server-style relational database that holds the ledger. On payday traffic spikes, the counters cause lock contention, and posting latency triples.
Neither team chose a bad database. Each chose a database that did not fit its access patterns. In a bank, a wrong choice shows up as:
- month-end jobs that never finish;
- double-posted payments when two writers race;
- a restore that takes a day when the business expected an hour;
- an audit request you can't answer without an engineering project.
The fix is not "always use SQL" or "always use NoSQL". It is a decision process you can repeat and defend.
The idea in plain language
A database is a promise about how data will be stored and found again. Different databases make different promises:
- Relational (SQL) databases promise that you can ask almost any question later. You store normalised tables, and the engine works out at query time how to join, filter and aggregate them. The price is that one server (or one writer) does most of the work, so write scale has a ceiling. Costs are mostly fixed: you pay per hour for the instance whether you use it or not.
- Key-value and document (NoSQL) databases like DynamoDB promise that the questions you designed for will be fast at any scale. You decide the access patterns first and shape the data around them. Questions you didn't plan for are slow or impossible without moving the data elsewhere.
- Caches promise speed, not durability. They hold copies of data that lives somewhere else.
- Warehouses and lakes promise cheap, fast analysis of large amounts of history. They are not built for thousands of small writes per second.
Analogy: A relational database is a well-run branch archive with a librarian. You can ask for "all letters to customers in Leeds about mortgages in 2019" and the librarian finds them, although it may take a while if the archive is huge. DynamoDB is a wall of numbered safe-deposit boxes. Opening box 4,217 takes the same instant whether there are a thousand boxes or a billion. But if you ask "which boxes contain mortgage letters?", someone has to open every box, unless you planned an index of exactly that question when you built the wall.
The skill this lesson teaches is to write down the questions first and choose the store second.
What problem the AWS database family solves
On-premises you probably ran one engine (SQL Server) for nearly everything, because every new engine meant new servers, licences, backups and DBA skills. AWS offers purpose-built managed databases. Each has its own operating model, and AWS runs the undifferentiated work: hardware, patching, backups and replication. That changes the economics. Using two engines is no longer twice the operational burden, so you can match each workload to the store that suits it.
Here is the family, in one sentence each.
| Service | What it is | Typical banking use | Watch out for |
|---|---|---|---|
| Amazon RDS | Managed instances of PostgreSQL, MySQL, MariaDB, SQL Server, Oracle and Db2 | Core ledger, customer master, anything relational with moderate scale | You still choose an instance size and pay per hour; one writer |
| Amazon Aurora | AWS-built relational engine compatible with PostgreSQL or MySQL. Its storage is spread over three AZs | Ledger needing fast failover, read scaling, cross-Region DR (Global Database) | Higher minimum cost than small RDS; not SQL Server |
| Aurora DSQL | Serverless, distributed SQL database, PostgreSQL-compatible, active-active across Regions (GA May 2025) | Multi-Region active-active systems of record | Supports only a subset of PostgreSQL features. Check every feature you rely on |
| Amazon DynamoDB | Serverless key-value and document store with single-digit-millisecond reads at any scale | Payment status, idempotency keys, device and session records, event-sourced projections | You model access patterns first; ad-hoc queries are expensive |
| Amazon ElastiCache | Managed Valkey, Redis OSS or Memcached, in memory | Cache-aside for reference data, rate limiting, short-lived sessions | Not a system of record; eviction and failover can lose data |
| Amazon MemoryDB | Durable, Valkey/Redis-compatible in-memory database | Real-time fraud features where in-memory speed must survive failure | Costs more than ElastiCache; niche |
| Amazon DocumentDB | MongoDB-API-compatible document database | Product catalogue, onboarding form data with flexible shape | Compatibility with MongoDB is partial ⚠ VERIFY your driver features |
| Amazon Redshift | Columnar data warehouse (provisioned or Serverless) | Regulatory reporting, finance analytics over years of transactions | Not for OLTP. Concurrency and cost need tuning |
| Amazon S3 (+ Athena) | Object storage. Athena queries files in place with SQL | Data lake of transaction history, statements archive, audit evidence | Not a database. Every query scans files, so partition them |
| Amazon Neptune | Graph database | Fraud rings, beneficial-ownership networks | Specialist skills; query languages Gremlin/openCypher/SPARQL |
| Amazon Timestream | Time-series database | Infrastructure and market-tick metrics ⚠ VERIFY current Timestream offerings before building on it | Not for money movements |
| Amazon Keyspaces | Cassandra-compatible wide-column store | Lift-and-shift of existing Cassandra workloads | Pick it only if you already run Cassandra |
The later lessons cover each major store in depth. This lesson is about choosing.
The hard limitations to keep in mind
- No store is "free" at scale. DynamoDB's on-demand pricing is per request: cheap at low volume, potentially expensive for a constant high load. RDS has a fixed hourly price: wasteful when idle, efficient when busy.
- Licensing is architecture. RDS for SQL Server licence-included pricing is much higher than open-source engines. A SQL Server habit can cost more than the compute.
- Data gravity is real. Once you have a few terabytes in one store, moving it is a project. Choose deliberately at the start.
- Managed ≠ responsibility-free. AWS patches and backs up. You choose encryption keys, network exposure, users, retention, and whether you ever tested a restore.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| SQL Server on a VM | RDS for SQL Server, or SQL Server on EC2 | RDS manages backups, patching and HA, but restricts sysadmin and host access |
| Azure SQL Database | Aurora PostgreSQL / RDS for PostgreSQL | Different engine. T-SQL must be converted (or partially run via Babelfish) |
| Azure SQL Hyperscale | Aurora (shared distributed storage) | Similar idea: storage separated from compute and replicated |
| Azure Cosmos DB (NoSQL API) | DynamoDB | DynamoDB has one data model (partition + sort key) and two consistency levels, not five |
| Azure Cosmos DB for MongoDB | DocumentDB | Both are "MongoDB-compatible", with different gaps |
| Azure Cache for Redis | ElastiCache (Valkey/Redis OSS) | Valkey is the open-source fork AWS now promotes |
| Azure Synapse dedicated SQL pool | Redshift | Both columnar MPP warehouses; Redshift Serverless is the pay-per-use option |
| ADLS Gen2 + Synapse serverless SQL | S3 + Athena | Same "query files in place" model |
| Azure Table Storage | DynamoDB (simplest use) | DynamoDB adds transactions, streams and global tables |
What carries over: normalisation, indexing, isolation levels, query plans, the habit of asking "what happens when this fails?". What is new: modelling for one store's access patterns, paying per request, and choosing between instance-based and serverless pricing.
Architecture
The way to think about it: one system of record per piece of data, with other stores holding derived copies for speed or analysis.
Security-boundary view. Every store sits in private subnets or behind IAM. Data that leaves the system of record (to caches, lakes or warehouses) takes its classification with it. If the ledger holds personal data under UK GDPR, so do its copies in S3 and Redshift. Each copy needs encryption with a KMS key, access control and a retention rule.
The decision framework
Ask these five questions, in this order, for every workload. Write the answers down: they become your ADR.
1. Access patterns: what questions, how often, how big?
List the reads and writes with rough numbers:
| # | Access pattern | Frequency | Latency need | Shape |
|---|---|---|---|---|
| A1 | Post a transaction to an account | 50/s peak | under 100 ms | Single row insert + balance update |
| A2 | Get balance and last 50 transactions | 500/s peak | under 50 ms | One account, ordered by time |
| A3 | Statement for one account and month | 20/s | under 1 s | Range on date |
| A4 | All payments over £10k to new beneficiaries last quarter | Daily, ad hoc | Minutes OK | Join + filter + group |
| A5 | Payment status by payment ID | 2,000/s peak | under 20 ms | Key lookup |
Rules of thumb:
- Mostly known, key-based lookups at high volume (A5) → key-value (DynamoDB).
- Joins, ad-hoc questions, reporting on live data (A4) → relational, or a copy in the lake/warehouse.
- Large scans over history → columnar (Redshift, Parquet + Athena), never the OLTP database at peak hours.
2. Consistency and integrity: what must never be wrong?
- Must a write be visible to the next read immediately (read-your-writes)? Balances: yes. A marketing dashboard: no.
- Do you need multi-row atomicity? Debiting one account and crediting another must both happen or neither. Relational databases do this naturally. DynamoDB does it with
TransactWriteItems, which accepts up to 100 actions (4 MB in total) per transaction. - Do you need constraints (unique, foreign key, check) enforced by the store? Relational: yes, built in. DynamoDB: only via condition expressions you write.
- Idempotency: can you enforce "this key is stored once"? Relational: a unique index. DynamoDB: a conditional
PutItemwithattribute_not_exists.
The transactions, isolation and consistency lesson goes deep on isolation levels.
3. Scale and growth: how big, how fast, how spiky?
- Data size in 1 and 5 years. RDS for PostgreSQL scales storage to 64 TiB. Aurora cluster volumes grow automatically up to 128 TiB, or 256 TiB on specific engine versions. DynamoDB has no practical table size limit.
- Write throughput. A single relational writer handles thousands of small transactions per second on a large instance. Beyond that you shard, or you move hot paths to DynamoDB.
- Spikiness. Payday and Black Friday spikes favour serverless capacity (DynamoDB on-demand, Aurora Serverless v2) or headroom you pay for all month.
4. Operations and skills: who runs it at 3 a.m.?
- Does the team know the engine? Harbourline's team knows SQL Server and EF Core. PostgreSQL is a short step; single-table DynamoDB design is a real learning curve.
- Which HA/DR story do you need? Multi-AZ RDS, Aurora with readers, Aurora Global Database, DynamoDB global tables (see Multi-AZ, replicas, backups and PITR).
- How will you back up, restore and prove it? Every store needs a restore drill.
5. Cost: what does it cost at idle, at average and at peak?
| Pricing model | Services | Good when |
|---|---|---|
| Per instance-hour | RDS, Aurora provisioned, ElastiCache nodes, DocumentDB, Redshift provisioned | Steady, predictable load |
| Per capacity unit consumed | Aurora Serverless v2 (ACU-hours), Redshift Serverless (RPU-hours) | Variable load, idle periods |
| Per request + storage | DynamoDB on-demand, S3 + Athena (per TB scanned) | Spiky or low volume |
| Provisioned throughput | DynamoDB provisioned capacity (+ auto scaling) | High, steady key-value load |
Always add backup storage, data transfer, KMS keys, Secrets Manager secrets and licences to the estimate. The cost profile in the module overview lists the free alternatives for this course.
The decision flow
Two notes on this flow:
- PostgreSQL JSONB is often a better answer than a separate document database. You keep one engine, one backup story and transactions across relational and JSON data.
- "Must I use SQL Server?" is a separate question. It is about licensing and migration effort, covered in SQL Server ↔ AWS comparison.
Step-by-step implementation
This lesson's "implementation" is a decision. You will produce a short workload inventory, a scored option table and an ADR for one Harbourline workload.
Inventory the workload
Pick one workload, for example the payment status store behind the Payments Service. Write its access patterns as in the A1–A5 table: what is read and written, how often, how fast, and what it looks like. Note the data classification. Payment status records contain IBANs and amounts, so they are personal data.
Write the non-negotiables
List three to five requirements that disqualify an option outright. Examples:
- "A duplicate payment must be impossible even with client retries."
- "RPO no more than 5 minutes; RTO no more than 1 hour."
- "Data stays in the UK or EU."
- "No static database passwords."
Shortlist two or three options
Never shortlist one option: you can't show you chose anything. Usually that means a relational option, a key-value option and possibly "extend an existing store".
Score them against the five questions
Use a simple table. Write reasons, not just scores. The reasons are what reviewers check.
Record the decision as an ADR
Use the template in the Complete code section below. Store it next to the code (docs/adr-000N-*.md) so it is reviewed in pull requests. The module project's ADR lives at labs/m06-banking-data-store/docs/adr-0001-ledger-data-store.md and is a worked example.
Set a review trigger
Write down the event that would make you revisit the decision, for example "peak writes exceed 2,000/s" or "the business asks for active-active in two Regions". Decisions age; triggers keep them honest.
AWS Console instructions
No resources are created. To explore what is available in your Region, without cost:
- Console → RDS → Create database: look at the engine options, then cancel. Note how the "Templates" choice (Production / Dev/Test / Free tier) changes Multi-AZ and instance class defaults.
- Console → DynamoDB → Tables → Create table: note the two capacity modes (on-demand and provisioned), then cancel.
- Console → Pricing Calculator (calculator.aws): estimate an RDS for PostgreSQL
db.t4g.microand a DynamoDB on-demand table with your access-pattern numbers. Compare the monthly totals at idle and at peak.
AWS CLI
These read-only commands help you see which engines and versions are offered. They cost nothing.
# Which PostgreSQL major versions can RDS create in us-east-1? (read-only, free)
aws rds describe-db-engine-versions --engine postgres \
--query "DBEngineVersions[?Status=='available'].EngineVersion" \
--output text --profile learn-sandbox --region us-east-1
# Which instance classes support RDS for PostgreSQL 17 here? Useful before you pick a size.
aws rds describe-orderable-db-instance-options --engine postgres --engine-version 17.6 \
--query "OrderableDBInstanceOptions[].DBInstanceClass" --output text \
--profile learn-sandbox --region us-east-1
# Is a DynamoDB table already present? (Empty list is the expected answer in a fresh sandbox.)
aws dynamodb list-tables --profile learn-sandbox --region us-east-1If 17.6 is not listed in the first command's output, use a version that is. Minor versions change every few months ⚠ VERIFY the minor version before running the second command.
Infrastructure as Code
You don't deploy anything in this lesson. What belongs in source control is the decision, next to the infrastructure code that implements it. A practical convention:
docs/
adr-0001-ledger-data-store.md <- the "why"
infra/
terraform/main.tf <- the "what" (links back to ADR-0001 in a comment)# infra/terraform/main.tf
# Data store choice: see docs/adr-0001-ledger-data-store.md (RDS for PostgreSQL, not Aurora / DynamoDB).
# Review trigger: sustained writes > 2,000/s or a cross-Region active-active requirement.
resource "aws_db_instance" "ledger" {
# ...defined in the module project, labs/m06-banking-data-store/infra/terraform
}// In a CDK app, record the decision as stack metadata and tags, so it travels with the resources.
using Amazon.CDK;
using Constructs;
public sealed class LedgerStack : Stack
{
public LedgerStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
// Visible in the CloudFormation template and in the console's stack metadata.
TemplateOptions.Description = "Harbourline ledger data store - decision: docs/adr-0001-ledger-data-store.md";
Amazon.CDK.Tags.Of(this).Add("DecisionRecord", "ADR-0001");
// Database resources are added in the RDS lesson.
}
}Complete code
A decision needs evidence. This small, runnable C# console app turns your access-pattern estimates into a monthly cost comparison between a provisioned relational instance and DynamoDB on-demand. It runs locally, needs no AWS account and uses no NuGet packages.
The prices are inputs you must look up on the AWS pricing pages for your Region on the day you decide. Never hard-code them into a decision document without a date.
using System.Globalization;
// Rough monthly cost sketch: relational instance vs DynamoDB on-demand, for ONE workload.
// Usage: dotnet run -- <writesPerSecAvg> <readsPerSecAvg> <itemKb> <storageGb>
// Example: dotnet run -- 20 200 1 50
var inv = CultureInfo.InvariantCulture;
var writesPerSec = args.Length > 0 ? double.Parse(args[0], inv) : 20;
var readsPerSec = args.Length > 1 ? double.Parse(args[1], inv) : 200;
var itemKb = args.Length > 2 ? double.Parse(args[2], inv) : 1;
var storageGb = args.Length > 3 ? double.Parse(args[3], inv) : 50;
// ---- PRICE INPUTS (USD, us-east-1). Look these up on the pricing pages and date them. ----
var p = new Prices(
InstanceHourly: 0.016m, // e.g. a small burstable RDS for PostgreSQL instance ⚠ VERIFY
RelationalStorageGbMonth: 0.115m, // gp3 storage per GB-month ⚠ VERIFY
MultiAzFactor: 2m, // Multi-AZ instance deployment roughly doubles instance + storage
DdbWritePerMillion: 0.625m, // on-demand write request units per million ⚠ VERIFY
DdbReadPerMillion: 0.125m, // on-demand read request units per million ⚠ VERIFY
DdbStorageGbMonth: 0.25m); // standard table class storage per GB-month ⚠ VERIFY
const double SecondsPerMonth = 730 * 3600; // AWS pricing uses 730 hours per month
// DynamoDB: 1 write unit per 1 KB written; 1 read unit per 4 KB read (strongly consistent).
// Eventually consistent reads cost half. We assume strongly consistent reads for balances.
var writeUnits = writesPerSec * SecondsPerMonth * Math.Ceiling(itemKb / 1.0);
var readUnits = readsPerSec * SecondsPerMonth * Math.Ceiling(itemKb / 4.0);
var ddb = (decimal)(writeUnits / 1_000_000) * p.DdbWritePerMillion
+ (decimal)(readUnits / 1_000_000) * p.DdbReadPerMillion
+ (decimal)storageGb * p.DdbStorageGbMonth;
var rdsSingleAz = 730m * p.InstanceHourly + (decimal)storageGb * p.RelationalStorageGbMonth;
var rdsMultiAz = rdsSingleAz * p.MultiAzFactor;
Console.WriteLine($"Workload: {writesPerSec} writes/s, {readsPerSec} reads/s, {itemKb} KB items, {storageGb} GB");
Console.WriteLine($"DynamoDB on-demand : {ddb,10:N2} USD/month");
Console.WriteLine($"RDS single-AZ : {rdsSingleAz,10:N2} USD/month (instance must also be big enough!)");
Console.WriteLine($"RDS Multi-AZ : {rdsMultiAz,10:N2} USD/month");
Console.WriteLine();
Console.WriteLine("Cost is one input. Re-read your access patterns and integrity needs before deciding.");
internal sealed record Prices(
decimal InstanceHourly,
decimal RelationalStorageGbMonth,
decimal MultiAzFactor,
decimal DdbWritePerMillion,
decimal DdbReadPerMillion,
decimal DdbStorageGbMonth);Create and run it:
dotnet new console -n StoreCostSketch -f net10.0
# replace Program.cs with the code above
dotnet run --project StoreCostSketch -- 20 200 1 50Here is the ADR template. Copy it into docs/adr-000N-<topic>.md:
# ADR-000N: <decision in a few words>
- Status: Proposed | Accepted | Superseded by ADR-000M
- Date: YYYY-MM-DD
- Deciders: <team, architect, data protection reviewer>
- Data classification: <e.g. personal data (UK GDPR), card data out of scope>
## Context
What workload, which access patterns (table with frequency/latency/shape), which non-negotiables.
## Options considered
| Option | Fit | Why chosen / not chosen |
## Decision
What we chose and the specific configuration that makes it correct (keys, constraints, encryption, HA).
## Consequences
Positive, negative, follow-ups. What this makes harder.
## Review trigger
The measurable event that reopens this decision.
## Compliance note
Technical controls only; retention/erasure/residency need compliance sign-off.Code explained line by line
var inv = CultureInfo.InvariantCulture;: parses1.5the same way on a UK or German Windows locale. Banking code should never depend on the machine's culture.- The
argslines read four numbers with sensible defaults, sodotnet runalone works. Pricesis a record of inputs, each marked ⚠ VERIFY. The point of the tool is to make you look the prices up and date them. It is not a price list.MultiAzFactor: 2m: a Multi-AZ DB instance deployment runs a synchronous standby you pay for, so instance and storage roughly double. This is an estimate; check the pricing page.SecondsPerMonth = 730 * 3600: AWS prices monthly usage as 730 hours.writeUnits: DynamoDB charges one write request unit per 1 KB written, rounded up per item. A 1.2 KB item costs 2 units per write.readUnits: a strongly consistent read costs one read request unit per 4 KB. Eventually consistent reads cost half. Balances need strong reads, so the sketch assumes the more expensive option.ddb = ...: monthly DynamoDB cost is requests plus storage. There is no idle charge, which is why it wins for spiky or low-volume workloads.rdsSingleAz = 730m * p.InstanceHourly + ...: a relational instance bills every hour, busy or idle. The warning in the output matters: the instance must be large enough for peak load, and that sizing is not modelled here.- The final line is the lesson in code: cost is one of five inputs.
Security considerations
- Classification travels with data. Personal data copied to a cache, lake or warehouse is still personal data. Each copy needs encryption, access control and a retention rule, under UK GDPR's storage-limitation principle.
- Fewer stores = smaller attack surface. Every new engine adds credentials, network paths, patching and monitoring. Prefer extending an existing store (for example PostgreSQL JSONB) when it fits.
- Choose stores that support your security baseline: encryption with a customer managed KMS key, private networking (VPC or VPC endpoints), IAM-based access, audit logging. All the stores in the table support these, with different mechanisms. Check before committing.
- Card data (PCI DSS): keep primary account numbers out of general-purpose stores entirely. Store tokens, and keep the card data environment as small as possible.
⚠ Learning shortcut — not for production
In this course, one person makes the data-store decision in an afternoon. In a bank, a data-store decision for a system of record goes through architecture review, a data protection impact assessment (DPIA) where personal data is involved, security review and often a risk committee. The ADR you write here is the input to that process, not a replacement for it.
Banking use case
Harbourline's architecture review applied the framework to five workloads. Fictional outcome:
| Workload | Key access patterns | Decision | Rejected and why |
|---|---|---|---|
| Core ledger (accounts, postings) | Post with idempotency; balance; statement by date; ad-hoc audit SQL | RDS for PostgreSQL, Multi-AZ; Aurora when RTO under 1 minute is required | DynamoDB: ad-hoc audit queries and multi-row integrity are harder; SQL Server: licence cost |
| Payment status | Get by payment ID (2,000/s); update status; idempotency key check | DynamoDB on-demand with conditional writes, Streams to the audit trail | RDS: needs headroom for spikes; the access pattern is pure key-value |
| FX rates and product reference data | Read-heavy, changes hourly | ElastiCache Valkey cache-aside in front of PostgreSQL | DynamoDB DAX: possible, but the source of truth is relational |
| Regulatory and finance reporting | Large scans and aggregations over 7 years | S3 data lake (Parquet) + Athena, Redshift for interactive BI | Querying the ledger at month-end: slows down posting |
| Onboarding form drafts | Save and resume a semi-structured form | PostgreSQL JSONB column in the onboarding schema | DocumentDB: a second engine for one table is not worth the operational cost |
Notice that the ledger did not move to NoSQL and payment status did not stay in the ledger. The reasons are written down, so the next architect can challenge them.
Common mistakes
- Choosing by fashion or by CV. "DynamoDB is modern" is not an access pattern. Write the access patterns down first.
- Assuming NoSQL means "no schema". DynamoDB has a strict schema for keys and an implicit one for everything else. Changing your access patterns later can mean rewriting the table.
- Using the OLTP database for analytics. Month-end reports on the primary instance slow down postings. Use a read replica, an export or the lake.
- Forgetting idle cost. A provisioned instance for a workload that runs one hour a day wastes 23 hours of spend. Look at serverless options.
- Ignoring licence cost. RDS for SQL Server Enterprise licence-included pricing is a big step up. Compare it with PostgreSQL before defaulting to the familiar engine.
- No review trigger. Decisions made for 50 writes/s quietly become wrong at 5,000 writes/s.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| A "simple" report takes hours | OLTP store used for analytics, or DynamoDB scan | Move analytics to S3/Athena or Redshift; use replicas or exports |
| DynamoDB bill much higher than expected | Scans, large items, strongly consistent reads, or hot on-demand traffic | Check access patterns; use queries not scans; consider provisioned capacity |
| Database CPU pinned at month-end | Batch and online workloads share one instance | Separate the workloads; schedule batch on a replica or the lake |
| Can't answer an auditor's question quickly | Store chosen for a single access pattern | Stream changes to S3; keep an analytics copy |
| Two teams store the "same" customer differently | No agreed system of record | Name one system of record per entity in the architecture docs |
Cost and cleanup
This lesson creates no resources and costs nothing. The CLI commands are read-only and free. If you opened a Create database or Create table page in the console, make sure you chose Cancel, then check:
# Expect no instances and no tables in a fresh sandbox
aws rds describe-db-instances --query "DBInstances[].DBInstanceIdentifier" --profile learn-sandbox --region us-east-1
aws dynamodb list-tables --profile learn-sandbox --region us-east-1Exercises
- 🟢 Guided: For the payment status workload, fill in an access-pattern table (at least five patterns with frequency, latency and shape) and run
StoreCostSketchwith your numbers for both average and peak load. - 🟡 Challenge: Write a full ADR for customer notification preferences (email/SMS/push opt-ins, read on every notification, updated rarely, must be auditable for consent under UK GDPR). Shortlist at least three options.
- 🔴 Live problem: A ticket arrives: "The fraud team wants to query 'all customers who share a device, phone number or beneficiary with a known mule account, up to 3 hops'. The ledger team says that's impossible in PostgreSQL. Please advise by Friday." Decide what you would recommend, what you would prototype first, and what you would refuse to do.
Assignment
Add docs/adr-0001-<workload>.md to your portfolio repository for a workload of your choice (Harbourline or your own fictional bank). It must include an access-pattern table, at least three options with reasons, the decision, consequences, a review trigger and the compliance note. Link it from your repository README.
Solutions
A reasonable access-pattern table for payment status:
| # | Pattern | Frequency | Latency | Shape |
|---|---|---|---|---|
| P1 | Create payment (with idempotency key) | 100/s, 1,000/s payday peak | under 50 ms | Single item, conditional put |
| P2 | Get payment by payment ID | 2,000/s | under 20 ms | Key lookup |
| P3 | Update status (validated → screening → submitted → settled) | 300/s | under 50 ms | Single item, conditional update on current status |
| P4 | List a customer's payments in the last 90 days | 100/s | under 100 ms | Query by customer + date range |
| P5 | Operational report of rejected payments by scheme per day | Hourly | Minutes | Aggregation → lake |
dotnet run -- 100 2000 1 20 shows that DynamoDB on-demand costs scale with requests, while an instance costs the same at idle. Why it matters: P1–P4 are key-based with known shapes, so DynamoDB fits. P4 needs a secondary index (customer ID + date). P5 belongs in the lake via Streams or export. The integrity rule "status only moves forward" is enforced with a condition expression on P3.
Options: (1) a table in the existing PostgreSQL customer schema; (2) DynamoDB keyed by customer ID; (3) ElastiCache only.
ElastiCache alone fails immediately: consent is a record you must be able to prove, and a cache is not durable evidence. Between PostgreSQL and DynamoDB, the deciding factors are the audit trail and joins. Consent changes must be kept as history: who, when, which channel, which wording version. In PostgreSQL that is an append-only consent_events table plus a current-state view, joined to customers for reports. DynamoDB can hold the same data with an item per event, but reporting joins move to the lake.
Most teams choose PostgreSQL here (low volume, high audit need, already operated), with an optional cache for the hot read path. The review trigger: notification volume beyond what the read path can handle with caching.
"Up to 3 hops through shared attributes" is a graph traversal. In PostgreSQL you can write recursive CTEs, and for a modest dataset they work. So "impossible" is wrong, but the query gets slow and hard to tune as connections fan out.
Recommendation:
- Prototype the question with a recursive CTE on a read replica or a lake copy, never the primary.
- If performance or expressiveness is not good enough, prototype Amazon Neptune fed from the lake (customers, devices, phones and beneficiaries as vertices, "uses" as edges).
Refuse to give the fraud team write access to the ledger, or to run exploratory graph queries on the primary at peak hours. Note the data protection angle: linking customers via devices and phone numbers is profiling. Involve the DPO and record the lawful basis before building it.
Knowledge check
Answer: Because every store optimises for some questions at the expense of others. Relational stores optimise for flexible queries; key-value stores optimise for known key lookups at any scale; columnar stores optimise for scans. Without the questions you cannot tell which trade-off you are making. Choosing by "scalability" alone ignores that most bank workloads are limited by integrity and query needs, not raw scale.
Answer: A cache (ElastiCache Valkey, cache-aside) in front of PostgreSQL, or read replicas if the reads need SQL. Moving the source of truth to DynamoDB would duplicate the system of record without need. A Redshift copy would be the wrong latency class.
Answer: The system of record is the authoritative store for an entity; derived copies (caches, lake files, warehouse tables) are rebuilt from it. For UK GDPR, both hold personal data, so erasure requests, retention limits and access controls apply to every copy. Knowing which is which tells you where to fix data and how to propagate deletions.
Answer: It avoids running a second engine, with its own credentials, backups, patching and monitoring, for a modest workload. It also lets you update relational and document data in one transaction. A dedicated document store makes sense when document workloads are large, central and need its specific features.
Interview questions
- "How do you decide between RDS and DynamoDB?" Start from access patterns and integrity needs, not scale. If the questions are ad hoc, relational and need multi-row constraints, choose RDS or Aurora. If they are known key lookups at high or spiky volume, with integrity expressible as conditional writes, choose DynamoDB. Then check operations, skills and cost at idle and at peak.
- "Your team wants a new database engine for one service. What do you ask?" What access pattern can't the existing stores serve? Who will operate it at 3 a.m.? How will it be backed up and restored, and how will we prove that? What does it cost at idle? How does it meet our encryption, network and audit baseline?
- "Where does reporting run in your design?" Not on the OLTP primary at peak. Use a read replica for light operational reports, and the lake (S3 + Athena) or a warehouse (Redshift) for heavy and historical analysis, fed by change data capture or exports.
- "What goes in an ADR?" Context and access patterns, options with reasons, the decision and its key configuration, consequences, a review trigger, and a compliance note.
Certification practice
A payments company stores the status of each payment. The application reads a payment by its ID about 3,000 times per second and updates its status a few times during its life. Traffic is five times higher on paydays. The team wants single-digit-millisecond reads and no capacity planning. Which solution meets these requirements?
A. Amazon RDS for PostgreSQL with a large instance class and a read replica B. Amazon DynamoDB in on-demand capacity mode, keyed by payment ID C. Amazon Redshift Serverless with a sort key on payment ID D. Amazon S3 with one object per payment and S3 Select
Answer: B. The access pattern is a key lookup with spiky volume, which is exactly what DynamoDB on-demand serves without capacity planning. A needs sizing for the peak and still has one writer. C is a warehouse built for analytical scans, not high-rate point lookups and updates. D is object storage with no single-digit-millisecond conditional updates.
A bank runs monthly regulatory reports that scan seven years of transaction history. The reports currently run on the production PostgreSQL primary and slow down online postings. The reports can tolerate data that is up to 24 hours old. What is the MOST cost-effective solution?
A. Increase the primary instance size during month-end B. Add an Aurora read replica and run the reports there C. Export transactions daily to Amazon S3 in Parquet, partitioned by booking date, and query them with Amazon Athena D. Cache the report results in Amazon ElastiCache
Answer: C. Seven years of history and 24-hour tolerance suit a partitioned columnar lake with pay-per-query Athena. A keeps the contention and adds cost. B moves engines and still runs large scans on an OLTP engine. D only caches results that must first be computed somewhere.
A developer must make "create payment" idempotent. Clients send an idempotency key, and the same key must never create two payments, even if two requests arrive at the same time on different containers. The payments are stored in Amazon DynamoDB. What should the developer do?
A. Check whether the key exists with GetItem, then call PutItem if it does not
B. Call PutItem with a condition expression attribute_not_exists on the key attribute
C. Store the keys in an in-memory dictionary in each container
D. Use a strongly consistent Scan to look for the key before writing
Answer: B. A conditional write is atomic in the store, so only one of two racing requests can succeed. A has a race window between the read and the write. C is per container and lost on restart. D is slow, expensive and still racy.
Completion checklist
- I can describe each AWS database service in one sentence and name a banking use for it
- I can explain the five decision questions and their order
- I completed an access-pattern table and ran the cost sketch
- I wrote an ADR with at least three options and a review trigger
- I confirmed no RDS instances or DynamoDB tables were created
Key takeaways
- Access patterns first, store second. Write the questions down with frequency, latency and shape.
- Relational stores buy query flexibility and integrity; key-value stores buy predictable speed at scale for known questions; warehouses and lakes buy cheap analysis of history.
- Keep one system of record per entity; caches, lakes and warehouses hold derived copies that inherit the data's classification.
- Cost has two shapes, per hour and per request. Compare them at idle, average and peak, and include licences, backups and keys.
- Record every decision in an ADR with a review trigger, stored next to the code.
Further reading
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Module 6: Databases & Data Architecture
Map your SQL Server experience onto the AWS database family: RDS, Aurora, DynamoDB, ElastiCache, DocumentDB, Redshift and S3. You learn to pick the right store for each banking workload, migrate SQL Server schemas and data safely, and connect a .NET API with EF Core, Secrets Manager credentials and least-privilege access.
SQL Server on AWS: RDS, EC2, Aurora PostgreSQL and Babelfish compared
Compare features, high availability, backups, tooling and licensing for SQL Server workloads across RDS for SQL Server, RDS Custom, SQL Server on EC2, Aurora PostgreSQL and Babelfish, and scan your T-SQL for blockers before you choose.