# FOCUS exercise: Northwind Analytics, 2026 Q3 (synthetic data)

A small, multi-cloud billing dataset in FOCUS format plus a DuckDB SQL exercise
with worked answers. Made by The FinOps Desk for training.

## Provenance: 100% synthetic

- **Every row was generated by a script.** It was produced by
  `scripts/generate-focus-exercise.mjs` (Node.js, no dependencies) using a
  seeded random number generator. Running the script again gives
  byte-identical files.
- **The company is fictional.** "Northwind Analytics" does not exist.
- **This is not real customer data**, and it was not derived from any real bill.
- **All identifiers are invented.** That covers account IDs, subscription and
  project IDs, resource IDs, ARNs and commitment IDs.
- **Real names, invented numbers.** Provider names (AWS, Microsoft, Google
  Cloud) and service names (for example "Amazon Elastic Compute Cloud",
  "Virtual Machines", "BigQuery") are used only so the rows look like each
  provider's export. Every price, quantity and cost is made up. None of them
  reflects real provider pricing.

## Files

| File | What it is |
|------|------------|
| `focus_exercise_2026-q3.csv` | 4,433 billing rows from 2026-07-01 to 2026-09-30, aggregated to one row per resource, per day, per charge type |
| `northwind_orders_2026-q3.csv` | Business metric: daily order counts (`date,orders`), 92 rows |
| `exercise.sql` | Questions Q1 to Q5 with solution queries, plus 20 reconciliation checks |
| `results.json` | The output of each query exactly as DuckDB printed it (`duckdb -csv`), for display on the website (website only, not in the zip) |
| `findings-memo.md` | Example one-page memo written from the query results (website only, not in the zip) |

## FOCUS version

- **Column names are from FOCUS 1.2.** That is the version AWS Data Exports
  and the Azure FOCUS export target (Azure labels it "1.2-preview"). The
  current ratified spec is FOCUS 1.4 (ratified 2026-06-04). The definitions
  used here were cross-checked against the 1.4 wording.
- **Only a practical subset of columns is included:** BillingAccountId,
  SubAccountId, ProviderName, InvoiceIssuerName, ServiceCategory, ServiceName,
  RegionId, ChargeCategory, ChargeFrequency, ChargePeriodStart/End,
  BillingPeriodStart/End, BillingCurrency, ListCost, ContractedCost,
  BilledCost, EffectiveCost, PricingQuantity, PricingUnit,
  CommitmentDiscountId, CommitmentDiscountCategory, CommitmentDiscountStatus,
  ResourceId and Tags.
- **One provider-specific column is added:** x_Credits.

## What is in the data

- **AWS: a 1-year, all-upfront, spend-based commitment (a Compute Savings
  Plan), bought 2026-07-01.**
  - The upfront payment is $149,650.00. It appears as one Purchase row with
    BilledCost 149,650.00 and EffectiveCost 0.
  - The covered EC2 usage rows have BilledCost 0, EffectiveCost equal to the
    amortized amount, and CommitmentDiscountStatus "Used".
  - On 2026-08-15 the fleet shrinks. From then on, part of each day's
    commitment shows up as an "Unused" row: BilledCost 0, EffectiveCost
    greater than 0, and no ResourceId.
  - Used plus Unused EffectiveCost is exactly $410.00 every day
    (149,650.00 / 365).
- **Azure: a 1-year reserved VM instance with monthly billing (no upfront).**
  - Each month has one Purchase row with ChargeFrequency "Recurring",
    BilledCost $1,023.00 and EffectiveCost 0.
  - The covered VM usage rows carry that month's payment as EffectiveCost
    (BilledCost 0).
- **Google Cloud: no Purchase rows, no Credit rows, and no CommitmentDiscount
  columns populated.**
  - A committed use discount is shown as a daily commitment fee row plus
    negative `x_Credits` on the covered VM usage rows. See the
    simplifications below.
- **A deliberate anomaly.** It is a 5-day cost spike in one AWS sub-account
  in September, and its driver can be found through Tags.
- **Other charge types.** There are Tax rows (one per provider per month),
  one AWS Credit row and one Azure Adjustment row.

## Provider-specific simplifications and limitations

- **Google Cloud and `x_Credits`.** The rule below is a convention of this
  synthetic file only. It is a simplified illustration, not a description of
  how Google's FOCUS export calculates its columns.
  - BilledCost is the gross usage cost before credits.
  - x_Credits is the sum of credits on that row: negative, and 0.00 when
    there are none.
  - EffectiveCost = BilledCost + x_Credits.

  In the real export, x_Credits is a nested structure with credit types and
  names. Here it is flattened to one number. Before you rely on any of this,
  read Google's documentation for its FOCUS export, which is in Preview:
  https://docs.cloud.google.com/billing/docs/how-to/export-data-bigquery-tables/focus-export
  (export structure) and
  https://docs.cloud.google.com/billing/docs/how-to/export-data-bigquery-focus-setup
  (setup).
- **Google commitment fee.** The committed use discount fee is modelled as a
  daily Usage row with ChargeFrequency "Recurring" and ResourceId set to the
  commitment. It is a teaching simplification.
- **x_Credits on other providers.** x_Credits is empty (NULL) on every AWS
  and Microsoft row.
- **AWS Savings Plan.** Real AWS data spreads Savings Plan benefit across
  usage hour by hour. This file allocates each day's $410.00 across instances
  in proportion to their usage valued at the Savings Plan rate. Any usage
  beyond the commitment is billed on demand as a separate row for the same
  resource. Only the EC2 fleet is matched against the Savings Plan, so the
  AWS Lambda rows stay on demand even on days with unused commitment. That
  is a simplification: a real Compute Savings Plan can also cover Lambda and
  Fargate.
- **Azure reservation.**
  - The reservation is modelled as 100% used every day.
  - The monthly payment is amortized evenly over the days of that month.
  - $1,023.00 was chosen because it divides exactly by 31 and by 30, so each
    day is a whole number of cents.
- **Tax.**
  - The tax rate is a flat, made-up 6.25% of each provider's monthly net
    billed amount, including the AWS upfront purchase.
  - Tax rows use ServiceCategory "Other" and ServiceName "Tax", and leave
    SubAccountId and ResourceId empty.
  - FOCUS requires EffectiveCost = BilledCost on Tax rows. So the tax on the
    July upfront payment is not amortized, and it lands in July's
    EffectiveCost.
- **Rounding.** All money was generated in integer cents. Any amount that has
  to be split (a day's commitment across resources, Google credits across
  VMs) uses largest-remainder allocation, so the parts always add up exactly
  to the whole.
- **Anomaly shape.** The spike ramps up each day on purpose. A trailing
  7-day z-score usually flags only the first day of a flat multi-day spike,
  because once the spike enters the baseline it raises the mean and the
  standard deviation. Remember this when you apply the Q4 method to real data.

## What the dataset does NOT model

- Hourly granularity. Rows are daily aggregates.
- Most FOCUS columns, including ChargeDescription, ChargeClass (corrections),
  SkuId, SkuPriceId, ListUnitPrice, ContractedUnitPrice, ConsumedQuantity,
  ConsumedUnit, PricingCategory, CapacityReservation columns, AvailabilityZone,
  ResourceName, ResourceType, SubAccountName, CommitmentDiscountName,
  CommitmentDiscountType, CommitmentDiscountQuantity and CommitmentDiscountUnit.
- Negotiated (contracted) discounts. ContractedCost equals ListCost
  everywhere.
- Savings Plan or reservation behaviour such as hour-by-hour application
  order, instance-size flexibility, sharing scope or exchanges.
- The rest of the commitment terms after 2026-09-30.
- Real tax rules, multiple currencies, invoice corrections to earlier
  periods, marketplace purchases and support charges.
- Spend-based Google committed use discounts, and Google's real credit
  structure.
- Any real provider price.

## How to run

1. Install DuckDB: https://duckdb.org/docs/installation (for example
   `brew install duckdb` on macOS). The SQL was verified with
   DuckDB v1.5.3.
2. Unzip the download and open a terminal in the folder that contains the CSV
   files.
3. Run:

   ```
   duckdb < exercise.sql
   ```

   The queries read the CSVs with `read_csv_auto` using relative paths, so
   run the command from that folder. The last result is a table of 20
   reconciliation checks. Every check should show `true`.

To regenerate the data from the FinOps Desk repository:

```
node scripts/generate-focus-exercise.mjs            # CSV files
node scripts/generate-focus-exercise.mjs --results  # CSV files + results.json (needs duckdb on PATH or DUCKDB_BIN)
```

## License

The dataset, `exercise.sql`, `results.json` and this README are licensed
under Creative Commons Attribution 4.0 International (CC BY 4.0),
https://creativecommons.org/licenses/by/4.0/ . Author: The FinOps Desk.

FOCUS is a project of the FinOps Foundation. This dataset is not affiliated
with or endorsed by the FinOps Foundation, AWS, Microsoft or Google.