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#.
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 withUNLOAD, 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
- Glue Data Catalog and crawlers — databases, tables and partition projection
- File formats — why Parquet scans less
- S3 data lake design — the buckets and the curated KMS key
- Tools: AWS CLI v2 (
learn-sandbox), .NET 10 SDK, CDK CLI or Terraform
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)
| Item | Value |
|---|---|
| SQL queries (on-demand) | USD 5 per TB scanned, rounded up to the nearest MB, 10 MB minimum per query |
| Failed queries, DDL statements | Commonly not charged ⚠ VERIFY on the pricing page; cancelled queries are charged for data scanned before cancellation |
| Capacity reservations | Dedicated capacity billed per DPU-hour (USD 0.30 per DPU-hour in the pricing example) for predictable, heavy workloads |
| S3 | Normal S3 request and storage charges for the data read and for results written |
| Athena-managed query results | No 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
| Statement | What it does | Limits and notes |
|---|---|---|
SELECT | Query; result to the results location | Results 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 table | Up to 100 partitions per CTAS; continue with INSERT INTO for more |
INSERT INTO | Appends query output to an existing table | Also 100 partitions per statement; appends, so re-running duplicates data |
UNLOAD | Writes query output to S3 in a chosen format, without creating a table | Good for extracts (finance CSV, partner files) |
CREATE VIEW | Saved query | No data stored; views hide complexity and columns |
MSCK REPAIR TABLE / ALTER TABLE ADD PARTITION | Register partitions | Prefer projection or explicit registration (previous lesson) |
PREPARE / EXECUTE ... USING, or ExecutionParameters in the API | Parameterised queries | Avoids 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).
| Setting | Why 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 managed | Results 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 alerts | CloudWatch alarms on scanned bytes per hour/day; notify through SNS (they do not cancel queries) |
| Publish CloudWatch metrics | Per-workgroup metrics for dashboards and alarms |
| Engine version | Automatic or pinned |
| Query result reuse | Reuse a recent identical query's result instead of scanning again (not supported with managed results) |
| Tags + IAM conditions | Restrict who can use which workgroup (athena:StartQueryExecution on the workgroup ARN) |
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Synapse serverless SQL pool | Amazon Athena | Both bill per data processed; Athena per TB scanned with a 10 MB minimum |
OPENROWSET / external tables in Synapse | Glue catalogue tables queried by Athena | The catalogue is shared with Glue, EMR, Spectrum |
CREATE EXTERNAL TABLE AS SELECT (CETAS) | Athena CTAS / UNLOAD | CTAS registers a table; UNLOAD just writes files |
| Synapse cost control ("TB processed per day/week/month" limits) | Workgroup per-query limit + workgroup alerts | Per-query limit cancels; workgroup-wide limits alert |
SQL Server parameterised sp_executesql | Athena ExecutionParameters / PREPARE ... EXECUTE USING | Positional ? parameters |
| Power BI DirectQuery to Synapse | Quick 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
- Console → Athena → Administration → Workgroups → Create workgroup: name
hl-m07-analysts; engine Athena SQL, upgrade Automatic; Query result configuration: "Customer managed" locations3://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. - Open the workgroup → Execution controls → Edit controls → Data scanned limit 100 MB.
- Workgroup data usage alerts → Add alert: threshold 1 GB per day, SNS topic
hl-m07-finops-alerts. - Query editor: choose workgroup
hl-m07-analysts(top right), data sourceAwsDataCatalog, databasehl_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. - 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 SumInfrastructure as Code
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)
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}/'
);-- 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;-- 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
<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>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("'", "''") + "'";
}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-25Code explained line by line
SQL
OpenCSVSerdewith every column asstring— 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.- 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 (
LIMITdoes not reduce bytes scanned for most formats, because Athena may still read whole files before applying the limit). - 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. CREATE TABLE ... WITH (format, write_compression, external_location, partitioned_by)— CTAS options. Partition columns must be the last columns in theSELECT.partitioned_by = ARRAY['month_key']— a few partitions (one per month); a CTAS may create at most 100 partitions per statement.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 andMERGE.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#
record QueryOutcomewithEstimatedCostUsd— 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).AthenaQueryRunner(IAmazonAthena athena, ...)— a primary constructor (C# 12) with an interface dependency, so tests can use a fake client.WorkGroup = workGroupwith noResultConfiguration— the workgroup enforces location, encryption and limits; the client cannot override them.ExecutionParameters = parameters.Select(Quote)— Athena substitutes positional?placeholders with these values. Strings must be passed as SQL literals (quoted), whichQuotedoes, doubling any embedded quotes.- 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. WaitAsyncwith exponential back-off — Athena is asynchronous;GetQueryExecutionis rate-limited per account, so do not poll in a tight loop.throw new InvalidOperationException(... StateChangeReason)— failures include the reason, such asQuery exhausted resources,HIVE_BAD_DATAor the data-limit cancellation.- The pagination loop with
NextToken—GetQueryResultsreturns at most 1,000 rows per call. For large results, read the CSV in S3 directly or useUNLOAD. Skip(first ? 1 : 0)— forSELECTqueries the first row of the first page contains the column headers.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,StopQueryExecutiononly onarn: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
StartQueryExecutionincluding 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:
| Workgroup | Users | Per-query limit | Results | Notes |
|---|---|---|---|---|
hl-analysts | Analysts (federated SSO role) | 50 GB | Athena managed (24 h) | Lake Formation filters hide tokenised columns and other regions |
hl-pipeline | Step Functions/Glue roles | 500 GB | Results bucket, SSE-KMS, 3-day expiry | CTAS/INSERT INTO/UNLOAD for gold |
hl-finance-bi | Quick Sight service role | 20 GB | Athena managed | Dashboards 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 *withLIMIT 10to "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_datevsyear/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 IcebergMERGE. - 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
| Symptom | Likely cause | Fix |
|---|---|---|
Query exhausted resources at this scale factor | Large ORDER BY, DISTINCT or join on skewed data | Filter earlier, aggregate before joining, avoid global ORDER BY on huge sets |
| Query cancelled: data limit exceeded | Per-query limit reached | Add partition filters or select fewer columns; raise the limit only with approval |
| Zero rows from a partitioned table | Partition not registered, or projection template mismatch | Check projection properties or register partitions |
HIVE_BAD_DATA: Error parsing field value | Data does not match the declared type (CSV) | Declare raw columns as string and cast in queries; fix the data |
HIVE_PARTITION_SCHEMA_MISMATCH | Partition metadata differs from table | Recreate partitions or use projection |
AccessDeniedException on results location | Role cannot write results or use the KMS key | Grant s3:PutObject on the results prefix and kms:GenerateDataKey, or use managed results |
HIVE_PATH_ALREADY_EXISTS on CTAS/UNLOAD | Target location not empty | Use a new location (include a run ID) or empty it first |
INVALID_FUNCTION_ARGUMENT on parameters | Unquoted string parameter | Pass strings as quoted literals ('ACC-...') |
Throttling: TooManyRequestsException | Concurrent query or API rate quota | Back 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
mediumdataset. - 🟡 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 workgrouphl-m07-reportingwhose IAM policy allows querying only that view's database. Show that a query againsthl_m07_curated.transactionsfrom 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
On small, all four queries are billed at the 10 MB minimum, but Data scanned still differs: A reads the whole CSV (about 215 KB), B a few tens of KB across all daily Parquet files, C a few KB from one folder, D every column of every file. On medium, A scans the whole CSV (about 35 MB), B several MB, C well under 1 MB, and engine time follows the same order. Why: the bill is driven by bytes scanned; partition and column pruning compound.
CREATE VIEW hl_m07_gold.v_card_spend_by_mcc AS
SELECT year, month, merchant_category_code, COUNT(*) AS txn_count, -SUM(amount) AS spend
FROM hl_m07_curated.transactions
WHERE type = 'card_payment' AND status = 'booked'
GROUP BY year, month, merchant_category_code;IAM for the reporting role: athena:StartQueryExecution/GetQueryExecution/GetQueryResults on the hl-m07-reporting workgroup only; glue:GetDatabase, glue:GetTable, glue:GetPartitions on database/hl_m07_gold and table/hl_m07_gold/* and on the underlying curated table (Athena views need catalogue access to the base table); S3 and KMS read on curated data. That last requirement is the weakness: with plain IAM, anyone who can query the view can also query the base table. The real control is Lake Formation, which can grant SELECT on the view's columns without granting the base table, or column/row filters on the base table. Note this in your answer; it is exactly why the governance lesson exists.
Find: ListQueryExecutions + BatchGetQueryExecution for the workgroup since midnight, sorted by DataScannedInBytes; group by query text hash. Typical finding: a dashboard or notebook re-running the same 40 GB query every minute (each under the per-query limit), or a scheduled job pointed at the analysts' workgroup. CloudTrail StartQueryExecution events give the principal. Stop it: disable or fix the offending schedule/dashboard, or deny that principal temporarily, rather than disabling the workgroup for everyone. Fix: add partition filters (most likely missing), materialise the result with CTAS into gold and point the dashboard there, enable query result reuse for repeated identical queries, move the workload to its own workgroup with its own limit and alert, and add an hourly alert threshold so the next spike is caught in an hour, not at 11:00.
Knowledge check
Answer: The 10 MB minimum: 10 MB at USD 5 per TB, about USD 0.00005.
Answer: It cancels the query. Workgroup-wide data usage alerts only notify (via CloudWatch and SNS); they do not cancel.
Answer: Athena may read entire files (and all columns) before applying the limit; bytes scanned, not rows returned, drive cost. Select columns and filter on partitions.
Answer: CTAS writes files and creates a catalogue table over them; UNLOAD only writes files in the chosen format.
Answer: Use execution parameters (positional ? placeholders with values passed separately, strings quoted), validate inputs, and restrict the role to a workgroup and the data it needs.
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
Analysts occasionally run queries that scan tens of terabytes by mistake. The company wants such queries stopped automatically while normal queries continue, with the least operational effort. What should a data engineer do?
A. Create an Athena workgroup for analysts with a per-query data usage control and enforce workgroup settings.
B. Create a CloudWatch alarm on the workgroup's total processed bytes that emails the team.
C. Move the data to Amazon Redshift.
D. Ask analysts to add LIMIT to every query.
Answer: A. The per-query control cancels any query exceeding the limit. B only notifies after the fact. C is a large change with its own costs. D does not reduce bytes scanned.
A security review finds Athena query results with personal data stored unencrypted in a bucket chosen by each user. Which change BEST addresses this?
A. Tell users to choose an encrypted bucket. B. Configure the workgroup with an encrypted result location (or Athena-managed results) and enable "override client-side settings". C. Enable S3 versioning on every bucket. D. Delete the results bucket weekly.
Answer: B. Enforced workgroup configuration sets the location and encryption for every query. A is not enforceable. C keeps more copies. D leaves data unencrypted for days.
A dashboard runs the same complex Athena aggregation over 2 TB of partitioned Parquet every 15 minutes during business hours, but the underlying data changes only once a night. What improves performance and cost the MOST?
A. Increase the per-query data limit. B. Run a nightly CTAS that materialises the aggregate into a small gold table and point the dashboard at it. C. Convert the data to CSV. D. Use a larger Athena instance type.
Answer: B. The heavy aggregation runs once per night; the dashboard reads a tiny table. A does nothing for speed. C increases scanned bytes. D: Athena has no instance types (capacity reservations exist but do not remove repeated scanning).
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 INTOandUNLOADmake Athena an ELT engine for gold tables and extracts; remember the 100-partition limit and thatINSERT INTOappends. - 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.
Lightweight transforms with Lambda: C# and Python functions for small files
Validate Harbourline's partner SFTP payment batches with an S3-triggered Lambda function in C# (.NET 10) or Python, including trailer control totals, IBAN checks and quarantine, and learn exactly where Lambda's limits make Glue the better tool.
Amazon Redshift and Redshift Serverless
Decide when Harbourline Bank needs a warehouse rather than Athena, load curated Parquet into Redshift with COPY, choose distribution and sort keys, query the lake with Spectrum, and predict the bill.