Skip to main content

​
Phase 1.4 Deliverable A: Authoritative PEPM Normalization

​
Normalization Expressions (Authoritative)

​
PEPM Agent Name Normalization

Expression: UPPER(TRIM(CONCAT(agent_first_name, ' ', agent_last_name))) Source Table: payroll-bi-gauntlet.payroll_raw.dim_agent_pepm Source Columns:
  • agent_first_name (STRING)
  • agent_last_name (STRING)
File References:
  • integration/bigquery/sql/validation/phase1_3_identity_resolution_validation.sql:15
  • integration/bigquery/sql/views/analytics_agent2_pepm_view.sql:20
  • api/bigquery/queries.py:7176
  • integration/bigquery/sql/tables/dim_agent_pepm_agent_map.sql:20 (comment)
  • integration/bigquery/schemas/dim_agent_pepm_agent_map_schema.json:18 (description)

​
PEPM Business Name Normalization

Expression: UPPER(TRIM(business_name)) Source Table: payroll-bi-gauntlet.payroll_raw.dim_agent_pepm Source Columns:
  • business_name (STRING)
File References:
  • integration/bigquery/sql/validation/phase1_3_identity_resolution_validation.sql:16
  • integration/bigquery/sql/views/analytics_agent2_pepm_view.sql:21
  • api/bigquery/queries.py:7177
  • integration/bigquery/sql/tables/dim_agent_pepm_agent_map.sql:21 (comment)
  • integration/bigquery/schemas/dim_agent_pepm_agent_map_schema.json:24 (description)

​
Confirmation

PEPM Raw Source Fields Used in Production:
  • dim_agent_pepm table contains:
    • agent_first_name (STRING, NULLABLE)
    • agent_last_name (STRING, NULLABLE)
    • business_name (STRING, NULLABLE)
    • pepm_rate (NUMERIC, NULLABLE)
    • root_owner (STRING, NULLABLE)
Schema Source: integration/bigquery/scripts/load_reference_tables.py:168-175 Critical Note: The runbook (docs/runbooks/phase1_3_prod_population.md) incorrectly shows Stage3 normalization (UPPER(TRIM(COALESCE(downline_agent_name, agent_name)))) in lines 37 and 65. This is NOT the authoritative normalization. The authoritative normalization uses CONCAT(agent_first_name, ' ', agent_last_name) from the PEPM raw table.