​

Identity Stability Execution Plan

​

Quick Reference: Guarantee Statements

​

Business ID Stability (Post-Fix)

​

Agent Identity Stability (Post-Migration)

​

Phase 1: Business ID Fixes (IMMEDIATE)

​

Step 1.1: Update Schema

​

Step 1.2: Create MERGE SQL

​

Step 1.3: Migration Script

-- Migration: Add tenant_id to dim_business_mapping
-- Run this BEFORE deploying new MERGE logic

-- Step 1: Add tenant_id column (nullable initially)
ALTER TABLE `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping`
ADD COLUMN IF NOT EXISTS tenant_id STRING;

-- Step 2: Backfill tenant_id for existing rows
UPDATE `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping`
SET tenant_id = 'creative_benefit_strategies'
WHERE tenant_id IS NULL;

-- Step 3: Verify no NULLs remain
SELECT COUNT(*) AS null_count
FROM `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping`
WHERE tenant_id IS NULL;
-- Expected: 0

-- Step 4: Add clustering (optional but recommended)
ALTER TABLE `payroll-bi-gauntlet.payroll_analytics.dim_business_mapping`
SET OPTIONS(
  clustering_fields = ['tenant_id', 'normalized_name', 'business_id']
);

​

Step 1.4: Update BusinessIDGenerator

1. Add tenant_id parameter to __init__() and all methods
2. Include tenant_id in CSV schema
3. Update _save_mapping() to include tenant_id

​

Step 1.5: Update Loader

​

Phase 2: Agent Identity Migration (REQUIRED)

​

Step 2.1: Create Schema

​

Step 2.2: Create MERGE SQL

​

Step 2.3: Initial Population Script

-- Migration: Populate dim_agent_identity from existing data
-- Generates agent_key for all agents found in stage3_snapshots

INSERT INTO `payroll-bi-gauntlet.payroll_analytics.dim_agent_identity`
SELECT DISTINCT
  tenant_id,
  -- Generate agent_key (deterministic hash)
  CONCAT('AGENT:', TO_HEX(SHA256(UPPER(TRIM(agent_name))))[:16]) AS agent_key,
  UPPER(TRIM(agent_name)) AS display_name,
  [UPPER(TRIM(agent_name))] AS aliases,  -- Start with canonical name
  agent_id AS upstream_agent_id,
  DATE('2024-01-01') AS effective_start_date,
  NULL AS effective_end_date,
  TRUE AS is_active,
  CURRENT_TIMESTAMP() AS created_at,
  CURRENT_TIMESTAMP() AS updated_at
FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`
WHERE period_label >= DATE('2024-01-01')
  AND agent_name IS NOT NULL
GROUP BY tenant_id, agent_name, agent_id
-- Avoid duplicates
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY tenant_id, UPPER(TRIM(agent_name))
  ORDER BY period_label DESC
) = 1;

​

Step 2.4: Update Semantic View (Backward Compatible)

1. Add agent_identity CTE (join to dim_agent_identity)
2. Update stage3_component_id hash to use agent_key (fallback to agent_id)
3. Keep agent_id in final SELECT for backward compatibility
4. Add agent_key to final SELECT

-- BEFORE:
CAST(agent_id AS STRING), '|',

-- AFTER:
CAST(COALESCE(ai.agent_key, s3.agent_id, 'UNKNOWN') AS STRING), '|',

​

Phase 3: QA Invariants

​

Step 3.1: Add Business ID Churn Check

​

Step 3.2: Add Agent Key Stability Check

​

Step 3.3: Update Tenant Isolation Tests

​

Validation Checklist

• dim_business_mapping has tenant_id column
• MERGE SQL uses (tenant_id, normalized_name) key
• business_id is never updated in MERGE (only inserted)
• BusinessIDGenerator includes tenant_id in all operations
• Loader uses MERGE instead of WRITE_TRUNCATE
• dim_agent_identity table created and populated
• Semantic view uses agent_key in stage3_component_id hash
• QA invariants pass (0 business_id churn, 0 agent_key churn)
• Tenant isolation tests updated for agent_key

​

Rollout Order

1. Week 1: Business ID fixes (Phase 1)
   • Deploy schema update
   • Run migration script
   • Update generator and loader
   • Validate with QA invariants
2. Week 2: Agent identity migration (Phase 2)
   • Create dim_agent_identity table
   • Populate from existing data
   • Update semantic view (backward compatible)
   • Validate with QA invariants
3. Week 3: QA and validation (Phase 3)
   • Run all invariants
   • Verify no ID churn
   • Test tenant isolation
   • Document results

​

Risk Mitigation

​

Backward Compatibility

• Keep agent_id in semantic view SELECT (add agent_key alongside)
• Existing queries continue to work
• Gradual migration path

​

Data Loss Prevention

• MERGE operations are idempotent (safe to rerun)
• business_id and agent_key are immutable (never updated)
• QA invariants detect churn before production

​

Rollback Plan

• Keep old loader code in git history
• MERGE statements can be reverted to INSERT-only
• Schema changes are additive (can drop tenant_id if needed)