​

Shadow Mode Implementation - Red Flags & Alignment Check

​

✅ ALIGNMENT VERIFIED

​

1. Base Objects Match Contract

• ✅ payroll_raw.stage1_snapshots (plural) - CORRECT
• ✅ payroll_processed.stage3_snapshots (plural) - CORRECT
• ✅ All 4 views correctly identified: ceo_snapshot, top_businesses, top_agents, business_growth_loss

​

2. View Dependencies Correctly Identified

• ✅ ceo_snapshot → stage1_snapshots only
• ✅ top_businesses → stage1_snapshots only
• ✅ top_agents → stage3_snapshots + stage1_snapshots (JOIN) - CRITICAL
• ✅ business_growth_loss → stage1_snapshots only

​

3. Parity Validator Scope Correct

• ✅ CEO MTD metrics (all required fields identified)
• ✅ Top businesses MTD (order + values)
• ✅ Top agents MTD (order + values)
• ✅ Growth/Loss reconciliation (MoM formula)

​

🔴 RED FLAGS

​

RED FLAG #1: Ambiguous Shadow Table Naming Strategy

• Option A: Table-level suffixing: stage1_snapshots_shadow, stage3_snapshots_shadow
• Option B: Dataset-level shadowing: payroll_raw_shadow.stage1_snapshots, payroll_processed_shadow.stage3_snapshots
• Task mentions BOTH: “parallel tables” with *_shadow suffix AND “parallel dataset payroll_analytics_shadow”

• If using table suffix: Views need to reference stage1_snapshots_shadow (same dataset)
• If using dataset suffix: Views need to reference payroll_raw_shadow.stage1_snapshots (different dataset)

• Use dataset-level shadowing (Option B) for cleaner separation
• Shadow tables: payroll_raw_shadow.stage1_snapshots, payroll_processed_shadow.stage3_snapshots
• Shadow views: payroll_analytics_shadow.* (all views)
• Rationale: Easier to drop entire shadow dataset for cleanup, clearer separation

​

RED FLAG #2: Hardcoded Dataset Constants in Upload Functions

DEFAULT_DATASET_RAW = "payroll_raw"
DEFAULT_DATASET_PROCESSED = "payroll_processed"
DEFAULT_DATASET_ANALYTICS = "payroll_analytics"

• Shadow mode needs to override dataset names
• Current code structure allows this (parameters exist), but need to ensure all call sites pass shadow dataset names

• Add BQ_SHADOW_MODE env var that sets dataset suffix
• Create helper function: get_dataset_name(base_name: str, shadow_mode: bool) -> str
• Update all upload function calls to use helper

​

RED FLAG #3: top_agents View Joins BOTH Shadow Tables

• payroll_processed.stage3_snapshots (for commissions)
• payroll_raw.stage1_snapshots (for employee counts)

• Shadow view MUST reference BOTH shadow tables:
  • payroll_processed_shadow.stage3_snapshots
  • payroll_raw_shadow.stage1_snapshots
• If shadow view only references one shadow table, parity test will fail

• Shadow view SQL must replace ALL table references:
  Copy

  Ask AI

  -- Production:
  FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`
  FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
  
  -- Shadow:
  FROM `payroll-bi-gauntlet.payroll_processed_shadow.stage3_snapshots`
  FROM `payroll-bi-gauntlet.payroll_raw_shadow.stage1_snapshots`
  

​

RED FLAG #4: July 2025 Period Validation

• ALLOWED_PERIODS_SEATBELT = {"2025-06"} in upload_to_bq.py (line 30)
• July 2025 may not be in allowlist

• Shadow mode upload may fail if July 2025 not in allowlist
• Need to either:
  • Add July 2025 to allowlist, OR
  • Use --force flag (dangerous), OR
  • Update seatbelt to allow shadow mode periods

• Add July 2025 to ALLOWED_PERIODS_SEATBELT OR
• Create separate ALLOWED_PERIODS_SHADOW constant
• Shadow mode should bypass seatbelt OR use separate allowlist

​

RED FLAG #5: Parity Validator Needs to Query BOTH Production and Shadow

• Production: Query payroll_analytics.* views (or payroll_raw.stage1_snapshots directly)
• Shadow: Query payroll_analytics_shadow.* views (or payroll_raw_shadow.stage1_snapshots directly)

• Validator needs to construct queries with configurable dataset names
• Cannot hardcode production dataset names

• Create query builder functions that accept dataset_suffix parameter
• Example: get_ceo_metrics_query(use_shadow: bool) -> str
• Or: Use parameterized queries with dataset names as parameters

​

RED FLAG #6: MoM Calculations Require Previous Period Data

• ceo_snapshot view uses LAG() window function (requires previous period)
• business_growth_loss view uses LAG() window function (requires previous period)
• Growth/Loss endpoints use FULL OUTER JOIN between current and previous month

• Shadow tables for July 2025 alone may not produce correct MoM calculations
• Need June 2025 shadow data for July MoM to work correctly

• Shadow mode backfill should include June 2025 (previous period) for July validation
• OR: Document that MoM calculations will be NULL/incorrect without previous period
• OR: Use direct table queries (bypass views) for parity validation

​

RED FLAG #7: YTD/QTD Functions Query Tables Directly

• get_top_businesses_ytd() → stage1_snapshots (direct)
• get_top_businesses_qtd() → stage1_snapshots (direct)
• get_top_agents_ytd() → stage3_snapshots (direct)
• get_top_agents_qtd() → stage3_snapshots (direct)

• Parity validator for YTD/QTD must query shadow tables directly
• Cannot rely on shadow views for YTD/QTD validation

• Parity validator should query shadow tables directly for YTD/QTD comparisons
• OR: Create shadow views that support YTD/QTD (but this may be overkill)

​

RED FLAG #8: Agent Commissions Query Uses tpa_applied = TRUE Filter

FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`
WHERE tpa_applied = TRUE

• Parity validator must ensure Repo B shadow data includes tpa_applied column
• Repo B upload must set tpa_applied correctly (matches Repo A logic)

• Verify Repo B load_stage3_to_bigquery() sets tpa_applied correctly
• Add validation check: SELECT COUNT(*) WHERE tpa_applied IS NULL should be 0

​

⚠️ WARNINGS (Non-Blocking)

​

WARNING #1: View SQL Hardcodes Project ID

FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`

• Shadow views will also hardcode project ID (acceptable for now)
• If project changes, need to update all shadow view DDL

​

WARNING #2: Rank Computation Mismatch

• Views use QUALIFY ROW_NUMBER() ... <= 10 to LIMIT but don’t expose rank
• Backend adds ROW_NUMBER() OVER (ORDER BY ...) as rank in SELECT

• Parity validator should ignore rank field (compare order by name/ID instead)
• Task already specifies: “ignore internal rank numbering fields” ✅

​

WARNING #3: Tolerance Rules May Need Adjustment

• Money: abs diff ≤ 0.05 (5 cents)
• Counts: exact match (0 tolerance)

• BigQuery NUMERIC precision may cause floating-point differences
• Need to verify tolerance is appropriate for actual data

​

📋 PRE-IMPLEMENTATION CHECKLIST

• Clarify shadow naming strategy (dataset-level vs table-level)
• Update seatbelt allowlist to include July 2025 (or create shadow allowlist)
• Verify Repo B upload sets tpa_applied correctly
• Decide on June 2025 shadow data requirement for July MoM validation
• Design query builder pattern for dual-dataset parity validation
• Verify all table references in shadow view DDL (especially top_agents JOIN)
• Test shadow dataset creation in BigQuery (permissions, quotas)

​

✅ ALIGNMENT SUMMARY

1. 🔴 Shadow naming strategy ambiguity
2. 🔴 Hardcoded dataset constants
3. 🔴 top_agents view dual-table dependency
4. 🔴 July 2025 seatbelt restriction
5. 🔴 Parity validator dual-dataset query pattern
6. 🔴 MoM calculations require previous period
7. 🔴 YTD/QTD direct table queries