Skip to main content

​
Semantic View QA Invariants - Fixes Applied

​
Summary

Fixed 3 critical issues in QA invariants to prevent false PASS/FAIL signals and ensure accurate validation.

​
Fix 1: Verified stage3_component_id Projection ✅

Status: Already correct - stage3_component_id is projected in view final SELECT (line 236) The view projects s1c.stage3_component_id which is derived from the stage3_allocations CTE using this hash: 
TO_HEX(SHA256(
  CONCAT(
    CAST(tenant_id AS STRING), '|',
    CAST(period_label AS STRING), '|',
    CAST(UPPER(TRIM(business_name)) AS STRING), '|',
    CAST(period_code AS STRING), '|',
    CAST(agent_id AS STRING), '|',
    CAST(COALESCE(commission_type, 'NULL') AS STRING), '|',
    CAST(COALESCE(downline_agent_name, agent_name, 'NULL') AS STRING)
  )
)) AS stage3_component_id
Hash Alignment: ✅ The QA invariants use the exact same hash signature, ensuring perfect alignment between view and tests.

​
Fix 2: Removed FLOAT64 Paths in Tolerance Logic ✅

Problem: Tolerance comparisons could accidentally coerce NUMERIC to FLOAT64. Solution: All tolerance checks now use explicit NUMERIC casts: 
-- BEFORE:
ABS(v.sum_allocated_raw - s.source_agent_total) <= 0.01

-- AFTER:
ABS(CAST(v.sum_allocated_raw AS NUMERIC) - CAST(COALESCE(s.source_agent_total, 0) AS NUMERIC)) <= CAST('0.01' AS NUMERIC)
Files Updated:
  • Invariant #1: Stage3 Component Allocation Tie-Out
  • Invariant #4: Allocation Sum Reconciliation

​
Fix 3: Robust Tenant Isolation Checks ✅

Problem: Original tenant isolation query had two issues:
  1. NULL handling in join comparisons could undercount leakage
  2. Only checked Stage1 hash match, not dimension joins
Solution: Split into two comprehensive checks:

​
Check A: Dimension Join Tenant Scoping

Validates that all dimension table joins (dim_business_mapping, dim_agent_hierarchy, dim_rule_definitions, dim_plan_versions) are tenant-scoped: 
SELECT dimension_name, COUNT(*) AS mismatch_count
FROM view v
INNER JOIN dim_table d ON ...
WHERE v.tenant_id != d.tenant_id

​
Check B: Stage1 Source Tenant Match

Validates that every view row has a matching Stage1 source row with the same tenant_id: 
SELECT COUNT(*) AS mismatch_count
FROM view v
WHERE NOT EXISTS (
  SELECT 1 FROM stage1_snapshots s1
  WHERE s1.tenant_id = v.tenant_id
    AND hash_match(...)
)
File Updated: Invariant #6: Tenant Isolation

​
Improvement A: Tenant+Period Scoping ✅

Added: Parameterized scoping comments to all Stage3 source queries: 
WHERE period_label >= DATE('2024-01-01')
  -- Scope to test tenant/period (comment out for full scan)
  -- AND tenant_id = @tenant_id AND period_label = @period_label
Benefits:
  • Reduces query cost during testing
  • Prevents misinterpretation of results
  • Easy to enable/disable scoping
Files Updated:
  • Invariant #1: Stage3 Component Allocation Tie-Out
  • Invariant #2: No Stage3 Component Row Loss
  • Invariant #4: Allocation Sum Reconciliation

​
Hash Signature Verification

​
stage3_component_id Hash (View & Tests)

Both use identical signature: 
tenant_id|period_label|UPPER(TRIM(business_name))|period_code|agent_id|COALESCE(commission_type, 'NULL')|COALESCE(downline_agent_name, agent_name, 'NULL')

​
stage1_row_id Hash (View & Tests)

Both use identical signature: 
tenant_id|period_label|business_label|member_id|payee_agent_id|period_code|credit|debit|total

​
Validation Checklist

Before running QA invariants:
  • View projects stage3_component_id in final SELECT
  • Hash signatures match exactly between view and tests
  • All tolerance checks use NUMERIC casts (no FLOAT64)
  • Tenant isolation checks validate dimension joins + Stage1 source
  • Tenant+period scoping comments added (can be enabled for testing)

​
Expected Results

All invariants should return 0 failure rows when run against a correctly deployed view:
  1. Invariant #1: 0 rows (all components tie out within 0.01 tolerance)
  2. Invariant #2: 0 rows (all Stage3 components present)
  3. Invariant #3: 0 rows (all Stage1 rows present)
  4. Invariant #4: 0 rows (all businesses reconcile within tolerance)
  5. Invariant #5: Deterministic counts (same on rerun)
  6. Invariant #6: All checks show mismatch_count = 0 (PASS)

​
Usage

-- For scoped testing (recommended):
SET @tenant_id = 'creative_benefit_strategies';
SET @period_label = DATE('2025-08-01');
-- Uncomment scoping lines in queries

-- For full scan (production validation):
-- Leave scoping lines commented