Skip to Content
Cloud Engineering Academy
15. Multi-Account Governance & FinOps

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.

advanced⏱ 1 h 40 min🟡 Low costSAP-C03SOA-C03CLF-C02

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 CostCenter tag 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 typeWhat it isUse it for
Cost and Usage Report 2.0 (CUR 2.0)The new and recommended way to get detailed cost and usage dataAlmost all new work: line-item analysis, showback, FinOps tooling
Legacy CURThe older Cost and Usage Reports, accessed with different API actionsExisting pipelines that have not migrated yet
FOCUS 1.2 / FOCUS 1.0 with AWS columnsData in the FinOps Open Cost and Usage Specification, an open schemaMulti-cloud reporting using one common schema
Cost optimization recommendationsOutput from Cost Optimization HubTracking recommendations over time
Carbon emissionsEmissions dataSustainability reporting
Cost and usage dashboardAn export plus QuickSight integration with a pre-built dashboardA 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_dateThe billing month
bill_payer_account_id, line_item_usage_account_idPayer and the account that used the service
line_item_product_codeThe service, for example AmazonEC2
line_item_usage_type, line_item_operationWhat exactly was used
line_item_line_item_typeUsage, Tax, Fee, Credit, SavingsPlanCoveredUsage, DiscountedUsage and others: crucial for avoiding double counting
line_item_usage_start_dateStart of the time unit
line_item_unblended_cost, pricing_public_on_demand_costCost at the line, and what On-Demand would have cost
line_item_resource_idThe 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

NeedHow an export helpsLimit
Reconcile the invoiceLine 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, hourGroup by any columnTags only appear if activated, and only for usage after activation (⚠ VERIFY backfill)
Find top resourcesEnable resource IDs and group by line_item_resource_idLarger files; do this for a limited period or a subset
Allocate shared costsAllocate by tag, usage or proportion using your own rulesAWS gives raw data; the rule is your policy
Keep a historyFiles persist in S3S3 lifecycle and retention are yours to set
Feed other toolsParquet or CSV to BI, Athena, DuckDB, RedshiftYou 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 equivalentWhat's different
Cost Management exports (scheduled CSV to storage)Data Exports to S3Similar; AWS offers SQL column selection at export creation
Azure Cost Management Power BI connectorQuickSight, Athena, or any BI tool on the ParquetAWS delivers files; you choose the query layer
FinOps Toolkit / FOCUS datasetsFOCUS 1.2 and 1.0 exports with AWS columnsBoth clouds now support the open FOCUS schema
Azure Synapse or Data Explorer over exportsAthena (serverless SQL on S3)Pay per data scanned: partition and use Parquet
EA / MCA price sheet and usage detailsCUR line itemsAWS rows are far more detailed; with resource IDs they can run to billions of rows for big estates
Azure Storage account with firewall for exportsS3 bucket with a policy that allows the billing service principalsYou 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 the CREATE EXTERNAL TABLE statement, then MSCK REPAIR TABLE to 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
hl-cur2-export.json
{
  "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-table.sql
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.

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

cur-sample.csv
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
showback.sql
-- 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.

CurReport.csproj
<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <TreatWarningsAsErrors>true</TreatWarningsAsErrors>
  </PropertyGroup>
  <ItemGroup>
    <!-- Look up exact 4.x versions at https://api.nuget.org/v3-flatcontainer/<id>/index.json -->
    <PackageReference Include="AWSSDK.Athena" Version="4.*" />
  </ItemGroup>
</Project>
Program.cs
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

  1. The billing period comes from an argument, defaulting to last month, because the current month is provisional and will be restated.
  2. tagKey is isolated in one constant. If your export names tag keys differently from the example, you change one line.
  3. The raw string literal """ holds the SQL. element_at(resource_tags, 'key') returns NULL if the key is missing, where resource_tags['key'] could raise an error on some engines. COALESCE(NULLIF(..., ''), '(untagged)') maps both missing and empty to one bucket.
  4. WHERE billing_period = ... uses the partition column so Athena reads only one month's files. That is the main lever on Athena cost.
  5. 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.
  6. StartQueryExecutionAsync names 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.
  7. The polling loop checks the state every 2 seconds. FAILED and CANCELLED stop with the reason. A query that hit the scan cutoff fails with an explanatory message.
  8. GetQueryResultsAsync returns pages. For a SELECT, the first row of the first page is the header, so the code skips it once. VarCharValue is null for SQL NULL, hence ?? "".
  9. 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 (and aws:SourceArn where 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

SymptomLikely causeFix
Export created but no files in S3First delivery not done yet, or the bucket policy does not allow the serviceWait up to a day; check the bucket policy principals and conditions
Export creation fails with an access errorBucket policy missing or wrong, or created from a member accountCreate from the management account; fix the policy ⚠ VERIFY
Athena returns zero rowsPartitions not loaded, wrong partition value, or wrong LOCATIONRun MSCK REPAIR TABLE; list the S3 prefix; check partition name and casing
HIVE_BAD_DATA or type mismatchTable schema differs from the Parquet schemaRebuild the table from the actual Parquet schema or select fewer columns
Query fails with "bytes scanned limit"The workgroup cutoff cancelled the queryAdd a partition filter and fewer columns; raise the limit only deliberately
Tag column is empty for known tagged resourcesTag not activated, or activated after the usage, or the map key name differsCheck activation date; inspect resource_tags keys with a sample query
Totals do not match Cost ExplorerDifferent cost metric, line item types or period boundaryUse the same metric and period; exclude or include the same types
Athena results bucket fills with filesQuery results are never cleanedAdd 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

Knowledge check

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 CostCenter tag 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

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.

On this page