Amazon Redshift and S3 as data stores
Separate operational and analytical data: move transaction history out of the OLTP database into a partitioned S3 data lake with a reconciled archive job, query it with Athena, and know when a Redshift Serverless warehouse (or zero-ETL) earns its cost.
Assumes: .NET 10 LTS ยท Npgsql 10.0.3 ยท AWS SDK for .NET v4 (AWSSDK.S3 4.1.1, AWSSDK.Athena 4.1.1.1, AWSSDK.RedshiftDataAPIService 4.1.0.2) ยท PostgreSQL 17 ยท Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain the difference between OLTP and OLAP workloads, and between a data warehouse and a data lake, using Harbourline examples.
- Argue โ with storage prices and operational evidence โ why most transaction history should leave the OLTP database.
- Build a .NET archive job that exports closed days from PostgreSQL to a partitioned, encrypted S3 data lake, reconciles counts and totals, and only then deletes from the source.
- Query the archive with Amazon Athena using partition projection, and run SQL on Redshift Serverless through the Redshift Data API from C#.
- Choose between Athena on S3, Redshift Serverless, provisioned Redshift and zero-ETL integrations for a given reporting need.
Prerequisites
- Transactions, isolation levels and consistency โ the
hl-m06-pgPostgreSQL container - Protecting data in S3: Object Lock and Macie โ bucket security baseline
- AWS CLI v2 with the
learn-sandboxprofile; .NET 10 SDK; Docker Desktop - Module 7: Data engineering goes deeper into pipelines, Glue and Lake Formation
Why this matters in banking
Harbourline's core ledger database holds nine years of transactions: 2.1 billion rows, 1.8 TB. Ninety-five per cent of queries touch the last 90 days. Yet every one of the following is driven by the other 1.7 TB:
- The nightly full backup takes four hours; a point-in-time restore drill took six โ breaching the one-hour recovery time objective the business agreed.
- Index maintenance on
transactionscompetes with the 02:00 batch window. - A "quick" ad hoc query by the finance team (
SUMby merchant category for 2019) scanned 400 GB on the production replica and slowed the mobile app. - Storage on a Multi-AZ RDS instance costs several times what the same bytes cost in S3.
Meanwhile the data team wants five years of history for fraud models and the regulators expect records to be retained for a defined period โ and, under UK GDPR's storage-limitation principle, not retained beyond it. The answer is not "a bigger database"; it is moving history to the right store, provably and safely.
The idea in plain language
- OLTP (online transaction processing): many small reads and writes of individual rows, low latency, strict consistency. Posting a payment. Lives in RDS/Aurora/DynamoDB.
- OLAP (online analytical processing): few large queries that scan and aggregate millions of rows. "Total card spend by MCC per month for five years." Wants columnar storage.
- Data lake: files (ideally Parquet or Iceberg tables) in Amazon S3, described by a catalogue (AWS Glue Data Catalog) and queried in place by engines such as Athena, Redshift Spectrum, EMR or Glue. Cheap storage, pay per query.
- Data warehouse: a database built for OLAP โ Amazon Redshift โ with columnar storage, massively parallel processing, materialised views, concurrency for many dashboard users, and predictable performance.
Analogy: The OLTP database is the branch's working ledger on the counter โ current, fast, small. The data lake is the bank's records archive: vast, cheap, organised by year/month/day in labelled boxes, and you pay a clerk per box opened. The warehouse is the finance department's analysis room: pre-sorted, indexed copies on desks, with many analysts working at once โ you pay for the room whether or not anyone is in it (or, with Serverless, by the minute when it's used).
What problem each store solves
| Store | Strengths | Limits and costs (us-east-1, checked 2026-11-18) |
|---|---|---|
| RDS/Aurora (OLTP) | Row-level ACID, low latency | Expensive per GB; big tables slow backups, restores, maintenance |
| S3 data lake | Cheapest durable storage; any engine; lifecycle to archive tiers; Object Lock for immutability | S3 Standard about USD 0.023 per GB-month for the first 50 TB; no transactions across files unless you use a table format (Iceberg / S3 Tables) |
| Athena on S3 | Serverless SQL, no infrastructure, pay per query | USD 5 per TB scanned; performance depends on file format and partitioning; not for sub-second dashboards with many users |
| Redshift Serverless | Warehouse performance without managing nodes; auto-pause | USD 0.375 per RPU-hour; base capacity from 4 to 1,024 RPUs; billed per second with a 60-second minimum; managed storage USD 0.024 per GB-month; USD 300 free-trial credit for first-time Serverless users (expires after 90 days) |
| Redshift provisioned (RA3) | Predictable, reservable capacity for steady heavy use | Paid per node-hour even when idle (pause/resume available) |
| Zero-ETL integrations | Near-real-time replication from Aurora PostgreSQL, RDS for PostgreSQL/MySQL/Oracle and DynamoDB into Redshift without pipelines | Source-version requirements (e.g. Aurora PostgreSQL 16.4+); a warehouse to pay for; not every data type or DDL change is supported โ VERIFY for your source |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Azure Synapse dedicated SQL pool | Amazon Redshift (provisioned RA3) | Both MPP columnar; Redshift uses distribution and sort keys, Synapse uses distributions and columnstore |
| Synapse serverless SQL pool over ADLS | Amazon Athena over S3 | Both pay per TB scanned and query files in place |
| Microsoft Fabric warehouse / lakehouse | Redshift Serverless + S3 (Iceberg / S3 Tables) + Glue Data Catalog | AWS composes separate services; Fabric bundles them |
| ADLS Gen2 | Amazon S3 | S3 has no hierarchical namespace; "folders" are key prefixes |
| Azure Data Factory copy activity | AWS Glue jobs, DMS, zero-ETL integrations | Zero-ETL removes the pipeline for supported sources |
| SQL Server table partitioning + archive DB | Export to S3 + lifecycle rules | Archive is cheaper and queryable in place |
| Synapse Link for Azure SQL / Cosmos DB | Redshift zero-ETL integrations | Same idea: operational data replicated to analytics automatically |
SqlConnection to Synapse | Redshift Data API (HTTPS, IAM) or JDBC/ODBC/Npgsql-compatible drivers | The Data API needs no driver, no connection pool and no VPC connectivity from the caller |
Architecture
Security-boundary view
The data lake usually lives in a separate data-platform account. The archive job may write but never delete there; analysts may read curated tables but not raw personal data. Module 7 covers Lake Formation column-level permissions.
Why history should leave the OLTP database
| Measure | Keeping 9 years in Aurora | 90 days in Aurora + history in S3 |
|---|---|---|
| Restore time | Hours (proportional to size) | Minutes for the hot database |
| Storage cost for 1.7 TB of history | Database storage rates (RDS Multi-AZ gp3 is billed for both copies) | About USD 40/month in S3 Standard; less in Glacier tiers |
| Ad hoc analytics | Runs on production or a replica | Runs on Athena/Redshift, isolated from customers |
| Schema changes | Rewrite billions of rows | Hot table only; history keeps its own schema version |
| Retention enforcement | DELETE jobs on huge tables | S3 lifecycle expiration by prefix, provable |
The rule Harbourline adopts: operational databases keep what operations need (90 days to 13 months); everything else lives in the lake with an explicit retention period agreed with Compliance.
Step-by-step implementation
Prepare data in local PostgreSQL
Start the lesson-9 container if needed and create a richer transactions table:
CREATE TABLE IF NOT EXISTS transactions (
transaction_id uuid PRIMARY KEY,
account_id text NOT NULL,
booking_date date NOT NULL,
amount numeric(18,2) NOT NULL,
currency char(3) NOT NULL,
type text NOT NULL,
channel text NOT NULL,
merchant_category_code text,
status text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ix_transactions_booking_date ON transactions (booking_date);
-- 30 days ร 2,000 fictional transactions per day.
INSERT INTO transactions
SELECT gen_random_uuid(),
'ACC-' || lpad((1 + (random() * 999)::int)::text, 10, '0'),
DATE '2026-09-01' + (g / 2000),
round((random() * -200)::numeric, 2),
'GBP',
(ARRAY['card_payment','direct_debit','transfer_out','fee'])[1 + (random() * 3)::int],
(ARRAY['card','mobile','web','batch'])[1 + (random() * 3)::int],
(ARRAY['5411','5812','4111','5999', NULL])[1 + (random() * 4)::int],
'booked', now()
FROM generate_series(0, 59999) AS g;Get-Content transactions.sql | docker exec -i hl-m06-pg psql -U postgresCreate the encrypted lake bucket
ACCOUNT=$(aws sts get-caller-identity --query Account --output text --profile learn-sandbox)
BUCKET=hl-m06-lake-$ACCOUNT
aws s3api create-bucket --bucket $BUCKET --profile learn-sandbox --region us-east-1
aws s3api put-public-access-block --bucket $BUCKET --public-access-block-configuration \
BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true --profile learn-sandbox
aws s3api put-bucket-encryption --bucket $BUCKET --server-side-encryption-configuration \
'{"Rules":[{"ApplyServerSideEncryptionByDefault":{"SSEAlgorithm":"aws:kms","KMSMasterKeyID":"alias/aws/s3"},"BucketKeyEnabled":true}]}' \
--profile learn-sandbox
aws s3api put-bucket-tagging --bucket $BUCKET --tagging \
'TagSet=[{Key=Project,Value=cea-lab},{Key=Lab,Value=m06-lake},{Key=Environment,Value=learn}]' --profile learn-sandboxThe AWS managed key aws/s3 keeps the lab free of KMS charges; production uses a customer managed key.
Run the archive job
Run the Complete code archive job with ARCHIVE_BEFORE=2026-09-20. It exports each closed day to transactions/year=2026/month=09/day=DD/part-0000.csv.gz, writes a manifest with row count and sum, verifies the upload, then deletes those rows from PostgreSQL in the same transaction that re-checks the totals.
Define the Athena table
CREATE DATABASE IF NOT EXISTS hl_lake;
CREATE EXTERNAL TABLE IF NOT EXISTS hl_lake.transactions (
transaction_id string,
account_id string,
booking_date date,
amount decimal(18,2),
currency string,
type string,
channel string,
merchant_category_code string,
status string,
created_at string
)
PARTITIONED BY (year string, month string, day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3://hl-m06-lake-111122223333/transactions/'
TBLPROPERTIES (
'skip.header.line.count' = '1',
'projection.enabled' = 'true',
'projection.year.type' = 'integer', 'projection.year.range' = '2018,2030',
'projection.month.type' = 'integer', 'projection.month.range' = '1,12', 'projection.month.digits' = '2',
'projection.day.type' = 'integer', 'projection.day.range' = '1,31', 'projection.day.digits' = '2',
'storage.location.template' = 's3://hl-m06-lake-111122223333/transactions/year=${year}/month=${month}/day=${day}/'
);Replace the account ID. Partition projection computes partitions from the template instead of storing them in the catalogue, so new days are queryable without running MSCK REPAIR TABLE.
Query
Run the Athena query from Complete code (or the console): spend by MCC for 1โ19 September. Note the data scanned โ a few hundred KB. Then convert to Parquet in Module 7 and compare.
AWS Console instructions
- Athena: Console โ Athena โ Query editor โ Settings โ Manage: set the query result location to
s3://<bucket>/athena-results/(or use a workgroup with a managed result location), with encryption SSE-KMS. Create a workgrouphl-m06with a per-query data usage limit (e.g. 1 GB) so a mistaken query cannot scan terabytes. - Glue Data Catalog: after running the DDL, Console โ AWS Glue โ Data Catalog โ Databases โ hl_lake โ Tables โ transactions shows the schema and projection properties.
- Redshift Serverless (optional): Console โ Amazon Redshift โ Serverless dashboard โ Create workgroup. Set base capacity to the minimum (4 RPUs), choose private subnets and a security group, use the default namespace with an IAM role that can read the lake bucket, and set a usage limit (RPU-hours per day) with action Turn off user queries. Use Query editor v2 to run SQL.
- S3 lifecycle: S3 โ bucket โ Management โ Create lifecycle rule on prefix
transactions/: transition to Glacier Instant Retrieval after 365 days; expire after the retention period Compliance specifies.
AWS CLI
# Athena workgroup with an encrypted result location and a per-query scan limit (1 GB).
aws athena create-work-group --name hl-m06 --configuration \
"ResultConfiguration={OutputLocation=s3://$BUCKET/athena-results/,EncryptionConfiguration={EncryptionOption=SSE_KMS,KmsKey=alias/aws/s3}},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=1073741824,PublishCloudWatchMetricsEnabled=true" \
--tags Key=Project,Value=cea-lab Key=Lab,Value=m06-lake --profile learn-sandbox --region us-east-1
# Run a query and fetch results.
QID=$(aws athena start-query-execution --work-group hl-m06 \
--query-string "SELECT merchant_category_code, count(*) n, sum(amount) total FROM hl_lake.transactions WHERE year='2026' AND month='09' GROUP BY 1 ORDER BY 3" \
--query QueryExecutionId --output text --profile learn-sandbox --region us-east-1)
aws athena get-query-execution --query-execution-id $QID \
--query "QueryExecution.[Status.State,Statistics.DataScannedInBytes]" --profile learn-sandbox --region us-east-1
aws athena get-query-results --query-execution-id $QID --profile learn-sandbox --region us-east-1
# Lifecycle rule: Glacier Instant Retrieval after 1 year, expire after 7 years (example only โ Compliance sets this).
aws s3api put-bucket-lifecycle-configuration --bucket $BUCKET --lifecycle-configuration '{
"Rules":[{"ID":"transactions-tiering","Filter":{"Prefix":"transactions/"},"Status":"Enabled",
"Transitions":[{"Days":365,"StorageClass":"GLACIER_IR"}],
"Expiration":{"Days":2557}}]}' --profile learn-sandbox
# Export a DynamoDB table to the lake without consuming read capacity (requires PITR on the table).
aws dynamodb export-table-to-point-in-time --table-arn arn:aws:dynamodb:us-east-1:111122223333:table/hl-m06-banking \
--s3-bucket $BUCKET --s3-prefix dynamodb-exports/ --export-format DYNAMODB_JSON \
--profile learn-sandbox --region us-east-1Infrastructure as Code
using Amazon.CDK;
using Amazon.CDK.AWS.Athena;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.S3;
using Constructs;
namespace Harbourline.Infra;
public sealed class LakeStack : Stack
{
public LakeStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
var key = new Key(this, "LakeKey", new KeyProps { Alias = "alias/hl-m06-lake", EnableKeyRotation = true });
var lake = new Bucket(this, "Lake", new BucketProps
{
BucketName = $"hl-m06-lake-{Account}",
BlockPublicAccess = BlockPublicAccess.BLOCK_ALL,
Encryption = BucketEncryption.KMS,
EncryptionKey = key,
BucketKeyEnabled = true,
EnforceSSL = true,
Versioned = true,
LifecycleRules = new[]
{
new LifecycleRule
{
Id = "transactions-tiering",
Prefix = "transactions/",
Transitions = new[] { new Transition { StorageClass = StorageClass.GLACIER_INSTANT_RETRIEVAL, TransitionAfter = Duration.Days(365) } },
Expiration = Duration.Days(2557), // example: set from the records retention schedule
NoncurrentVersionExpiration = Duration.Days(30)
}
},
RemovalPolicy = RemovalPolicy.DESTROY, // lab only
AutoDeleteObjects = true // lab only
});
_ = new CfnWorkGroup(this, "Athena", new CfnWorkGroupProps
{
Name = "hl-m06",
WorkGroupConfiguration = new CfnWorkGroup.WorkGroupConfigurationProperty
{
EnforceWorkGroupConfiguration = true,
BytesScannedCutoffPerQuery = 1_073_741_824,
PublishCloudWatchMetricsEnabled = true,
ResultConfiguration = new CfnWorkGroup.ResultConfigurationProperty
{
OutputLocation = $"s3://{lake.BucketName}/athena-results/",
EncryptionConfiguration = new CfnWorkGroup.EncryptionConfigurationProperty
{
EncryptionOption = "SSE_KMS",
KmsKey = key.KeyArn
}
}
},
RecursiveDeleteOption = true
});
Tags.SetTag("Project", "cea-lab");
Tags.SetTag("Lab", "m06-lake");
Tags.SetTag("Environment", "learn");
}
}Complete code
Archive job: export โ verify โ delete
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="Npgsql" Version="10.0.3" />
<PackageReference Include="AWSSDK.S3" Version="4.1.1" />
<PackageReference Include="AWSSDK.Athena" Version="4.1.1.1" />
</ItemGroup>
</Project>using System.Globalization;
using System.IO.Compression;
using System.Text;
using System.Text.Json;
using Amazon.S3;
using Amazon.S3.Model;
using Npgsql;
namespace Harbourline.DataPlatform.Archive;
public sealed record DayManifest(DateOnly BookingDate, long RowCount, decimal TotalAmount, string ObjectKey, string Sha256);
public sealed class ArchiveJob(NpgsqlDataSource db, IAmazonS3 s3, string bucket)
{
private static readonly CultureInfo Inv = CultureInfo.InvariantCulture;
public async Task ArchiveBeforeAsync(DateOnly cutoff, CancellationToken ct)
{
foreach (var day in await ClosedDaysAsync(cutoff, ct))
{
var manifest = await ExportDayAsync(day, ct);
await VerifyUploadAsync(manifest, ct);
await DeleteDayAsync(manifest, ct);
Console.WriteLine($"{day:yyyy-MM-dd}: archived {manifest.RowCount} rows, total {manifest.TotalAmount:0.00}");
}
}
private async Task<List<DateOnly>> ClosedDaysAsync(DateOnly cutoff, CancellationToken ct)
{
await using var cmd = db.CreateCommand(
"SELECT DISTINCT booking_date FROM transactions WHERE booking_date < $1 ORDER BY 1");
cmd.Parameters.AddWithValue(cutoff);
var days = new List<DateOnly>();
await using var r = await cmd.ExecuteReaderAsync(ct);
while (await r.ReadAsync(ct)) days.Add(r.GetFieldValue<DateOnly>(0));
return days;
}
private async Task<DayManifest> ExportDayAsync(DateOnly day, CancellationToken ct)
{
var key = $"transactions/year={day:yyyy}/month={day:MM}/day={day:dd}/part-0000.csv.gz";
long rows = 0;
decimal total = 0;
// Stream rows into a gzip buffer. For very large days, use multipart upload instead of MemoryStream.
await using var buffer = new MemoryStream();
await using (var gzip = new GZipStream(buffer, CompressionLevel.Optimal, leaveOpen: true))
await using (var writer = new StreamWriter(gzip, new UTF8Encoding(false)))
{
await writer.WriteLineAsync("transaction_id,account_id,booking_date,amount,currency,type,channel,merchant_category_code,status,created_at");
await using var cmd = db.CreateCommand(
"""
SELECT transaction_id, account_id, booking_date, amount, currency, type, channel,
merchant_category_code, status, created_at
FROM transactions WHERE booking_date = $1 ORDER BY transaction_id
""");
cmd.Parameters.AddWithValue(day);
await using var r = await cmd.ExecuteReaderAsync(ct);
while (await r.ReadAsync(ct))
{
var amount = r.GetDecimal(3);
rows++;
total += amount;
await writer.WriteLineAsync(string.Join(',',
r.GetGuid(0), r.GetString(1), r.GetFieldValue<DateOnly>(2).ToString("yyyy-MM-dd", Inv),
amount.ToString("0.00", Inv), r.GetString(4), r.GetString(5), r.GetString(6),
r.IsDBNull(7) ? "" : r.GetString(7), r.GetString(8),
r.GetFieldValue<DateTime>(9).ToUniversalTime().ToString("O", Inv)));
}
}
buffer.Position = 0;
var sha = Convert.ToBase64String(System.Security.Cryptography.SHA256.HashData(buffer));
buffer.Position = 0;
await s3.PutObjectAsync(new PutObjectRequest
{
BucketName = bucket,
Key = key,
InputStream = buffer,
ContentType = "text/csv",
ChecksumAlgorithm = ChecksumAlgorithm.SHA256,
ChecksumSHA256 = sha, // S3 rejects the upload if the bytes it received don't match
ServerSideEncryptionMethod = ServerSideEncryptionMethod.AWSKMS
}, ct);
var manifest = new DayManifest(day, rows, total, key, sha);
await s3.PutObjectAsync(new PutObjectRequest
{
BucketName = bucket,
Key = $"manifests/transactions/{day:yyyy-MM-dd}.json",
ContentBody = JsonSerializer.Serialize(manifest),
ContentType = "application/json",
ServerSideEncryptionMethod = ServerSideEncryptionMethod.AWSKMS
}, ct);
return manifest;
}
private async Task VerifyUploadAsync(DayManifest m, CancellationToken ct)
{
var head = await s3.GetObjectMetadataAsync(new GetObjectMetadataRequest
{
BucketName = bucket, Key = m.ObjectKey, ChecksumMode = ChecksumMode.ENABLED
}, ct);
if (head.ChecksumSHA256 != m.Sha256)
throw new InvalidOperationException($"Checksum mismatch for {m.ObjectKey}; refusing to delete source rows");
}
private async Task DeleteDayAsync(DayManifest m, CancellationToken ct)
{
await using var conn = await db.OpenConnectionAsync(ct);
await using var tx = await conn.BeginTransactionAsync(System.Data.IsolationLevel.RepeatableRead, ct);
// Re-check inside the transaction: if late-arriving rows appeared, abort and re-export next run.
await using (var check = new NpgsqlCommand(
"SELECT count(*), coalesce(sum(amount), 0) FROM transactions WHERE booking_date = $1", conn, tx))
{
check.Parameters.AddWithValue(m.BookingDate);
await using var r = await check.ExecuteReaderAsync(ct);
await r.ReadAsync(ct);
var (count, sum) = (r.GetInt64(0), r.GetDecimal(1));
if (count != m.RowCount || sum != m.TotalAmount)
throw new InvalidOperationException($"{m.BookingDate}: source changed since export ({count}/{sum} vs {m.RowCount}/{m.TotalAmount})");
}
await using (var del = new NpgsqlCommand("DELETE FROM transactions WHERE booking_date = $1", conn, tx))
{
del.Parameters.AddWithValue(m.BookingDate);
var deleted = await del.ExecuteNonQueryAsync(ct);
if (deleted != m.RowCount) throw new InvalidOperationException("Delete count mismatch; rolling back");
}
await tx.CommitAsync(ct);
}
}Athena query and the program
using Amazon.Athena;
using Amazon.Athena.Model;
namespace Harbourline.DataPlatform.Archive;
public sealed class AthenaQuery(IAmazonAthena athena, string workGroup)
{
public async Task<(List<string[]> Rows, long BytesScanned)> RunAsync(string sql, CancellationToken ct)
{
var start = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest
{
QueryString = sql,
WorkGroup = workGroup // result location and encryption are enforced by the workgroup
}, ct);
QueryExecution exec;
var delay = TimeSpan.FromMilliseconds(250);
while (true)
{
exec = (await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest { QueryExecutionId = start.QueryExecutionId }, ct)).QueryExecution;
var state = exec.Status.State;
if (state == QueryExecutionState.SUCCEEDED) break;
if (state == QueryExecutionState.FAILED || state == QueryExecutionState.CANCELLED)
throw new InvalidOperationException($"Athena query {state}: {exec.Status.StateChangeReason}");
await Task.Delay(delay, ct);
delay = TimeSpan.FromMilliseconds(Math.Min(delay.TotalMilliseconds * 2, 2000));
}
var rows = new List<string[]>();
string? next = null;
do
{
var page = await athena.GetQueryResultsAsync(new GetQueryResultsRequest
{
QueryExecutionId = start.QueryExecutionId, NextToken = next
}, ct);
rows.AddRange(page.ResultSet.Rows.Select(r => r.Data.Select(d => d.VarCharValue ?? "").ToArray()));
next = page.NextToken;
} while (next is not null);
return (rows, exec.Statistics?.DataScannedInBytes ?? 0);
}
}using System.Globalization;
using Amazon;
using Amazon.Athena;
using Amazon.S3;
using Harbourline.DataPlatform.Archive;
using Npgsql;
var bucket = Environment.GetEnvironmentVariable("LAKE_BUCKET") ?? throw new InvalidOperationException("Set LAKE_BUCKET");
var cutoff = DateOnly.ParseExact(Environment.GetEnvironmentVariable("ARCHIVE_BEFORE") ?? "2026-09-20", "yyyy-MM-dd", CultureInfo.InvariantCulture);
// Local PostgreSQL from lesson 9. In AWS, credentials come from Secrets Manager or IAM auth.
await using var db = NpgsqlDataSource.Create("Host=localhost;Username=postgres;Password=LocalOnly_ChangeMe1;Database=postgres");
using var s3 = new AmazonS3Client(RegionEndpoint.USEast1); // AWS_PROFILE=learn-sandbox
using var athena = new AmazonAthenaClient(RegionEndpoint.USEast1);
await new ArchiveJob(db, s3, bucket).ArchiveBeforeAsync(cutoff, CancellationToken.None);
var (rows, scanned) = await new AthenaQuery(athena, "hl-m06").RunAsync(
"""
SELECT coalesce(merchant_category_code, 'none') AS mcc, count(*) AS n, sum(amount) AS total
FROM hl_lake.transactions
WHERE year = '2026' AND month = '09' AND day BETWEEN '01' AND '19'
GROUP BY 1 ORDER BY 3
""", CancellationToken.None);
foreach (var r in rows) Console.WriteLine(string.Join(" | ", r));
Console.WriteLine($"Data scanned: {scanned / 1024.0:0.0} KB");Optional: Redshift Serverless through the Data API
using Amazon.RedshiftDataAPIService;
using Amazon.RedshiftDataAPIService.Model;
namespace Harbourline.DataPlatform.Archive;
// Uses IAM credentials of the caller; no database password, driver or VPC route needed.
public sealed class RedshiftReport(IAmazonRedshiftDataAPIService api, string workgroup, string database = "dev")
{
public async Task<List<List<Field>>> RunAsync(string sql, CancellationToken ct)
{
var exec = await api.ExecuteStatementAsync(new ExecuteStatementRequest
{
WorkgroupName = workgroup, Database = database, Sql = sql
}, ct);
while (true)
{
var d = await api.DescribeStatementAsync(new DescribeStatementRequest { Id = exec.Id }, ct);
if (d.Status == StatusString.FINISHED) break;
if (d.Status == StatusString.FAILED || d.Status == StatusString.ABORTED)
throw new InvalidOperationException($"Redshift statement {d.Status}: {d.Error}");
await Task.Delay(500, ct);
}
var result = await api.GetStatementResultAsync(new GetStatementResultRequest { Id = exec.Id }, ct);
return result.Records;
}
}
// Example SQL once an external schema points at the Glue catalogue:
// CREATE EXTERNAL SCHEMA lake FROM DATA CATALOG DATABASE 'hl_lake'
// IAM_ROLE 'arn:aws:iam::111122223333:role/hl-m06-redshift-lake-read';
// SELECT merchant_category_code, sum(amount) FROM lake.transactions
// WHERE year = '2026' AND month = '09' GROUP BY 1;Code explained line by line
ClosedDaysAsync(cutoff)โ only days strictly before the cutoff are archived. The cutoff is a business decision (for example "older than 13 months and after month-end close").- Key
transactions/year=YYYY/month=MM/day=DD/โฆโ Hive-style partitions matching Harbourline's convention. Athena prunes partitions fromWHERE year=โฆ AND month=โฆand reads only matching prefixes. GZipStreamover CSV โ compresses about 5โ10ร; Athena reads gzip natively. Parquet (Module 7) is better still: columnar, typed, and scans only the columns you query.amount.ToString("0.00", Inv)โ money as a fixed-format decimal string; never throughdouble.rows++andtotal += amountโ the control totals computed during export; written into the manifest.ChecksumAlgorithm = SHA256with a precomputedChecksumSHA256โ S3 verifies integrity on arrival and stores the checksum; a corrupted upload is rejected.- Manifest object โ evidence for auditors: what was archived, when, how many rows, what total, which checksum.
GetObjectMetadataAsyncwithChecksumMode.ENABLEDโ confirms the object exists with the expected checksum before any deletion.RepeatableReadtransaction with a re-count โ if a late-arriving transaction was booked to that day after export, totals differ and the job aborts instead of deleting un-archived data.deleted != m.RowCountโ throw โ the transaction rolls back. Deletion is all-or-nothing per day.WorkGroup = workGroupโ the workgroup enforces the encrypted result location and the per-query scan limit; the code cannot bypass them.- Polling with exponential backoff up to 2 s โ Athena queries are asynchronous.
exec.Statistics.DataScannedInBytesโ the number that drives cost. Log it per query.- Redshift Data API
ExecuteStatement/DescribeStatement/GetStatementResultโ asynchronous like Athena; authenticates with IAM, so a Lambda or ECS task needs no database credentials.
Security considerations
โ Learning shortcut โ not for production
The lab uses the AWS managed aws/s3 key, one account, a local postgres superuser, and force_destroy/AutoDeleteObjects so cleanup is easy. In production: a customer managed KMS key in the data-platform account with a key policy limited to the archive and analytics roles, a cross-account bucket policy restricting writers to the archive role with aws:PrincipalOrgID and aws:SecureTransport, Object Lock (compliance or governance mode) if records must be immutable, Lake Formation column permissions so analysts cannot see raw IBANs, and an archive database role with SELECT and DELETE on transactions only.
- Retention is a legal decision. UK GDPR's storage-limitation principle says personal data must not be kept longer than necessary; financial-services record-keeping rules require some records for set periods. The lifecycle values in this lesson are examples. Compliance and Legal set them; engineering implements and evidences them.
- Erasure requests reach the lake too. If a customer's data must be erased (where no legal retention obligation overrides), CSV/Parquet files must be rewritten without their rows. Table formats like Apache Iceberg (or S3 Tables) support row-level deletes and are worth adopting for this reason.
- Pseudonymise before analytics. Analysts rarely need names or IBANs. Tokenise or hash identifiers in curated tables; keep the raw zone locked down.
- Athena result files contain query output โ often personal data. Encrypt the result location, restrict who can read it, and expire results with a lifecycle rule (e.g. 7 days).
- Redshift: use IAM-based access (Data API, IAM Identity Center integration), encrypt with KMS, deploy in private subnets, and enable audit logging to CloudWatch or S3.
Banking use case
Harbourline's target state for transaction data:
| Tier | Store | Retention | Who uses it |
|---|---|---|---|
| Hot | Aurora PostgreSQL ledger | 13 months | Payments, mobile API |
| Read model | DynamoDB (TX# items, TTL) | 13 months | Mobile home screen |
| Raw lake | S3 Standard โ Glacier Instant Retrieval after 1 year | Per retention schedule (e.g. 7 years) | Data platform only |
| Curated lake | S3 Parquet/Iceberg, pseudonymised | Same | Fraud models, finance via Athena |
| Warehouse | Redshift Serverless, zero-ETL from Aurora for current data + Spectrum for history | Rolling 3 years materialised | Finance dashboards, regulatory reporting |
Decision rules the architecture board adopted:
- Ad hoc, infrequent questions โ Athena on the lake. No standing cost.
- Many concurrent dashboard users, complex joins, sub-second expectations โ Redshift Serverless with base capacity at the minimum and usage limits.
- Near-real-time reporting on operational data โ zero-ETL integration rather than a home-made CDC pipeline, if the source version supports it.
- Never run analytical queries on the production writer.
Common mistakes
- Archiving without reconciliation โ "the export looked fine" โ control totals, checksums, verify-then-delete in a transaction.
- Unpartitioned lake โ every query scans everything โ
year=/month=/day=partitions plus partition projection. - CSV forever โ Athena bills by bytes scanned โ convert to Parquet (Module 7); typically scans an order of magnitude less.
- Many tiny files โ thousands of 10 KB objects per day โ compact to files of roughly 128 MBโ1 GB.
- Leaving Redshift Serverless without limits โ a runaway dashboard refresh โ set base capacity minimum, max RPU, and usage limits with an action.
- Lifecycle "expire" without Compliance sign-off โ irreversible deletion of records that had to be kept โ retention schedule approval first; Object Lock where immutability is required.
- Using the production replica for finance queries โ replica lag and resource contention โ lake or warehouse.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Athena returns zero rows | Wrong LOCATION, partition values not matching the projection (e.g. month=9 vs 09) | Check projection.month.digits = 2 and the S3 keys |
HIVE_BAD_DATA on amount | A row has a value that doesn't fit decimal(18,2) or a stray comma | Quote or escape fields; validate before writing; prefer Parquet |
| Header line appears as a data row | skip.header.line.count missing | Add the table property |
Access Denied on query results | Workgroup result location bucket/KMS permissions | Grant s3:PutObject and kms:GenerateDataKey on the result prefix and key |
| Query cancelled: "Bytes scanned limit was exceeded" | Workgroup cutoff working | Add partition filters; convert to Parquet |
| Archive job aborts with "source changed since export" | Late-arriving transaction booked to the day | Expected safety behaviour; the next run re-exports the day |
S3 BadDigest / checksum error | Stream position not reset after hashing | Reset buffer.Position = 0 before upload |
Redshift Data API ValidationException about workgroup | Wrong workgroup name or Region | Check WorkgroupName and the client's Region |
Cost and cleanup
- S3: a few MB of gzip CSV โ well under USD 0.01 per month.
- Athena: each query scans a few hundred KB; at USD 5 per TB scanned that is a fraction of a cent. Check the pricing page for minimum billing per query.
- Glue Data Catalog: the first million objects stored are free.
- Redshift Serverless (optional): at the 4-RPU minimum, USD 1.50 per hour of active querying, billed per second with a 60-second minimum per query window; first-time users get USD 300 trial credit for 90 days. Free alternative: Athena only.
# Delete Athena workgroup (and its saved queries/history).
aws athena delete-work-group --work-group hl-m06 --recursive-delete-option --profile learn-sandbox --region us-east-1
# Drop the catalogue objects (run in Athena) โ or delete via Glue:
aws glue delete-table --database-name hl_lake --name transactions --profile learn-sandbox --region us-east-1
aws glue delete-database --name hl_lake --profile learn-sandbox --region us-east-1
# Empty and delete the bucket (versioned buckets need versions removed; the CDK/Terraform lab settings do this for you).
aws s3 rm s3://$BUCKET --recursive --profile learn-sandbox
aws s3api delete-bucket --bucket $BUCKET --profile learn-sandbox --region us-east-1
# Redshift Serverless, if created:
aws redshift-serverless delete-workgroup --workgroup-name hl-m06 --profile learn-sandbox --region us-east-1
aws redshift-serverless delete-namespace --namespace-name hl-m06 --profile learn-sandbox --region us-east-1
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--query "ResourceTagMappingList[].ResourceARN" --profile learn-sandbox --region us-east-1Exercises
- ๐ข Guided: Run the archive job for days before 20 September, then the Athena query. Record rows archived, remaining rows in PostgreSQL, and bytes scanned. Run the job again and explain why nothing happens.
- ๐ก Challenge: Insert a "late" transaction for an already-archived day directly into PostgreSQL, run the job, and design how the archive should handle it (append a
part-0001file? rewrite the day?). Implement your choice with an updated manifest. - ๐ด Live problem: Finance asks for "a Redshift cluster with all transactions since 2017, refreshed every 5 minutes, for 40 analysts". The data platform budget is fixed. Propose an architecture and a cost estimate, and say what you would push back on.
Assignment
Add docs/data-tiering.md to your portfolio: the tier table for your own (fictional) bank, the archive job with its reconciliation design, the Athena DDL with partition projection, a lifecycle policy marked "pending Compliance approval", and a one-paragraph explanation of how an erasure request is handled in each tier.
Solutions
With the sample data (2,000 rows per day from 1 September), 19 days are archived: 38,000 rows leave PostgreSQL and 22,000 remain. The Athena query scans roughly a few hundred KB of gzip.
On the second run ClosedDaysAsync finds no rows before the cutoff, so nothing is exported or deleted. The job is idempotent by construction: the source is the to-do list. If a run crashes after upload but before delete, the next run re-exports the same day and overwrites the same S3 key โ also safe.
Prefer append with a new part file and a new manifest version: day=05/part-0001.csv.gz holding only the late rows, and manifests/transactions/2026-09-05.v2.json listing both parts with cumulative totals. Change ExportDayAsync to write part-{n:D4} where n is the count of existing parts (list the prefix), and compute the manifest from the union.
Why: rewriting the whole day risks losing data if the rewrite fails mid-way and breaks any downstream consumer that already processed the original file. Appending keeps history auditable. With Iceberg tables you would simply INSERT into the table and let the table format manage files and snapshots.
- Clarify needs: which reports need 5-minute freshness (probably today's activity), and which only need history (most of them).
- Architecture: zero-ETL integration from Aurora PostgreSQL into Redshift Serverless for current data (minutes of latency without a pipeline); history stays in the S3 lake as Parquet/Iceberg, queried via Redshift Spectrum external tables; materialised views for the top dashboards.
- Cost controls: Serverless base capacity at a measured level (start at the minimum and test), max RPU cap, daily usage limit with alerting; workload management queues so ad hoc queries cannot starve dashboards.
- Estimate: e.g. 8 RPUs ร 10 busy hours ร 22 days ร USD 0.375 โ USD 660/month compute plus managed storage for the hot years โ compare with a provisioned RA3 reservation if usage is steady all day.
- Push back on: "all data since 2017 in the warehouse" (use the lake), "40 analysts with raw access" (curated, pseudonymised views; Lake Formation), and "every 5 minutes for everything".
Knowledge check
Answer: Athena bills by bytes scanned. Partition pruning skips whole prefixes; Parquet is columnar and compressed, so only needed columns are read. Both cut bytes scanned.
Answer: When many users run frequent, complex queries with joins and expect consistent low latency โ dashboards and regulatory reporting โ and the data model benefits from a warehouse (distribution, sort keys, materialised views).
Answer: So no data is ever deleted that is not provably in the archive. Checksums prove the bytes arrived; control totals prove the content; the re-check in a transaction catches late-arriving rows.
Answer: An Athena table feature that calculates partition values and locations from table properties instead of storing each partition in the Glue catalogue, so new partitions are queryable immediately and planning is faster for highly partitioned tables.
Answer: It does not consume read capacity; it uses point-in-time recovery data, which must be enabled.
Interview questions
- "Data lake or data warehouse?" โ Both, usually: the lake as the cheap, open, long-term store (S3, Parquet/Iceberg, Glue catalogue); the warehouse for curated, high-concurrency analytics on the subset that needs it. Choose by access pattern, concurrency and cost.
- "How would you shrink a 2 TB OLTP database?" โ Agree retention per tier with Compliance; archive closed periods to S3 with control totals and checksums; verify; delete in batches in transactions; tier with lifecycle rules; make history queryable with Athena.
- "What is zero-ETL?" โ Managed replication from supported operational databases (Aurora PostgreSQL, RDS for PostgreSQL/MySQL/Oracle, DynamoDB) into Redshift, removing custom pipelines for near-real-time analytics.
- "How do you control Athena cost?" โ Partitioning, Parquet, compaction, workgroups with per-query scan limits, CloudWatch metrics per workgroup, and result-location lifecycle rules.
Certification practice
A data engineer stores daily transaction files as uncompressed CSV in Amazon S3 under a single prefix. Analysts query one month at a time with Amazon Athena, and costs are rising. Which actions reduce cost the most? (Choose two.)
A. Convert the files to Apache Parquet. B. Organise objects into year/month/day prefixes and define them as partitions (or partition projection). C. Move the files to S3 Standard-IA. D. Increase the Athena workgroup's per-query data limit. E. Run the queries from Redshift Serverless instead.
Answer: A and B. Parquet and partition pruning reduce bytes scanned, which is what Athena bills. Storage class affects storage cost, not scan cost, and adds retrieval charges (C). Raising the limit increases risk (D). Redshift adds a different cost without fixing the data layout (E).
A company's Amazon RDS for PostgreSQL database stores 8 years of transactions, but applications use only the last year. Backups and restores are slow, and storage costs are high. Historical data must remain queryable for occasional audits. What is the most cost-effective solution?
A. Increase the DB instance class and storage IOPS. B. Create a read replica for audit queries. C. Export historical data to Amazon S3, query it with Amazon Athena when needed, and delete it from the database. D. Migrate the database to Amazon Aurora with Aurora Serverless v2.
Answer: C. S3 storage is far cheaper, Athena charges only when audits run, and the smaller database restores faster. A bigger instance or replica raises cost (A, B). Moving to Aurora keeps the data in an operational database (D).
Completion checklist
- I can explain OLTP vs OLAP and lake vs warehouse with banking examples
- I ran the archive job and saw rows leave PostgreSQL only after verification
- I queried the archive with Athena and noted the bytes scanned
- I can say when Redshift Serverless or zero-ETL is worth its cost
- I deleted the workgroup, catalogue objects, bucket and any Redshift resources
Key takeaways
- Keep operational databases small: hot data in RDS/Aurora/DynamoDB, history in an S3 lake with explicit, Compliance-approved retention.
- Archive provably: control totals, checksums, verify-then-delete in a transaction, manifests as evidence.
- Athena is the zero-standing-cost way to query the lake; partitions and Parquet control the bill; workgroups enforce limits.
- Redshift (Serverless) earns its cost for high-concurrency, complex analytics; zero-ETL removes pipelines from supported sources.
- Retention, erasure and pseudonymisation are design inputs, not afterthoughts.
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.
Amazon DocumentDB
Decide when a document model fits a banking workload โ product catalogues, onboarding applications โ and when PostgreSQL JSONB or DynamoDB is the better answer; then connect to Amazon DocumentDB from .NET 10 with TLS, schema validation and the MongoDB-compatibility caveats in mind.
Performance optimisation
Diagnose and fix slow databases on RDS and Aurora: read DB load and wait events in CloudWatch Database Insights (the successor to Performance Insights), find top SQL with pg_stat_statements and Query Store, read execution plans, design indexes, and tame parameter sniffing on SQL Server and PostgreSQL.