Cloud Billing Analysis | The Garnet Wiki

Cloud billing analysis is the process of extracting, transforming, and interpreting raw cloud cost data into actionable insights about usage, spend, and efficiency. It matters when a team must answer: *Why did costs rise in the last quarter?* or *Which services are underutilized but over-provisioned?* or *How much did a specific product or team contribute to the cloud budget?* This page details the actual steps and mechanics of analyzing cloud billing data, from ingestion to insight, grounded in real tools and workflows.

Understanding Cloud Billing Data

Cloud billing data typically arrives as a time-series of line items, each representing a resource or service consumed over a period. These line items are usually structured as records in a columnar format—CSV, Parquet, or a relational table—with fields like timestamp, resource_id, service, region, usage_type, quantity, unit, cost, and invoice_period.

The first step is acquiring this data. Most cloud providers expose billing data via APIs, but the most common entry point is a cloud storage bucket (e.g., AWS S3, GCP Cloud Storage, Azure Blob Storage), where billing files are written daily or hourly.


aws s3 ls s3://billing-data/2025/04/ --recursive --human-readable
# Output:
# 2025-04-01T00:00:00.000Z     12.3 MiB  billing-2025-04-01.csv.gz
# 2025-04-02T00:00:00.000Z     13.1 MiB  billing-2025-04-02.csv.gz
# ...

Each file is a compressed CSV with a consistent schema. The cost field is often total_cost, but some providers include rate, usage_cost, and tax as separate columns.

To load this into a data warehouse, teams use tools like dbt (data build tool), Airflow, or prefect. A typical pipeline extracts the files, unzips them, and loads into a staging table:

-- Create staging table for raw billing data
CREATE TABLE billing.raw_billing (
    invoice_period DATE,
    timestamp TIMESTAMP,
    resource_id TEXT,
    service TEXT,
    region TEXT,
    usage_type TEXT,
    quantity DOUBLE PRECISION,
    unit TEXT,
    total_cost NUMERIC,
    rate NUMERIC,
    usage_cost NUMERIC,
    tax NUMERIC
);

-- Load data using a script with `psql` and `COPY`
COPY billing.raw_billing FROM PROGRAM 'zcat -c billing-2025-04-01.csv.gz' WITH (FORMAT CSV, HEADER TRUE, DELIMITER ',', QUOTE '"');

The key failure mode here is missing or misaligned timestamps. If a billing file is generated at 00:00 UTC but the timestamp field in the CSV uses local time, or if the file is written before the day’s data is finalized, the invoice_period may not match the timestamp range. This causes cost overlap and misattribution.

Aggregating Costs by Dimension

Once data is in a warehouse, the next task is aggregating costs across dimensions. The most common dimensions are service, region, resource_id, tag, and application.

To compute total cost per service, a query like this is standard:

-- Total cost per cloud service, grouped by month
SELECT
    EXTRACT(YEAR FROM timestamp) AS year,
    EXTRACT(MONTH FROM timestamp) AS month,
    service,
    SUM(total_cost) AS monthly_cost
FROM billing.raw_billing
GROUP BY year, month, service
ORDER BY year, month, monthly_cost DESC;

A frequent mistake is using invoice_period instead of timestamp for time-based grouping. The invoice_period is when the bill is issued, but timestamp is when the usage occurred. If a service is billed quarterly but usage spans multiple months, grouping by invoice_period lumps costs from different usage windows into a single period.

For cost breakdowns by tag, the query must join the raw billing data with a tag table. Tags are often stored as a JSON column or a separate table:

-- Tags as JSON in the raw table
ALTER TABLE billing.raw_billing ADD COLUMN tags JSONB;

-- Update tags using a script
UPDATE billing.raw_billing
SET tags = jsonb_build_object(
    'team', 'backend',
    'project', 'user-profile',
    'env', 'prod'
)
WHERE service = 'EC2' AND region = 'us-west-2';

Then, to roll up costs by tags:

-- Cost per team and project, with tags
SELECT
    tags->>'team' AS team,
    tags->>'project' AS project,
    SUM(total_cost) AS total_cost
FROM billing.raw_billing
WHERE tags IS NOT NULL
GROUP BY team, project
ORDER BY total_cost DESC;

The sharp edge: tag hierarchy and inheritance. If a tag is applied at the resource level (e.g., EC2 instance), but the parent volume or VPC has a different tag, the cost may be attributed inconsistently. A common error is tagging only the root resource and not propagating tags to child resources, leading to undercounted or missing cost allocations.

Identifying Cost Anomalies

After aggregation, the next step is detecting deviations from expected behavior. Anomalies arise when a service’s cost spikes unexpectedly, or a region’s usage patterns shift.

A basic anomaly detection query compares current month’s cost to the previous three months' average:

-- Identify monthly cost anomalies
WITH monthly_avg AS (
    SELECT
        service,
        EXTRACT(YEAR FROM timestamp) AS year,
        EXTRACT(MONTH FROM timestamp) AS month,
        AVG(total_cost) AS avg_cost
    FROM billing.raw_billing
    WHERE timestamp >= '2025-01-01'
    GROUP BY service, year, month
),
current AS (
    SELECT
        service,
        EXTRACT(YEAR FROM timestamp) AS year,
        EXTRACT(MONTH FROM timestamp) AS month,
        SUM(total_cost) AS current_cost
    FROM billing.raw_billing
    WHERE timestamp >= '2025-04-01'
    GROUP BY service, year, month
)
SELECT
    c.service,
    c.month,
    c.current_cost,
    a.avg_cost,
    (c.current_cost - a.avg_cost) / a.avg_cost AS anomaly_ratio
FROM current c
JOIN monthly_avg a ON c.service = a.service AND c.year = a.year AND c.month = a.month
WHERE (c.current_cost - a.avg_cost) > 2 * a.avg_cost
ORDER BY anomaly_ratio DESC;

This reveals services with cost spikes. But the most common failure is not accounting for seasonality. A spike in RDS cost during a monthly data export is normal, but a spike in Lambda cost every Monday at 9 AM is not. The solution is to model expected cost per day of week and hour of day.

-- Expected cost by day of week and hour
SELECT
    EXTRACT(DOW FROM timestamp) AS day_of_week,
    EXTRACT(HOUR FROM timestamp) AS hour,
    AVG(total_cost) AS expected_cost
FROM billing.raw_billing
WHERE timestamp >= '2024-01-01' AND timestamp < '2025-04-01'
GROUP BY day_of_week, hour
ORDER BY day_of_week, hour;

Then, compare actual cost to expected cost:

-- Anomalies by day of week and hour
WITH expected AS (
    SELECT
        EXTRACT(DOW FROM timestamp) AS dow,
        EXTRACT(HOUR FROM timestamp) AS hour,
        AVG(total_cost) AS expected_cost
    FROM billing.raw_billing
    WHERE timestamp >= '2024-01-01'
    GROUP BY dow, hour
),
actual AS (
    SELECT
        EXTRACT(DOW FROM timestamp) AS dow,
        EXTRACT(HOUR FROM timestamp) AS hour,
        SUM(total_cost) AS actual_cost
    FROM billing.raw_billing
    WHERE timestamp >= '2025-04-01'
    GROUP BY dow, hour
)
SELECT
    a.dow,
    a.hour,
    a.actual_cost,
    e.expected_cost,
    (a.actual_cost - e.expected_cost) / e.expected_cost AS deviation_ratio
FROM actual a
JOIN expected e ON a.dow = e.dow AND a.hour = e.hour
WHERE (a.actual_cost - e.expected_cost) > 1.5 * e.expected_cost;

This reveals patterns like “Lambda cost spikes every Monday 9–11 AM across all teams” or “S3 costs double on Tuesdays due to backup jobs.”

Diagnosing Root Causes of Cost Increases

When a cost anomaly is identified, the next step is diagnosing its root cause. The most common approach is drilling down into the data by adding dimensions.

For example, a spike in EC2 cost is diagnosed by:

-- EC2 cost breakdown by instance type and region
SELECT
    service,
    region,
    instance_type,
    COUNT(*) AS instance_count,
    SUM(quantity) AS total_hours,
    SUM(total_cost) AS total_cost
FROM billing.raw_billing
WHERE service = 'EC2' AND timestamp >= '2025-04-01'
GROUP BY service, region, instance_type
ORDER BY total_cost DESC;

A failure mode is not including usage_type. An EC2 cost line item may have usage_type = 'OnDemand', usage_type = 'Reserved', or usage_type = 'Spot'. Each has different pricing structures. A cost increase could be due to more Spot instances, which are cheaper but less stable, or a shift from OnDemand to Reserved.

The next level of diagnosis is correlating with operational events. A common tool is kubectl for Kubernetes workloads. A team might query the cluster logs to see when a new deployment happened:

# Query Kubernetes events for a specific time window
kubectl get events --all-namespaces \
    --field-selector "timestamp>=2025-04-05T08:00:00Z,timestamp<=2025-04-05T10:00:00Z" \
    --sort-by=.metadata.creationTimestamp \
    --watch

Then, join these events with billing data:

-- Cost change correlated with deployment events
SELECT
    b.service,
    b.region,
    b.instance_type,
    b.timestamp,
    b.total_cost,
    e.type,
    e.reason,
    e.message
FROM billing.raw_billing b
JOIN (
    SELECT
        'Deployment' AS type,
        'NewRollout' AS reason,
        'Deployed user-service v2.1' AS message,
        '2025-04-05T09:15:00Z' AS timestamp
    UNION ALL
    SELECT
        'Scaling',
        'AutoScaling',
        'Increased replicas from 5 to 15',
        '2025-04-05T09:22:00Z'
) e ON b.timestamp >= e.timestamp - INTERVAL '5 minutes'
               AND b.timestamp <= e.timestamp + INTERVAL '10 minutes'
WHERE b.service = 'EC2'
ORDER BY b.timestamp;

This reveals that a 20% cost increase on April 5 was due to a deployment that triggered a 10-minute spike in EC2 usage, with a sudden increase in t3.large instances.

Tracking Cost Trends Over Time

Finally, cloud billing analysis is not a one-off task. It must be repeated and automated. A common pattern is building a cost dashboard that shows daily, weekly, and monthly trends.

The most effective way is to materialize a summary table that aggregates costs daily:

-- Daily cost summary table
CREATE TABLE billing.daily_cost_summary AS
SELECT
    DATE(timestamp) AS day,
    service,
    region,
    SUM(total_cost) AS daily_cost,
    SUM(quantity) AS daily_quantity
FROM billing.raw_billing
GROUP BY day, service, region;

This table is then used to generate time-series visualizations. Tools like Grafana, Metabase, or Superset pull data via SQL queries:

-- Query for a time-series chart of EC2 cost
SELECT
    DATE(timestamp) AS date,
    SUM(total_cost) AS ec2_cost
FROM billing.raw_billing
WHERE service = 'EC2'
GROUP BY date
ORDER BY date;

The key failure mode is delayed data ingestion. If the pipeline runs once per day but the billing files are generated at 03:00 UTC and the pipeline starts at 05:00 UTC, the previous day’s data is missing from the dashboard until the next run. To fix this, teams use incremental loads:

-- Incremental load: only new files since last run
SELECT
    b.invoice_period,
    b.timestamp,
    b.resource_id,
    b.service,
    b.region,
    b.total_cost
FROM billing.raw_billing b
WHERE b.invoice_period > (SELECT MAX(invoice_period) FROM billing.daily_cost_summary)

This ensures the dashboard reflects the latest data within hours.

Common Pitfalls and Silent Failures

Misaligned billing periods: invoice_period vs timestamp leads to cost overlap and misattribution.
Tagging only root resources: child resources (e.g., EBS volumes, RDS instances) inherit parent tags but are not explicitly tagged, causing cost drift.
Not accounting for seasonality: spikes due to predictable events (e.g., monthly reports) are mistaken for anomalies.
Missing usage_type: cost increases from Spot instances are overlooked because usage_type is not included in aggregations.
Delayed or incomplete data: pipelines fail silently, but the dashboard shows incomplete data for days or weeks.
Over-aggregation: summing costs without grouping by service, region, and usage_type leads to misleading totals.

In conclusion, cloud billing analysis is the foundational practice of turning raw cost data into insight. It requires careful handling of time, dimensions, and data lineage. The process is not about reporting—it is about understanding, diagnosing, and acting on cloud spend.

This page was rewritten on 18 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.