# dbt

dbt project for FinOps multi-cloud billing analytics. This project transforms raw billing data from AWS, GCP, Azure, and Alibaba Cloud into unified, analysis-ready data marts.

## Quick Start

```bash
# 1. Install dbt-bigquery
pip install dbt-bigquery

# 2. Configure profiles.yml
cp profiles.yml.example ~/.dbt/profiles.yml
# Edit ~/.dbt/profiles.yml with your GCP credentials

# 3. Test connection
dbt debug

# 4. Build all (seeds + models + tests)
dbt build

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

> [!IMPORTANT]
> Always use `dbt build` instead of separate `dbt run` commands to ensure seeds are loaded before models that depend on them.

## Project Structure

```
dbt/
├── dbt_project.yml                 # Project configuration
├── profiles.yml.example            # BigQuery connection template
├── models/
│   ├── staging/                    # 1:1 clean from source (VIEW)
│   │   ├── _sources.yml            # Raw table definitions
│   │   ├── _stg_models.yml         # Schema & tests
│   │   └── stg_aws_cost.sql        # AWS CUR 2.0 staging
│   ├── intermediate/               # Filters + calculations (VIEW)
│   │   ├── _int_models.yml         # Schema & tests
│   │   ├── int_aws_billing_ledger.sql  # All charge types + account_mapping join
│   │   └── int_aws_billing.sql     # Usage-only view from ledger
│   └── marts/core/                 # Aggregated tables (TABLE)
│       ├── _mart_models.yml        # Schema & tests
│       └── mart_cost_monthly.sql   # Unified usage mart
├── seeds/
│   ├── _seeds.yml                  # Seed schema definitions
│   └── account_mapping.csv         # Account → 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_mart_cost_monthly_unique_grain.sql  # Ensure mart grain uniqueness
    └── assert_no_overlapping_account_mappings.sql  # Validate SCD Type 2 date ranges
```

## Model Summary

| Model | Layer | Type | Purpose |
|-------|-------|------|---------|
| `stg_aws_cost` | Staging | VIEW | 1:1 clean from raw, all rows preserved (39 fields) |
| `account_mapping` | Seed | TABLE | Account to cost_center/owner/access_level mapping (SCD Type 2) |
| `int_aws_billing_ledger` | Intermediate | VIEW | All row types for invoice/reconciliation + attribution |
| `int_aws_billing` | Intermediate | VIEW | Usage-only view for optimization marts |
| `mart_cost_monthly` | Mart | TABLE | Unified usage mart (region + service + account + tags + usage_unit) |

## Key Commands

| Command | Purpose |
|---------|---------|
| `dbt build` | **RECOMMENDED** - Run seeds + models + tests in order |
| `dbt seed --full-refresh` | Reload all seed data (for account_mapping updates) |
| `dbt run --select marts` | Run only mart models |
| `dbt test` | Run all data quality tests |
| `dbt compile --select analyses/unmapped_accounts` | Compile analysis query |

## Target Dataset

- **Project**: `gcp-rd-research-dev`
- **Dataset**: `finops_cloud_billing`
- **Location**: `us-west2`

## Documentation

For detailed documentation, see:

- [MVP Phase 1 Implementation](design/finops-mvp-phase1.md) - Schema and implementation details
- [System Design](design/finops-system-design.md) - Architecture overview
- [Deployment Guide](design/finops-deployment-guide.md) - Operational procedures
- [Superset AWS MVP Deployment Guide](design/superset-aws-mvp-deployment-guide.md) - Superset 6.0 deployment, BigQuery connection, and dashboard setup