Azure Cost Forensics

Transform enrollment-scale billing CSV files into auditable service-family datasets, then uncover conventional and non-obvious cost-reduction opportunities.

Operating Principles

• Work out of core. Prefer DuckDB SQL over loading the full CSV into memory.
• Treat the source export as immutable. Never edit, truncate, normalize in place, or overwrite it.
• Preserve evidence. Every finding must trace back to source columns, filters, groupings, and calculations.
• Distinguish observed facts from hypotheses. Billing data shows charged usage, not CPU, memory, IOPS, or application intent.
• Never claim a resource is idle, oversized, or safe to delete from billing data alone. State the telemetry or owner confirmation needed.
• Zero cost never proves absence of usage. Test existence and runtime with Quantity, never with Cost.
• Report costs in the source currency and billing period. Do not mix currencies or amortized and actual cost views.
• Do not expose customer names, subscription IDs, resource IDs, tags, or cost data outside the local workspace.

Inputs

• Source export: required. One CSV, compressed CSV, or a directory of compatible parts.
• Output directory: optional, default output/.
• Billing period and currency: optional, detect from the data when omitted.
• Focus areas: optional, run the full analysis when omitted.

Ask only for the source path when it is missing. Inspect the data before asking anything else.

A usage-only export cannot show purchases, unused benefits, or amortized cost. When an amortized or full cost export is also available, request it, because it enables commitment coverage and unused-benefit analysis that a usage-only file cannot support.

Bundled Scripts

scripts/ holds a proven DuckDB pipeline validated against a 16 GB, 9.5M-row EA export. Reuse and adapt it rather than writing a new pipeline. Configure it with environment variables:

export COST_EXPORT_CSV=./Detail_Enrollment_<id>_<period>_en.csv
export COST_OUTPUT_DIR=./output
python scripts/01_convert.py      # CSV -> normalized.parquet, control totals
python scripts/02_profile.py      # schema, null rates, distinct counts, coverage
python scripts/02b_taxonomy.py    # meter taxonomy discovery
python scripts/02c_storage_aks.py # storage sub-taxonomy, AKS + managed-RG envelopes
python scripts/03_classify.py     # mutually exclusive service families
python scripts/04_baselines.py    # summaries by subscription, resource, meter, region
python scripts/05_deep_infra.py   # disks, compute, storage requests, network, AKS
python scripts/06_deep_services.py # databases, analytics, AI, devtools, ER, monitoring
python scripts/10a_tag_discovery.py # allocation tag key discovery (run before 07/08)
python scripts/07_anomalies.py    # trends, step changes, robust outliers
python scripts/08_sizing.py       # opportunity sizing for the findings register
python scripts/08b_orphan_disks.py # orphan candidates tested on hours, never cost
python scripts/13_quality_gates.py # machine-checked gates; exits non-zero on failure

scripts/03_classify.py is the auditable core. Its rule precedence is deliberate: marketplace first, then AKS on hard evidence, then managed disks before the generic storage rule, then meter-category and consumed-service fallbacks. Preserve that ordering and extend the category maps rather than rewriting them, so family totals stay comparable across billing periods.

scripts/_conn.py holds the definitions that must exist in exactly one place, because each has previously been restated in a downstream script and silently produced wrong numbers:

• PLATFORM_MANAGED_RG — managed-resource-group patterns per platform. A prefix that is too narrow truncates the envelope without error; databricks-rg-% misses databricks-new-rg-%.
• period_bounds(con) — comparison windows derived from min/max(UsageDate). A literal date mislabels every other billing period.
• discover_allocation_keys() / primary_allocation_key() — ranks allocation-tag candidates from _tag_keys.json by intent and cost coverage. Never assume a key name.
• tag_value_sql(key) — exact-key extraction, so squad cannot match squad_old.

Verify before reuse:

• _conn.py declares a 50-column EA layout. Confirm the real header first and adjust when the export differs. This fails loudly rather than silently.
• Run 10a_tag_discovery.py before 07 and 08; both resolve the allocation key from its output and will stop if it is missing.
• Scripts that generate report narrative, manifests, or finding lists are deliberately not bundled, because they carry conclusions specific to one billing period. Generate those per run.
• Earlier versions of 07_anomalies.py and 08_sizing.py embedded one estate's resource names, subscription names, and allocation tag key, and one deliverable's findings written as SQL. They are not bundled. Never reintroduce a literal date, resource name, subscription name, currency, or tag key into these scripts.

Workflow

1. Inspect and Profile

Confirm the input path, output directory, file size, delimiter, encoding, row estimate, and free disk. Read only the header and a sample first, and detect the schema instead of assuming Azure column names. Map billing date and period, subscription, resource identity, service and meter taxonomy, quantity, effective price, unit price, retail price, cost, currency, charge type, pricing model, benefit and product-order identifiers, service metadata, cost center, and invoice section.

Profile null rates, distinct counts, date coverage, currencies, charge types, pricing models, and total cost. Reject any column as a grouping dimension when it is constant or near-constant, and record it as unusable rather than trusting its name.

Declare the export scope: usage-only versus one including purchases, refunds, taxes, credits, unused-benefit, and rounding-adjustment rows, and whether cost is actual or amortized. When purchase rows are absent, state that the export understates true spend and that commitment findings cannot be validated.

Reconcile the profiled total against the raw cost column before continuing, and record malformed or rejected rows. Budget roughly 20% of source size in output, and do not silently fall back to an in-memory full-file load.

2. Normalize and Classify

Build a reproducible SQL classification layer producing mutually exclusive families with an unclassified catch-all so no spend disappears. See classification for the required families and rules.

3. Materialize Analysis Datasets

Write one Parquet dataset per family plus CSV summaries:

output/
  manifest.json
  data_quality.csv
  enrollment_summary.csv
  subscription_summary.csv
  resource_summary.csv
  resource_group_summary.csv
  region_summary.csv
  meter_summary.csv
  business_area_summary.csv
  tag_coverage.csv
  families/*.parquet
  findings/
    quick_wins.csv
    deep_opportunities.csv
    challenges.csv
    anomalies.csv
    validation_requests.csv
  queries/
  report.md

The manifest must record source path and size, run timestamp, date range, currencies, row counts, source and classified totals, output files, schema mapping, classification rules, and tool versions. The sum of all mutually exclusive families must equal the reconciled source total within a documented rounding tolerance.

4. Analyze Cost Allocation Tags

Determine how much spend is attributable to a business area and how much is not. Never assume a tag key exists; discover the taxonomy present in this export.

Parse the tag column into key/value pairs, handling JSON objects, quoted lists, blanks, and nulls without dropping rows. Rank every key by charged cost and distinct value count. Identify allocation keys by intent rather than fixed name, covering cost-center, business-unit, department, squad, product, application, owner, project, and environment semantics, including abbreviations and non-English spellings. Include non-tag dimensions such as cost center, invoice section, department, subscription, and resource group. Lowercase and trim keys and collapse value variants before grouping, reporting every merged variant. Select the primary key by highest cost coverage and keep secondary keys for cross-tabulation.

Produce per business area: charged cost, share of enrollment spend, and resource count; allocation coverage as tagged, untagged, and percentage; untagged spend by subscription, resource group, and family so owners can be pursued; value hygiene problems; and business area cross-tabulated by family and environment.

Assign each billed row to exactly one business area per dimension so totals never double count, applying a documented precedence when tags compete. Treat ownership inferred from naming as a hypothesis requiring confirmation, never as tagged allocation.

5. Establish Baselines

Analyze cost and usage by day, subscription, resource group, resource, family, product, meter, region, pricing model, and tags. Compute totals, daily cost, cost per unit where units are comparable, share of spend, trend and step changes, day-of-week patterns, first and last charged dates, active charged days, concentration, and robust peer comparisons using medians, percentiles, and median absolute deviation. Never compare unlike meters, units, regions, SKUs, redundancy tiers, or currencies.

6. Find Quick Wins

Cover Advisor and Well-Architected-style opportunities where evidence supports them: commitment candidates with stable eligible usage, Hybrid Benefit and licensing review, orphan-candidate disks, snapshots, public IPs and gateways marked validation-required, excessive snapshot and backup retention, premium tiers with cheaper alternatives, avoidable egress, Log Analytics ingestion and retention, continuously charged dev/test resources, and stale-looking resources without asserting deletion safety.

7. Perform Deep Forensics

Search for cost drivers disproportionate to capacity, peers, or prior baseline. See deep forensics for cross-cutting techniques and family-specific analysis.

8. Challenge the Premises

Question the design itself, not only its configuration. See challenges.

9. Rank and Validate

For each finding record: ID and title; type as quick win, deep opportunity, or premise challenge; family and scope; observed evidence; current cost and period; savings as a range or not estimable from billing export; method and assumptions; confidence; effort and operational risk; required validation telemetry or owner question; a reproducible query reference; and affected subscriptions and resources using local identifiers only.

Rank by evidence-backed savings potential, confidence, effort, and risk. Avoid false precision. Separate gross opportunity from achievable savings, accounting for existing commitments, negotiated pricing, tier transition charges, and engineering effort.

10. Report and Reconcile

Write report.md with: executive summary; dataset scope and data-quality caveats; cost concentration and trends; cost allocation by business area; prioritized quick wins; prioritized deep opportunities; premise challenges; service-family findings; additional telemetry and owner questions; and methodology, assumptions, and reconciliation.

The executive summary must state allocation coverage alongside total spend. The allocation section must name the primary key, rank business areas by cost and share, show the largest untagged pockets with owners to confirm, and note tag hygiene that distorts the view. The challenges section must lead with the annualized cost of each assumption, state what would have to be true, and name the decision owner; never merge it into quick wins.

End with a control-total table proving source, normalized, family, and reported totals reconcile, including unclassified and excluded costs.

Focus Areas

Reconciliation, cost allocation, and premise challenges always run. A focus deepens an area rather than enabling it.

full, top-spend, anomalies, quick-wins, storage, storage-requests, aks, compute, disks, databases, data-analytics, openai-foundry, marketplace, devtools, network, expressroute, bandwidth, monitoring, backup, commitments, allocation, challenge, data-quality.

Quality Gates

Run scripts/13_quality_gates.py before presenting conclusions. It exits non-zero on failure so a broken run cannot quietly become a report. It checks source-to-family reconciliation, mutual exclusivity at the billed-line level, unclassified disclosure, currency and charge-type mixing, constant columns, credit handling, managed-RG envelope truncation, and missing days.

The gates it cannot automate, verify by hand:

• business-area allocation reconciles to the enrollment total, with tagged and untagged stated and no row counted twice
• price comparisons distinguish effective, unit, and retail price, and no cost-per-unit average spans a meter with wide unit-price variance
• no existence, runtime, or orphan test relies on cost being zero
• anomalies are not month-boundary, missing-day, or partial-day artifacts
• savings estimates identify their baseline and do not double count overlapping actions
• every high-priority finding has a reproducible query and evidence trail
• every utilization-dependent recommendation is labeled as requiring telemetry
• no figure, resource name, or narrative has been carried over from a previous billing period

Boundaries

• Do not modify Azure resources, reservations, budgets, policies, or billing settings.
• Do not upload customer data to web services or external APIs.
• Do not recommend deletion or downgrade without impact validation.
• Do not fabricate resource relationships from similar names alone.
• Do not stop after top-spend tables or generic Advisor recommendations.
• Do not limit findings to changes that are easy, incremental, or politically comfortable.
• Do not present a premise challenge without its switching cost, risk, and falsifying conditions.
• Do not bury unclassified spend or parse failures.
• Do not copy findings, totals, or narrative from a previous billing period.

Final Response

Summarize output location, reconciliation result, allocation coverage and leading business areas, highest-value quick wins, most important deep findings, premise challenges worth escalating, and unresolved validation needs. Link to the report and key files. State clearly which conclusions are billing-data facts and which remain hypotheses.