1. Dataset Name

tm_diversity

2. Description

An employee-level dataset built for diversity, equity, and inclusion analytics on the Talent Management platform. Each row represents one employee-skill pair — the base employee record is enriched with mentorship flags, direct report counts, succession plan data, and profile skill information. Key derived flags (is_female, is_urm, is_leader, is_stagnant, is_progressing, tenure_days) are computed inline from employee attributes. This dataset powers headcount diversity metrics, gender and race distribution charts, succession pipeline diversity, and skill-level gender distribution on the TM Diversity dashboard.

3. Source Tables

• customer_views.employee (primary employee record — aliased emp)
• customer_views.employee_skills (aliased es in employee_skills_enriched CTE — profile skills, LEFT JOINed to customer_views.role_skills to flag role skill matches)
• customer_views.role_skills (aliased rs — required role skills, joined via jie_role_id = position_id and skill_name)
• customer_views.profile_data (emp_mentorship CTE — namespace = 'mentorship', provides is_mentor and is_mentee flags per profile)
• customer_views.succession_plan (succession_agg CTE — incumbent and successor attributes for succession pipeline diversity metrics)

4. Grain

One row per employee-skill pair. An employee with ten profile skills will have ten rows. Succession and mentorship columns are denormalized at the employee level (same value on every row for a given employee). num_reports_data derives the manager's direct report count by grouping on manager_email, then joining back to the employee table.

5. Dashboards Supported

• TM Diversity Dashboard

6. Metrics Supported

7. Known Exclusions / Filters Applied

• employee_id IS NOT NULL is the primary active-employee filter applied at the widget level — it excludes alumni, incomplete profiles, and any record without a valid employee ID.
• is_female is derived as CASE WHEN emp.gender = 'female' THEN TRUE ELSE FALSE END — case-sensitive match on the raw gender field.
• is_urm is derived via COALESCE(CAST(get_json_object(attributes_json, '$.is_ethnically_diverse[0]') AS BOOLEAN), FALSE) — URM status comes from the is_ethnically_diverse attribute in the employee's JSON attributes, defaulting to FALSE when absent.
• is_leader is derived as CASE WHEN seniority IN ('CXO', 'Vice President', 'Director') THEN TRUE ELSE FALSE END.
• successor_is_urm in the succession_agg CTE classifies a successor as URM when their race falls into: American Indian or Alaskan Native, Black, Hispanic, Two or More Races, or Native Hawaiian or Pacific Islander (all lowercased via LOWER()).
• Succession columns (succession_position_id, successor_gender, successor_is_urm, etc.) are NULL for employees not found in customer_views.succession_plan as an incumbent. % Pipelines metrics use NULLIF to guard against division by zero.
• business_unit, lob (aliased as job_function), and location_country are all lowercased via LOWER() at dataset level.
• All metrics are all-time snapshots — no date range filters are applied in this dataset.

8. Schema