Most multicloud cost reports die in a spreadsheet. One engineer exports a CSV from AWS, another pulls a table from Azure, a third pastes in a BigQuery result, and the next day disappears into reconciling column names. This tutorial replaces that ritual with a repeatable FinOps pipeline. Provider exports in the FOCUS format land in object storage, DuckDB normalizes them, a short list of SQL checks guards the numbers, and a scheduled job publishes showback by team plus Kubernetes namespace allocation from OpenCost.
The spec doing the heavy lifting is FOCUS, the FinOps Open Cost and Usage Specification. Version 1.4 is the newest published release and the FinOps Foundation ratified it on June 4, 2026 (the AWS Builder Center runs a deep dive on FOCUS 1.4 and FinOps X 2026 if you want the AWS view of it). The catch is that providers have not caught up. Reporting from October 2026 puts AWS, Azure and Google Cloud exports at roughly the FOCUS 1.2 level, with Google Cloud’s BigQuery export still described as preview. Your pipeline has to absorb that gap, which is exactly why a thin normalization layer you own beats waiting for the clouds.
You will not need a paid platform for any of this. The native dashboards (see how AWS Cost Explorer, Azure Cost Management and Google Cloud Billing compare) stay useful for drill-downs, but none of them answers the cross-cloud question finance keeps asking. The lab below is about 250 lines of Python and SQL. It runs end to end in under two seconds on synthetic data, then you swap in real exports. I tested every script on Ubuntu with Python 3.12 and DuckDB 2.0.0 on October 24, 2026.
Don't miss new tech stories on Google
Add Tech Insider once in the Google app and our stories appear in your news suggestions.
Why FOCUS 1.4 Matters for Your FinOps Pipeline in 2026
Before FOCUS, every provider invented its own vocabulary. AWS called a line item one thing, Azure another, and Google Cloud a third, so a query that summed compute across clouds needed three different mappings and a person who remembered why. FOCUS defines a common schema for billing and usage data across AWS, Azure, Google Cloud and other technology sources. It is an open specification, licensed CC BY 4.0, maintained through the FinOps Foundation ecosystem. It is not a product, and it has no plans or prices.
Version 1.4 pushed the spec beyond basic consumption normalization. It added invoice reconciliation, richer commitment details and better billing-period analysis, the areas finance teams lean on most. The table below lists what the FinOps Foundation says the release contains.
| What FOCUS 1.4 added | Count or name | Why a pipeline cares |
|---|---|---|
| Ratification date | June 4, 2026 | Newest published release as of October 24, 2026 |
| New datasets | 2 (Invoice Detail, Billing Period) | Tie consumption rows to issued invoices and provider billing periods |
| New columns | 47 | More commitment and contract fields to preserve in your tables |
| New attributes | 6 | Extra metadata to carry through normalization |
| Glossary entries | 17 | Shared definitions, so a team argues about numbers instead of words |
| Supported features | 2, including invoice-reconciliation data | Lets you prove that your totals match the bill |
One caution keeps the rest of this guide honest: a FinOps Foundation GitHub issue dated October 13, 2026 says the community FOCUS Converters project carries stale AWS, Azure and GCP mappings and does not support current spec versions. That repository is deprioritized partly because the providers began shipping native FOCUS data. The practical reading: native exports are the right input, converters are not, and you should expect to own a small mapping layer yourself.
The urgency also comes from the budget side. Finance teams are pushing FinOps programs harder, and the pressure is visible in coverage of how CFOs are taming runaway cloud costs. The FinOps Foundation’s own write-up of its FinOps X 2026 Day 2 keynote, titled From Alerts to Agents, gives a sense of where the community’s attention sits this year. Dashboards that only alert are giving way to pipelines that feed automation, and a clean unified table is the prerequisite for both.
What You Will Build and What You Need
The finished project has five stages. Exports from each cloud arrive in object storage. DuckDB reads them directly and builds one focus_unified table in USD. A validation script gates the data. An analysis script produces provider, category, team, anomaly and Kubernetes views. A report script writes HTML and Parquet, and cron runs the chain nightly. Here is the target layout.
focus-lab/
gen_sample_data.py # Step 2: synthetic exports (skip once you have real ones)
fx.csv # Step 7: currency rates, owned by finance
build.py # Step 7: normalize into focus_unified
checks.py # Step 8: validation gate, non-zero exit on failure
analysis.py # Steps 9-12: showback, anomalies, Kubernetes allocation
report.py # Step 13: HTML and Parquet outputs
data/ # raw exports (synthetic or downloaded)
out/ # report.html, focus_unified.parquetPlan on about 75 minutes if you also wire up one real cloud account. The sample-data path alone takes 15 minutes. The table lists the versions I tested and why each tool is there.
| Tool | Version tested | Role in the pipeline |
|---|---|---|
| Python | 3.12.3 | Runs the build, check and report scripts |
| DuckDB | 2.0.0 LTS (released October 21, 2026) | SQL engine that reads exports and builds the unified table |
| pandas | 3.1.0 | Gives DuckDB results a .df() view and writes HTML tables |
| AWS CLI | Current v2 | Lists Data Exports tables and manages the S3 bucket |
| kubectl | Matches your cluster | Port-forwards the OpenCost API |
| Helm | Current release | Installs the OpenCost chart |
| Kubernetes cluster | Any conformant cluster | Source of OpenCost allocation data |
| Cloud permissions | Billing read plus storage read | Lets DuckDB read S3, Blob Storage and GCS objects |
A note on the DuckDB version. The 2.0 line is a long-term support release supported until at least October 2027, and DuckDB 1.4 stays supported only until November 17, 2026. The project also extended 1.5 support through March 2027, so staying on 1.5 is defensible if you cannot upgrade this week. New work should start on 2.0. Only people compiling from source need a C++17 compiler, because 2.0 raised that floor.
Steps 1 and 2: Create the Workspace and Generate Sample Exports
Step 1. Create an isolated Python environment and install the two libraries. A virtual environment keeps the DuckDB version pinned to what you tested, which matters because the project now maintains three supported release lines.
mkdir focus-lab && cd focus-lab
python3 -m venv .venv
./.venv/bin/pip install duckdb pandas
./.venv/bin/python -c "import duckdb; print(duckdb.__version__)"The last command should print 2.0.0 or a newer 2.x patch release. If it prints a 1.x version, an older wheel got cached, so rerun the install with --upgrade --no-cache-dir.
Step 2. Generate synthetic exports so you can build and test everything before touching a real billing account. The generator writes one CSV per provider using real FOCUS column names, then plants six realistic problems that your pipeline must survive. Azure costs arrive in EUR. Azure tag keys use TitleCase while the others use lowercase. AWS rows include a Savings Plan with covered usage billed at zero. A monthly Purchase row and a Credit row test the charge categories. About 15% of rows carry no team tag. And one Bedrock day spikes to six times normal on September 14, 2026, so Step 11 has something to find. The provider names, services and dollar amounts are invented for the lab.
"""Generate synthetic FOCUS-style exports for three providers plus an OpenCost allocation sample."""
import csv, json, random, pathlib, datetime as dt
random.seed(42)
OUT = pathlib.Path("data")
OUT.mkdir(exist_ok=True)
START, DAYS = dt.date(2026, 7, 1), 115 # July 1 through October 23, 2026
TEAMS = ["checkout", "search", "ml-platform", "data", "platform"]
# (service, FOCUS ServiceCategory, ServiceSubcategory, daily base cost in USD)
CATALOG = {
"aws": [
("Amazon Elastic Compute Cloud", "Compute", "Virtual Machines", 420),
("Amazon Elastic Kubernetes Service", "Compute", "Containers", 4.8),
("Amazon Simple Storage Service", "Storage", "Object Storage", 35),
("Amazon Relational Database Service", "Databases", "Relational Databases", 160),
("Amazon Bedrock", "AI and Machine Learning", "Generative AI", 90),
("AWS Data Transfer", "Networking", "Network Infrastructure", 28),
],
"azure": [
("Virtual Machines", "Compute", "Virtual Machines", 210),
("Azure Kubernetes Service", "Compute", "Containers", 2.4),
("Storage Accounts", "Storage", "Object Storage", 20),
("Azure SQL Database", "Databases", "Relational Databases", 75),
("Azure AI Services", "AI and Machine Learning", "Generative AI", 55),
("Bandwidth", "Networking", "Network Infrastructure", 12),
],
"gcp": [
("Compute Engine", "Compute", "Virtual Machines", 150),
("Google Kubernetes Engine", "Compute", "Containers", 2.4),
("Cloud Storage", "Storage", "Object Storage", 18),
("Cloud SQL", "Databases", "Relational Databases", 60),
("Vertex AI", "AI and Machine Learning", "Generative AI", 70),
("Networking", "Networking", "Network Infrastructure", 9),
],
}
PROVIDERS = {
"aws": dict(name="Amazon Web Services", acct="111122223333", cur="USD", fx=1.0, region=("us-east-1", "US East (N. Virginia)")),
"azure": dict(name="Microsoft", acct="a1b2c3d4-0000-4000-8000-00000000cafe", cur="EUR", fx=1 / 1.10, region=("westeurope", "West Europe")),
"gcp": dict(name="Google Cloud", acct="01A2B3-C4D5E6-F7A8B9", cur="USD", fx=1.0, region=("us-central1", "us-central1")),
}
FIELDS = ["BillingAccountId", "BillingCurrency", "BillingPeriodStart", "BillingPeriodEnd", "ChargeCategory",
"ChargeDescription", "ChargePeriodStart", "ChargePeriodEnd", "CommitmentDiscountId", "ServiceName",
"ServiceCategory", "ServiceSubcategory", "RegionId", "RegionName", "ResourceId", "SkuId", "SubAccountId",
"Tags", "ListCost", "ContractedCost", "BilledCost", "EffectiveCost", "ConsumedQuantity", "ConsumedUnit"]
def tags(provider, team, env, cluster):
d = {"team": team, "env": env}
if cluster:
d["cluster"] = cluster
if team is None:
d.pop("team")
if provider == "azure": # Azure exports in this lab use TitleCase tag keys
d = {k.capitalize(): v for k, v in d.items()}
return json.dumps(d)
for prov, meta in PROVIDERS.items():
rows = []
for i in range(DAYS):
day = START + dt.timedelta(days=i)
nxt = day + dt.timedelta(days=1)
month_start = day.replace(day=1)
month_end = (month_start + dt.timedelta(days=32)).replace(day=1)
growth = 1 + i * 0.002
for svc, cat, sub, base in CATALOG[prov]:
for r in range(3):
team = random.choice(TEAMS) if random.random() > 0.15 else None
env = random.choices(["prod", "staging", "dev"], [0.7, 0.2, 0.1])[0]
cluster = "prod-eks" if prov == "aws" and cat == "Compute" and env == "prod" else None
factor = growth * random.uniform(0.85, 1.15) / 3
if prov == "aws" and svc == "Amazon Bedrock" and day == dt.date(2026, 9, 14):
factor *= 6 # planted anomaly for Step 10
lst = round(base * factor, 4)
contracted, billed, eff = lst * 0.95, lst * 0.95, lst * 0.95
commit = ""
if prov == "aws" and svc == "Amazon Elastic Compute Cloud" and env == "prod":
commit, billed, eff = "sp-0042", 0.0, lst * 0.72 # Savings Plan covered
fx = meta["fx"]
rows.append([meta["acct"], meta["cur"], month_start, month_end, "Usage", f"{svc} usage",
f"{day}T00:00:00Z", f"{nxt}T00:00:00Z", commit, svc, cat, sub, meta["region"][0],
meta["region"][1], f"{prov}-{svc[:6].lower().replace(' ', '')}-{r}", f"sku-{r}",
f"{prov}-sub-{r % 2}", tags(prov, team, env, cluster), round(lst * fx, 4),
round(contracted * fx, 4), round(billed * fx, 4), round(eff * fx, 4),
round(lst * 10, 2), "Hours"])
if prov == "aws" and day.day == 1: # monthly commitment purchase and a credit
rows.append([meta["acct"], "USD", month_start, month_end, "Purchase", "Savings Plan monthly fee",
f"{day}T00:00:00Z", f"{nxt}T00:00:00Z", "sp-0042", "Amazon Elastic Compute Cloud",
"Compute", "Virtual Machines", "us-east-1", "US East (N. Virginia)", "sp-0042", "sku-sp",
"aws-sub-0", json.dumps({"team": "platform", "env": "prod"}), 0, 0, 3000, 0, 1, "Months"])
rows.append([meta["acct"], "USD", month_start, month_end, "Credit", "Promotional credit",
f"{day}T00:00:00Z", f"{nxt}T00:00:00Z", "", "Amazon Bedrock", "AI and Machine Learning",
"Generative AI", "us-east-1", "US East (N. Virginia)", "credit-1", "sku-credit", "aws-sub-0",
"{}", 0, 0, -250, -250, 1, "Credits"])
with open(OUT / f"{prov}_focus.csv", "w", newline="") as f:
w = csv.writer(f)
w.writerow(FIELDS)
w.writerows(rows)
print(f"{prov}: {len(rows)} rows")
# OpenCost-style allocation response (7-day window, aggregate=namespace)
ns = {"checkout": 410.0, "search": 290.0, "ml-platform": 640.0, "kube-system": 95.0, "monitoring": 70.0, "__idle__": 330.0}
alloc = {}
for name, total in ns.items():
cpu, ram = round(total * 0.55, 2), round(total * 0.35, 2)
alloc[name] = {"name": name, "cpuCost": cpu, "ramCost": ram, "pvCost": round(total * 0.06, 2),
"networkCost": round(total * 0.04, 2), "totalCost": total,
"cpuEfficiency": round(random.uniform(0.18, 0.55), 2), "ramEfficiency": round(random.uniform(0.3, 0.7), 2)}
json.dump({"code": 200, "data": [alloc]}, open(OUT / "opencost_allocation_7d.json", "w"), indent=2)
print("opencost sample written")Run it and confirm the row counts.
./.venv/bin/python gen_sample_data.pyaws: 2078 rows
azure: 2070 rows
gcp: 2070 rows
opencost sample writtenThe data folder now holds aws_focus.csv, azure_focus.csv, gcp_focus.csv and a sample OpenCost response. Open one CSV and look at the header. Those 24 columns are a subset of the full spec, enough to teach every technique in this guide without drowning the examples.
Steps 3 to 5: Switch On FOCUS Exports in Each Cloud
Skip this section on your first pass if you only want the sample-data path. When you are ready for real numbers, enable exports in each cloud and let them accumulate for a day or two. Treat the export format as Parquet where offered, because Parquet keeps types intact and avoids the CSV quoting traps covered in the troubleshooting section.
Step 3: AWS Data Exports
AWS delivers billing exports through Data Exports, and its Cloud Financial Management page lists Data Exports next to Cost Explorer and the Cost and Usage Report. The Data Exports API is served from us-east-1, so point your CLI there. List the available tables first, because the FOCUS table name carries a version suffix that changes over time.
aws bcm-data-exports list-tables --region us-east-1 \
--query "Tables[].TableName" --output text
aws bcm-data-exports get-table --region us-east-1 --table-name <FOCUS_TABLE_FROM_ABOVE>Pick the FOCUS table, choose Parquet, and deliver to an S3 bucket in the console wizard, which also prints the bucket policy you must attach. According to AWS’s April 2026 announcement, Data Exports can deliver standard exports, FOCUS among them, to a bucket in another authorized account. That is useful when a central FinOps account owns the lake. Expect AWS-specific extension columns prefixed with x_, since the spec reserves that prefix for provider extras.
Step 4: Azure Cost Management exports
In the Azure portal, open Cost Management, choose Exports, and create a new export. Select the FOCUS-format cost and usage dataset, set the schedule to daily, and point it at a storage account container. Microsoft Q&A material lists budgets, cost details, exports and query APIs as the Cost Management toolset, and Microsoft’s finops-toolkit project is associated with Azure FinOps and FOCUS exports. Reporting from October 2026 describes the Azure schema as a FOCUS 1.2 preview, so check the schema version in the export manifest before you trust a new column.
Step 5: Google Cloud billing export to BigQuery
Google Cloud’s FOCUS-format BigQuery export was still reported as preview-level in October 2026, so enable the standard detailed billing export too and keep a mapping view as a fallback. Once a FOCUS table exists in BigQuery, extract it to Cloud Storage as Parquet, which DuckDB can read directly.
bq extract --destination_format=PARQUET \
'my-project:billing_export.focus_daily' \
'gs://my-finops-lake/gcp/focus/part-*.parquet'Google Cloud’s October 22 announcement adds a wrinkle worth planning for. OpenAI models bought through Google Cloud Marketplace can appear on one unified Google Cloud invoice, drawing down existing commitments. Preserve the commitment and marketplace fields during normalization, and keep Marketplace charges separate from native service usage in your reports. Organization structure matters here too, since billing accounts, subscriptions and projects map differently across clouds, as the comparison of landing zone and organization structure differences explains.
Steps 6 and 7: Connect DuckDB and Build the Unified Table
Step 6. DuckDB reads objects straight from cloud storage, so you never load billing data into a separate warehouse. The 2.0 release adds asynchronous I/O designed to speed up parallel remote reads, including s3:// paths, which is the exact access pattern a folder of daily export files creates. Create one secret per cloud, using credential chains so no keys land in your scripts.
INSTALL httpfs; LOAD httpfs;
CREATE SECRET aws_s3 (TYPE s3, PROVIDER credential_chain, REGION 'us-east-1');
SELECT count(*) FROM read_parquet('s3://my-finops-lake/aws/focus/**/*.parquet');
INSTALL azure; LOAD azure;
CREATE SECRET az_blob (TYPE azure, PROVIDER credential_chain, ACCOUNT_NAME 'myfinopsstorage');
SELECT count(*) FROM read_parquet('az://exports/azure/focus/**/*.parquet');
CREATE SECRET gcs_lake (TYPE gcs, KEY_ID '<hmac-key-id>', SECRET '<hmac-secret>');
SELECT count(*) FROM read_parquet('gcs://my-finops-lake/gcp/focus/*.parquet');Local-first analysis is also a mainstream habit. DuckDB’s own 2024 user survey analysis found that 87% of surveyed users ran DuckDB on laptops, 73% used it through Python and 79% used Parquet, which is precisely this stack. Syntax for extensions can shift between major versions, so confirm the secret options against the 2.0 documentation if a statement errors.
Real exports arrive as many files, often nested under date folders. A glob such as **/*.parquet reads them all, and the filename = true option adds a column holding each row’s source file, which makes duplicate-export debugging far easier. When folders follow a key=value pattern such as billing_period=2026-10, add hive_partitioning = true so DuckDB exposes the folder values as columns and skips folders a query does not need. I confirmed filename = true against the lab CSVs, where it returned all three provider files.
Step 7. Now the core of the pipeline. build.py reads the three exports, converts every amount to USD from a rate table that finance owns, lowercases and unifies tag keys, and stacks the result into focus_unified. Create the rate file first. The values here are placeholders for the lab, so replace them with the rate your finance team uses.
currency,usd_rate
USD,1.0
EUR,1.10Save that as fx.csv, then create build.py.
import duckdb
con = duckdb.connect("finops.duckdb")
con.execute("CREATE OR REPLACE TABLE fx AS SELECT * FROM read_csv('fx.csv')")
SELECT = """
SELECT
'{prov}' AS source,
'{provider_name}' AS ServiceProviderName,
BillingAccountId, BillingCurrency, SubAccountId,
CAST(ChargePeriodStart AS TIMESTAMP) AS ChargePeriodStart,
CAST(BillingPeriodStart AS DATE) AS BillingPeriodStart,
ChargeCategory, ChargeDescription, CommitmentDiscountId,
ServiceName, ServiceCategory, ServiceSubcategory, RegionName, ResourceId,
COALESCE(lower(json_extract_string(Tags, '$.team')), lower(json_extract_string(Tags, '$.Team'))) AS team,
COALESCE(lower(json_extract_string(Tags, '$.env')), lower(json_extract_string(Tags, '$.Env'))) AS env,
COALESCE(json_extract_string(Tags, '$.cluster'), json_extract_string(Tags, '$.Cluster')) AS cluster,
ListCost * usd_rate AS ListCostUsd,
BilledCost * usd_rate AS BilledCostUsd,
EffectiveCost * usd_rate AS EffectiveCostUsd
FROM read_csv('data/{prov}_focus.csv') e
LEFT JOIN fx ON fx.currency = e.BillingCurrency
"""
parts = [
SELECT.format(prov="aws", provider_name="Amazon Web Services"),
SELECT.format(prov="azure", provider_name="Microsoft"),
SELECT.format(prov="gcp", provider_name="Google Cloud"),
]
con.execute("CREATE OR REPLACE TABLE focus_unified AS " + " UNION ALL ".join(parts))
print(con.execute("""
SELECT source, count(*) AS rows, round(sum(BilledCostUsd),2) AS billed_usd, round(sum(EffectiveCostUsd),2) AS effective_usd
FROM focus_unified GROUP BY source ORDER BY source
""").df().to_string(index=False))Four decisions in that file deserve a defense. The join to the rate table is a LEFT JOIN, so a currency with no rate produces a null cost that Step 8 will catch, instead of a silently dropped row. Tag extraction tries both $.team and $.Team because Azure disagrees with the other two on case. The provider name is set by the script as ServiceProviderName, the FOCUS 1.2-and-later column, because older exports call it ProviderName. And every cost column gets a Usd suffix so nobody mistakes a converted number for the original currency. Run it.
./.venv/bin/python build.pysource rows billed_usd effective_usd
aws 2078 66281.86 80926.22
azure 2070 45796.56 45796.56
gcp 2070 37671.05 37671.05Azure’s two cost totals match, while AWS shows a gap between billed and effective cost. That gap is the Savings Plan at work, and Step 9 explains why it matters.
Step 8: Validate Before You Trust a Dollar
A pipeline that publishes wrong numbers is worse than no pipeline, because people act on them. The validation script runs five assertions against focus_unified and exits non-zero if any fail. Wire it before the report in cron, and a bad export never reaches a dashboard.
import duckdb, sys
con = duckdb.connect("finops.duckdb", read_only=True)
checks = {
"no null charge periods": "SELECT count(*) FROM focus_unified WHERE ChargePeriodStart IS NULL",
"no negative usage rows": "SELECT count(*) FROM focus_unified WHERE ChargeCategory = 'Usage' AND BilledCostUsd < 0",
"no duplicate usage rows": """SELECT count(*) FROM (
SELECT ResourceId, ChargePeriodStart, ChargeDescription, count(*) c
FROM focus_unified WHERE ChargeCategory = 'Usage' GROUP BY ALL HAVING c > 1)""",
"all currencies converted": "SELECT count(*) FROM focus_unified WHERE BilledCostUsd IS NULL",
"known categories only": """SELECT count(*) FROM focus_unified
WHERE ChargeCategory NOT IN ('Usage','Purchase','Tax','Credit','Adjustment')""",
}
failed = 0
for name, sql in checks.items():
bad = con.execute(sql).fetchone()[0]
print(f"{'PASS' if bad == 0 else 'FAIL'} {name} (bad rows: {bad})")
failed += bad > 0
tagless = con.execute("""SELECT round(100.0 * sum(EffectiveCostUsd) FILTER (WHERE team IS NULL) / sum(EffectiveCostUsd), 1)
FROM focus_unified WHERE ChargeCategory = 'Usage'""").fetchone()[0]
print(f"INFO untagged spend share: {tagless}%")
sys.exit(1 if failed else 0)PASS no null charge periods (bad rows: 0)
PASS no negative usage rows (bad rows: 0)
PASS no duplicate usage rows (bad rows: 0)
PASS all currencies converted (bad rows: 0)
PASS known categories only (bad rows: 0)
INFO untagged spend share: 15.0%The final line is informational. It reports that 15.0% of usage spend carries no team tag, a number you should track weekly because it measures how much of your showback is guesswork. To prove the gate works, I deleted the EUR row from fx.csv and reran the build. The currency check failed and the script returned exit code 1.
FAIL all currencies converted (bad rows: 2070)Every Azure row lost its conversion, and the pipeline stopped. With an inner join the same mistake would have shrunk the totals quietly and passed every check. Add checks as you meet new failure modes. A row-count floor is the cheapest to write. Each provider produces 18 usage rows per day in the lab, so this query lists any day that falls short, and it returns nothing on healthy data.
SELECT source, CAST(ChargePeriodStart AS DATE) AS day, count(*) AS n
FROM focus_unified WHERE ChargeCategory = 'Usage'
GROUP BY ALL HAVING n < 18 ORDER BY day;Set the threshold from your own normal volume. Two more good candidates: a freshness check on the newest ChargePeriodStart per provider, and a comparison of your monthly total with the invoice total once you ingest the Invoice Detail dataset from FOCUS 1.4.
Steps 9 and 10: Pick the Right Cost Column, Then Run Showback
Step 9. FOCUS carries four cost columns, and picking the wrong one is the most common error in multicloud reports. The table sets out which to use for what.
| Column | What it measures | Use it for |
|---|---|---|
| ListCost | Public list price before any discount | Measuring how much your discounts are worth |
| ContractedCost | Price under your negotiated agreement | Checking that contract terms apply correctly |
| BilledCost | What lands on the invoice | Cash and invoice reconciliation |
| EffectiveCost | Amortized cost, with commitment fees spread across the usage they cover | Showback, chargeback and unit economics |
The lab data makes the difference visible. When a Savings Plan covers EC2 usage, the usage rows show a billed cost of zero, because the money moves through a separate monthly Purchase row. Summing billed cost by team would blame whoever happened to buy the plan, while effective cost spreads the benefit to the teams that consumed it. Default to effective cost for any report a human will argue about.
Create analysis.py. It holds four report blocks, and this step runs the first three. The same file carries the anomaly query for Step 11 and the Kubernetes join for Step 12.
import duckdb, json
con = duckdb.connect("finops.duckdb")
pd_opts = dict(index=False)
print("\n== Spend by provider and month (EffectiveCost, USD) ==")
print(con.execute("""
SELECT strftime(BillingPeriodStart, '%Y-%m') AS month,
round(sum(EffectiveCostUsd) FILTER (WHERE source='aws'), 0) AS aws,
round(sum(EffectiveCostUsd) FILTER (WHERE source='azure'), 0) AS azure,
round(sum(EffectiveCostUsd) FILTER (WHERE source='gcp'), 0) AS gcp,
round(sum(EffectiveCostUsd), 0) AS total
FROM focus_unified GROUP BY 1 ORDER BY 1""").df().to_string(**pd_opts))
print("\n== Spend by ServiceCategory (all providers) ==")
print(con.execute("""
SELECT ServiceCategory, round(sum(EffectiveCostUsd), 0) AS usd,
round(100 * sum(EffectiveCostUsd) / sum(sum(EffectiveCostUsd)) OVER (), 1) AS pct
FROM focus_unified GROUP BY 1 ORDER BY usd DESC""").df().to_string(**pd_opts))
print("\n== Showback by team ==")
print(con.execute("""
SELECT coalesce(team, '(untagged)') AS team, round(sum(EffectiveCostUsd), 0) AS usd
FROM focus_unified WHERE ChargeCategory = 'Usage' GROUP BY 1 ORDER BY usd DESC""").df().to_string(**pd_opts))
print("\n== BilledCost vs EffectiveCost, AWS compute ==")
print(con.execute("""
SELECT ChargeCategory, round(sum(BilledCostUsd), 0) AS billed, round(sum(EffectiveCostUsd), 0) AS effective
FROM focus_unified WHERE source = 'aws' AND ServiceCategory = 'Compute' GROUP BY 1 ORDER BY 1""").df().to_string(**pd_opts))
print("\n== Daily anomalies (z-score > 3 vs trailing 14 days, per service) ==")
con.execute("""
CREATE OR REPLACE TABLE daily AS
SELECT source, ServiceName, CAST(ChargePeriodStart AS DATE) AS day, sum(EffectiveCostUsd) AS usd
FROM focus_unified WHERE ChargeCategory = 'Usage' GROUP BY ALL""")
print(con.execute("""
WITH w AS (
SELECT *, avg(usd) OVER win AS mean14, stddev_samp(usd) OVER win AS sd14
FROM daily
WINDOW win AS (PARTITION BY source, ServiceName ORDER BY day ROWS BETWEEN 14 PRECEDING AND 1 PRECEDING))
SELECT source, ServiceName, day, round(usd, 0) AS usd, round(mean14, 0) AS mean14, round((usd - mean14) / sd14, 1) AS z
FROM w WHERE sd14 > 0 AND (usd - mean14) / sd14 > 3 ORDER BY z DESC LIMIT 5""").df().to_string(**pd_opts))
print("\n== Kubernetes: allocate prod-eks cloud cost to namespaces (OpenCost 7-day shares) ==")
alloc = json.load(open("data/opencost_allocation_7d.json"))["data"][0]
con.execute("CREATE OR REPLACE TABLE oc_alloc (namespace VARCHAR, total_cost DOUBLE, cpu_eff DOUBLE)")
con.executemany("INSERT INTO oc_alloc VALUES (?, ?, ?)",
[(k, v["totalCost"], v["cpuEfficiency"]) for k, v in alloc.items()])
print(con.execute("""
WITH cluster AS (
SELECT sum(EffectiveCostUsd) AS usd FROM focus_unified
WHERE cluster = 'prod-eks' AND CAST(ChargePeriodStart AS DATE) >= DATE '2026-10-17')
SELECT namespace, total_cost AS opencost_usd,
round(100 * total_cost / sum(total_cost) OVER (), 1) AS share_pct,
round(100 * total_cost / sum(total_cost) OVER () * (SELECT usd FROM cluster) / 100, 0) AS allocated_cloud_usd,
cpu_eff
FROM oc_alloc ORDER BY total_cost DESC""").df().to_string(**pd_opts))Step 10. Run it and read the first sections.
./.venv/bin/python analysis.py
== Spend by provider and month (EffectiveCost, USD) ==
month aws azure gcp total
2026-07 19918.0 11429.0 9471.0 40818.0
2026-08 21307.0 12147.0 9947.0 43400.0
2026-09 22179.0 12312.0 10190.0 44681.0
2026-10 17523.0 9908.0 8064.0 35495.0
== Spend by ServiceCategory (all providers) ==
ServiceCategory usd pct
Compute 87984.0 53.5
Databases 35932.0 21.9
AI and Machine Learning 25611.0 15.6
Storage 8916.0 5.4
Networking 5952.0 3.6
== Showback by team ==
team usd
data 29834.0
platform 28993.0
checkout 28185.0
ml-platform 27964.0
search 25650.0
(untagged) 24768.0
== BilledCost vs EffectiveCost, AWS compute ==
ChargeCategory billed effective
Purchase 12000.0 0.0
Usage 16795.0 43439.0Compute is the largest slice of spend, and AI and machine learning is a smaller slice that grows fast once teams adopt token-metered services. The showback table has a more awkward lesson. Untagged usage (synthetic data) comes close to the search team’s total. If you present this table without a footnote, someone will ask why nobody owns such a large share of the bill. Fix tag coverage at the source with tag policies and required tags in infrastructure code, and use this query as the scoreboard. If you are standing up native tooling first, the AWS Cost Explorer and Budgets setup guide covers cost allocation tags on the AWS side.
The last block in that output shows the billed-versus-effective gap. Billed usage for AWS compute is far lower than its effective usage, while the Purchase rows add billed cost and zero effective cost. Billed usage alone makes AWS compute look much cheaper than its effective cost, and a per-team report built on it would mislead every reader.
Step 11: Catch Spend Spikes With a Window Function
Budgets alert at a threshold. They do not tell you that a service tripled overnight while still sitting below the monthly cap. A rolling z-score against the trailing 14 days catches that pattern with one window function, and DuckDB runs it across the whole unified table in milliseconds.
The anomaly block in analysis.py first builds a daily table of effective cost per provider, service and day. Then it computes the mean and sample standard deviation over the 14 preceding rows, excluding the current day, and flags days more than three standard deviations above the mean. Here is the output from the lab data.
== Daily anomalies (z-score > 3 vs trailing 14 days, per service) ==
source ServiceName day usd mean14 z
aws Amazon Bedrock 2026-09-14 542.0 97.0 134.4
gcp Compute Engine 2026-09-05 181.0 154.0 4.2
aws Amazon Elastic Compute Cloud 2026-07-03 348.0 311.0 4.0
gcp Vertex AI 2026-07-27 79.0 68.0 3.9
gcp Cloud SQL 2026-07-03 65.0 57.0 3.8The planted Bedrock spike in the lab data scores a z far above any threshold, so no reasonable setting misses it. The four rows beneath it are noise, with z scores between 3.8 and 4.2 on days that moved by $37 or less. That is the standard trap with z-scores on stable series. A tiny standard deviation turns small wiggles into huge ratios. Add a dollar floor and raise the threshold, so only moves that matter to a person get flagged.
WHERE sd14 > 0 AND usd - mean14 > 100 AND (usd - mean14) / sd14 > 6With that filter the lab returns exactly one row, the Bedrock day. AI spend is the category most likely to produce real spikes in 2026, since token-metered services can run away faster than any instance fleet. AWS says it added Amazon Bedrock product attributes to Cost Explorer, Budgets and Cost Management Dashboards on October 8, 2026, so you can group by model, model provider and inference type in the console. The pipeline view complements that, because it puts Bedrock beside Azure and Google Cloud model spend in one table. For context on why the model bill varies so much by cloud, see the GPU price gaps between the three clouds.
Step 12: Add Kubernetes Allocation With OpenCost
Cloud bills stop at the node. Your invoice says a cluster of EC2 instances cost a certain amount, but not which namespace or team used the CPU. OpenCost fills that gap. It is an open-source Kubernetes cost monitoring project and a CNCF Incubating project, accepted in June 2022 and promoted to Incubating on October 25, 2024. It is not a billing normalizer, and the official FOCUS 1.4 material does not show native OpenCost allocation, so the join happens in your pipeline.
Install OpenCost with Helm. It needs a Prometheus to read from. The chart values below point at the Prometheus service created in the first command, and you should confirm the exact value names with helm show values opencost/opencost because chart options evolve.
helm repo add prometheus-community https://prometheus-community.github.io/helm-charts
helm repo add opencost https://opencost.github.io/opencost-helm-chart
helm install prometheus prometheus-community/prometheus -n prometheus-system --create-namespace
helm install opencost opencost/opencost -n opencost --create-namespace \
--set opencost.prometheus.internal.serviceName=prometheus-server \
--set opencost.prometheus.internal.namespaceName=prometheus-system
kubectl -n opencost port-forward service/opencost 9003:9003 &
curl -s -G http://localhost:9003/allocation/compute \
-d window=7d -d aggregate=namespace -d accumulate=true > data/opencost_allocation_7d.jsonThe last command overwrites the sample file with live data, in the same shape the lab uses. Each namespace carries cpuCost, ramCost, pvCost, networkCost, totalCost and efficiency ratios. The release line moves quickly, so pin a chart version in production rather than tracking latest.
The join in analysis.py takes the cloud cost of everything tagged cluster = prod-eks over the same seven days and splits it by each namespace’s share of OpenCost’s total. Here is the output.
== Kubernetes: allocate prod-eks cloud cost to namespaces (OpenCost 7-day shares) ==
namespace opencost_usd share_pct allocated_cloud_usd cpu_eff
ml-platform 640.0 34.9 626.0 0.23
checkout 410.0 22.3 401.0 0.43
__idle__ 330.0 18.0 323.0 0.31
search 290.0 15.8 283.0 0.48
kube-system 95.0 5.2 93.0 0.35
monitoring 70.0 3.8 68.0 0.37Read the __idle__ row first. It is capacity you pay for that no workload requested, and in this sample it is a sizable share of the cluster. The ml-platform namespace takes the largest share of the cost at a low CPU efficiency, which means it requests far more CPU than it uses. That is a rightsizing conversation with one team, and it exists only because the Kubernetes layer and the billing layer now sit in one place. The proportional split is a simplification, so document it for your finance partners and consider weighting by CPU and memory separately later.
OpenCost 1.121.0 also added Kubernetes inference cost tracking through an integration with the CNCF llm-d project, as the CNCF write-up of OpenCost 1.121.0 describes. The OpenCost team’s worked example shows why it matters. A self-hosted model at 25% utilization costs $1.00 per million tokens on a usage basis but $4.00 per million on an allocation basis, against an external API price of $2.00, and self-hosting becomes competitive above roughly 50% utilization. If you serve models on your own clusters, track the allocation-based figure. Cast AI has also published an explainer on the FOCUS spec applied to Kubernetes and AI costs, which is a good companion read.
Step 13: Publish the Report and Schedule the Job
The last stage turns tables into artifacts that people open. report.py writes a small static HTML page and exports the unified table as Parquet, which any BI tool can read later.
import duckdb, pathlib, datetime as dt
con = duckdb.connect("finops.duckdb", read_only=True)
out = pathlib.Path("out"); out.mkdir(exist_ok=True)
sections = {
"Spend by provider and month": """
SELECT strftime(BillingPeriodStart, '%Y-%m') AS month, source,
round(sum(EffectiveCostUsd), 0) AS effective_usd
FROM focus_unified GROUP BY ALL ORDER BY 1, 2""",
"Top 10 services": """
SELECT source, ServiceName, round(sum(EffectiveCostUsd), 0) AS effective_usd
FROM focus_unified GROUP BY ALL ORDER BY effective_usd DESC LIMIT 10""",
"Showback by team": """
SELECT coalesce(team, '(untagged)') AS team, round(sum(EffectiveCostUsd), 0) AS effective_usd
FROM focus_unified WHERE ChargeCategory = 'Usage' GROUP BY 1 ORDER BY 2 DESC""",
}
html = [f"<h1>Multicloud FinOps report</h1><p>Generated {dt.date.today()} from FOCUS-style exports.</p>"]
for title, sql in sections.items():
html.append(f"<h2>{title}</h2>" + con.execute(sql).df().to_html(index=False, border=0))
(out / "report.html").write_text("<meta charset='utf-8'><style>body{font:15px sans-serif;margin:2rem}td,th{padding:4px 12px}</style>" + "".join(html))
con.execute("COPY focus_unified TO 'out/focus_unified.parquet' (FORMAT parquet)")
print("wrote out/report.html and out/focus_unified.parquet")wrote out/report.html and out/focus_unified.parquetOpen out/report.html in a browser to see three tables: spend by provider and month, the ten biggest services, and showback by team. Static HTML is deliberate. It needs no server, it can sit in a private bucket behind your identity provider, and it survives staff turnover better than a dashboard nobody remembers building.
Now schedule the chain. Order matters, so use && so a failed check stops the report from refreshing.
15 6 * * * cd /opt/focus-lab && ./.venv/bin/python build.py && ./.venv/bin/python checks.py && ./.venv/bin/python report.py >> run.log 2>&1Exports land with a delay, so schedule the job well after your providers’ export windows and confirm in the first week that yesterday’s data is complete. If you would rather not maintain a server, the same three scripts fit a scheduled function, and the serverless runtime limits across the big three tell you whether a nightly build fits in one invocation. Keep finops.duckdb on persistent storage in that case, or rebuild from Parquet every run, which this dataset size easily allows.
Common Pitfalls
Seven mistakes cause most of the bad numbers in homegrown cost pipelines. Each one is cheap to prevent once you know it.
- Mixing billed and effective cost. Reports that sum BilledCost by team punish whoever bought a commitment and flatter everyone who used it. Use EffectiveCost for showback and keep BilledCost for invoice reconciliation only.
- Assuming every export is FOCUS 1.4. Providers reportedly ship exports near the 1.2 level. A query that references a 1.4-only column fails on AWS data, so check the manifest version and keep mappings per provider.
- Ignoring tag key case. Azure’s TitleCase keys mean
teamandTeamare different keys. Normalize withlower()on both keys and values, then count coverage after normalization. - Converting currency at the wrong time. Converting after aggregation hides month-boundary effects. Convert each row at the rate finance approves, and store the rate date next to the table.
- Double counting commitments. Adding Purchase rows to effective cost counts the same money twice. Purchases belong in cash views, amortized usage belongs in showback.
- Rescanning the whole lake nightly. Reading every file every run works at lab scale and gets slow at year-three scale. Partition by billing month and rebuild only the current and previous month.
- Skipping the validation gate. A pipeline without checks eventually publishes a wrong total with a confident chart. The gate in Step 8 costs ten lines.
Troubleshooting
These are the failures you are most likely to hit, with the cause and the fix. I reproduced the first three while building the lab.
- Secret Validation Failure: no profile ‘default’ found in config file. DuckDB’s
credential_chainprovider found no AWS profile. Runaws configure, exportAWS_PROFILE, or run on a host with an instance role, then recreate the secret. - Binder Error: Referenced column not found. A column name in the SQL does not match the table. In my first build the rate column was
usd_ratewhile the query usedrate. RunDESCRIBEon the source and match names exactly. - FAIL all currencies converted with thousands of bad rows. A currency has no rate in
fx.csv. Add it and rebuild. The check did its job, so do not loosen it. - IO Error about being unable to set a lock on the database file. Another process holds
finops.duckdbopen for writing, often a notebook. Close it, or open the file withread_only=Truefor reports. - Access denied errors when reading s3:// or az:// paths. The identity lacks list or read permission on the bucket or container. Fix the policy or role, and check that the export wizard’s bucket policy was attached.
- Conversion Error: invalid timestamp. Providers format timestamps differently. Cast with
TRY_CAST(ChargePeriodStart AS TIMESTAMP), count the nulls, and inspect sample rows before choosing a parse format. - Tags column breaks JSON functions. CSV quoting damaged the embedded JSON. Re-read with
quote='"'andescape='"', or request Parquet exports to avoid it entirely. - UNION ALL fails on column count or type. Provider schemas drifted. Select explicit columns as
build.pydoes, or useUNION ALL BY NAMEwhile debugging. - Anomaly query returns nothing. The window needs 14 days of history before it can score a day, and a flat series has a standard deviation of zero. Wait for history or shorten the frame.
- OpenCost returns empty data. Prometheus is not scraping yet, the port-forward died, or the window is longer than the data you hold. Check the OpenCost pod logs, confirm Prometheus targets are up and try
window=1d. - Export files missing for yesterday. Exports arrive late. Add a row-count floor check per provider per day, and move the cron time later.
Advanced Tips
Once the basics run, six upgrades give the best return.
Partition the Parquet output. Write focus_unified partitioned by billing month, for example COPY focus_unified TO 'out/lake' (FORMAT parquet, PARTITION_BY (source, BillingPeriodStart)). Downstream tools prune partitions, and rebuilds touch only the current month.
Prepare for FOCUS 1.4 columns. Add Invoice Detail and Billing Period datasets to the lake as separate tables when your providers start emitting them. Joining consumption rows to invoice rows is how you prove the pipeline equals the bill, and it is the headline capability of the 1.4 release.
Preserve provider extras. Keep x_ extension columns in a side table, or try DuckDB 2.0’s first-class VARIANT type for semi-structured fields. I have not tested VARIANT for this use, so run it on a copy first.
Track commitments explicitly. Aggregate by CommitmentDiscountId to see utilization of each plan, and compare list cost with effective cost to quantify the discount you earn. The same view shows when interruptible capacity pays off, as in this walkthrough of AWS Spot savings on game server fleets.
Test engine upgrades on a copy. DuckDB 2.0 replaces the PostgreSQL-derived parser with a PEG-based one, and any major release can change edge-case behavior. Before you move a production file from 1.5 to 2.0, rebuild finops.duckdb from the raw exports under both versions and diff the totals with a query. Matching numbers are your upgrade proof, and a mismatch tells you which statement to inspect before users see a different report.
Automate the questions. AWS says it can connect to Billing and Cost Management through a Billing and Cost Management MCP server, and DuckDB 2.0 ships an agent mode. Both point toward asking cost questions in plain language against your own clean table. Treat that as an experiment, keep read-only credentials, and let the validation gate stay in front of any numbers an assistant reports.
Complete Project Layout and How This Stacks Up
You now have the complete working project: gen_sample_data.py for tests, build.py, checks.py, analysis.py and report.py, plus fx.csv. Run order for a fresh environment is generate, build, check, analyze and report. A single shell wrapper that chains them with && is all the orchestration you need at this scale.
./.venv/bin/python gen_sample_data.py && \
./.venv/bin/python build.py && \
./.venv/bin/python checks.py && \
./.venv/bin/python analysis.py && \
./.venv/bin/python report.pyIs the DIY route right for you? It depends on team size and how many clouds you run. The table compares four common approaches on qualities rather than prices, because pricing varies by contract.
| Approach | Cross-cloud view | Kubernetes allocation | Setup effort | Control over mappings |
|---|---|---|---|---|
| DuckDB plus FOCUS (this guide) | Yes, one table | Yes, via OpenCost join | Hours | Full |
| Native cloud consoles | No, one cloud each | Limited | Minutes | None |
| OpenCost alone | No, Kubernetes only | Yes | An hour | Moderate |
| Commercial FinOps platform | Yes | Usually yes | Days, plus procurement | Vendor decides |
The pipeline wins when you have two or more clouds, someone who can maintain 250 lines of SQL and Python, and a need to explain numbers to finance. A commercial platform wins when you want anomaly workflows, budgets and approvals out of the box without engineering time. Some teams run both, using the pipeline as the audit layer. For more on the platforms behind these services, browse the cloud computing coverage on this site.
FAQ
What is FOCUS in FinOps?
FOCUS is the FinOps Open Cost and Usage Specification, an open schema that standardizes billing and usage data across cloud providers. It is maintained through the FinOps Foundation ecosystem and licensed CC BY 4.0. The newest published version is 1.4, ratified on June 4, 2026.
Do AWS, Azure and Google Cloud export FOCUS 1.4 today?
Not fully. Reporting from October 2026 says provider exports sit near the FOCUS 1.2 level, with Google Cloud’s BigQuery export still at preview level. Plan for provider-specific mappings and check each export’s schema version.
Is FOCUS 1.5 released?
No. FOCUS 1.5 is in development with a planned December 2026 announcement. Build against 1.4 until the new version ships.
Why use DuckDB for a FinOps pipeline?
DuckDB runs in-process, reads Parquet and CSV straight from object storage, and handles window functions and JSON natively, so one small script replaces a warehouse for billing-sized data. Version 2.0.0 LTS shipped on October 21, 2026 and is supported until at least October 2027.
Should I use BilledCost or EffectiveCost for showback?
Use EffectiveCost. It amortizes commitment fees across the usage they cover, so teams are charged for what they consumed. Keep BilledCost for reconciling to the invoice.
Does OpenCost replace Kubecost or cloud billing tools?
No. OpenCost is a CNCF Incubating project for Kubernetes cost visibility and allocation, not a billing normalizer. This pipeline pairs it with FOCUS exports so cluster costs and cloud bills meet in one table. Commercial tools built on similar data add workflows on top.
Can I run this on a laptop?
Yes. The lab runs in under two seconds on synthetic data, and a laptop is the usual home for DuckDB. Move to a small scheduled VM or function when you need the nightly refresh.
How do I know the pipeline matches the invoice?
Ingest the Invoice Detail dataset from FOCUS 1.4 once your providers emit it, then add a check that sums billed cost per invoice and compares it to the invoice amount. Until then, compare monthly provider totals with the invoice PDF by hand and note any variance.
