​

Dashboard Data Contract Map - Code Validation

​

Summary (3–6 bullets)

• Validates dashboard contract assumptions against actual query functions and SQL view definitions.
• Extracts concrete FROM objects for endpoint-related backend query paths.
• Resolves naming and contract contradictions (table names, ranking behavior, source objects).
• Produces a dependency matrix from API endpoints to base BigQuery objects.

​

When to use this (3–6 bullets)

• When reconciling docs with backend/query reality.
• Before refactoring dashboard query functions or analytics views.
• During incident response for contract drift or data source confusion.
• When auditing whether endpoint behavior still matches documented contracts.

​

What you’ll walk away with (2–5 bullets)

• A code-backed contract map for dashboard endpoint dependencies.
• Clear resolutions for common source-of-truth ambiguities.
• A validation checklist you can reuse after query/view changes.

​

1. Query Functions - FROM Clauses

​

get_ceo_snapshot_from_view

FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`

​

get_ceo_metrics_range

1. Financial metrics:
   Copy

   Ask AI

   FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`
   
2. Distinct counts:
   Copy

   Ask AI

   FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
   
3. Active snapshot:
   Copy

   Ask AI

   FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`
   
4. MoM fields (for single period):
   Copy

   Ask AI

   FROM `payroll-bi-gauntlet.payroll_analytics.ceo_snapshot`
   

​

get_agent_commissions_range

FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`

​

get_top_businesses_from_view

FROM `payroll-bi-gauntlet.payroll_analytics.top_businesses`

​

get_top_businesses_ytd

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

​

get_top_businesses_qtd

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

​

get_top_agents_from_view

FROM `payroll-bi-gauntlet.payroll_analytics.top_agents`

​

get_top_agents_ytd

FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`

​

get_top_agents_qtd

FROM `payroll-bi-gauntlet.payroll_processed.stage3_snapshots`

​

get_growth_loss_summary

• get_growth_loss_summary_mom() → calls get_growth_loss_details_from_view() → queries stage1_snapshots (FULL OUTER JOIN)
• get_growth_loss_summary_snapshot() → queries stage1_snapshots directly

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

​

get_growth_loss_details

FROM `payroll-bi-gauntlet.payroll_analytics.business_growth_loss`

​

get_new_lost_businesses

-- Current period CTE:
FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`

-- Previous period CTE:
FROM `payroll-bi-gauntlet.payroll_raw.stage1_snapshots`

​

get_business_health_distribution

FROM `payroll-bi-gauntlet.payroll_analytics.business_growth_loss`

​

2. View SQL - SELECT Clauses

​

analytics_ceo_snapshot.sql

SELECT
  s.tenant_id,
  s.period_label,
  s.total_businesses,
  s.total_employees,
  s.gross_payout,
  s.chargebacks,
  s.net_payout,
  -- MoM changes
  s.total_businesses - COALESCE(p.prev_total_businesses, 0) as business_change_mom,
  s.total_employees - COALESCE(p.prev_total_employees, 0) as employee_change_mom,
  s.gross_payout - COALESCE(p.prev_gross_payout, 0) as gross_payout_change_mom,
  s.net_payout - COALESCE(p.prev_net_payout, 0) as net_payout_change_mom,
  -- MoM percentages
  CASE WHEN COALESCE(p.prev_total_businesses, 0) > 0 
    THEN ROUND((s.total_businesses - p.prev_total_businesses) / p.prev_total_businesses * 100, 2)
    ELSE 0 END as business_change_pct_mom,
  CASE WHEN COALESCE(p.prev_total_employees, 0) > 0
    THEN ROUND((s.total_employees - p.prev_total_employees) / p.prev_total_employees * 100, 2)
    ELSE 0 END as employee_change_pct_mom,
  CASE WHEN COALESCE(p.prev_gross_payout, 0) > 0
    THEN ROUND((s.gross_payout - p.prev_gross_payout) / p.prev_gross_payout * 100, 2)
    ELSE 0 END as gross_payout_change_pct_mom,
  CASE WHEN COALESCE(p.prev_net_payout, 0) > 0
    THEN ROUND((s.net_payout - p.prev_net_payout) / p.prev_net_payout * 100, 2)
    ELSE 0 END as net_payout_change_pct_mom

​

analytics_top_businesses.sql

SELECT
  tenant_id,
  period_label,
  business_label AS business_name,
  SUM(total) AS gross_payout,
  COUNT(DISTINCT member_id) AS employees,
  SAFE_DIVIDE(SUM(total), NULLIF(COUNT(DISTINCT member_id), 0)) AS per_employee,
  CASE
    WHEN COUNT(DISTINCT member_id) >= 250 THEN 'Large'
    WHEN COUNT(DISTINCT member_id) >= 100 THEN 'Medium'
    WHEN COUNT(DISTINCT member_id) >= 25 THEN 'Small'
    ELSE 'Micro'
  END AS status

​

analytics_top_agents.sql

• payroll-bi-gauntlet.payroll_processed.stage3_snapshots (for commissions)
• payroll-bi-gauntlet.payroll_raw.stage1_snapshots (for employee counts, via JOIN)

SELECT
  c.tenant_id,
  c.period_label,
  c.agent_name,
  c.total_commission,
  c.businesses,
  COALESCE(e.employees, 0) AS employees,
  SAFE_DIVIDE(c.total_commission, NULLIF(e.employees, 0)) AS avg_pepm

​

analytics_business_growth_loss.sql

SELECT
  tenant_id,
  business_label AS business_name,
  period_label,
  employees AS current_employees,
  LAG(employees) OVER (PARTITION BY tenant_id, business_label ORDER BY period_label) AS previous_employees,
  employees - LAG(employees) OVER (PARTITION BY tenant_id, business_label ORDER BY period_label) AS net_change,
  SAFE_DIVIDE(
    employees - LAG(employees) OVER (PARTITION BY tenant_id, business_label ORDER BY period_label),
    LAG(employees) OVER (PARTITION BY tenant_id, business_label ORDER BY period_label)
  ) * 100 AS pct_change

​

3. Object Dependency Table

​

4. Contradiction Resolution

​

4.1 stage1_snapshot vs stage1_snapshots

• All query functions use: payroll-bi-gauntlet.payroll_raw.stage1_snapshots
• View SQL files use: payroll-bi-gauntlet.payroll_raw.stage1_snapshots
• No references to stage1_snapshot (singular) found in codebase

​

4.2 Rank Computed in View vs Rank Added in Backend

• View uses QUALIFY ROW_NUMBER() OVER (PARTITION BY tenant_id, period_label ORDER BY SUM(total) DESC) <= 10 to LIMIT results
• View does NOT include rank column in SELECT clause
• Backend function (get_top_businesses_from_view) adds: ROW_NUMBER() OVER (ORDER BY gross_payout DESC) as payout_rank
• Backend also adds: 0 as employee_change (placeholder)

• View uses QUALIFY ROW_NUMBER() OVER (PARTITION BY c.tenant_id, c.period_label ORDER BY c.total_commission DESC) <= 10 to LIMIT results
• View does NOT include rank column in SELECT clause
• Backend function (get_top_agents_from_view) adds: ROW_NUMBER() OVER (ORDER BY total_commission DESC) as rank

• get_top_businesses_ytd(): Adds ROW_NUMBER() OVER (ORDER BY total_payout DESC) AS rank in SELECT
• get_top_businesses_qtd(): Adds ROW_NUMBER() OVER (ORDER BY total_payout DESC) AS rank in SELECT
• get_top_agents_ytd(): Adds ROW_NUMBER() OVER (ORDER BY total_commission DESC) AS rank in SELECT
• get_top_agents_qtd(): Adds ROW_NUMBER() OVER (ORDER BY total_commission DESC) AS rank in SELECT

• Views use QUALIFY ROW_NUMBER() ... <= 10 to LIMIT results to top 10, but do NOT expose rank column
• Backend query functions add rank column using ROW_NUMBER() in their SELECT clauses
• This is intentional: views filter to top 10, backend adds rank for display purposes

​

4.3 stage3_snapshots vs agent_report_v5

• All dashboard query functions query payroll_processed.stage3_snapshots
• get_top_agents_from_view() queries payroll_analytics.top_agents view, which joins stage3_snapshots
• get_agent_commissions_range() queries payroll_processed.stage3_snapshots directly
• No references to agent_report_v5 found in dashboard-related query functions

​

5. Summary of Base Objects

​

Tables (Source of Truth)

1. payroll_raw.stage1_snapshots
   • Used by: CEO metrics (distinct counts), Top Businesses (YTD/QTD), Growth/Loss, New/Lost Businesses
   • Key fields: tenant_id, period_label, business_label, member_id, credit, debit, total, payee_agent_id
2. payroll_processed.stage3_snapshots
   • Used by: Agent Commissions, Top Agents (YTD/QTD), Top Agents view (via JOIN)
   • Key fields: tenant_id, period_label, business_name, downline_agent_name, agent_total, tpa_applied

​

Views (Pre-aggregated)

1. payroll_analytics.ceo_snapshot
   • Base: stage1_snapshots
   • Used by: CEO Metrics (MTD), CEO Metrics Range (financial metrics)
2. payroll_analytics.top_businesses
   • Base: stage1_snapshots
   • Used by: Top Businesses (MTD)
3. payroll_analytics.top_agents
   • Base: stage3_snapshots (commissions) + stage1_snapshots (employee counts, via JOIN)
   • Used by: Top Agents (MTD)
4. payroll_analytics.business_growth_loss
   • Base: stage1_snapshots
   • Used by: Growth/Loss Details, Business Health Distribution

​

6. Validation Checklist

• All query functions mapped to FROM objects
• All view SQL SELECT clauses extracted
• Object dependency table created
• stage1_snapshots vs stage1_snapshot resolved (plural is correct)
• Rank computation resolved (views filter, backend adds rank column)
• stage3_snapshots vs agent_report_v5 resolved (dashboard uses stage3_snapshots)