Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

intermediateโฑ 2 h 10 min๐ŸŸก Low costDEA-C01SAA-C03

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

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 transactions competes with the 02:00 batch window.
  • A "quick" ad hoc query by the finance team (SUM by 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

StoreStrengthsLimits and costs (us-east-1, checked 2026-11-18)
RDS/Aurora (OLTP)Row-level ACID, low latencyExpensive per GB; big tables slow backups, restores, maintenance
S3 data lakeCheapest durable storage; any engine; lifecycle to archive tiers; Object Lock for immutabilityS3 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 S3Serverless SQL, no infrastructure, pay per queryUSD 5 per TB scanned; performance depends on file format and partitioning; not for sub-second dashboards with many users
Redshift ServerlessWarehouse performance without managing nodes; auto-pauseUSD 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 usePaid per node-hour even when idle (pause/resume available)
Zero-ETL integrationsNear-real-time replication from Aurora PostgreSQL, RDS for PostgreSQL/MySQL/Oracle and DynamoDB into Redshift without pipelinesSource-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 equivalentWhat's different
Azure Synapse dedicated SQL poolAmazon Redshift (provisioned RA3)Both MPP columnar; Redshift uses distribution and sort keys, Synapse uses distributions and columnstore
Synapse serverless SQL pool over ADLSAmazon Athena over S3Both pay per TB scanned and query files in place
Microsoft Fabric warehouse / lakehouseRedshift Serverless + S3 (Iceberg / S3 Tables) + Glue Data CatalogAWS composes separate services; Fabric bundles them
ADLS Gen2Amazon S3S3 has no hierarchical namespace; "folders" are key prefixes
Azure Data Factory copy activityAWS Glue jobs, DMS, zero-ETL integrationsZero-ETL removes the pipeline for supported sources
SQL Server table partitioning + archive DBExport to S3 + lifecycle rulesArchive is cheaper and queryable in place
Synapse Link for Azure SQL / Cosmos DBRedshift zero-ETL integrationsSame idea: operational data replicated to analytics automatically
SqlConnection to SynapseRedshift Data API (HTTPS, IAM) or JDBC/ODBC/Npgsql-compatible driversThe 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

MeasureKeeping 9 years in Aurora90 days in Aurora + history in S3
Restore timeHours (proportional to size)Minutes for the hot database
Storage cost for 1.7 TB of historyDatabase storage rates (RDS Multi-AZ gp3 is billed for both copies)About USD 40/month in S3 Standard; less in Glacier tiers
Ad hoc analyticsRuns on production or a replicaRuns on Athena/Redshift, isolated from customers
Schema changesRewrite billions of rowsHot table only; history keeps its own schema version
Retention enforcementDELETE jobs on huge tablesS3 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:

transactions.sql
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 postgres

Create 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-sandbox

The 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

athena-ddl.sql
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 workgroup hl-m06 with 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-1

Infrastructure as Code

LakeStack.cs
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

Harbourline.DataPlatform.Archive.csproj
<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>
ArchiveJob.cs
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

AthenaQuery.cs
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);
    }
}
Program.cs
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

RedshiftReport.cs
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

  1. 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").
  2. Key transactions/year=YYYY/month=MM/day=DD/โ€ฆ โ€” Hive-style partitions matching Harbourline's convention. Athena prunes partitions from WHERE year=โ€ฆ AND month=โ€ฆ and reads only matching prefixes.
  3. GZipStream over CSV โ€” compresses about 5โ€“10ร—; Athena reads gzip natively. Parquet (Module 7) is better still: columnar, typed, and scans only the columns you query.
  4. amount.ToString("0.00", Inv) โ€” money as a fixed-format decimal string; never through double.
  5. rows++ and total += amount โ€” the control totals computed during export; written into the manifest.
  6. ChecksumAlgorithm = SHA256 with a precomputed ChecksumSHA256 โ€” S3 verifies integrity on arrival and stores the checksum; a corrupted upload is rejected.
  7. Manifest object โ€” evidence for auditors: what was archived, when, how many rows, what total, which checksum.
  8. GetObjectMetadataAsync with ChecksumMode.ENABLED โ€” confirms the object exists with the expected checksum before any deletion.
  9. RepeatableRead transaction 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.
  10. deleted != m.RowCount โ†’ throw โ†’ the transaction rolls back. Deletion is all-or-nothing per day.
  11. WorkGroup = workGroup โ€” the workgroup enforces the encrypted result location and the per-query scan limit; the code cannot bypass them.
  12. Polling with exponential backoff up to 2 s โ€” Athena queries are asynchronous.
  13. exec.Statistics.DataScannedInBytes โ€” the number that drives cost. Log it per query.
  14. 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:

TierStoreRetentionWho uses it
HotAurora PostgreSQL ledger13 monthsPayments, mobile API
Read modelDynamoDB (TX# items, TTL)13 monthsMobile home screen
Raw lakeS3 Standard โ†’ Glacier Instant Retrieval after 1 yearPer retention schedule (e.g. 7 years)Data platform only
Curated lakeS3 Parquet/Iceberg, pseudonymisedSameFraud models, finance via Athena
WarehouseRedshift Serverless, zero-ETL from Aurora for current data + Spectrum for historyRolling 3 years materialisedFinance dashboards, regulatory reporting

Decision rules the architecture board adopted:

  1. Ad hoc, infrequent questions โ†’ Athena on the lake. No standing cost.
  2. Many concurrent dashboard users, complex joins, sub-second expectations โ†’ Redshift Serverless with base capacity at the minimum and usage limits.
  3. Near-real-time reporting on operational data โ†’ zero-ETL integration rather than a home-made CDC pipeline, if the source version supports it.
  4. 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

SymptomLikely causeFix
Athena returns zero rowsWrong 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 amountA row has a value that doesn't fit decimal(18,2) or a stray commaQuote or escape fields; validate before writing; prefer Parquet
Header line appears as a data rowskip.header.line.count missingAdd the table property
Access Denied on query resultsWorkgroup result location bucket/KMS permissionsGrant s3:PutObject and kms:GenerateDataKey on the result prefix and key
Query cancelled: "Bytes scanned limit was exceeded"Workgroup cutoff workingAdd partition filters; convert to Parquet
Archive job aborts with "source changed since export"Late-arriving transaction booked to the dayExpected safety behaviour; the next run re-exports the day
S3 BadDigest / checksum errorStream position not reset after hashingReset buffer.Position = 0 before upload
Redshift Data API ValidationException about workgroupWrong workgroup name or RegionCheck 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-1

Exercises

  • ๐ŸŸข 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-0001 file? 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

Knowledge check

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

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.

On this page