CEO Report Pipeline (DATASTORE.CEO)
Snowflake procedure and task pipeline that publishes the weekly CEO scorecard by combining client-entered KPI performance with warehouse-derived employee metrics for Power BI executive reporting.
Overview
Builds a weekly client scorecard across C metrics (client KPI outcomes) and E metrics (employee attrition and ESAT), then materializes a four-table hierarchy used for executive drill-down in Power BI.
Business Purpose
Provide a governed, repeatable executive performance baseline that unifies operations, employee outcomes, and composite scoring at client, LOB, location, and profit-center levels.
Technology Stack
Business Impact
Data quality or orchestration failures directly affect executive KPI visibility, score reliability, and weekly leadership decision cycles.
Output Model
4 reporting levels with linked drill-down from client weekly summary to raw C and E detail.
Core Procedures
SP_POPULATEREPORT, SP_RUNPASTCEO, and SP_UPDATENOTIFICATIONS drive full refresh and backfill behavior.
Execution Cadence
Daily root execution at 07:30 ET with sequential backfill pages and notification rebuild.
Run Governance
All procedures publish execution metadata to DATASTORE.MODELED.TBL_PROCEDURE_RUN_LOG.
Output Table Hierarchy
| Level | Table | Grain | Purpose |
|---|---|---|---|
| 1 | TBLLEVEL1_V3 | Client/week | Top-level aggregate with headcount-weighted C and E scores plus composite I score. |
| 2C | TBLLEVEL2C_V3 | Client/LOB/location/week | C metric KPI detail including attainment, weighted KPI score, rank, and rank score. |
| 2EO | TBLLEVEL2EO_V3 | Client/profit center/week | E and O detail for attrition, ESAT, YTD values, and composite score outputs. |
| 3 | TBLLEVEL3_V3 | Location/week (C and E row types) | Raw detail rows, using LOBID and WAH flags to separate KPI and employee-metric payloads. |
Pipeline Flow
- SP_POPULATEREPORT builds staging datasets for vertical assignment, KPI configuration, weekly KPI entries, E-metric inputs, and YTD aggregates.
- Inside a single transaction, all four output levels are deleted and rebuilt for the execution week, including C rows, EO rows, Level 3 detail, and Level 1 aggregates.
- Post-insert updates finalize weighted aggregate columns, composite scores, and compatibility safeguards for KPI definition changes.
- Execution details, statement row counts, and timing metrics are logged for observability and execution-level traceability.
C metric source lineage: KPI values are entered through the KPI Entry Application form, stored in the Analytics SQL Server system of record, then replicated by ETL into Snowflake for CEO report processing.
Week model: Sunday WEEKSTART through Saturday WEEKEND, with the execution date treated as the processing week anchor.
Scoring Model Highlights
- C KPI attainment is directional by LOWFLAG, clamped to 0-1, then combined as weighted KPISCORE up to a 1.0 cap.
- Rank score normalization maps rank position into a 1.0 to 0.0 range when eligible rank cohorts exist.
- E score combines attrition attainment and ESAT attainment at the profit-center grain, then feeds composite scoring with weighted C and E components.
- Current composite constants in procedure logic use C/E weighting for I score and preserve a C-only path for non-EO mapped clients.
The Level 3 compatibility guard nulls prior-week KPI slots for 13 weeks when KPI type changes occur at the same client/LOB/location key, preventing invalid trend comparisons.
Task Orchestration
- TASK_RUN_CEO executes the primary rebuild through SP_POPULATEREPORT.
- TASK_RUN_PAST_CEO_1 through _4 execute SP_RUNPASTCEO with page offsets 0, 2, 4, and 6 to backfill up to eight candidate historical weeks per cycle.
- TASK_RUN_NOTIFICATIONS executes SP_UPDATENOTIFICATIONS to rebuild TBLNOTIFICATION and TBLKPICOMPLETION for KPI-entry tracking.
- All tasks run on REPORTING_WH with centralized task error integration for failure notification routing.
Primary Data and View Dependencies
Business outcomes: repeatable executive score publication, auditable KPI-entry completion tracking, and resilient historical correction through bounded automated backfills.