# FinOps SQL Deployment Guide

This document provides complete deployment steps for the FinOps MVP using dbt, including BigQuery setup, seed data management, and Looker integration.

---

## Table of Contents

1. [Prerequisites](#1-prerequisites)
2. [dbt Project Setup](#2-dbt-project-setup)
3. [Seed Data Management](#3-seed-data-management)
4. [dbt Model Execution](#4-dbt-model-execution)
5. [Data Validation](#5-data-validation)
6. [Looker Configuration](#6-looker-configuration)
7. [Ongoing Maintenance](#7-ongoing-maintenance)
8. [FAQ](#8-faq)

---

## 1. Prerequisites

### 1.1 Software Requirements

```bash
# Python 3.8+ required
python --version

# Install dbt-bigquery
pip install dbt-bigquery

# Verify installation
dbt --version
```

### 1.2 Permission Requirements

```bash
# Ensure you are logged into Google Cloud
gcloud auth login
gcloud auth application-default login

# Set project
gcloud config set project gcp-rd-research-dev

# Required roles on target project (gcp-rd-research-dev):
# - roles/bigquery.dataEditor (create tables and views)
# - roles/bigquery.jobUser (run queries)

# Required roles on source project (gcp-digital-bi):
# - roles/bigquery.dataViewer (read raw billing data)
```

### 1.3 Data Source Verification

```sql
-- Confirm source data is accessible
SELECT COUNT(*) as row_count
FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_*`
WHERE line_item_usage_start_date >= TIMESTAMP('2025-10-01')
LIMIT 1;
```

---

## 2. dbt Project Setup

### 2.1 Configure profiles.yml

Create or update `~/.dbt/profiles.yml`:

```yaml
finops_cloud_billing:
  target: dev
  outputs:
    dev:
      type: bigquery
      method: oauth
      project: gcp-rd-research-dev
      dataset: finops_cloud_billing
      location: us-west2
      threads: 4
      timeout_seconds: 300
    prod:
      type: bigquery
      method: service-account
      project: gcp-rd-research-dev
      dataset: finops_cloud_billing
      location: us-west2
      keyfile: /path/to/service-account.json
      threads: 4
      timeout_seconds: 300
```

### 2.2 Verify Connection

```bash
cd dbt
dbt debug
```

Expected output: All checks should pass.

### 2.3 Create BigQuery Dataset (if not exists)

```bash
bq --location=us-west2 mk \
  --dataset \
  --description="FinOps multi-cloud billing analytics" \
  gcp-rd-research-dev:finops_cloud_billing
```

---

## 3. Seed Data Management

### 3.1 Account Mapping Overview

The `account_mapping` seed table provides:
- Human-readable account names
- Business attributes (cost_center, owner, account_purpose)
- Row-level security via access_level
- Historical attribution with SCD Type 2 (effective_from/effective_to)

### 3.2 Initial Setup

1. Edit `seeds/account_mapping.csv` with your actual account data:

```csv
cloud_provider,account_id,account_display_name,account_purpose,owner,cost_center,access_level,effective_from,effective_to
aws,123456789012,AWS-Production,Production,infra-team,CC-001,restricted,2025-01-01,9999-12-31
aws,234567890123,AWS-Development,Development,dev-team,CC-002,public,2025-01-01,9999-12-31
alibaba,ali-prod-001,Alibaba-Prod,Production,infra-team,CC-001,restricted,2025-01-01,9999-12-31
```

2. Load seed data into BigQuery:

```bash
dbt seed
```

3. Verify seed data:

```sql
SELECT * FROM `gcp-rd-research-dev.finops_cloud_billing.account_mapping`;
```

### 3.3 Updating Account Mapping

**Adding a new account:**

```csv
# Simply add a new row
aws,345678901234,AWS-NewProject,Development,new-team,CC-003,public,2025-01-16,9999-12-31
```

**Changing cost_center or owner (SCD Type 2):**

```csv
# 1. Close out old record by setting effective_to
aws,123456789012,AWS-Production,Production,infra-team,CC-001,restricted,2025-01-01,2025-12-31
# 2. Add new record with new attribution
aws,123456789012,AWS-Production,Production,platform-team,CC-002,restricted,2026-01-01,9999-12-31
```

Then reload:

```bash
dbt seed --full-refresh
```

### 3.4 Detecting Unmapped Accounts

Run periodically to find accounts not in mapping:

```bash
# Compile the analysis SQL
dbt compile --select analyses/unmapped_accounts

# Execute in BigQuery Console or via bq CLI
bq query --use_legacy_sql=false < target/compiled/finops_cloud_billing/analyses/unmapped_accounts.sql
```

Unmapped accounts will:
- Default to `account_purpose = 'Others'`
- Default to `cost_center = 'UNMAPPED'`
- Default to `access_level = 'public'`

---

## 4. dbt Model Execution

### 4.1 Full Build (First Time)

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

```bash
cd dbt

# Option 1: dbt build (RECOMMENDED - runs seeds + models + tests in correct order)
dbt build

# Option 2: If you need to run separately (ensure seed runs first!)
dbt seed              # Load seed data first (account_mapping)
dbt run               # Then run models
dbt test              # Then run tests
```

> [!WARNING]
> Running `dbt run` without first running `dbt seed` will fail because intermediate models depend on `account_mapping` seed table.

### 4.2 Execution Order

dbt automatically handles dependencies via `ref()`. The execution order is:

```
1. account_mapping (seed)
2. stg_aws_cost (staging view)
3. int_aws_billing_ledger (intermediate view, joins account_mapping, all row types)
4. int_aws_billing (intermediate view, usage-only slice)
5. mart_cost_monthly (usage mart table)
```

### 4.3 Selective Runs

```bash
# Run only staging
dbt run --select staging

# Run only marts
dbt run --select marts

# Run specific model and its downstream
dbt run --select int_aws_billing+

# Run specific model and its upstream
dbt run --select +mart_cost_monthly
```

### 4.4 Verify Model Creation

```sql
-- Check all objects created
SELECT table_name, table_type, creation_time
FROM `gcp-rd-research-dev.finops_cloud_billing.INFORMATION_SCHEMA.TABLES`
ORDER BY creation_time;

-- Expected objects:
-- account_mapping (TABLE - seed)
-- stg_aws_cost (VIEW)
-- int_aws_billing_ledger (VIEW)
-- int_aws_billing (VIEW)
-- mart_cost_monthly (TABLE)
```

---

## 5. Data Validation

### 5.1 dbt Built-in Tests

```bash
# Run all tests
dbt test

# Run tests for specific model
dbt test --select int_aws_billing
```

### 5.2 Key Validations

| Test | Description | Expected |
|------|-------------|----------|
| `stg_aws_cost._row_id` not_null | Staging lineage identifier exists for all rows | Pass |
| `assert_no_overlapping_account_mappings` | SCD2 ranges do not overlap | Pass |
| `assert_ledger_preserves_staging_totals` | Ledger join does not lose or fan-out rows/cost | Pass |
| `line_item_type` accepted values | Only Usage types in intermediate | Pass |
| `account_purpose` accepted values | Production/Development/Others | Pass |
| `access_level` accepted values | public/restricted | Pass |

### 5.3 Cost Reconciliation

```sql
-- Verify staging matches raw data
WITH stg_total AS (
  SELECT
    billing_period_month,
    ROUND(SUM(actual_cost_usd), 2) AS stg_cost
  FROM `gcp-rd-research-dev.finops_cloud_billing.stg_aws_cost`
  GROUP BY billing_period_month
),
raw_total AS (
  SELECT
    DATE_TRUNC(DATE(bill_billing_period_start_date), MONTH) AS billing_period_month,
    ROUND(SUM(line_item_unblended_cost), 2) AS raw_cost
  FROM `gcp-digital-bi.aws_billing_no_payer.aws_billing_2025*`
  WHERE line_item_usage_start_date >= TIMESTAMP('2025-10-01')
    AND _TABLE_SUFFIX >= '1001'
  GROUP BY billing_period_month
)
SELECT
  COALESCE(s.billing_period_month, r.billing_period_month) AS billing_period_month,
  s.stg_cost,
  r.raw_cost,
  ROUND(s.stg_cost - r.raw_cost, 2) AS diff
FROM stg_total s
FULL OUTER JOIN raw_total r USING (billing_period_month)
ORDER BY billing_period_month;

-- Expected: diff < $0.01 per month
```

### 5.4 Account Mapping Coverage

```sql
-- Check account mapping coverage
SELECT
  account_purpose,
  cost_center,
  COUNT(DISTINCT account_id) AS account_count,
  ROUND(SUM(actual_cost_usd), 2) AS total_cost
FROM `gcp-rd-research-dev.finops_cloud_billing.int_aws_billing`
GROUP BY account_purpose, cost_center
ORDER BY total_cost DESC;

-- UNMAPPED cost_center indicates accounts needing mapping
```

---

## 6. Looker Configuration

### 6.1 Connection Setup

Create a BigQuery Connection in Looker Admin:

| Setting | Value |
|---------|-------|
| Connection Name | `finops-billing` (or custom) |
| Dialect | Google BigQuery Standard SQL |
| Project | `gcp-rd-research-dev` |
| Dataset | `finops_cloud_billing` |
| Location | `us-west2` |
| Authentication | Service Account (recommended) |

### 6.2 Row-Level Security in Looker

For access_level filtering, add User Attributes and sql_always_where:

```lookml
explore: int_aws_billing {
  # User attribute: access_level (values: 'all' for admins, 'public' for others)
  sql_always_where: 
    {% if _user_attributes['access_level'] == 'all' %}
      1=1
    {% else %}
      ${access_level} = 'public'
    {% endif %}
  ;;
}
```

If you later add finance/invoice marts, apply the same filter pattern on those marts.

### 6.3 Recommended Dashboard Tiles

**Executive Summary Dashboard:**

| Tile | Data Source | Visualization |
|------|-------------|---------------|
| Monthly Cost Trend | mart_cost_monthly | Line Chart |
| Cost by Region | mart_cost_monthly | Bar Chart |
| Top 10 Services | mart_cost_monthly | Table |
| Savings Rate | mart_cost_monthly | Single Value |

**Cost Allocation Dashboard:**

| Tile | Data Source | Visualization |
|------|-------------|---------------|
| Cost by Account Purpose | int_aws_billing (grouped) | Pie Chart |
| Cost by Cost Center | int_aws_billing (grouped) | Bar Chart |
| Unmapped Account Cost | int_aws_billing (filtered) | Single Value |

---

## 7. Ongoing Maintenance

### 7.1 Daily/Weekly Tasks

| Task | Command | Frequency |
|------|---------|-----------|
| Refresh mart tables | `dbt run --select marts` | Daily |
| Run data tests | `dbt test` | Daily |
| Check unmapped accounts | Run `unmapped_accounts.sql` | Weekly |

### 7.2 Monthly Tasks

| Task | Action |
|------|--------|
| Update account_mapping | Add new accounts, update cost centers |
| Review access_level | Ensure security policies are current |
| Cost reconciliation | Compare stg vs raw totals |

### 7.3 Scheduled Execution

For production, set up scheduled dbt runs:

**Option 1: Cloud Scheduler + Cloud Functions**

```bash
# Create a Cloud Function that runs dbt
# Trigger via Cloud Scheduler daily at 6am
```

**Option 2: dbt Cloud**

Use dbt Cloud for managed scheduling and monitoring.

**Option 3: Airflow/Cloud Composer**

```python
# DAG example
dbt_run = BashOperator(
    task_id='dbt_run',
    bash_command='cd /path/to/dbt && dbt run',
)
```

---

## 8. FAQ

### 8.1 Permission Error

**Problem**: `Access Denied: Table gcp-digital-bi.aws_billing_no_payer.aws_billing_*`

**Solution**: Ensure service account has `bigquery.dataViewer` permission on source project.

### 8.2 Unmapped Account Warning

**Problem**: Many rows have `cost_center = 'UNMAPPED'`

**Solution**:
1. Run `unmapped_accounts.sql` to get list of accounts
2. Add them to `seeds/account_mapping.csv`
3. Run `dbt seed --full-refresh`

### 8.3 Slow Queries

**Problem**: Mart queries are slow

**Solution**:
1. Ensure partition filters are used (billing_period_month)
2. Check clustering is effective for query patterns
3. Consider incremental builds for large datasets

### 8.4 Account Attribution Changed

**Problem**: Need to change cost_center for an account

**Solution**: Use SCD Type 2 pattern:
1. Set `effective_to` on old record
2. Add new record with `effective_from`
3. Run `dbt seed --full-refresh`

Historical data will retain old attribution; new data gets new attribution.

### 8.5 Duplicate Dataset Name

**Problem**: dbt creates both `finops_cloud_billing` and `finops_cloud_billing_finops_cloud_billing`.

**Cause**: dbt default schema naming concatenates `target.schema` and model `+schema`.
If both are `finops_cloud_billing`, the final schema becomes duplicated.

**Solution**:
1. Keep `dataset: finops_cloud_billing` in profile
2. Add `macros/generate_schema_name.sql` to use custom schema directly (no prefix concatenation)
3. Re-run `dbt build`

---

## Quick Reference

### One-Click Deployment

```bash
#!/bin/bash
set -e

cd dbt

echo "Step 1: Verify connection..."
dbt debug

echo "Step 2: Full build (seeds + models + tests)..."
dbt build

echo "Step 3: Generate documentation..."
dbt docs generate

echo "Deployment complete!"
echo "Run 'dbt docs serve' to view documentation."
```

### Object Inventory

| Object | Type | Full Path |
|--------|------|-----------|
| Dataset | Schema | `gcp-rd-research-dev.finops_cloud_billing` |
| account_mapping | Table (seed) | `finops_cloud_billing.account_mapping` |
| stg_aws_cost | View | `finops_cloud_billing.stg_aws_cost` |
| int_aws_billing_ledger | View | `finops_cloud_billing.int_aws_billing_ledger` |
| int_aws_billing | View | `finops_cloud_billing.int_aws_billing` |
| mart_cost_monthly | Table | `finops_cloud_billing.mart_cost_monthly` |

### Key dbt Commands

| Command | Purpose |
|---------|---------|
| `dbt debug` | Verify connection |
| `dbt build` | **RECOMMENDED** - Run seeds + models + tests in order |
| `dbt seed` | Load seed data only |
| `dbt seed --full-refresh` | Reload all seed data (for updates) |
| `dbt run` | Run all models (requires seed first!) |
| `dbt run --select +model_name` | Run model and its upstream dependencies |
| `dbt test` | Run all tests |
| `dbt docs generate && dbt docs serve` | Generate and view docs |
| `dbt compile --select analyses/unmapped_accounts` | Compile analysis query |

---

## Related Documents

- [System Design](./finops-system-design.md) - Architecture and full schema design
- [MVP Phase 1 Implementation](./finops-mvp-phase1.md) - Detailed implementation guide

---

*Document Version: 2.0*
*Created: 2026-01-12*
*Last Updated: 2026-02-11*