Breaking Down AWS Cost and Usage Reports (CUR) A Step-by-Step Guide – Part 2

CUR Analysis
Share this post:
Reading Time: 9 minutes

I remember sitting down with a public sector customer who’d just started using AWS Cost and Usage Reports (CUR). Their FinOps lead said, “We have the basics, but now we need to unpack this data.” I’ve heard this same thought from many teams: they’re fine with generating a basic CUR, yet they struggle to turn raw numbers into business intelligence.

In Part 1, I walked you through creating your first CUR and examining its basic structure. If you haven’t read that yet, I suggest you check it out first. This post moves beyond the simple setup and into approaches that help you spot trends, build dashboards, and drive decisions across your organisation.

What You’ll Discover

Here, I plan to go further with CUR analysis and create a custom dashboard. I’ll share how I’ve helped my own customers transform cost data into intelligence that shapes business decisions.

You’ll see how to break down data, produce targeted visualisations, and automate the reporting that genuinely matters. CUR analysis isn’t only about cost awareness. It’s about using data as a catalyst for action.

Why does this matter so much? One of my customers saved over $50,000 by identifying a strange cost spike in their development environment through a custom CUR analysis. Imagine if you could catch similar anomalies in your own AWS accounts. Wouldn’t that be a game-changer for you and your team?

Are You Ready for Advanced CUR Analysis?

Ask yourself:

  • Can you easily highlight projects running above budget?
  • Do you know if your Reserved Instance coverage might fall short?
  • How confident are you in forecasting cloud spend for the next quarter or year?

If any of these questions leave you stumped, stay with me. By the end, my hope is that you’ll have the methods and knowldege to tackle these obstacles head-on.

Advanced CUR Analysis Techniques

Fine-Tuning the CUR Data Structure

Your AWS Cost and Usage Reports (CUR) can grow massive in no time. It’s worth arranging the data to make your analysis simpler.

1. Convert CUR Files to Parquet

By default, CUR arrives as CSV, which can be unwieldy for large-scale queries. Converting those files to Parquet shrinks storage usage and accelerates analysis.

2. AWS Glue for Schema Updates

I like using AWS Glue to infer and manage CUR schemas. A Glue Crawler refreshes Athena’s knowledge of the latest CUR schema, so I don’t need to make manual changes.

3. Partitioning

Partitioning your CUR data (for example by year and month) drastically improves query performance. If you only need last month’s data, it’s much quicker to query a specific partition than the entire dataset.

Custom SQL for Data Driven Intelligence

SQL queries tailored to your own needs are the key to turning raw numbers into actionable findings.

1. Detecting Cost Anomalies

SELECT line_item_usage_start_date, 
       SUM(line_item_blended_cost) AS daily_cost
FROM aws_cur_table
GROUP BY line_item_usage_start_date
HAVING daily_cost > (
    SELECT AVG(SUM(line_item_blended_cost)) * 1.2 
    FROM aws_cur_table
);

This approach looks for daily spend that’s more than 20% above your average.

2. Costs by Project or Environment

SELECT resource_tags_project, 
       SUM(line_item_unblended_cost) AS project_cost
FROM aws_cur_table
GROUP BY resource_tags_project;

Tags like Environment or Project let you group costs by team or feature.

Merging CUR Data with Other Systems

If you have internal spreadsheets or finance databases, you can join them with CUR data in S3 to create a holistic view of your cloud spend.

Handling Cross-Account Charges and Shared Costs

Allocation Strategies

Shared resources (like NAT Gateways or central logging) might need splitting among multiple teams. One method is proportional cost allocation based on usage.

Charge Cross Account

With consolidated billing, group and distribute charges through AWS Cost Categories. Tag each item properly (e.g. Department, Project) so teams see their usage plainly.

Blended vs. Unblended Costs

  • Blended Rates: Combine costs across accounts to spread Reserved Instance (RI) and Savings Plan benefits.
  • Unblended Rates: Reflect actual spend by each account individually.

I recommend using unblended rates for finer detail on each team’s spend and blended rates for an organisation-wide lens. But be cautious: blended rates can obscure precise account-level usage.

Prepping CUR Data for QuickSight

Athena Views for QuickSight

If your team wants a straightforward way to extract insights, AWS CUDOS (Cloud Intelligence Dashboards) is an excellent starting point. It relies on SQL views that re-arrange CUR data into simpler structures, including the widely used summary_view.

Deploying the CUDOS summary_view

Here’s the SQL code I rely on to create the summary_view in Athena. You can find this query and more on the AWS GitHub Repository This view provides a good starting point for almost all of the basic data that you would want to visualise Swap in your own CUR database and table:

CREATE OR REPLACE VIEW summary_view AS
SELECT
    split_part("billing_period", '-', 1) "year",
    split_part("billing_period", '-', 2) "month",
    "bill_billing_period_start_date" "billing_period",
    CASE
        WHEN ("date_trunc"('month',"line_item_usage_start_date")) >= ("date_trunc"('month', current_timestamp) - INTERVAL '3' MONTH)
        THEN "date_trunc"('day', "line_item_usage_start_date")
        ELSE "date_trunc"('month', "line_item_usage_start_date")
    END "usage_date",
    "bill_payer_account_id" "payer_account_id",
    "line_item_usage_account_id" "linked_account_id",
    "bill_invoice_id" "invoice_id",
    "line_item_line_item_type" "charge_type",
    CASE
        WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN 'Running_Usage'
        WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN 'Running_Usage'
        WHEN ("line_item_line_item_type" = 'Usage') THEN 'Running_Usage'
        ELSE 'non_usage'
    END "charge_category",
    CASE
        WHEN ("savings_plan_savings_plan_a_r_n" <> '') THEN 'SavingsPlan'
        WHEN ("reservation_reservation_a_r_n" <> '') THEN 'Reserved'
        WHEN ("line_item_usage_type" LIKE '%Spot%') THEN 'Spot'
        ELSE 'OnDemand'
    END "purchase_option",
    CASE
        WHEN ("savings_plan_savings_plan_a_r_n" <> '') THEN "savings_plan_savings_plan_a_r_n"
        WHEN ("reservation_reservation_a_r_n" <> '') THEN "reservation_reservation_a_r_n"
        ELSE CAST('' AS varchar)
    END "ri_sp_arn",
    "line_item_product_code" "product_code",
    product['product_name'] "product_name",
    CASE
        WHEN ("bill_billing_entity" = 'AWS Marketplace' 
              AND "line_item_line_item_type" NOT LIKE '%Discount%') 
             THEN product['product_name']
        WHEN ("product_servicecode" = '') THEN "line_item_product_code"
        ELSE "product_servicecode"
    END "service",
    "product_product_family" "product_family",
    "line_item_usage_type" "usage_type",
    "line_item_operation" "operation",
    "line_item_line_item_description" "item_description",
    "line_item_availability_zone" "availability_zone",
    product['region'] "region",
    CASE
        WHEN (("line_item_usage_type" LIKE '%Spot%') 
              AND ("line_item_product_code" = 'AmazonEC2') 
              AND ("line_item_line_item_type" = 'Usage')) 
             THEN split_part("line_item_line_item_description", '.', 1)
        ELSE product['instance_type_family']
    END "instance_type_family",
    CASE
        WHEN (("line_item_usage_type" LIKE '%Spot%') 
              AND ("line_item_product_code" = 'AmazonEC2') 
              AND ("line_item_line_item_type" = 'Usage')) 
             THEN split_part("line_item_line_item_description", ' ', 1)
        ELSE product['instance_type']
    END "instance_type",
    CASE
        WHEN (("line_item_usage_type" LIKE '%Spot%') 
              AND ("line_item_product_code" = 'AmazonEC2') 
              AND ("line_item_line_item_type" = 'Usage')) 
             THEN split_part(split_part("line_item_line_item_description", ' ', 2), '/', 1)
        ELSE product['operating_system']
    END "platform",
    product['tenancy'] "tenancy",
    product['physical_processor'] "processor",
    product['processor_features'] "processor_features",
    product['database_engine'] "database_engine",
    product['group'] "product_group",
    "product_from_location" "product_from_location",
    "product_to_location" "product_to_location",
    product['current_generation'] "current_generation",
    "line_item_legal_entity" "legal_entity",
    "bill_billing_entity" "billing_entity",
    "pricing_unit" "pricing_unit",
    approx_distinct("line_item_resource_id") "resource_id_count",
    sum(
        CASE
            WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') THEN "line_item_usage_amount"
            WHEN ("line_item_line_item_type" = 'DiscountedUsage') THEN "line_item_usage_amount"
            WHEN ("line_item_line_item_type" = 'Usage') THEN "line_item_usage_amount"
            ELSE 0
        END
    ) "usage_quantity",
    sum("line_item_unblended_cost") "unblended_cost",
    sum(
        CASE
            WHEN ("line_item_line_item_type" = 'SavingsPlanCoveredUsage') 
                 THEN "savings_plan_savings_plan_effective_cost"
            WHEN ("line_item_line_item_type" = 'SavingsPlanRecurringFee') 
                 THEN ("savings_plan_total_commitment_to_date" - "savings_plan_used_commitment")
            WHEN ("line_item_line_item_type" = 'DiscountedUsage') 
                 THEN "reservation_effective_cost"
            ELSE "line_item_unblended_cost"
        END
    ) "amortized_cost"
FROM
    "${cur2_database}"."${cur2_table_name}"
WHERE
    "bill_billing_period_start_date" >= ("date_trunc"('month', current_timestamp) - INTERVAL '7' MONTH)
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34;

The summary_view is just one piece of the larger Cloud Intelligence Dashboard (CID). Additional SQL views often accompany it. There’s a complete walkthrough in the official AWS Cloud Intelligence Dashboards guide.

Building QuickSight Dashboards

After shaping your CUR data, you can design interactive QuickSight dashboards. Here’s how I go about it:

Connecting to CUR Data

  1. In QuickSight, create a New Dataset.
  2. Select Athena and link it to your CUR database.
  3. Use the summary_view or your own custom view as the main dataset.

Streamline Dataset Fields

  • Remove unnecessary columns when preparing the dataset so your views aren’t cluttered.
  • Rename fields to friendlier labels (for example, line_item_unblended_costUnblended Cost).

QuickSight Speed-Up

  • Switch on SPICE for quicker dashboard loading.
  • Refresh your dataset often so it matches the latest CUR data.

Useful Calculated Fields

Daily Average Cost

sum({unblended_cost}) / countDistinct({usage_date})

Cost Share by Service

sum({unblended_cost}) / sumOver({unblended_cost}, [service]) * 100

Tag-Based Spend

ifelse({tag_project} = 'ProjectA', {unblended_cost}, 0)

Filters and Parameters

Timeframe Filters

Add date filter controls (e.g. last 30 days, this month, or custom ranges). Link these to usage_date in your dataset.

Dropdown Filters

Give readers a simple way to switch between services, regions, or accounts. Let them drill down further (for example, service → instance type).

Parameter Controls

Set parameters for budget thresholds or custom usage scenarios (like actual versus planned spend). Use conditional formatting to highlight cost overruns.

Email Summaries and Alerts

I always recommend scheduling email reports so stakeholders remain informed:

  • Email Summaries: Set up routine PDF or link-based reports that land in inboxes.
  • Alerts on Threshold Breaches: QuickSight can trigger notifications when certain cost thresholds are passed (for example, $50,000 a month). This keeps your team in the loop.

Essential Dashboard Templates

Well-structured dashboards turn data into practical insights. I often start with the following:

1. Cost and Usage Overview

Purpose: A high-level summary for senior management.
Key Widgets:

  • Total Monthly Cost (by month/year)
  • Top 5 Services by Cost
  • Regional Cost Distribution
  • Daily Spend Trends

2. Resource Usage Focus

Purpose: For engineering teams to find wasted resources.

Key Widgets:

  • Instance Usage Rates
  • Low-Usage Resources
  • Usage by Environment Tag (dev, test, prod)
  • Spots for RI or Savings Plan coverage

3. Savings Plans and RI Analysis

Purpose: For FinOps teams to track coverage and usage.

Key Widgets:

  • Coverage Percentage (RI or Savings Plans)
  • Usage of Prepaid Commitments
  • Potential Gains from Additional Coverage
  • Renewal Alerts

4. Anomaly Detection

Purpose: Spot unexpected cost spikes or usage changes.

Key Widgets:

  • Daily Cost Over Time
  • Service-Level Alerts
  • Account-Level Comparison
  • Custom Thresholds for Rapid Response

AWS Well-Architected CID

The AWS Well-Architected Labs offer dashboards ready to go. They give you templates for:

  • Service-by-service cost analysis
  • Tag-based allocation
  • Special business metrics

For the full rollout, check the Cloud Intelligence Dashboards Guide, which explains the steps in detail.

Advanced Cost Analysis Patterns

After you’ve built fundamental reports, you may want deeper analytics, such as forecasting, anomaly detection, or your own business KPIs.

Forecasting with QuickSight ML

Steps:

  • Train QuickSight’s built-in forecasting on daily or monthly costs.
  • Add a forecast visual with confidence ranges.
  • Set parameters to see the impact of hypothetical usage surges.

Anomaly Detection

How to Set It Up:

  • Switch on QuickSight’s ML Insights for time-series data.
  • Pick a variance threshold (like 15% above average).
  • Create email or SNS alerts if spend jumps unexpectedly.

Custom Metrics

Basic CUR fields might not match your exact metrics. So define your own:

  • Cost per Transaction

    sum({unblended_cost}) / countDistinct({transaction_id})
  • Cost per Unit (e.g. GB of storage)

    sum({unblended_cost}) / sum({usage_quantity})

Include tags or cost categories to break these metrics down by team or product.

Automated Cost Allocation

Steps:

  1. Use Cost Categories for grouping or departmental chargeback.
  2. Combine them with Athena views to create a monthly cost breakdown.
  3. Schedule QuickSight to share monthly allocation reports with each team.

Integration and Automation

Smooth cost management often depends on linking CUR data to other systems and automating everyday tasks.

Automated Refresh Pipelines

Daily Pipeline Example:

  1. S3 receives CUR updates.
  2. AWS Glue crawlers refresh tables.
  3. Athena views update automatically.
  4. QuickSight data refreshes on a schedule.

Multi-Account Environments

AWS Organisations:

  • Consolidate billing.
  • Analyse cost across linked accounts using the linked_account_id.
  • Group costs by business function or department with AWS Cost Categories.

Custom ETL

For advanced needs, you might create a custom ETL pipeline:

  • AWS Glue or Spark on EMR for heavier transformations.
  • Step Functions to orchestrate multi-step tasks, such as partition updates, data cleaning, or KPI calculations.

Automated Reporting

  • Scheduled QuickSight Emails: Monthly or weekly cost reports go straight to relevant teams.
  • APIs: Integrate with external tools (Tableau, Power BI, or custom dashboards).
  • Notifications: Combine with Amazon SNS to let folks know about significant cost updates.

Best Practices and Pitfalls

Best Practices

  1. Format CUR as Parquet and partition by date.
  2. Use Athena views (like CUDOS) for simpler queries and aggregated results.
  3. Cross-Check with AWS Cost Explorer for data consistency.
  4. Tag everything, and confirm tags are accurate.

Pitfalls to Avoid

  • Ignoring the massive detail in CUR. You may need aggregated views to keep queries from bogging down.
  • Skipping tags. Without consistent tagging, you lose clarity on cost ownership.
  • Relying on real-time queries with CUR. Remember there can be a 24-hour delay.
  • Overspending on Athena. Unoptimised queries can get pricey.
  • Forgetting about access controls. Your cost data should stay restricted to those who truly need it.

Success Checklist

  • ✅ CUR in Parquet, updated daily
  • ✅ Data partitions by month/year
  • ✅ Athena queries tested for performance
  • ✅ Frequent reviews of tags and cost categories
  • ✅ Secure QuickSight dashboards with IAM

Wrapping Up and Next Steps

AWS CUR is your friend when you want clarity on your cloud spend. In Part 1, I talked about getting started. Here, I’ve guided you through advanced analytics, interactive dashboards, and hands-off workflows.

Some ways to get moving:

  • Experiment with a single dashboard or Athena view.
  • Start exploring the CUDOS framework for in-depth reporting.
  • Develop a regular practice of reviewing your cost data for anomalies or coverage issues.
  • Expand your setup gradually, introducing new dashboards or ETL pipelines as your comfort and scale grow.

I’ve seen how these methods give teams the insights to shape strong cost strategies. I’d love to hear if you put any of these ideas into action. Feel free to reach out or drop a comment with your questions or suggestions.

Good luck with your CUR deep dive!

Series Navigation<< Breaking Down AWS Cost and Usage Reports (CUR): A Step-by-Step Guide – Part 1

Discover more from Vinay Sastry

Subscribe to get the latest posts sent to your email.

  • December 16, 2024
0
Would love your thoughts, please comment.x
()
x