HR Workforce Analytics data model
Kimball warehouse for HR/HCM: a monthly headcount periodic snapshot, transaction-grain hire/termination events, and a periodic compensation snapshot over conformed employee/department/job/location/manager dimensions. National ID is fully masked and work email hashed.
Human-resources workforce analytics covering monthly headcount, hires and terminations, and employee compensation over a conformed SCD2 employee dimension with department, job, location, and manager dimensions. Sensitive employee PII (date of birth, national ID, ethnicity, salary) is classified and masked under GDPR. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 9
- tables
- 97
- columns
- 15
- relationships
- 13
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Monthly headcount as a periodic snapshot
Headcount and FTE must be reported at consistent month-end intervals regardless of movement volume; a periodic snapshot gives a dense, semi-additive grain ideal for trending and ratio analysis.
Separate transaction fact for hires and terminations
Attrition and hiring velocity need event-level grain with reason and voluntary/regrettable flags that a snapshot would obscure.
SCD2 on employee, department, job, and manager
Reorgs, promotions, and transfers must be analysed as-of any point in time, so descriptive history is preserved with type-2 versioning.
Masking on national ID, salary, and demographics
GDPR requires minimising exposure of special-category personal data; national ID is fully masked, email hashed, and DOB/ethnicity restricted.
Tables in the HR Workforce Analytics model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (3)
fct_headcount_snapshot
Grain: one row per active employee per month-end (periodic snapshot)
Monthly headcount snapshot — counts are semi-additive across time.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| employee_key | bigint | |
| department_key | bigint | |
| job_key | bigint | |
| location_key | bigint | |
| manager_key | bigint | |
| headcount | smallint | |
| fte | decimal | |
| tenure_months | smallint | |
| is_new_hire_this_month | boolean | |
| is_termination_this_month | boolean |
fct_hire_termination
Grain: one row per hire or termination event
Transaction-grain workforce movement events.
| Column | Type | Keys |
|---|---|---|
| hire_termination_key | bigint | PK |
| date_key | bigint | |
| employee_key | bigint | |
| department_key | bigint | |
| job_key | bigint | |
| location_key | bigint | |
| event_id | varchar | |
| event_type | varchar | |
| termination_reason | varchar | |
| is_voluntary | boolean | |
| is_regrettable | boolean | |
| tenure_at_event_months | smallint | |
| notice_days | smallint |
fct_compensation
Grain: one row per employee per compensation period (periodic snapshot)
Periodic compensation snapshot — salary is non-additive; aggregate by averaging.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| employee_key | bigint | |
| department_key | bigint | |
| job_key | bigint | |
| base_salary | decimal | |
| bonus_target_amount | decimal | |
| bonus_actual_amount | decimal | |
| equity_grant_value | decimal | |
| total_compensation | decimal | |
| currency | varchar | |
| pay_grade | varchar | |
| compa_ratio | decimal | |
| is_merit_increase_period | boolean |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| month_name | varchar | |
| quarter | smallint | |
| year | smallint | |
| fiscal_year | smallint | |
| fiscal_period | smallint | |
| is_month_end | boolean | |
| is_business_day | boolean |
dim_employee
Grain: one row per employee per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| employee_key | bigint | PK |
| employee_id | varchar | BK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| preferred_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | PII |
| national_id | varchar | PII |
| work_email | varchar | PII |
| personal_phone | varchar | PII |
| home_postal_code | varchar | PII |
| hire_date | date | |
| original_hire_date | date | |
| termination_date | date | |
| employment_type | varchar | |
| employment_status | varchar | |
| is_full_time | boolean | |
| is_regrettable_attrition | boolean | |
| ethnicity | varchar | PII |
| veteran_status | varchar | PII |
dim_department
Grain: one row per department per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| department_key | bigint | PK |
| department_id | varchar | BK |
| department_name | varchar | |
| division | varchar | |
| cost_center | varchar | |
| function | varchar | |
| parent_department_id | varchar |
dim_job
Grain: one row per job/role per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| job_key | bigint | PK |
| job_id | varchar | BK |
| job_title | varchar | |
| job_family | varchar | |
| job_level | varchar | |
| is_manager_role | boolean | |
| is_exempt | boolean | |
| eeo_category | varchar |
dim_location
Grain: one row per work location
| Column | Type | Keys |
|---|---|---|
| location_key | bigint | PK |
| location_id | varchar | BK |
| location_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| region | varchar | |
| work_model | varchar | |
| timezone | varchar |
dim_manager
Grain: one row per manager per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| manager_key | bigint | PK |
| manager_id | varchar | BK |
| manager_name | varchar | PII |
| manager_email | varchar | PII |
| management_level | varchar | |
| org_layer | smallint |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_headcount_snapshot.date_key → dim_date (many-to-one)
- fct_headcount_snapshot.employee_key → dim_employee (many-to-one)
- fct_headcount_snapshot.department_key → dim_department (many-to-one)
- fct_headcount_snapshot.job_key → dim_job (many-to-one)
- fct_headcount_snapshot.location_key → dim_location (many-to-one)
- fct_headcount_snapshot.manager_key → dim_manager (many-to-one)
- fct_hire_termination.date_key → dim_date (many-to-one)
- fct_hire_termination.employee_key → dim_employee (many-to-one)
- fct_hire_termination.department_key → dim_department (many-to-one)
- fct_hire_termination.job_key → dim_job (many-to-one)
- fct_hire_termination.location_key → dim_location (many-to-one)
- fct_compensation.date_key → dim_date (many-to-one)
- fct_compensation.employee_key → dim_employee (many-to-one)
- fct_compensation.department_key → dim_department (many-to-one)
- fct_compensation.job_key → dim_job (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.