​

Shadow Mode Implementation - Updated Alignment Check

​

✅ RESOLVED RED FLAGS

​

RED FLAG #1: Shadow Naming Strategy ✅ RESOLVED

“Use dataset-level shadowing only.” “Shadow datasets: payroll_raw_shadow, payroll_processed_shadow, payroll_analytics_shadow” ”❌ Do NOT suffix individual tables”

​

RED FLAG #2: Hardcoded Dataset Constants ✅ PARTIALLY RESOLVED

“Introduce a single helper (example): resolve_dataset("payroll_raw") -> "payroll_raw_shadow" if BQ_SHADOW_MODE=1” “One canonical resolver” “No hardcoded dataset strings elsewhere”

• Exact function signature
• Where to place helper function
• How to integrate with existing load_stage1_to_bigquery() and load_stage3_to_bigquery() calls

• All call sites use helper (not hardcoded strings)
• Helper reads BQ_SHADOW_MODE env var
• Default behavior (shadow mode OFF) writes to prod datasets

​

RED FLAG #3: top_agents Dual-Table Dependency ✅ RESOLVED

“Critical: top_agents shadow view must replace both: payroll_processed.stage3_snapshots, payroll_raw.stage1_snapshots”

​

RED FLAG #4: Period Validation ✅ RESOLVED

“Introduce ALLOWED_PERIODS_SHADOW” “Shadow allowlist must include: 2025-06-01, 2025-07-01, 2025-08-01” “Shadow mode must not bypass validation silently”

ALLOWED_PERIODS_SEATBELT = {"2025-06"}  # Line 30

ALLOWED_PERIODS_SHADOW = {"2025-06", "2025-07", "2025-08"}

​

RED FLAG #5: Parity Validator Dual-Dataset Queries ⚠️ NEEDS CLARIFICATION

“Compare Repo A (prod) vs Repo B (shadow)” Lists all metrics to compare

• How to construct queries with configurable dataset names
• Whether to use query builder functions or string replacement
• Example query pattern

def build_ceo_metrics_query(use_shadow: bool, period_label: str) -> str:
    dataset_suffix = "_shadow" if use_shadow else ""
    return f"""
        SELECT * FROM `payroll-bi-gauntlet.payroll_analytics{dataset_suffix}.ceo_snapshot`
        WHERE period_label = @period_label
    """

​

RED FLAG #6: MoM Calculations Require Previous Period ✅ RESOLVED

“Run Repo B shadow ingestion for: June 2025, July 2025, August 2025” “This is required to satisfy: MoM LAG() logic in views, Growth/Loss reconciliation”

​

RED FLAG #7: YTD/QTD Direct Table Queries ✅ RESOLVED

“QTD (June–August)” listed in parity validator scope Implies validator will query shadow tables directly for QTD

• Start: 2025-07-01 (Q3 start)
• End: 2025-08-31 (August end)
• Baseline: 2025-07-01 (for snapshot comparison)

​

RED FLAG #8: tpa_applied Column ✅ VERIFIED IN CODE

# repo_b/upload_to_bq.py:599
stage3_df['tpa_applied'] = df['is_tpa'].apply(parse_bool)

​

🔴 REMAINING RED FLAGS

​

RED FLAG #9: QTD Baseline Calculation for Multi-Month Validation

• If validating August 2025 QTD: baseline = July 1, current = August 31
• If validating July 2025 QTD: baseline = July 1, current = July 31
• If validating June 2025 QTD: baseline = April 1 (Q2), current = June 30

• Parity validator needs to understand QTD baseline varies by selected period
• For August 2025 QTD validation, need July 1 baseline (already covered by multi-month backfill)
• For June 2025 QTD validation, need April 1 baseline (NOT in shadow backfill scope)

• Clarify: Are we validating QTD for each month separately (June QTD, July QTD, August QTD)?
• OR: Are we validating Q3 QTD only (aggregating June–August)?
• Current spec says “QTD (June–August)” which suggests Q3 aggregation, but needs clarification

​

RED FLAG #10: Growth/Loss QTD/YTD Uses Snapshot Comparison

• Current snapshot: Selected period end
• Baseline snapshot: Quarter start (for QTD) or Jan 1 (for YTD)

• QTD Growth/Loss for August 2025 compares:
  • Current: August 31 snapshot
  • Baseline: July 1 snapshot
• This requires July shadow data (✅ covered)
• But also requires understanding that QTD uses snapshot comparison, not cumulative aggregation

• Parity validator should use get_growth_loss_summary_snapshot() logic for QTD validation
• Document that QTD Growth/Loss uses snapshot comparison (not cumulative)

​

RED FLAG #11: View DDL Storage Location

“Store shadow view SQL in: integration/bigquery/sql/views_shadow/”

​

⚠️ WARNINGS (Non-Blocking)

​

WARNING #1: Project ID Hardcoding

​

WARNING #2: Period Format Consistency

• 2025-06-01 (with day)
• 2025-06 (without day)

​

WARNING #3: QTD Quarter Definition

• If validating “QTD (June–August)”, this is NOT a standard quarter
• Standard Q3 = July–September
• June is end of Q2

• Are we validating Q2 QTD (April–June) + Q3 QTD (July–September)?
• OR: Are we validating a custom 3-month period (June–August)?

​

📋 UPDATED PRE-IMPLEMENTATION CHECKLIST

• ✅ Shadow naming strategy clarified (dataset-level)
• ✅ Multi-month scope defined (June, July, August)
• ✅ top_agents dual-table dependency acknowledged
• ✅ Shadow allowlist specified
• ⚠️ Clarify QTD validation scope (standard quarter vs custom period)
• ⚠️ Verify is_tpa column exists in Repo B Stage3 CSV
• ⚠️ Design query builder pattern for parity validator (or use string replacement)
• ⚠️ Create views_shadow/ directory structure
• ⚠️ Document QTD Growth/Loss snapshot comparison logic

​

✅ ALIGNMENT SUMMARY

​

Resolved (6):

1. ✅ Shadow naming strategy (dataset-level)
2. ✅ Hardcoded constants (helper function specified)
3. ✅ top_agents dual-table dependency
4. ✅ Period validation (shadow allowlist)
5. ✅ MoM previous period requirement (multi-month backfill)
6. ✅ YTD/QTD scope (included in validator)

​

Needs Clarification (2):

1. ⚠️ QTD validation scope (standard quarter vs custom June–August period)
2. ⚠️ Parity validator query builder pattern (implementation detail)

​

New Red Flags (1):

1. 🔴 QTD baseline calculation for multi-month validation

​

🎯 READINESS ASSESSMENT

1. Clarify QTD validation scope before writing parity validator SQL
2. Verify is_tpa column exists in Repo B outputs before shadow backfill
3. Design query builder helper early (before writing validator)