FinOps Analytics — Full Reference

Quper's FinOps module is built entirely on the FOCUS v1.2 (FinOps Open Cost and Usage Specification) data model — the vendor-neutral open standard for cloud cost and usage data. All cost calculations, aggregations, and optimization signals derive from FOCUS-normalized columns, ensuring consistent semantics across cloud providers and billing systems.

Data Architecture — Silver → Gold Pipeline

Analytics flow through a two-layer warehouse architecture before reaching any API endpoint:

FOCUS Column Mapping (Key Fields)

Silver Ingestion Algorithm

1. 1
   Pull from Athena in batches

   Reads the FOCUS-normalised Athena table in batches of 1,000 rows. Column names are normalised case-insensitively — underscores, hyphens, and spaces are treated as equivalent to handle minor schema drift across cloud providers or export tools.

2. 2
   Map 70+ FOCUS columns

   All cost, usage, commitment, capacity reservation, pricing, resource, service, and tag columns are written verbatim. Tags and pricing detail fields are stored as JSON blobs.

3. 3
   Write with provenance metadata

   Each batch is stamped with a batch_id (UUID), loaded_at timestamp, and source_file reference. An optional dev-DB mirror writes in parallel for testing without touching production data.

Gold Aggregation Algorithm

1. 1
   Group by resource + day

   Silver rows are grouped by billing_account_id, provider_name, invoice_id, region_name, service_name, charge_category, resource_id, and charge_period_start truncated to date.

2. 2
   Extract workload ID from ARN

   When resource_id follows the AWS ARN pattern (arn:partition:service:region:account:resource), workload_id is the first path segment after the final slash, falling back to the seventh colon-delimited segment.

3. 3
   Determine cost_type with commitment priority

   If any row in the group carries a non-null commitment_discount_type (e.g. Reserved Instance, Savings Plan), that value wins. Otherwise: charge_category Tax → Tax, Credit → Credit, Refund → Refund, all else → OnDemand.

4. 4
   Aggregate cost and usage

   cost_amount = SUM(effective_cost). amortized_cost = SUM(effective_cost). list_cost = SUM(list_cost). usage_quantity = SUM(consumed_quantity). All aggregations COALESCE null to 0.

5. 5
   Tag governance flag

   required_tag_missing_flag = TRUE when the aggregated tags JSON is null, empty string, '{}', or the literal string 'null'. No custom tag schema is required.

6. 6
   Initialise idle fields

   idle_flag = FALSE and idle_cost = 0 as defaults. These are back-filled by the Idle Spend enrichment job after the gold table is populated.

Story 1 — Unit Cost & Efficiency

Unit cost measures what each unit of consumed resource costs. Efficiency measures whether cost is growing faster or slower than usage — the ratio of the two growth rates.

Computation Steps

1. 1
   Build current-period CTE

   Group the gold table by the requested dimension over the current date window. Sum cost_amount or amortized_cost depending on the cost_basis parameter.

2. 2
   Build previous-period CTE

   Identical grouping over the preceding equivalent window. Both windows are always the same number of days for a fair comparison.

3. 3
   FULL OUTER JOIN on group key

   Ensures resources present in only one period are still included — current-only items show zero previous cost; previous-only items show zero current cost.

4. 4
   Apply formulas and rank

   Unit cost, growth percentages, and efficiency ratio are computed per row. Results are ordered by current_cost descending.

Cost Basis Options

Story 2 — Unit Cost Time-Series

The time-series variant computes unit cost and efficiency for every day in the requested window, enabling trend charts. It uses SQL window functions rather than self-joins to avoid a full table scan per day.

1. 1
   Daily aggregation

   Group the gold table by usage_date and the chosen dimension to produce daily total_cost and total_usage per group key.

2. 2
   LAG window function

   LAG(total_cost) OVER (PARTITION BY group_key ORDER BY usage_date) retrieves the previous day's value within the same group in a single sorted pass. Applied identically for usage.

3. 3
   Per-day formula application

   Unit cost, cost_growth_pct, usage_growth_pct, and efficiency_ratio are computed from the current and lagged values using the same formulas as Story 1. Days with no prior day have NULL growth values.

Story 3 — Period Comparison

Compares two arbitrary time windows of equal length and surfaces absolute and relative deltas per group. Two comparison modes:

Resources appearing only in the current period carry has_previous_data = false. Resources that existed only in the prior period appear with current_cost = 0 and a fully negative delta, surfacing deleted or decommissioned workloads.

Story 4 — Optimization Indicators

Four independent indicators, each targeting a distinct waste category. Thresholds are read from qup_config_idle_thresholds at query time — changing a threshold takes effect immediately without a redeployment.

4a — Idle Spend

The gold table's idle_flag and idle_cost are pre-computed by a background enrichment job using the idle_cpu_threshold and idle_query_threshold config values. The endpoint aggregates them:

4b — Overprovisioning

A resource group is over-provisioned only when all three performance dimensions are simultaneously below threshold. The SQL aggregates mean values; the flag is applied in Python after the query:

overprovision_flag = cpu_ok AND queue_ok AND latency_ok— all three must hold. Low CPU with high queue time signals I/O saturation, not over-provisioning, and is not flagged.

4c — Usage Concentration (Top-N Risk)

1. 1
   Compute per-group usage share

   usage_pct = group_usage ÷ total_usage × 100, computed against a grand-total CTE to avoid correlated subqueries.

2. 2
   Rank by usage descending

   ROW_NUMBER() OVER (ORDER BY usage_quantity DESC) assigns rank 1 to the largest consumer.

3. 3
   Compute top-N concentration

   top_n_pct = SUM(usage_pct WHERE rank ≤ concentration_top_n). Default top_n = 3. A high percentage (e.g. > 80 %) signals a concentration risk where one team or service dominates cloud spend.

Story 5 — Baseline & Z-Score Anomaly Detection

Anomaly detection uses a rolling statistical baseline to distinguish genuine cost spikes from normal variation. The baseline window deliberately excludes the two most recent days so that the current anomaly cannot contaminate its own reference period.

1. 1
   Daily aggregation

   Group gold table by usage_date and chosen dimension to produce daily_cost and daily_usage per group key.

2. 2
   Rolling window function

   SQL window: ROWS BETWEEN 16 PRECEDING AND 3 PRECEDING, partitioned by group_key, ordered by usage_date. AVG() and STDDEV() are computed within this window in a single sorted pass.

3. 3
   Insufficient-data guard

   Baseline mean and stddev are emitted as NULL when window_size < 7. This avoids anomaly flags when fewer than a week of history exists.

4. 4
   Compute Z-score

   Z = (daily_value − baseline_mean) ÷ stddev, computed independently for cost and usage. NULL when stddev = 0 (no variation in the baseline window).

5. 5
   Flag anomalies

   is_anomaly = |Z| > z_threshold. Default z_threshold = 2.0 (≈ 95th percentile under a normal distribution). Configurable 0.5 – 5.0. A threshold of 2.0 means a day must sit more than two standard deviations from the 14-day mean to be flagged.

Story 6 — Stability & Volatility

Volatility measures how erratically cost or usage moves over a rolling window. The metric is the Coefficient of Variation (CV) — a normalised, unitless measure of dispersion that is comparable across resources with very different cost scales.

Story 7 — Advanced Optimization Metrics

7a — Idle Days

Uses the pre-computed idle_flag column in the gold daily table to count days of zero meaningful activity and the cost incurred during those days.

7b — Zero-Query Cost Days

Identifies Redshift cluster-days where compute cost was incurred but zero queries were processed — the strongest signal for completely unused capacity.

7c — Off-Hours Spend

Computed from the silver table because hourly granularity (charge_period_start hour) is only available at that layer. The gold table aggregates to day, losing intra-day hour information.

7d — Underutilised Clusters

Distinguishes under-used clusters (low but non-zero CPU) from idle clusters (zero CPU). Only cluster-days with a measured CPU reading above zero are considered.

Configuration Thresholds

API Endpoints

Grouping Dimensions

Standard Response Envelope

{
  "success": true,
  "data": [ ... ],
  "meta": {
    "period": { "from": "2024-01-01", "to": "2024-01-31" },
    "group_by": "service",
    "cost_basis": "amortized",
    "row_count": 24
  }
}

See API Overview for authentication details and the error response format.