Breaking Down AWS Cost and Usage Reports (CUR) A Step-by-Step Guide – Part 2
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
- In QuickSight, create a New Dataset.
- Select Athena and link it to your CUR database.
- 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_cost → Unblended 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:
- Use Cost Categories for grouping or departmental chargeback.
- Combine them with Athena views to create a monthly cost breakdown.
- 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:
- S3 receives CUR updates.
- AWS Glue crawlers refresh tables.
- Athena views update automatically.
- 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
- Format CUR as Parquet and partition by date.
- Use Athena views (like CUDOS) for simpler queries and aggregated results.
- Cross-Check with AWS Cost Explorer for data consistency.
- 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!
Discover more from Vinay Sastry
Subscribe to get the latest posts sent to your email.