Multi-Cloud Accounting

import pandas as pd
from datetime import datetime
 
def transform_aws_cur_to_focus(cur_df: pd.DataFrame) -> pd.DataFrame:
    """
    Transform AWS CUR columns to FOCUS 1.1 schema.
    Handles the most common line item types.
    """
    focus = pd.DataFrame()
 
    focus["BillingAccountId"]   = cur_df["bill_payer_account_id"]
    focus["BillingAccountName"] = cur_df["bill_payer_account_id"]  # enrich from Org API
    focus["SubAccountId"]       = cur_df["line_item_usage_account_id"]
    focus["Provider"]           = "AWS"
    focus["InvoiceIssuerName"]  = "Amazon Web Services"
 
    focus["ServiceName"]        = cur_df["product_product_name"]
    focus["ServiceCategory"]    = cur_df["product_product_family"].map({
        "Compute Instance": "Compute",
        "Storage":          "Storage",
        "Database":         "Databases",
        "Data Transfer":    "Networking",
    }).fillna("Other")
 
    focus["ResourceId"]         = cur_df["line_item_resource_id"]
    focus["ResourceName"]       = cur_df["line_item_resource_id"]
    focus["Region"]             = cur_df["product_region_code"]
 
    focus["ChargeType"] = cur_df["line_item_line_item_type"].map({
        "Usage":                     "Usage",
        "Fee":                       "Purchase",
        "Credit":                    "Adjustment",
        "Refund":                    "Adjustment",
        "Tax":                       "Tax",
        "SavingsPlanCoveredUsage":   "Usage",
        "SavingsPlanRecurringFee":   "Purchase",
        "RIFee":                     "Purchase",
    }).fillna("Usage")
 
    focus["ChargeCategory"] = "Cloud"
 
    # Amortized cost is the FOCUS EffectiveCost
    focus["EffectiveCost"] = cur_df.apply(lambda r: (
        r.get("savings_plan_effective_cost", 0)
        if r.get("line_item_line_item_type") == "SavingsPlanCoveredUsage"
        else r.get("reservation_effective_cost", 0)
        if r.get("line_item_line_item_type") == "DiscountedUsage"
        else r.get("line_item_unblended_cost", 0)
    ), axis=1)
 
    focus["ListCost"]          = cur_df.get("pricing_public_on_demand_cost", focus["EffectiveCost"])
    focus["BilledCost"]        = cur_df["line_item_unblended_cost"]
    focus["UsageQuantity"]     = cur_df["line_item_usage_amount"]
    focus["UsageUnit"]         = cur_df["pricing_unit"]
 
    focus["ChargePeriodStart"] = pd.to_datetime(cur_df["line_item_usage_start_date"])
    focus["ChargePeriodEnd"]   = pd.to_datetime(cur_df["line_item_usage_end_date"])
 
    # Propagate cost allocation tags
    for tag_col in [c for c in cur_df.columns if c.startswith("resource_tags_user_")]:
        focus_tag = tag_col.replace("resource_tags_user_", "x_aws_tag_")
        focus[focus_tag] = cur_df[tag_col]
 
    return focus

-- Monthly spend by provider and service category
SELECT
  Provider,
  ServiceCategory,
  DATE_TRUNC('month', ChargePeriodStart)  AS month,
  ROUND(SUM(EffectiveCost), 0)            AS effective_cost_usd,
  ROUND(SUM(ListCost), 0)                 AS list_cost_usd,
  ROUND(
    100.0 * (1 - SUM(EffectiveCost) / NULLIF(SUM(ListCost), 0))
  , 1)                                    AS effective_discount_pct
FROM unified_billing.focus_v1
WHERE ChargePeriodStart >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '3' MONTH)
  AND ChargeType = 'Usage'
GROUP BY 1, 2, 3
ORDER BY month DESC, effective_cost_usd DESC;

resource "aws_cur_report_definition" "focus" {
  report_name                = "focus-v1-export"
  time_unit                  = "HOURLY"
  format                     = "Parquet"
  compression                = "Parquet"
  additional_schema_elements = ["RESOURCES", "SPLIT_COST_ALLOCATION_DATA"]
  s3_bucket                  = aws_s3_bucket.cur.bucket
  s3_region                  = var.region
  s3_prefix                  = "focus/"
  report_versioning          = "OVERWRITE_REPORT"
  additional_artifacts       = []
 
  # Enable FOCUS format (CUR v2)
  # Set via console toggle "Enable FOCUS-compatible export" — Terraform support varies by provider version
}