Skip to content

feat(people): build fct_staff_attrition #3579

@cristinabaldor

Description

@cristinabaldor

Summary

Implement fct_staff_attrition as a periodic snapshot fact table capturing each staff member's attrition status at the close of each academic year, across three measurement methodologies (foundation, nj_compliance, recruitment).

This replaces fct_staff_attrition and fct_staff_terminations from the prototype marts folder and plugs into the star schema defined in the 2026-03-27 design doc.

Design

See docs/superpowers/specs/2026-04-02-fct-staff-attrition-design.md.

Key decisions

  • Single model: combines attrition + termination context (fct_staff_terminations dropped — no downstream consumers)
  • Grain: employee × academic_year × attrition_type (3 rows per person per year)
  • snapshot_date: per-methodology window close for retained staff; termination effective date for attritors
  • Source: int_people__staff_roster_history
  • Location: models/marts/facts/fct_staff_attrition.sql

Acceptance criteria

  • fct_staff_attrition.sql built from int_people__staff_roster_history
  • Surrogate PK: employee_number + academic_year + attrition_type
  • FKs to dim_staff and dim_staff_work_assignments (SCD2, resolved on snapshot_date)
  • All three attrition types present with correct cohort window logic
  • Interns with Internship Ended excluded from all cohorts
  • Artifact termination reasons excluded
  • fct_staff_terminations removed
  • dbt tests pass

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions