​

Plan Red Flags & Alignment Review

​

🔴 RED FLAG #1: dim_agent_hierarchy Missing tenant_id

​

Issue

agent_hierarchy AS (
  SELECT
    tenant_id,  -- ❌ NOT IN SCHEMA
    agent_id,
    agent_name,
    ...
  FROM `payroll-bi-gauntlet.payroll_analytics.dim_agent_hierarchy`
  ...
  QUALIFY ROW_NUMBER() OVER (
    PARTITION BY tenant_id, agent_id  -- ❌ tenant_id doesn't exist
    ...
  ) = 1
)

• ⚠️ CRITICAL: View will fail at runtime (column not found)
• ⚠️ Tenant isolation broken: Cannot enforce tenant scoping in hierarchy joins
• ⚠️ QA tests will fail: Tenant isolation checks assume tenant_id exists

1. Add tenant_id to dim_agent_hierarchy schema
2. Create migration script to backfill tenant_id
3. Update loader to include tenant_id
4. OR: Remove tenant_id from semantic view CTE (if single-tenant assumption)

​

🔴 RED FLAG #2: agent_key Generation Inconsistency

​

Issue

CONCAT('AGENT:', TO_HEX(SHA256(UPPER(TRIM(agent_name))))[:16]) AS agent_key

hash_obj = hashlib.sha256(normalized.encode('utf-8'))
return f"AGENT:{hash_obj.hexdigest()[:16]}"

'AGENT:721995' AS agent_key,  -- Example: use upstream ID if stable

• Strategy A/B: Hash-based (deterministic, but different from legacy agent_id)
• Strategy C: Uses upstream numeric ID (721995) - NOT deterministic from name

• ⚠️ Inconsistent keys: Same agent could get different agent_key depending on which strategy is used
• ⚠️ Breaks determinism: If using upstream ID, agent_key is not deterministic from agent_name
• ⚠️ Migration confusion: Unclear which strategy to use

• Option 1: Always use hash-based (deterministic from agent_name)
• Option 2: Use upstream ID if available, hash otherwise (requires mapping logic)
• Option 3: Use hash for all, store upstream ID separately in upstream_agent_id

​

🔴 RED FLAG #3: Join Strategy Unclear

​

Issue

• dim_agent_hierarchy.agent_id: STRING (can be numeric “721995” or minted “NAME:abc123”)
• dim_agent_identity.upstream_agent_id: STRING (nullable, stores upstream ID)
• dim_agent_identity.agent_key: STRING (hash-based, deterministic)

LEFT JOIN agent_hierarchy ah
  ON s1c.tenant_id = ah.tenant_id  -- ❌ tenant_id doesn't exist in hierarchy
  AND s1c.agent_id = ah.agent_id

• How to join dim_agent_identity to dim_agent_hierarchy?
• Should join be: agent_key → upstream_agent_id → dim_agent_hierarchy.agent_id?
• Or: agent_name match?
• Or: Direct agent_id match (if hierarchy uses minted IDs)?

1. What is the join key between dim_agent_identity and dim_agent_hierarchy?
2. Does dim_agent_hierarchy.agent_id contain:
   • Upstream numeric IDs (721995, 668078)?
   • Minted hash IDs (“NAME:abc123”)?
   • Both?
3. Should semantic view join via agent_key → upstream_agent_id → dim_agent_hierarchy.agent_id?

​

🟡 YELLOW FLAG #1: agent_key Hash Length Mismatch

​

Issue

return f"NAME:{hash_obj.hexdigest()[:12]}"  # 12 chars

CONCAT('AGENT:', TO_HEX(SHA256(...))[:16])  # 16 chars

• ⚠️ Different lengths: agent_id = “NAME:abc123def456” (18 chars), agent_key = “AGENT:abc123def4567890” (22 chars)
• ⚠️ Not directly comparable: Cannot use agent_id as fallback for agent_key in hash

​

🟡 YELLOW FLAG #2: Migration Script Syntax Error

​

Issue

CONCAT('AGENT:', TO_HEX(SHA256(UPPER(TRIM(agent_name))))[:16]) AS agent_key

CONCAT('AGENT:', SUBSTR(TO_HEX(SHA256(UPPER(TRIM(agent_name)))), 1, 16)) AS agent_key

​

🟡 YELLOW FLAG #3: Missing Migration File

​

Issue

• integration/bigquery/sql/migrations/add_tenant_id_to_business_mapping.sql (NOT CREATED)
• integration/bigquery/sql/migrations/populate_agent_identity.sql (NOT CREATED)

​

✅ ALIGNMENT CHECKS

​

Schema Alignment

• ✅ dim_business_mapping_schema.json includes tenant_id (updated)
• ✅ dim_agent_identity_schema.json created with correct fields
• ❌ dim_agent_hierarchy_schema.json missing tenant_id (RED FLAG #1)

​

MERGE Key Alignment

• ✅ dim_business_mapping: MERGE key (tenant_id, normalized_name) documented
• ✅ dim_agent_identity: MERGE key (tenant_id, agent_key) documented
• ✅ Both immutable fields (business_id, agent_key) correctly marked as never updated

​

Data Type Alignment

• ✅ stage3_snapshots.agent_id: STRING
• ✅ dim_agent_hierarchy.agent_id: STRING
• ✅ Type-compatible for joins

​

Hash Signature Alignment

• ✅ stage3_component_id hash documented in both view and tests
• ✅ stage1_row_id hash documented in both view and tests
• ⚠️ agent_key generation inconsistent (RED FLAG #2)

​

🔴 CRITICAL ACTION ITEMS

​

Immediate (Blocking)

1. Fix RED FLAG #1: Add tenant_id to dim_agent_hierarchy schema OR remove from semantic view
2. Fix RED FLAG #2: Standardize agent_key generation strategy (recommend hash-based)
3. Fix RED FLAG #3: Document explicit join strategy for dim_agent_hierarchy → dim_agent_identity

​

Short-term (Required)

4. Fix YELLOW FLAG #2: Update SQL syntax ([:16] → SUBSTR(..., 1, 16))
5. Fix YELLOW FLAG #3: Create migration SQL files
6. Clarify: How to handle hardcoded numeric IDs (721995, 668078) in dim_agent_hierarchy

​

Summary: Red Flags Found