TM skills
1. Dataset Name
tm_skills_and_gaps_v3
2. Description
Employee-level skill gap and role-fit dataset that combines an employee's skill-gap assessments (manager rating vs. benchmark), their broader employee-skills profile, and role-skill fit, then computes per-employee summary percentages (skill assessment coverage, skill-fit rate) as window aggregates. Built to power skill gap and role-readiness analyses at the individual, role, and organizational level.
3. Source Tables
- customer_views.employee
- customer_views.employee_skill_gaps (aliased esg — role skill gap records per employee, LEFT JOIN on profile_id)
- customer_views.employee_skills (aliased es — skills on employee profiles, LEFT JOIN on profile_id)
- customer_views.role_skills (aliased rs — required skills for the employee's role, joined via es.jie_role_id = rs.position_id AND es.skill_name = rs.skill_name)
4. Grain
Not a clean 1:1 grain — flag before use. employee_skill_gaps and employee_skills are each LEFT JOINed onto employee independently, both keyed only on profile_id (not on skill name or any shared key between them). This means an employee with, say, 5 skill-gap records and 8 employee-skill records will produce 5 × 8 = 40 rows — one row per combination of a skill-gap record and an employee-skill record, not one row per actual skill. role_skills is then joined onto es.jie_role_id/es.skill_name, adding role_skill_name/is_role_skill per employee-skill row (this join does not fan out further, since it's a targeted lookup).
5. Dashboards Supported
- Skill Gap Analysis Dashboard
- Talent Development / Skill Readiness Dashboard
(Best-guess based on field mix — confirm against actual dashboard configs before publishing.)
6. Metrics Supported
| Metric | Supported Breakdowns |
| Manager Skill Assessment Coverage (%) | Business unit, job function, employee level, seniority — see caveat below on denominator |
| Self Skill Assessment Coverage (%) | Same as above |
| Skill Fit Ratio (has skill / total) | Same as above |
| Manager Skill-Fit Rate (rating ≥ benchmark) | Same as above |
| Self Skill-Fit Rate (rating ≥ benchmark) | Same as above |
| Skill Gap Classification (Gap / No Gap / No Rating) | By skill, by manager vs. self rating |
| Role-Skill Coverage (is_role_skill) | Position, role variant |
7. Known Exclusions / Filters Applied
- No rows are filtered out — all joins are LEFT JOINs, so every employee, skill-gap record, and employee-skill record is retained (subject to the fan-out described in Grain).
- The per-employee percentage window calculations (gap_perc_manager_skill_assessed, gap_perc_self_skill_assessed, gap_ratio_skills, gap_perc_manager_skill_fit, gap_perc_self_skill_fit) are computed over COUNT(1) OVER (PARTITION BY emp.profile_id) — i.e., the count of rows in the fanned-out result, not the count of distinct skill-gap records. Because of the cross-join behavior described above, this denominator is inflated for any employee who also has multiple employee_skills records, which will understate/distort these percentages. Treat these percentage fields with caution until the join logic is corrected (an intersecting key between esg and es, or a de-duplicated employee count, is needed for these to be numerically correct at the individual level).
- Percentage bucket groupings (gap_manager_skill_assessment_grouping, etc.) inherit whatever distortion exists in the underlying percentage fields.
- gap_manager_rating_benchmark_gap / gap_self_rating_benchmark_gap are computed per-row (not windowed), so these are unaffected by the fan-out issue and can be trusted at face value for any given skill-gap row.
8. Schema
| Column Name | Data Type | Notes |
| profile_id | varchar | Employee's profile identifier |
| employee_id | varchar | Employee identifier |
| first_name | varchar | Employee first name |
| last_name | varchar | Employee last name |
| employee_name | varchar | Derived: CONCAT(first_name, ' ', last_name) |
| business_unit | varchar | Employee's business unit |
| job_function | varchar | Aliased from lob |
| employee_level | varchar | Aliased from level |
| location | varchar | Employee location, full string |
| location_country | varchar | Employee location, country |
| seniority | varchar | Employee seniority |
| gender | varchar | Raw gender field |
| ef_gender | varchar | Normalized/EF-standard gender field |
| race | varchar | Raw race field |
| ef_race | varchar | Normalized/EF-standard race field |
| is_employee | bigint | Flag (0/1); active employee status |
| is_alumni | bigint | Flag (0/1); employee is now an alum |
| termination_date | datetime | Null unless employee has left |
| profile_activated_ts | datetime | Timestamp profile was activated |
| employee_email | varchar | Employee's email |
| manager_name | varchar | Employee's manager name — new in v3 |
| manager_email | varchar | Employee's manager email — new in v3 |
| position_id | varchar | From employee_skill_gaps |
| gap_skill_name | varchar | Skill name from employee_skill_gaps |
| skill_benchmark | double | Benchmark rating for the skill |
| gap_manager_rating | double | Manager rating from employee_skill_gaps |
| gap_manager_rating_ts | datetime | Timestamp of manager rating |
| gap_self_rating | double | Self rating from employee_skill_gaps |
| gap_self_rating_ts | datetime | Timestamp of self rating |
| gap_inferred_proficiency | double | System-inferred proficiency — new in v3 |
| gap_inferred_proficiency_ts | datetime | Timestamp of inferred proficiency — new in v3 |
| gap_is_highlighted | bigint | Flag; skill highlighted on profile — new in v3 |
| gap_num_endorsements | bigint | Count of endorsements for this skill-gap record — new in v3 |
| is_role_approved | bigint | Flag from employee_skill_gaps |
| employee_has_skill | bigint | Flag from employee_skill_gaps |
| es_jie_role_id | varchar | Role ID from employee_skills |
| es_role_variant_id | varchar | Role variant ID from employee_skills — new in v3 |
| es_skill_name | varchar | Skill name from employee_skills |
| es_manager_rating | double | Manager rating from employee_skills |
| es_manager_rating_ts | datetime | Timestamp of manager rating on employee_skills |
| es_self_rating | double | Self rating from employee_skills |
| es_self_rating_ts | datetime | From employee_skills |
| es_inferred_proficiency | double | From employee_skills — new in v3 |
| es_inferred_proficiency_ts | datetime | From employee_skills — new in v3 |
| es_is_highlighted | bigint | Flag from employee_skills — new in v3 |
| es_num_endorsements | bigint | Count of endorsements from employee_skills — new in v3 |
| role_skill_name | varchar | Populated only if es_skill_name matches a role_skills entry for that role |
| is_role_skill | bigint | Derived: 1 if role_skill_name is not null, else 0 |
| gap_perc_manager_skill_assessed | double | Windowed % — see denominator caveat above |
| gap_perc_self_skill_assessed | double | Windowed % — see denominator caveat above |
| gap_ratio_skills | double | Windowed ratio — see denominator caveat above |
| gap_perc_manager_skill_fit | double | Windowed % — see denominator caveat above |
| gap_perc_self_skill_fit | double | Windowed % — see denominator caveat above |
| gap_manager_rating_benchmark_gap | varchar | Per-row: Gap / No Gap / No Rating (manager rating vs. benchmark) |
| gap_self_rating_benchmark_gap | varchar | Per-row: Gap / No Gap / No Rating (self rating vs. benchmark) |
| gap_manager_skill_assessment_grouping | varchar | Bucketed gap_perc_manager_skill_assessed: NA, 0-20%, 20-40%, 40-60%, 60-80%, 80-100%, Unavailable |
| gap_self_skill_assessment_grouping | varchar | Bucketed gap_perc_self_skill_assessed, same bands |
| gap_role_perc_grouping | varchar | Bucketed gap_ratio_skills, same bands |
| gap_perc_manager_skill_fit_grouping | varchar | Bucketed gap_perc_manager_skill_fit, same bands |
| gap_perc_self_skill_fit_grouping | varchar | Bucketed gap_perc_self_skill_fit, same bands |