HR Workforce EDW data model
Data Vault 2.0 raw vault for HR/HCM: hubs (employee/position/department/location/job), links (employee-position assignment, position-department, employee-location, employee-job), and satellites (employee details with full-masked SSN and hashed email, position, department, location details, an assignment effectivity satellite, and a compensation satellite). Hash keys and load metadata are generated by the DDL layer.
HR / workforce enterprise data warehouse modeled as a Data Vault 2.0 raw vault: employee, position, department, location, and job hubs; assignment, organizational, and location links; and descriptive plus effectivity satellites — built for integrating Workday/ADP/HRIS source systems with full historization and the personal-data lineage (GDPR access and erasure) that makes Data Vault attractive for sensitive workforce data. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 101
- columns
- 14
- relationships
- 11
- 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
Hubs hold only workforce business keys
Isolating employee_id, position_code, and department_code into hubs lets payroll, recruiting, and benefits source systems attach over time without reworking existing structures — the core agility of Data Vault.
Assignment modeled as a link with an effectivity satellite
An employee's position assignment changes over time (promotions, transfers, acting roles); a link plus an effectivity satellite records the valid period of each assignment for as-of org reporting.
Compensation split into its own satellite
Compensation is more sensitive and changes on a different cadence than core employee attributes; a dedicated satellite isolates restricted pay data and keeps history compact (satellite-per-rate-of-change).
GDPR-driven masking on the employee satellite
SSN is full-masked and email is hashed so the governance report flags the most sensitive identifiers; load_date + record_source give the queryable lineage needed for GDPR access and erasure requests.
Tables in the HR Workforce EDW 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.
Hubs (5)
hub_employee
Grain: one row per unique hub_employee business key
| Column | Type | Keys |
|---|---|---|
| employee_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_position
Grain: one row per unique hub_position business key
| Column | Type | Keys |
|---|---|---|
| position_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_department
Grain: one row per unique hub_department business key
| Column | Type | Keys |
|---|---|---|
| department_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_location
Grain: one row per unique hub_location business key
| Column | Type | Keys |
|---|---|---|
| location_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_job
Grain: one row per unique hub_job business key
| Column | Type | Keys |
|---|---|---|
| job_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_employee_position
Grain: one row per lnk_employee_position relationship
Which position an employee is assigned to (assignment).
| Column | Type | Keys |
|---|---|---|
| employee_hk | binary | |
| position_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_position_department
Grain: one row per lnk_position_department relationship
Which department a position belongs to.
| Column | Type | Keys |
|---|---|---|
| position_hk | binary | |
| department_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_employee_location
Grain: one row per lnk_employee_location relationship
Which work location an employee reports to.
| Column | Type | Keys |
|---|---|---|
| employee_hk | binary | |
| location_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_employee_job
Grain: one row per lnk_employee_job relationship
Which job/role classification an employee holds.
| Column | Type | Keys |
|---|---|---|
| employee_hk | binary | |
| job_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_employee_details
Grain: one row per employee_hk per change
Employee descriptive history (sensitive PII).
| Column | Type | Keys |
|---|---|---|
| employee_hk | binary | PK |
| load_date | timestamp | PK |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| ssn | varchar | PII |
| varchar | PII | |
| phone | varchar | PII |
| gender | varchar | PII |
| nationality | varchar | PII |
| home_address | varchar | PII |
| employment_status | varchar | |
| hire_date | date | |
| termination_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_position_details
Grain: one row per position_hk per change
Position descriptive history.
| Column | Type | Keys |
|---|---|---|
| position_hk | binary | PK |
| load_date | timestamp | PK |
| position_title | varchar | |
| position_type | varchar | |
| fte_percentage | decimal | |
| is_manager_position | boolean | |
| pay_grade | varchar | |
| headcount_status | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_department_details
Grain: one row per department_hk per change
Department descriptive history.
| Column | Type | Keys |
|---|---|---|
| department_hk | binary | PK |
| load_date | timestamp | PK |
| department_name | varchar | |
| cost_center | varchar | |
| division | varchar | |
| function | varchar | |
| budget_owner | varchar | PII |
| load_end_date | timestamp | |
| record_source | varchar |
sat_location_details
Grain: one row per location_hk per change
Work location descriptive history.
| Column | Type | Keys |
|---|---|---|
| location_hk | binary | PK |
| load_date | timestamp | PK |
| location_name | varchar | |
| address_line | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| postal_code | varchar | |
| timezone | varchar | |
| is_remote_eligible | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_assignment_effectivity
Grain: one row per assignment per effectivity change
Effectivity satellite on the employee-position assignment link.
| Column | Type | Keys |
|---|---|---|
| employee_position_hk | binary | PK |
| load_date | timestamp | PK |
| assignment_type | varchar | |
| reports_to_employee_id | varchar | PII |
| effective_from | date | |
| effective_to | date | |
| is_active | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_compensation
Grain: one row per employee per compensation change
Compensation satellite split out for confidentiality and rate of change.
| Column | Type | Keys |
|---|---|---|
| employee_hk | binary | PK |
| load_date | timestamp | PK |
| base_salary | decimal | |
| currency | varchar | |
| pay_frequency | varchar | |
| target_bonus | decimal | |
| bonus_target_pct | decimal | |
| equity_grant_value | decimal | |
| compensation_review_date | date | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_country
Grain: one row per ISO country
| Column | Type | Keys |
|---|---|---|
| country_code | varchar | BK |
| country_name | varchar | |
| region | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_employee_position.employee_hk → hub_employee (many-to-one)
- lnk_employee_position.position_hk → hub_position (many-to-one)
- lnk_position_department.position_hk → hub_position (many-to-one)
- lnk_position_department.department_hk → hub_department (many-to-one)
- lnk_employee_location.employee_hk → hub_employee (many-to-one)
- lnk_employee_location.location_hk → hub_location (many-to-one)
- lnk_employee_job.employee_hk → hub_employee (many-to-one)
- lnk_employee_job.job_hk → hub_job (many-to-one)
- sat_employee_details.employee_hk → hub_employee (many-to-one)
- sat_position_details.position_hk → hub_position (many-to-one)
- sat_department_details.department_hk → hub_department (many-to-one)
- sat_location_details.location_hk → hub_location (many-to-one)
- sat_assignment_effectivity.employee_position_hk → lnk_employee_position (many-to-one)
- sat_compensation.employee_hk → hub_employee (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.