Cost and Usage Reports: billing data exports, Athena and showback
Export the most detailed AWS billing data with Data Exports (CUR 2.0), query it with Athena or DuckDB, and build a showback report that allocates direct and shared costs per cost centre.
Assumes: .NET 10 LTS · AWS SDK for .NET v4 (AWSSDK.Athena 4.x) · AWS CLI v2 · CDK v2 (Amazon.CDK.Lib 2.277.0) · Terraform AWS provider v6 · DuckDB · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Explain what a Cost and Usage Report contains, and why it is the source of truth behind Cost Explorer.
- Choose between CUR 2.0, the legacy CUR, FOCUS and cost optimization recommendation exports in AWS Data Exports.
- Create an export that delivers Parquet files to S3, and secure the bucket properly.
- Query billing data with Athena (and with DuckDB locally for free) to answer cost questions by account, service and tag.
- Build a showback report that allocates unattributed and shared costs fairly, and explain the rule you used.
- Run an Athena query from C# with a bytes-scanned limit to keep query cost under control.
Prerequisites
- FinOps foundations for cost metrics, budgets and showback versus chargeback.
- Tagging strategy and tag policies for the
CostCentertag and its activation. - Amazon Athena for tables, partitions and Parquet. If you have not done it, skim the first sections first.
- Tools: AWS CLI v2 with SSO profiles for the management account, .NET 10 SDK. For the free path: DuckDB (the command-line tool, or
pip install duckdb).
Why this matters in banking
Cost Explorer answers the first question. It does not answer the second. "Which service in which account for which cost centre, hour by hour, including the savings plan benefit and the tax?" needs line-item data. A finance team that needs to reconcile the AWS invoice to internal charges, or a platform team that wants to find the 50 resources behind 80 percent of the spend, works from a billing export. A bank also needs this data to be retained, access controlled and reproducible: when someone asks in two years why Q3 2026 cost what it did, the export is the evidence.
The risk on the other side is that billing data is sensitive and large. A detailed export with resource IDs is a map of your estate. And an unlimited Athena query scanning terabytes of it is a surprise bill in its own right. This lesson builds the pipeline with both in mind.
The idea in plain language
AWS records every billable event: an hour of an instance, a gigabyte stored, a million Lambda requests, a tax line. A Cost and Usage Report is that ledger, delivered as files, one row per line item per time unit, with dozens to hundreds of columns: who, what, when, how much, at what rate, with which tags and discounts.
Think of the AWS invoice as the credit card statement you get on paper, and the export as the underlying transaction file. The statement tells you the total. The transaction file tells you everything.
Analogy: Cost Explorer is the banking app's spending chart: easy and good enough most days. The export is the full transaction CSV you download for your accountant, with every merchant, every date and every reference, so they can slice it any way they like.
Data Exports and the report types
AWS now groups billing exports under Data Exports in the Billing and Cost Management console. Current export types, from the documentation:
| Export type | What it is | Use it for |
|---|---|---|
| Cost and Usage Report 2.0 (CUR 2.0) | The new and recommended way to get detailed cost and usage data | Almost all new work: line-item analysis, showback, FinOps tooling |
| Legacy CUR | The older Cost and Usage Reports, accessed with different API actions | Existing pipelines that have not migrated yet |
| FOCUS 1.2 / FOCUS 1.0 with AWS columns | Data in the FinOps Open Cost and Usage Specification, an open schema | Multi-cloud reporting using one common schema |
| Cost optimization recommendations | Output from Cost Optimization Hub | Tracking recommendations over time |
| Carbon emissions | Emissions data | Sustainability reporting |
| Cost and usage dashboard | An export plus QuickSight integration with a pre-built dashboard | A quick visual without building anything |
Data Exports also lets you write SQL to choose columns and filter rows (with the CLI or SDK), so you can drop sensitive columns or exclude certain linked accounts before the data ever lands in S3. That fits a least-privilege view of billing data.
AWS Usage Report is in maintenance (see the module overview). Do not build new work on it.
What is in a row
You do not need to learn all columns. These carry most analysis (names follow the CUR column naming convention; ⚠ VERIFY exact names in your own export, because CUR 2.0 and legacy CUR differ in nesting and naming):
| Column (approximate) | Meaning |
|---|---|
bill_billing_period_start_date | The billing month |
bill_payer_account_id, line_item_usage_account_id | Payer and the account that used the service |
line_item_product_code | The service, for example AmazonEC2 |
line_item_usage_type, line_item_operation | What exactly was used |
line_item_line_item_type | Usage, Tax, Fee, Credit, SavingsPlanCoveredUsage, DiscountedUsage and others: crucial for avoiding double counting |
line_item_usage_start_date | Start of the time unit |
line_item_unblended_cost, pricing_public_on_demand_cost | Cost at the line, and what On-Demand would have cost |
line_item_resource_id | The resource, when you enable resource IDs (bigger files) |
resource_tags (CUR 2.0 map) | Your activated cost allocation tags |
cost_category (CUR 2.0 map) | Values of your cost categories |
A key gotcha: the line item type matters. If you add up every row without filtering, some commitment-related rows can double count or look odd (for example, the Savings Plan's own fee row versus the usage it covers). Decide which line item types you include, and document it.
What problem the AWS service solves
| Need | How an export helps | Limit |
|---|---|---|
| Reconcile the invoice | Line items sum to the bill (with the right cost columns and types) | Rounding, tax, credits and timing make exact matches fiddly |
| Cost by tag, account, service, Region, hour | Group by any column | Tags only appear if activated, and only for usage after activation (⚠ VERIFY backfill) |
| Find top resources | Enable resource IDs and group by line_item_resource_id | Larger files; do this for a limited period or a subset |
| Allocate shared costs | Allocate by tag, usage or proportion using your own rules | AWS gives raw data; the rule is your policy |
| Keep a history | Files persist in S3 | S3 lifecycle and retention are yours to set |
| Feed other tools | Parquet or CSV to BI, Athena, DuckDB, Redshift | You operate the pipeline |
Export refresh is not instant: data typically updates a few times a day and finalises after the month closes (and can be restated). ⚠ VERIFY the cadence for your export type. Any report built from it should show "as of" and mark the current month as provisional.
Microsoft / Azure comparison
| You know (Microsoft/Azure) | AWS equivalent | What's different |
|---|---|---|
| Cost Management exports (scheduled CSV to storage) | Data Exports to S3 | Similar; AWS offers SQL column selection at export creation |
| Azure Cost Management Power BI connector | QuickSight, Athena, or any BI tool on the Parquet | AWS delivers files; you choose the query layer |
| FinOps Toolkit / FOCUS datasets | FOCUS 1.2 and 1.0 exports with AWS columns | Both clouds now support the open FOCUS schema |
| Azure Synapse or Data Explorer over exports | Athena (serverless SQL on S3) | Pay per data scanned: partition and use Parquet |
| EA / MCA price sheet and usage details | CUR line items | AWS rows are far more detailed; with resource IDs they can run to billions of rows for big estates |
| Azure Storage account with firewall for exports | S3 bucket with a policy that allows the billing service principals | You write the bucket policy that lets the export service write |
Architecture
Security-boundary view: the export is created in the management account, but the S3 bucket, catalogue and Athena workgroup can live in a dedicated FinOps account (or the log archive account). Only the billing service principals can write to the bucket. FinOps analysts get read access through a role. Finance gets the results, not the raw resource-level files. A KMS key protects the data at rest and its key policy must allow the export service to use it. ⚠ VERIFY key policy requirements for exports.
Step-by-step implementation
Decide the scope and the columns
Choose CUR 2.0, Parquet, daily granularity. Resource IDs off at first (smaller, cheaper, and less sensitive). Include split cost allocation data only if you run containers and need per-pod cost. ⚠ VERIFY the options your account shows.
Create the bucket and its policy
A private, encrypted, versioned bucket. The bucket policy lets the Data Exports service principal and the legacy billing reports principal write objects, scoped with aws:SourceAccount and aws:SourceArn conditions to stop a confused-deputy abuse. ⚠ VERIFY the exact principals and condition keys in the current documentation.
Create the export
In the management account (Region us-east-1, which is where billing data exports are managed ⚠ VERIFY), create the CUR 2.0 export to the bucket with a prefix and a name. The first delivery can take up to a day.
Create the Glue table and the Athena workgroup
Create a table over the Parquet path, partitioned by billing period. Create a dedicated workgroup with an output location and a bytes-scanned-per-query cutoff so no query can scan unbounded data.
Write the three core queries
Cost by account, cost by service, and cost by cost centre including the "(untagged)" bucket. Check totals against Cost Explorer for the same period, with the same cost metric.
Build the showback with an allocation rule
Direct cost (tagged) goes to its cost centre. Unattributed and shared cost (untagged, support, tax, network) is allocated by a rule, for example in proportion to each cost centre's direct spend. Document the rule and share it with finance.
Automate and retain
Run the showback monthly after the month closes. Keep each month's result as a file in S3 with a retention rule, and keep the export bucket for your retention period.
AWS Console instructions
- Create an export: Console (management account) → Billing and Cost Management → Data Exports → Create. Choose Standard data export, table Cost and usage report 2.0 (CUR 2.0). Pick the columns you need (or all). Set time granularity Daily, file format Parquet, compression Parquet, and file versioning Overwrite existing export file. Select your S3 bucket and prefix. ⚠ VERIFY console wording.
- See delivery: Data Exports → select the export → Export runs (or open the S3 prefix). Files appear under
<prefix>/<export-name>/data/BILLING_PERIOD=YYYY-MM/. ⚠ VERIFY the path layout. - Athena: Console → Athena → Query editor → choose the workgroup
hl-finops→ run theCREATE EXTERNAL TABLEstatement, thenMSCK REPAIR TABLEto load partitions. - Pre-built dashboard (optional): Data Exports → Create → Cost and usage dashboard deploys QuickSight resources. QuickSight has its own cost, so skip it in the free path.
AWS CLI
# 1) Create a CUR 2.0 export to S3 (management account, us-east-1). JSON file shown below.
aws bcm-data-exports create-export --export file://hl-cur2-export.json \
--profile learn-mgmt --region us-east-1
# 2) List exports and check the status of one.
aws bcm-data-exports list-exports --profile learn-mgmt --region us-east-1
aws bcm-data-exports get-export --export-arn <export-arn> --profile learn-mgmt --region us-east-1
# 3) See delivered files.
aws s3 ls s3://hl-billing-exports-111122223333/cur2/hl-cur2/ --recursive \
--profile learn-finops | head
# 4) Create an Athena workgroup that cancels queries scanning more than about 1 GB.
aws athena create-work-group --name hl-finops \
--configuration 'ResultConfiguration={OutputLocation=s3://hl-billing-exports-111122223333/athena-results/},EnforceWorkGroupConfiguration=true,BytesScannedCutoffPerQuery=1073741824' \
--profile learn-finops --region us-east-1
# 5) Run a query and fetch its results.
QID=$(aws athena start-query-execution --work-group hl-finops \
--query-string "SELECT 1" --query-execution-context Database=hl_billing \
--profile learn-finops --region us-east-1 --query QueryExecutionId --output text)
aws athena get-query-execution --query-execution-id "$QID" --profile learn-finops --region us-east-1
aws athena get-query-results --query-execution-id "$QID" --profile learn-finops --region us-east-1{
"Export": {
"Name": "hl-cur2",
"Description": "Harbourline CUR 2.0, daily, Parquet",
"DataQuery": {
"QueryStatement": "SELECT * FROM COST_AND_USAGE_REPORT",
"TableConfigurations": {
"COST_AND_USAGE_REPORT": {
"TIME_GRANULARITY": "DAILY",
"INCLUDE_RESOURCES": "FALSE",
"INCLUDE_MANUAL_DISCOUNT_COMPATIBILITY": "FALSE",
"INCLUDE_SPLIT_COST_ALLOCATION_DATA": "FALSE"
}
}
},
"DestinationConfigurations": {
"S3Destination": {
"S3Bucket": "hl-billing-exports-111122223333",
"S3Prefix": "cur2",
"S3Region": "us-east-1",
"S3OutputConfigurations": {
"OutputType": "CUSTOM",
"Format": "PARQUET",
"Compression": "PARQUET",
"Overwrite": "OVERWRITE_REPORT"
}
}
},
"RefreshCadence": { "Frequency": "SYNCHRONOUS" }
}
}SELECT * takes every column. To trim sensitive or unneeded columns, list them instead (SELECT bill_billing_period_start_date, line_item_usage_account_id, ...). ⚠ VERIFY the option names and column names against the current Data Exports documentation and CLI help, because the schema is large and evolves.
The Athena table, reduced to the columns this lesson uses. A real CUR 2.0 table has many more; selecting only a few columns in the export (above) gives you a table this size.
CREATE DATABASE IF NOT EXISTS hl_billing;
CREATE EXTERNAL TABLE IF NOT EXISTS hl_billing.cur2 (
line_item_usage_account_id string,
line_item_product_code string,
line_item_line_item_type string,
line_item_usage_start_date timestamp,
line_item_unblended_cost double,
resource_tags map<string,string>
)
PARTITIONED BY (billing_period string)
STORED AS PARQUET
LOCATION 's3://hl-billing-exports-111122223333/cur2/hl-cur2/data/';
MSCK REPAIR TABLE hl_billing.cur2;The partition name must match the folder naming (BILLING_PERIOD=2026-11 uses an upper-case key in the S3 path, and Hive-style partition discovery is case sensitive in some setups). ⚠ VERIFY the exact casing and the way the tags map keys are named (CUR 2.0 tag keys in the map look like user_CostCenter ⚠ VERIFY) by running SELECT resource_tags FROM hl_billing.cur2 LIMIT 5 first.
Infrastructure as Code
A hardened bucket, a workgroup with a scan limit and the export itself. The export uses the L1 construct from the Data Exports module. Property names follow the CloudFormation reference for AWS::BCMDataExports::Export, and the construct must be deployed in us-east-1. ⚠ VERIFY the package name and property types before relying on them.
using System.Collections.Generic;
using Amazon.CDK;
using Amazon.CDK.AWS.Athena;
using Amazon.CDK.AWS.BCMDataExports;
using Amazon.CDK.AWS.IAM;
using Amazon.CDK.AWS.S3;
using Constructs;
namespace Harbourline.Governance.Infra;
public sealed class BillingExportStack : Stack
{
public BillingExportStack(Construct scope, string id, IStackProps? props = null) : base(scope, id, props)
{
Amazon.CDK.Tags.Of(this).Add("Project", "cea-lab");
Amazon.CDK.Tags.Of(this).Add("Lab", "m15-l10");
Amazon.CDK.Tags.Of(this).Add("Environment", "learn");
var bucket = new Bucket(this, "ExportBucket", new BucketProps
{
BucketName = $"hl-billing-exports-{Aws.ACCOUNT_ID}",
BlockPublicAccess = BlockPublicAccess.BLOCK_ALL,
Encryption = BucketEncryption.S3_MANAGED, // SSE-KMS with a CMK is stronger ⚠ VERIFY export support
EnforceSSL = true,
Versioned = true,
LifecycleRules = new[]
{
new LifecycleRule { Id = "expire-athena-results", Prefix = "athena-results/", Expiration = Duration.Days(30) },
},
RemovalPolicy = RemovalPolicy.RETAIN,
});
// Let the billing export services write, only on behalf of this account.
bucket.AddToResourcePolicy(new PolicyStatement(new PolicyStatementProps
{
Sid = "AllowBillingExportsWrite",
Effect = Effect.ALLOW,
Principals = new IPrincipal[]
{
new ServicePrincipal("bcm-data-exports.amazonaws.com"),
new ServicePrincipal("billingreports.amazonaws.com"),
},
Actions = new[] { "s3:PutObject", "s3:GetBucketPolicy" },
Resources = new[] { bucket.BucketArn, bucket.ArnForObjects("*") },
Conditions = new Dictionary<string, object>
{
["StringEquals"] = new Dictionary<string, object> { ["aws:SourceAccount"] = Aws.ACCOUNT_ID },
},
}));
new CfnExport(this, "Cur2Export", new CfnExportProps
{
Export = new CfnExport.ExportProperty
{
Name = "hl-cur2",
Description = "Harbourline CUR 2.0, daily, Parquet",
DataQuery = new CfnExport.DataQueryProperty
{
QueryStatement = "SELECT * FROM COST_AND_USAGE_REPORT",
TableConfigurations = new Dictionary<string, object>
{
["COST_AND_USAGE_REPORT"] = new Dictionary<string, string>
{
["TIME_GRANULARITY"] = "DAILY",
["INCLUDE_RESOURCES"] = "FALSE",
["INCLUDE_MANUAL_DISCOUNT_COMPATIBILITY"] = "FALSE",
["INCLUDE_SPLIT_COST_ALLOCATION_DATA"] = "FALSE",
},
},
},
DestinationConfigurations = new CfnExport.DestinationConfigurationsProperty
{
S3Destination = new CfnExport.S3DestinationProperty
{
S3Bucket = bucket.BucketName,
S3Prefix = "cur2",
S3Region = "us-east-1",
S3OutputConfigurations = new CfnExport.S3OutputConfigurationsProperty
{
OutputType = "CUSTOM",
Format = "PARQUET",
Compression = "PARQUET",
Overwrite = "OVERWRITE_REPORT",
},
},
},
RefreshCadence = new CfnExport.RefreshCadenceProperty { Frequency = "SYNCHRONOUS" },
},
});
// Athena workgroup: every query is capped at about 1 GB of scanned data.
new CfnWorkGroup(this, "FinOpsWorkGroup", new CfnWorkGroupProps
{
Name = "hl-finops",
State = "ENABLED",
WorkGroupConfiguration = new CfnWorkGroup.WorkGroupConfigurationProperty
{
EnforceWorkGroupConfiguration = true,
BytesScannedCutoffPerQuery = 1_073_741_824,
ResultConfiguration = new CfnWorkGroup.ResultConfigurationProperty
{
OutputLocation = $"s3://{bucket.BucketName}/athena-results/",
EncryptionConfiguration = new CfnWorkGroup.EncryptionConfigurationProperty
{
EncryptionOption = "SSE_S3",
},
},
},
});
}
}Complete code
The free path: DuckDB on a sample file
Use this first. It needs no AWS account. Create a small fictional export sample (flattened, so a CSV can hold it), then run the showback query locally.
bill_billing_period,line_item_usage_account_id,line_item_product_code,line_item_line_item_type,line_item_usage_start_date,line_item_unblended_cost,cost_center
2026-10,111122223333,AmazonEC2,Usage,2026-10-03T00:00:00Z,412.50,CC-5100
2026-10,111122223333,AmazonRDS,Usage,2026-10-03T00:00:00Z,288.10,CC-5100
2026-10,111122223333,AmazonS3,Usage,2026-10-04T00:00:00Z,35.75,CC-5100
2026-10,222233334444,AmazonEC2,Usage,2026-10-03T00:00:00Z,640.00,CC-4410
2026-10,222233334444,AWSLambda,Usage,2026-10-05T00:00:00Z,52.20,CC-4410
2026-10,222233334444,AmazonCloudWatch,Usage,2026-10-05T00:00:00Z,118.40,
2026-10,333344445555,AmazonEC2,Usage,2026-10-06T00:00:00Z,95.00,
2026-10,333344445555,AWSSupportBusiness,Fee,2026-10-01T00:00:00Z,100.00,
2026-10,333344445555,AmazonEC2,Tax,2026-10-31T00:00:00Z,45.30,
2026-10,111122223333,AmazonEC2,SavingsPlanCoveredUsage,2026-10-07T00:00:00Z,210.00,CC-5100-- Run with: duckdb < showback.sql (or paste into any DuckDB session)
CREATE TABLE cur AS
SELECT * FROM read_csv('cur-sample.csv', header = true, types = {'cost_center': 'VARCHAR'});
-- 1. Cost by cost centre, with an explicit untagged bucket.
SELECT COALESCE(NULLIF(cost_center, ''), '(untagged)') AS cost_center,
ROUND(SUM(line_item_unblended_cost), 2) AS cost
FROM cur
WHERE bill_billing_period = '2026-10'
GROUP BY 1
ORDER BY cost DESC;
-- 2. Showback: allocate the untagged/shared pool in proportion to each cost centre's direct spend.
WITH base AS (
SELECT COALESCE(NULLIF(cost_center, ''), '(untagged)') AS cc,
line_item_unblended_cost AS cost
FROM cur
WHERE bill_billing_period = '2026-10'
),
direct AS (SELECT cc, SUM(cost) AS direct_cost FROM base WHERE cc <> '(untagged)' GROUP BY cc),
shared AS (SELECT SUM(cost) AS shared_cost FROM base WHERE cc = '(untagged)'),
tot AS (SELECT SUM(direct_cost) AS t FROM direct)
SELECT d.cc AS cost_center,
ROUND(d.direct_cost, 2) AS direct_cost,
ROUND(s.shared_cost * d.direct_cost / t.t, 2) AS allocated_shared,
ROUND(d.direct_cost + s.shared_cost * d.direct_cost / t.t, 2) AS showback_total
FROM direct d CROSS JOIN shared s CROSS JOIN tot t
ORDER BY showback_total DESC;Expected result on the sample: CC-5100 direct 946.35, allocated shared 207.17, total 1,153.52; CC-4410 direct 692.20, allocated shared 151.53, total 843.73. The two totals add to 1,997.25, which equals the sum of every line. If your allocated total does not equal the bill, the allocation has a bug. Always reconcile.
Why this rule? Proportional allocation is simple and transparent, but it is a policy, not a fact. Finance might prefer an even split for support fees, by headcount for tooling, or by usage for shared network. Pick one, write it down and apply it consistently. Do not hide it inside a query nobody reads.
The Athena version with a C# runner
For the real export, the same logic reads the resource_tags map. This C# tool runs a query in the hl-finops workgroup, waits for it and prints the rows. The workgroup's scan cutoff protects your wallet.
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net10.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
<TreatWarningsAsErrors>true</TreatWarningsAsErrors>
</PropertyGroup>
<ItemGroup>
<!-- Look up exact 4.x versions at https://api.nuget.org/v3-flatcontainer/<id>/index.json -->
<PackageReference Include="AWSSDK.Athena" Version="4.*" />
</ItemGroup>
</Project>using Amazon.Athena;
using Amazon.Athena.Model;
string period = args.Length > 0 ? args[0] : DateTime.UtcNow.AddMonths(-1).ToString("yyyy-MM");
// Tag key name inside resource_tags. CUR 2.0 key naming must be checked in your data ⚠ VERIFY.
const string tagKey = "user_CostCenter";
string sql = $"""
WITH base AS (
SELECT COALESCE(NULLIF(element_at(resource_tags, '{tagKey}'), ''), '(untagged)') AS cc,
line_item_unblended_cost AS cost
FROM hl_billing.cur2
WHERE billing_period = '{period}'
),
direct AS (SELECT cc, SUM(cost) AS direct_cost FROM base WHERE cc <> '(untagged)' GROUP BY cc),
shared AS (SELECT SUM(cost) AS shared_cost FROM base WHERE cc = '(untagged)'),
tot AS (SELECT SUM(direct_cost) AS t FROM direct)
SELECT d.cc AS cost_center,
ROUND(d.direct_cost, 2) AS direct_cost,
ROUND(s.shared_cost * d.direct_cost / t.t, 2) AS allocated_shared,
ROUND(d.direct_cost + s.shared_cost * d.direct_cost / t.t, 2) AS showback_total
FROM direct d CROSS JOIN shared s CROSS JOIN tot t
ORDER BY showback_total DESC
""";
using var athena = new AmazonAthenaClient();
var start = await athena.StartQueryExecutionAsync(new StartQueryExecutionRequest
{
QueryString = sql,
WorkGroup = "hl-finops", // output location and scan cutoff come from the workgroup
QueryExecutionContext = new QueryExecutionContext { Database = "hl_billing" },
});
string id = start.QueryExecutionId;
Console.WriteLine($"Started query {id} for billing period {period}");
QueryExecutionState? state;
do
{
await Task.Delay(TimeSpan.FromSeconds(2));
var status = await athena.GetQueryExecutionAsync(new GetQueryExecutionRequest { QueryExecutionId = id });
state = status.QueryExecution.Status.State;
if (state == QueryExecutionState.FAILED || state == QueryExecutionState.CANCELLED)
{
Console.Error.WriteLine($"Query {state}: {status.QueryExecution.Status.StateChangeReason}");
return 1;
}
}
while (state != QueryExecutionState.SUCCEEDED);
string? token = null;
bool header = true;
do
{
var page = await athena.GetQueryResultsAsync(new GetQueryResultsRequest { QueryExecutionId = id, NextToken = token });
foreach (var row in page.ResultSet.Rows ?? [])
{
if (header) { header = false; continue; } // the first row repeats the column names
var cells = (row.Data ?? []).Select(d => d.VarCharValue ?? "").ToArray();
Console.WriteLine($"{cells[0],-14} direct {cells[1],10} shared {cells[2],10} total {cells[3],10}");
}
token = page.NextToken;
}
while (token is not null);
return 0;Code explained line by line
- The billing period comes from an argument, defaulting to last month, because the current month is provisional and will be restated.
tagKeyis isolated in one constant. If your export names tag keys differently from the example, you change one line.- The raw string literal
"""holds the SQL.element_at(resource_tags, 'key')returnsNULLif the key is missing, whereresource_tags['key']could raise an error on some engines.COALESCE(NULLIF(..., ''), '(untagged)')maps both missing and empty to one bucket. WHERE billing_period = ...uses the partition column so Athena reads only one month's files. That is the main lever on Athena cost.- The three CTEs mirror the DuckDB version: direct cost per cost centre, the shared pool, and the total of direct cost used as the allocation denominator.
StartQueryExecutionAsyncnames a workgroup. The workgroup supplies the output bucket and the scan cutoff, so the tool does not need to know them and cannot override them when the workgroup enforces its configuration.- The polling loop checks the state every 2 seconds.
FAILEDandCANCELLEDstop with the reason. A query that hit the scan cutoff fails with an explanatory message. GetQueryResultsAsyncreturns pages. For aSELECT, the first row of the first page is the header, so the code skips it once.VarCharValueisnullfor SQL NULL, hence?? "".- The method returns an exit code, so a scheduled job can fail visibly.
Security considerations
⚠ Learning shortcut — not for production
The lab leaves resource IDs off, uses SSE-S3 and runs the queries with your own sandbox role. In production, the bucket uses a customer-managed KMS key with a tight key policy, lives in a dedicated FinOps or log archive account with Object Lock on the monthly showback files, Athena runs under a read-only role with Lake Formation or IAM column-level restrictions on sensitive columns, and billing exports with resource IDs are available only to the platform team.
- Confused deputy. The bucket policy that allows the billing services to write should carry
aws:SourceAccount(andaws:SourceArnwhere the service supports it) conditions, so another account cannot trick the service into writing to your bucket. ⚠ VERIFY the supported condition keys. - Sensitive content. Resource IDs, account IDs and tags are metadata that attackers value. Do not publish extracts. Share aggregated showback, not raw rows.
- Cost of the analysis itself. Without a workgroup scan limit, a careless
SELECT *across years of data is a cost incident. Always use partitions, Parquet and a cutoff. - Retention and privacy. Billing data rarely holds personal data, but tags could (for example a person's name in
Owner). The tagging lesson forbids that for this reason. - Immutable history. Keep monthly showback outputs in a bucket with Object Lock so a restated or altered number can be detected.
Banking use case
Harbourline's finance team needs the October showback by the 5th working day. The platform team's pipeline runs after month close: the export has delivered the final October files, Athena runs the showback query with a one-gigabyte cutoff, and a small job writes a CSV and a PDF-ready table to a locked bucket. The report shows direct spend per cost centre, a "shared" line for support, tax and untagged resources, and the allocation rule in its footer. Finance reconciles the grand total to the invoice within a small tolerance explained by credits. A recurring finding: three accounts with no CostCenter tag contribute 18 percent of the shared pool, so the platform team uses the same data to send those account owners a list of untagged resources. In the second month the shared pool drops by a third.
Common mistakes
- Summing every row. Some line item types (fees, credits, Savings Plan lines, tax) need explicit handling. Decide which types are in, and test against the invoice.
- Mixing cost columns. Unblended, net unblended, amortized and net amortized give different totals. Pick one for the report.
- Querying without a partition filter. Full scans cost money and time.
- Treating the current month as final. It is provisional and can be restated.
- Hiding the allocation rule. If teams do not understand why their share changed, they stop trusting the report.
- Resource IDs on for every account. Larger files, higher Athena cost and more sensitive data. Turn on for diagnosis, not by default.
- Building on the legacy format. Prefer CUR 2.0. Plan migration if you have an older pipeline.
- Forgetting that activated tags are not retroactive by default. The history before activation has empty tag values.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Export created but no files in S3 | First delivery not done yet, or the bucket policy does not allow the service | Wait up to a day; check the bucket policy principals and conditions |
| Export creation fails with an access error | Bucket policy missing or wrong, or created from a member account | Create from the management account; fix the policy ⚠ VERIFY |
| Athena returns zero rows | Partitions not loaded, wrong partition value, or wrong LOCATION | Run MSCK REPAIR TABLE; list the S3 prefix; check partition name and casing |
HIVE_BAD_DATA or type mismatch | Table schema differs from the Parquet schema | Rebuild the table from the actual Parquet schema or select fewer columns |
| Query fails with "bytes scanned limit" | The workgroup cutoff cancelled the query | Add a partition filter and fewer columns; raise the limit only deliberately |
| Tag column is empty for known tagged resources | Tag not activated, or activated after the usage, or the map key name differs | Check activation date; inspect resource_tags keys with a sample query |
| Totals do not match Cost Explorer | Different cost metric, line item types or period boundary | Use the same metric and period; exclude or include the same types |
| Athena results bucket fills with files | Query results are never cleaned | Add a lifecycle expiry on athena-results/ |
Cost and cleanup
Cost: credits for the AWS path, free for the DuckDB path. Data Exports to S3 have no export charge themselves, you pay for S3 storage and requests, which for a small sandbox account is cents. ⚠ VERIFY current export pricing. Athena bills per amount of data scanned (about 5 USD per terabyte at the time of checking, with a minimum per query). ⚠ VERIFY the price. A small sandbox export in Parquet is megabytes, so a query costs a fraction of a cent. Glue Data Catalog storage for a handful of tables is within the free allowance. ⚠ VERIFY. A large production estate is another matter: that is why the workgroup cutoff exists.
Free alternative: do everything in DuckDB with the sample CSV above, or download one small month from S3 and query it locally.
Teardown:
# Delete the export (management account).
aws bcm-data-exports delete-export --export-arn <export-arn> \
--profile learn-mgmt --region us-east-1
# Drop the table and database in Athena.
aws athena start-query-execution --work-group hl-finops \
--query-string "DROP TABLE IF EXISTS hl_billing.cur2" \
--profile learn-finops --region us-east-1
aws athena start-query-execution --work-group hl-finops \
--query-string "DROP DATABASE IF EXISTS hl_billing" \
--profile learn-finops --region us-east-1
# Delete the workgroup and its query history.
aws athena delete-work-group --work-group hl-finops --recursive-delete-option \
--profile learn-finops --region us-east-1
# Empty and delete the bucket (versioned: remove all versions too, or use the console "Empty" action).
aws s3 rb s3://hl-billing-exports-111122223333 --force --profile learn-finops
# Confirm nothing tagged for the lab remains billing.
aws resourcegroupstaggingapi get-resources --tag-filters Key=Project,Values=cea-lab \
--region us-east-1 --profile learn-finops --query 'ResourceTagMappingList[].ResourceARN'A versioned bucket needs all object versions and delete markers removed before it can be deleted. aws s3 rb --force handles current objects only, so empty it from the console or use aws s3api delete-objects on the version list.
Exercises
- 🟢 Guided: run the DuckDB showback on the sample CSV. Confirm that the showback total equals the sum of all lines.
- 🟡 Challenge: change the allocation rule so that support fees (
line_item_product_code LIKE 'AWSSupport%') are split evenly between cost centres, while the rest of the shared pool stays proportional. Reconcile the total again. - 🔴 Live problem: finance reports that your showback total for October is 2.4 percent above the invoice. List the five most likely causes (credits, tax treatment, line item types, timing, restatement) and the query you would run for each.
Assignment
Create governance/cur/ in your portfolio repository with the export definition (CDK or Terraform), the DDL, the three queries, the C# or DuckDB runner and a short showback-method.md that states the cost metric, the line item types included, the allocation rule and who approved it.
Solutions
Run the file with DuckDB. The direct cost for CC-5100 is 946.35 and for CC-4410 is 692.20, the untagged pool is 358.70, and allocation is in proportion 946.35 : 692.20. Allocated shared amounts of 207.17 and 151.53 add to 358.70, so the two showback totals (1,153.52 and 843.73) add to 1,997.25, the sum of every line. The reconciliation check is the important part of the exercise: it catches both logic errors and double counting.
Split the pool into two CTEs: support (rows where line_item_product_code LIKE 'AWSSupport%') and other_shared (the rest of the untagged pool). Allocate support with support_cost / number_of_cost_centres and other_shared proportionally. Sum both for each cost centre. Check again that the allocated total equals the pool. In the sample, support is 100.00, so each of the two cost centres gets 50.00 from it, and the remaining 258.70 is allocated proportionally. The point: an allocation rule per cost type is a policy decision, and the query makes it explicit and reviewable.
Knowledge check
Answer: Cost and Usage Report 2.0 (CUR 2.0) in Data Exports. The legacy CUR still works but is the older way, and AWS Usage Report is in maintenance.
Answer: Athena charges for data scanned. A partition filter reads only the relevant month, and Parquet is columnar and compressed so only the needed columns are read. Together they can cut scanned bytes by orders of magnitude.
Answer: No. Cost allocation tags apply from activation (unless backfilled), so earlier rows have no tag values. Report that period with the account or cost category dimension, or use a backfill if available. ⚠ VERIFY
Answer: It cancels any query that scans more than the limit, protecting you from an accidental expensive full scan.
Answer: Because an allocation that does not add up to the total has either double counted or dropped cost. Reconciliation is the control that makes the report trustworthy to finance.
Interview questions
- How would you build a cost showback for a multi-account bank? Export CUR 2.0 to S3 as Parquet, query with Athena in a controlled workgroup, group by
CostCentertag and cost category, allocate shared costs by a documented rule, reconcile to the invoice, and retain the monthly result immutably. - What is FOCUS and why does it matter? An open specification for cost and usage data. It lets one query model work across cloud providers, which helps multi-cloud and Azure-plus-AWS organisations.
- How do you stop Athena queries from becoming a cost problem? Workgroups with a bytes-scanned cutoff, partitions, Parquet, column selection, and result lifecycle rules.
- Why can the export and Cost Explorer differ? Cost metric, line item types, timing, restatement and what each includes (tax, credits, support).
Certification practice
A company needs hourly-level cost and usage data for all 80 accounts in its organisation, retained for five years, and queried with SQL. Which design is MOST appropriate?
A. Screenshots of Cost Explorer each month. B. A CUR 2.0 data export created in the management account delivering Parquet to a secured S3 bucket, queried with Athena. C. CloudWatch billing metrics. D. AWS Budgets reports.
Answer: B. Data Exports deliver the most detailed data to S3, Parquet with Athena is cost-efficient and S3 gives long retention. A is manual and not queryable. C offers only an estimated charges metric. D reports on budgets, not line items.
An engineer's Athena queries on billing data cost more than expected. Which TWO changes reduce cost the most? (Choose two.)
A. Convert to or use Parquet and select only needed columns. B. Filter on the billing period partition. C. Increase the Athena result retention. D. Add more Glue crawlers.
Answer: A and B. Athena charges per bytes scanned: columnar format with column pruning and partition filters cut scanning. C and D do not change bytes scanned.
Which AWS capability provides the most detailed billing data, delivered to S3?
A. AWS Cost and Usage Reports (through Data Exports). B. Service Quotas. C. AWS Trusted Advisor. D. AWS Support plan.
Answer: A. Cost and Usage Reports contain line-item billing data. The others do not.
Completion checklist
- I can explain the difference between CUR 2.0, legacy CUR and FOCUS
- I ran the DuckDB showback and reconciled the total
- I understand why partitions, Parquet and a workgroup cutoff matter
- I documented the cost metric, line item types and allocation rule
- I ran the cleanup steps (or skipped AWS and stayed on the free path)
Key takeaways
- The export is the ledger behind every cost screen. Use CUR 2.0 in Data Exports for new work.
- Query with partitions, Parquet and a workgroup scan cutoff, or use DuckDB for free.
- Decide the cost metric and line item types first, and reconcile to the invoice.
- Shared cost allocation is a policy. Write it down, apply it consistently and show it in the report.
- Treat billing data as sensitive and the current month as provisional.
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.
FinOps foundations: Cost Explorer, budgets per account and anomaly alerts
Make cloud spend visible and owned: read Cost Explorer by account, service and tag, set a budget and an anomaly monitor per account, group costs with cost categories, and report unit costs with a small C# tool.
Commitments and rightsizing: Savings Plans, Reserved Instances, Compute Optimizer and Trusted Advisor
Cut a steady cloud bill safely: rightsize first, then size a Savings Plans commitment from your own usage data, understand Reserved Instances, and use Compute Optimizer and Trusted Advisor findings as a governed backlog.