This sample shows the document HR hands over to DSD for each critical dashboard: what to build in Snowflake, how keys, history and mappings work, and how the POC proves it. It uses an illustrative Workforce Headcount and Attrition dashboard until HR shares the actual Level-1 report.

Document control

This is version 0.1, a sample for HR review; DSD must not build from it until it is signed.

Field	Value
Document	HR Analytics Technical Architecture and POC Document
Version	0.1 — Sample for review
Prepared by	Dinesh R M, Technical Architect, Team Academy
Prepared for	Khalid Mahmood (HR) and the DSD Snowflake team
Dashboard covered	Workforce Headcount, Movement and Attrition (illustrative)
Data in examples	Illustrative codes and names, not client data
Status	Draft, not approved for build

How DSD uses this document

It is the build specification for this dashboard: tables, grain, keys, history, mappings and quality checks.
DSD builds only what is specified here. Anything unclear is raised as an open question, not assumed.HR produces one such document per critical dashboard.
Shared dimensions (Employee, Organisation, Date, Grade) are defined once and reused, so the dashboards stay consistent.
The POC test cases at the end are the acceptance tests for the Snowflake build.

How the document is organised

Business need: the sample dashboard and its KPI definitions.
Design: solution architecture, data model, table specifications, keys, mapping tables and rules.
Build: requirements for DSD and the Power BI semantic model.
Proof: POC test cases, complexity register, open questions and sign-off.

Sample dashboard and KPI definitions

The sample dashboard answers four questions: how many people, where, how this changed over 10 years, and who is leaving.

Visuals

#	Visual	Measures	Slicers
1	KPI cards	Headcount, Joiners, Leavers, Attrition rate %	Month, Company, Division
2	10-year headcount trend (line)	Month-end Headcount	Company, Division, Grade
3	Joiners versus leavers by month (columns)	Joiners, Leavers	Company, Division
4	Attrition by division and grade (matrix)	Attrition rate %, Voluntary attrition %	Year
5	Promotions and career-plan moves (bars)	Promotions, Career-plan moves, Transfers	Year, Division
6	Employee drill-through (table)	Event history per employee	Employee

KPI definitions

KPI	Business definition	Calculation rule	Time basis
Headcount	Employees on strength at the end of the period	Distinct Employee_ID with Status = Active on the month-end snapshot	Month-end
Joiners	Employees hired in the period, including rehires	Count of events with Event_Type = Hire, effective date in period	Event date
Leavers	Employees whose employment ended in the period	Count of events with Event_Type = Termination, last working day in period	Event date
Attrition rate % (12 months)	Share of the workforce that left over the last 12 months	Leavers in last 12 months ÷ average of opening and closing headcount × 100	Rolling 12 months
Voluntary attrition %	Attrition from resignations only	As above, Termination category = Voluntary	Rolling 12 months
Promotions	Moves to a higher grade	Count of events with unified Event_Type = Promotion	Event date
Career-plan moves	Moves under an approved career or development plan	Count of events with unified Event_Type = Career Plan	Event date
Transfers	Organisation changes without a grade change	Count of events with Event_Type = Transfer	Event date

HR must name an owner for each KPI and confirm the scope of Headcount (see open questions).

Solution architecture

All HR data lands once in Snowflake, is translated and historised in CORE, and reaches Power BI only through MART views.

The mapping tables are a source in their own right: HR owns them, and CORE applies them to every record by effective date.

Source systems (HR to confirm)

Source	Data supplied	Years held	Load method	Refresh
SAP SuccessFactors Employee Central	Employees, effective-dated job and employment records, events and reasons	From go-live (HR to confirm)	Integration by DSD	Daily
Legacy HR systems (3 to 4)	Historical employees, job history and events	Earlier years, up to 10 in total (HR to confirm)	One-time historical extracts	Once, plus corrections
Excel / SharePoint	Items not held in any system	As needed	Controlled templates	Monthly or on change
HR mapping tables	Translations of codes across systems and years	All years	Controlled templates	On every approved change

Layer responsibilities

Layer	Purpose	Rule
HR_RAW	Exact copy of each source extract	Never edited; kept for audit and reloads
HR_STAGING	Typed, cleaned, one record per employee per date	Fan-out removed here (see rules)
HR_CORE	Unified keys, mappings applied, full history (SCD Type 2)	The single version of HR truth
HR_MART	Star-schema views shaped for reporting	The only layer Power BI may read

Target data model

The dashboard runs on a star schema: two fact tables at declared grains, joined to six shared dimensions, never one flat table.

target star schema · 2 facts, 6 dimensions

Headcount comes from the snapshot fact and movements from the event fact; because both use the same Employee, Organisation, Grade and Date dimensions, a filter on a division gives matching numbers in every visual.

Design decisions

Two facts, not one. Headcount is a state at a point in time; joiners, leavers and promotions are events. Mixing them in one table causes double counting.
Monthly snapshot. Storing one row per employee per month-end makes 10-year trends fast and reconcilable, with no re-calculation from raw events.
SCD Type 2 for Employee and Organisation. Each fact row links to the version valid on its date, so restructures never rewrite history.
Conformed dimensions. Dim_Date, Dim_Employee and Dim_Organisation are defined once for all HR dashboards and can later join other departments.

Table specifications

The model has 2 fact tables, 6 dimensions and 5 mapping tables; every table declares its grain, and no join may change that grain.

Table catalogue

Table	Type	Grain (one row per)	Business key	History
Fact_Workforce_Snapshot	Periodic snapshot fact	Employee per month-end, while employed	Employee_ID + Snapshot_Date	Kept for every month-end, 10 years
Fact_Workforce_Event	Transaction fact	HR event per employee (hire, termination, promotion, transfer, career-plan move)	Source_System + Source_Event_ID	All events, 10 years
Dim_Employee	Dimension, SCD Type 2	Employee version	Employee_ID	New version on each attribute change
Dim_Organisation	Dimension, SCD Type 2	Organisation unit version	Org_Code (unified)	New version on rename or restructure
Dim_Grade	Dimension	Grade	Grade_Code (unified)	Current structure, legacy grades mapped
Dim_Event_Type	Dimension	Unified event type and reason	Event_Type_Code	Static, maintained by HR
Dim_Date	Dimension	Calendar day	Date	Fixed
Dim_Source_System	Dimension	Source system	Source_System_Code	Static

Fact_Workforce_Snapshot

Column	Type	Key	Description
Snapshot_Date_Key	INTEGER	FK → Dim_Date	Calendar month-end date (YYYYMMDD)
Employee_SK	INTEGER	FK → Dim_Employee	Employee version valid on the snapshot date
Employee_ID	VARCHAR	Durable key	Unified employee ID, used for distinct counts
Org_SK	INTEGER	FK → Dim_Organisation	Organisation version valid on the snapshot date
Grade_SK	INTEGER	FK → Dim_Grade	Grade on the snapshot date
Source_System_Key	INTEGER	FK → Dim_Source_System	System the record came from
Employment_Status	VARCHAR		Active, Suspended, On leave
Is_Primary_Assignment	BOOLEAN		Always TRUE here: only the primary assignment is loaded
FTE	NUMBER(4,2)		Full-time equivalent
Load_Batch_ID, Load_Timestamp	VARCHAR, TIMESTAMP		Audit columns

Unique on Employee_ID + Snapshot_Date_Key, so each employee appears once per month-end.

Fact_Workforce_Event

Column	Type	Key	Description
Event_Date_Key	INTEGER	FK → Dim_Date	Effective date of the event
Employee_SK	INTEGER	FK → Dim_Employee	Employee version valid on the event date
Employee_ID	VARCHAR	Durable key	Unified employee ID
Event_Type_SK	INTEGER	FK → Dim_Event_Type	Unified event type, via Map_Event_Reason
From_Org_SK, To_Org_SK	INTEGER	FK → Dim_Organisation	Organisation before and after the event
From_Grade_SK, To_Grade_SK	INTEGER	FK → Dim_Grade	Grade before and after the event
Source_System_Key	INTEGER	FK → Dim_Source_System	System the event came from
Source_Event_ID	VARCHAR	Degenerate	Original event ID, for audit
Event_Sequence	INTEGER		Order of events on the same date
Load_Batch_ID, Load_Timestamp	VARCHAR, TIMESTAMP		Audit columns

Dim_Organisation

Column	Type	Key	Description
Org_SK	INTEGER	PK, surrogate	One per version
Org_Code	VARCHAR	Business key	Unified code from Map_Organisation
Company, Branch, Division, Department	VARCHAR		Hierarchy levels, current naming of that version
Valid_From, Valid_To	DATE		Effective period of this version (open end = 9999-12-31)
Is_Current	BOOLEAN		TRUE for the latest version

Dim_Employee follows the same pattern: Employee_SK, Employee_ID, reporting attributes (gender, nationality group, hire date, termination date), Valid_From, Valid_To, Is_Current. Salary and personal contact details are out of scope for this dashboard.

Keys and relationships

Every relationship is one-to-many from a dimension to a fact, joined on an integer surrogate key and filtering in one direction only.

Fact column	Dimension	Cardinality	Filter direction	Note
Snapshot.Snapshot_Date_Key	Dim_Date.Date_Key	Many to one	Date → Fact	Month-end dates only
Snapshot.Employee_SK	Dim_Employee.Employee_SK	Many to one	Employee → Fact	Version valid at month-end
Snapshot.Org_SK	Dim_Organisation.Org_SK	Many to one	Organisation → Fact	Version valid at month-end
Snapshot.Grade_SK	Dim_Grade.Grade_SK	Many to one	Grade → Fact
Event.Event_Date_Key	Dim_Date.Date_Key	Many to one	Date → Fact
Event.Employee_SK	Dim_Employee.Employee_SK	Many to one	Employee → Fact	Version valid on event date
Event.Event_Type_SK	Dim_Event_Type.Event_Type_SK	Many to one	Event type → Fact
Event.To_Org_SK	Dim_Organisation.Org_SK	Many to one	Organisation → Fact	Active relationship
Event.From_Org_SK	Dim_Organisation.Org_SK	Many to one	Organisation → Fact	Inactive; used only in transfer-out measures
Both facts.Source_System_Key	Dim_Source_System	Many to one	Source → Fact	Audit and lineage

Key rules

• Surrogate keys are integers generated in Snowflake CORE; source IDs never join facts to dimensions.

• A fact row picks the dimension version whose Valid_From to Valid_To covers the snapshot or event date.

• Rows that cannot be matched to a dimension version go to the exception table; facts never default them to an Unknown member.

• No many-to-many and no bidirectional relationships in this model.

Translation (mapping) tables

Five HR-owned mapping tables translate every source code into one unified code for the date it applied; without them the 10-year trends cannot be built.

Register

Mapping table	Translates	Used by	Owner (HR to confirm)
Map_Employee_ID	Legacy employee numbers → SuccessFactors person ID	Dim_Employee, both facts	HR Operations
Map_Organisation	Company, branch, division and department codes per system and year → unified Org_Code	Dim_Organisation	HR Organisation Design
Map_Event_Reason	Event and reason codes per system → unified Event_Type (Promotion, Career Plan, Transfer, Hire, Termination)	Dim_Event_Type, Fact_Workforce_Event	HR Operations
Map_Grade	Legacy grade codes → current grade structure	Dim_Grade	Compensation
Map_Termination_Reason	Termination reasons → Voluntary or Involuntary	Voluntary attrition %	HR Operations

Standard template (every mapping table)

Column	Purpose
Source_System	System the code comes from
Source_Code, Source_Description	Value as it appears in the source
Target_Code, Target_Description	Unified value used in reporting
Valid_From, Valid_To	Period in which this translation applies
Version	Increments on every approved change
Owner, Approved_By, Approved_Date	Governance
Comment	Reason for the mapping, such as a restructure

Sample rows: Map_Organisation (illustrative)

Source_System	Source_Code	Source_Description	Target_Code	Target_Description	Valid_From	Valid_To	Version
Legacy HRMS	BR-07	Branch 7	ORG-120	North Branch	2016-01-01	2024-12-31	1
SuccessFactors	5000123	North Branch	ORG-120	North Branch	2025-01-01	9999-12-31	2
Legacy HRMS	DIV-SS	Support Services	ORG-300	Support Services	2016-01-01	2024-12-31	1
SuccessFactors	5000310	Shared Services	ORG-310	Shared Services	2025-01-01	9999-12-31	2

The branch kept its identity across systems, so its trend stays continuous under ORG-120. The division was restructured in 2025, so it gets a new code; HR decides whether reports show it as continuous through a parent level.

Sample rows: Map_Event_Reason (illustrative)

Source_System	Source_Code	Source_Description	Target_Code	Valid_From	Valid_To
Legacy HRMS	GRADE_UPG	Grade Upgrade	PROMOTION	2016-01-01	2024-12-31
SuccessFactors	PRM	Promotion	PROMOTION	2025-01-01	9999-12-31
Legacy HRMS	DEV_PLAN	Development Plan	CAREER_PLAN	2016-01-01	2024-12-31
SuccessFactors	CPM	Career Plan Move	CAREER_PLAN	2025-01-01	9999-12-31

Lookup logic (Snowflake CORE)

-- Translate each source record using the mapping valid on its effective date
SELECT s.source_system,
        s.employee_source_id,
        s.effective_date,
        m.target_code            AS org_code
FROM    hr_staging.job_history   s
JOIN    hr_core.map_organisation m
ON    m.source_system = s.source_system
AND    m.source_code   = s.org_code
AND    s.effective_date BETWEEN m.valid_from AND m.valid_to;

-- Anything that finds no mapping goes to the exception table, never dropped
INSERT INTO hr_core.exception_unmapped
SELECT s.*, 'Map_Organisation' AS missing_mapping, CURRENT_TIMESTAMP()
FROM    hr_staging.job_history s
WHERE   NOT EXISTS (
          SELECT 1 FROM hr_core.map_organisation m
          WHERE m.source_system = s.source_system
            AND m.source_code   = s.org_code
            AND s.effective_date BETWEEN m.valid_from AND m.valid_to);

Mapping rules

Each source code matches exactly one row on any given date; overlapping validity periods are rejected at load.
HR maintains the tables in a controlled SharePoint template; DSD loads them into CORE on each run.
Every change creates a new version; history is never overwritten.
Unmapped codes appear on a data-quality page in Power BI until HR resolves them.

Business and data quality rules

These rules are binding on the build; each one closes a failure HR has already hit, starting with duplicated rows.

Fan-out prevention (the Employee ID with 3 records)

Cause: SuccessFactors keeps several effective-dated job records per employee, and legacy systems hold more. Joining a raw job table on Employee_ID turns one employee into 3 rows, and every total triples.
Rule: before any join, reduce job records to one row per employee per date: the record with the latest effective date on or before that date, then the highest sequence number.
Rule: facts join dimensions only on surrogate keys, never on Employee_ID against a multi-row table.
Test: a duplicate check at the declared grain runs on every load (test T02).

Counting rules

Headcount is a distinct count of Employee_ID on the month-end snapshot, never a row count of job records.
Only the primary assignment counts; concurrent assignments are kept in CORE for other reports.
A rehire is a new Hire event and a new employment period for the same Employee_ID.
Organisation and grade are taken as at the snapshot or event date, never today's values.

History rules

Legacy years load into RAW unchanged and are translated through the mapping tables in CORE.
Original source codes are kept beside the unified codes for audit.
Backdated or late events reprocess the affected month-end snapshots; by default the last 3 months on every load.

Quality rules

Unmapped codes go to the exception table and a data-quality page; no hidden "Unknown" bucket.
Manual uploads come only through controlled SharePoint templates with a named owner and validation.
Month-end headcount must reconcile to HR's official figure; HR sets the tolerance.
Sensitive fields such as salary are excluded from this dashboard; access is restricted by organisation.

Build requirements for DSD

DSD builds 14 requirements in Snowflake; each has an acceptance criterion that the POC tests check.

ID	Requirement	Acceptance criterion
DSD-01	Create four layers as separate schemas: HR_RAW, HR_STAGING, HR_CORE, HR_MART	Schemas exist in each environment with role-based access
DSD-02	Load SuccessFactors job and employment records with full effective-dated history	No record overwritten; every effective date and sequence kept
DSD-03	Load 10 years of legacy HR system history into HR_RAW as delivered	Row counts match the source extracts per year
DSD-04	Load the five HR mapping tables from the SharePoint templates into HR_CORE	Every load is versioned; overlapping periods rejected
DSD-05	Translate all source codes through the mapping tables by effective date	Zero unmapped rows outside the exception table
DSD-06	Build Dim_Employee and Dim_Organisation as SCD Type 2	Valid_From, Valid_To and Is_Current correct for sample employees
DSD-07	Build Dim_Grade, Dim_Event_Type, Dim_Date and Dim_Source_System	Every fact key finds a dimension row
DSD-08	Build Fact_Workforce_Snapshot at employee per month-end grain	Passes duplicate check T02
DSD-09	Build Fact_Workforce_Event at one row per event	Passes tests T06 and T09
DSD-10	Generate integer surrogate keys; facts reference surrogate keys only	No source IDs used as join keys in HR_MART
DSD-11	Expose HR_MART as views with business-friendly names for Power BI	Power BI connects to views only, never to CORE tables
DSD-12	Daily incremental load, reprocessing the last 3 months of snapshots	Backdated test event updates the right month (T09)
DSD-13	Add audit columns and run quality checks on every load	Load report lists duplicates, unmapped codes and orphan keys
DSD-14	Grant the Power BI service account read-only access to HR_MART	No write access; no access to RAW or STAGING

Power BI semantic model blueprint

Power BI imports the HR_MART views into a star-schema model; all business logic beyond the mappings lives in a small set of governed measures.
Model settings

Storage: Import mode from HR_MART views, with incremental refresh on both fact tables by date.
Date table: Dim_Date marked as the date table; relationships on Date_Key.
Visibility: surrogate keys and audit columns hidden; only business names shown.
Security: one row-level security role filters Dim_Organisation through Sec_User_Org (user, Org_Code), a table maintained by HR.
Fabric option: if HR adopts Fabric, the same HR_MART design feeds it without changing the model.

Core measures

Headcount =
CALCULATE (
    DISTINCTCOUNT ( Fact_Workforce_Snapshot[Employee_ID] ),
    LASTDATE ( 'Dim_Date'[Date] )
)
Joiners =
CALCULATE ( COUNTROWS ( Fact_Workforce_Event ), Dim_Event_Type[Event_Type] = "Hire" )
Leavers =
CALCULATE ( COUNTROWS ( Fact_Workforce_Event ), Dim_Event_Type[Event_Type] = "Termination" )
Attrition Rate % 12M =
VAR Leavers12M =
    CALCULATE ( [Leavers], DATESINPERIOD ( 'Dim_Date'[Date], MAX ( 'Dim_Date'[Date] ), -12, MONTH ) )
VAR HCClose = [Headcount]
VAR HCOpen = CALCULATE ( [Headcount], DATEADD ( 'Dim_Date'[Date], -12, MONTH ) )
RETURN
    DIVIDE ( Leavers12M, ( HCOpen + HCClose ) / 2 )
Voluntary Attrition % 12M =
CALCULATE ( [Attrition Rate % 12M], Dim_Event_Type[Termination_Category] = "Voluntary" )
RLS rule on Dim_Organisation =
Dim_Organisation[Org_Code]
    IN CALCULATETABLE ( VALUES ( Sec_User_Org[Org_Code] ), Sec_User_Org[UPN] = USERPRINCIPALNAME () )

Headcount reads only the last month-end in the selected period, so a year shows December's figure, not a sum of 12 months. The termination filter in Voluntary attrition does not touch Headcount, because Dim_Event_Type relates only to the event fact.

POC validation test cases

The POC runs these 10 tests on sample data before sign-off; DSD reruns the same tests as acceptance of the Snowflake build.

ID	Test	Expected result	Result
T01	Month-end headcount for a chosen month	Equals HR's official figure within the agreed tolerance	Not run
T02	Duplicate check at the snapshot grain	Zero rows sharing Employee_ID and Snapshot_Date_Key	Not run
T03	Employee with 3 job records on the same date	Counted once in Headcount	Not run
T04	Branch renamed between 2024 and 2025	One continuous 10-year trend under the unified code	Not run
T05	Every source organisation and event code on its effective date	Exactly one mapping; zero unmapped outside the exception table	Not run
T06	Legacy "Grade Upgrade" and SuccessFactors "Promotion"	Both counted under Promotions	Not run
T07	Monthly movement for the whole organisation (internal transfers net to zero)	Opening headcount + joiners − leavers = closing headcount	Not run
T08	Rehired employee	One Hire event per employment period; counted once per month-end	Not run
T09	Backdated termination entered after month-end	Affected month-end snapshot and attrition update on next load	Not run
T10	Division manager signs in	Sees only their division's data	Not run

Complexity and showstopper register

Six of the seven known complexities have a design answer in this document; missing history (C05) is a potential showstopper that only HR can resolve.

ID	Complexity	Impact if ignored	Design response
C01	3 to 4 feeder systems with different employee numbers	Same person counted twice across years	Map_Employee_ID to one durable Employee_ID
C02	Organisation codes and names changed between years (2024 to 2025)	Broken 10-year trends	Effective-dated Map_Organisation; SCD Type 2 Dim_Organisation
C03	Same event named differently across systems (promotion, career plan)	Under-counted promotions and career moves	Map_Event_Reason to unified event types
C04	Several job records per employee	Totals multiplied (1 employee shows as 3)	One-record-per-date rule; declared grain; duplicate test T02
C05	Years missing or incomplete in every system	Trend cannot be shown for those years: a showstopper	HR confirms coverage per year; affected years flagged or excluded
C06	Manual Excel files feeding some reports	Inconsistent, unaudited figures	Controlled SharePoint templates with owner and validation
C07	Snowflake built as one flat table	Cannot join HR to other departments; rework	Star schema and DSD-01 to DSD-14 mandated

Open questions and HR inputs needed

These inputs turn the sample into the real document for the first critical dashboard.

HR: share the actual Level-1 dashboard to replace the illustrative sample
HR: list which years each source system holds, to confirm the 10-year coverage (C05)
HR: provide sample extracts from SuccessFactors and each legacy system, masked where needed
HR: provide every existing mapping file and its versions
HR: confirm Headcount scope (contractors, secondees, trainees, employees on long leave)
HR: confirm the attrition method (opening and closing average, or 12-month average) and the voluntary or involuntary reason list
HR: name an owner for each KPI and each mapping table
HR: give the official headcount figure for one reconciliation month, and the tolerance
HR: define row-level security, meaning who sees which organisation units
DSD: share Snowflake naming standards, environments and the load tool to be used

Sign-off
DSD starts the build only after all four roles sign this document for the dashboard.

Role	Name	Responsibility	Signature	Date
HR business owner	Khalid Mahmood	KPI definitions, mapping ownership, scope
DSD lead	To be named	Snowflake build against DSD-01 to DSD-14
Snowflake implementation lead	To be named	Technical feasibility and load design
Technical Architect	Dinesh R M, Team Academy	Data model, rules and POC validation

HR Analytics — Technical Architecture and POC Document (Sample)

Recent Post

Document control

Sample dashboard and KPI definitions

Solution architecture

Target data model

Table specifications

Fact_Workforce_Snapshot

Keys and relationships

Translation (mapping) tables

Business and data quality rules

Build requirements for DSD

Power BI semantic model blueprint

POC validation test cases

Complexity and showstopper register

Open questions and HR inputs needed

Sign-off
DSD starts the build only after all four roles sign this document for the dashboard.

Estimate Shipping

Add A Coupon

HR Analytics — Technical Architecture and POC Document (Sample)

Recent Post

Document control

Sample dashboard and KPI definitions

Solution architecture

Target data model

Table specifications

Fact_Workforce_Snapshot

Keys and relationships

Translation (mapping) tables

Business and data quality rules

Build requirements for DSD

Power BI semantic model blueprint

POC validation test cases

Complexity and showstopper register

Open questions and HR inputs needed

Sign-offDSD starts the build only after all four roles sign this document for the dashboard.

Related Articles

Estimate Shipping

Add A Coupon

Sign-off
DSD starts the build only after all four roles sign this document for the dashboard.