# DBT Multi-Cloud Cost Modeling Implementation Guide

Implement in the order AWS -> GCP -> Alibaba Cloud -> Azure. The target output is a unified cost fact table at the grain: company -> cloud vendor -> department -> account -> service -> resource, for BI tools like Superset.

## Directory and naming conventions

```
models/
  staging/{aws,gcp,alibaba,azure}/stg_*.sql
  intermediate/{aws,gcp,alibaba,azure}/int_*_cost.sql
  dims/dim_org_unit.sql
  dims/dim_account.sql
  dims/dim_service.sql
  dims/dim_resource_tag.sql
  facts/fct_cost_daily.sql
  facts/fct_cost_monthly.sql
  marts/mart_superset_cost_overview.sql
macros/partition_filter.sql
macros/pivot_tags.sql
seeds/dim_org_unit.csv
seeds/dim_account_map.csv
seeds/dim_service_map.csv
```

> Materialization strategy: use `incremental` for staging/intermediate. Fact tables can be `table`. For incremental loads, use partition overwrite (insert_overwrite).

## Unified field conventions (core)

- Time: `usage_date` (daily grain), `usage_month` (monthly grain).
- Account: `payer_account_id` (when applicable), `account_id` (linked/subscription/project).
- Service: `service`, `service_family`, `sku`.
- Cost: `cost_unblended` (list/unblended), `cost_amortized` (RI/SP amortized), `cost_net` (net, includes credits), `currency`, `credits`.
- Ownership: `org_unit_id`, `env`, `owner` (from `dim_account` or tag mapping).
- Tags: `tags_flat` or key/value structure. Resource identifiers `resource_id`/`project_id` depend on the cloud provider.

## Macros and partition filtering

- `macros/partition_filter.sql`: add partition conditions per cloud vendor to avoid full scans.
- `macros/pivot_tags.sql`: flatten tags from JSON or columns. Can return EAV (key/value) or a pivoted whitelist.

### Example: partition filter macro (AWS Hive year/month)

```sql
{% macro aws_partition_filter(year_from=2024, month_from=1) -%}
  year >= {{ year_from }} AND month >= {{ month_from }}
{%- endmacro %}
```

> If using the consolidated billing dataset `aws_billing_no_payer`, switch to a date partition filter: `{% macro aws_unified_partition_filter(days_back=90) -%} line_item_usage_end_date >= date_sub(current_date(), interval {{ days_back }} day) {%- endmacro %}`.

## Implementation order and key points

### 1. AWS (aws_prod_billing, aws_nonprod_billing, aws_billing_no_payer)
- **Partitioning**: external tables use Hive `year`, `month`; consolidated billing uses daily partition `line_item_usage_end_date`.
- **stg_aws_cur.sql**
  - Fields: `payer_account_id = bill_payer_account_id`, `account_id = line_item_usage_account_id`.
  - Time: `usage_date = DATE(line_item_usage_start_date)`; `usage_month = DATE_FROM_UNIX_DATE(...)` based on `year/month`.
  - Cost: `cost_unblended = line_item_unblended_cost`, `cost_amortized = savings_plan_savings_plan_effective_cost` (if present) or `reservation_effective_cost`, `cost_net = effective_cost`.
  - Tags: flatten `resource_tags_user_*`; optionally apply `pivot_tags` whitelist (project/env/owner).
  - Incremental: external tables use `where {{ aws_partition_filter(var('aws_year_from',2024), var('aws_month_from',1)) }}`; consolidated billing uses date partition: `where line_item_usage_end_date >= date_sub(current_date(), interval {{ var('aws_days_back',90) }} day)`.
- **int_aws_cost.sql**
  - Join `dim_account` to get `org_unit_id`, `env`, `account_type` (prod/nonprod).
  - Standardize services: `service = coalesce(product_product_name, product_product_family)`; `sku = line_item_usage_type`.
  - RI/SP: compute `ri_sp_savings = cost_unblended - cost_amortized` (optional).
- **Tests**: `not_null` on `usage_date`, `account_id`; `relationships` to `dim_account`; `accepted_values` on `account_type`.

### 2. GCP (gcp_billing_v2.gcp_billing_export_v1_01A169_D3D9BF_FB82A2)
- **Partitioning**: `_PARTITIONDATE`; incremental by last 7-14 days or watermark.
- **stg_gcp_billing.sql**
  - Fields: `usage_date = DATE(usage_start_time)`; `account_id = project.id`; `service = service.description`; `sku = sku.description`.
- Cost: `cost_unblended = cost_at_list` (or `cost`), `cost_net = cost + SUM(credit.amount)`; expand `credits` via `UNNEST(credits)` and aggregate.
  - Tags: `labels/tags/system_labels` via `pivot_tags`, keep `tags_flat`.
  - Incremental: `where _PARTITIONDATE >= date_sub(current_date(), interval {{ var('gcp_days_back',14) }} day)`.
- **int_gcp_cost.sql**
  - Join `dim_account` (project -> org/owner/env) to fill `org_unit_id`.
  - FX (if needed): `cost_home = cost_net * currency_exchange_rate`.
  - Resource: keep `resource_name`, `location.region/zone` for drilldown.
- **Tests**: `not_null` on `usage_date`, `account_id`, `service`; `relationships` to `dim_account`.

### 3. Alibaba Cloud (alibaba_cloud.daily_YYYYMMDD / monthly_YYYYMM01)
- **Partitioning**: `partition_date`; monthly tables are unioned by table name.
- **stg_alibaba_daily.sql**
  - Fields: `usage_date = partition_date`; `account_id`; `service = product_code`; `resource_id = instance_id`.
  - Cost: `cost_unblended = list_price - discount_amount - coupon_deduction`, `cost_net = amount_after_discount`, `currency = pricing_currency`.
  - Tags: parse `instance_tags` JSON via `pivot_tags`; keep `resource_group`.
  - Incremental: `where partition_date >= date_sub(current_date(), interval {{ var('ali_days_back',7) }} day)`.
- **int_alibaba_cost.sql**
  - Account/department mapping: `account_id` + `financial_unit` -> `dim_account`; allow `org_unit_id` override.
  - Optional monthly aggregation for reconciliation: `stg_alibaba_monthly` + `int_alibaba_monthly`.
- **Tests**: `not_null` on `usage_date`, `account_id`, `cost_net`; `relationships` to `dim_account`.

### 4. Azure (azure_billing_cost_YYYYMM01)
- **Partitioning**: monthly tables; in staging, generate `usage_month` and use `incremental insert_overwrite`.
- **stg_azure_cost.sql**
  - Fields: `usage_date = date`; `usage_month = date_trunc(date, month)`; `account_id = billingProfileName || ':' || invoiceSectionName` (for uniqueness if needed); `service = productName`; `service_family = meterCategory`.
  - Cost: `cost_unblended = paygCostInUsd`, `cost_net = costInUsd`; keep `pricingModel`, `chargeType`.
  - Tags: parse `tags` JSON via `pivot_tags`; add `provider/tenant/usedby`.
  - Incremental: `where date >= date_sub(current_date(), interval {{ var('azure_days_back',40) }} day)`.
- **int_azure_cost.sql**
  - Account mapping: `billingProfileName/invoiceSectionName` -> `dim_account`; fill `org_unit_id`.
  - RI/SP: `pricingModel` distinguishes Reservation/Benefit; compute `ri_sp_savings` if needed.
- **Tests**: `not_null` on `usage_date`, `account_id`, `service`; `accepted_values` on `pricingModel` (PAYG/Reservation/... ).

## Dimensions and seeds

- `dim_org_unit.csv`: `org_unit_id, org_level1, org_level2, org_level3, owner, cost_center`.
- `dim_account_map.csv`: `vendor, account_id, payer_account_id, org_unit_id, env, owner, account_type` (prod/nonprod/dev).
- `dim_service_map.csv`: `vendor, service, sku, service_family, ucm_service` (unified service catalog).
- `dim_resource_tag`: can be generated from a tag whitelist (e.g. `project/env/team/owner`) or stored as EAV for downstream filtering.

## Facts and mart

- `fct_cost_daily`: union of `int_*_cost`, at least account + service grain (optionally resource/tags).
- `fct_cost_monthly`: `date_trunc(usage_date, month)` aggregation; output common metrics (net, amortized, list, credits, optional FX).
- `mart_superset_cost_overview`: BI-facing wide table with org hierarchy, accounts, service TopN, RI/SP coverage, and derived metrics.

## Validation and release steps

1. Configure `sources.yml` (four datasets) plus vars (`aws_year_from`, etc.), then run `dbt seed` to load dimension mappings.
2. Run `stg_*` (with basic tests), sample-verify last 1 week/1 month amounts against source tables.
3. Run `int_*`, check mapping hit rates (account -> org, tags -> owner/env); adjust seeds.
4. Generate `fct_cost_daily/monthly` and reconcile cross-cloud totals (same-month totals vs source tables or control views).
5. Register exposures for Superset; publish datasets and verify permissions/column naming.
6. Iteration: add FX, budget/threshold alerts, RI/SP utilization metrics, and expand data quality tests (distribution/anomaly checks).