Skip to Content
Cloud Engineering Academy
7. Data Engineering & ETL Pipelines

Amazon Athena: partition pruning, CTAS, workgroups with cost limits and encrypted results

Query Harbourline's lake with Amazon Athena: prune partitions, compare bytes scanned on CSV and Parquet, build gold tables with CTAS, INSERT INTO and UNLOAD, cap spend with workgroup data-usage controls, encrypt query results, and run parameterised queries from C#.

intermediate⏱ 2 h 🟡 Low costDEA-C01SAA-C03DVA-C03

Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.Athena 4.1.1.1) · Athena engine version 3 · Amazon.CDK.Lib 2.277.0 · Terraform AWS provider v6 · 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:

  • Explain how Athena executes SQL over S3, what it bills (bytes scanned, with a 10 MB minimum per query), and how to read a query's statistics.
  • Write queries that prune partitions and prune columns, and measure the difference between CSV and Parquet on the same data.
  • Build gold tables with CTAS, append with INSERT INTO, and export files with UNLOAD, within Athena's limits.
  • Configure a workgroup that enforces settings, caps bytes scanned per query, alerts on total usage, and encrypts query results with KMS (or uses Athena-managed results).
  • Run parameterised queries from C# with pagination and cost reporting, and protect against SQL injection.

Prerequisites

Why this matters in banking

A Harbourline analyst writes SELECT * FROM transactions against three years of CSV history to "have a quick look". Athena scans 2.4 TB, the query costs about USD 12, and the analyst runs it five times while tweaking a filter. Nobody notices until the monthly bill, and the FinOps team asks why "the analytics account" cost USD 4,000 more than last month.

At the same time, query results are files in S3. If they land in a bucket without encryption or lifecycle, the result of SELECT name, iban, balance ... sits there for years, readable by anyone with access to that bucket. Results are data, and in a bank they are often personal data.

Athena is one of the best tools a data engineer has: no servers, standard SQL, and seconds to answer most questions over the lake. This lesson is about using it so it stays cheap and safe.

The idea in plain language

Amazon Athena runs SQL directly over files in S3, using table definitions from the Glue Data Catalog. There is nothing to provision: you submit a query, Athena's engine (based on Trino) reads the files it needs, and writes the result as a CSV file (plus metadata) to a results location.

You pay for the data the query reads. So the art of Athena is reading less:

  • Partition pruning: filters on partition columns (year, month, day) make Athena skip whole folders.
  • Column pruning: with Parquet, selecting 3 columns reads roughly 3 columns' worth of bytes.
  • Predicate pushdown: Parquet statistics let Athena skip row groups that cannot match.
  • Compression: fewer bytes on disk means fewer bytes scanned.

Analogy: Athena is a research librarian who charges by the page read. Ask "everything about everyone" and you pay for the whole archive. Ask for "the amount and currency columns for 16 November" from a well-indexed archive (partitioned Parquet) and the librarian opens one drawer and reads two columns of one ledger.

What problem the AWS service solves

Athena gives SQL access to the lake without loading data into a warehouse. It suits ad hoc analysis, scheduled transformations (CTAS/ELT), data-quality checks, and reports over curated and gold data.

Pricing and statistics (us-east-1, checked 2026-11-18)

ItemValue
SQL queries (on-demand)USD 5 per TB scanned, rounded up to the nearest MB, 10 MB minimum per query
Failed queries, DDL statementsCommonly not charged ⚠ VERIFY on the pricing page; cancelled queries are charged for data scanned before cancellation
Capacity reservationsDedicated capacity billed per DPU-hour (USD 0.30 per DPU-hour in the pricing example) for predictable, heavy workloads
S3Normal S3 request and storage charges for the data read and for results written
Athena-managed query resultsNo additional cost; kept 24 hours, then deleted automatically

Every query records DataScannedInBytes, EngineExecutionTimeInMillis, QueryQueueTimeInMillis and more. The console shows Data scanned under the editor. Make reading it a habit.

Engine version 3

Athena engine version 3 (Trino-based) is current. It brought Iceberg time-travel syntax (FOR TIMESTAMP AS OF), MERGE, better performance and many Trino functions. Set workgroups to automatic engine upgrades unless you have a reason to pin.

Statements you will use

StatementWhat it doesLimits and notes
SELECTQuery; result to the results locationResults above the console download limit: use UNLOAD or the API
CREATE TABLE AS SELECT (CTAS)Writes query output as files (Parquet, ORC, Avro, JSON, CSV, text) and registers a tableUp to 100 partitions per CTAS; continue with INSERT INTO for more
INSERT INTOAppends query output to an existing tableAlso 100 partitions per statement; appends, so re-running duplicates data
UNLOADWrites query output to S3 in a chosen format, without creating a tableGood for extracts (finance CSV, partner files)
CREATE VIEWSaved queryNo data stored; views hide complexity and columns
MSCK REPAIR TABLE / ALTER TABLE ADD PARTITIONRegister partitionsPrefer projection or explicit registration (previous lesson)
PREPARE / EXECUTE ... USING, or ExecutionParameters in the APIParameterised queriesAvoids SQL injection in applications

Workgroups

A workgroup is a named container for queries with its own settings, permissions, metrics and limits. Use separate workgroups per team or application (hl-m07-analysts, hl-m07-pipeline).

SettingWhy it matters
Override client-side settings (EnforceWorkGroupConfiguration)Users cannot point results at another bucket or turn off encryption
Query result location + encryption (SSE-S3, SSE-KMS, CSE-KMS) or Athena managedResults are protected; managed results are encrypted (AWS owned key by default, CMK optional) and deleted after 24 hours
Per-query data usage control (BytesScannedCutoffPerQuery)Cancels any query that scans more than the limit (minimum 10 MB)
Workgroup-wide data usage alertsCloudWatch alarms on scanned bytes per hour/day; notify through SNS (they do not cancel queries)
Publish CloudWatch metricsPer-workgroup metrics for dashboards and alarms
Engine versionAutomatic or pinned
Query result reuseReuse a recent identical query's result instead of scanning again (not supported with managed results)
Tags + IAM conditionsRestrict who can use which workgroup (athena:StartQueryExecution on the workgroup ARN)

Microsoft / Azure comparison

You know (Microsoft/Azure)AWS equivalentWhat's different
Synapse serverless SQL poolAmazon AthenaBoth bill per data processed; Athena per TB scanned with a 10 MB minimum
OPENROWSET / external tables in SynapseGlue catalogue tables queried by AthenaThe catalogue is shared with Glue, EMR, Spectrum
CREATE EXTERNAL TABLE AS SELECT (CETAS)Athena CTAS / UNLOADCTAS registers a table; UNLOAD just writes files
Synapse cost control ("TB processed per day/week/month" limits)Workgroup per-query limit + workgroup alertsPer-query limit cancels; workgroup-wide limits alert
SQL Server parameterised sp_executesqlAthena ExecutionParameters / PREPARE ... EXECUTE USINGPositional ? parameters
Power BI DirectQuery to SynapseQuick Sight on Athena (direct query or SPICE)Covered in the reporting lesson

Architecture

Security-boundary view: the analyst needs athena:*Query* on one workgroup, read access to the catalogue tables, read access (directly or via Lake Formation) to curated and gold data, kms:Decrypt on the curated key, and write access to the results location (or nothing, with managed results). The analyst never needs raw-zone access.

Step-by-step implementation

Create the workgroup

Deploy hl-m07-analysts with the IaC below: enforced configuration, results in hl-m07-athena-results-<account-id> encrypted with the curated CMK, a 100 MB per-query scan limit, CloudWatch metrics on, engine version automatic.

Land the same data as CSV and Parquet

Upload the clean CSV to raw and the converted Parquet (from the file-formats lesson, or the generator's --format parquet) to curated. For a more visible difference, use the medium dataset (about 100,000 transactions).

Create tables and compare bytes scanned

Run 01-tables.sql, then the comparison queries in 02-compare.sql. Record Data scanned for each. Lab 7.3 (Athena: CSV vs Parquet) turns this into a measured lab report.

Build a gold table with CTAS, then append

Run 03-gold-ctas.sql to create hl_m07_gold.daily_summary partitioned by month, then INSERT INTO for a new day. Then UNLOAD a finance extract.

Hit the cost guardrail on purpose

Lower the per-query limit to 10 MB and run SELECT * on the medium CSV table: the query is cancelled with a data-limit error. Restore the limit.

Query from C#

Run the C# tool with a parameterised query for one account and day; it prints rows, bytes scanned and the estimated cost.

AWS Console instructions

  1. Console → Athena → Administration → Workgroups → Create workgroup: name hl-m07-analysts; engine Athena SQL, upgrade Automatic; Query result configuration: "Customer managed" location s3://hl-m07-athena-results-<account-id>/ with Encrypt query results → SSE-KMS → alias/hl-m07-curated (or choose Athena managed to skip the bucket); Override client-side settings on; Publish query metrics to CloudWatch on. Add tags.
  2. Open the workgroup → Execution controls → Edit controls → Data scanned limit 100 MB.
  3. Workgroup data usage alerts → Add alert: threshold 1 GB per day, SNS topic hl-m07-finops-alerts.
  4. Query editor: choose workgroup hl-m07-analysts (top right), data source AwsDataCatalog, database hl_m07_curated. Run a query; read Data scanned, Run time and Queue time below the editor. Recent queries keeps history; Saved queries stores named queries per workgroup.
  5. After a query, Query stats shows the stages and rows processed per stage; useful to confirm pruning.

AWS CLI

export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)
WG=hl-m07-analysts

# Create the workgroup (if not using IaC)
aws athena create-work-group --name "$WG" \
  --configuration "ResultConfiguration={OutputLocation=s3://hl-m07-athena-results-$ACCOUNT/,EncryptionConfiguration={EncryptionOption=SSE_KMS,KmsKey=arn:aws:kms:us-east-1:$ACCOUNT:alias/hl-m07-curated}},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=104857600,EngineVersion={SelectedEngineVersion=AUTO}" \
  --tags Key=Project,Value=cea-lab Key=Lab,Value=m07-transaction-pipeline Key=Environment,Value=learn Key=Owner,Value=your-name

# Run a query and wait for it
QID=$(aws athena start-query-execution --work-group "$WG" \
  --query-execution-context Database=hl_m07_curated \
  --query-string "SELECT currency, COUNT(*) n, SUM(amount) net FROM transactions WHERE year='2026' AND month='11' AND day='16' GROUP BY currency" \
  --query QueryExecutionId --output text)
aws athena get-query-execution --query-execution-id "$QID" \
  --query 'QueryExecution.{State:Status.State,ScannedBytes:Statistics.DataScannedInBytes,EngineMs:Statistics.EngineExecutionTimeInMillis}'
aws athena get-query-results --query-execution-id "$QID" --query 'ResultSet.Rows[].Data[].VarCharValue'

# A parameterised query (positional ? parameters, string literals quoted)
aws athena start-query-execution --work-group "$WG" \
  --query-execution-context Database=hl_m07_curated \
  --query-string "SELECT transaction_id, amount, status FROM transactions WHERE year=? AND month=? AND day=? AND account_id=?" \
  --execution-parameters "'2026'" "'11'" "'16'" "'ACC-1000000001'"

# Lower the guardrail to 10 MB to see a cancellation, then restore it
aws athena update-work-group --work-group "$WG" --configuration-updates BytesScannedCutoffPerQuery=10485760
aws athena update-work-group --work-group "$WG" --configuration-updates BytesScannedCutoffPerQuery=104857600

# Usage in the last day (requires CloudWatch metrics on the workgroup)
aws cloudwatch get-metric-statistics --namespace AWS/Athena --metric-name ProcessedBytes \
  --dimensions Name=WorkGroup,Value="$WG" \
  --start-time "$(date -u -d '-1 day' +%FT%TZ)" --end-time "$(date -u +%FT%TZ)" \
  --period 86400 --statistics Sum

Infrastructure as Code

infra/AthenaStack.cs
using Amazon.CDK;
using Amazon.CDK.AWS.Athena;
using Amazon.CDK.AWS.CloudWatch;
using Amazon.CDK.AWS.CloudWatch.Actions;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.SNS;
using Constructs;

namespace Harbourline.DataLake.Infra;

public sealed class AthenaStack : Stack
{
    public AthenaStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
    {
        var account = Aws.ACCOUNT_ID;
        var curatedKey = Key.FromLookup(this, "CuratedKey", new KeyLookupOptions { AliasName = "alias/hl-m07-curated" });

        var workGroup = new CfnWorkGroup(this, "Analysts", new CfnWorkGroupProps
        {
            Name = "hl-m07-analysts",
            Description = "Harbourline analysts: enforced results encryption and a per-query scan cap",
            State = "ENABLED",
            RecursiveDeleteOption = true,   // learning only: lets cdk destroy remove saved queries
            WorkGroupConfiguration = new CfnWorkGroup.WorkGroupConfigurationProperty
            {
                EnforceWorkGroupConfiguration = true,
                PublishCloudWatchMetricsEnabled = true,
                BytesScannedCutoffPerQuery = 100L * 1024 * 1024,     // 100 MB; queries above are cancelled
                EngineVersion = new CfnWorkGroup.EngineVersionProperty { SelectedEngineVersion = "AUTO" },
                ResultConfiguration = new CfnWorkGroup.ResultConfigurationProperty
                {
                    OutputLocation = $"s3://hl-m07-athena-results-{account}/",
                    EncryptionConfiguration = new CfnWorkGroup.EncryptionConfigurationProperty
                    {
                        EncryptionOption = "SSE_KMS",
                        KmsKey = curatedKey.KeyArn,
                    },
                },
            },
        });

        // Workgroup-wide guardrail: alert (not cancel) when more than 1 GB is scanned in a day.
        var topic = new Topic(this, "FinOpsAlerts", new TopicProps
        {
            TopicName = "hl-m07-finops-alerts",
            EnforceSSL = true,
        });
        var scanned = new Metric(new MetricProps
        {
            Namespace = "AWS/Athena",
            MetricName = "ProcessedBytes",
            DimensionsMap = new Dictionary<string, string> { ["WorkGroup"] = workGroup.Name },
            Statistic = "Sum",
            Period = Duration.Days(1),
        });
        var alarm = new Alarm(this, "DailyScanAlarm", new AlarmProps
        {
            AlarmName = "hl-m07-analysts-daily-scan",
            Metric = scanned,
            Threshold = 1L * 1024 * 1024 * 1024,
            EvaluationPeriods = 1,
            ComparisonOperator = ComparisonOperator.GREATER_THAN_THRESHOLD,
            TreatMissingData = TreatMissingData.NOT_BREACHING,
        });
        alarm.AddAlarmAction(new SnsAction(topic));

        Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
        Amazon.CDK.Tags.Of(this).Add("Lab", "m07-transaction-pipeline");
        Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
        Amazon.CDK.Tags.Of(this).Add("Owner", "your-name");
    }
}

CloudWatch alarm periods longer than one day are not allowed; a daily sum is the practical maximum here. cdk-nag will ask for an SNS topic key (AwsSolutions-SNS2) in production.

Complete code

SQL (workgroup hl-m07-analysts; replace ACCOUNT_ID)

01-tables.sql
CREATE DATABASE IF NOT EXISTS hl_m07_raw;
CREATE DATABASE IF NOT EXISTS hl_m07_curated;
CREATE DATABASE IF NOT EXISTS hl_m07_gold;

-- Raw CSV (quoted fields, header row). Same data as curated, for the comparison.
CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_raw.transactions_csv (
  transaction_id string, account_id string, booking_date string, value_date string,
  amount string, currency string, type string, channel string, counterparty_name string,
  counterparty_iban string, merchant_category_code string, reference string,
  status string, idempotency_key string, created_at string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '"')
LOCATION 's3://hl-m07-raw-ACCOUNT_ID/transactions/source=core/ingest_date=2026-11-16/'
TBLPROPERTIES ('skip.header.line.count' = '1');

-- Curated Parquet with partition projection (see the Glue Data Catalog lesson for the full column list).
CREATE EXTERNAL TABLE IF NOT EXISTS hl_m07_curated.transactions (
  transaction_id string, account_id string, booking_date date, value_date date,
  amount decimal(18,2), currency string, type string, channel string,
  merchant_category_code string, reference string, status string, created_at timestamp)
PARTITIONED BY (year string, month string, day string)
STORED AS PARQUET
LOCATION 's3://hl-m07-curated-ACCOUNT_ID/transactions/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.year.type' = 'integer',  'projection.year.range' = '2025,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-m07-curated-ACCOUNT_ID/transactions/year=${year}/month=${month}/day=${day}/'
);
02-compare.sql
-- A: CSV, whole file read (no partitions, no columns to prune)
SELECT currency, COUNT(*) AS n, SUM(CAST(amount AS decimal(18,2))) AS net
FROM hl_m07_raw.transactions_csv
WHERE status = 'booked' AND booking_date = '2026-11-16'
GROUP BY currency;

-- B: Parquet, all partitions, 3 columns read
SELECT currency, COUNT(*) AS n, SUM(amount) AS net
FROM hl_m07_curated.transactions
WHERE status = 'booked' AND booking_date = DATE '2026-11-16'
GROUP BY currency;

-- C: Parquet, one partition, 3 columns read (partition + column pruning)
SELECT currency, COUNT(*) AS n, SUM(amount) AS net
FROM hl_m07_curated.transactions
WHERE year = '2026' AND month = '11' AND day = '16' AND status = 'booked'
GROUP BY currency;

-- D: the anti-pattern. SELECT * reads every column of every partition.
SELECT * FROM hl_m07_curated.transactions LIMIT 10;
03-gold-ctas.sql
-- CTAS: build the gold daily summary for October as partitioned Parquet.
CREATE TABLE hl_m07_gold.daily_summary
WITH (
  format = 'PARQUET',
  write_compression = 'ZSTD',
  external_location = 's3://hl-m07-gold-ACCOUNT_ID/daily_summary/',
  partitioned_by = ARRAY['month_key']
) AS
SELECT booking_date,
       currency,
       COUNT(*)                                         AS txn_count,
       SUM(amount)                                      AS net_amount,
       SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS credits,
       SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END) AS debits,
       year || '-' || month                             AS month_key    -- partition column must be last
FROM hl_m07_curated.transactions
WHERE year = '2026' AND month = '10' AND status = 'booked'
GROUP BY booking_date, currency, year, month;

-- INSERT INTO: append November. Re-running this duplicates rows, so the pipeline
-- deletes the target partition's files first or uses an Iceberg table with MERGE.
INSERT INTO hl_m07_gold.daily_summary
SELECT booking_date, currency, COUNT(*), SUM(amount),
       SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END),
       SUM(CASE WHEN amount < 0 THEN amount ELSE 0 END),
       year || '-' || month
FROM hl_m07_curated.transactions
WHERE year = '2026' AND month = '11' AND status = 'booked'
GROUP BY booking_date, currency, year, month;

-- UNLOAD: a CSV extract for finance (no table created). Target prefix must be empty.
UNLOAD (
  SELECT booking_date, currency, txn_count, net_amount
  FROM hl_m07_gold.daily_summary
  WHERE month_key = '2026-11'
  ORDER BY booking_date, currency
)
TO 's3://hl-m07-gold-ACCOUNT_ID/exports/daily_summary/month=2026-11/run=001/'
WITH (format = 'TEXTFILE', field_delimiter = ',', compression = 'NONE');

C#: parameterised queries with pagination and cost

AthenaQuery/AthenaQuery.csproj
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
    <RootNamespace>Harbourline.DataLake.AthenaQuery</RootNamespace>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="AWSSDK.Athena" Version="4.1.1.1" />
  </ItemGroup>
</Project>
AthenaQuery/AthenaQueryRunner.cs
using Amazon.Athena;
using Amazon.Athena.Model;

namespace Harbourline.DataLake.AthenaQuery;

public sealed record QueryOutcome(string QueryExecutionId, IReadOnlyList<string> Columns,
                                  IReadOnlyList<string?[]> Rows, long BytesScanned, long EngineMs)
{
    // USD 5 per TB, rounded up to the next MB, 10 MB minimum.
    public decimal EstimatedCostUsd
    {
        get
        {
            var mb = Math.Max(10, (long)Math.Ceiling(BytesScanned / 1_048_576.0));
            return mb / 1_048_576m * 5m;
        }
    }
}

public sealed class AthenaQueryRunner(IAmazonAthena athena, string workGroup, string database)
{
    public async Task<QueryOutcome> RunAsync(string sql, IReadOnlyList<string> parameters,
                                             int maxRows = 1_000, CancellationToken ct = default)
    {
        var start = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest
        {
            QueryString = sql,
            WorkGroup = workGroup,                       // results location + encryption + limits enforced here
            QueryExecutionContext = new QueryExecutionContext { Database = database },
            // Values are sent separately from the SQL text: no string concatenation, no injection.
            ExecutionParameters = parameters.Select(Quote).ToList(),
        }, ct);

        var execution = await WaitAsync(start.QueryExecutionId, ct);

        var columns = new List<string>();
        var rows = new List<string?[]>();
        string? next = null;
        var first = true;
        do
        {
            var page = await athena.GetQueryResultsAsync(new GetQueryResultsRequest
            {
                QueryExecutionId = start.QueryExecutionId,
                NextToken = next,
                MaxResults = 1000,
            }, ct);

            if (first)
            {
                columns.AddRange(page.ResultSet.ResultSetMetadata.ColumnInfo.Select(c => c.Name));
            }
            // For SELECT statements the first row of the first page repeats the column names.
            foreach (var row in page.ResultSet.Rows.Skip(first ? 1 : 0))
            {
                if (rows.Count >= maxRows) break;
                rows.Add(row.Data.Select(d => d.VarCharValue).ToArray());
            }
            first = false;
            next = page.NextToken;
        } while (next is not null && rows.Count < maxRows);

        return new QueryOutcome(start.QueryExecutionId, columns, rows,
            execution.Statistics?.DataScannedInBytes ?? 0, execution.Statistics?.EngineExecutionTimeInMillis ?? 0);
    }

    private async Task<QueryExecution> WaitAsync(string id, CancellationToken ct)
    {
        var delay = TimeSpan.FromMilliseconds(250);
        while (true)
        {
            var execution = (await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest { QueryExecutionId = id }, ct)).QueryExecution;
            var state = execution.Status.State;
            if (state == QueryExecutionState.SUCCEEDED) return execution;
            if (state == QueryExecutionState.FAILED || state == QueryExecutionState.CANCELLED)
                throw new InvalidOperationException($"Athena query {id} {state}: {execution.Status.StateChangeReason}");

            await Task.Delay(delay, ct);
            delay = TimeSpan.FromMilliseconds(Math.Min(delay.TotalMilliseconds * 2, 4_000));
        }
    }

    // Athena execution parameters are SQL literals: strings must be quoted and embedded quotes doubled.
    private static string Quote(string value) => "'" + value.Replace("'", "''") + "'";
}
AthenaQuery/Program.cs
using System.Globalization;
using Amazon.Athena;
using Harbourline.DataLake.AthenaQuery;

if (args.Length != 2)
{
    Console.Error.WriteLine("usage: AthenaQuery <account_id ACC-##########> <booking date yyyy-MM-dd>");
    return 2;
}

// Validate inputs before they go anywhere near SQL, even though parameters are used.
if (!System.Text.RegularExpressions.Regex.IsMatch(args[0], @"^ACC-\d{10}$") ||
    !DateOnly.TryParseExact(args[1], "yyyy-MM-dd", CultureInfo.InvariantCulture, DateTimeStyles.None, out var day))
{
    Console.Error.WriteLine("Invalid account id or date.");
    return 2;
}

using var athena = new AmazonAthenaClient(Amazon.RegionEndpoint.USEast1);
var runner = new AthenaQueryRunner(athena, workGroup: "hl-m07-analysts", database: "hl_m07_curated");

const string sql = """
    SELECT transaction_id, booking_date, amount, currency, type, status
    FROM transactions
    WHERE year = ? AND month = ? AND day = ? AND account_id = ?
    ORDER BY created_at
    """;

var outcome = await runner.RunAsync(sql,
    [day.ToString("yyyy", CultureInfo.InvariantCulture), day.ToString("MM", CultureInfo.InvariantCulture),
     day.ToString("dd", CultureInfo.InvariantCulture), args[0]]);

Console.WriteLine(string.Join(" | ", outcome.Columns));
foreach (var row in outcome.Rows)
    Console.WriteLine(string.Join(" | ", row.Select(v => v ?? "NULL")));

Console.WriteLine($"""

    query id      {outcome.QueryExecutionId}
    rows          {outcome.Rows.Count}
    data scanned  {outcome.BytesScanned:N0} bytes
    engine time   {outcome.EngineMs} ms
    est. cost     USD {outcome.EstimatedCostUsd:0.000000} (10 MB minimum applies)
    """);
return 0;

Run it:

aws sso login --profile learn-sandbox && export AWS_PROFILE=learn-sandbox
dotnet run --project AthenaQuery -- ACC-1000000001 2026-10-25

Code explained line by line

SQL

  1. OpenCSVSerde with every column as string — reads quoted CSV safely. CAST(amount AS decimal(18,2)) happens in the query, which is one reason CSV queries are slower: parsing and casting every row.
  2. The comparison queries A–D — same answer, different bytes scanned. A reads the whole CSV; B reads three columns of every Parquet file; C reads three columns of one partition; D reads everything (LIMIT does not reduce bytes scanned for most formats, because Athena may still read whole files before applying the limit).
  3. In query B the filter is on booking_date, which is a data column, not a partition column: Athena can still skip row groups using Parquet statistics, but it must open every file. Query C filters on the partition columns, so it opens only one folder.
  4. CREATE TABLE ... WITH (format, write_compression, external_location, partitioned_by) — CTAS options. Partition columns must be the last columns in the SELECT.
  5. partitioned_by = ARRAY['month_key'] — a few partitions (one per month); a CTAS may create at most 100 partitions per statement.
  6. INSERT INTO — appends. It is not idempotent on its own; pipelines delete the target partition's objects (and catalogue entry) before re-running, write to a run-specific location, or use Iceberg and MERGE.
  7. UNLOAD (...) TO ... WITH (format = 'TEXTFILE', field_delimiter = ',') — exports a CSV extract without creating a table; the target prefix must be empty, so include a run identifier.

C#

  1. record QueryOutcome with EstimatedCostUsd — makes cost visible in every tool you build: bytes rounded up to MB, minimum 10 MB, USD 5 per TB (1 TB = 1,048,576 MB).
  2. AthenaQueryRunner(IAmazonAthena athena, ...) — a primary constructor (C# 12) with an interface dependency, so tests can use a fake client.
  3. WorkGroup = workGroup with no ResultConfiguration — the workgroup enforces location, encryption and limits; the client cannot override them.
  4. ExecutionParameters = parameters.Select(Quote) — Athena substitutes positional ? placeholders with these values. Strings must be passed as SQL literals (quoted), which Quote does, doubling any embedded quotes.
  5. Input validation in Program.cs (regex for the account ID, strict date parsing) — defence in depth: parameters prevent injection, validation prevents nonsense queries and keeps scans small.
  6. WaitAsync with exponential back-off — Athena is asynchronous; GetQueryExecution is rate-limited per account, so do not poll in a tight loop.
  7. throw new InvalidOperationException(... StateChangeReason) — failures include the reason, such as Query exhausted resources, HIVE_BAD_DATA or the data-limit cancellation.
  8. The pagination loop with NextToken — GetQueryResults returns at most 1,000 rows per call. For large results, read the CSV in S3 directly or use UNLOAD.
  9. Skip(first ? 1 : 0) — for SELECT queries the first row of the first page contains the column headers.
  10. maxRows — an application should never pull unbounded results into memory.

Security considerations

⚠ Learning shortcut — not for production

You run these queries as an SSO administrator, the analyst workgroup reads curated data through plain IAM permissions, and query results stay for 7 days in a lab bucket. In production, analysts use a dedicated role restricted to their workgroup; data access is granted through Lake Formation (column and row filters, so masked columns and other regions' rows are invisible); results are Athena-managed (24 hours) or in a results bucket with a short lifecycle; and query history is reviewed through CloudTrail.

  • Results are data. Encrypt them (SSE-KMS with a CMK, or managed results), expire them quickly, and do not give the results bucket broader access than the source data.
  • Enforce workgroup settings so users cannot write results to a bucket of their choice or disable encryption.
  • Restrict workgroups with IAM: allow athena:StartQueryExecution, GetQueryExecution, GetQueryResults, StopQueryExecution only on arn:aws:athena:us-east-1:<account>:workgroup/hl-m07-analysts.
  • SQL injection applies to Athena exactly as to SQL Server: never concatenate user input into SQL; use execution parameters and validate inputs.
  • Federated queries (Lambda-based connectors to other databases) extend Athena's reach; review them like any data integration, because they can read operational systems.
  • CloudTrail records StartQueryExecution including the query text. Avoid literal personal data in query strings (for example searching by a customer's name); search by tokens or IDs.

Banking use case

Harbourline's analytics account has three workgroups:

WorkgroupUsersPer-query limitResultsNotes
hl-analystsAnalysts (federated SSO role)50 GBAthena managed (24 h)Lake Formation filters hide tokenised columns and other regions
hl-pipelineStep Functions/Glue roles500 GBResults bucket, SSE-KMS, 3-day expiryCTAS/INSERT INTO/UNLOAD for gold
hl-finance-biQuick Sight service role20 GBAthena managedDashboards on gold only

Daily alerts at 500 GB (analysts) and 2 TB (pipeline) go to FinOps and the data-platform on-call. A weekly report lists the top ten queries by bytes scanned; most savings come from fixing two or three of them (usually a missing partition filter or a SELECT *).

Common mistakes

  • SELECT * with LIMIT 10 to "peek" → still scans whole files → select columns; filter on partitions; use the table preview on a single partition.
  • Filtering on a data column instead of the partition column (booking_date vs year/month/day) → every partition opened → filter on partition columns or use Iceberg hidden partitioning.
  • Re-running INSERT INTO → duplicated gold rows → delete the partition first, use run-specific locations, or Iceberg MERGE.
  • No per-query limit → one mistake costs tens of dollars → set limits on every workgroup.
  • Unencrypted, never-expiring results → personal data accumulates → enforced encryption, lifecycle or managed results.
  • Thousands of tiny files → planning and opening overhead dominates → compact curated data.
  • String concatenation for parameters in apps → SQL injection → ExecutionParameters.

Troubleshooting

SymptomLikely causeFix
Query exhausted resources at this scale factorLarge ORDER BY, DISTINCT or join on skewed dataFilter earlier, aggregate before joining, avoid global ORDER BY on huge sets
Query cancelled: data limit exceededPer-query limit reachedAdd partition filters or select fewer columns; raise the limit only with approval
Zero rows from a partitioned tablePartition not registered, or projection template mismatchCheck projection properties or register partitions
HIVE_BAD_DATA: Error parsing field valueData does not match the declared type (CSV)Declare raw columns as string and cast in queries; fix the data
HIVE_PARTITION_SCHEMA_MISMATCHPartition metadata differs from tableRecreate partitions or use projection
AccessDeniedException on results locationRole cannot write results or use the KMS keyGrant s3:PutObject on the results prefix and kms:GenerateDataKey, or use managed results
HIVE_PATH_ALREADY_EXISTS on CTAS/UNLOADTarget location not emptyUse a new location (include a run ID) or empty it first
INVALID_FUNCTION_ARGUMENT on parametersUnquoted string parameterPass strings as quoted literals ('ACC-...')
Throttling: TooManyRequestsExceptionConcurrent query or API rate quotaBack off and retry; queue queries; request quota increases if sustained

Cost and cleanup

Expected cost: about 15 queries on the small sample, each billed at the 10 MB minimum: 15 × 10 MB × USD 5/TB ≈ USD 0.0007. On the medium dataset (about 35 MB of CSV), the CSV comparison query scans about 35 MB (≈ USD 0.00017) and the partitioned Parquet query is billed at the 10 MB minimum. S3 storage for results and gold files: well under USD 0.01. KMS requests for encrypted results: a few cents at most. SNS and the CloudWatch alarm: the first 10 alarms are free tier; otherwise USD 0.10 per alarm-month prorated. Total: under USD 0.10.

export AWS_PROFILE=learn-sandbox AWS_REGION=us-east-1
ACCOUNT=$(aws sts get-caller-identity --query Account --output text)

# Drop the tables created here (catalogue only; data is removed below)
for SQL in "DROP TABLE IF EXISTS hl_m07_gold.daily_summary" \
           "DROP TABLE IF EXISTS hl_m07_raw.transactions_csv"; do
  aws athena start-query-execution --work-group hl-m07-analysts --query-string "$SQL"
done
aws s3 rm "s3://hl-m07-gold-$ACCOUNT/daily_summary/" --recursive
aws s3 rm "s3://hl-m07-gold-$ACCOUNT/exports/" --recursive
aws s3 rm "s3://hl-m07-athena-results-$ACCOUNT/" --recursive

# Workgroup, alarm, topic (or: cdk destroy HlM07Athena / terraform destroy)
aws athena delete-work-group --work-group hl-m07-analysts --recursive-delete-option
aws cloudwatch delete-alarms --alarm-names hl-m07-analysts-daily-scan
aws sns delete-topic --topic-arn "arn:aws:sns:us-east-1:$ACCOUNT:hl-m07-finops-alerts"

aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
  --query "ResourceTagMappingList[].ResourceARN"

Remove the lake itself with the steps in S3 data lake design when you finish the module's lessons for the day.

Exercises

  • 🟢 Guided: Create the workgroup and tables, run queries A–D, and record bytes scanned and engine time for each in a table. Repeat on the medium dataset.
  • 🟡 Challenge: Create a view hl_m07_gold.v_card_spend_by_mcc (card payments only, grouped by month and MCC, with no account IDs) and a second workgroup hl-m07-reporting whose IAM policy allows querying only that view's database. Show that a query against hl_m07_curated.transactions from that workgroup is denied.
  • 🔴 Live problem: The FinOps alarm fires at 11:00: the analysts' workgroup has scanned 3.1 TB today, compared with about 200 GB normally. No single query hit the 50 GB per-query limit. Find the cause and stop the spending without blocking all analysts.

Assignment

Build an Athena cost report tool in C# using ListQueryExecutions and BatchGetQueryExecution for a workgroup: the top 20 queries in the last 24 hours by bytes scanned, with estimated cost, user (from CloudTrail if you want extra credit), and the first 100 characters of each query. Add a README explaining the per-query and workgroup limits you recommend for Harbourline and why.

Solutions

Knowledge check

Interview questions

  • "How do you control Athena costs?" — Parquet, partitioning (or projection/Iceberg), compaction, column selection; workgroups per team with per-query limits and daily alerts; materialise hot queries with CTAS; query result reuse; review top queries weekly; capacity reservations for predictable heavy use.
  • "Athena or Redshift?" — Athena for ad hoc and moderate, spiky workloads directly on the lake with no infrastructure; Redshift for heavy, concurrent, repeated BI with complex joins and strict latency.
  • "How do you secure query results?" — Enforced workgroup configuration, encryption with a CMK or Athena-managed results, short retention, and least-privilege access to the results location.

Certification practice

Completion checklist

  • I created a workgroup with enforced, encrypted results and a per-query scan limit
  • I compared bytes scanned for CSV, Parquet, and partition-pruned Parquet
  • I built a gold table with CTAS, appended with INSERT INTO and exported with UNLOAD
  • I ran a parameterised query from C# with pagination and a cost estimate
  • I dropped the tables, emptied results and gold prefixes, and deleted the workgroup, alarm and topic

Key takeaways

  • Athena bills by bytes scanned (USD 5 per TB, 10 MB minimum): partition filters, column selection, Parquet and compaction are cost controls.
  • CTAS, INSERT INTO and UNLOAD make Athena an ELT engine for gold tables and extracts; remember the 100-partition limit and that INSERT INTO appends.
  • Workgroups enforce result location, encryption and per-query limits; workgroup-wide alerts catch slow leaks.
  • Query results are data: encrypt, expire, and restrict them.
  • From C#, use execution parameters, back-off polling and pagination, and report bytes scanned with every query.

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