# FinOps SQL System Design

This document describes the architecture and design for building a unified multi-cloud billing analytics platform using BigQuery. The design prioritizes data consistency, query performance, and extensibility for future cloud provider integrations.

---

## Changelog

| Version | Date | Changes |
|---------|------|---------|
| v2.11 | 2026-03-02 | Slimmed `mart_cost_monthly` grain (removed tags JSON, account_name, usage_unit/usage_amount). Added `mart_cost_summary` pre-aggregated dashboard mart. Converted both marts to incremental with `insert_overwrite` and 3-month lookback. |
| v2.10 | 2026-02-11 | Consolidated 3 usage marts into one `mart_cost_monthly` (single-table MVP). New grain includes account dimensions, key tags, and `usage_unit` for unit-safe usage metrics. |
| v2.9 | 2026-02-11 | Clarified `_row_id` boundary: keep it in staging for lineage/debug only, do not expose in intermediate models, and never use it as PK/dedup/join key. |
| v2.8 | 2026-02-11 | Removed `_row_id`-based row_number dedup in `int_aws_billing_ledger` to avoid silent row loss. Added reconciliation test `assert_ledger_preserves_staging_totals` to enforce staging-vs-ledger row/cost preservation. |
| v2.7 | 2026-02-11 | Split Intermediate into `int_aws_billing_ledger` (all charge types) and `int_aws_billing` (usage-only). Usage marts now use SP/RI-aware effective usage cost and include SP coverage metrics. Clarified that finance/invoice marts should be built from ledger intermediate. |
| v2.6 | 2026-02-04 | Corrected schema to 39 fields (30 Unified + 9 AWS-Specific): 8 tag fields (environment, service_type, usedby, component, tenant, project, cluster + full JSON). Fixed field references in docs and validation scripts. Updated Appendix B tag key format. |
| v2.5 | 2026-01-29 | Extended schema to 39 fields: dual-cost perspective (actual_cost_usd/list_cost_usd), resource/instance fields, Tags, AWS-specific SP/RI fields. Updated marts with weighted_savings_rate. |
| v2.4 | 2026-01-21 | Document BigQuery location constraint (GCP billing export in US), clarify MVP AWS-only focus, and add metric notes for future extensions |
| v2.3 | 2026-01-16 | Add account_name to unified field mapping, add account_mapping seed table with SCD Type 2 (effective_from/effective_to) for historical attribution |
| v2.2 | 2026-01-15 | Add dbt integration, introduce Intermediate layer (Staging → Intermediate → Marts), simplify to usage rows only |
| v2.1 | 2026-01-12 | MVP Phase 1 - AWS CUR 2.0 only (>= 2025-10-01), simplified 20-field schema, core marts. See [finops-mvp-phase1.md](./finops-mvp-phase1.md) for implementation details. |
| v2.0 | 2026-01-09 | Extended design - 37-field schema with product/resource/SP-RI dimensions, 5 extended marts |
| v1.0 | 2026-01-09 | Initial draft - Multi-cloud unified architecture concept |

---

## 1. Overview

### 1.1 Goals

1. **Unified Analytics**: Create a single source of truth for billing data across AWS, GCP, Azure, and Alibaba Cloud
2. **Looker-Ready Marts**: Build pre-aggregated mart tables optimized for Looker dashboards
3. **Extensibility**: Design a schema that supports adding new cloud providers without restructuring existing tables
4. **Performance**: Leverage BigQuery partitioning and clustering for efficient queries
5. **Cost Traceability**: Enable drill-down from high-level summaries to line-item details
6. **Deep Analysis Support**: Retain sufficient raw fields in stg layer to support instance-level optimization, SP/RI utilization analysis, and discount rate calculations

### 1.2 Assumptions

#### Data Scope

| Cloud | Source Tables | Time Range |
|-------|---------------|------------|
| AWS | CUR 2.0: `aws_billing_no_payer` | >= 2025-10-01 |
| AWS | CUR 1.0: `aws_nonprod_billing`, `aws_prod_billing` | < 2025-10-01 (Future) |
| GCP | `gcp_billing_export_v1_01D088_*` | >= 2025-11-20 (Future) |
| GCP | `gcp_billing_export_v1_01A169_*` | < 2025-11-20 (Future) |
| Azure | `azure_billing_cost_YYYYMM01` | >= 2025-04-01 (Future) |
| Alibaba | `daily_en_*`, `monthly_en_*` | >= 2025-12-01 (Future) |
| Alibaba | `daily_*`, `monthly_*` (Legacy CN) | < 2025-12-01 (Future) |

#### Cost Semantics

| Cloud | Primary Cost Field | Net Cost Calculation |
|-------|-------------------|---------------------|
| AWS | `line_item_unblended_cost` | Direct use (includes discount rows) |
| GCP | `cost` | `cost + SUM(credits.amount)` |
| Azure | `costInUsd` | Direct use |
| Alibaba | `amount_after_discount` | Direct use (already net) |

#### Currency

- All cost values normalized to USD
- AWS/Azure already in USD
- GCP in USD (billing currency)
- Alibaba requires exchange rate conversion (CNY to USD)

#### BigQuery Location Constraints (Important)

BigQuery datasets are tied to a **location** (region or multi-region). **Cross-location queries are not supported**, which impacts the Phase 3 "multi-cloud UNION ALL" plan.

Current known locations:
- AWS CUR 2.0 (`gcp-digital-bi.aws_billing_no_payer`): `us-west2`
- Alibaba / Azure raw billing datasets: `us-west2` (planned)
- GCP billing export (`gcp-digital-bi.gcp_billing_v2`): `US` (multi-region)
- dbt target dataset (`gcp-rd-research-dev.finops_cloud_billing`): `us-west2`

Implications:
- Phase 3 "unified" models (UNION across clouds) require all inputs to be available in the **same location** (e.g. replicate GCP export into `us-west2`, or move analytics/marts into `US`, or publish per-location marts and replicate only aggregated results for cross-cloud dashboards).
- **MVP Phase 1 focuses on AWS only** to unblock cost dashboards first; multi-cloud unification is deferred until a location strategy is decided and implemented.

---

## 2. Architecture

### 2.1 dbt Three-Layer Architecture

The platform uses **dbt (data build tool)** for modular development, version control, and incremental builds. The architecture follows dbt best practices with three transformation layers:

```
+------------------------------------------------------------------+
|                     SOURCE (Raw Data)                             |
|  dbt: _sources.yml configuration                                  |
+------------------------------------------------------------------+
|  gcp-digital-bi.aws_billing_no_payer.aws_billing_*               |
|  gcp-digital-bi.gcp_billing_v2          (Future)                  |
|  gcp-digital-bi.azure_billing_cost_*    (Future)                  |
|  gcp-digital-bi.alibaba_cloud           (Future)                  |
+------------------------------------------------------------------+
                                |
                                v
+------------------------------------------------------------------+
|                     STAGING LAYER                                 |
|  dbt: models/staging/ | Materialization: VIEW                     |
|  Purpose: 1:1 clean, rename, type cast - NO joins/aggregations    |
+------------------------------------------------------------------+
|  stg_aws_cost                                                     |
|  - Standardize column names                                       |
|  - Type casting (timestamp → date)                                |
|  - Simple COALESCE for NULLs                                      |
|  - Preserve all rows (for audit)                                  |
+------------------------------------------------------------------+
                                |
                                v
+------------------------------------------------------------------+
|                   INTERMEDIATE LAYER                              |
|  dbt: models/intermediate/ | Materialization: VIEW                |
|  Purpose: Separate usage analytics and invoice semantics            |
+------------------------------------------------------------------+
|  int_aws_billing_ledger                                           |
|  - Keep all row types (Usage/Fee/Tax/Credit/Discount/Refund)      |
|  - Join account_mapping + SCD2 attribution                         |
|  - Add is_usage_row and SP/RI-aware effective_usage_cost_usd       |
|                                                                   |
|  int_aws_billing                                                  |
|  - Filter to Usage rows only (Usage, SavingsPlanCoveredUsage)     |
|  - Usage-focused view for optimization marts                       |
|  - Clean, simplified data for downstream marts                    |
|                                                                   |
|  Phase 2+: int_billing_with_tags (tag extraction, attribution)    |
|  Phase 3:  int_billing_unified (multi-cloud UNION ALL)            |
+------------------------------------------------------------------+
                                |
                                v
+------------------------------------------------------------------+
|                      MARTS LAYER                                  |
|  dbt: models/marts/ | Materialization: incremental (insert_overwrite) |
|  Purpose: Aggregated tables for BI - partitioned by month/day     |
+------------------------------------------------------------------+
|  Core Marts (MVP):                                                |
|  - mart_cost_monthly (detail mart, 17-dim grain)                 |
|    Grain: month + region + service + account + tags              |
|    (usage-only, SP/RI-aware effective cost + list-cost view)      |
|  - mart_cost_summary (dashboard mart, 11-dim grain)              |
|    Re-aggregated from detail mart for Superset queries            |
|                                                                   |
|  Extended Marts (Phase 2+):                                       |
|  - mart_cost_monthly_by_account                                   |
|  - mart_cost_daily_trend                                          |
|  - mart_cost_by_tenant                                            |
+------------------------------------------------------------------+
                                |
                                v
+------------------------------------------------------------------+
|                   BI / VISUALIZATION LAYER                        |
+------------------------------------------------------------------+
|                                                                   |
|  +------------------------------------------------------------+   |
|  |  LOOKER (Quick Validation)                                 |   |
|  |  - Rapid prototyping and data validation                   |   |
|  |  - Connects to Marts only - no complex SQL in BI           |   |
|  +------------------------------------------------------------+   |
|                                                                   |
|  +------------------------------------------------------------+   |
|  |  SUPERSET (Production Dashboard)                           |   |
|  |  - Final integrated visualization platform                 |   |
|  |  - Production dashboards for end users                     |   |
|  +------------------------------------------------------------+   |
|                                                                   |
+------------------------------------------------------------------+
```

### 2.2 Why Intermediate Layer?

| Without Intermediate | With Intermediate |
|---------------------|-------------------|
| Staging becomes too wide (flatten all JSON) | Staging stays 1:1 with source |
| Marts recalculate JSON every run (expensive) | Logic processed once in Intermediate |
| Business logic scattered across marts | Centralized filtering + attribution logic |
| Hard to test complex mappings | Isolated testable layer |

### 2.3 Cost Scope and Dual Perspective

**Focus on Usage Rows Only** - Filter to actual resource consumption:

```sql
WHERE line_item_type IN ('Usage', 'SavingsPlanCoveredUsage')
```

**Dual Cost Perspective:**

| Metric | Field | Purpose | User |
|--------|-------|---------|------|
| Financial View | `actual_cost_usd` | What we actually pay | Finance, Budget |
| R&D View | `list_cost_usd` | Resource consumption at list price | Engineering, Optimization |

**Key Calculated Metrics (Intermediate Layer):**
- `savings_amount`: list_cost_usd - actual_cost_usd
- `savings_rate`: savings_amount / list_cost_usd (0.0 to 1.0)
- `instance_family`: Extracted from instance_type (e.g., m5 from m5.large)

**Mart Stored Metrics:**
- `actual_cost_usd`: SUM(actual_cost_usd)
- `list_cost_usd`: SUM(list_cost_usd)
- `sp_covered_list_cost_usd`: SUM(sp_covered_list_cost_usd)

**BI Layer Derived Metrics** (not stored in mart, compute at query time):
- `savings_amount`: SUM(list_cost_usd) - SUM(actual_cost_usd)
- `weighted_savings_rate`: savings_amount / SUM(list_cost_usd)
- `sp_coverage_rate`: SUM(sp_covered_list_cost_usd) / SUM(list_cost_usd)

**Excluded from Marts (kept in stg for audit):**
- Tax, Fee, Refund, Credit, Discount rows

> [!NOTE]
> Usage-only marts are optimized for resource consumption analysis. They are not a full invoice/ledger view (which needs Tax/Fee/Credit/Discount rows).

### 2.4 BigQuery Dataset Organization

```
gcp-digital-bi (project) - Raw data (Sources)
├── aws_billing_no_payer     [existing - raw CUR 2.0, us-west2]
├── gcp_billing_v2           [existing - raw GCP, US]        (Future)
├── azure_billing            [existing - raw Azure, us-west2] (Future)
└── alibaba_cloud            [existing - raw Alibaba, us-west2] (Future)

gcp-rd-research-dev (project) - Analytics (dbt target)
└── finops_cloud_billing     [dbt project output, us-west2]
    ├── stg_aws_cost              (Staging VIEW)
    ├── int_aws_billing_ledger    (Intermediate VIEW, all row types)
    ├── int_aws_billing           (Intermediate VIEW)
    ├── mart_cost_monthly         (Mart TABLE, incremental)
    ├── mart_cost_summary         (Mart TABLE, incremental)
    └── ... (additional models)
```

### 2.5 dbt Project Structure

```
dbt/
├── dbt_project.yml               # Project configuration
├── profiles.yml                  # BigQuery connection
├── models/
│   ├── staging/
│   │   ├── _sources.yml          # Raw table definitions
│   │   ├── _stg_models.yml       # Schema & tests
│   │   └── stg_aws_cost.sql      # 1:1 clean from raw
│   ├── intermediate/
│   │   ├── _int_models.yml       # Schema & tests
│   │   ├── int_aws_billing_ledger.sql  # All charge types + attribution
│   │   └── int_aws_billing.sql   # Usage-only from ledger
│   └── marts/
│       └── core/
│           ├── _mart_models.yml
│           ├── mart_cost_monthly.sql
│           └── mart_cost_summary.sql
├── seeds/
│   ├── _seeds.yml
│   └── account_mapping.csv               # Account ID → cost_center/owner mapping (SCD Type 2)
├── analyses/
│   ├── unmapped_accounts.sql             # Detect accounts not in mapping
│   └── validate_stg_vs_raw.sql           # Validate staging vs raw data
└── tests/
    ├── assert_ledger_preserves_staging_totals.sql   # Ensure ledger preserves staging rows/cost
    ├── assert_cost_totals_match.sql              # Verify mart totals match intermediate
    └── assert_no_overlapping_account_mappings.sql  # Validate SCD Type 2 date ranges
```

---

## 3. Data Model

### 3.1 Current Implementation (39 Fields)

The staging layer uses a 39-field schema (30 Unified + 9 AWS-Specific) supporting dual-cost perspective and deep analysis.

**Field Groups:**

| Category | Field Count | Fields |
|----------|-------------|--------|
| Identity | 6 | cloud_provider, usage_date, billing_period_month, account_id, account_name, payer_account_id |
| Dimensions | 6 | service, service_category, region, availability_zone, resource_id, instance_type |
| Cost (Dual Perspective) | 4 | actual_cost_usd, list_cost_usd, usage_amount, usage_unit |
| Classification | 4 | line_item_type, charge_type, pricing_model, pricing_term |
| Tags | 8 | tags, tag_environment, tag_service_type, tag_usedby, tag_component, tag_tenant, tag_project, tag_cluster |
| AWS-Specific | 9 | aws_product_code, aws_operation, aws_usage_type, aws_line_item_desc, aws_sku_id, aws_savings_plan_arn, aws_reservation_arn, aws_sp_effective_cost, aws_amortized_cost |
| Metadata | 2 | _source_table, _row_id |
| **Total** | **39** | |

**Dual Cost Perspective:**

| Field | Purpose | Source |
|-------|---------|--------|
| `actual_cost_usd` | Financial view - what we actually pay | `line_item_unblended_cost` |
| `list_cost_usd` | R&D view - resource consumption at list price | `pricing_public_on_demand_cost` |

**Intermediate Layer Calculated Fields:**
- `savings_amount` = list_cost_usd - actual_cost_usd
- `savings_rate` = savings_amount / list_cost_usd
- `instance_family` = REGEXP_EXTRACT(instance_type, r'^([a-z]+[0-9]+)')

### 3.2 Multi-Cloud Field Strategy

| Category | Unified (30) | Cloud-Specific |
|----------|--------------|----------------|
| Identity | 6 fields | - |
| Dimensions | 6 fields | - |
| Cost | 4 fields | - |
| Classification | 4 fields | - |
| Tags | 8 fields | - |
| Metadata | 2 fields | - |
| AWS-Specific | - | 9 fields (SP/RI analysis) |
| GCP-Specific | - | TBD (committed use discounts) |
| Azure-Specific | - | TBD (reservations) |

**List Cost Strategy by Cloud:**

| Cloud | list_cost_usd Source | Strategy |
|-------|---------------------|----------|
| AWS | `pricing_public_on_demand_cost` | Direct use |
| GCP | `cost_at_list` | COALESCE(cost_at_list, cost) |
| Azure | `paygCostInBillingCurrency` | COALESCE(payg, costInUsd) |
| Alibaba | N/A | Use actual_cost, mark list_cost_available = FALSE |

### 3.3 Multi-Cloud Field Mapping

| Unified Field | AWS CUR 2.0 | GCP | Azure | Alibaba |
|---------------|-------------|-----|-------|---------|
| cloud_provider | `'aws'` | `'gcp'` | `'azure'` | `'alibaba'` |
| usage_date | `DATE(line_item_usage_start_date)` | `DATE(usage_start_time)` | `date` | `partition_date` |
| account_id | `line_item_usage_account_id` | `project.id` | `subscriptionId` | `owner_account_id` |
| account_name | `line_item_usage_account_name` | `project.name` | `subscriptionName` | `owner_account_name` |
| service | `product_servicecode` ¹ | `service.description` | `consumedService` | `product_name` |
| region | `COALESCE(product.region, product_region_code, 'global')` | `COALESCE(location.region, 'global')` | `resourceLocation` | `region` |
| actual_cost_usd | `line_item_unblended_cost` | `cost` | `costInUsd` | `amount_after_discount / rate` |
| instance_type | `product_instance_type` | Parse from SKU | Parse from meter | `instance_spec` |
| tags | Transform `resource_tags` | Transform `labels` | `tags` (JSON) | `instance_tag` |

> ¹ **AWS `service` field**: We use `product_servicecode` rather than `line_item_product_code`.
> `product_servicecode` provides finer-grained service attribution — e.g., EC2-generated
> Data Transfer costs are classified as `AWSDataTransfer`, not `AmazonEC2`. Per-service
> totals will differ from AWS Cost Explorer's "Service" dimension (which uses
> `line_item_product_code`), but the cross-service grand total is identical.

### 3.4 Account Mapping Seed

Cloud billing data lacks business attributes such as `cost_center`, `owner`, and `account_purpose`. We use a **dbt seed table** with **SCD Type 2** pattern to:

1. **Add business attributes**: Map all cloud accounts to cost centers and owners for chargeback reporting
2. **Improve readability**: Provide human-readable display names (especially for Alibaba Cloud, which uses admin emails as `account_name`)
3. **Support historical attribution**: Track organizational changes over time

> [!IMPORTANT]
> All cloud accounts (AWS, GCP, Azure, Alibaba) must be maintained in this mapping table when cost_center or owner attribution is required.

**Seed File: `seeds/account_mapping.csv`**

| Field | Description |
|-------|-------------|
| `cloud_provider` | Cloud provider identifier (aws, gcp, azure, alibaba) |
| `account_id` | Original account/project/subscription ID |
| `account_display_name` | Human-readable display name for the account |
| `account_purpose` | `Production` / `Development` / `Others` (non-R&D like IT, Presales) |
| `owner` | Team or individual responsible for this account |
| `cost_center` | Cost center code for financial reporting |
| `access_level` | Row-level security: `public` (all employees) or `restricted` (authorized only) |
| `effective_from` | Start date of this mapping (inclusive) |
| `effective_to` | End date of this mapping (inclusive), use `9999-12-31` for current |

**Handling Organizational Changes:**

When `cost_center` or `owner` changes due to organizational restructuring:

```csv
# Old attribution (close out the old record)
alibaba,1234567890,Alibaba-Prod,Production,infra-team,CC-001,restricted,2025-01-01,2025-12-31
# New attribution (add new record)
alibaba,1234567890,Alibaba-Prod,Production,platform-team,CC-002,restricted,2026-01-01,9999-12-31
```

**Usage in Intermediate Layer:**

```sql
SELECT 
    b.*,
    COALESCE(m.account_display_name, b.account_name) AS account_display_name,
    COALESCE(m.account_purpose, 'Others') AS account_purpose,
    COALESCE(m.cost_center, 'UNMAPPED') AS cost_center,
    COALESCE(m.access_level, 'public') AS access_level
FROM {{ ref('stg_xxx_cost') }} b
LEFT JOIN {{ ref('account_mapping') }} m
    ON b.cloud_provider = m.cloud_provider
    AND b.account_id = m.account_id
    AND b.usage_date >= m.effective_from
    AND b.usage_date <= m.effective_to
```

**Guardrail:** Do not use `_row_id`-based `ROW_NUMBER` to force `rank = 1` during this join. `_row_id` is a staging lineage identifier and is not guaranteed globally unique in CUR source data. Overlap/fan-out issues should fail tests (`assert_no_overlapping_account_mappings`, `assert_ledger_preserves_staging_totals`) instead of silently dropping rows. Intermediate models intentionally do not expose `_row_id`.

**Handling Unmapped Accounts:**

When new cloud accounts are created frequently, use the following strategy:

| Strategy | Implementation |
|----------|----------------|
| **Default fallback** | `COALESCE` provides sensible defaults: `account_purpose='Others'`, `access_level='public'` |
| **Detection report** | Run `analyses/unmapped_accounts.sql` periodically to find accounts needing mapping |
| **Safe behavior** | Unmapped accounts default to `public` access, ensuring visibility while awaiting mapping |

> [!TIP]
> Run `dbt compile --select analyses/unmapped_accounts` and execute the compiled SQL in BigQuery to get a list of unmapped accounts sorted by cost.

**Row-Level Security Implementation:**

The `access_level` field enables data visibility control based on account sensitivity:

| access_level | Visibility | Example Accounts |
|--------------|------------|------------------|
| `public` | All company employees | Development, Testing, Sandbox |
| `restricted` | Authorized teams only (FinOps, Infra) | Production, Staging |

**Option 1: BigQuery Row-Level Security (Recommended for direct queries)**

```sql
-- Policy for restricted data (authorized users only)
CREATE ROW ACCESS POLICY restricted_data_policy
ON `project.finops_cloud_billing.mart_cost_monthly_by_account`
GRANT TO ('group:finops-admins@company.com', 'group:infra-team@company.com')
FILTER USING (access_level IN ('public', 'restricted'));

-- Policy for public data (all authenticated users)
CREATE ROW ACCESS POLICY public_data_policy
ON `project.finops_cloud_billing.mart_cost_monthly_by_account`
GRANT TO ('allAuthenticatedUsers')
FILTER USING (access_level = 'public');
```

**Option 2: BI Layer Filters**

| BI Tool | Implementation |
|---------|----------------|
| Looker | User Attributes + `sql_always_where` with access_level filter |
| Superset | Row Level Security with access_level-based filter |

**Benefits:**
- **Historical accuracy**: Usage dates correctly reflect organizational structure at that time
- **Easy maintenance**: CSV file in git, human-editable
- **Cross-cloud unified**: All cloud accounts in one file
- **Graceful fallback**: `COALESCE` ensures unmapped accounts still have display names
- **Security-ready**: access_level enables row-level security at multiple layers

---

## 4. Mart Layer Design

### 4.1 Core Marts (MVP)

**mart_cost_monthly** (detail mart)
- Purpose: Finest-grain usage mart for analytics and drill-down.
- Materialization: incremental (`insert_overwrite`, 3-month lookback).
- Grain (17 dimensions):
  - billing_period_month + cloud_provider
  - region + service + service_category
  - account dimensions (`account_id`, `account_display_name`, `account_purpose`, `cost_center`, `access_level`, `account_owner`, `payer_account_id`)
  - 7 extracted tag columns (`tag_environment`, `tag_service_type`, `tag_usedby`, `tag_component`, `tag_tenant`, `tag_project`, `tag_cluster`)
- Base metrics (stored):
  - `actual_cost_usd` (SP/RI-aware effective usage cost)
  - `list_cost_usd` (list-price resource consumption)
  - `sp_covered_list_cost_usd` (list-cost amount covered by Savings Plans)
- Derived metrics (compute in BI layer, not stored):
  - `savings_amount` = `list_cost_usd - actual_cost_usd`
  - `weighted_savings_rate` = `savings_amount / list_cost_usd`
  - `sp_coverage_rate` = `sp_covered_list_cost_usd / list_cost_usd`
- Removed from grain (v2.11): `tags` JSON (row explosion), `account_name` (redundant), `usage_unit`/`usage_amount` (row bloat).
- Why derived metrics are not stored: rate columns are not additive — `SUM(rate)` or `AVG(rate)` across rows produces wrong results. BI layer must recompute from base metrics.

**mart_cost_summary** (dashboard mart)
- Purpose: Pre-aggregated mart for Superset dashboard queries, re-aggregated from `mart_cost_monthly`.
- Materialization: incremental (`insert_overwrite`, 3-month lookback).
- Grain (11 dimensions):
  - billing_period_month + cloud_provider
  - region + service + service_category
  - account_id + account_display_name + account_purpose + cost_center + account_owner
  - tag_project
- Same base metrics as detail mart (3 stored columns).

### 4.2 Extended Marts (Phase 2+)

| Mart | Purpose | Key Dimensions |
|------|---------|----------------|
| mart_cost_monthly_by_account | Account/project-level chargeback | account_id, payer_account_id |
| mart_cost_daily_trend | Anomaly detection, forecasting | usage_date |
| mart_cost_by_instance_type | Rightsizing recommendations | instance_type, instance_family |
| mart_sp_utilization | Savings Plan utilization tracking | commitment_arn |
| mart_ri_utilization | Reserved Instance utilization | commitment_arn, service |
| mart_discount_analysis | Discount rate analysis | pricing_model, service |
| mart_cost_by_tenant | Multi-tenant cost allocation | tag_tenant, tag_environment |

### 4.3 Aggregation Rules

To prevent double counting:

| Cloud | Strategy |
|-------|----------|
| AWS | SUM(line_item_unblended_cost) - discounts have negative values |
| GCP | SUM(cost) - credits are separate rows with negative cost |
| Azure | costInUsd is already net cost |
| Alibaba | amount_after_discount is already net |

### 4.4 Metric Notes

These notes are important for multi-cloud and deeper optimization use cases.

- **Only base metrics stored in marts**: Marts store `actual_cost_usd`, `list_cost_usd`, `sp_covered_list_cost_usd`. Derived metrics (`savings_amount`, `weighted_savings_rate`, `sp_coverage_rate`) are computed in the BI layer to avoid non-additive column misuse.
- **Rate metrics must be recomputed at query grain**: `weighted_savings_rate = SUM(list_cost_usd - actual_cost_usd) / SUM(list_cost_usd)`. Never SUM or AVG pre-computed rates — they are not additive.
- **dual cost perspective**: Marts include both `actual_cost_usd` (financial) and `list_cost_usd` (R&D) to support different stakeholder views. BI layer can switch based on user role.
- **usage vs invoice views**: keep usage-only marts for optimization, and add separate invoice/ledger marts (include Tax/Fee/Credit/Discount) when financial reconciliation is required.
- **model split for clarity**: `int_aws_billing` feeds usage marts, while `int_aws_billing_ledger` is the source for future finance/invoice marts.

---

## 5. Partition & Clustering Strategy

### Staging Tables

```sql
PARTITION BY usage_date
CLUSTER BY cloud_provider, service, account_id
```

### Mart Tables

```sql
-- Monthly marts
PARTITION BY billing_period_month
CLUSTER BY cloud_provider, <primary_dimension>

-- Daily trend mart
PARTITION BY usage_date
CLUSTER BY cloud_provider
```

**Benefits:**
- Efficient time-range filtering via partitions
- Fast per-cloud queries via clustering
- Clustering by key dimensions speeds up common aggregations

---

## 6. Data Validation Strategy

### Staging Layer Validation

| Check | Expected Result |
|-------|-----------------|
| Total cost matches raw | Diff < $0.01 per month |
| No NULL cloud_provider | 0 rows |
| Date range correct | Within expected boundaries |
| All charge_types mapped | No significant 'Other' |
| Tag JSON valid | No parse errors |

### Mart Layer Validation

| Check | Expected Result |
|-------|-----------------|
| Mart total = stg total | Diff = $0.00 per month |
| No duplicate grain | COUNT(*) = COUNT(DISTINCT pk) |
| Pricing model breakdown | Sum of models = total_cost |

---

## 7. Looker Integration

### Connection Setup

- Connect to `gcp-rd-research-dev` project
- Dataset: `finops_cloud_billing`
- Location: `us-west2`
- Role: BigQuery Data Viewer

### Recommended Calculated Fields

```sql
-- Savings Amount (derived from base metrics)
list_cost_usd - actual_cost_usd AS savings_amount

-- Savings Rate (compute at query grain, never store pre-computed)
SAFE_DIVIDE(SUM(list_cost_usd) - SUM(actual_cost_usd), SUM(list_cost_usd)) * 100

-- SP Coverage Rate
SAFE_DIVIDE(SUM(sp_covered_list_cost_usd), SUM(list_cost_usd)) * 100

-- Actual vs List Cost Ratio
SAFE_DIVIDE(actual_cost_usd, list_cost_usd) * 100
```

### Dashboard Templates

- Cost by Region (geographic distribution)
- Cost by Service (top cost drivers)
- Cost by Account (chargeback)
- Daily Trend (anomaly detection)
- Pricing Model Mix (optimization opportunities)

---

## 8. dbt Integration

### 8.1 Why dbt?

| Benefit | Description |
|---------|-------------|
| **Version Control** | SQL models in git, full change history |
| **Modularity** | Reusable macros, ref() for dependencies |
| **Incremental Builds** | Only process new/changed data, reduce BigQuery costs |
| **Testing** | Built-in data quality tests |
| **Documentation** | Auto-generated docs and lineage |

### 8.2 dbt Configuration

**dbt_project.yml:**
```yaml
name: 'finops_cloud_billing'
version: '1.0.0'

vars:
  billing_start_date: '2025-10-01'

models:
  finops_cloud_billing:
    staging:
      +materialized: view
      +schema: finops_cloud_billing
    intermediate:
      +materialized: view
      +schema: finops_cloud_billing
    marts:
      +materialized: table
      +schema: finops_cloud_billing
      +partition_by:
        field: billing_period_month
        data_type: date
```

### 8.3 Incremental Strategy for Late-Arriving Data

Cloud billing data often has corrections and late-arriving entries (AWS CUR corrections typically arrive within 1-2 billing cycles). Both core marts use a 3-month lookback strategy:

```sql
{{ config(
    materialized='incremental',
    partition_by={'field': 'billing_period_month', 'data_type': 'date'},
    cluster_by=['cloud_provider', 'account_id', 'service', 'region'],
    incremental_strategy='insert_overwrite',
    on_schema_change='append_new_columns'
) }}

SELECT ...
FROM {{ ref('int_aws_billing') }}
{% if is_incremental() %}
WHERE billing_period_month >= DATE_SUB(
    (SELECT MAX(billing_period_month) FROM {{ this }}),
    INTERVAL {{ var('mart_lookback_months') }} MONTH
)
{% endif %}
```

**Configuration:** `mart_lookback_months: 3` (set in `dbt_project.yml`).

**Run modes:**
- `dbt build` — incremental, only rebuilds the most recent 3 monthly partitions.
- `dbt build --full-refresh` — full rebuild, for backfill or schema changes.

### 8.4 dbt Commands

```bash
# Run all models
dbt run

# Run specific model and dependencies
dbt run --select mart_cost_monthly+

# Test data quality
dbt test

# Generate documentation
dbt docs generate && dbt docs serve
```

---

## 9. Roadmap

### Phase 1: AWS MVP (Current)

- **dbt-first approach** with 3-layer architecture
- Data: AWS CUR 2.0 >= 2025-10-01
- Staging: `stg_aws_cost` (VIEW, all rows)
- Intermediate: `int_aws_billing_ledger` (VIEW, all row types) + `int_aws_billing` (VIEW, usage-only)
- Marts: `mart_cost_monthly` (single usage mart in MVP)
- Status: **In Progress**

### Phase 2: Extended Analytics

- [x] Enable incremental builds with lookback (3-month `insert_overwrite`)
- [x] Add `mart_cost_summary` pre-aggregated dashboard mart
- [x] Slim `mart_cost_monthly` grain (remove tags JSON, account_name, usage_unit/usage_amount)
- Add tag-based cost allocation marts (`mart_cost_by_tenant`, `mart_cost_by_environment`)
- Tags already extracted in stg_aws_cost (7 named tags + full JSON)
- Add `mart_cost_daily_trend`, `mart_cost_monthly_by_account`

### Phase 3: Multi-Cloud Support

- Resolve BigQuery location alignment (GCP export is in `US`, current analytics is `us-west2`)
- Add `stg_gcp_cost`, `stg_azure_cost`
- Create stg_cost_unified (UNION ALL)
- Update all marts to query unified view

### Phase 4: Advanced Analysis

- SP/RI utilization marts
- Discount analysis marts
- Tenant cost allocation marts
- Full 39-field schema

---

## Appendix A: Service Category Mapping

| Category | AWS | GCP | Azure | Alibaba |
|----------|-----|-----|-------|---------|
| Compute | AmazonEC2, AWSLambda | Compute Engine, Cloud Functions | Virtual Machines | ECS |
| Storage | AmazonS3, AmazonEBS | Cloud Storage, Persistent Disk | Blob Storage | OSS |
| Database | AmazonRDS, AmazonDynamoDB | Cloud SQL, Bigtable | SQL Database | RDS |
| Network | AWSDataTransfer, AmazonVPC | VPC, Cloud Interconnect | Virtual Network | VPC |
| Kubernetes | AmazonEKS | GKE | AKS | ACK |

---

## Appendix B: Tag Key Mapping

| Semantic | AWS Key | GCP Label | Azure Tag | Alibaba Tag |
|----------|---------|-----------|-----------|-------------|
| Tenant ID | `user:tenant` | `tenant` | `tenant` | `tenant_id` |
| Environment | `user:env`, `user:environment` | `env` | `environment` | `env` |
| Service Type | `user:servicetype` | `service_type` | `servicetype` | `service_type` |
| Used By | `user:usedby` | `owner` | `usedby` | `owner` |
| Component | `user:component` | `component` | `component` | `component` |
| Cluster ID | `user:cluster` | `cluster` | `cluster` | `cluster_id` |
| Project | `user:project` | `project` | `project` | `project` |

---

## Appendix C: Key Design Decisions

### Preserving line_item_type

Keep both original `line_item_type` and mapped `charge_type` to enable detailed SP/RI coverage analysis.

### Handling 'global' Region

Use `COALESCE(product.region, product_region_code, 'global')` - `product_region_code` is NULL for Data Transfer and VPC rows; `product.region` from the RECORD provides better coverage.

### View vs Materialized View

Use regular VIEW for stg layer (no storage cost, always fresh), convert to MATERIALIZED VIEW if performance requires.

### Enterprise Discounts (pricing_model = 'Discount')

Separate enterprise-level discounts (EDP, PrivateRate, BundledDiscount) from OnDemand to prevent negative values polluting cost metrics.

---

## Related Documents

- [MVP Phase 1 Implementation](./finops-mvp-phase1.md) - Detailed implementation guide for AWS CUR 2.0
- [Deployment Guide](./finops-deployment-guide.md) - Operational procedures for deploying the solution
- [Superset AWS MVP Deployment Guide](./superset-aws-mvp-deployment-guide.md) - Superset 6.0 deployment and dashboard operations

---

*Document Status: Active*
*Last Updated: 2026-03-02*