Data Guide to People Data Analysis

Objectives for this page

This handbook page provides an overview of data definitions and data models used by the People Analytics team. Use this page for clarification on terminology, calculations, or models that use People data. If you have any questions please don’t hesitate to reach out to someone from the team for help.

Helpful places to start

• DBT Docs - This resource contains comprehensive documentation on all available dbt models. This is a great starting point to understanding our models. For specific People Models, please reference the Commonly Used Data Models section below for a starting point.
• Definitive guides to data subject areas managed by the Data team.
• Documentation on data pipelines for the technically curious analyst. This page goes into each data source and extraction details.
• People Group Tech Stack Guide for overview of all the integrations that go into and out of our HR systems and all the tools we use.

People Group Data Dictionary

Employment Data

Data pertaining to the worker and employment at GitLab once hired

Diversity, Inclusion, and Belonging (DIB)

Data pertaining to the demographics of a worker and how they choose to identify

Position

Information related to the position a team member holds at GitLab

Paid Time Off and Absence

Information related to Paid Time Off and Absence

Recruiting and Onboarding

Data pertaining to the Onboarding process and Recruiting metrics used by Talent Acquisition

Systems

Systems used by the People Group

Data Models

The Data Team is working with the People Analytics team to build data marts that allow users to explore our different people data sources

• “mart” models are a combination of dimensions and facts that are joined together to enable easy analysis.
• “rpt” (“report”) models are built with specific business logic for a specific use case.

Underneath each model is a clean lineage of dimensions and facts that can also be used for analysis. This list included all the prep tables, dimensions and facts that have been created for people analysis as well as the ones that we are planning to build in the upcoming quarters based on the Team Member Common ERD

Prep, dimension and fact tables

Marts

Model usage

dim_team_member

This table contains team members work and personal information. Sensitive columns are masked using dynamic masking and the fields are only visible by team members with the analyst_people role assigned in Snowflake. This table is a hybrid SCD (Type 1 + Type 2).

The table includes information regarding current team members, new hires who have records created in Workday before their start date and team members who were terminated in 2021 onwards. Team members who were terminated before 2021 are not captured in this model at this time. The grain of this table is one row per employee_id per valid_to/valid_from combination.

SELECT
  region,
  COUNT(DISTINCT employee_id)
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE
GROUP BY
  region

SELECT
  COUNT(DISTINCT employee_id)
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE

SELECT
  key_talent_status,
  COUNT(*) * 100 / SUM(COUNT(*)) OVER() AS key_talent_percentage
FROM
  PROD.COMMON.DIM_TEAM_MEMBER
WHERE
  is_current = TRUE AND is_current_team_member = TRUE
GROUP BY 1

dim_team

dim_team contains team (organizations) information. It includes information regarding teams and their hierarchy. It is a Type 2 SCD.

The goal of this table is to determine the team superior organization and the hierarchy of every organization in Workday. Additionally, this table provides team data such as team name, manager, team member count and date of inactivation for each organization.

The grain of this table is one row per Team ID per event.

SELECT
  COUNT(*)
FROM
  PREP.SENSITIVE.DIM_TEAM
WHERE
  is_current = TRUE AND is_team_active = TRUE

SELECT
  SUM(team_members_count)
FROM
  PREP.SENSITIVE.DIM_TEAM
WHERE
  is_current = TRUE AND is_team_active = TRUE

fct_team_member_position

fct_team_member_position contains team members’ job history, including any changes in their job profile or team. It provides a history of the team member’s job profile in detail. The grain of this table is one row per employee_id, team_id, effective_date and date_time_initiated combination. It includes all team members, regardless of their current employment status.

SELECT
  entity,
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  is_current = true
GROUP BY 1

SELECT
  position,
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  is_position_active
    AND is_current
GROUP BY 1

SELECT
  COUNT(*)
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  (job_specialty_single LIKE '%ModelOps%' OR job_specialty_multi LIKE '%ModelOps%')
    AND is_current;

SELECT
  *
FROM
  PROD.COMMON.FCT_TEAM_MEMBER_POSITION
WHERE
  position LIKE '%Backend Engineer%'
    AND entity = 'GitLab France S.A.S.'
     AND is_current

fct_team_member_status

This table contains termination reason, type, exit impact and employment status. Sensitive columns are masked and only visible by team members with the analyst_people role assigned in Snowflake. This table contains only past terminations.

The grain of this table is one row per employee_id, employment_status and status_effective_date combination.

SELECT
  COUNT(*)
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  exit_impact = 'Yes';

SELECT
  COUNT(*)
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  termination_type = 'Resignation (Voluntary)';

SELECT
  DISTINCT termination_reason
FROM
  PROD.COMMON.fct_team_member_status
WHERE
  termination_type = 'Resignation (Voluntary)'
    AND exit_impact = 'Yes';

fct_team_status

This table is a derived fact from fct_team_member_status and fct_team_member_position. Sensitive columns are masked and only visible by team members with the analyst_people role assigned in Snowflake. This table only contains one change in the team member’s position per effective date, as opposed to the fct_team_member_position table which contains all changes to a team member’s position profile, regardless of whether they became effective or not. This table doesn’t include future hires, only people working at GitLab as of today’s date.

SELECT
  *
FROM
  PROD.COMMON.fct_team_status
WHERE
  position LIKE '%Backend Engineer%'
    AND entity = 'GitLab France S.A.S.'
      AND employment_status = 'Active'
        AND is_current

Termination type and termination reason are masked fields, only team members with the analyst_people role in Snowflake can query it

SELECT
  team_id,
  COUNT(*)
FROM
  PROD.COMMON.fct_team_status
WHERE
    employment_status = 'Terminated'
       AND termination_type = 'Resignation (Voluntary)'
           AND is_current
GROUP BY 1;

Termination type and termination reason are masked fields, only team members with the analyst_people role in Snowflake can query it

SELECT
  management_level, COUNT(*)
FROM
  PROD.COMMON.fct_team_status
WHERE
    employment_status = 'Terminated'
       AND termination_type = 'Resignation (Voluntary)'
          AND management_level != 'Individual Contributor'
           AND is_current
GROUP BY 1;

fct_team_member_absence

This table contains team members’ absence information. Sensitive columns are masked using dynamic masking and the fields are only visible by team members with the analyst_people role assigned in Snowflake. This table is a Type 0 SCD

The table includes information from Time Off By Deel, . The grain of this table is one row per team member ID per pto_uuid and absence_date combination.

mart_team_member_absence

This table is a derived mart from fct_team_member_absence and dim_team_member. Sensitive columns are masked and only visible by team members with the analyst_people role assigned in Snowflake. This table will be a replacement of the workspace table wk_pto.This table is a hybrid SCD (Type 0 + Type 2).

The grain of this table is one row per team member ID per pto_uuid and absence_date combination.

WITH final AS (
  SELECT
    *,
    DATEDIFF(DAY, absence_start, absence_end) + 1 AS pto_days_requested,
    ROW_NUMBER() OVER (
      PARTITION BY
        employee_id,
        absence_date
      ORDER BY
        absence_end DESC,
        pto_uuid DESC
    )                                             AS pto_rank
  FROM prod.common_mart_people.mart_team_member_absence
  WHERE absence_date <= CURRENT_DATE
    AND pto_days_requested <= 25
    AND COALESCE(pto_group_type, '') != 'EXL'
    AND NOT COALESCE(absence_status, '') IN ('CEO Shadow Program', 'Conference', 'Customer Visit')
  QUALIFY pto_rank = 1
)

SELECT
  QUARTER(absence_date) AS quarter,
  absence_status,
  COUNT(absence_date)   AS absence_count
FROM final
WHERE full_name = 'John Doe'
  AND YEAR(absence_date) = 2024
GROUP BY QUARTER(absence_date), absence_status
ORDER BY quarter ASC, absence_count DESC;

mart_team_member_directory

This table is a derived fact from fct_team_member_position and dim_team. Sensitive columns are masked and only visible by team members with the analyst_people role assigned in Snowflake. This table will become a replacement of the legacy tables employee_directory_* once all the BambooHR data has been included in the upstream tables.

The grain of this table is one row per employee per valid_from/valid_to combination.

SELECT
    directory.division,
    DATE_TRUNC('month', dates.date_actual) AS month,
    ROUND(AVG(location_factors.location_factor),2) AS average_location_factor
  FROM PROD.COMMON_MART_PEOPLE.MART_TEAM_MEMBER_DIRECTORY AS directory
  INNER JOIN PROD.LEGACY.DATE_DETAILS AS dates
    ON dates.date_actual >= directory.valid_from
        AND dates.date_actual < directory.valid_to
  LEFT JOIN PREP.SENSITIVE.EMPLOYEE_LOCATION_FACTOR_SNAPSHOTS AS location_factors
    ON REPLACE(location_factors.bamboo_employee_number,',','') = directory.employee_id
        AND NOT (directory.valid_to <= location_factors.valid_from
          OR directory.valid_from >= location_factors.valid_to)
  WHERE location_factors.location_factor IS NOT NULL
      AND directory.is_current AND directory.is_current_team_member
  GROUP BY 1, 2

SELECT
  employee_id,
  hire_date,
  DATEDIFF(day, hire_date, CURRENT_DATE())           AS tenure_in_days,
  CASE WHEN tenure_in_days BETWEEN 0 AND 183 THEN '0 - 6 Months'
       WHEN tenure_in_days BETWEEN 184 AND 365 THEN '6 - 12 Months'
       WHEN tenure_in_days BETWEEN 366 AND 1095 THEN '1 - 3 Years'
       WHEN tenure_in_days BETWEEN 1096 AND 2190 THEN '4 - 6 Years'
       WHEN tenure_in_days > 2191 THEN '6+ Years'
       ELSE null
   END                                                  AS tenure_bucketed
FROM PROD.COMMON_MART_PEOPLE.MART_TEAM_MEMBER_DIRECTORY
WHERE is_current AND is_current_team_member

Legacy and Other Data Models

Legacy models are models we will be transitioning from at some point but are still being used for reporting.

Legacy Workday/BambooHR Data Models

Greenhouse Data Models

View page source - Edit this page - please contribute.