Retention Dashboard Pipeline (DATASTORE.MART)
Three-step Snowflake retention pipeline that refreshes per-agent retention metrics and manager-level attrition summary outputs for Power BI retention reporting.
Overview
Orchestrates three procedures in sequence to rebuild retention and attrition datasets through full table refresh patterns.
Business Purpose
Publish a governed retention model that combines risk, ESAT, and CHEQIN metrics with attrition rollups used by operations leadership.
Technology Stack
Business Impact
Windowing or inclusion-rule errors can misstate retention-risk profiles, attrition trend baselines, and manager comparisons in business reviews.
Task Graph
Root task plus two dependency-triggered child tasks: retention build, attrition staging, and attrition summary.
Execution Cadence
Root schedule runs daily at 05:00 ET on REPORTING_WH; child tasks execute with AFTER dependencies.
Objects in Scope
3 tasks, 3 procedures, and 3 target tables under DATASTORE.MART for dashboard-facing outputs.
Run Governance
All procedures log to SP_LOG_PROCEDURE_RUN with transactional rollback and re-raise behavior on failure.
Pipeline Object Inventory
| Object Group | Count | Objects |
|---|---|---|
| Tasks | 3 | TASK_UPDATERETENTION_V2, TASK_UPDATERETENTION_V2_PART2, TASK_UPDATERETENTION_V2_PART3 |
| Procedures | 3 | SP_GETRETENTIONDATA, SP_GETATTRITIONDATA, SP_POPULATEATTRITIONDATA |
| Target Tables | 3 | FACT_RETENTION_V2, ATTRITION_HOLDING_TABLE, FACT_RETENTION_ATTRITION |
Power BI Data Contract
| Table | Grain | Dashboard Usage |
|---|---|---|
| FACT_RETENTION_V2 | One row per in-scope agent (DOMAINID) | Per-agent risk, ESAT, CHEQIN, hierarchy, and reporting-window metrics. |
| ATTRITION_HOLDING_TABLE | Date plus employee (DATE_DATE, EMPLOYEEID, DOMAINID) | Intermediate attrition staging for daily flags and hierarchy rollup attributes. |
| FACT_RETENTION_ATTRITION | One row per DOMAINID plus WAH | Manager-level 3-month and YTD attrition terms and average headcount outputs. |
End-to-End Processing Flow
- TASK_UPDATERETENTION_V2 executes SP_GETRETENTIONDATA and fully rebuilds FACT_RETENTION_V2 with in-scope retention metrics.
- TASK_UPDATERETENTION_V2_PART2 executes SP_GETATTRITIONDATA and fully rebuilds ATTRITION_HOLDING_TABLE for the rolling 100-day attrition stage.
- TASK_UPDATERETENTION_V2_PART3 executes SP_POPULATEATTRITIONDATA and fully rebuilds FACT_RETENTION_ATTRITION with manager-level 3-month and YTD summaries.
- Uses rolling windows for risk (30 days), ESAT (previous 3 full months), CHEQIN save (rolling 3 months), and CHEQIN completion (12 closed assignment periods).
- Population scope includes VT06, VT07, VT08 with DLA labor category, non-seasonal employees, and status codes 801 and 802.
- Aligns risk, ESAT, and save activity to client tenure by removing records before each employee client start date.
- Removes inactive status 802 rows with no CHEQIN activity or paid hours below 4 before final load.
Load pattern: TRUNCATE and full insert into FACT_RETENTION_V2 each run.
- Builds a 100-day date spine through yesterday and resolves hierarchy GM, GM-1, and GM-2 by day and domain.
- Applies VT06, VT07, VT08 scope with DLA and non-seasonal employee filtering.
- Drops domains that are only terminated (802) and never active (801) during the window.
- Ghost-term suppression zeros term contribution where tenure is under 30 days and paid hours are under 4.
Load pattern: DELETE and full insert into ATTRITION_HOLDING_TABLE each run.
- Resolves rolling 3-month and YTD windows and expands each staged row into five hierarchy domain levels.
- Aggregates by DOMAINID plus WAH for two summary sets: 3-month terms and average headcount, and YTD terms and average headcount.
- Builds final manager-level output by full-joining 3-month and YTD summary sets.
- Publishes ATTRITION_3_MONTH_TERMS, ATTRITION_YTD_TERMS, ATTRITION_3_MONTH_AVG_HC, and ATTRITION_YTD_AVG_HC.
Load pattern: DELETE and full insert into FACT_RETENTION_ATTRITION each run.
Primary Dependencies and Validation Focus
Validation priorities: resumed task graph health, refreshed UPDATEDDT values, expected metric-window metadata fields, non-null 3-month and YTD attrition outputs, and successful procedure-run logs for all three procedures.