# Revenue Category Impact Assessment

## Executive Summary

This document assesses the impact of the current revenue category pivot structure in `dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec` and evaluates two approaches for handling new revenue categories:

1. **Current Approach (Column-based)**: Adding new columns for each revenue category
2. **Proposed Approach (Key-Value Array)**: Restructuring to use a key-value array structure

## Current Structure Analysis

### Source Model: `dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec`

**Location**: `models/fp_and_a/dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec.sql`

**Current Revenue Categories** (as columns):
- `dedicated_revrec_amount` / `dedicated_revrec_usd`
- `serverless_revrec_amount` / `serverless_revrec_usd` (renamed from Serverless to Starter)
- `support_revrec_amount` / `support_revrec_usd`
- `essential_revrec_amount` / `essential_revrec_usd` ⚠️ **Created but NOT used downstream**
- `other_revrec_amount` / `other_revrec_usd`
- `remaining_revrec_amount` / `remaining_revrec_usd` ⚠️ **Created but NOT used downstream**

**Total**: 6 categories × 2 columns (amount + USD) = 12 revenue category columns

### Downstream Models Count

**Total Downstream Models**: 70 models (excluding the source model itself)

**Models Directly Using Revenue Category Columns**: 10 models

---

## Part 1: Complete List of Downstream Models

### Direct Dependencies (1 level)
1. `dws_salesforce_order_product_monthly_revrec_usd_after_202303_v2` ⚠️ **DIRECT USAGE**

### Indirect Dependencies (2+ levels)
2. `basecase_arr_forecast_bigger_than_bestcase` (test)
3. `dim_salesforce_account_nrr_month_v2`
4. `dim_salesforce_order_mrr_month_commission_base`
5. `dim_salesforce_order_mrr_month_v2`
6. `dwm_salesforce_account_current_monthly_nrr_v2`
7. `dwm_salesforce_activated_order_mrr_month_v2`
8. `dwm_salesforce_activated_order_product_future_1year_monthly_rpo_v2`
9. `dwm_salesforce_cloud_commitment_plan_to_marketplace_order_product_detail_accum_revrec_v2`
10. `dwm_salesforce_cloud_commitment_plan_to_marketplace_order_product_monthly_revrec_v2`
11. `dwm_salesforce_order_monthly_mrrarr_with_ratio_commission_base`
12. `dwm_salesforce_order_monthly_mrrarr_with_ratio_v2`
13. `dwm_salesforce_order_revrec_end_date_v2` ⚠️ **DIRECT USAGE**
14. `dwm_salesforce_order_revrec_start_date_v2` ⚠️ **DIRECT USAGE**
15. `dws_combined_china_and_overseas_account_monthly_revrec_change_explore`
16. `dws_combined_china_and_overseas_order_product_account_monthly_revrec` ⚠️ **DIRECT USAGE**
17. `dws_combined_overseas_and_china_order_product_account_monthly_revrec`
18. `dws_fy25_company_acv_achivement_and_forecast_summary`
19. `dws_fy25_regional_acv_achivement_and_forecast_detail`
20. `dws_global_arr_forecast_all_op_and_cloud_account_best_and_base`
21. `dws_global_arr_forecast_all_op_and_cloud_account_best_and_base_daily_snapshot` (snapshot)
22. `dws_global_arr_forecast_all_op_and_cloud_account_best_and_base_snapshot`
23. `dws_global_arr_forecast_all_op_and_cloud_account_best_and_base_snapshot_v2`
24. `dws_global_arr_forecast_all_op_and_cloud_account_best_and_base_v2`
25. `dws_global_arr_forecast_op_to_cloud_account_best_and_base`
26. `dws_global_arr_forecast_op_to_cloud_best_and_base_detail`
27. `dws_global_fy25_regional_acv_achivement_and_forecast_detail`
28. `dws_salesforce_account_current_monthly_mrrarr_bo_subscription_and_cloud`
29. `dws_salesforce_account_monthly_nrr_v2`
30. `dws_salesforce_account_monthly_revrec_usd`
31. `dws_salesforce_account_quarterly_mrrarr_bo_subscription_and_cloud_pivot`
32. `dws_salesforce_account_quarterly_mrrarr_bo_subscription_and_cloud_pivot_old`
33. `dws_salesforce_activated_order_account_monthly_revrec_v3` ⚠️ **DIRECT USAGE**
34. `dws_salesforce_activated_order_account_monthly_revrec_v3_stg` ⚠️ **DIRECT USAGE**
35. `dws_salesforce_activated_order_monthly_total_rpo_v2`
36. `dws_salesforce_activated_order_product_future_1year_rpo_v2`
37. `dws_salesforce_activated_order_product_monthly_total_rpo_v2`
38. `dws_salesforce_activated_order_product_monthly_total_rpo_v2_stg`
39. `dws_salesforce_arr_summary`
40. `dws_salesforce_cloud_commitment_plan_order_spend_progress_v3`
41. `dws_salesforce_fy25_account_current_monthly_mrrarr_bo_subscription_and_cloud_attain_detail`
42. `dws_salesforce_fy25_monthly_mrrarr_bo_subscription_and_cloud_attain_summary`
43. `dws_salesforce_fy25_monthly_mrrarr_bo_subscription_and_cloud_attain_summary_middle`
44. `dws_salesforce_fy25_order_current_monthly_mrrarr_bo_subscription_and_cloud_attain_detail`
45. `dws_salesforce_fy25_order_current_monthly_mrrarr_bo_subscription_and_cloud_attain_middle`
46. `dws_salesforce_order_current_monthly_mrrarr_bo_subscription_and_cloud`
47. `dws_salesforce_order_current_monthly_mrrarr_bo_subscription_and_cloud_middle`
48. `dws_salesforce_order_monthly_mrrarr_bo_pre`
49. `dws_salesforce_order_monthly_mrrarr_bo_subscription_and_cloud`
50. `dws_salesforce_order_monthly_mrrarr_v2`
51. `dws_salesforce_order_monthly_mrrarr_v2_commission_base`
52. `dws_salesforce_order_monthly_mrrarr_v3`
53. `dws_salesforce_order_monthly_mrrarr_v3_plus_revrec_break_down`
54. `dws_salesforce_order_monthly_revrec_usd_accsum_v2`
55. `dws_salesforce_order_monthly_revrec_usd_v2` ⚠️ **DIRECT USAGE**
56. `dws_salesforce_order_monthly_revrec_usd_v2_stg`
57. `dws_salesforce_order_product_monthly_revrec_usd_accsum_v2`
58. `dws_salesforce_order_product_monthly_revrec_usd_accsum_v2_stg`
59. `dws_salesforce_order_product_monthly_revrec_usd_v2` ⚠️ **DIRECT USAGE**
60. `dws_salesforce_order_product_monthly_revrec_usd_v2_stg` ⚠️ **DIRECT USAGE**
61. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud`
62. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot`
63. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot_middle1`
64. `dws_salesforce_order_product_monthly_revrec_usd_after_202303_v2` ⚠️ **DIRECT USAGE**
65. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot_middle1p`
66. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot_middle1pp`
67. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot_middle2`
68. `dws_salesforce_order_quarterly_mrrarr_bo_subscription_and_cloud_pivot_old`
69. `fy25_acv_per_account_current_quarter_bigger_than_10m` (test)

---

## Part 2: Models Directly Using Revenue Category Columns

### Critical Models (10 total)

These models explicitly reference revenue category columns (`dedicated_revrec_amount`, `serverless_revrec_amount`, `support_revrec_amount`, `other_revrec_amount`, etc.):

1. **`dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec.sql`** (SOURCE)
   - Creates all revenue category columns
   - **Impact**: Must be updated for any new category

2. **`dws_salesforce_order_product_monthly_revrec_usd_after_202303_v2.sql`**
   - Aggregates all 4 categories (dedicated, serverless, support, other)
   - Uses `SUM` with NULL handling: `sum(case when dedicated_revrec_amount is null then 0 else dedicated_revrec_amount end)`
   - **Impact**: HIGH - Must add new category columns here

3. **`dws_salesforce_order_product_monthly_revrec_usd_v2.sql`**
   - UNION ALL with null placeholders for categories
   - **Impact**: HIGH - Must add null columns for new categories

4. **`dws_salesforce_order_product_monthly_revrec_usd_v2_stg.sql`**
   - Uses `EXCEPT` to exclude category columns, then UNION ALL
   - **Impact**: MEDIUM - Must update EXCEPT clause

5. **`dws_salesforce_order_monthly_revrec_usd_v2.sql`**
   - Aggregates all 4 categories from product-level
   - Uses same NULL handling pattern
   - **Impact**: HIGH - Must add new category columns

6. **`dwm_salesforce_order_revrec_start_date_v2.sql`**
   - Uses `EXCEPT` to exclude category columns
   - **Impact**: MEDIUM - Must update EXCEPT clause

7. **`dwm_salesforce_order_revrec_end_date_v2.sql`**
   - Uses `EXCEPT` to exclude category columns
   - **Impact**: MEDIUM - Must update EXCEPT clause

8. **`dws_salesforce_activated_order_account_monthly_revrec_v3.sql`**
   - Passes through all category columns
   - Used in UNION with FenXiangXiaoKe data (which has nulls for categories)
   - **Impact**: HIGH - Must add new category columns in both UNION branches

9. **`dws_salesforce_activated_order_account_monthly_revrec_v3_stg.sql`**
   - Similar structure to v3
   - **Impact**: HIGH - Must add new category columns

10. **`dws_combined_china_and_overseas_order_product_account_monthly_revrec.sql`**
    - UNION ALL combining Salesforce and FenXiangXiaoKe data
    - First branch sets categories to NULL, second branch uses actual values
    - **Impact**: HIGH - Must add new category columns in both branches

---

## Part 3: Impact Assessment

### 3A. Impact of Adding New Revenue Categories (Column-Based Approach)

#### Required Changes Per New Category

**For each new revenue category (e.g., "Premium"), you must:**

1. **Update Source Pivot Model** (`dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec.sql`):
   - Add new CTE filter (e.g., `common_cte_premium`)
   - Add new LEFT JOIN
   - Add 2 new columns (`premium_revrec_amount`, `premium_revrec_usd`)
   - Update `common_cte_remaining` filter to exclude new category
   - **Lines to modify**: ~15-20 lines

2. **Update Direct Dependencies (10 models)**:
   - **Aggregation models** (3 models): Add SUM aggregation for new columns
   - **UNION models** (4 models): Add NULL placeholders in UNION branches
   - **EXCEPT models** (2 models): Update EXCEPT clause (or add columns)
   - **Pass-through models** (1 model): Add columns to SELECT

3. **Update Indirect Dependencies**:
   - Models that use `SELECT *` from affected models: **No changes needed** ✅
   - Models that explicitly select columns: **May need updates** ⚠️
   - Models that aggregate or transform: **May need updates** ⚠️

#### Estimated Effort Per New Category

| Task | Models Affected | Estimated Hours |
|------|----------------|-----------------|
| Update pivot model | 1 | 1-2 hours |
| Update direct dependencies | 10 | 4-6 hours |
| Testing & validation | All 70 | 8-12 hours |
| **Total** | **70** | **13-20 hours** |

#### Risks & Challenges

1. **Inconsistency Risk**: Easy to miss updating one of the 10 direct dependency models
2. **Testing Burden**: Must test all 70 downstream models
3. **Schema Evolution**: BigQuery schema changes require careful deployment
4. **Data Quality**: NULL handling must be consistent across all models
5. **Documentation**: Must document new category in multiple places

#### Current Issues Identified

⚠️ **`essential_revrec_amount/usd` columns exist but are NOT used in any downstream model**
- Created in pivot model but never referenced downstream
- Indicates potential inconsistency or incomplete implementation

⚠️ **`remaining_revrec_amount/usd` columns exist but are NOT used in any downstream model**
- Created in pivot model but never referenced downstream
- May indicate dead code or future planning

---

### 3B. Impact of Restructuring to Key-Value Array Approach

#### Proposed Structure

Instead of:
```sql
dedicated_revrec_amount, dedicated_revrec_usd,
serverless_revrec_amount, serverless_revrec_usd,
support_revrec_amount, support_revrec_usd,
...
```

Use:
```sql
revenue_categories ARRAY<STRUCT<
  category STRING,
  revrec_amount FLOAT64,
  revrec_usd FLOAT64
>>
```

Or normalized approach:
```sql
-- Separate table/view
revenue_category_breakdown ARRAY<STRUCT<
  category STRING,
  revrec_amount FLOAT64,
  revrec_usd FLOAT64
>>
```

#### Benefits

1. **Scalability**: New categories automatically available without schema changes
2. **Maintainability**: Single source of truth for category logic
3. **Flexibility**: Easy to add/remove categories without code changes
4. **Consistency**: No risk of missing updates in downstream models
5. **Future-proof**: Handles unlimited categories

#### Required Changes

**Phase 1: Source Model Refactoring**
- Refactor `dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec.sql`
- Convert pivot logic to array aggregation
- **Estimated Effort**: 4-6 hours

**Phase 2: Direct Dependencies (10 models)**
- Refactor aggregation logic to use array functions
- Update UNION statements to handle array structure
- **Estimated Effort**: 20-30 hours

**Phase 3: Indirect Dependencies (60 models)**
- Models using `SELECT *`: May work automatically ✅
- Models with explicit column references: Need refactoring ⚠️
- Models with aggregations: Need array aggregation logic ⚠️
- **Estimated Effort**: 40-60 hours

**Phase 4: Testing & Validation**
- Comprehensive testing across all 70 models
- Data validation and reconciliation
- **Estimated Effort**: 20-30 hours

**Total Estimated Effort**: **84-126 hours** (2-3 weeks for 1 developer)

#### Challenges

1. **BigQuery Array Performance**: Array operations may be slower than column access
2. **Query Complexity**: More complex SQL for aggregations and filtering
3. **BI Tool Compatibility**: Some BI tools may not handle arrays well
4. **Migration Risk**: Large refactoring with potential for bugs
5. **Backward Compatibility**: May break existing reports/dashboards

#### Recommended Approach

**Hybrid Solution**: Create a new model with array structure while maintaining backward compatibility:

1. Create `dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec_v3` with array structure
2. Create helper macros/functions for common array operations
3. Gradually migrate downstream models
4. Maintain old model until migration complete

---

## Part 4: Recommendations

### Short-Term (Adding New Categories)

**If you need to add 1-2 categories quickly:**
- Use column-based approach
- Create a checklist of all 10 direct dependency models
- Use dbt tests to validate column existence
- Document the process for future additions

**Estimated Time**: 13-20 hours per category

### Long-Term (Scalable Solution)

**If you expect frequent category additions:**
- Implement key-value array structure
- Create migration plan with phases
- Build helper macros for common operations
- Migrate gradually with backward compatibility

**Estimated Time**: 84-126 hours (one-time investment)

### Immediate Actions

1. ✅ **Document current state** (this assessment)
2. ⚠️ **Fix inconsistencies**: Remove or implement `essential_revrec` and `remaining_revrec` columns
3. 📋 **Create migration checklist** if proceeding with array approach
4. 🧪 **Add dbt tests** to validate revenue category columns exist

---

## Part 5: Detailed Model-by-Model Impact

### Models Requiring Column Updates (10 models)

| Model | Change Type | Complexity | Risk Level |
|-------|-------------|------------|------------|
| `dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec` | Add CTE + JOIN + columns | High | High |
| `dws_salesforce_order_product_monthly_revrec_usd_after_202303_v2` | Add SUM aggregation | Medium | Medium |
| `dws_salesforce_order_product_monthly_revrec_usd_v2` | Add NULL columns in UNION | Low | Low |
| `dws_salesforce_order_product_monthly_revrec_usd_v2_stg` | Update EXCEPT clause | Low | Low |
| `dws_salesforce_order_monthly_revrec_usd_v2` | Add SUM aggregation | Medium | Medium |
| `dwm_salesforce_order_revrec_start_date_v2` | Update EXCEPT clause | Low | Low |
| `dwm_salesforce_order_revrec_end_date_v2` | Update EXCEPT clause | Low | Low |
| `dws_salesforce_activated_order_account_monthly_revrec_v3` | Add columns in UNION | Medium | Medium |
| `dws_salesforce_activated_order_account_monthly_revrec_v3_stg` | Add columns in UNION | Medium | Medium |
| `dws_combined_china_and_overseas_order_product_account_monthly_revrec` | Add columns in UNION | Medium | Medium |

### Models Using SELECT * (May Work Automatically)

These models use `SELECT *` and may automatically pick up new columns:
- `dws_salesforce_order_product_monthly_revrec_usd_v2_stg` (partial - uses EXCEPT)
- Many intermediate aggregation models

**Note**: Even with `SELECT *`, if downstream models have explicit column lists, they may need updates.

---

## Conclusion

The current column-based approach works but is **not scalable**. Each new category requires:
- Updates to 10+ models
- 13-20 hours of work
- High risk of inconsistencies

The key-value array approach is a **significant investment** (84-126 hours) but provides:
- Unlimited scalability
- Single source of truth
- Reduced maintenance burden

**Recommendation**: 
- **Short-term**: Use column-based approach with strict checklist
- **Long-term**: Plan migration to array-based structure if categories will change frequently

---

## Appendix: dbt Commands Used

```bash
# List all downstream models
dbt list --select dwm_netsuite_monthly_revrec_usd_after_202303_v2_pivot_cloud_revrec+ --output json

# Find models using revenue category columns
grep -r "dedicated_revrec_amount\|serverless_revrec_amount\|support_revrec_amount\|essential_revrec_amount\|other_revrec_amount\|remaining_revrec_amount" models/
```

---

**Document Generated**: $(date)
**Analyst**: AI Assistant
**Review Status**: Pending Review