Active headcount
How many active employees (snapshot)?
COUNT(*) FROM employee WHERE status = 'active'
status=active; lock contractor policy.
Adapt: FTE-weighted variant.
HCM
HRIS: employee/employment, time off, org — API load, strict workforce PII; never cleartext salary in marts.
Templates only — grain, filters, custom fields and ownership are firm-specific.
Copy formulas into the KPI Definition Card, then adapt grain and filters.
How many active employees (snapshot)?
COUNT(*) FROM employee WHERE status = 'active'
status=active; lock contractor policy.
Adapt: FTE-weighted variant.
How many hires in the period?
COUNT(*) FROM employee WHERE hire_date IN period
hire_date required; treat rehires separately.
Adapt: Count primary employment only.
How many terminations in the period?
COUNT(*) FROM employee WHERE termination_date IN period
Clarify soft-deleted vs terminated.
Adapt: Voluntary vs involuntary if field exists.
How many approved absence days in the period?
SUM(days) FROM time_off WHERE status = 'approved' AND start_date IN period
Do not load medical notes.
Adapt: Vacation-only vs all types.
What is the average span of control?
AVG(direct_reports) FROM manager_edge WHERE manager is active
Active direct reports only.
Adapt: Max span as governance KPI.
Same formulas as a table — copy into KPI cards or SQL drafts.
| Label | Formula | Grain | Fields |
|---|---|---|---|
| Active headcount |
COUNT(*) FROM employee WHERE status = 'active'
|
Active employee | Employee.id, Employee.status, Department.id |
| Hires |
COUNT(*) FROM employee WHERE hire_date IN period
|
Hire event | Employee.id, Employee.hire_date |
| Terminations |
COUNT(*) FROM employee WHERE termination_date IN period
|
Termination event | Employee.termination_date, Employee.id |
| Time-off days |
SUM(days) FROM time_off WHERE status = 'approved' AND start_date IN period
|
Approved time-off day | TimeOff.days, TimeOff.status, TimeOff.type |
| Avg span of control |
AVG(direct_reports) FROM manager_edge WHERE manager is active
|
Manager with direct reports | Manager.manager_id, Manager.employee_id |
| Id | Label | Grain | Notes |
|---|---|---|---|
department |
Department | department.id | Org rollups; renames SCD2. |
employment_status |
Employment status | employee.status | Clearly separate active vs terminated. |
employment_type |
Employment type | employment.employment_type | Clarify contractor in headcount definition. |
location_country |
Country | location.country | Prefer ISO country. |
time_off_type |
Time-off type | time_off.type | Sick without diagnosis/note fields. |
Inventory direct and quasi identifiers for this source — tag, restrict RAW, hash or drop before marts.
| Entity | Fields | Classification | Stage treatment | Treatment |
|---|---|---|---|---|
| Employee master | id, work_email, personal_email, first_name, last_name, phone, address |
Workforce | RAW: workforce policy; Curated: id only; Mart: aggregates | Employee master — cleartext only with legal basis; id as key. |
| National identifiers | national_id, tax_id, ssn, social_security |
Sensitive | RAW: restrict/hash; Curated: hash; Mart: never cleartext | Hash national IDs or do not load. |
| Compensation | amount, bonus, equity, pay_rate |
Sensitive | RAW: restricted; Curated: bands/flags; Mart: never cleartext | Never put salary in public marts. |
| Time off / sick | notes, medical_certificate, diagnosis |
Sensitive | RAW: never notes; Curated: type/days only; Mart: aggregates | No health free text. |
| HR documents | contract_pdf, id_scan, attachment_body |
Sensitive | RAW: never bodies; Curated: meta; Mart: meta | Document meta only. |
Where personal data can hide beyond obvious contact fields — warehouse copies included.
| Focus | Notes |
|---|---|
| Match keys | employee id, work_email hashed, external ids. |
| Primary objects | Employee, employment, time off, org, compensation meta. |
| API / export copies | Exports and webhook payloads duplicate PII — allowlist. |
| Sandbox / test tenants | Do not mix sandbox with prod HR marts. |
Source-specific guidance: what goes wrong, how to prevent it, what staff must know — plus where metadata lives and how to access it.
Prevent issues in the source first. Warehouse checks are a safety net — not a substitute for staff habits.
work_email IS NULL AND status = 'active'manager_id NOT IN dim_employeestatus='active' AND department_id IS NULLstatus='terminated' AND termination_date IS NULLcolumn_exists(mart, 'amount') OR column_exists(mart, 'salary')time_off.employee_id IS NULL OR NOT IN dim_employeeEmployee
Employee duplicates / rehire
id, work_email hashed, national_id hashed, external_idDepartment
Department rename / drift
department id, name, parent_idSource-native catalogs for schema, picklists/properties and access paths — not a full dump.
Object overview with short descriptions and load guidance — not a full schema dump.
| Table | Description | Grain | Load |
|---|---|---|---|
employee
Employee
|
BambooHR employee — master; email/name = workforce PII. | One employee (id) | Load |
employment
Employment
|
Employment / job info — status, start/end, department. | One employment row | Load |
department
Department
|
Org unit / team structure. | One department | Load |
location
Location
|
Work location / site. | One location | Optional |
time_off
Time off
|
Time off — type, status, days; no medical notes. | One time-off request | Load |
compensation_meta
Compensation meta
|
Compensation metadata — band/currency/effective; never cleartext amounts in marts. | One compensation row (meta) | Optional |
manager
Manager edge
|
Manager hierarchy as employee ids. | One manager assignment | Load |
document_meta
HR document meta
|
Document metadata — no contract bodies. | One HR document (meta) | Optional |
Most important fields to load early — not a full schema dump.
| Entity | Field | Role | Why |
|---|---|---|---|
| Employee |
Employee.id
|
Key | Employee join key |
| Employee |
Employee.work_email
|
PII | Work email / workforce PII |
| Employee |
Employee.personal_email
|
PII | Personal email / PII |
| Employee |
Employee.first_name
|
PII | First name / PII |
| Employee |
Employee.last_name
|
PII | Last name / PII |
| Employee |
Employee.birth_date
|
PII | Birth date / PII |
| Employee |
Employee.national_id
|
PII | National ID — hash/restrict |
| Employee |
Employee.status
|
Dimension | Active / inactive / terminated |
| Employee |
Employee.hire_date
|
Measure | Hire date |
| Employee |
Employee.termination_date
|
Measure | Termination date |
| Employment |
Employment.employee_id
|
Key | Employee join |
| Employment |
Employment.job_title
|
Dimension | Job title |
| Employment |
Employment.employment_type
|
Dimension | Full-time / part-time / contractor |
| Employment |
Employment.fte
|
Measure | FTE factor |
| Department |
Department.id
|
Key | Department join |
| Department |
Department.name
|
Dimension | Department name |
| Location |
Location.id
|
Key | Location join |
| Location |
Location.country
|
Dimension | Country |
| TimeOff |
TimeOff.id
|
Key | Time-off request key |
| TimeOff |
TimeOff.employee_id
|
Dimension | Employee join |
| TimeOff |
TimeOff.type
|
Dimension | Vacation / sick / other |
| TimeOff |
TimeOff.status
|
Dimension | Approved / pending / denied |
| TimeOff |
TimeOff.days
|
Measure | Absence days |
| TimeOff |
TimeOff.start_date
|
Measure | Start |
| CompensationMeta |
CompensationMeta.employee_id
|
Dimension | Employee join |
| CompensationMeta |
CompensationMeta.currency
|
Dimension | Currency |
| CompensationMeta |
CompensationMeta.effective_on
|
Measure | Effective date |
| CompensationMeta |
CompensationMeta.amount
|
PII | Salary — never cleartext in default marts |
| Manager |
Manager.employee_id
|
Key | Employee |
| Manager |
Manager.manager_id
|
Dimension | Manager employee id |
| DocumentMeta |
DocumentMeta.id
|
Key | Document meta key |
| DocumentMeta |
DocumentMeta.employee_id
|
Dimension | Employee join |
| DocumentMeta |
DocumentMeta.document_type
|
Dimension | Contract / ID / other (meta) |
System and noise objects/fields you typically should not sync into the warehouse by default.
| Table | Category | Why |
|---|---|---|
Compensation amount cleartext in marts |
System | Salary/bonus cleartext — meta/bands or restricted RAW only. |
National ID / SSN / tax id cleartext |
System | Hash national IDs or do not load. |
HR document / contract bodies |
System | Contract PDF/bodies — meta only. |
Medical / sick notes free text |
System | Health data — never in default analytics. |
Warehouse-neutral examples for landing (RAW) and curated models — copy and adapt to your dialect. Not vendor SOQL/API DDL.
Workforce PII in RAW; no salary cleartext columns in default extract.
-- Warehouse-neutral RAW from BambooHR HRIS extract
CREATE TABLE raw_bamboohr_employee (
employee_id VARCHAR,
work_email VARCHAR, -- workforce PII
first_name VARCHAR, -- PII
last_name VARCHAR, -- PII
status VARCHAR,
hire_date DATE,
termination_date DATE,
department_id VARCHAR,
location_id VARCHAR,
_loaded_at TIMESTAMP
-- omit national_id cleartext; omit salary
);
CREATE TABLE raw_bamboohr_employment (
employment_id VARCHAR,
employee_id VARCHAR,
job_title VARCHAR,
employment_type VARCHAR,
fte DECIMAL(6,3),
effective_on DATE,
_loaded_at TIMESTAMP
);
Absence meta without medical notes; manager as ids.
CREATE TABLE raw_bamboohr_time_off (
time_off_id VARCHAR,
employee_id VARCHAR,
type VARCHAR,
status VARCHAR,
days DECIMAL(8,2),
start_date DATE,
end_date DATE,
_loaded_at TIMESTAMP
-- no medical notes
);
CREATE TABLE raw_bamboohr_manager (
employee_id VARCHAR,
manager_id VARCHAR,
_loaded_at TIMESTAMP
);
Without email/name cleartext.
CREATE TABLE curated_dim_bamboohr_employee AS
SELECT
employee_id,
status,
hire_date,
termination_date,
department_id,
location_id
-- omit work_email, first_name, last_name
FROM raw_bamboohr_employee;
Approved absences grain.
CREATE TABLE curated_fct_bamboohr_time_off AS
SELECT
t.time_off_id,
t.employee_id,
t.type,
t.status,
t.days,
t.start_date,
t.end_date
FROM raw_bamboohr_time_off t
WHERE t.employee_id IS NOT NULL;
Headcount, hires, terminations, time-off days.
-- Active headcount
SELECT COUNT(*) AS active_headcount
FROM curated_dim_bamboohr_employee
WHERE status = 'active';
-- Hires in period
SELECT COUNT(*) AS hires
FROM curated_dim_bamboohr_employee
WHERE hire_date BETWEEN :period_start AND :period_end;
-- Approved time-off days
SELECT COALESCE(SUM(days),0) AS time_off_days
FROM curated_fct_bamboohr_time_off
WHERE status = 'approved'
AND start_date BETWEEN :period_start AND :period_end;
Open related Binom-Tools workflows to adapt formulas, PII and measures for this source.
Related governance stories for modelling, PII and measure design around this source.