ADR-002: Cross-Validation Strategy - CSV Ground Truth Comparison

Status: ✅ Accepted Date: 2026-01-15 Deciders: @cloud-architect, @product-owner Related: ADR-001 (Cost Metric Selection)

---

Context

Problem: Need automated validation to ensure Cost Explorer API accuracy ≥99.5% vs AWS Console billing data

Current State: - Manual comparison between API results and AWS Console exports - Time-consuming (2 hours/validation) - Error-prone (spreadsheet copy-paste mistakes) - No statistical analysis of variance - No audit trail for SOC2 Type II compliance

Accuracy Target: ≥99.5% (ADLC Constitutional Principle V - Observability)

Stakeholder Requirements: - Finance Team: Confidence in automated cost data for chargeback - Audit/Compliance: SOC2 Type II requires documented cost control accuracy - Engineering Team: Continuous monitoring of API data quality

Evidence Required: - Accuracy percentage vs ground truth - Per-service variance analysis (which services contribute to error) - Statistical summary (mean, median, standard deviation) - Audit trail (timestamp, input data, calculation methodology)

---

Decision

Create scripts/cross_validate_costs.py - Automated cross-validation script comparing CSV ground truth vs Cost Explorer API responses

Design

Inputs: 1. CSV Ground Truth: AWS Console billing export (manual, one-time per validation period) - Format: AWS Cost and Usage Report (CUR) format - Columns: Service, Account, Cost (UnblendedCost) - Source: AWS Console → Billing → Cost Explorer → Download CSV 2. Cost Explorer API Response: rb_fo_001.py output (automated) - Format: JSON from boto3 ce.get_cost_and_usage() - Metric: UnblendedCost (per ADR-001) - Granularity: Daily (aggregated to monthly for comparison)

Processing: 1. Load CSV ground truth into pandas DataFrame 2. Parse Cost Explorer API JSON response 3. Aggregate both datasets to service-level monthly totals 4. Calculate variance: variance = (api_amount - csv_amount) / csv_amount * 100 5. Calculate overall accuracy: accuracy = 100 - abs(total_variance) 6. Generate per-service variance table 7. Compute statistical summary (mean, median, std dev, min, max)

Outputs: 1. JSON Evidence Report: tmp/cloud-infrastructure/finops/cross-validation/validation-report-YYYY-MM-DD.json

{
  "timestamp": "ISO8601",
  "period": "2025-12",
  "accuracy_pct": 99.7,
  "total_csv": 145784.56,
  "total_api": 142272.62,
  "total_variance_pct": -2.4,
  "per_service_variance": [...],
  "statistical_summary": {...},
  "validation_status": "PASS"
}

2. Markdown Summary: tmp/cloud-infrastructure/finops/cross-validation/validation-summary-YYYY-MM-DD.md 3. CSV Per-Service Variance: tmp/cloud-infrastructure/finops/cross-validation/service-variance-YYYY-MM-DD.csv

Validation Formula:

accuracy = 100 - (abs(api_total - csv_total) / csv_total * 100)
# Target: accuracy >= 99.5%

Statistical Analysis: - Mean Variance: Average per-service variance - Median Variance: Middle value (less sensitive to outliers) - Standard Deviation: Variance spread (consistency measure) - Min/Max Variance: Best and worst performing services

---

Consequences

Positive

✅ Automated Validation: 2 hours → 5 minutes (96% time reduction) - No manual spreadsheet comparison - Reproducible results - Scheduled execution (monthly validation)

✅ Per-Service Variance Analysis: Identifies accuracy issues by service - Example: "EC2: +0.2%, RDS: -1.5%, S3: +0.1%" - Pinpoints which services contribute to total variance - Enables targeted investigation of outliers

✅ Evidence Reports for SOC2 Audit Trail: - Timestamped validation records - Documented methodology (formula, inputs, outputs) - Statistical confidence (mean, median, std dev) - Audit-ready JSON + Markdown + CSV artifacts

✅ Continuous Monitoring Capability: - Run validation monthly (automated) - Detect API accuracy degradation early - Alert if accuracy drops below 99.5% threshold - Historical trend analysis (accuracy over time)

✅ Statistical Confidence: - Mean variance measures central tendency - Standard deviation quantifies consistency - Outlier detection (services >5% variance)

Negative

⚠️ Manual CSV Export Required (one-time per validation period): - Finance team must export CSV from AWS Console - 15-minute overhead per validation - Manual step in otherwise automated workflow

⚠️ Script Maintenance Overhead: - Requires updates if AWS CSV format changes - Pandas dependency management - Error handling for edge cases (missing services, zero costs)

Risks

Risk	Probability	Impact	Mitigation
CSV export format changes	Low	Medium	Flexible CSV parsing with schema validation, log errors with clear messages
Performance bottleneck for large datasets	Low	Low	Pandas optimization (vectorized operations), caching API responses, sampling strategy (validate 10% of services, extrapolate accuracy)
CSV export human error (wrong month)	Medium	Medium	Script validates CSV period matches API period, logs warning if mismatch detected
API response caching staleness	Low	Low	1-hour TTL for API response cache, validate cache timestamp before comparison

---

Alternatives Considered

Alternative 1: AWS Budgets API Comparison

Description: Compare Cost Explorer API with AWS Budgets API as ground truth

Pros: - Fully automated (no manual CSV export) - AWS native comparison (both AWS APIs)

Cons: - Budgets API uses forecasts, not actuals (not suitable as ground truth) - Budgets may not reflect actual invoice amounts - Limited granularity (monthly only, no daily breakdown)

Decision: REJECTED (Budgets API not suitable as ground truth)

---

Alternative 2: CloudHealth/Cloudability Third-Party Validation

Description: Use CloudHealth or Cloudability as independent third-party validation source

Pros: - Independent validation (not AWS native) - Multi-cloud cost aggregation capability - Pre-built dashboards and reports

Cons: - Cost: $5K-$10K/year (not justified for one-time validation) - Additional vendor relationship and integration overhead - May introduce its own accuracy variance

Decision: REJECTED (cost not justified, one-time validation use case)

---

Alternative 3: Manual Spreadsheet Comparison

Description: Continue current process (manual CSV vs API comparison in Excel)

Pros: - No script development required - Finance team familiar with process

Cons: - Error-prone (copy-paste mistakes, formula errors) - Time-consuming (2 hours per validation) - Not scalable (50+ accounts) - No audit trail (Excel file can be modified without history) - No statistical analysis (manual calculations)

Decision: REJECTED (defeats automation goal, high error risk)

---

Alternative 4: AWS Cost and Usage Report (CUR) API

Description: Use CUR API instead of manual CSV export

Pros: - Fully automated (no manual CSV export) - AWS native API comparison

Cons: - CUR API requires S3 data lake setup (infrastructure overhead) - Athena queries required (additional complexity and cost) - Latency: CUR data available 24-48 hours after month-end (slower than Console export)

Decision: DEFERRED (future enhancement after Stage 1 validation, ADR-003 candidate)

---

References

• AWS Cost and Usage Report: CUR User Guide
• Cost Explorer API: GetCostAndUsage
• Pandas DataFrame: pandas.read_csv()
• FOCUS 1.3: Data Quality Validation
• SOC2 Type II: CC7.2 (Monitoring Activities)
• Related ADRs: ADR-001 (Cost Metric Selection)

---

Evidence

Cross-Validation Results

• December 2025 validation: tmp/cloud-infrastructure/finops/cross-validation/validation-report-2026-01-15.json
• Per-service variance analysis: tmp/cloud-infrastructure/finops/cross-validation/service-variance-2026-01-15.csv
• Statistical summary: tmp/cloud-infrastructure/finops/cross-validation/statistical-summary-2026-01-15.md

Coordination Evidence

• Product Owner approval: tmp/cloud-infrastructure/coordination-logs/product-owner-2026-01-15.json
• Cloud Architect approval: tmp/cloud-infrastructure/coordination-logs/cloud-architect-2026-01-15.json

---

Implementation Plan

Stage 1: Script Development (Cloud-Infrastructure) - 1-2 days

Step 1: Create Script Structure

# scripts/cross_validate_costs.py
import pandas as pd
import json
from pathlib import Path
from datetime import datetime

def load_csv_ground_truth(csv_path: str) -> pd.DataFrame:
    """Load AWS Console CSV export"""
    pass

def load_api_response(json_path: str) -> dict:
    """Load Cost Explorer API JSON response"""
    pass

def aggregate_to_service_level(df: pd.DataFrame) -> pd.DataFrame:
    """Aggregate costs by service (sum across accounts)"""
    pass

def calculate_variance(csv_df: pd.DataFrame, api_dict: dict) -> pd.DataFrame:
    """Calculate per-service variance"""
    pass

def calculate_accuracy(total_csv: float, total_api: float) -> float:
    """Calculate overall accuracy percentage"""
    return 100 - (abs(total_api - total_csv) / total_csv * 100)

def generate_evidence_report(results: dict, output_dir: str):
    """Generate JSON + Markdown + CSV evidence reports"""
    pass

def main():
    """Main execution"""
    pass

Step 2: Add Validation Logic

• Validate CSV period matches API period
• Check for missing services (in CSV but not in API, or vice versa)
• Handle zero-cost services (division by zero prevention)
• Log warnings for variance >5% (outlier detection)

Step 3: Statistical Analysis

• Calculate mean variance (central tendency)
• Calculate median variance (less sensitive to outliers)
• Calculate standard deviation (consistency measure)
• Identify min/max variance (best/worst services)

Step 4: Evidence Report Generation

• JSON report with all metrics
• Markdown summary with human-readable table
• CSV per-service variance export
• Timestamp all outputs (ISO8601 format)

Stage 2: Testing & Validation - 1 day

Test Cases

1. Happy Path: December 2025 CSV vs API (accuracy ≥99.5%)
2. Missing Service: Service in CSV but not in API (log warning)
3. Zero Cost Service: Service with $0 cost (skip variance calculation)
4. Period Mismatch: CSV is Dec 2025, API is Jan 2026 (error + exit)
5. High Variance Outlier: Service with >5% variance (log warning)

Validation Criteria

• Accuracy ≥99.5% (PASS/FAIL)
• Per-service variance ≤5% for top 10 services (PASS/FAIL)
• Script execution time ≤5 minutes (performance)
• Evidence reports generated with correct timestamps

Stage 3: Documentation - 1 day

User Guide

• Update docs/runbooks/rb-fo-001-cost-explorer-analysis.md
• Add "Cross-Validation" section with usage examples
• Document CSV export instructions (AWS Console steps)
• Explain accuracy threshold (≥99.5%) and interpretation

Troubleshooting

• "Accuracy <99.5%" → Investigate per-service variance, identify outliers
• "Period mismatch" → Ensure CSV and API cover same month
• "Missing service" → Check if service was introduced mid-month

---

Success Metrics

Metric	Target	Measurement
Accuracy	≥99.5%	100 - (abs(api_total - csv_total) / csv_total * 100)
Execution Time	≤5 minutes	Script start to evidence report generation
Manual Effort	≤15 minutes	CSV export + script execution
Audit Compliance	100%	SOC2 Type II auditor acceptance of evidence reports
Error Detection	100%	All variance >5% logged with warnings

---

Review Schedule

• 1 month (February 2026): Validate with January 2026 data (confirm accuracy maintained)
• 3 months (April 2026): Review script performance with Q1 2026 data (3 months)
• 6 months (July 2026): Evaluate automation opportunity (ADR-003: CUR API integration)

---

Future Enhancements (Post-Stage 1)

ADR-003 Candidate: CUR API Integration

Objective: Eliminate manual CSV export by querying CUR API directly

Benefits: - Fully automated (no human-in-loop for CSV export) - Historical validation (query any past month) - Scheduled monthly validation (no manual trigger)

Trade-offs: - Infrastructure setup (S3 data lake + Athena + Glue) - Additional AWS costs ($10-$20/month for S3 + Athena) - 24-48 hour CUR data latency (slower than Console export)

Decision: DEFERRED to ADR-003 (after Stage 1 validation proves script value)

---

Version: 1.0.0 ADLC Version: 3.1.0 Status: ✅ APPROVED - Ready for python-expert implementation