Attrition Dashboard Pipeline (SP_GETATTRITION)
Daily Snowflake procedure and task pipeline that transforms employee status and termination records into governed fact outputs for the Power BI Attrition dashboard.
Overview
Runs a 30-day rolling refresh across attrition summary, manager, and detail fact tables while preserving historical lock boundaries for previously frozen periods.
Business Purpose
Provide a trusted attrition model with stable numerators, denominators, and segmentation fields used by leadership and operations teams in Power BI trend and drillthrough reporting.
Technology Stack
Business Impact
Incorrect term classification or window controls can skew attrition rates, manager comparisons, and training/seasonal visibility used for workforce decisions.
Execution Cadence
TASK_UPDATEATTRITION runs daily at 05:00 ET and calls SP_GETATTRITION with default end date logic.
Processing Window
Each run rebuilds V_ENDDATE and V_STARTDATE for a 30-day span and only touches rows above the freeze boundary.
History Protection
Rows older than 60 days from V_ENDDATE are marked FROZEN = TRUE and excluded from future deletes/inserts.
Operational Logging
Success and failure telemetry is captured through SP_LOG_PROCEDURE_RUN with rollback and re-raise on load exceptions.
Power BI Data Contract
| Table | Grain | Dashboard Usage |
|---|---|---|
| FACT_ATTRITION | Date plus org slice plus reason slice plus tenure plus WAH/BYOD | Primary trend and KPI source for numerators and denominators. |
| FACT_ATTRITIONBYMANAGER | Date plus org slice plus MANAGERDOMAINID | Leader-level trends and manager comparison views. |
| FACT_ATTRITIONDETAILS | Employee-level termination event row | Drillthrough diagnostics with reason, timing, and employee context. |
Daily Processing Flow
- Resolve run-date variables, freeze boundary, and date spine rowcount for the current execution window.
- Build temporary paid-hours aggregates to support ghost-termination filtering across a 60-day lookback span.
- Build daily hierarchy expansion with GM, GM-1, and GM-2 per employee per in-window date.
- Build employee staging with status filters, vertical restriction, EngageHR enrichment, vertical/profit-center corrections, and seasonal override.
- Build TMP_ATTRITION cross-joined by employee/date with tenure, agent, seasonal, term, training, and reason-classification metrics.
- Inside one transaction, delete unfrozen rows and reinsert refreshed rows into all three attrition fact tables.
- Apply freeze updates for rows older than 60 days, commit, then log run details and return procedure metadata.
- Population scope is limited to vertical codes VT06, VT07, VT08, and VT17.
- Rows must be non-deleted and in employee statuses 801 (active) or 802 (recently terminated).
- SCD rows must overlap the run window and terms before window start are excluded from processing scope.
- GM assignment is deterministic by vertical, with VT08 optionally mapped to Rod.Hoddinott when hierarchy indicates that path.
These guards are applied before fact insertion so all downstream visuals share the same inclusion baseline.
- Ghost term criteria: less than 30 days from hire to term and fewer than 4 paid hours in the lookback window.
- Ghost terms still exist at detail level but contribute zero to agent and term numerator/denominator metric columns.
- WITHINTRAINING flags terms at or before TRAININGENDDATE when paid-hours threshold is met.
- Seasonal employee treatment is force-overridden for listed DOMAINID values in ATTRITION_TEMP_DOMAINS.
This logic protects published rates from onboarding artifacts while preserving diagnostic transparency in detail outputs.
- Term rows are identified where NEWTERMDATE equals THEDATE for the generated spine date.
- Reasons beginning with I are classified as involuntary; non-I reasons are classified as voluntary.
- Blank or null reason values are mapped to UNKNOWN on the applicable voluntary or involuntary column.
- Rows on or below V_FROZENDATE are never deleted or reinserted in later runs.
If no frozen rows exist yet, V_FROZENDATE is null and guard predicates that require THEDATE > V_FROZENDATE do not process rows until a freeze boundary is established.
Primary Dependencies and Consumers
Business outcomes: stable attrition trend publication, manager-level comparability, and drillthrough-ready employee termination diagnostics for the Power BI reporting model.